{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":35332,"databundleVersionId":3723648,"sourceType":"competition"},{"sourceId":3703374,"sourceType":"datasetVersion","datasetId":2215340}],"dockerImageVersionId":30664,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2024-03-06T10:00:38.522729Z","iopub.execute_input":"2024-03-06T10:00:38.523223Z","iopub.status.idle":"2024-03-06T10:00:38.528956Z","shell.execute_reply.started":"2024-03-06T10:00:38.523177Z","shell.execute_reply":"2024-03-06T10:00:38.527838Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- The original dataset provided in csv is too large (50GB)\n- The data cannot fit into memory\n- [Amex-Feather-Dataset](https://www.kaggle.com/datasets/seefun/amex-default-prediction-feather) provided by @munum is a feather file that has smaller size than an equivalent csv file","metadata":{}},{"cell_type":"code","source":"import os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:00:38.531270Z","iopub.execute_input":"2024-03-06T10:00:38.533615Z","iopub.status.idle":"2024-03-06T10:00:38.545027Z","shell.execute_reply.started":"2024-03-06T10:00:38.533581Z","shell.execute_reply":"2024-03-06T10:00:38.543754Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# display options\npd.set_option('display.max_rows', None)    # Show all rows\npd.set_option('display.max_columns', None) # Show all columns\npd.set_option('display.width', None)","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:00:38.546465Z","iopub.execute_input":"2024-03-06T10:00:38.546926Z","iopub.status.idle":"2024-03-06T10:00:38.556388Z","shell.execute_reply.started":"2024-03-06T10:00:38.546896Z","shell.execute_reply":"2024-03-06T10:00:38.555255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# constants\nCAT_VARS = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:00:38.557950Z","iopub.execute_input":"2024-03-06T10:00:38.558893Z","iopub.status.idle":"2024-03-06T10:00:38.574320Z","shell.execute_reply.started":"2024-03-06T10:00:38.558856Z","shell.execute_reply":"2024-03-06T10:00:38.572977Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_size = 10000\nsample_train = pd.read_feather(\"/kaggle/input/amex-default-prediction-feather/train.feather\").head(sample_size)\nsample_target = pd.read_csv(\"/kaggle/input/amex-default-prediction/train_labels.csv\")\n# filter target\nsample_target = sample_target[sample_target.customer_ID.isin(sample_train.customer_ID.unique())]","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:00:38.577559Z","iopub.execute_input":"2024-03-06T10:00:38.577968Z","iopub.status.idle":"2024-03-06T10:00:45.848477Z","shell.execute_reply.started":"2024-03-06T10:00:38.577936Z","shell.execute_reply":"2024-03-06T10:00:45.847321Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# sample size and features\nprint(\"Train data count rows =\",sample_train.shape[0])\nprint(\"Number of Columns =\", sample_train.shape[1])","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:00:45.849902Z","iopub.execute_input":"2024-03-06T10:00:45.850241Z","iopub.status.idle":"2024-03-06T10:00:45.856412Z","shell.execute_reply.started":"2024-03-06T10:00:45.850212Z","shell.execute_reply":"2024-03-06T10:00:45.855144Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cont_vars = [col for col in sample_train.columns if col not in CAT_VARS]\ncont_vars.remove('customer_ID')\ncont_vars.remove('S_2')","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:00:45.857755Z","iopub.execute_input":"2024-03-06T10:00:45.858241Z","iopub.status.idle":"2024-03-06T10:00:45.870106Z","shell.execute_reply.started":"2024-03-06T10:00:45.858186Z","shell.execute_reply":"2024-03-06T10:00:45.868944Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# count of target binary variable\nfig, ax = plt.subplots(figsize=(10,5))\nsns.countplot(x=sample_target.target)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:00:45.871391Z","iopub.execute_input":"2024-03-06T10:00:45.871737Z","iopub.status.idle":"2024-03-06T10:00:46.143018Z","shell.execute_reply.started":"2024-03-06T10:00:45.871710Z","shell.execute_reply":"2024-03-06T10:00:46.141895Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# date range\n# parsing the S_2 to datetime format\nsample_train['S_2'] = pd.to_datetime(sample_train['S_2'], format='%Y-%m-%d')\nprint(f'There are records dated from {sample_train[\"S_2\"].min()} to {sample_train[\"S_2\"].max()}')","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:00:46.144448Z","iopub.execute_input":"2024-03-06T10:00:46.144843Z","iopub.status.idle":"2024-03-06T10:00:46.158761Z","shell.execute_reply.started":"2024-03-06T10:00:46.144812Z","shell.execute_reply":"2024-03-06T10:00:46.157781Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# number of statements taken per day per across customers\nplt.figure(figsize=(10, 6))\n\ndaily_counts = sample_train.groupby(pd.to_datetime(sample_train[\"S_2\"]).dt.date).count()[\"customer_ID\"]\n\nsns.lineplot(x=daily_counts.index, y=daily_counts.values)\n\nplt.title('Number of Statements Taken per Day')\nplt.xlabel('Date')\nplt.ylabel('Count')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:02:28.093245Z","iopub.execute_input":"2024-03-06T10:02:28.094542Z","iopub.status.idle":"2024-03-06T10:02:28.803894Z","shell.execute_reply.started":"2024-03-06T10:02:28.094472Z","shell.execute_reply":"2024-03-06T10:02:28.802735Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# missing value for rows each column/feature wise out of 10000 samples/rows\nsample_train.isnull().sum().sort_values(ascending = False).head(40)","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:03:21.164822Z","iopub.execute_input":"2024-03-06T10:03:21.165201Z","iopub.status.idle":"2024-03-06T10:03:21.185775Z","shell.execute_reply.started":"2024-03-06T10:03:21.165171Z","shell.execute_reply":"2024-03-06T10:03:21.184607Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# statement count per customer ID\nfig, ax = plt.subplots(figsize=(20,5))\nsns.countplot(x=sample_train.groupby(\"customer_ID\")['customer_ID'].count().values)\nplt.title(\"customer wise statement count\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:00:46.776929Z","iopub.execute_input":"2024-03-06T10:00:46.777277Z","iopub.status.idle":"2024-03-06T10:00:47.139657Z","shell.execute_reply.started":"2024-03-06T10:00:46.777248Z","shell.execute_reply":"2024-03-06T10:00:47.138224Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"statements_per_customer  = sample_train.customer_ID.value_counts()\nstatements_per_customer_w_labels =  pd.merge(sample_target, statements_per_customer, on=\"customer_ID\")\n\ntot_occ = statements_per_customer_w_labels.groupby(by=[\"target\", \"count\"]).size().reset_index(name='occurrences').sort_values(by='count')\ntot_occ.reset_index(drop=True, inplace=True)\n\n\n# Calculate the total occurrences for each count\ntot_occ['total_occurrences'] = tot_occ.groupby('count')['occurrences'].transform('sum')\ntot_occ['ratio'] = tot_occ['occurrences'] / tot_occ['total_occurrences']\ntot_occ.drop(columns='total_occurrences', inplace=True)\ntot_occ = tot_occ[tot_occ['target'] == 0]\n\nplt.figure(figsize=(10, 6))\nplt.bar(tot_occ['count'], tot_occ['ratio']*100, color='skyblue')\nplt.xlabel('Count of statements')\nplt.ylabel('Perecentage of bad customers')\nplt.title('Statement Count')\nplt.xticks(rotation=45, ha='right')  # Rotate x-axis labels for better readability\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:05:05.094151Z","iopub.execute_input":"2024-03-06T10:05:05.094666Z","iopub.status.idle":"2024-03-06T10:05:05.535340Z","shell.execute_reply.started":"2024-03-06T10:05:05.094628Z","shell.execute_reply":"2024-03-06T10:05:05.534241Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(10, 6))\n\n# Assuming 'sample_train' is your DataFrame and 'time_series_data' is your time series data with time index\ngrouped_data = sample_train.drop(\"customer_ID\", axis = 1).groupby('S_2').mean().reset_index()\n\n# Assuming 'time_series_data' has a datetime index and a column 'value'\nsns.lineplot(x=grouped_data['S_2'], y=grouped_data['D_88'], label='Time Series Data')\n\nplt.title('Time Series Data')\nplt.xlabel('Date')\nplt.ylabel('Value')\nplt.legend()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:06:48.598162Z","iopub.execute_input":"2024-03-06T10:06:48.598583Z","iopub.status.idle":"2024-03-06T10:06:49.062408Z","shell.execute_reply.started":"2024-03-06T10:06:48.598537Z","shell.execute_reply":"2024-03-06T10:06:49.061318Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# There are no NAN values in any categorical variables. Following shows the number of good and bad customers a.k.a target label wise distribution for each category\n\n# Uncomment to see category wise distribution of tar for each \ntrain = pd.merge(sample_train, sample_target, on =\"customer_ID\")\n\nfor col in CAT_VARS:\n    plt.figure(figsize=(10, 6))\n    \n    # Group by target and col, count occurrences, and reset index\n    grouped_data = train.groupby(['target', col]).size().unstack(fill_value=0).reset_index()\n    \n    # Melt the DataFrame to have 'target', col', and 'count' as columns\n    grouped_data_melted = pd.melt(grouped_data, id_vars=['target'], var_name=col, value_name='count')\n    \n    # Create count plot\n    sns.barplot(x=col, y='count', hue='target', data=grouped_data_melted, order=train[col].value_counts(dropna=False).index)\n    \n    plt.title(f'Distribution of {col} by Target')\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:09:55.091919Z","iopub.execute_input":"2024-03-06T10:09:55.092328Z","iopub.status.idle":"2024-03-06T10:09:58.709756Z","shell.execute_reply.started":"2024-03-06T10:09:55.092296Z","shell.execute_reply":"2024-03-06T10:09:58.708557Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# from ydata_profiling import ProfileReport\n# profile = ProfileReport(sample_train, title = \"Profile Report\", minimal=True)\n# # profile.to_widgets()\n\n# profile.to_notebook_iframe()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Sort the DataFrame by 'customer_ID' and 'S_2'\nsample_train = sample_train.sort_values(['customer_ID', 'S_2'])\nsample_target = sample_target.sort_values('customer_ID')\n\n# Calculate the time difference between consecutive statement dates for each customer\nsample_train['time_to_previous_statement'] = sample_train.groupby('customer_ID')['S_2'].diff()\n\n# Display the DataFrame with the new column\nsample_train[['customer_ID', 'S_2', 'time_to_previous_statement']].head(5)","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:10:29.916251Z","iopub.execute_input":"2024-03-06T10:10:29.916662Z","iopub.status.idle":"2024-03-06T10:10:29.948928Z","shell.execute_reply.started":"2024-03-06T10:10:29.916628Z","shell.execute_reply":"2024-03-06T10:10:29.946879Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"first_date_per_customer = sample_train.groupby('customer_ID')['S_2'].first().dt.month\nmean_time_difference_per_customer = sample_train.groupby('customer_ID')['time_to_previous_statement'].mean().dt.days\nhighest_time_difference_per_customer = sample_train.groupby('customer_ID')['time_to_previous_statement'].max().dt.days\nleast_time_difference_per_customer = sample_train.groupby('customer_ID')['time_to_previous_statement'].min().dt.days\ncount = sample_train.groupby('customer_ID')['time_to_previous_statement'].size()\nstd = sample_train.groupby('customer_ID')['time_to_previous_statement'].std().dt.days\n\n\n# Display the results\nresult_df = pd.DataFrame({\n    'customer_ID': first_date_per_customer.index,\n    'first_date': first_date_per_customer.values,\n    'mean_time_difference': mean_time_difference_per_customer.values,\n    'max_time' : highest_time_difference_per_customer.values,\n    'min_time' : least_time_difference_per_customer.values,\n    'count' : count.values,\n    'std' : std.values\n})\nresult_df = pd.merge(result_df, sample_target, on=\"customer_ID\")\nresult_df = pd.merge(result_df, sample_train.drop(['S_2'], axis = 1).groupby(\"customer_ID\").mean(), on=\"customer_ID\")\nresult_df.sort_values('mean_time_difference').head(10)","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:10:39.706723Z","iopub.execute_input":"2024-03-06T10:10:39.707116Z","iopub.status.idle":"2024-03-06T10:10:40.003975Z","shell.execute_reply.started":"2024-03-06T10:10:39.707088Z","shell.execute_reply":"2024-03-06T10:10:40.003016Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"result_df.describe()","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:10:50.674960Z","iopub.execute_input":"2024-03-06T10:10:50.675377Z","iopub.status.idle":"2024-03-06T10:10:51.269849Z","shell.execute_reply.started":"2024-03-06T10:10:50.675348Z","shell.execute_reply":"2024-03-06T10:10:51.268060Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"result_df.drop(['customer_ID'], axis = 1).groupby(\"target\").mean()","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:10:54.104882Z","iopub.execute_input":"2024-03-06T10:10:54.106220Z","iopub.status.idle":"2024-03-06T10:10:54.269891Z","shell.execute_reply.started":"2024-03-06T10:10:54.106180Z","shell.execute_reply":"2024-03-06T10:10:54.268084Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"result_df.head(10)","metadata":{"execution":{"iopub.status.busy":"2024-03-06T10:10:56.315649Z","iopub.execute_input":"2024-03-06T10:10:56.316340Z","iopub.status.idle":"2024-03-06T10:10:56.525749Z","shell.execute_reply.started":"2024-03-06T10:10:56.316296Z","shell.execute_reply":"2024-03-06T10:10:56.524553Z"},"trusted":true},"execution_count":null,"outputs":[]}]}