{"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":"# Reviewing the default rate\n\nThis EDA aims to analyse the default rate for the credit card datasets. If a default rate moves with time this could help to provide some insights into the flows of default activity. We will have to see what insights can be gained.","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"}},{"cell_type":"markdown","source":"Taken inspiration from @ambrosm [AMEX EDA which makes sense](https://www.kaggle.com/code/ambrosm/amex-eda-which-makes-sense).","metadata":{}},{"cell_type":"markdown","source":"### Objectives\n* Build the default rate time series\n* Understand the flow and stock positions\n\nAfter reviewing the code by customer_ID there appear to be no customer's that flow into or out of default.","metadata":{}},{"cell_type":"code","source":"# Import packages\nimport pandas as pd\nimport numpy as np\nimport pickle\nfrom matplotlib import pyplot as plt\nimport os\nimport plotly.express as px\nimport plotly.graph_objects as go","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-05-30T11:22:47.345069Z","iopub.execute_input":"2022-05-30T11:22:47.346182Z","iopub.status.idle":"2022-05-30T11:22:47.351116Z","shell.execute_reply.started":"2022-05-30T11:22:47.346139Z","shell.execute_reply":"2022-05-30T11:22:47.350180Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# The labels\n\nWe start by reading the labels for the training data. There are neither missing values nor duplicated customer_IDs. Of the 458913 customer_IDs, 340000 (74 %) have a label of 0 (good customer, no default) and 119000 (26 %) have a label of 1 (bad customer, default).\n\nWe know that the good customers have been subsampled by a factor of 20; this means that in reality there are 6.8 million good customers. 98 % of the customers are good; 2 % are bad.\n\n**Insight:**\n- The classes are imbalanced. A StratifiedKFold for cross-validation is recommended.\n- Because the classes are imbalanced, accuracy would be a bad metric to evaluate a classifier. The [competition metric](https://www.kaggle.com/competitions/amex-default-prediction/discussion/327464) is a mix of area under the roc curve (auc) and recall.","metadata":{}},{"cell_type":"code","source":"train_labels = pd.read_csv('../input/amex-default-prediction/train_labels.csv')\ntrain_labels.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:07:13.222160Z","iopub.execute_input":"2022-05-30T10:07:13.223071Z","iopub.status.idle":"2022-05-30T10:07:14.208197Z","shell.execute_reply.started":"2022-05-30T10:07:13.223028Z","shell.execute_reply":"2022-05-30T10:07:14.207287Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check for missing data and duplicated customer_IDs\ntrain_labels.isna().any().any(), train_labels.customer_ID.duplicated().any()","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:07:19.046762Z","iopub.execute_input":"2022-05-30T10:07:19.047702Z","iopub.status.idle":"2022-05-30T10:07:19.155208Z","shell.execute_reply.started":"2022-05-30T10:07:19.047661Z","shell.execute_reply":"2022-05-30T10:07:19.154217Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"label_stats = pd.DataFrame({'absolute': train_labels.target.value_counts(),\n              'relative': train_labels.target.value_counts() / len(train_labels)})\nlabel_stats['absolute upsampled'] =  label_stats.absolute * np.array([20, 1])\nlabel_stats['relative upsampled'] = label_stats['absolute upsampled'] / label_stats['absolute upsampled'].sum()\nlabel_stats","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:07:39.847125Z","iopub.execute_input":"2022-05-30T10:07:39.847902Z","iopub.status.idle":"2022-05-30T10:07:39.871650Z","shell.execute_reply.started":"2022-05-30T10:07:39.847853Z","shell.execute_reply":"2022-05-30T10:07:39.870916Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# The data\n\nThe dataset of this competition has a considerable size. If you read the original csv files, the data barely fits into memory. That's why we read the data from @munumbutt's [AMEX-Feather-Dataset](https://www.kaggle.com/datasets/munumbutt/amexfeather). In this [Feather](https://arrow.apache.org/docs/python/feather.html) file, the floating point precision has been reduced from 64 bit to 16 bit. And reading a Feather file is faster than reading a csv file because the Feather file format is binary.\n\nThere are 5.5 million rows for training and 11 million rows of test data.","metadata":{}},{"cell_type":"code","source":"%%time\ntrain = pd.read_feather('../input/amexfeather/train_data.ftr')\ntest = pd.read_feather('../input/amexfeather/test_data.ftr')\nwith pd.option_context(\"display.min_rows\", 6):\n    display(train)\n    display(test)","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:08:08.721583Z","iopub.execute_input":"2022-05-30T10:08:08.721981Z","iopub.status.idle":"2022-05-30T10:09:11.399074Z","shell.execute_reply.started":"2022-05-30T10:08:08.721952Z","shell.execute_reply":"2022-05-30T10:09:11.397905Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The target column of the train dataframe corresponds to the target column of train_labels.csv. In the csv file of the train data, there is no target column; it has been joined into the Feather file as a convenience.\n\nS_2 is the statement date. All train statement dates are between March of 2017 and March of 2018 (13 months), and no statement dates are missing. All test statement dates are between April of 2018 and October of 2019. This means that the statement dates of train and test don't overlap:","metadata":{}},{"cell_type":"code","source":"train.dtypes","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:11:09.449226Z","iopub.execute_input":"2022-05-30T10:11:09.449662Z","iopub.status.idle":"2022-05-30T10:11:09.458190Z","shell.execute_reply.started":"2022-05-30T10:11:09.449612Z","shell.execute_reply":"2022-05-30T10:11:09.457322Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('Train statement dates: ', train.S_2.min(), train.S_2.max(), train.S_2.isna().any())\nprint('Test statement dates: ',  test.S_2.min(), test.S_2.max(), test.S_2.isna().any())","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:10:49.576397Z","iopub.execute_input":"2022-05-30T10:10:49.577069Z","iopub.status.idle":"2022-05-30T10:10:49.716532Z","shell.execute_reply.started":"2022-05-30T10:10:49.577029Z","shell.execute_reply":"2022-05-30T10:10:49.715392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Insight:**\n- The test data come from a different phase in the economic cycle than the training data. Our models have no way of learning the effect of the economic cycle.\n\nThis could be something to review. We could bring in the key American macro economic data relating to the time periods for the training and test data. Having this exogenous component to the model could really help with any additional noise that the model is not seeing and could provide further insights into how the customers are defaulting.","metadata":{}},{"cell_type":"code","source":"# Understanding the file size of one file\nfrom humanize import naturalsize\nsize = train.memory_usage(deep='True').sum()\nprint(size)\nprint(naturalsize(size))","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:20:45.337270Z","iopub.execute_input":"2022-05-30T10:20:45.337696Z","iopub.status.idle":"2022-05-30T10:20:45.761551Z","shell.execute_reply.started":"2022-05-30T10:20:45.337659Z","shell.execute_reply":"2022-05-30T10:20:45.760901Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f'Train data memory usage: {naturalsize(train.memory_usage(deep=\"True\").sum())} ')\nprint(f'Test data memory usage:  {naturalsize(test.memory_usage(deep=\"True\").sum())}')","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:22:15.718929Z","iopub.execute_input":"2022-05-30T10:22:15.719572Z","iopub.status.idle":"2022-05-30T10:22:17.059522Z","shell.execute_reply.started":"2022-05-30T10:22:15.719536Z","shell.execute_reply":"2022-05-30T10:22:17.058346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The training data takes 2.8 GBytes of RAM. The test data is twice the size of the training data.\n\n**Insight:**\n- With that much data, we need to have an eye on memory efficiency. Avoid keeping unnecessary copies of the data in memory, and avoid keeping unnecessary copies of models!\n- Whereas most machine learning algorithms expect the whole training data to be in memory, we don't need to load all the test data at once. The test data can be processed in batches.\n- You may want to separate training and inference code into two notebooks so that you never have training and test data in memory at the same time.","metadata":{}},{"cell_type":"markdown","source":"The info function shows that most other features have missing values:\n","metadata":{}},{"cell_type":"code","source":"train.info(max_cols=200, show_counts=True, memory_usage='deep')","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:24:19.156735Z","iopub.execute_input":"2022-05-30T10:24:19.157292Z","iopub.status.idle":"2022-05-30T10:24:25.276990Z","shell.execute_reply.started":"2022-05-30T10:24:19.157259Z","shell.execute_reply":"2022-05-30T10:24:25.276262Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Insight:**\n- There are many columns with missing values: Dropping all columns which have missing values is not a sensible strategy.\n- There are many rows with missing values: Dropping all rows which have missing values is not a sensible strategy.\n- Many decision-tree based algorithms can deal with missing values. If we choose such a model, we don't need to change the missing values.\n- Neural networks and other estimators cannot deal with missing values. If we choose such a model, we need to impute values. See [this guide](https://www.kaggle.com/code/parulpandey/a-guide-to-handling-missing-values-in-python) for an overview of the many imputation options.\n- Most features are 16-bit floats. The original data (in the csv file) has higher precision. By rounding it to 16-bit precision, some information is lost. To make this information loss more tangible: Every float16 number between 1 and 2 is a multiple of 1/1024. These numbers have only three digits behind the decimal point! This precision is enough to start the competition; maybe we'll have to switch to higher precision towards the end.","metadata":{}},{"cell_type":"markdown","source":"# Building default data","metadata":{}},{"cell_type":"markdown","source":"When reviewing the target variable within a time series we want to see how the pattern of defaults change as time progresses. Do we see more defaults at the beginning or end of a cycle.\n\nA few items to be aware of:\n- Need to check if each customer ID has a record for each month\n- Having statement dates spread throughout the month may distort the assessment of default. Adding a start of the month date can ensure consistency for monthly reviews","metadata":{}},{"cell_type":"code","source":"# Review the target variable across time\nt_data = train.loc[:, ['customer_ID', 'S_2', 'target']]\nt_data.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:27:26.822646Z","iopub.execute_input":"2022-05-30T10:27:26.823513Z","iopub.status.idle":"2022-05-30T10:27:26.987046Z","shell.execute_reply.started":"2022-05-30T10:27:26.823456Z","shell.execute_reply":"2022-05-30T10:27:26.986167Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Review the numbers of records by customer ID\nt_data.groupby(['customer_ID'])['target'].count().value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:36:25.138743Z","iopub.execute_input":"2022-05-30T10:36:25.139480Z","iopub.status.idle":"2022-05-30T10:36:26.386031Z","shell.execute_reply.started":"2022-05-30T10:36:25.139432Z","shell.execute_reply":"2022-05-30T10:36:26.385022Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# What proportion of the customers are seeing default status by number of statements received?\nt_data_summ = t_data.groupby(['customer_ID']).agg({\n                    'target': ['count', sum]\n                }).value_counts(normalize=True).to_frame(name='prop')\nt_data_summ","metadata":{"execution":{"iopub.status.busy":"2022-05-30T10:58:22.284014Z","iopub.execute_input":"2022-05-30T10:58:22.285107Z","iopub.status.idle":"2022-05-30T10:58:23.634563Z","shell.execute_reply.started":"2022-05-30T10:58:22.285063Z","shell.execute_reply":"2022-05-30T10:58:23.633608Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The two largest groups are the customers who had 13 statements with either all non-default (64.6%) or all default (19.5%) status. With the defaulters this creates a challenge as we don't understand when they first entered default. It is the entering default that we are trying to understand and seeking to find variables that highlight the increased likelihood of entering.","metadata":{}},{"cell_type":"code","source":"# Number of unique values for the statement dates. Lets review this with a visualization\ndef summary(x):\n    result = {\n                'total': x['target'].count()\n                ,'perf': (np.where(x['target']==0,1,0)).sum()\n                ,'def': (np.where(x['target']==1,1,0)).sum()\n            }\n    return pd.Series(result).round(4)\n\n# Apply the summary method grouped by the time period variable\ntime_d = t_data.groupby(['S_2']).apply(summary)\ntime_d = time_d.reset_index()\n\nfig = px.line(time_d, x='S_2', y=\"total\", title='Customer statements by day')\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-30T11:27:29.189811Z","iopub.execute_input":"2022-05-30T11:27:29.190599Z","iopub.status.idle":"2022-05-30T11:27:32.116911Z","shell.execute_reply.started":"2022-05-30T11:27:29.190548Z","shell.execute_reply":"2022-05-30T11:27:32.115973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Review the stock positions over time\nfig = px.area(time_d, x='S_2', y=[\"perf\", \"def\"], title='Default stock positions across time', groupnorm='percent')\n\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-30T11:36:37.116871Z","iopub.execute_input":"2022-05-30T11:36:37.117406Z","iopub.status.idle":"2022-05-30T11:36:37.201294Z","shell.execute_reply.started":"2022-05-30T11:36:37.117353Z","shell.execute_reply":"2022-05-30T11:36:37.200342Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The default stock positions show the stable positions over time with no spikes. A stock position shows were the customer is at during that point in time. For the analysis this would be either a performing or default status. \n\nNext stage is to review the flows into and out of default","metadata":{}},{"cell_type":"code","source":"# Need to sort the data by customer_ID and date\nt_data = t_data.sort_values(by = ['customer_ID', 'S_2'], ignore_index=True)\nt_data.head(20)","metadata":{"execution":{"iopub.status.busy":"2022-05-30T11:46:34.073650Z","iopub.execute_input":"2022-05-30T11:46:34.074464Z","iopub.status.idle":"2022-05-30T11:46:37.552127Z","shell.execute_reply.started":"2022-05-30T11:46:34.074418Z","shell.execute_reply":"2022-05-30T11:46:37.551410Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Review movement by records that are provided. Note we will have to go back and ensure that each customer has statements for consecutive months\ndef default_rules(df):\n    # Check to make sure that the current loan and previous loan match\n    df['cust_c'] = np.where(df.customer_ID.shift(1) == df.customer_ID, 1, 0)\n    # Numpy condition to check for matching loan and flow into default\n    df_cond_np = np.array((df.cust_c == 1) & ((df.target.shift(1) == 0) &  (df.target == 1)))\n    # Apply the numpy array to create a default flow variable\n    df['def_flow'] = np.where(df_cond_np, 1, 0)\n    # Understanding if any customers return to performing status\n    df_cond_p = np.array((df.cust_c == 1) & ((df.target.shift(1) == 1) &  (df.target == 0)))\n    # Apply the numpy array to create a performing flow variable\n    df['perf_flow'] = np.where(df_cond_p, 1, 0)\n    return df\n\nt_data_1 = default_rules(t_data)\nt_data_1.head(50)","metadata":{"execution":{"iopub.status.busy":"2022-05-30T12:13:24.516535Z","iopub.execute_input":"2022-05-30T12:13:24.517273Z","iopub.status.idle":"2022-05-30T12:13:25.237836Z","shell.execute_reply.started":"2022-05-30T12:13:24.517218Z","shell.execute_reply":"2022-05-30T12:13:25.236912Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# It looks like there are no flows into or out of default. So all customers are either in one state or the other\nt_data_1[['def_flow', 'perf_flow']].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-05-30T12:14:41.815104Z","iopub.execute_input":"2022-05-30T12:14:41.815495Z","iopub.status.idle":"2022-05-30T12:14:42.198366Z","shell.execute_reply.started":"2022-05-30T12:14:41.815462Z","shell.execute_reply":"2022-05-30T12:14:42.197465Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Confirm that the def_flow doesn't change by time\ntime_d1 = t_data_1.groupby(['S_2'])['def_flow'].sum()\ntime_d1 = time_d1.reset_index()\n\nfig = px.line(time_d1, x='S_2', y=\"def_flow\", title='Customer statements by day')\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-30T12:10:27.274981Z","iopub.execute_input":"2022-05-30T12:10:27.275427Z","iopub.status.idle":"2022-05-30T12:10:27.465641Z","shell.execute_reply.started":"2022-05-30T12:10:27.275386Z","shell.execute_reply":"2022-05-30T12:10:27.464518Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}