{"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":50160,"databundleVersionId":7602123,"sourceType":"competition"}],"dockerImageVersionId":30664,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Overview¶\n\nIn this notebooks, you can know below information：\n\n1. Introduction of Depth_1:`train_deposit_1` data\n2. check NAN% of all colums with EDA\n3. feature engineering build new features\n4. EDA new features and description the insights\n5. new feature dataset to download\n\n## Others HomeCredict Analysis Notebook\n\n1. `train_applprev_1_*`：https://www.kaggle.com/code/algeryang/homecredit-eda-fe-depth1-applprev-1","metadata":{}},{"cell_type":"markdown","source":"# Install & Import Package & Read","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"}},{"cell_type":"code","source":"!pip install googletrans==3.1.0a0 --upgrade --quiet","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:17.970070Z","iopub.execute_input":"2024-03-04T07:50:17.970875Z","iopub.status.idle":"2024-03-04T07:50:33.913884Z","shell.execute_reply.started":"2024-03-04T07:50:17.970815Z","shell.execute_reply":"2024-03-04T07:50:33.912232Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from googletrans import Translator\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport polars as pl\nimport gc\nimport warnings\nwarnings.simplefilter(action='ignore', category=FutureWarning)","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:33.920697Z","iopub.execute_input":"2024-03-04T07:50:33.921107Z","iopub.status.idle":"2024-03-04T07:50:35.386201Z","shell.execute_reply.started":"2024-03-04T07:50:33.921065Z","shell.execute_reply":"2024-03-04T07:50:35.385067Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base = pd.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_base.csv\")\ndf = pd.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_deposit_1.csv\")\nfd = pd.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/feature_definitions.csv\")","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:35.387875Z","iopub.execute_input":"2024-03-04T07:50:35.388540Z","iopub.status.idle":"2024-03-04T07:50:36.551648Z","shell.execute_reply.started":"2024-03-04T07:50:35.388496Z","shell.execute_reply":"2024-03-04T07:50:36.550391Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp = fd.loc[fd['Variable'].isin(list(df.columns))]\ntranslator = Translator()\ntmp[\"Description_ch\"] = tmp[\"Description\"].map(lambda x: translator.translate(x, src=\"en\", dest=\"zh-TW\").text)\ntmp['fea_type'] = tmp['Variable'].apply(lambda x: x[-1])","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:36.555223Z","iopub.execute_input":"2024-03-04T07:50:36.555646Z","iopub.status.idle":"2024-03-04T07:50:36.733066Z","shell.execute_reply.started":"2024-03-04T07:50:36.555602Z","shell.execute_reply":"2024-03-04T07:50:36.731523Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## We can see that `train_deposit_1` below:","metadata":{}},{"cell_type":"code","source":"df","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:36.735389Z","iopub.execute_input":"2024-03-04T07:50:36.735853Z","iopub.status.idle":"2024-03-04T07:50:36.759460Z","shell.execute_reply.started":"2024-03-04T07:50:36.735775Z","shell.execute_reply":"2024-03-04T07:50:36.758311Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"shape of Depth_1: train_deposit_1 \", df.shape)\nprint(\"the unique case_id : \", len(set(df.case_id)))\nprint(\"all case_id in train_base are: \", len(train_base.case_id))\nprint(\"Only \",100*round(len(set(df.case_id))/len(train_base.case_id),3),\"%\", \"case_id in train_base have deposit_1 history records\")","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:36.761020Z","iopub.execute_input":"2024-03-04T07:50:36.761807Z","iopub.status.idle":"2024-03-04T07:50:36.836987Z","shell.execute_reply.started":"2024-03-04T07:50:36.761753Z","shell.execute_reply":"2024-03-04T07:50:36.835890Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It'means if we build new features from `train_deposit_1` dataset, **93%** case_id in train_base will be NAN for all features !!!\n\n***\n\n## Description example : case_id = 1377353","metadata":{"_kg_hide-input":true}},{"cell_type":"code","source":"df.loc[df['case_id'] == 1377353].sort_values(['num_group1'])","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:36.838075Z","iopub.execute_input":"2024-03-04T07:50:36.838428Z","iopub.status.idle":"2024-03-04T07:50:36.862768Z","shell.execute_reply.started":"2024-03-04T07:50:36.838397Z","shell.execute_reply":"2024-03-04T07:50:36.861424Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:36.864509Z","iopub.execute_input":"2024-03-04T07:50:36.864941Z","iopub.status.idle":"2024-03-04T07:50:36.879050Z","shell.execute_reply.started":"2024-03-04T07:50:36.864903Z","shell.execute_reply":"2024-03-04T07:50:36.877530Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* num_group1 = 0 is openingdate_313D:`2014-08-12`\n* num_group1 = 64 is openingdate_313D:`2015-03-11`\n* Contrary to `train_applprev_1_*`, a smaller num_group1 represents an older date, while a larger num_group1 indicates a more recent date.\n* Only 3 columns to build feautres","metadata":{}},{"cell_type":"markdown","source":"## NAN percentage in train_deposit_1","metadata":{}},{"cell_type":"code","source":"percent_missing = np.round(df.isnull().sum() * 100 / len(df),4)\nmissing_value_df = pd.DataFrame({'column_name': df.columns,\n                                 'percent_missing': percent_missing}).reset_index(drop=True)\nprint(\"depth_1 train_applprev_1_* columns : \", df.shape[1])\nprint(\"how many columns have NAN : \",missing_value_df.loc[missing_value_df['percent_missing']>0].shape[0])","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:36.880992Z","iopub.execute_input":"2024-03-04T07:50:36.881547Z","iopub.status.idle":"2024-03-04T07:50:36.921519Z","shell.execute_reply.started":"2024-03-04T07:50:36.881488Z","shell.execute_reply":"2024-03-04T07:50:36.920334Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"missing_value_df.sort_values(['percent_missing'],ascending=False).\\\n  plot.bar(x='column_name', y='percent_missing', rot=20,figsize=(6,3));","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:36.922967Z","iopub.execute_input":"2024-03-04T07:50:36.923482Z","iopub.status.idle":"2024-03-04T07:50:37.239333Z","shell.execute_reply.started":"2024-03-04T07:50:36.923443Z","shell.execute_reply":"2024-03-04T07:50:37.238114Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The above chart shows that only the field `contractenddate_991D` has NANs, with a proportion of 54.92%.\nSo i'll ignore this field for feature engineering","metadata":{}},{"cell_type":"markdown","source":"## Each case_id with not only one row in `train_deposit_1`\n\nSince each case_id has many historical records, we observe the proportion of all case_id across different numbers of historical records.","metadata":{}},{"cell_type":"code","source":"depth_1_table = \\\n    df.groupby(['case_id'])['num_group1'].\\\n    count().\\\n    reset_index().\\\n    rename(columns={'num_group1':'count'}).\\\n    sort_values(['count'],ascending=False)\n\np_df_1 = depth_1_table.groupby(['count']).nunique().reset_index()\np_df_1['pct'] = 100*(p_df_1['case_id']/p_df_1['case_id'].sum())","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:37.240771Z","iopub.execute_input":"2024-03-04T07:50:37.241623Z","iopub.status.idle":"2024-03-04T07:50:37.284044Z","shell.execute_reply.started":"2024-03-04T07:50:37.241587Z","shell.execute_reply":"2024-03-04T07:50:37.282687Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Set the aesthetic style of the plots\nsns.set_style(\"whitegrid\")\n\n# Plotting the bar chart\nplt.figure(figsize=(8, 4))\nbarplot = sns.barplot(x='count', y='pct', data=p_df_1)\nbarplot.bar_label(barplot.containers[0],fontsize=8)\n# Set the title and labels of the plot\nbarplot.set_title('Percentage of case_id between different Row Count')\nbarplot.set_xlabel('Row Count (num_group1 Count)')\nbarplot.set_ylabel('pct %')\n\n# Rotate x-axis labels for better readability\nplt.xticks(rotation=0)\n\n# Show the plot\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:37.287986Z","iopub.execute_input":"2024-03-04T07:50:37.288383Z","iopub.status.idle":"2024-03-04T07:50:38.236358Z","shell.execute_reply.started":"2024-03-04T07:50:37.288351Z","shell.execute_reply":"2024-03-04T07:50:38.234919Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* 75% case_id have 1 history record (num_group1 Count)\n* 17% case_id have 2 history record \n* 4.9% case_id have 3 history record \n* 1.43% case_id have 4 history record\n* Most of them only have one historical record, so perhaps when we build features, we could try using the most recent account information for each case_id as the feature.\n\n---","metadata":{"execution":{"iopub.status.busy":"2024-03-04T05:10:15.737249Z","iopub.execute_input":"2024-03-04T05:10:15.737741Z","iopub.status.idle":"2024-03-04T05:10:15.746205Z","shell.execute_reply.started":"2024-03-04T05:10:15.737707Z","shell.execute_reply":"2024-03-04T05:10:15.744152Z"}}},{"cell_type":"markdown","source":"# Feature Engineering ","metadata":{}},{"cell_type":"markdown","source":"## The function of Build features below:","metadata":{}},{"cell_type":"code","source":"# feature engineering function demo and testing\ndef applepre_feature_eng(data,fealist,agglist):\n    result = pd.DataFrame({'case_id':list(set(data.case_id))})\n    for f in fealist:\n        print(f,'......')\n        df_table = data.loc[:,['case_id',f,'num_group1']].\\\n            sort_values(['case_id','num_group1'],ascending = False).\\\n            groupby(['case_id'])[f].\\\n            agg(agglist).\\\n            add_suffix('_'+ f).\\\n            reset_index()\n        result = result.merge(df_table,on=['case_id'],how='left')\n    return result","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:38.241629Z","iopub.execute_input":"2024-03-04T07:50:38.242098Z","iopub.status.idle":"2024-03-04T07:50:38.251101Z","shell.execute_reply.started":"2024-03-04T07:50:38.242061Z","shell.execute_reply":"2024-03-04T07:50:38.249718Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# A：Transform amount\n\n* **amount_416A**","metadata":{}},{"cell_type":"code","source":"tmp.loc[tmp['Variable'] == \"amount_416A\"]","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:38.252470Z","iopub.execute_input":"2024-03-04T07:50:38.252869Z","iopub.status.idle":"2024-03-04T07:50:38.275588Z","shell.execute_reply.started":"2024-03-04T07:50:38.252828Z","shell.execute_reply":"2024-03-04T07:50:38.274361Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"A_fea_table = applepre_feature_eng(data = df,fealist = [\"amount_416A\"],agglist = ['first'])","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:38.277356Z","iopub.execute_input":"2024-03-04T07:50:38.278600Z","iopub.status.idle":"2024-03-04T07:50:38.452838Z","shell.execute_reply.started":"2024-03-04T07:50:38.278555Z","shell.execute_reply":"2024-03-04T07:50:38.451350Z"},"_kg_hide-input":false,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"A_fea_table","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:38.454475Z","iopub.execute_input":"2024-03-04T07:50:38.454928Z","iopub.status.idle":"2024-03-04T07:50:38.471540Z","shell.execute_reply.started":"2024-03-04T07:50:38.454889Z","shell.execute_reply":"2024-03-04T07:50:38.470082Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# D：Transform date\n\n* **openingdate_313D** \n* Because of **contractenddate_991D** NAN > 50%, i ignore this feature in the short time","metadata":{}},{"cell_type":"code","source":"tmp.loc[tmp['Variable'] == \"openingdate_313D\"]","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:38.473683Z","iopub.execute_input":"2024-03-04T07:50:38.474152Z","iopub.status.idle":"2024-03-04T07:50:38.489376Z","shell.execute_reply.started":"2024-03-04T07:50:38.474115Z","shell.execute_reply":"2024-03-04T07:50:38.488245Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"D_fea_table = applepre_feature_eng(data = df,fealist = [\"openingdate_313D\"],agglist = ['first'])","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:38.492437Z","iopub.execute_input":"2024-03-04T07:50:38.492873Z","iopub.status.idle":"2024-03-04T07:50:38.705048Z","shell.execute_reply.started":"2024-03-04T07:50:38.492835Z","shell.execute_reply":"2024-03-04T07:50:38.703621Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"D_fea_table","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:38.706722Z","iopub.execute_input":"2024-03-04T07:50:38.707247Z","iopub.status.idle":"2024-03-04T07:50:38.722912Z","shell.execute_reply.started":"2024-03-04T07:50:38.707198Z","shell.execute_reply":"2024-03-04T07:50:38.721584Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feature Engineering Result Table\n\n* At the end, we join all new feature table to train_base\n* new features data from `train_deposit_1` has finished","metadata":{"execution":{"iopub.status.busy":"2024-03-04T06:53:04.610201Z","iopub.execute_input":"2024-03-04T06:53:04.610651Z","iopub.status.idle":"2024-03-04T06:53:04.621758Z","shell.execute_reply.started":"2024-03-04T06:53:04.610620Z","shell.execute_reply":"2024-03-04T06:53:04.619696Z"}}},{"cell_type":"code","source":"data = train_base.\\\n    merge(A_fea_table,on=['case_id'],how = 'left').\\\n    merge(D_fea_table,on=['case_id'],how = 'left').dropna()","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:38.725033Z","iopub.execute_input":"2024-03-04T07:50:38.725690Z","iopub.status.idle":"2024-03-04T07:50:39.593957Z","shell.execute_reply.started":"2024-03-04T07:50:38.725623Z","shell.execute_reply":"2024-03-04T07:50:39.592601Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Now, add new feature : difference total days : date_dicision - first_openingdate_313D","metadata":{}},{"cell_type":"code","source":"data['diff_total_day'] = (pd.to_datetime(data.date_decision) - pd.to_datetime(data.first_openingdate_313D)).dt.days","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:39.595926Z","iopub.execute_input":"2024-03-04T07:50:39.597059Z","iopub.status.idle":"2024-03-04T07:50:39.668710Z","shell.execute_reply.started":"2024-03-04T07:50:39.597007Z","shell.execute_reply":"2024-03-04T07:50:39.667566Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# EDA for new features","metadata":{}},{"cell_type":"code","source":"data.head()","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:39.670599Z","iopub.execute_input":"2024-03-04T07:50:39.670973Z","iopub.status.idle":"2024-03-04T07:50:39.689207Z","shell.execute_reply.started":"2024-03-04T07:50:39.670940Z","shell.execute_reply":"2024-03-04T07:50:39.687747Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ind = 0\nfor col in ['diff_total_day','first_amount_416A']:\n\n    if ind % 2 == 0:\n        plt.figure(figsize=(40, 4))\n    plt.subplot(1, 4, ind % 2 + 1)\n    \n    sns.histplot(data=data, x=col, hue=\"target\", bins=50)\n    plt.ylabel(\"\")\n    \n    if ind % 2 == 1:\n        plt.show()\n    \n    ind += 1","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:39.691279Z","iopub.execute_input":"2024-03-04T07:50:39.691813Z","iopub.status.idle":"2024-03-04T07:50:40.984470Z","shell.execute_reply.started":"2024-03-04T07:50:39.691746Z","shell.execute_reply":"2024-03-04T07:50:40.983144Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.boxplot(data=data, x=\"target\", y=\"diff_total_day\")","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:40.986248Z","iopub.execute_input":"2024-03-04T07:50:40.986649Z","iopub.status.idle":"2024-03-04T07:50:41.299862Z","shell.execute_reply.started":"2024-03-04T07:50:40.986614Z","shell.execute_reply":"2024-03-04T07:50:41.298385Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.boxplot(data=data, x=\"target\", y=\"first_amount_416A\")\nplt.ylim(0, 1000)","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:41.301613Z","iopub.execute_input":"2024-03-04T07:50:41.302051Z","iopub.status.idle":"2024-03-04T07:50:41.616112Z","shell.execute_reply.started":"2024-03-04T07:50:41.302012Z","shell.execute_reply":"2024-03-04T07:50:41.614851Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(30, 10))\nsns.scatterplot(data=data, x=\"diff_total_day\", y=\"first_amount_416A\", hue=\"target\",palette=\"deep\")\n# control x and y limits\n# plt.ylim(0, 2000000)\n# plt.xlim(0, 2000)","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:41.618189Z","iopub.execute_input":"2024-03-04T07:50:41.619383Z","iopub.status.idle":"2024-03-04T07:50:48.369793Z","shell.execute_reply.started":"2024-03-04T07:50:41.619331Z","shell.execute_reply":"2024-03-04T07:50:48.368485Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Summary Insights\n\n1. 93% of case_id in `train_base` will be `NAN`, so I think that `train_deposit_1` is not powerful.\n\n2. From the results of the EDA graphs above, it is shown that the correlation of `first_amount_416A` and `first_openingdate_313D` with the target is not high.\n\n3. About 74% of case_id in `train_deposit_1` only have one historical record, so when creating new features, we can consider using the most recent record to perform the aggregate feature.\"\n\n4. The proportion of NAN in `contractenddate_991D` is about 54%, so it is discarded for feature engineering.\"","metadata":{}},{"cell_type":"code","source":"data.to_parquet(\"deposit_1_FE_v1.parquet.gzip\",compression='gzip')","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:48.371262Z","iopub.execute_input":"2024-03-04T07:50:48.371626Z","iopub.status.idle":"2024-03-04T07:50:48.803284Z","shell.execute_reply.started":"2024-03-04T07:50:48.371595Z","shell.execute_reply":"2024-03-04T07:50:48.802061Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## you can read data by below code¶\n\n`pd.read_parquet('/kaggle/working/deposit_1_FE_v1.parquet.gzip')`","metadata":{}},{"cell_type":"code","source":"pd.read_parquet('/kaggle/working/deposit_1_FE_v1.parquet.gzip').head()","metadata":{"execution":{"iopub.status.busy":"2024-03-04T07:50:48.805270Z","iopub.execute_input":"2024-03-04T07:50:48.805810Z","iopub.status.idle":"2024-03-04T07:50:48.922046Z","shell.execute_reply.started":"2024-03-04T07:50:48.805740Z","shell.execute_reply":"2024-03-04T07:50:48.920853Z"},"trusted":true},"execution_count":null,"outputs":[]}]}