{"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":"## Competition Objective:\n\nThe objective of the competition is to predict credit default i.e., to predict the probability that a customer does not pay back their credit card balance amount in the future based on their monthly customer profile.  Training, validation, and testing datasets include time-series behavioral data and anonymized customer profile information.","metadata":{}},{"cell_type":"markdown","source":"## Notebook Objective\n\nThe objective of the notebook is to explore the given datasets and make some inferences along the way.","metadata":{}},{"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 matplotlib.pyplot as plt\nimport seaborn as sns\ncolor = sns.color_palette()\nimport plotly.graph_objects as go\nimport plotly.express as px\n\n%matplotlib inline\n\npd.options.mode.chained_assignment = None\npd.options.display.max_columns = 999","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-05-26T17:00:43.523162Z","iopub.execute_input":"2022-05-26T17:00:43.523492Z","iopub.status.idle":"2022-05-26T17:00:43.530850Z","shell.execute_reply.started":"2022-05-26T17:00:43.523462Z","shell.execute_reply":"2022-05-26T17:00:43.530032Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Files Information\n\nLet us first look at the given files. ","metadata":{}},{"cell_type":"code","source":"import os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        file_size = round(os.path.getsize(os.path.join(dirname, filename)) / (1e9), 2)\n        print(f\"Filename : {filename} \\t File Size : {file_size} GB\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T17:00:43.531860Z","iopub.execute_input":"2022-05-26T17:00:43.532162Z","iopub.status.idle":"2022-05-26T17:00:43.543344Z","shell.execute_reply.started":"2022-05-26T17:00:43.532133Z","shell.execute_reply":"2022-05-26T17:00:43.542682Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We are given four files and the names are self explanatory. But look at those file sizes, they are just huge!!","metadata":{}},{"cell_type":"markdown","source":"## Reading the dataset files\n\nAs the files are huge, reading them using pandas `read_csv` as is will blew up the Kaggle notebook memory. So let us convert the `float64` columns to `float16` and then read the training dataset.","metadata":{}},{"cell_type":"code","source":"dtype_dict = {'customer_ID': \"object\",\n 'S_2': \"object\",\n 'P_2': 'float16',\n 'D_39': 'float16',\n 'B_1': 'float16',\n 'B_2': 'float16',\n 'R_1': 'float16',\n 'S_3': 'float16',\n 'D_41': 'float16',\n 'B_3': 'float16',\n 'D_42': 'float16',\n 'D_43': 'float16',\n 'D_44': 'float16',\n 'B_4': 'float16',\n 'D_45': 'float16',\n 'B_5': 'float16',\n 'R_2': 'float16',\n 'D_46': 'float16',\n 'D_47': 'float16',\n 'D_48': 'float16',\n 'D_49': 'float16',\n 'B_6': 'float16',\n 'B_7': 'float16',\n 'B_8': 'float16',\n 'D_50': 'float16',\n 'D_51': 'float16',\n 'B_9': 'float16',\n 'R_3': 'float16',\n 'D_52': 'float16',\n 'P_3': 'float16',\n 'B_10': 'float16',\n 'D_53': 'float16',\n 'S_5': 'float16',\n 'B_11': 'float16',\n 'S_6': 'float16',\n 'D_54': 'float16',\n 'R_4': 'float16',\n 'S_7': 'float16',\n 'B_12': 'float16',\n 'S_8': 'float16',\n 'D_55': 'float16',\n 'D_56': 'float16',\n 'B_13': 'float16',\n 'R_5': 'float16',\n 'D_58': 'float16',\n 'S_9': 'float16',\n 'B_14': 'float16',\n 'D_59': 'float16',\n 'D_60': 'float16',\n 'D_61': 'float16',\n 'B_15': 'float16',\n 'S_11': 'float16',\n 'D_62': 'float16',\n 'D_63': 'object',\n 'D_64': 'object',\n 'D_65': 'float16',\n 'B_16': 'float16',\n 'B_17': 'float16',\n 'B_18': 'float16',\n 'B_19': 'float16',\n 'D_66': 'float16',\n 'B_20': 'float16',\n 'D_68': 'float16',\n 'S_12': 'float16',\n 'R_6': 'float16',\n 'S_13': 'float16',\n 'B_21': 'float16',\n 'D_69': 'float16',\n 'B_22': 'float16',\n 'D_70': 'float16',\n 'D_71': 'float16',\n 'D_72': 'float16',\n 'S_15': 'float16',\n 'B_23': 'float16',\n 'D_73': 'float16',\n 'P_4': 'float16',\n 'D_74': 'float16',\n 'D_75': 'float16',\n 'D_76': 'float16',\n 'B_24': 'float16',\n 'R_7': 'float16',\n 'D_77': 'float16',\n 'B_25': 'float16',\n 'B_26': 'float16',\n 'D_78': 'float16',\n 'D_79': 'float16',\n 'R_8': 'float16',\n 'R_9': 'float16',\n 'S_16': 'float16',\n 'D_80': 'float16',\n 'R_10': 'float16',\n 'R_11': 'float16',\n 'B_27': 'float16',\n 'D_81': 'float16',\n 'D_82': 'float16',\n 'S_17': 'float16',\n 'R_12': 'float16',\n 'B_28': 'float16',\n 'R_13': 'float16',\n 'D_83': 'float16',\n 'R_14': 'float16',\n 'R_15': 'float16',\n 'D_84': 'float16',\n 'R_16': 'float16',\n 'B_29': 'float16',\n 'B_30': 'float16',\n 'S_18': 'float16',\n 'D_86': 'float16',\n 'D_87': 'float16',\n 'R_17': 'float16',\n 'R_18': 'float16',\n 'D_88': 'float16',\n 'B_31': 'int64',\n 'S_19': 'float16',\n 'R_19': 'float16',\n 'B_32': 'float16',\n 'S_20': 'float16',\n 'R_20': 'float16',\n 'R_21': 'float16',\n 'B_33': 'float16',\n 'D_89': 'float16',\n 'R_22': 'float16',\n 'R_23': 'float16',\n 'D_91': 'float16',\n 'D_92': 'float16',\n 'D_93': 'float16',\n 'D_94': 'float16',\n 'R_24': 'float16',\n 'R_25': 'float16',\n 'D_96': 'float16',\n 'S_22': 'float16',\n 'S_23': 'float16',\n 'S_24': 'float16',\n 'S_25': 'float16',\n 'S_26': 'float16',\n 'D_102': 'float16',\n 'D_103': 'float16',\n 'D_104': 'float16',\n 'D_105': 'float16',\n 'D_106': 'float16',\n 'D_107': 'float16',\n 'B_36': 'float16',\n 'B_37': 'float16',\n 'R_26': 'float16',\n 'R_27': 'float16',\n 'B_38': 'float16',\n 'D_108': 'float16',\n 'D_109': 'float16',\n 'D_110': 'float16',\n 'D_111': 'float16',\n 'B_39': 'float16',\n 'D_112': 'float16',\n 'B_40': 'float16',\n 'S_27': 'float16',\n 'D_113': 'float16',\n 'D_114': 'float16',\n 'D_115': 'float16',\n 'D_116': 'float16',\n 'D_117': 'float16',\n 'D_118': 'float16',\n 'D_119': 'float16',\n 'D_120': 'float16',\n 'D_121': 'float16',\n 'D_122': 'float16',\n 'D_123': 'float16',\n 'D_124': 'float16',\n 'D_125': 'float16',\n 'D_126': 'float16',\n 'D_127': 'float16',\n 'D_128': 'float16',\n 'D_129': 'float16',\n 'B_41': 'float16',\n 'B_42': 'float16',\n 'D_130': 'float16',\n 'D_131': 'float16',\n 'D_132': 'float16',\n 'D_133': 'float16',\n 'R_28': 'float16',\n 'D_134': 'float16',\n 'D_135': 'float16',\n 'D_136': 'float16',\n 'D_137': 'float16',\n 'D_138': 'float16',\n 'D_139': 'float16',\n 'D_140': 'float16',\n 'D_141': 'float16',\n 'D_142': 'float16',\n 'D_143': 'float16',\n 'D_144': 'float16',\n 'D_145': 'float16'}\n\ndf = pd.read_csv(\"/kaggle/input/amex-default-prediction/train_data.csv\", dtype=dtype_dict)\ndf.shape","metadata":{"execution":{"iopub.status.busy":"2022-05-26T17:00:43.544496Z","iopub.execute_input":"2022-05-26T17:00:43.544826Z","iopub.status.idle":"2022-05-26T17:06:14.410332Z","shell.execute_reply.started":"2022-05-26T17:00:43.544797Z","shell.execute_reply":"2022-05-26T17:06:14.409097Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The training dataset has got 5,531,451 rows and 190 columns.\n\nFeatures are anonymized and normalized and grouped into following general categories.\n* D_* = Delinquency variables\n* S_* = Spend variables\n* P_* = Payment variables\n* B_* = Balance variables\n* R_* = Risk variables","metadata":{}},{"cell_type":"code","source":"df.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T17:07:07.720657Z","iopub.execute_input":"2022-05-26T17:07:07.721116Z","iopub.status.idle":"2022-05-26T17:07:07.865804Z","shell.execute_reply.started":"2022-05-26T17:07:07.721079Z","shell.execute_reply":"2022-05-26T17:07:07.864701Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Takeaways:**\n\n* It is also given that the following variables are categorical.`['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']`\n\n* We are given the monthly customer profiles which means we have got multiple rows per `customer_ID`, one for each month.\n\n* Number of variables in each of the general categories\n  - Delinquency variables = 96\n  - Spend variables = 22\n  - Payment variables = 3\n  - Balance variables = 40\n  - Risk variables = 28\n  \nThere are several other ways to load the data faster using other packages and file formats. Please check the below excellent tutorial notebook by Rohan Rao.\nhttps://www.kaggle.com/code/rohanrao/tutorial-on-reading-large-datasets/notebook","metadata":{}},{"cell_type":"markdown","source":"## Customer Analysis\n\nIn this section, let us look into the customer level information.\n\nLet us start with looking into the number of unique customers in the dataset.","metadata":{}},{"cell_type":"code","source":"print(f\"There are {df['customer_ID'].nunique()} unique customers in the training dataset\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T17:16:18.713007Z","iopub.execute_input":"2022-05-26T17:16:18.713502Z","iopub.status.idle":"2022-05-26T17:16:19.552465Z","shell.execute_reply.started":"2022-05-26T17:16:18.713465Z","shell.execute_reply":"2022-05-26T17:16:19.551398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now let us check the number of months for which the customer profiles are available.","metadata":{}},{"cell_type":"code","source":"cnt_srs = (df['customer_ID'].value_counts()).value_counts()\nplt.figure(figsize=(12,6))\nsns.barplot(x=cnt_srs.index, y=cnt_srs.values, alpha=0.8, color=color[0])\nplt.xlabel('Number of Months', fontsize=12)\nplt.ylabel('Number of Customers', fontsize=12)\nplt.title(\"Number of months for which the customer profile is present\", fontsize=15)\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-05-26T17:35:08.206841Z","iopub.execute_input":"2022-05-26T17:35:08.207291Z","iopub.status.idle":"2022-05-26T17:35:09.019200Z","shell.execute_reply.started":"2022-05-26T17:35:08.207257Z","shell.execute_reply":"2022-05-26T17:35:09.017895Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Takeaways:**\n* Most of the customers in the given training dataset have 13 months of customer profile.","metadata":{}},{"cell_type":"markdown","source":"## Time Period Analysis\n\nIn this section, let us check the time period of the given training dataset. The variable `S_2` has time information.","metadata":{}},{"cell_type":"code","source":"df['S_2'] = pd.to_datetime(df['S_2'])\nprint(f\"Minimum date value in the training dataset : {df['S_2'].min()}\")\nprint(f\"Maximum date value in the training dataset : {df['S_2'].max()}\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T17:26:52.985081Z","iopub.execute_input":"2022-05-26T17:26:52.985531Z","iopub.status.idle":"2022-05-26T17:26:54.018458Z","shell.execute_reply.started":"2022-05-26T17:26:52.985495Z","shell.execute_reply":"2022-05-26T17:26:54.017379Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"So, we are given the customer monthly data from March 1, 2017 to March 31, 2018 for 13 months in the training dataset. \n\nNow let us see how many records are there in each of these months.","metadata":{}},{"cell_type":"code","source":"cnt_srs = (df['S_2'].dt.year*100 + df['S_2'].dt.month).value_counts()\nplt.figure(figsize=(12,6))\nsns.barplot(x=cnt_srs.index, y=cnt_srs.values, alpha=0.8, color=color[0])\n#plt.xticks(rotation='vertical')\nplt.xlabel('YearMonth', fontsize=12)\nplt.ylabel('Number of Rows', fontsize=12)\nplt.title(\"Monthly distribution of training data\", fontsize=15)\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-05-26T17:35:36.011034Z","iopub.execute_input":"2022-05-26T17:35:36.011479Z","iopub.status.idle":"2022-05-26T17:35:37.293192Z","shell.execute_reply.started":"2022-05-26T17:35:36.011442Z","shell.execute_reply":"2022-05-26T17:35:37.292423Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Takeaways**\n* The number of customer profiles for each month is slightly increasing month on month\n\nLet us check the day of the month distribution as well.","metadata":{}},{"cell_type":"code","source":"cnt_srs = (df['S_2'].dt.day).value_counts()\nplt.figure(figsize=(12,6))\nsns.barplot(x=cnt_srs.index, y=cnt_srs.values, alpha=0.8, color=color[0])\nplt.xlabel('Day of the month', fontsize=12)\nplt.ylabel('Number of Rows', fontsize=12)\nplt.title(\"Daywise distribution of training data\", fontsize=15)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T17:48:20.883210Z","iopub.execute_input":"2022-05-26T17:48:20.883745Z","iopub.status.idle":"2022-05-26T17:48:21.750207Z","shell.execute_reply.started":"2022-05-26T17:48:20.883701Z","shell.execute_reply":"2022-05-26T17:48:21.749155Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Takeaways:**\n* The monthly customer profile is taken from all days of the month.\n* Beginning of the month has comparatively lower customer profiles than the rest of the month.\n\nNow let us take a sample customer and see if the profiling is done at the same day of every month.","metadata":{}},{"cell_type":"code","source":"sample_customer_id = df[\"customer_ID\"].values[0]\ntemp_df = df[df[\"customer_ID\"]==sample_customer_id]\ntemp_df[\"YearMonth\"] = pd.to_datetime(pd.DataFrame({\"year\":temp_df['S_2'].dt.year, \"month\":temp_df['S_2'].dt.month, \"day\":[1]*temp_df.shape[0]}))\ntemp_df[\"DayOfMonth\"] = temp_df['S_2'].dt.day\n\nplt.figure(figsize=(12,6))\nsns.scatterplot(data=temp_df, x=\"YearMonth\", y=\"DayOfMonth\", alpha=0.8, color=color[0], s=80)\nplt.xlabel('Year&Month', fontsize=12)\nplt.ylabel('Day of Month', fontsize=12)\nplt.title(f\"Customer profiling days for customer_ID={sample_customer_id}\", fontsize=15)\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-05-26T17:55:29.943892Z","iopub.execute_input":"2022-05-26T17:55:29.944301Z","iopub.status.idle":"2022-05-26T17:55:30.569105Z","shell.execute_reply.started":"2022-05-26T17:55:29.944270Z","shell.execute_reply":"2022-05-26T17:55:30.567005Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Takeaways:**\n* So customer profiling is done once every month \n* Customer profiling is not done on a fixed day every month","metadata":{"execution":{"iopub.status.busy":"2022-05-26T17:54:04.906385Z","iopub.execute_input":"2022-05-26T17:54:04.906847Z","iopub.status.idle":"2022-05-26T17:54:04.918841Z","shell.execute_reply.started":"2022-05-26T17:54:04.906805Z","shell.execute_reply":"2022-05-26T17:54:04.918080Z"}}},{"cell_type":"markdown","source":"**This is a work in progress. More to come. Stay tuned**","metadata":{}}]}