{"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":"# Days overdue feature in the dataset\n\nThis notebook is dedicated to show evidence that anonymized `D_39` feature is in fact `days_overdue` feature - which is equivalent to the `target` in this competition!","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\ntrain = pd.read_parquet('../input/amex-data-integer-dtypes-parquet-format/train.parquet')","metadata":{"execution":{"iopub.status.busy":"2022-06-22T11:19:28.691914Z","iopub.execute_input":"2022-06-22T11:19:28.692107Z","iopub.status.idle":"2022-06-22T11:19:44.608538Z","shell.execute_reply.started":"2022-06-22T11:19:28.692086Z","shell.execute_reply":"2022-06-22T11:19:44.607482Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Days?\n\nFirst, let's describe visualize the distribution of this feature.","metadata":{}},{"cell_type":"code","source":"# count of 0\nnp.sum(train['D_39']==0)/train.shape[0]","metadata":{"execution":{"iopub.status.busy":"2022-06-22T11:19:44.609719Z","iopub.execute_input":"2022-06-22T11:19:44.609958Z","iopub.status.idle":"2022-06-22T11:19:44.622819Z","shell.execute_reply.started":"2022-06-22T11:19:44.609937Z","shell.execute_reply":"2022-06-22T11:19:44.622050Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As expected, most of the rows are zeroes (meaning no overdue payment days).\n\nLet's plot the distribution (y-axis in log scale):","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(16, 10))\nsns.kdeplot(train['D_39'], log_scale=[False,True])","metadata":{"execution":{"iopub.status.busy":"2022-06-22T11:19:44.625003Z","iopub.execute_input":"2022-06-22T11:19:44.625199Z","iopub.status.idle":"2022-06-22T11:20:11.487402Z","shell.execute_reply.started":"2022-06-22T11:19:44.625179Z","shell.execute_reply":"2022-06-22T11:20:11.486483Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now this is where it gets interesting. The maximum value of `D_39` is 183 (exactly 6months or ~180days). There are a few spikes at 31,61,91,121,151 - which itself signals about different `D_39` calculation logic based on different products (i.e. instalment vs non-instalment payments). However, most importantly, these values do confirm that this is feature related to days!","metadata":{}},{"cell_type":"markdown","source":"So... all the `D_39` values are less than 6 months (~180 days). Does this mean that the dataset only contains 'good' customers? meaning only such customers who have never previously defaulted?","metadata":{}},{"cell_type":"markdown","source":"## Already defaulted in train?\n\nFirst identify some customers with large `D_39` values:","metadata":{}},{"cell_type":"code","source":"pd.options.display.max_colwidth = 100\ncols = ['customer_ID','S_2','P_2']+[x for x in train.columns if 'D_' in x]\ntrain.loc[train.D_39>170, cols].head(10)","metadata":{"execution":{"iopub.status.busy":"2022-06-22T11:20:11.488687Z","iopub.execute_input":"2022-06-22T11:20:11.489457Z","iopub.status.idle":"2022-06-22T11:20:11.518998Z","shell.execute_reply.started":"2022-06-22T11:20:11.489423Z","shell.execute_reply":"2022-06-22T11:20:11.518171Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's explore several customer's timelines:","metadata":{}},{"cell_type":"code","source":"train.loc[train.customer_ID=='026ef3a81feea5de51a09d5796b996a1e3ec306ccd7327dd96d55d8d440203a4', cols]","metadata":{"execution":{"iopub.status.busy":"2022-06-22T11:20:11.519935Z","iopub.execute_input":"2022-06-22T11:20:11.520143Z","iopub.status.idle":"2022-06-22T11:20:11.838810Z","shell.execute_reply.started":"2022-06-22T11:20:11.520123Z","shell.execute_reply":"2022-06-22T11:20:11.837931Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.loc[train.customer_ID=='07683296b5cdbcbb9fb41884a545ed7490dbf17816358af23fec5d8c4a03ccf6', cols]","metadata":{"execution":{"iopub.status.busy":"2022-06-22T11:22:16.315397Z","iopub.execute_input":"2022-06-22T11:22:16.315874Z","iopub.status.idle":"2022-06-22T11:22:16.652046Z","shell.execute_reply.started":"2022-06-22T11:22:16.315847Z","shell.execute_reply":"2022-06-22T11:22:16.651371Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.loc[train.customer_ID=='176ad229cbd819198ffc212077a54ea3c3b4a1edbd7b5a10fd461a062de27f77', cols]","metadata":{"execution":{"iopub.status.busy":"2022-06-22T11:22:31.299110Z","iopub.execute_input":"2022-06-22T11:22:31.299423Z","iopub.status.idle":"2022-06-22T11:22:31.618270Z","shell.execute_reply.started":"2022-06-22T11:22:31.299399Z","shell.execute_reply":"2022-06-22T11:22:31.617213Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Did you notice what they have in common? THERE ARE MONTHS MISSING JUST AFTER LARGE `D_39` VALUES! This may indicate that the value increased in these missing periods!\n\nWhat does it mean? The most likely answer is that organizers removed months which had `D_39` showing that customer already has defaulted (`D_39`>180 or so)!\n\nNow this is interesting! You can actually reverse engineer the removed month logic - and this is not too hard to do so.\n\nWhat can be done with this information? I can think of 2 key things:\n\n1. Feature engineering related to large D_39 and missing months (i.e. does the customer had large D_39 and missing following months?)\n2. Expanding training dataset, by splitting customer timeline into segments (defaulted in train & defaulted as per competition)\n\nUltimately this can lead to using all rows (not only last) for training when defaulted in train has been handled. Currently this is impossible or does not produce good results as `target` is noisy for these customers in retrospective months.\n","metadata":{}},{"cell_type":"markdown","source":"## Finalle\n\nDeleted observations related to `target` is not the first time that has happened in kaggle. In one of the previous competition this has caused huge information leak. Thank god that this is time related competition.\n\nAnyway, I hope you enjoyed and are ready to dive in into this problem further!","metadata":{}}]}