{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n for filename in filenames:\n print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2023-04-01T16:58:35.115887Z","iopub.execute_input":"2023-04-01T16:58:35.116820Z","iopub.status.idle":"2023-04-01T16:58:35.153598Z","shell.execute_reply.started":"2023-04-01T16:58:35.116781Z","shell.execute_reply":"2023-04-01T16:58:35.152556Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Dataset Loading and Performance Analytics

\n","metadata":{}},{"cell_type":"markdown","source":"

The given dataset for this competition is too big to process for the allocated memory

\n\nLoading large datasets into memory can be a challenge when you have limited memory. Here are a few workarounds you can use to load large datasets into a small memory:\n\n* **Load data in chunks:** You can use the chunksize parameter in the read_csv() function to load the data in smaller chunks. This will allow you to process the data in manageable portions instead of loading the entire dataset into memory. You can loop through the chunks and process each chunk before moving on to the next.\n\n* **Select only the columns you need:** You can use the usecols parameter in the read_csv() function to select only the columns you need. This will reduce the memory usage by loading only the required columns into memory.\n\n* **Load data from disk when needed:** Instead of loading the entire dataset into memory, you can keep the data on disk and load only the portion of the data you need when you need it. This can be done using libraries such as dask or pandas's read_csv() function with the iterator and chunksize parameters.\n\n* **Use data compression:** If your data is highly compressible, you can save memory by compressing it before loading it into memory. Pandas supports reading compressed files such as gzip, bz2, and zip using the read_csv() function.\n\n* **Use smaller data types:** You can reduce the memory usage by using smaller data types for your columns. For example, if you know that a column only contains integers between 0 and 255, you can use the uint8 data type instead of the default int64 data type. Pandas provides a dtype parameter that can be used to specify the data type for each column.\n\n* **Remove unnecessary data:** If your dataset contains unnecessary data that you don't need, you can remove it before loading it into memory. This can be done using tools such as awk, sed, or grep on the command line, or using Python libraries such as pandas or dask.\n\nBy using these workarounds, you can load and process large datasets in a small memory environment.\n\n\n

We are going to use three methods to test the performance:

\n\n* [First we try loading the dataset with the `pandas` library in smaller chunks.](#1)\n* [Then we are going to try loading the dataset using `pyspark`.](#2)\n* [Then we are going to try loading the dataset using `sqlite`.](#3)\n\n

Two things are going to be tested in the process:

\n\n* Time of execution. (in seconds)\n* RAM used (in MiB)","metadata":{}},{"cell_type":"markdown","source":"

First of all, we nee memory profiler to track how much RAM is being used for the data reading tasks

\n\n
To do that we need to run the following command:
","metadata":{}},{"cell_type":"code","source":"!pip install memory_profiler","metadata":{"execution":{"iopub.status.busy":"2023-03-31T17:28:13.414546Z","iopub.execute_input":"2023-03-31T17:28:13.414952Z","iopub.status.idle":"2023-03-31T17:28:24.856632Z","shell.execute_reply.started":"2023-03-31T17:28:13.414915Z","shell.execute_reply":"2023-03-31T17:28:24.855582Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Method 1: Using Pandas

\n\n
\n\n

Let's select a few columns (assuming their usefulness)

\n\n* Since the dataset is too large to import, it's been made smaller.\n\n*P.S. The following workaround has been collected from this source: [Link](https://www.kaggle.com/code/zonwie/begin-how-do-i-import-the-dataset)*\n\n\nšŸ”[Back to top](#0)","metadata":{}},{"cell_type":"code","source":"%load_ext memory_profiler\ncols_without_missing = [\"session_id\", \"index\", \"elapsed_time\", \"event_name\", \"name\", \"level\", \"room_coor_x\", \"room_coor_y\", \"screen_coor_x\", \"screen_coor_y\", \"fqid\", \"room_fqid\", \"fullscreen\", \n \"hq\", \"music\", \"level_group\"]\n\n#Make dataframe smaller.\ndtypes_smaller = {\"session_id\": np.int64, \"index\": np.int64, \"elapsed_time\": np.int64, \"event_name\": object, \"name\": object, \"level\": np.int8, \"room_coor_x\": np.float32, \"room_coor_y\": np.float32, \n \"screen_coor_x\": np.float32, \"screen_coor_y\": np.float32, \"fqid\": object, \"room_fqid\": object, \"fullscreen\": np.int8, \"hq\": np.int8 , \"music\": np.int8, \"level_group\": object}\n\n#Read the file.\n%time %memit dataset = pd.read_csv(\"/kaggle/input/predict-student-performance-from-game-play/train.csv\", usecols = cols_without_missing, dtype = dtypes_smaller)\n%time %memit dataset.head()","metadata":{"execution":{"iopub.status.busy":"2023-03-31T14:45:13.554046Z","iopub.execute_input":"2023-03-31T14:45:13.554448Z","iopub.status.idle":"2023-03-31T14:47:15.720262Z","shell.execute_reply.started":"2023-03-31T14:45:13.554411Z","shell.execute_reply":"2023-03-31T14:47:15.718675Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"___\n

System Performance to read the dataset

\n\n| Description | Value |\n| --- | --- |\n| Peak Memory | 6040.46 MiB |\n| Memory Increment | 5830.76 MiB |\n| CPU Time (User) | 1min 4s |\n| CPU Time (System) | 6.83 s |\n| Total CPU Time | 1min 10s |\n| Wall Time | 2min 1s |\n\n___\n\n

System Performance to load dataset head

\n\n| Description | Value |\n| --- | --- |\n| Peak Memory | 3933.94 MiB |\n| Memory Increment | 0.00 MiB |\n| CPU Time (User) | 125 ms |\n| CPU Time (System) | 55.7 ms |\n| Total CPU Time | 181 ms |\n| Wall Time | 326 ms |\n\n___","metadata":{}},{"cell_type":"markdown","source":"### Conclusion: \n___\n\n#### Quite Memory Consuming","metadata":{}},{"cell_type":"markdown","source":"

Method 2: Loading the Dataset with Pyspark

\n(As a distributed computing approach)\n
\n
\n\nšŸ” [Back to top](#0)","metadata":{}},{"cell_type":"markdown","source":"### Installing Pyspark šŸ—³","metadata":{}},{"cell_type":"code","source":"!pip install pyspark","metadata":{"execution":{"iopub.status.busy":"2023-03-31T15:21:22.292241Z","iopub.execute_input":"2023-03-31T15:21:22.29257Z","iopub.status.idle":"2023-03-31T15:22:07.955835Z","shell.execute_reply.started":"2023-03-31T15:21:22.292536Z","shell.execute_reply":"2023-03-31T15:22:07.954759Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Initializing SparkSession and Reading the dataset as a Spark Schema. šŸŽ‡","metadata":{}},{"cell_type":"code","source":"from pyspark.sql import SparkSession\n%load_ext memory_profiler\n# create a SparkSession object\nspark = SparkSession.builder.appName('predict-student-performance').getOrCreate()\n\n# read in the data\n%time %memit df = spark.read.csv('/kaggle/input/predict-student-performance-from-game-play/train.csv', header=True, inferSchema=True)\n","metadata":{"execution":{"iopub.status.busy":"2023-03-31T15:22:19.819232Z","iopub.execute_input":"2023-03-31T15:22:19.819597Z","iopub.status.idle":"2023-03-31T15:23:54.523592Z","shell.execute_reply.started":"2023-03-31T15:22:19.819559Z","shell.execute_reply":"2023-03-31T15:23:54.52232Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

PySpark Performance to read the entire dataset

\n\n| Statistic | Value |\n| --- | --- |\n| Peak Memory | 215.30 MiB |\n| Memory Increment | 0.54 MiB |\n| CPU Total Time(User+Sys) | 207 ms |\n| Wall Time | 1min 28s |\n","metadata":{}},{"cell_type":"code","source":"%time %memit df.show(5)","metadata":{"execution":{"iopub.status.busy":"2023-03-31T15:28:24.479838Z","iopub.execute_input":"2023-03-31T15:28:24.480418Z","iopub.status.idle":"2023-03-31T15:28:25.297099Z","shell.execute_reply.started":"2023-03-31T15:28:24.480365Z","shell.execute_reply":"2023-03-31T15:28:25.295927Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

PySpark Performance to read the dataset heads

\n\n| Peak Memory | Increment | CPU Time (User+Sys) | Wall Time |\n|-------------|-----------|---------------------|-----------|\n| 215.35 MiB | 0.02 MiB | 175 ms | 806 ms | \n\n","metadata":{}},{"cell_type":"markdown","source":"### Conclusion:\n---\n#### Loading the dataset with `Pyspark` results in way less memory consumption","metadata":{}},{"cell_type":"markdown","source":"\n

Method 3: Using SQLite to store and access the data

\n
\n\n","metadata":{}},{"cell_type":"code","source":"%%time\n\nimport sqlite3\nfrom memory_profiler import memory_usage\n\n# create a connection to the database\nconn = sqlite3.connect('training.db')\n\n# read in the data and write it to the database\n\ndef write_data_to_db():\n for chunk in pd.read_csv('/kaggle/input/predict-student-performance-from-game-play/train.csv', chunksize=100000):\n chunk.to_sql('data', conn, if_exists='append')\n\n\n\n# profile the memory usage of the function\nmem_usage = memory_usage((write_data_to_db, ), interval=0.1)\n\nprint('Memory usage (in MB):', max(mem_usage) - min(mem_usage))","metadata":{"execution":{"iopub.status.busy":"2023-04-01T16:58:44.486313Z","iopub.execute_input":"2023-04-01T16:58:44.486692Z","iopub.status.idle":"2023-04-01T17:05:12.897914Z","shell.execute_reply.started":"2023-04-01T16:58:44.486660Z","shell.execute_reply":"2023-04-01T17:05:12.896644Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"| Metric | Value |\n|--------------------|----------------|\n| Memory Usage (in MB)| 123.08203125 |\n| CPU Time | 5min 21s |\n| System Time | 20.6 s |\n| Total Time | 5min 41s |\n| Wall Time | 6min 53s |\n","metadata":{}},{"cell_type":"code","source":"%load_ext memory_profiler\n# query the database\nquery = 'SELECT * FROM data where level_group=\"0-4\"'\n%time %memit result = pd.read_sql(query, conn)","metadata":{"_kg_hide-output":true,"execution":{"iopub.status.busy":"2023-04-01T17:05:58.306357Z","iopub.execute_input":"2023-04-01T17:05:58.306740Z","iopub.status.idle":"2023-04-01T17:06:47.703313Z","shell.execute_reply.started":"2023-04-01T17:05:58.306704Z","shell.execute_reply":"2023-04-01T17:06:47.702237Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Performance results of the query

\n\n| Metric | Value |\n| --- | --- |\n| Peak Memory | 5.64058 GB |\n| Memory Increment | 5.37979 GB |\n| CPU Times (user) | 36.7 s |\n| CPU Times (sys) | 8.52 s |\n| Total CPU Times | 45.2 s |\n| Wall Time | 49.4 s |\n","metadata":{}},{"cell_type":"code","source":"result.head()","metadata":{"execution":{"iopub.status.busy":"2023-04-01T17:07:29.555509Z","iopub.execute_input":"2023-04-01T17:07:29.555912Z","iopub.status.idle":"2023-04-01T17:07:29.598700Z","shell.execute_reply.started":"2023-04-01T17:07:29.555877Z","shell.execute_reply":"2023-04-01T17:07:29.597684Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Conclusion:\n___\n* The memory usage is dependent on the size of the data. (can still crash the system)\n* Also, the process takes a lot of time loading \n\n","metadata":{}},{"cell_type":"markdown","source":"

Final Comments

\n\n* If you're someone who is comfortable with SQL you should definitely go for SQL. In this case it's lightweight and easily manoeuvrable. But it still can cause the system to crash, because we are still using pandas to process the queries.\n\n* On the other hand if you're good with PySpark, then it's cherry on top for you, because it's been the fastest so far.\n\n* Yes, using just pandas would probably lead you to make some decisions like: having to decide dropping some columns beforehand or having to change datatypes etc. \n\n\nIf you want to play around with the whole dataset at these settings, then consider using distributed computing methods (as in PySpark). They are faster and memory efficient.","metadata":{}}]}