{"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":"## Read all rows of train and test data but just a subset of columns. This way we can train a model using all rows !\n## all generated files contains customer_ID and S_2 (date) which uniquely identify a row","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-30T18:32:04.7251Z","iopub.execute_input":"2022-06-30T18:32:04.727374Z","iopub.status.idle":"2022-06-30T18:32:04.758728Z","shell.execute_reply.started":"2022-06-30T18:32:04.725474Z","shell.execute_reply":"2022-06-30T18:32:04.757965Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Let's first get the columns of train in a list. For that we will just read 1 line !","metadata":{}},{"cell_type":"code","source":"train_1_line = pd.read_csv(\"/kaggle/input/amex-default-prediction/train_data.csv\", nrows = 1)\ntrain_1_line","metadata":{"execution":{"iopub.status.busy":"2022-06-30T18:32:10.718109Z","iopub.execute_input":"2022-06-30T18:32:10.718493Z","iopub.status.idle":"2022-06-30T18:32:10.781689Z","shell.execute_reply.started":"2022-06-30T18:32:10.718463Z","shell.execute_reply":"2022-06-30T18:32:10.781042Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Store all columns and : \n* D_* = Delinquency variables\n* S_* = Spend variables\n* P_* = Payment variables\n* B_* = Balance variables\n* R_* = Risk variables\n\n### in different lists","metadata":{"execution":{"iopub.status.busy":"2022-06-30T09:25:30.853177Z","iopub.execute_input":"2022-06-30T09:25:30.853637Z","iopub.status.idle":"2022-06-30T09:25:30.879246Z","shell.execute_reply.started":"2022-06-30T09:25:30.853598Z","shell.execute_reply":"2022-06-30T09:25:30.878104Z"}}},{"cell_type":"code","source":"# All columns\nall_cols = list(train_1_line.columns)\n\n# Delinquency variables\ndelinquency_cols = [c for c in all_cols if c[0:2] == 'D_' ]\n\n\n# Spend variables\nspend_cols = [c for c in all_cols if c[0:2] == 'S_' ]\nspend_cols.remove('S_2') # remove S_2 which is a date\n\n# Payment variables\npayment_cols = [c for c in all_cols if c[0:2] == 'P_' ]\n\n# Balance variables\nbalance_cols = [c for c in all_cols if c[0:2] == 'B_' ]\n\n# Risk variables\nrisk_cols = [c for c in all_cols if c[0:2] == 'R_' ]\n\n# customerID and S_2\nidentification_cols = ['customer_ID', 'S_2']","metadata":{"execution":{"iopub.status.busy":"2022-06-30T10:04:29.640705Z","iopub.execute_input":"2022-06-30T10:04:29.641729Z","iopub.status.idle":"2022-06-30T10:04:29.651272Z","shell.execute_reply.started":"2022-06-30T10:04:29.641668Z","shell.execute_reply":"2022-06-30T10:04:29.650535Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Check : total len of all lists must equals len of all_cols","metadata":{}},{"cell_type":"code","source":"len( identification_cols +  delinquency_cols + spend_cols + payment_cols + balance_cols + risk_cols) == len(all_cols)","metadata":{"execution":{"iopub.status.busy":"2022-06-30T10:04:33.124626Z","iopub.execute_input":"2022-06-30T10:04:33.125322Z","iopub.status.idle":"2022-06-30T10:04:33.13287Z","shell.execute_reply.started":"2022-06-30T10:04:33.12527Z","shell.execute_reply":"2022-06-30T10:04:33.131944Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### We will also need the indices of theses columns","metadata":{}},{"cell_type":"code","source":"# Delinquency variables\ndelinquency_cols_indices = [all_cols.index(c) for c in delinquency_cols]\n\n\n# Spend variables\nspend_cols_indices = [all_cols.index(c) for c in spend_cols]\n\n# Payment variables\npayment_cols_indices = [all_cols.index(c) for c in payment_cols]\n\n# Balance variables\nbalance_cols_indices = [all_cols.index(c) for c in balance_cols]\n\n# Risk variables\nrisk_cols_indices = [all_cols.index(c) for c in delinquency_cols]\n\n# Identification cols\nidentification_cols_indices = [all_cols.index(c) for c in identification_cols]","metadata":{"execution":{"iopub.status.busy":"2022-06-30T10:07:53.568268Z","iopub.execute_input":"2022-06-30T10:07:53.568732Z","iopub.status.idle":"2022-06-30T10:07:53.576742Z","shell.execute_reply.started":"2022-06-30T10:07:53.568693Z","shell.execute_reply":"2022-06-30T10:07:53.575687Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Chek for payment_cols","metadata":{}},{"cell_type":"code","source":"payment_cols","metadata":{"execution":{"iopub.status.busy":"2022-06-30T10:05:24.72233Z","iopub.execute_input":"2022-06-30T10:05:24.723274Z","iopub.status.idle":"2022-06-30T10:05:24.729429Z","shell.execute_reply.started":"2022-06-30T10:05:24.723236Z","shell.execute_reply":"2022-06-30T10:05:24.728722Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_1_line[identification_cols + [all_cols[i] for i in payment_cols_indices]]","metadata":{"execution":{"iopub.status.busy":"2022-06-30T10:05:55.268157Z","iopub.execute_input":"2022-06-30T10:05:55.268581Z","iopub.status.idle":"2022-06-30T10:05:55.283713Z","shell.execute_reply.started":"2022-06-30T10:05:55.268548Z","shell.execute_reply":"2022-06-30T10:05:55.282919Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Let's create functions for reading all rows of train but only a subset of columns\n### We will need the number of rows of each file but I have already calculated them in [that notebook](https://www.kaggle.com/code/amineteffal/group-split-data-by-customer)\n### This numbers are used for iterating on the original files","metadata":{}},{"cell_type":"code","source":"train_data_rows_count = 5531452 # including header\ntest_data_rows_count =  11363763 # including header\nn_cols = 190","metadata":{"execution":{"iopub.status.busy":"2022-06-30T10:12:53.856786Z","iopub.execute_input":"2022-06-30T10:12:53.85785Z","iopub.status.idle":"2022-06-30T10:12:53.8624Z","shell.execute_reply.started":"2022-06-30T10:12:53.857806Z","shell.execute_reply":"2022-06-30T10:12:53.861714Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Create a function that read a chunk of the file but only a subset of culumns","metadata":{}},{"cell_type":"code","source":"def read_a_chunk_cols(csv_file, chunk_size, chunk_order, cols_indices) :\n    '''\n        Read the chunk_order chunk from csv_file,\n        take only columns passed as list of indices of those columns \n        in cols_indices. \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    cols = chunk_data.columns\n        \n    return chunk_data[[cols[i] for i in cols_indices]]","metadata":{"execution":{"iopub.status.busy":"2022-06-30T10:13:04.533877Z","iopub.execute_input":"2022-06-30T10:13:04.534539Z","iopub.status.idle":"2022-06-30T10:13:04.542056Z","shell.execute_reply.started":"2022-06-30T10:13:04.534489Z","shell.execute_reply":"2022-06-30T10:13:04.541236Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def read_all_chunk_cols (csv_file, chunk_size, cols_indices, n_rows):\n    \n    '''\n        Read all the rows of csv_file each chunk at time,\n        take only columns passed as list of indices of those columns \n        in cols_indices. \n        The chunk to read each time is of size chunk_size\n    \n    '''\n    \n    # read first chunck of cols\n    chuncks_cols = read_a_chunk_cols(csv_file, chunk_size, 0, cols_indices)\n    \n    # read the following chuncks\n    for i in range(1, int(n_rows/chunk_size) + 1) :\n        # read a chunk\n        chuncks_cols_temp = read_a_chunk_cols(csv_file, chunk_size, i, cols_indices)\n        \n        # concatenate with chunks_cols\n        chuncks_cols = pd.concat([chuncks_cols, chuncks_cols_temp])\n    \n        # free memory and call garbage collector\n        del chuncks_cols_temp\n        gc.collect()\n    \n    return chuncks_cols","metadata":{"execution":{"iopub.status.busy":"2022-06-30T10:14:44.946094Z","iopub.execute_input":"2022-06-30T10:14:44.946997Z","iopub.status.idle":"2022-06-30T10:14:44.955509Z","shell.execute_reply.started":"2022-06-30T10:14:44.946943Z","shell.execute_reply":"2022-06-30T10:14:44.954726Z"},"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-30T10:14:57.414629Z","iopub.execute_input":"2022-06-30T10:14:57.415372Z","iopub.status.idle":"2022-06-30T10:14:57.420486Z","shell.execute_reply.started":"2022-06-30T10:14:57.415323Z","shell.execute_reply":"2022-06-30T10:14:57.419741Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Test the functions on Train data : Read the 10 first  columns (+ customerID and S_2)","metadata":{}},{"cell_type":"code","source":"n_cols_to_read = 10\ncols_indices = identification_cols_indices + list(range( 2, n_cols_to_read + 2)) # + 2 because of customer_ID and S_2\ntrain_10_first_cols = read_all_chunk_cols(\"/kaggle/input/amex-default-prediction/train_data.csv\", chunk_size, cols_indices, train_data_rows_count)","metadata":{"execution":{"iopub.status.busy":"2022-06-30T11:43:39.080438Z","iopub.execute_input":"2022-06-30T11:43:39.081259Z","iopub.status.idle":"2022-06-30T11:58:23.721568Z","shell.execute_reply.started":"2022-06-30T11:43:39.081209Z","shell.execute_reply":"2022-06-30T11:58:23.705382Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_10_first_cols.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-06-30T12:31:48.634845Z","iopub.execute_input":"2022-06-30T12:31:48.635663Z","iopub.status.idle":"2022-06-30T12:31:48.660282Z","shell.execute_reply.started":"2022-06-30T12:31:48.635605Z","shell.execute_reply":"2022-06-30T12:31:48.659354Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### check rows count","metadata":{}},{"cell_type":"code","source":"train_10_first_cols.shape[0] == train_data_rows_count - 1","metadata":{"execution":{"iopub.status.busy":"2022-06-30T11:05:58.884727Z","iopub.execute_input":"2022-06-30T11:05:58.88576Z","iopub.status.idle":"2022-06-30T11:05:58.893068Z","shell.execute_reply.started":"2022-06-30T11:05:58.885708Z","shell.execute_reply":"2022-06-30T11:05:58.89207Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Read all rows of spend columns (+ customerID and S_2)","metadata":{}},{"cell_type":"code","source":"# free memory\ndel train_10_first_cols\ngc.collect()\ncols_indices = identification_cols_indices + spend_cols_indices \ntrain_spend_cols = read_all_chunk_cols(\"/kaggle/input/amex-default-prediction/train_data.csv\", chunk_size, cols_indices, train_data_rows_count)\n","metadata":{"execution":{"iopub.status.busy":"2022-06-30T13:08:22.938245Z","iopub.execute_input":"2022-06-30T13:08:22.939179Z","iopub.status.idle":"2022-06-30T13:24:16.986849Z","shell.execute_reply.started":"2022-06-30T13:08:22.93914Z","shell.execute_reply":"2022-06-30T13:24:16.977856Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_spend_cols.info()","metadata":{"execution":{"iopub.status.busy":"2022-06-30T13:35:04.752965Z","iopub.execute_input":"2022-06-30T13:35:04.753374Z","iopub.status.idle":"2022-06-30T13:35:04.807087Z","shell.execute_reply.started":"2022-06-30T13:35:04.753342Z","shell.execute_reply":"2022-06-30T13:35:04.806218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_spend_cols.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-06-30T13:29:35.91622Z","iopub.execute_input":"2022-06-30T13:29:35.916799Z","iopub.status.idle":"2022-06-30T13:29:35.973006Z","shell.execute_reply.started":"2022-06-30T13:29:35.916757Z","shell.execute_reply":"2022-06-30T13:29:35.97201Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### check rows count","metadata":{}},{"cell_type":"code","source":"train_spend_cols.shape[0] == train_data_rows_count - 1","metadata":{"execution":{"iopub.status.busy":"2022-06-30T13:29:50.29389Z","iopub.execute_input":"2022-06-30T13:29:50.294329Z","iopub.status.idle":"2022-06-30T13:29:50.299669Z","shell.execute_reply.started":"2022-06-30T13:29:50.294292Z","shell.execute_reply":"2022-06-30T13:29:50.299026Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Get labels","metadata":{}},{"cell_type":"code","source":"train_labels = pd.read_csv(\"/kaggle/input/amex-default-prediction/train_labels.csv\")\ntrain_spend_cols = train_spend_cols.set_index('customer_ID')\ntrain_labels = train_labels.set_index('customer_ID')\ntrain_spend_cols = train_spend_cols.join(train_labels, lsuffix='_caller', rsuffix='_other', how='right')\ntrain_spend_cols = train_spend_cols.reset_index()","metadata":{"execution":{"iopub.status.busy":"2022-06-30T13:46:19.900877Z","iopub.execute_input":"2022-06-30T13:46:19.901372Z","iopub.status.idle":"2022-06-30T13:46:30.900984Z","shell.execute_reply.started":"2022-06-30T13:46:19.901336Z","shell.execute_reply":"2022-06-30T13:46:30.898629Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_spend_cols.head()","metadata":{"execution":{"iopub.status.busy":"2022-06-30T13:54:53.926041Z","iopub.execute_input":"2022-06-30T13:54:53.927174Z","iopub.status.idle":"2022-06-30T13:54:53.956944Z","shell.execute_reply.started":"2022-06-30T13:54:53.927118Z","shell.execute_reply":"2022-06-30T13:54:53.956018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Check rows count","metadata":{}},{"cell_type":"code","source":"train_spend_cols.shape[0] == train_data_rows_count - 1","metadata":{"execution":{"iopub.status.busy":"2022-06-30T13:48:49.11979Z","iopub.execute_input":"2022-06-30T13:48:49.120893Z","iopub.status.idle":"2022-06-30T13:48:49.127547Z","shell.execute_reply.started":"2022-06-30T13:48:49.120843Z","shell.execute_reply":"2022-06-30T13:48:49.126541Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Save to csv\ntrain_spend_cols.to_csv(\"/kaggle/working/train_spend_cols.csv\", index = False)","metadata":{"execution":{"iopub.status.busy":"2022-06-30T13:56:33.896766Z","iopub.execute_input":"2022-06-30T13:56:33.897939Z","iopub.status.idle":"2022-06-30T14:00:28.202964Z","shell.execute_reply.started":"2022-06-30T13:56:33.897892Z","shell.execute_reply":"2022-06-30T14:00:28.201843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Do same thing for test data","metadata":{}},{"cell_type":"code","source":"# free memory\ndel train_spend_cols\ngc.collect()\ncols_indices = identification_cols_indices + spend_cols_indices \ntest_spend_cols = read_all_chunk_cols(\"/kaggle/input/amex-default-prediction/test_data.csv\", chunk_size, cols_indices, test_data_rows_count)\n","metadata":{"execution":{"iopub.status.busy":"2022-06-30T14:04:25.748541Z","iopub.execute_input":"2022-06-30T14:04:25.749689Z","iopub.status.idle":"2022-06-30T14:22:05.966732Z","shell.execute_reply.started":"2022-06-30T14:04:25.749627Z","shell.execute_reply":"2022-06-30T14:22:05.958667Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_spend_cols.head()","metadata":{"execution":{"iopub.status.busy":"2022-06-30T14:23:06.106869Z","iopub.execute_input":"2022-06-30T14:23:06.107691Z","iopub.status.idle":"2022-06-30T14:23:06.165349Z","shell.execute_reply.started":"2022-06-30T14:23:06.107626Z","shell.execute_reply":"2022-06-30T14:23:06.164413Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### check row count","metadata":{}},{"cell_type":"code","source":"test_spend_cols.shape[0] == test_data_rows_count - 1","metadata":{"execution":{"iopub.status.busy":"2022-06-30T14:23:38.662674Z","iopub.execute_input":"2022-06-30T14:23:38.663114Z","iopub.status.idle":"2022-06-30T14:23:38.670192Z","shell.execute_reply.started":"2022-06-30T14:23:38.663072Z","shell.execute_reply":"2022-06-30T14:23:38.669056Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_spend_cols.to_csv(\"/kaggle/working/test_spend_cols.csv\", index = False)","metadata":{"execution":{"iopub.status.busy":"2022-06-30T14:27:11.232266Z","iopub.execute_input":"2022-06-30T14:27:11.233061Z","iopub.status.idle":"2022-06-30T14:31:46.616633Z","shell.execute_reply.started":"2022-06-30T14:27:11.23302Z","shell.execute_reply":"2022-06-30T14:31:46.615653Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### If you want to split all rows of train data into several smaller (subset of columns) files and store them as ouput, uncomment this code !","metadata":{}},{"cell_type":"code","source":"# # clean memory\n# del train_spend_cols\n# del test_spend_cols\n# gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-30T14:40:10.328208Z","iopub.execute_input":"2022-06-30T14:40:10.328696Z","iopub.status.idle":"2022-06-30T14:40:10.6901Z","shell.execute_reply.started":"2022-06-30T14:40:10.328657Z","shell.execute_reply":"2022-06-30T14:40:10.689259Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# n_cols_to_read = 20 # number of columns to read at each iteration\n# n_chunks = int(n_cols/n_cols_to_read)\n\n# for i in range(n_chunks + 1):\n#     start_col = i*n_cols_to_read + 2  # + 2 because of customer_ID and S_2\n#     end_col = min(n_cols, (i+1)*n_cols_to_read + 2) # we can't have indice bigger than n_cols\n#     if start_col < n_cols:\n#         # customer_ID and S_2 columns will be always present --> [0, 1]\n#         cols_indices = identification_cols_indices + list(range(start_col, end_col)) \n#         chunk = read_all_chunk_cols(\"/kaggle/input/amex-default-prediction/train_data.csv\", chunk_size, cols_indices, train_data_rows_count)\n#         chunk.to_csv(\"/kaggle/working/train_\" + str(i) + \".csv\", index=False)\n#         del chunk\n#         gc.collect()\n    ","metadata":{"execution":{"iopub.status.busy":"2022-06-30T14:43:03.045322Z","iopub.execute_input":"2022-06-30T14:43:03.045831Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## We can do the same thing for test data but unfortunately we can't store them as the limit of the output folder is 20 GB ! \n## So I commented the code.\n## We can solve this by running this code in different notebooks (changing each time the range of the i variable) to generate all the test data and then import them in a another notebook (plus the splitted rain data) in order to train different models and then aggregate the predictions to get one predictiion.","metadata":{}},{"cell_type":"code","source":"# n_cols_to_read = 10 # as test twice huge, read by 10 cols\n# n_chunks = int(n_cols/n_cols_to_read)\n\n# for i in range(n_chunks + 1):\n#     start_col = i*n_cols_to_read + 2  # + 2 because of customer_ID and S_2\n#     end_col = min(n_cols, (i+1)*n_cols_to_read + 2) # we can't have indice bigger than n_cols\n#     if start_col < n_cols:\n#         # customer_ID and S_2 columns will be always present --> [0, 1]\n#         cols_indices = identification_cols_indices + list(range(start_col, end_col)) \n#         chunk = read_all_chunk_cols(\"/kaggle/input/amex-default-prediction/test_data.csv\", chunk_size, cols_indices, test_data_rows_count)\n#         chunk.to_csv(\"/kaggle/working/train_\" + str(i) + \".csv\", index=False)\n#         del chunk\n#         gc.collect()","metadata":{},"execution_count":null,"outputs":[]}]}