{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":50160,"databundleVersionId":7602123,"sourceType":"competition"},{"sourceId":162001866,"sourceType":"kernelVersion"}],"dockerImageVersionId":30646,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Home Credit: faster data loading\n\n*Disclaimer: timings presented in this notebook are only indicative, as those could vary on different systems from run to run, but they can still give you an idea as to what is slow and what is blazingly fast.*\n\n# Introduction \n\nFor this competition we've got quite a lot of data: dozens of files with the overall size of ~26 GB. Loading data is going to be an intrinsic part of every kernel, so it is obviously important to minimize reading times as much as possible. \n\nAccording to the official dataset description, all files can be found in both CSV and Parquet formats. So what is the fastest way to read them? Let's figure this out by benchmarking popular python packages: [pandas](https://pandas.pydata.org/docs/), [polars](https://pola.rs/) and [datatable](https://datatable.readthedocs.io/).\n\n![](https://www.googleapis.com/download/storage/v1/b/kaggle-forum-message-attachments/o/inbox%2F2099265%2Fe314f1bee56e388ecb7ec1b090aec82d%2Fpandas_polars_datatable.png?generation=1707791559484413&alt=media)\n\n**TL;DR:** use `polars` to read Parquet or `datatable` to load memory-mapped files almost instantly.","metadata":{}},{"cell_type":"markdown","source":"# 1. Loading data from CSV files\n\nFirst, let's do some imports and compile a list of the provided CSV files.","metadata":{}},{"cell_type":"code","source":"import pandas as pd \nimport polars as pl\nimport datatable as dt\nimport numpy as np\nimport time\nimport glob\nimport matplotlib.pyplot as plt\nfrom tqdm import tqdm\n\n\nDATA_PATH = \"/kaggle/input/home-credit-credit-risk-model-stability/\"\nCSV_PATH = DATA_PATH + \"csv_files/train/\"\ncsv_files = sorted(glob.glob(f\"{CSV_PATH}/*\"))","metadata":{"execution":{"iopub.status.busy":"2024-02-08T07:51:20.36393Z","iopub.execute_input":"2024-02-08T07:51:20.366253Z","iopub.status.idle":"2024-02-08T07:51:21.406478Z","shell.execute_reply.started":"2024-02-08T07:51:20.366016Z","shell.execute_reply":"2024-02-08T07:51:21.405113Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, let's measure timing needed to load CSV files into dataframes. For `polars` and `datatable`, we will also save a copy of data into binary formats for further comparison.","metadata":{}},{"cell_type":"code","source":"# Number of files to use for file-wise comparison\nNCOMPARE = 5 \n\ncsv_fns = {\n    \"pandas\"    : pd.read_csv,\n    \"polars\"    : pl.read_csv,\n    \"datatable\" : dt.fread,\n}\n\ncsv_times = {\n    \"pandas\"    : [], \n    \"polars\"    : [],\n    \"datatable\" : [],  \n}\n\nwrite_mmap_fns = {\n    \"polars\"    : (\"write_ipc\", \"ipc\"),\n    \"datatable\" : (\"to_jay\", \"jay\"),\n}\n\nfor fid, f in enumerate(tqdm(csv_files)):\n    fname = f.split(\"/\")[-1][:-3]\n    for package, csv_fn in csv_fns.items():\n        t0 = time.time()\n        df = csv_fn(f)    \n        t = time.time() - t0\n        csv_times[package].append(t)\n        \n        # For polars and datatable save data into their native bin formats\n        if package in write_mmap_fns and fid < NCOMPARE:\n            write_mmap_fn = write_mmap_fns[package][0]\n            mmap_extension = write_mmap_fns[package][1]\n            getattr(df, write_mmap_fn)(fname + mmap_extension)\n            \n        del df","metadata":{"execution":{"iopub.status.busy":"2024-02-08T07:51:36.716065Z","iopub.execute_input":"2024-02-08T07:51:36.716504Z","iopub.status.idle":"2024-02-08T07:51:51.049878Z","shell.execute_reply.started":"2024-02-08T07:51:36.716468Z","shell.execute_reply":"2024-02-08T07:51:51.048262Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The total time taken by a package to load CSV files can now be easily calculated.","metadata":{}},{"cell_type":"code","source":"for package, t in csv_times.items():\n    print(f\"{package:10}: {sum(t):6.2f} [s]\")","metadata":{"execution":{"iopub.status.busy":"2024-02-08T07:40:44.560631Z","iopub.execute_input":"2024-02-08T07:40:44.561601Z","iopub.status.idle":"2024-02-08T07:40:44.56821Z","shell.execute_reply.started":"2024-02-08T07:40:44.561559Z","shell.execute_reply":"2024-02-08T07:40:44.566947Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We can also plot the timings package- and file-wise. To make the plot clear enough, we limit comparison to the first five files.","metadata":{}},{"cell_type":"code","source":"for i, t in enumerate(csv_times.values()):\n    plt.bar(np.arange(NCOMPARE) + (i-1)*0.2, list(t)[:NCOMPARE], width=0.2)\n    \nxlabels = [f.split(\"/\")[-1][:-4] for f in csv_files[:NCOMPARE]]\nplt.xticks(range(NCOMPARE), xlabels, rotation=20)\n    \nplt.legend(csv_times.keys())\nplt.ylabel(\"Seconds\")\nplt.title(\"Time to load CSV files\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-08T07:40:44.57196Z","iopub.execute_input":"2024-02-08T07:40:44.572471Z","iopub.status.idle":"2024-02-08T07:40:44.93434Z","shell.execute_reply.started":"2024-02-08T07:40:44.572408Z","shell.execute_reply":"2024-02-08T07:40:44.933077Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As we see,`pandas` is pretty slow when it comes to load larger files. `datatable` is doing a little bit better than `polars`, though both these packages are quite fast demonstrating similar performance file-wise.","metadata":{}},{"cell_type":"markdown","source":"# 2. Loading data from Parquet files\n\nLoading data from Parquet should be much faster, because that's a binary format. Here, we will only compare `pandas` and `polars`, because `datatable` doesn't have a dedicated function to load `.parquet` relying on the `arrow` library.","metadata":{}},{"cell_type":"code","source":"PARQUET_PATH = DATA_PATH + \"/parquet_files/train/\"\nparquet_files = sorted(glob.glob(f\"{PARQUET_PATH}/*\"))\n\nparquet_fns = {\n    \"pandas\"    : pd.read_parquet,\n    \"polars\"    : pl.read_parquet,\n}\n\nparquet_times = {\n    \"pandas\"    : [],\n    \"polars\"    : [],  \n}\n\nfor f in tqdm(parquet_files):\n    for package, parquet_fn in parquet_fns.items():\n        t0 = time.time()\n        df = parquet_fn(f)\n        t = time.time() - t0\n        parquet_times[package].append(t)\n        del df","metadata":{"execution":{"iopub.status.busy":"2024-02-08T07:40:44.936006Z","iopub.execute_input":"2024-02-08T07:40:44.93642Z","iopub.status.idle":"2024-02-08T07:44:19.656826Z","shell.execute_reply.started":"2024-02-08T07:40:44.936387Z","shell.execute_reply":"2024-02-08T07:44:19.655349Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The total time taken by a package to load Parquet files has been measured as follows","metadata":{}},{"cell_type":"code","source":"for package, t in parquet_times.items():\n    print(f\"{package:10}: {sum(t):6.2f} [s]\")","metadata":{"execution":{"iopub.status.busy":"2024-02-08T07:44:19.658823Z","iopub.execute_input":"2024-02-08T07:44:19.66033Z","iopub.status.idle":"2024-02-08T07:44:19.669309Z","shell.execute_reply.started":"2024-02-08T07:44:19.660274Z","shell.execute_reply":"2024-02-08T07:44:19.667883Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Similarly to CSV, we can also compare timings package- and file-wise for the first five files.","metadata":{}},{"cell_type":"code","source":"for i, t in enumerate(parquet_times.values()):\n    plt.bar(np.arange(NCOMPARE) + (i-0.5)*0.2, list(t)[:NCOMPARE], width=0.2)\n    \nxlabels = [f.split(\"/\")[-1][:-4] for f in csv_files[:NCOMPARE]]\nplt.xticks(range(NCOMPARE), xlabels, rotation=20)\n    \nplt.legend(csv_times.keys())\nplt.ylabel(\"Seconds\")\nplt.title(\"Time to load Parquet files\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-08T07:44:19.671395Z","iopub.execute_input":"2024-02-08T07:44:19.67183Z","iopub.status.idle":"2024-02-08T07:44:19.958693Z","shell.execute_reply.started":"2024-02-08T07:44:19.671797Z","shell.execute_reply":"2024-02-08T07:44:19.95743Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As expected, both packages process Parquet much faster than CSV with `polars` demonstrating better performance. However, processing times are still non-negligible when it comes to loading all the data.","metadata":{}},{"cell_type":"markdown","source":"# 3. Loading data with [memory mapping](https://en.wikipedia.org/wiki/Memory-mapped_file)\n\nOnce data is loaded into a dataframe, you can actually export it in a binary format appropriate for memory mapping: [ipc](https://docs.pola.rs/py-polars/html/reference/api/polars.DataFrame.write_ipc.html) for `polars` and [jay](https://datatable.readthedocs.io/en/latest/api/frame/to_jay.html) for `datatable`. Reading data back should literally take no time, because files are instantly mapped into the memory address space. To confirm that, let's benchmark `.ipc` and `.jay` loading for five first files from the competition's data.","metadata":{}},{"cell_type":"code","source":"mmap_fns = {\n    \"polars\"    : pl.read_ipc,\n    \"datatable\" : dt.fread,\n}\n\nmmap_times = {\n    \"polars\"    : [],\n    \"datatable\" : [],  \n}\n\n\nfor f in tqdm(csv_files[:NCOMPARE]):\n    for package, mmap_fn in mmap_fns.items():\n        mmap_extension = write_mmap_fns[package][1]\n        mmap_fname = f.split(\"/\")[-1][:-3] + mmap_extension    \n    \n        t0 = time.time()\n        df = mmap_fn(mmap_fname)\n        t = time.time() - t0\n        mmap_times[package].append(t)\n        del df","metadata":{"execution":{"iopub.status.busy":"2024-02-08T07:52:02.253504Z","iopub.execute_input":"2024-02-08T07:52:02.254999Z","iopub.status.idle":"2024-02-08T07:52:02.352353Z","shell.execute_reply.started":"2024-02-08T07:52:02.254898Z","shell.execute_reply":"2024-02-08T07:52:02.351074Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As can be seen below, the measured timings are much smaller than even those for Parquet with `datatable` memory mapping being almost immediate. Note, here we have only measured timing for five files.","metadata":{}},{"cell_type":"code","source":"for package, t in mmap_times.items():\n    print(f\"{package:10}: {sum(t):6.2f} [s]\")","metadata":{"execution":{"iopub.status.busy":"2024-02-08T07:52:04.46185Z","iopub.execute_input":"2024-02-08T07:52:04.462544Z","iopub.status.idle":"2024-02-08T07:52:04.470115Z","shell.execute_reply.started":"2024-02-08T07:52:04.462473Z","shell.execute_reply":"2024-02-08T07:52:04.468556Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"When comparing file-wise, one can see that `datatable`'s performance it almost file independent, while `polars` timings change quite a lot.","metadata":{}},{"cell_type":"code","source":"for i, t in enumerate(mmap_times.values()):\n    plt.bar(np.arange(NCOMPARE) + (i-0.5)*0.2, t, width=0.2)\n    \nxlabels = [f.split(\"/\")[-1][:-4] for f in csv_files[:NCOMPARE]]\nplt.xticks(range(NCOMPARE), xlabels, rotation=20)\n    \nplt.legend(mmap_times.keys())\nplt.ylabel(\"Seconds\")\nplt.title(\"Time to load data through memory mapping\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-08T07:52:05.96205Z","iopub.execute_input":"2024-02-08T07:52:05.962588Z","iopub.status.idle":"2024-02-08T07:52:06.316191Z","shell.execute_reply.started":"2024-02-08T07:52:05.962544Z","shell.execute_reply":"2024-02-08T07:52:06.315044Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Conclusion\n\nTo sum up, CSV is the slowest way to get the data, reading Parquet is about twice faster and memory mapping could be almost instant. The benchmarking results are also summarized in the table below. Note, we are not using actual numbers in this table, becuase those could vary on different systems from run to run.\n\n|               | pandas | polars | datatable |\n|---------------|--------|--------|-----------|\n| CSV           | Slow   | Fast   | Fast      | \n| Parquet       | OK     | Fast   | N/A       |\n| Memory mapping| N/A    | OK     | Fast      |\n\n<br>\nHope it helps and good luck with this awesome competition!","metadata":{}}]}