{"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 the balance 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-07-02T08:38:16.022627Z","iopub.execute_input":"2022-07-02T08:38:16.023453Z","iopub.status.idle":"2022-07-02T08:38:16.056633Z","shell.execute_reply.started":"2022-07-02T08:38:16.023334Z","shell.execute_reply":"2022-07-02T08:38:16.055819Z"},"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-07-02T08:41:54.048148Z","iopub.execute_input":"2022-07-02T08:41:54.048584Z","iopub.status.idle":"2022-07-02T08:41:54.110639Z","shell.execute_reply.started":"2022-07-02T08:41:54.048549Z","shell.execute_reply":"2022-07-02T08:41:54.109579Z"},"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-07-02T08:42:04.354222Z","iopub.execute_input":"2022-07-02T08:42:04.354767Z","iopub.status.idle":"2022-07-02T08:42:04.366462Z","shell.execute_reply.started":"2022-07-02T08:42:04.354718Z","shell.execute_reply":"2022-07-02T08:42:04.365174Z"},"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-07-02T08:43:47.870848Z","iopub.execute_input":"2022-07-02T08:43:47.871309Z","iopub.status.idle":"2022-07-02T08:43:47.877831Z","shell.execute_reply.started":"2022-07-02T08:43:47.871254Z","shell.execute_reply":"2022-07-02T08:43:47.877138Z"},"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-07-02T08:43:55.978694Z","iopub.execute_input":"2022-07-02T08:43:55.979105Z","iopub.status.idle":"2022-07-02T08:43:55.986818Z","shell.execute_reply.started":"2022-07-02T08:43:55.979071Z","shell.execute_reply":"2022-07-02T08:43:55.985903Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Chek for payment_cols","metadata":{}},{"cell_type":"code","source":"balance_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 balance_cols_indices]]","metadata":{"execution":{"iopub.status.busy":"2022-07-02T08:44:50.716639Z","iopub.execute_input":"2022-07-02T08:44:50.717044Z","iopub.status.idle":"2022-07-02T08:44:50.745614Z","shell.execute_reply.started":"2022-07-02T08:44:50.717012Z","shell.execute_reply":"2022-07-02T08:44:50.744652Z"},"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-07-02T08:45:01.590334Z","iopub.execute_input":"2022-07-02T08:45:01.590713Z","iopub.status.idle":"2022-07-02T08:45:01.601311Z","shell.execute_reply.started":"2022-07-02T08:45:01.590684Z","shell.execute_reply":"2022-07-02T08:45:01.600328Z"},"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-07-02T08:45:07.652311Z","iopub.execute_input":"2022-07-02T08:45:07.652688Z","iopub.status.idle":"2022-07-02T08:45:07.660118Z","shell.execute_reply.started":"2022-07-02T08:45:07.652659Z","shell.execute_reply":"2022-07-02T08:45:07.658757Z"},"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-07-02T08:45:13.903411Z","iopub.execute_input":"2022-07-02T08:45:13.903801Z","iopub.status.idle":"2022-07-02T08:45:13.911115Z","shell.execute_reply.started":"2022-07-02T08:45:13.903772Z","shell.execute_reply":"2022-07-02T08:45:13.910443Z"},"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-07-02T08:45:24.897037Z","iopub.execute_input":"2022-07-02T08:45:24.897480Z","iopub.status.idle":"2022-07-02T08:45:24.901422Z","shell.execute_reply.started":"2022-07-02T08:45:24.897444Z","shell.execute_reply":"2022-07-02T08:45:24.900636Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Read all rows of balance columns (+ customerID and S_2)","metadata":{}},{"cell_type":"code","source":"cols_indices = identification_cols_indices + balance_cols_indices \ntrain_balance_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_balance_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_balance_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_balance_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_balance_cols = train_balance_cols.set_index('customer_ID')\ntrain_labels = train_labels.set_index('customer_ID')\ntrain_balance_cols = train_balance_cols.join(train_labels, lsuffix='_caller', rsuffix='_other', how='right')\ntrain_balance_cols = train_balance_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_balance_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_balance_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_balance_cols.to_csv(\"/kaggle/working/train_balance_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_balance_cols\ngc.collect()\ncols_indices = identification_cols_indices + balance_cols_indices \ntest_balance_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_balance_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_balance_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_balance_cols.to_csv(\"/kaggle/working/test_balance_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":"### You can do the same things for the other types of columns or you can read another subset of columns. see [this notebook](https://www.kaggle.com/code/amineteffal/read-all-rows-but-just-a-subset-of-columns)","metadata":{}}]}