{"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":"# **Notebook Objective**\n\nExploring train and test data timeperiods\n\n\n\n# **Target Definition**\n\nThe target binary variable is calculated by observing 18 months performance window after the latest credit card statement, and if the customer does not pay due amount in 120 days after their latest statement date it is considered a default event. \n\n1. In credit terminology what this definition means is **people who are 120+ days delinquent in 18 months**\n\n2. **ECM Model** For Amex this is an Existing customer management model, This model will primarily be used for Credit line management, assesing portfolio risk.\n\n","metadata":{}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-05-28T01:33:30.371455Z","iopub.execute_input":"2022-05-28T01:33:30.372141Z","iopub.status.idle":"2022-05-28T01:33:30.406927Z","shell.execute_reply.started":"2022-05-28T01:33:30.372044Z","shell.execute_reply":"2022-05-28T01:33:30.405766Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Delinquencies DQ Transitions: Credit Terminology an example\n\nA customer who got his credit statement in April 31st and does not pay the minimum amount , is\n\n* 30+ DQ on May 1\n\n* 60+ DQ on June 1\n\n* 90+ DQ on July 1\n\n* 120+ DQ on August 1\n\n* 150+ DQ on September 1\n\n* 180+ DQ on October 1 - At this point customer is considered as charged off.\n\nWe can think of above transition phases as a markov chain, where in recovery or cure rate from one stage to another decreases as we move towards final(charge off state).\n\nOnce a customer enters DQ phases, it is easy to predict transition rates for next phases, so existing customer models focus on the time period before a customer enters DQ cycles","metadata":{}},{"cell_type":"markdown","source":"# **Understanding vintage(time periods) given in the dataset**\n\n\nFor this initial part, I am focusing on statement date column, S_2","metadata":{"execution":{"iopub.status.busy":"2022-05-28T01:20:44.296845Z","iopub.execute_input":"2022-05-28T01:20:44.297528Z","iopub.status.idle":"2022-05-28T01:20:44.303341Z","shell.execute_reply.started":"2022-05-28T01:20:44.29749Z","shell.execute_reply":"2022-05-28T01:20:44.302145Z"}}},{"cell_type":"code","source":"dtype_dict = {'customer_ID': \"object\",\n 'S_2': \"object\"}\n\ntrain = pd.read_csv(\"/kaggle/input/amex-default-prediction/train_data.csv\", dtype=dtype_dict, usecols=['customer_ID','S_2'])\nprint(f' Number of customers {train.customer_ID.nunique()} , Number of rows {train.shape[0]}')\ntrain.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-28T01:34:33.509782Z","iopub.execute_input":"2022-05-28T01:34:33.510452Z","iopub.status.idle":"2022-05-28T01:38:51.96708Z","shell.execute_reply.started":"2022-05-28T01:34:33.510418Z","shell.execute_reply":"2022-05-28T01:38:51.965555Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# get statement month\ntrain['stmt_mon'] = train['S_2'].to_numpy().astype('datetime64[M]')\ntrain.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-28T01:42:54.954193Z","iopub.execute_input":"2022-05-28T01:42:54.954636Z","iopub.status.idle":"2022-05-28T01:42:55.584387Z","shell.execute_reply.started":"2022-05-28T01:42:54.954601Z","shell.execute_reply":"2022-05-28T01:42:55.583331Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gp = train.groupby('stmt_mon').agg({'customer_ID':'nunique'})\nprint(gp)\ngp.plot.bar(title='Number of Unique customers in each month')","metadata":{"execution":{"iopub.status.busy":"2022-05-28T01:49:50.376004Z","iopub.execute_input":"2022-05-28T01:49:50.376448Z","iopub.status.idle":"2022-05-28T01:49:52.34881Z","shell.execute_reply.started":"2022-05-28T01:49:50.376413Z","shell.execute_reply":"2022-05-28T01:49:52.347753Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# March, 2018 as the vintage for Train data\n\nFrom the above we can see, that train data has statements upto March, 2018","metadata":{}},{"cell_type":"markdown","source":"####  Get data at the customer level\n**Starting and ending statement month for each customer**","metadata":{}},{"cell_type":"code","source":"cust = train.groupby(['customer_ID']).agg({'customer_ID':'count',\n                             'stmt_mon':['min','max']\n                              \n                             })\ncust.columns = ['num_obs','st_mon','end_mon']\ncust.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-28T01:55:10.086997Z","iopub.execute_input":"2022-05-28T01:55:10.087786Z","iopub.status.idle":"2022-05-28T01:55:12.422185Z","shell.execute_reply.started":"2022-05-28T01:55:10.087746Z","shell.execute_reply":"2022-05-28T01:55:12.421081Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"vc = cust.num_obs.value_counts(normalize=True).round(4)\nprint(vc)\nvc.plot.bar('Number of customers by number of statments')","metadata":{"execution":{"iopub.status.busy":"2022-05-28T01:58:37.087598Z","iopub.execute_input":"2022-05-28T01:58:37.088341Z","iopub.status.idle":"2022-05-28T01:58:37.319716Z","shell.execute_reply.started":"2022-05-28T01:58:37.088292Z","shell.execute_reply":"2022-05-28T01:58:37.318945Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**For 85% of the customers we have data for 13 months**","metadata":{}},{"cell_type":"code","source":"cust = cust.reset_index()\ncust.groupby('st_mon').agg({'customer_ID':'count','num_obs':[min,max]})","metadata":{"execution":{"iopub.status.busy":"2022-05-28T02:05:10.906161Z","iopub.execute_input":"2022-05-28T02:05:10.906629Z","iopub.status.idle":"2022-05-28T02:05:11.012032Z","shell.execute_reply.started":"2022-05-28T02:05:10.906594Z","shell.execute_reply":"2022-05-28T02:05:11.01102Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Starting month of Train data is March 2017**\n\n* My assumption is :  Other starting months might represent customers who were on book in the month of March 17, but were spend inactive during that time period, But this needs to be investigated,","metadata":{}},{"cell_type":"code","source":"cust.groupby('end_mon').agg({'customer_ID':'count','num_obs':[min,max]})","metadata":{"execution":{"iopub.status.busy":"2022-05-28T02:06:13.86608Z","iopub.execute_input":"2022-05-28T02:06:13.86714Z","iopub.status.idle":"2022-05-28T02:06:13.947277Z","shell.execute_reply.started":"2022-05-28T02:06:13.867081Z","shell.execute_reply":"2022-05-28T02:06:13.946337Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Train data represents Default behavior as seen on Statment of March 2018**\n\n# **Relevance of 13 months**\n\n* **We are given performance data for a maximum of 13 months, after which a customer has done default in next 18 months+**\n\n\n# Relation with target by number of statements\n","metadata":{}},{"cell_type":"code","source":"dep = pd.read_csv('/kaggle/input/amex-default-prediction/train_labels.csv')\ncust = cust.merge(dep,on='customer_ID')\ncust.shape","metadata":{"execution":{"iopub.status.busy":"2022-05-28T02:28:11.596587Z","iopub.execute_input":"2022-05-28T02:28:11.597036Z","iopub.status.idle":"2022-05-28T02:28:13.286363Z","shell.execute_reply.started":"2022-05-28T02:28:11.597001Z","shell.execute_reply":"2022-05-28T02:28:13.285295Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gp  = cust.groupby('num_obs').target.mean()\ngp.plot.bar('Target proportion by number of statements')","metadata":{"execution":{"iopub.status.busy":"2022-05-28T02:30:01.662022Z","iopub.execute_input":"2022-05-28T02:30:01.6625Z","iopub.status.idle":"2022-05-28T02:30:01.88804Z","shell.execute_reply.started":"2022-05-28T02:30:01.662462Z","shell.execute_reply":"2022-05-28T02:30:01.886988Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**As the above plot shows people who were inactive for some of the months have higher default rates**","metadata":{}},{"cell_type":"markdown","source":"# **Time periods for test data**","metadata":{}},{"cell_type":"code","source":"del(train)\ndel(dep)","metadata":{"execution":{"iopub.status.busy":"2022-05-28T02:33:42.578746Z","iopub.execute_input":"2022-05-28T02:33:42.579298Z","iopub.status.idle":"2022-05-28T02:33:42.607038Z","shell.execute_reply.started":"2022-05-28T02:33:42.579239Z","shell.execute_reply":"2022-05-28T02:33:42.606018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ntest = pd.read_csv(\"/kaggle/input/amex-default-prediction/test_data.csv\", dtype=dtype_dict, usecols=['customer_ID','S_2'])\nprint(f' Number of customers {test.customer_ID.nunique()} , Number of rows {test.shape[0]}')","metadata":{"execution":{"iopub.status.busy":"2022-05-28T02:35:50.85095Z","iopub.execute_input":"2022-05-28T02:35:50.851346Z","iopub.status.idle":"2022-05-28T02:44:43.910646Z","shell.execute_reply.started":"2022-05-28T02:35:50.851315Z","shell.execute_reply":"2022-05-28T02:44:43.909322Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test['stmt_mon'] = test['S_2'].to_numpy().astype('datetime64[M]')\ngp = test.groupby('stmt_mon').agg({'customer_ID':'nunique'})\nprint(gp)\ngp.plot.bar(title='Number of unique customers in each month for test dataset')","metadata":{"execution":{"iopub.status.busy":"2022-05-28T02:45:19.515381Z","iopub.execute_input":"2022-05-28T02:45:19.51582Z","iopub.status.idle":"2022-05-28T02:45:25.899953Z","shell.execute_reply.started":"2022-05-28T02:45:19.515786Z","shell.execute_reply":"2022-05-28T02:45:25.898978Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_test = test.groupby(['customer_ID']).agg({'customer_ID':'count',\n                             'stmt_mon':['min','max']\n                              \n                             })\ncust_test.columns = ['num_obs','st_mon','end_mon']\ncust_test = cust_test.reset_index()\ncust_test.groupby('st_mon').agg({'customer_ID':'count','num_obs':[min,max]})","metadata":{"execution":{"iopub.status.busy":"2022-05-28T02:46:39.379741Z","iopub.execute_input":"2022-05-28T02:46:39.380415Z","iopub.status.idle":"2022-05-28T02:46:44.218851Z","shell.execute_reply.started":"2022-05-28T02:46:39.380366Z","shell.execute_reply":"2022-05-28T02:46:44.217725Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Checking overlap of customers","metadata":{}},{"cell_type":"code","source":"set(cust_test.index).intersection(cust.customer_ID)","metadata":{"execution":{"iopub.status.busy":"2022-05-28T02:53:12.849279Z","iopub.execute_input":"2022-05-28T02:53:12.849752Z","iopub.status.idle":"2022-05-28T02:53:13.10413Z","shell.execute_reply.started":"2022-05-28T02:53:12.849718Z","shell.execute_reply":"2022-05-28T02:53:13.103322Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_test.groupby('end_mon').agg({'customer_ID':'count','num_obs':[min,max]})","metadata":{"execution":{"iopub.status.busy":"2022-05-28T02:46:44.220835Z","iopub.execute_input":"2022-05-28T02:46:44.221502Z","iopub.status.idle":"2022-05-28T02:46:44.361651Z","shell.execute_reply.started":"2022-05-28T02:46:44.221454Z","shell.execute_reply":"2022-05-28T02:46:44.360548Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Test data is from 2 Time periods** \n\n1. April 2018 to April 2019\n2. October 2018 to October 2019\n\n**As DQ behavior is influenced by seasonality, Out of time validation for this competition might be the most challenging part,(considering train data is from a  single vintage of March 2017)**\n\n\n# Leaderboard scoring is done on April 2019 vintage and Final evaluation will be done on October 2019 vintage\n\n","metadata":{}}]}