{"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":"# Converting CSV files to Parquet\n\nAim of the notebook is to understand what methods help to reduce memory with the CSV files. Leveraging the great article on [Converting CSVs to Parquet](https://www.confessionsofadataguy.com/converting-csvs-to-parquets-with-python-and-scala/) to understand what can be acheived.","metadata":{}},{"cell_type":"markdown","source":"Using the correct data type for each feature is a key challenge when working with large datasets. Great work by [SRK within the discussion](https://www.kaggle.com/competitions/amex-default-prediction/discussion/327205) who has shared a dictionary for the feature data types. Having this initialised should greatly improve the data processing. By default a pandas dataframe will take each float as float64 data type unless the method has be advised otherwise.","metadata":{}},{"cell_type":"code","source":"# Feature data type dictionary.\n# 1. Reviewed the list of categorical features from the data tab ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']\n#    to understand if they should be adjusted to data type = 'category'\ndtype_dict = {\n    'customer_id':\"object\",'S_2':\"object\",'S_3':'float16','S_5':'float16','S_6':'float16','S_7':'float16','S_8':'float16'\n    ,'S_9':'float16','S_11':'float16','S_12':'float16','S_13':'float16','S_15':'float16','S_16':'float16','S_17':'float16'\n    ,'S_18':'float16','S_19':'float16','S_20':'float16','S_22':'float16','S_23':'float16','S_24':'float16','S_25':'float16'\n    ,'S_26':'float16','S_27':'float16'\n    ,'P_2':'float16','P_3':'float16','P_4':'float16'\n    ,'R_1':'float16','R_2':'float16','R_3':'float16','R_4':'float16','R_5':'float16','R_6':'float16','R_7':'float16'\n    ,'R_8':'float16','R_9':'float16','R_10':'float16','R_11':'float16','R_12':'float16','R_13':'float16','R_14':'float16'\n    ,'R_15':'float16','R_16':'float16','R_17':'float16','R_18':'float16','R_19':'float16','R_20':'float16','R_21':'float16'\n    ,'R_22':'float16','R_23':'float16','R_24':'float16','R_25':'float16','R_26':'float16','R_27':'float16','R_28':'float16'\n    ,'B_1':'float16','B_2':'float16','B_3':'float16','B_4':'float16','B_5':'float16','B_6':'float16','B_7':'float16'\n    ,'B_8':'float16','B_9':'float16','B_10':'float16','B_11':'float16','B_12':'float16','B_13':'float16','B_14':'float16'\n    ,'B_15':'float16','B_16':'float16','B_17':'float16','B_18':'float16','B_19':'float16','B_20':'float16','B_21':'float16'\n    ,'B_22':'float16','B_23':'float16','B_24':'float16','B_25':'float16','B_26':'float16','B_27':'float16','B_28':'float16'\n    ,'B_29':'float16','B_30':'float16','B_31':'int64','B_32':'float16','B_33':'float16','B_36':'float16','B_37':'float16'\n    ,'B_38':'float16','B_39':'float16','B_40':'float16','B_41':'float16','B_42':'float16'\n    ,'D_39':'float16','D_41':'float16','D_42':'float16','D_43':'float16','D_44':'float16','D_45':'float16','D_46':'float16'\n    ,'D_47':'float16','D_48':'float16','D_49':'float16','D_50':'float16','D_51':'float16','D_52':'float16','D_53':'float16'\n    ,'D_54':'float16','D_55':'float16','D_56':'float16','D_58':'float16','D_59':'float16','D_60':'float16','D_61':'float16'\n    ,'D_62':'float16','D_63':'object','D_64':'object','D_65':'float16','D_66':'float16','D_68':'float16','D_69':'float16'\n    ,'D_70':'float16','D_71':'float16','D_72':'float16','D_73':'float16','D_74':'float16','D_75':'float16','D_76':'float16'\n    ,'D_77':'float16','D_78':'float16','D_79':'float16','D_80':'float16','D_81':'float16','D_82':'float16','D_83':'float16'\n    ,'D_84':'float16','D_86':'float16','D_87':'float16','D_88':'float16','D_89':'float16','D_91':'float16','D_92':'float16'\n    ,'D_93':'float16','D_94':'float16','D_96':'float16','D_102':'float16','D_103':'float16','D_104':'float16','D_105':'float16'\n    ,'D_106':'float16','D_107':'float16','D_108':'float16','D_109':'float16','D_110':'float16','D_111':'float16','D_112':'float16'\n    ,'D_113':'float16','D_114':'float16','D_115':'float16','D_116':'float16','D_117':'float16','D_118':'float16','D_119':'float16'\n    ,'D_120':'float16','D_121':'float16','D_122':'float16','D_123':'float16','D_124':'float16','D_125':'float16','D_126':'float16'\n    ,'D_127':'float16','D_128':'float16','D_129':'float16','D_130':'float16','D_131':'float16','D_132':'float16','D_133':'float16'\n    ,'D_134':'float16','D_135':'float16','D_136':'float16','D_137':'float16','D_138':'float16','D_139':'float16','D_140':'float16'\n    ,'D_141':'float16','D_142':'float16','D_143':'float16','D_144':'float16','D_145':'float16'\n}","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-05-29T09:46:08.632053Z","iopub.execute_input":"2022-05-29T09:46:08.633015Z","iopub.status.idle":"2022-05-29T09:46:08.671859Z","shell.execute_reply.started":"2022-05-29T09:46:08.632887Z","shell.execute_reply":"2022-05-29T09:46:08.670556Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Import packages\nimport numpy as np\nimport pandas as pd\nfrom glob import glob\nfrom datetime import datetime\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-05-29T09:46:12.991419Z","iopub.execute_input":"2022-05-29T09:46:12.991798Z","iopub.status.idle":"2022-05-29T09:46:12.999306Z","shell.execute_reply.started":"2022-05-29T09:46:12.991767Z","shell.execute_reply":"2022-05-29T09:46:12.998437Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create a list of the csv file names in the directory\ndef get_local_files() -> list:\n    local_files = glob(\"/kaggle/input/amex-default-prediction/*.csv\")\n    return local_files","metadata":{"execution":{"iopub.status.busy":"2022-05-29T09:46:14.904035Z","iopub.execute_input":"2022-05-29T09:46:14.905180Z","iopub.status.idle":"2022-05-29T09:46:14.908146Z","shell.execute_reply.started":"2022-05-29T09:46:14.905154Z","shell.execute_reply":"2022-05-29T09:46:14.907721Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Run the method\nlocal_files = get_local_files()\nlocal_files","metadata":{"execution":{"iopub.status.busy":"2022-05-29T09:46:17.734013Z","iopub.execute_input":"2022-05-29T09:46:17.734395Z","iopub.status.idle":"2022-05-29T09:46:17.742258Z","shell.execute_reply.started":"2022-05-29T09:46:17.734366Z","shell.execute_reply":"2022-05-29T09:46:17.741512Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Pandas to convert to parquet","metadata":{}},{"cell_type":"code","source":"train = local_files[1]\ntrain","metadata":{"execution":{"iopub.status.busy":"2022-05-29T09:46:20.438129Z","iopub.execute_input":"2022-05-29T09:46:20.438773Z","iopub.status.idle":"2022-05-29T09:46:20.444686Z","shell.execute_reply.started":"2022-05-29T09:46:20.438731Z","shell.execute_reply":"2022-05-29T09:46:20.443948Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Testing to understand if the updated data type dictionary allows for a pandas dataframe to be created in memory. results were good, the dataframe can be created and manipulated with a much reduced memory","metadata":{}},{"cell_type":"code","source":"# Test using the updated data type dictionary with Pandas to understand change. Changes the data types allows the dataframe to be created\n# in memory\n# df = pd.read_csv(train, dtype = dtype_dict)\n# df.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-29T09:47:59.508809Z","iopub.execute_input":"2022-05-29T09:47:59.509168Z","iopub.status.idle":"2022-05-29T09:47:59.515031Z","shell.execute_reply.started":"2022-05-29T09:47:59.509140Z","shell.execute_reply":"2022-05-29T09:47:59.513940Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Displaying the memory footprint for the new dataframe\n# df.info(memory_usage='True')","metadata":{"execution":{"iopub.status.busy":"2022-05-28T10:01:51.386749Z","iopub.execute_input":"2022-05-28T10:01:51.387495Z","iopub.status.idle":"2022-05-28T10:01:51.402749Z","shell.execute_reply.started":"2022-05-28T10:01:51.387447Z","shell.execute_reply":"2022-05-28T10:01:51.401402Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Making use of chunksize we are able to reduce the size of each ","metadata":{}},{"cell_type":"code","source":"# Work with pandas to convert the train data\n# 1. Updated to include chunk size as the file is too large to fit in RAM\n# def file_df_to_parquet(local_file: str, parquet_file: str) -> None:\n#     for i, chunk in enumerate(pd.read_csv(local_file, chunksize=1000000)):\n#         chunk.to_parquet(f\"parquet_file_{i}.parquet\")\n\n# if __name__ == \"__main__\":\n#     t1 = datetime.now()\n#     file_df_to_parquet(train, \"/kaggle/working/train_parquet\")\n#     t2 = datetime.now()\n#     duration = t2 - t1\n#     print(f\"It took {duration} seconds to process\")","metadata":{"execution":{"iopub.status.busy":"2022-05-27T11:19:39.042474Z","iopub.execute_input":"2022-05-27T11:19:39.043432Z","iopub.status.idle":"2022-05-27T11:28:02.620132Z","shell.execute_reply.started":"2022-05-27T11:19:39.043375Z","shell.execute_reply":"2022-05-27T11:28:02.618876Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Count the number of output parquet files using this method\n# def get_output_files() -> list:\n#     local_files = glob(\"/kaggle/working/*.parquet\")\n#     return local_files\n\n# output_files = get_output_files()\n# len(output_files)","metadata":{"execution":{"iopub.status.busy":"2022-05-27T11:31:34.682334Z","iopub.execute_input":"2022-05-27T11:31:34.683172Z","iopub.status.idle":"2022-05-27T11:31:34.691351Z","shell.execute_reply.started":"2022-05-27T11:31:34.683124Z","shell.execute_reply":"2022-05-27T11:31:34.690321Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Understanding the file size of one file\n# from humanize import naturalsize\n# size = os.stat(\"/kaggle/working/parquet_file_42.parquet\").st_size\n# print(size)\n# print(naturalsize(size))","metadata":{"execution":{"iopub.status.busy":"2022-05-27T11:37:28.310583Z","iopub.execute_input":"2022-05-27T11:37:28.310988Z","iopub.status.idle":"2022-05-27T11:37:28.317965Z","shell.execute_reply.started":"2022-05-27T11:37:28.310957Z","shell.execute_reply":"2022-05-27T11:37:28.316967Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Review folder size\n# from pathlib import Path\n\n# def get_size(path: str = '.') -> str:\n#     size = 0\n#     for file_ in Path(path).rglob('*.parquet'):\n#         size += file_.stat().st_size\n#     return naturalsize(size)\n\n# print(get_size('/kaggle/working/'))","metadata":{"execution":{"iopub.status.busy":"2022-05-27T11:42:17.153455Z","iopub.execute_input":"2022-05-27T11:42:17.153942Z","iopub.status.idle":"2022-05-27T11:42:17.165573Z","shell.execute_reply.started":"2022-05-27T11:42:17.153907Z","shell.execute_reply":"2022-05-27T11:42:17.164434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This method was able to produce 56 parquet files. Time taken was 8 mins and 23 seconds. Memory reduced from 16.39GB to 8.4GB","metadata":{}},{"cell_type":"markdown","source":"## Use Dask to convert csv file to parquet\nAs Dask has a similar API to pandas we are able to create data frames using similar keyword arguments","metadata":{}},{"cell_type":"code","source":"# Import packages\n# from dask.dataframe import read_csv","metadata":{"execution":{"iopub.status.busy":"2022-05-28T13:50:21.041819Z","iopub.execute_input":"2022-05-28T13:50:21.042354Z","iopub.status.idle":"2022-05-28T13:50:21.892613Z","shell.execute_reply.started":"2022-05-28T13:50:21.042317Z","shell.execute_reply":"2022-05-28T13:50:21.891516Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create the method for the dataframe storage in parquet format\n# def dask_df_parquet(local_file: str, parquet_file: str) -> None:\n# #     dask_df = read_csv(local_file, dtype = dtype_dict)\n#     dask_df = read_csv(local_file)\n#     dask_df.to_parquet(parquet_file)\n\n# if __name__ == \"__main__\":\n#     t1 = datetime.now()\n#     dask_df_parquet(train, \"/kaggle/working/train_parquet.parquet\")\n#     t2 = datetime.now()\n#     duration = t2 - t1\n#     print(f\"It took {duration} seconds to process\")","metadata":{"execution":{"iopub.status.busy":"2022-05-28T14:28:49.957252Z","iopub.execute_input":"2022-05-28T14:28:49.957654Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}