{"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":"<div style=\"padding:20px;color:white;margin:0;font-size:175%;text-align:center;display:fill;border-radius:5px;background-color:#016CC9;overflow:hidden;font-weight:500\">[EDA]American Express/Comparison of training and test</div>\n\n# <b><span style='color:#4B4B4B'>1 |</span><span style='color:#016CC9'> Notes</span></b>\nThis note follows the specifications of the following note. [[here]](https://www.kaggle.com/code/kellibelcher/amex-default-prediction-eda-lgbm-baseline)\nIf you upvote, please be sure to upvote the referenced note as well.\n\n\nThis note compares the differences between training and test data in terms of histograms and missing values.\n\n# <b><span style='color:#4B4B4B'>2 |</span><span style='color:#016CC9'> Data Overview</span></b>\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\nThe dataset contains aggregated profile features for each customer at each statement date. Features are anonymized and normalized, and fall into the following general categories:  \n**`D_*`:** Delinquency variables  \n**`S_*`:** Spend variables  \n**`P_*`:** Payment variables  \n**`B_*`:** Balance variables  \n**`R_*`:** Risk variables  \nWith the following features being categorical: `B_30`, `B_38`, `D_63`, `D_64`, `D_66`, `D_68`, `D_114`, `D_116`, `D_117`, `D_120`, `D_126`. \n\nThere are a total of 190 variables in the dataset with approximately 450,000 customers in the training set and 925,000 in the test set. Due to the dataset size, I will use the compressed version of the train and test sets provided by @munumbutt's [AMEX-Feather-Dataset](https://www.kaggle.com/datasets/munumbutt/amexfeather) and take the last statement for each customer.","metadata":{}},{"cell_type":"code","source":"import numpy as np \nimport pandas as pd\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport matplotlib.colors\nimport plotly.express as px\nimport plotly.graph_objects as go\nfrom plotly.subplots import make_subplots\nfrom plotly.offline import init_notebook_mode\nfrom sklearn.preprocessing import StandardScaler\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.model_selection import StratifiedKFold \nfrom sklearn.metrics import roc_auc_score, roc_curve, auc\nfrom lightgbm import LGBMClassifier, early_stopping, log_evaluation\nimport warnings, gc\nwarnings.filterwarnings(\"ignore\")\ninit_notebook_mode(connected=True)\n\ntemp=dict(layout=go.Layout(font=dict(family=\"Franklin Gothic\", size=12), \n                           height=500, width=1000))\n\ntrain = pd.read_feather('../input/amexfeather/train_data.ftr')\ntrain = train.groupby('customer_ID').tail(1).set_index('customer_ID')\n\nprint(\"The training data begins on {} and ends on {}.\".format(train['S_2'].min().strftime('%m-%d-%Y'),train['S_2'].max().strftime('%m-%d-%Y')))\nprint(\"There are {:,.0f} customers in the training set and {} features.\".format(train.shape[0],train.shape[1]))\n\ntest = pd.read_feather('../input/amexfeather/test_data.ftr')\ntest = test.groupby('customer_ID').tail(1).set_index('customer_ID')\n\nprint(\"\\nThe test data begins on {} and ends on {}.\".format(test['S_2'].min().strftime('%m-%d-%Y'),test['S_2'].max().strftime('%m-%d-%Y')))\nprint(\"There are {:,.0f} customers in the test set and {} features.\".format(test.shape[0],test.shape[1]))\n\ndel test['S_2']\ngc.collect()\n\ntitles=['Delinquency '+str(i).split('_')[1] if i.startswith('D') else 'Spend '+str(i).split('_')[1] \n        if i.startswith('S') else 'Payment '+str(i).split('_')[1]  if i.startswith('P') \n        else 'Balance '+str(i).split('_')[1] if i.startswith('B') else \n        'Risk '+str(i).split('_')[1] for i in train.columns[:-1]]\ncat_cols=['Balance 30', 'Balance 38', 'Delinquency 63', 'Delinquency 64', 'Delinquency 66', 'Delinquency 68',\n          'Delinquency 114', 'Delinquency 116', 'Delinquency 117', 'Delinquency 120', 'Delinquency 126', 'Target','category']\ntest.columns=titles[1:]\ntitles.append('Target')\ntrain.columns=titles\n\ntrain = train.assign(category=0)#Insert data name\ntrain.pop('Spend 2')#Exclude time series columns\n\ntest = test.assign(Target=\"\")#Insert missing value\ntest = test.assign(category=1)#Insert data name","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-06-16T05:51:49.530029Z","iopub.execute_input":"2022-06-16T05:51:49.530497Z","iopub.status.idle":"2022-06-16T05:52:47.260989Z","shell.execute_reply.started":"2022-06-16T05:51:49.53046Z","shell.execute_reply":"2022-06-16T05:52:47.259874Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b><span style='color:#4B4B4B'>3 |</span><span style='color:#016CC9'> Exploratory Data Analysis</span></b>","metadata":{}},{"cell_type":"code","source":"target=train.category.value_counts(normalize=True)\ntarget.rename(index={1:'Default',0:'Paid'},inplace=True)\npal, color=['#016CC9','#DEB078'], ['#8DBAE2','#EDD3B3']","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-06-16T05:55:21.308437Z","iopub.execute_input":"2022-06-16T05:55:21.308917Z","iopub.status.idle":"2022-06-16T05:55:21.319359Z","shell.execute_reply.started":"2022-06-16T05:55:21.308877Z","shell.execute_reply":"2022-06-16T05:55:21.318389Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = pd.concat([train,test])","metadata":{"execution":{"iopub.status.busy":"2022-06-16T05:55:23.151628Z","iopub.execute_input":"2022-06-16T05:55:23.152051Z","iopub.status.idle":"2022-06-16T05:55:23.963825Z","shell.execute_reply.started":"2022-06-16T05:55:23.152017Z","shell.execute_reply":"2022-06-16T05:55:23.962992Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b><div style='padding:15px;background-color:#4B4B4B;color:white;border-radius:5px;font-size:60%'>3.1 EDA of Delinquency Variables</div></b>","metadata":{}},{"cell_type":"code","source":"cols=[col for col in train.columns if (col.startswith(('D','T','c'))) & (col not in cat_cols[:-1])]\nplot_df=train[cols]\nfig, ax = plt.subplots(18,5, figsize=(16,54))\nfig.suptitle('Distribution of Delinquency Variables',fontsize=16)\nrow=0\ncol=[0,1,2,3,4]*18\nfor i, column in enumerate(plot_df.columns[:-1]):\n    if (i!=0)&(i%5==0):\n        row+=1\n    sns.kdeplot(x=column, hue='category', palette=pal[::-1], hue_order=[0,1], \n                label=['train','test'], data=plot_df, \n                fill=True, linewidth=2, legend=False, ax=ax[row,col[i]])\n    ax[row,col[i]].tick_params(left=False,bottom=False)\n    ax[row,col[i]].set(title='\\n\\n{}'.format(column), xlabel='', ylabel=('Density' if i%5==0 else ''))\nfor i in range(2,5):\n    ax[17,i].set_visible(False)\nhandles, _ = ax[0,0].get_legend_handles_labels() \nfig.legend(labels=['train','test'], handles=reversed(handles), ncol=2, bbox_to_anchor=(0.18, 0.983))\nsns.despine(bottom=True, trim=True)\nplt.tight_layout(rect=[0, 0.2, 1, 0.99])","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Delinquency_142　 Focusing on the unevenness on the right side of the distribution outline, we can confirm that there is a difference between the training and test data.**","metadata":{}},{"cell_type":"markdown","source":"# <b><div style='padding:15px;background-color:#4B4B4B;color:white;border-radius:5px;font-size:60%'>3.2 EDA of Spend Variables</div></b>","metadata":{}},{"cell_type":"code","source":"cols=[col for col in train.columns if (col.startswith(('S','T','c'))) & (col not in cat_cols[:-1])]\nplot_df=train[cols]\nfig, ax = plt.subplots(5,5, figsize=(16,20))\nfig.suptitle('Distribution of Spend Variables',fontsize=16)\nrow=0\ncol=[0,1,2,3,4]*5\nfor i, column in enumerate(plot_df.columns[:-1]):\n    if (i!=0)&(i%5==0):\n        row+=1\n    sns.kdeplot(x=column, hue='category', palette=pal[::-1], hue_order=[0,1], \n                label=['train','test'], data=plot_df, \n                fill=True, linewidth=2, legend=False, ax=ax[row,col[i]])\n    ax[row,col[i]].tick_params(left=False,bottom=False)\n    ax[row,col[i]].set(title='\\n\\n{}'.format(column), xlabel='', ylabel=('Density' if i%5==0 else ''))\nfor i in range(1,5):\n    ax[4,i].set_visible(False)\nhandles, _ = ax[0,0].get_legend_handles_labels() \nfig.legend(labels=['train','test'], handles=reversed(handles), ncol=2, bbox_to_anchor=(0.18, 0.985))\nsns.despine(bottom=True, trim=True)\nplt.tight_layout(rect=[0, 0.2, 1, 0.99])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-06-16T05:55:27.02098Z","iopub.execute_input":"2022-06-16T05:55:27.021618Z","iopub.status.idle":"2022-06-16T05:57:53.175116Z","shell.execute_reply.started":"2022-06-16T05:55:27.021582Z","shell.execute_reply":"2022-06-16T05:57:53.173987Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Spend_11　Spend_11 has different external shapes for training and test data. There is a possibility that the distribution can be regarded as the same by scaling.**","metadata":{}},{"cell_type":"markdown","source":"# <b><div style='padding:15px;background-color:#4B4B4B;color:white;border-radius:5px;font-size:60%'>3.3 EDA of Payment Variables</div></b>","metadata":{}},{"cell_type":"code","source":"cols=[col for col in train.columns if (col.startswith(('P','T','c'))) & (col not in cat_cols[:-1])]\nplot_df=train[cols]\nfig, ax = plt.subplots(1,3, figsize=(16,5))\nfig.suptitle('Distribution of Payment Variables',fontsize=16)\nfor i, col in enumerate(plot_df.columns[:-1]):\n    sns.kdeplot(x=col, hue='category', palette=pal[::-1], hue_order=[0,1], \n                label=['train','test'], data=plot_df, \n                fill=True, linewidth=2, legend=False, ax=ax[i])\n    ax[i].tick_params(left=False,bottom=False)\n    ax[i].set(title='{}'.format(col), xlabel='', ylabel=('Density' if i==0 else ''))\nhandles, _ = ax[0].get_legend_handles_labels() \nfig.legend(labels=['train','test'], handles=reversed(handles), ncol=2, bbox_to_anchor=(0.18, 1))\nsns.despine(bottom=True, trim=True)\nplt.tight_layout(rect=[0, 0.2, 1, 0.99])","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b><div style='padding:15px;background-color:#4B4B4B;color:white;border-radius:5px;font-size:60%'>3.4 EDA of Balance Variables</div></b>","metadata":{}},{"cell_type":"code","source":"cols=[col for col in train.columns if (col.startswith(('B','T','c'))) & (col not in cat_cols[:-1])]\nplot_df=train[cols]\nfig, ax = plt.subplots(8,5, figsize=(16,32))\nfig.suptitle('Distribution of Balance Variables',fontsize=16)\nrow=0\ncol=[0,1,2,3,4]*8\nfor i, column in enumerate(plot_df.columns[:-1]):\n    if (i!=0)&(i%5==0):\n        row+=1\n    sns.kdeplot(x=column, hue='category', palette=pal[::-1], hue_order=[0,1], \n                label=['train','test'], data=plot_df, \n                fill=True, linewidth=2, legend=False, ax=ax[row,col[i]])\n    ax[row,col[i]].tick_params(left=False,bottom=False)\n    ax[row,col[i]].set(title='\\n\\n{}'.format(column), xlabel='', ylabel=('Density' if i%5==0 else ''))\nfor i in range(3,5):\n    ax[7,i].set_visible(False)\nhandles, _ = ax[0,0].get_legend_handles_labels() \nfig.legend(labels=['train','test'], handles=reversed(handles), ncol=2, bbox_to_anchor=(0.18, 0.984))\nsns.despine(bottom=True, trim=True)\nplt.tight_layout(rect=[0, 0.2, 1, 0.99])","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b><div style='padding:15px;background-color:#4B4B4B;color:white;border-radius:5px;font-size:60%'>3.5 EDA of Risk Variables</div></b>","metadata":{}},{"cell_type":"code","source":"cols=[col for col in train.columns if (col.startswith(('R','T','c'))) & (col not in cat_cols[:-1])]\nplot_df=train[cols]\nfig, ax = plt.subplots(6,5, figsize=(16,24))\nfig.suptitle('Distribution of Risk Variables',fontsize=16)\nrow=0\ncol=[0,1,2,3,4]*6\nfor i, column in enumerate(plot_df.columns[:-1]):\n    if (i!=0)&(i%5==0):\n        row+=1\n    sns.kdeplot(x=column, hue='category', palette=pal[::-1], hue_order=[0,1], \n                label=['train','test'], data=plot_df, \n                fill=True, linewidth=2, legend=False, ax=ax[row,col[i]])\n    ax[row,col[i]].tick_params(left=False,bottom=False)\n    ax[row,col[i]].set(title='\\n\\n{}'.format(column), xlabel='', ylabel=('Density' if i%5==0 else ''))\nfor i in range(3,5):\n    ax[5,i].set_visible(False)\nhandles, _ = ax[0,0].get_legend_handles_labels() \nfig.legend(labels=['train','test'], handles=reversed(handles), ncol=2, bbox_to_anchor=(0.18, 0.984))\nsns.despine(bottom=True, trim=True)\nplt.tight_layout(rect=[0, 0.2, 1, 0.99])","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b><div style='padding:15px;background-color:#4B4B4B;color:white;border-radius:5px;font-size:60%'>3.6 EDA of Categorical Variables</div></b>","metadata":{}},{"cell_type":"code","source":"rgb=['rgba'+str(matplotlib.colors.to_rgba(i,0.7)) for i in pal]","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = make_subplots(rows=4, cols=3, \n                    subplot_titles=cat_cols[:-2], \n                    vertical_spacing=0.1)\nrow=0\nc=[1,2,3]*5\nplot_df=train[cat_cols]\nfor i,col in enumerate(cat_cols[:-2]):\n    if i%3==0:\n        row+=1\n    plot_df[col]=plot_df[col].astype(object)\n    df=plot_df.groupby(col)['category'].value_counts().rename('count').reset_index().replace('',np.nan)\n    \n    fig.add_trace(go.Bar(x=df[df.category==0][col], y=df[df.category==0]['count'],\n                         marker_color=rgb[1], marker_line=dict(color=pal[1],width=2), \n                         hovertemplate='Value %{x} Frequency = %{y}',\n                         name='train', showlegend=(True if i==0 else False)),\n                  row=row, col=c[i])\n    fig.add_trace(go.Bar(x=df[df.category==1][col], y=df[df.category==1]['count'],\n                         marker_color=rgb[0], marker_line=dict(color=pal[0],width=2),\n                         hovertemplate='Value %{x} Frequency = %{y}',\n                         name='test', showlegend=(True if i==0 else False)),\n                  row=row, col=c[i])\n    if i%3==0:\n        fig.update_yaxes(title='Frequency',row=row,col=c[i])\nfig.update_layout(template=temp,title=\"Distribution of Categorical Variables\",\n                  legend=dict(orientation=\"h\",yanchor=\"bottom\",y=1.03,xanchor=\"right\",x=0.2),\n                  barmode='group',height=1500,width=900)\nfig.show()","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b><div style='padding:15px;background-color:#4B4B4B;color:white;border-radius:5px;font-size:60%'>Missing value</div></b>","metadata":{}},{"cell_type":"code","source":"null=round((train[train.category==0].isna().sum()/train[train.category==0].shape[0]*100),2).sort_values(ascending=False)\nnull=null.to_frame().rename(columns={0:'Missing_train %'})\n\nnull2=round((train[train.category==1].isna().sum()/train[train.category==1].shape[0]*100),2).sort_values(ascending=False)\nnull2=null2.to_frame().rename(columns={0:'Missing_test %'})\n\n\nnull = pd.merge(null,null2,left_index=True,right_index=True)\nnull['diff %'] = null['Missing_train %'] - null['Missing_test %']\n# null['diff_abs %'] = abs(null['Missing %_x'] - null['Missing %_y'])\nnull['diff %'].sort_values(ascending=False)\n# sort_index(axis=1, ascending=False)\n# null.sort_values(by='diff_abs %', ascending=False).head(50)\n\npd.set_option('display.max_rows', None)\n\n# null.sort_values(by='diff_abs %', ascending=False)\nnull.sort_index()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"If the value of diff is positive, the training data has a smaller percentage of missing data, and if the value of diff is negative, the test data has a larger percentage of missing data.","metadata":{}},{"cell_type":"code","source":"plt.rcParams[\"figure.figsize\"] = (20, 10)\nsns.scatterplot(data=null, x='Missing_train %', y='Missing_test %')\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b><div style='padding:15px;background-color:#4B4B4B;color:white;border-radius:5px;font-size:60%'>Couclusion</div></b>\n\n# <b><span style='color:#4B4B4B'>1 |</span><span style='color:#016CC9'>Scaling Delinquency_142 and Spend_11 may improve the accuracy of the model.</span></b>\n\n# <b><span style='color:#4B4B4B'>2 |</span><span style='color:#016CC9'>Balance_29 and Spend 9 should be handled with care.</span></b>","metadata":{}}]}