{"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":"### This notebook aims to group data by customer in order to have smaller files.\n### The group function used is the min, mean and max (numeric variables only).","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-06-14T14:31:34.312482Z","iopub.execute_input":"2022-06-14T14:31:34.312968Z","iopub.status.idle":"2022-06-14T14:31:34.346905Z","shell.execute_reply.started":"2022-06-14T14:31:34.312875Z","shell.execute_reply":"2022-06-14T14:31:34.346146Z"}}},{"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport os\nimport gc # garbage collector to free memory","metadata":{"execution":{"iopub.status.busy":"2022-06-27T13:58:18.199408Z","iopub.execute_input":"2022-06-27T13:58:18.200038Z","iopub.status.idle":"2022-06-27T13:58:18.225123Z","shell.execute_reply.started":"2022-06-27T13:58:18.199934Z","shell.execute_reply":"2022-06-27T13:58:18.224569Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### As the files are huge, let's first count the number of their lines","metadata":{}},{"cell_type":"code","source":"###################### code adapted from : https://www.geeksforgeeks.org/how-to-count-the-number-of-lines-in-a-csv-file-in-python/ ###############\ndef count_rows(csv_file):\n    #Setting initial value of the counter to zero\n    rowcount  = 0\n    \n    #iterating through the whole file\n    for row in open(csv_file):\n        rowcount += 1\n    \n    return rowcount\n    ","metadata":{"execution":{"iopub.status.busy":"2022-06-27T13:58:18.243023Z","iopub.execute_input":"2022-06-27T13:58:18.243469Z","iopub.status.idle":"2022-06-27T13:58:18.247353Z","shell.execute_reply.started":"2022-06-27T13:58:18.243436Z","shell.execute_reply":"2022-06-27T13:58:18.246833Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_data_rows_count = count_rows(\"/kaggle/input/amex-default-prediction/train_data.csv\")\ntest_data_rows_count = count_rows(\"/kaggle/input/amex-default-prediction/test_data.csv\")\ntrain_labels_rows_count = count_rows(\"/kaggle/input/amex-default-prediction/train_labels.csv\")\nsample_submission_rows_count = count_rows(\"/kaggle/input/amex-default-prediction/sample_submission.csv\")\nprint(\"train_data_rows_count : \", train_data_rows_count)\nprint(\"test_data_rows_count : \", test_data_rows_count)\nprint(\"train_labels_rows_count : \", train_labels_rows_count)\nprint(\"sample_submission_rows_count : \", sample_submission_rows_count)","metadata":{"execution":{"iopub.status.busy":"2022-06-27T13:58:18.248352Z","iopub.execute_input":"2022-06-27T13:58:18.24884Z","iopub.status.idle":"2022-06-27T14:09:09.558363Z","shell.execute_reply.started":"2022-06-27T13:58:18.248813Z","shell.execute_reply":"2022-06-27T14:09:09.557708Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Create a function that read a chunk of the file and aggregate it by customer_ID (keeping numeric columns only)","metadata":{}},{"cell_type":"code","source":"def read_a_chunk(csv_file, chunk_size, chunk_order) :\n    '''\n        Read the the chunk_order chunk from csv_file,\n        group columns by customer_ID and return a dataframe. \n        The chunk to read is of size chunk_size\n    \n    '''\n    \n    chunk_data = pd.read_csv(csv_file, skiprows = range(1,chunk_order * chunk_size + 1),nrows=chunk_size)\n    \n    # Drop str columns except customer_ID\n    chunk_data = chunk_data.drop(['S_2', 'D_63', 'D_64'], axis = 1)\n    \n    #Fill na's with mean of corresponding column\n    chunk_data = chunk_data.fillna(chunk_data.drop('customer_ID', axis=1).mean())\n    \n    # group by customer_ID\n    chunk_data = pd.DataFrame(chunk_data.groupby(by='customer_ID').agg([np.nanmin, np.nanmean, np.nanmax, np.nanstd])).reset_index()\n    \n    # rename columns\n    chunk_data.columns = [c[0]+c[1] for c in chunk_data]\n    \n    return chunk_data","metadata":{"execution":{"iopub.status.busy":"2022-06-27T14:09:09.559769Z","iopub.execute_input":"2022-06-27T14:09:09.56042Z","iopub.status.idle":"2022-06-27T14:09:09.567931Z","shell.execute_reply.started":"2022-06-27T14:09:09.560379Z","shell.execute_reply":"2022-06-27T14:09:09.567158Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Define chunk size","metadata":{}},{"cell_type":"code","source":"chunk_size = 1000000 ","metadata":{"execution":{"iopub.status.busy":"2022-06-27T14:09:09.569373Z","iopub.execute_input":"2022-06-27T14:09:09.569611Z","iopub.status.idle":"2022-06-27T14:09:09.577894Z","shell.execute_reply.started":"2022-06-27T14:09:09.56959Z","shell.execute_reply":"2022-06-27T14:09:09.577191Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Train data","metadata":{}},{"cell_type":"code","source":"# read first chunk\ntrain_data_groupped = read_a_chunk(\"/kaggle/input/amex-default-prediction/train_data.csv\", chunk_size, 0)\n\nfor i in range(1, int(train_data_rows_count/chunk_size) + 1) :\n    chunk_temp = read_a_chunk(\"/kaggle/input/amex-default-prediction/train_data.csv\", chunk_size, i)\n    train_data_groupped = pd.concat([train_data_groupped, chunk_temp])\n    del chunk_temp\n    gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-27T14:09:09.578838Z","iopub.execute_input":"2022-06-27T14:09:09.579391Z","iopub.status.idle":"2022-06-27T14:20:04.769906Z","shell.execute_reply.started":"2022-06-27T14:09:09.57936Z","shell.execute_reply":"2022-06-27T14:20:04.759651Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('train_data_groupped info : ', train_data_groupped.info())\nprint('shape of train_data_groupped : ', train_data_groupped.shape)\ntrain_data_groupped.to_csv(\"/kaggle/working/train_data_groupped.csv\", index=False)\ndel train_data_groupped\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-27T14:20:04.779413Z","iopub.execute_input":"2022-06-27T14:20:04.779928Z","iopub.status.idle":"2022-06-27T14:25:34.919725Z","shell.execute_reply.started":"2022-06-27T14:20:04.779885Z","shell.execute_reply":"2022-06-27T14:25:34.918905Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Do same thing for test data","metadata":{}},{"cell_type":"code","source":"# read first chunk\ntest_data_groupped = read_a_chunk(\"/kaggle/input/amex-default-prediction/test_data.csv\", chunk_size, 0)\n\nfor i in range(1, int(test_data_rows_count/chunk_size) + 1) :\n    chunk_temp = read_a_chunk(\"/kaggle/input/amex-default-prediction/test_data.csv\", chunk_size, i)\n    test_data_groupped = pd.concat([test_data_groupped, chunk_temp])\n    del chunk_temp\n    gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-27T14:25:35.075193Z","iopub.status.idle":"2022-06-27T14:25:35.075508Z","shell.execute_reply.started":"2022-06-27T14:25:35.075363Z","shell.execute_reply":"2022-06-27T14:25:35.075378Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('test_data_groupped info : ', test_data_groupped.info())\nprint('shape of test_data_groupped : ', test_data_groupped.shape)\ntest_data_groupped.to_csv(\"/kaggle/working/test_data_groupped.csv\", index=False)\ndel test_data_groupped\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-27T14:27:21.054047Z","iopub.execute_input":"2022-06-27T14:27:21.054465Z","iopub.status.idle":"2022-06-27T14:27:21.067179Z","shell.execute_reply.started":"2022-06-27T14:27:21.054425Z","shell.execute_reply":"2022-06-27T14:27:21.066369Z"},"trusted":true},"execution_count":null,"outputs":[]}]}