{"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":"markdown","source":"What do you do if you find reading CSV files in [pandas](https://pandas.pydata.org/) is quite slow? In fact, CSV is not the best format for input and output from a performance perspective, so you may convert CSV data into other formats. For this competition, we already see:\n\n- [💥Feather to compress your data(8x Faster)](https://www.kaggle.com/code/gazu468/feather-to-compress-your-data-8x-faster)\n- [TPS_OCT22 Load Entire Data in just 24 seconds](https://www.kaggle.com/code/senkmp/tps-oct22-load-entire-data-in-just-24-seconds)\n- [Compress Files - Parquet (7x Loading Speedup)](https://www.kaggle.com/code/reymaster/compress-files-parquet-7x-loading-speedup)\n\nOr you may try other libraries for reading CSVs:\n\n- [🚀Modin📚Lib to🎞🎠 Load 📕data ⚡Faster](https://www.kaggle.com/code/satyaprakashshukl/modin-lib-to-load-data-faster)\n\nIndeed, [pandas 1.4](https://pandas.pydata.org/pandas-docs/stable/whatsnew/v1.4.0.html) has introduced [a new CSV engine](https://pandas.pydata.org/pandas-docs/stable/whatsnew/v1.4.0.html#multi-threaded-csv-reading-with-a-new-csv-engine-based-on-pyarrow) based on [PyArrow](https://arrow.apache.org/docs/python/index.html):\n\n- [The fastest way to read a CSV in Pandas](https://pythonspeed.com/articles/pandas-read-csv-fast/)\n\nUnfortunately, the current version of pandas with Kaggle is `1.3.5`:","metadata":{}},{"cell_type":"code","source":"import pandas as pd\npd.__version__","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:19:04.477713Z","iopub.execute_input":"2022-10-01T17:19:04.478171Z","iopub.status.idle":"2022-10-01T17:19:04.512337Z","shell.execute_reply.started":"2022-10-01T17:19:04.478079Z","shell.execute_reply":"2022-10-01T17:19:04.511007Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"which is the latest version that can be installed with Python 3.7 (pandas 1.4 requires Python 3.8 and it seems impossible to [upgrade Python version with Kaggle](https://www.kaggle.com/questions-and-answers/210493)).","metadata":{}},{"cell_type":"code","source":"import sys\nsys.version","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:19:04.514698Z","iopub.execute_input":"2022-10-01T17:19:04.515499Z","iopub.status.idle":"2022-10-01T17:19:04.522118Z","shell.execute_reply.started":"2022-10-01T17:19:04.515414Z","shell.execute_reply":"2022-10-01T17:19:04.520941Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In this notebook, I would like to give it a try to directly use PyArrow for reading CSV files and then convert PyArrow tables into pandas data frames, which leads to about 3x speedup in the measured elapsed time. (Sadly, CSV is in any way not as fast as feather, parquet or pickle.)","metadata":{}},{"cell_type":"code","source":"import gc\nimport time\nimport pandas as pd\nimport pyarrow.csv","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-10-01T17:19:04.524254Z","iopub.execute_input":"2022-10-01T17:19:04.524935Z","iopub.status.idle":"2022-10-01T17:19:04.549377Z","shell.execute_reply.started":"2022-10-01T17:19:04.524886Z","shell.execute_reply":"2022-10-01T17:19:04.548522Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We read the same CSV files twice, so need to be careful with disk cache. The following function aims to consume Linux disk cache with meaningless data, to invalidate disk cache loaded before.","metadata":{}},{"cell_type":"code","source":"def comsume_linux_disk_cache(size_in_gb):\n    \"\"\"Consume Linux disk cache.\"\"\"\n    for i in range(size_in_gb):\n        !dd if=/dev/zero of=file$i oflag=direct bs=1M count=1000 >/dev/null 2>&1\n    !sync\n    for i in range(size_in_gb):\n        !cat file$i >/dev/null\n        !rm file$i","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:19:04.550947Z","iopub.execute_input":"2022-10-01T17:19:04.551871Z","iopub.status.idle":"2022-10-01T17:19:04.564042Z","shell.execute_reply.started":"2022-10-01T17:19:04.551822Z","shell.execute_reply":"2022-10-01T17:19:04.562841Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Reading CSV files and concatenation with pandas","metadata":{}},{"cell_type":"code","source":"%%time\ncomsume_linux_disk_cache(16)","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:19:04.565594Z","iopub.execute_input":"2022-10-01T17:19:04.566040Z","iopub.status.idle":"2022-10-01T17:22:00.887312Z","shell.execute_reply.started":"2022-10-01T17:19:04.565998Z","shell.execute_reply":"2022-10-01T17:22:00.885662Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n\n# from the example in \"Dataset Description\"\ndtypes_df = pd.read_csv(\n    '/kaggle/input/tabular-playground-series-oct-2022/train_dtypes.csv'\n)\ndtypes = {k: v for (k, v) in zip(dtypes_df.column, dtypes_df.dtype)}\n\n# load the training data as one DataFrame\ndata = []\ntimes = []\nfor i in range(10):\n    t1 = time.time()\n    \n    df = pd.read_csv(\n        f'/kaggle/input/tabular-playground-series-oct-2022/train_{i}.csv', dtype=dtypes\n    )\n    \n    t2 = time.time()\n    dt = t2 - t1\n    data.append(df)\n    times.append(dt)\n    print(f'loading train_{i}.csv : {dt:.3f}s')\ntrain_df = pd.concat(data, ignore_index=True)\ntime_df = pd.DataFrame({'time': times})","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:22:00.890545Z","iopub.execute_input":"2022-10-01T17:22:00.890952Z","iopub.status.idle":"2022-10-01T17:28:12.166380Z","shell.execute_reply.started":"2022-10-01T17:22:00.890903Z","shell.execute_reply":"2022-10-01T17:28:12.164176Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"time_df","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:28:12.169439Z","iopub.execute_input":"2022-10-01T17:28:12.169920Z","iopub.status.idle":"2022-10-01T17:28:12.194623Z","shell.execute_reply.started":"2022-10-01T17:28:12.169874Z","shell.execute_reply":"2022-10-01T17:28:12.193529Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"time_df.sum()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:28:12.195934Z","iopub.execute_input":"2022-10-01T17:28:12.196268Z","iopub.status.idle":"2022-10-01T17:28:12.206678Z","shell.execute_reply.started":"2022-10-01T17:28:12.196238Z","shell.execute_reply":"2022-10-01T17:28:12.205288Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"time_df.describe()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:28:12.208741Z","iopub.execute_input":"2022-10-01T17:28:12.209071Z","iopub.status.idle":"2022-10-01T17:28:12.239291Z","shell.execute_reply.started":"2022-10-01T17:28:12.209042Z","shell.execute_reply":"2022-10-01T17:28:12.238056Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pandas_df = train_df","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:28:12.240873Z","iopub.execute_input":"2022-10-01T17:28:12.241206Z","iopub.status.idle":"2022-10-01T17:28:12.246979Z","shell.execute_reply.started":"2022-10-01T17:28:12.241176Z","shell.execute_reply":"2022-10-01T17:28:12.245569Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del data, times, dtypes_df, df, train_df, time_df\ngc.collect();","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:28:12.251064Z","iopub.execute_input":"2022-10-01T17:28:12.251479Z","iopub.status.idle":"2022-10-01T17:28:12.724982Z","shell.execute_reply.started":"2022-10-01T17:28:12.251433Z","shell.execute_reply":"2022-10-01T17:28:12.723849Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Reading CSV files and concatenation with PyArrow","metadata":{}},{"cell_type":"markdown","source":"For [`pyarrow.csv.read_csv()`](https://arrow.apache.org/docs/python/generated/pyarrow.csv.read_csv.html), the column data types may be passed as `convert_options`, but there is a pitfall; `object` and `float16` are not accepted. We remove them from `column_types` and later convert `float16` columns.","metadata":{}},{"cell_type":"code","source":"%%time\ncomsume_linux_disk_cache(16)","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:28:12.727146Z","iopub.execute_input":"2022-10-01T17:28:12.727705Z","iopub.status.idle":"2022-10-01T17:31:12.306613Z","shell.execute_reply.started":"2022-10-01T17:28:12.727586Z","shell.execute_reply":"2022-10-01T17:31:12.304710Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n\n# from the example in \"Dataset Description\"\ndtypes_df = pd.read_csv(\n    '/kaggle/input/tabular-playground-series-oct-2022/train_dtypes.csv'\n)\ndtypes = {k: v for (k, v) in zip(dtypes_df.column, dtypes_df.dtype)}\n\n# load the training data as one DataFrame\ndata = []\ntimes = []\nfor i in range(10):\n    t1 = time.time()\n\n    df = (\n        pyarrow.csv.read_csv(\n            f'/kaggle/input/tabular-playground-series-oct-2022/train_{i}.csv',\n            convert_options=pyarrow.csv.ConvertOptions(\n                column_types={\n                    k: v for (k, v) in dtypes.items() if v not in ['object', 'float16']\n                }\n            ),\n        )\n        .to_pandas()\n        .astype({k: v for (k, v) in dtypes.items() if v == 'float16'}, copy=False)\n    )\n    \n    t2 = time.time()\n    dt = t2 - t1\n    data.append(df)\n    times.append(dt)\n    print(f'loading train_{i}.csv : {dt:.3f}s')\ntrain_df = pd.concat(data, ignore_index=True)\ntime_df = pd.DataFrame({'time': times})","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:31:12.308831Z","iopub.execute_input":"2022-10-01T17:31:12.309270Z","iopub.status.idle":"2022-10-01T17:32:58.418022Z","shell.execute_reply.started":"2022-10-01T17:31:12.309224Z","shell.execute_reply":"2022-10-01T17:32:58.416553Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"time_df","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:32:58.419883Z","iopub.execute_input":"2022-10-01T17:32:58.420378Z","iopub.status.idle":"2022-10-01T17:32:58.432967Z","shell.execute_reply.started":"2022-10-01T17:32:58.420329Z","shell.execute_reply":"2022-10-01T17:32:58.431487Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"time_df.sum()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:32:58.434957Z","iopub.execute_input":"2022-10-01T17:32:58.435497Z","iopub.status.idle":"2022-10-01T17:32:58.454848Z","shell.execute_reply.started":"2022-10-01T17:32:58.435429Z","shell.execute_reply":"2022-10-01T17:32:58.453843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"time_df.describe()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:32:58.456167Z","iopub.execute_input":"2022-10-01T17:32:58.456602Z","iopub.status.idle":"2022-10-01T17:32:58.476524Z","shell.execute_reply.started":"2022-10-01T17:32:58.456568Z","shell.execute_reply":"2022-10-01T17:32:58.475163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pyarrow_df = train_df","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:32:58.478298Z","iopub.execute_input":"2022-10-01T17:32:58.479093Z","iopub.status.idle":"2022-10-01T17:32:58.484636Z","shell.execute_reply.started":"2022-10-01T17:32:58.479045Z","shell.execute_reply":"2022-10-01T17:32:58.483384Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del data, times, dtypes_df, df, train_df, time_df\ngc.collect();","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:32:58.486371Z","iopub.execute_input":"2022-10-01T17:32:58.487304Z","iopub.status.idle":"2022-10-01T17:32:58.769048Z","shell.execute_reply.started":"2022-10-01T17:32:58.487251Z","shell.execute_reply":"2022-10-01T17:32:58.767789Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Data comparison","metadata":{}},{"cell_type":"markdown","source":"Let us check whether the 2 data frames are the same. First, for column data types:","metadata":{}},{"cell_type":"code","source":"(pandas_df.dtypes == pyarrow_df.dtypes).all()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:32:58.770790Z","iopub.execute_input":"2022-10-01T17:32:58.771178Z","iopub.status.idle":"2022-10-01T17:32:58.788170Z","shell.execute_reply.started":"2022-10-01T17:32:58.771146Z","shell.execute_reply":"2022-10-01T17:32:58.786924Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"At first glance, some values in specific columns look different:","metadata":{}},{"cell_type":"code","source":"with pd.option_context('display.max_rows', None):\n    display((pandas_df == pyarrow_df).all())","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:32:58.790815Z","iopub.execute_input":"2022-10-01T17:32:58.791858Z","iopub.status.idle":"2022-10-01T17:33:04.425275Z","shell.execute_reply.started":"2022-10-01T17:32:58.791809Z","shell.execute_reply":"2022-10-01T17:33:04.424020Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"but this is because of `nan` values. After adequately handling `nan` and empty strings, they are the same indeed:","metadata":{}},{"cell_type":"code","source":"# Unfortunately, the following code leads to memory error (too much memory usage)\n\n# pandas_df.fillna(0, inplace=True)\n# pyarrow_df.fillna(0, inplace=True)\n# pyarrow_df['team_scoring_next'].replace('', 0, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:33:04.427003Z","iopub.execute_input":"2022-10-01T17:33:04.427526Z","iopub.status.idle":"2022-10-01T17:33:04.433236Z","shell.execute_reply.started":"2022-10-01T17:33:04.427478Z","shell.execute_reply":"2022-10-01T17:33:04.431931Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# column by column\nfor c in pandas_df.columns:\n    pandas_df[c].fillna(0, inplace=True)\n    pyarrow_df[c].fillna(0, inplace=True)\npyarrow_df['team_scoring_next'].replace('', 0, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:33:04.434629Z","iopub.execute_input":"2022-10-01T17:33:04.434999Z","iopub.status.idle":"2022-10-01T17:33:12.017326Z","shell.execute_reply.started":"2022-10-01T17:33:04.434956Z","shell.execute_reply":"2022-10-01T17:33:12.016158Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"(pandas_df == pyarrow_df).all().all()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:33:12.018651Z","iopub.execute_input":"2022-10-01T17:33:12.019293Z","iopub.status.idle":"2022-10-01T17:33:16.538515Z","shell.execute_reply.started":"2022-10-01T17:33:12.019256Z","shell.execute_reply":"2022-10-01T17:33:16.537057Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Memory usage:","metadata":{}},{"cell_type":"code","source":"print(pandas_df.memory_usage(index=True, deep=False).sum())\nprint(pyarrow_df.memory_usage(index=True, deep=False).sum())\nprint(pandas_df.memory_usage(index=True, deep=True).sum())\nprint(pyarrow_df.memory_usage(index=True, deep=True).sum())","metadata":{"execution":{"iopub.status.busy":"2022-10-01T17:33:16.540008Z","iopub.execute_input":"2022-10-01T17:33:16.540364Z","iopub.status.idle":"2022-10-01T17:33:20.686948Z","shell.execute_reply.started":"2022-10-01T17:33:16.540325Z","shell.execute_reply":"2022-10-01T17:33:20.685532Z"},"trusted":true},"execution_count":null,"outputs":[]}]}