{"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\n### In this notebooks, you can know below information：\n\n1. Introduction of Depth_1:`train_applprev_1_*` data\n2. check NAN% of all colums with EDA\n3. feature engineering build new features\n4. EDA new features and description the insight\n5. new feature dataset to download","metadata":{"_kg_hide-output":false}},{"cell_type":"markdown","source":"# Install & Import Package & Read","metadata":{}},{"cell_type":"code","source":"!pip install googletrans==3.1.0a0 --upgrade --quiet","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-03-03T11:13:00.305085Z","iopub.execute_input":"2024-03-03T11:13:00.306018Z","iopub.status.idle":"2024-03-03T11:13:18.107623Z","shell.execute_reply.started":"2024-03-03T11:13:00.305946Z","shell.execute_reply":"2024-03-03T11:13:18.105868Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:13:18.110463Z","iopub.execute_input":"2024-03-03T11:13:18.110953Z","iopub.status.idle":"2024-03-03T11:13:19.934881Z","shell.execute_reply.started":"2024-03-03T11:13:18.110907Z","shell.execute_reply":"2024-03-03T11:13:19.933600Z"},"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\")\ntrain_applprev_1_0 = pd.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_applprev_1_0.csv\")\ntrain_applprev_1_1 = pd.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_applprev_1_1.csv\")\n# train_applprev_2 = pd.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_applprev_2.csv\")\nfd = pd.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/feature_definitions.csv\")","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-03-03T11:13:19.936658Z","iopub.execute_input":"2024-03-03T11:13:19.937258Z","iopub.status.idle":"2024-03-03T11:14:21.271787Z","shell.execute_reply.started":"2024-03-03T11:13:19.937199Z","shell.execute_reply":"2024-03-03T11:14:21.270120Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# combine train_applprev_1_0 + train_applprev_1_1\ndf = pd.concat([train_applprev_1_0,train_applprev_1_1],axis=0).reset_index(drop=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:21.275520Z","iopub.execute_input":"2024-03-03T11:14:21.275998Z","iopub.status.idle":"2024-03-03T11:14:35.275846Z","shell.execute_reply.started":"2024-03-03T11:14:21.275955Z","shell.execute_reply":"2024-03-03T11:14:35.273999Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## We can see that `train_applprev_1_0` + `train_applprev_1_1` below:","metadata":{}},{"cell_type":"code","source":"df","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:35.277814Z","iopub.execute_input":"2024-03-03T11:14:35.278331Z","iopub.status.idle":"2024-03-03T11:14:39.347724Z","shell.execute_reply.started":"2024-03-03T11:14:35.278285Z","shell.execute_reply":"2024-03-03T11:14:39.345817Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"shape of Depth_1: train_applprev_1_0 + train_applprev_1_1 is \", 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))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:39.349363Z","iopub.execute_input":"2024-03-03T11:14:39.349757Z","iopub.status.idle":"2024-03-03T11:14:40.541418Z","shell.execute_reply.started":"2024-03-03T11:14:39.349721Z","shell.execute_reply":"2024-03-03T11:14:40.539776Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* From the analysis so far, it is known that the case_id contained in `train_applprev_1_*` accounts for 80% of the total.\n* About 20% of case_id cannot use `train_applprev_1_*` to generate features, which means that if features are built using `train_applprev_1_*`, there will be 20% of case_id in `train_base` that will be NAN.","metadata":{}},{"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":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:40.542655Z","iopub.execute_input":"2024-03-03T11:14:40.543067Z","iopub.status.idle":"2024-03-03T11:14:42.316882Z","shell.execute_reply.started":"2024-03-03T11:14:40.543034Z","shell.execute_reply":"2024-03-03T11:14:42.315164Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Description example : case_id = **1299172**","metadata":{}},{"cell_type":"code","source":"df.loc[(df['case_id'] == 1299172),['case_id','creationdate_885D','num_group1']].sort_values(['num_group1'])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:42.318718Z","iopub.execute_input":"2024-03-03T11:14:42.319081Z","iopub.status.idle":"2024-03-03T11:14:42.452325Z","shell.execute_reply.started":"2024-03-03T11:14:42.319050Z","shell.execute_reply":"2024-03-03T11:14:42.451384Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* num_group1 = 0 means the latest row, and 19 means the last row\n* some case_id only 1 record, and max is 20 recoreds for each case_id","metadata":{}},{"cell_type":"markdown","source":"## Create a field translation table for easy reference to the meaning of the fields during analysis.","metadata":{"execution":{"iopub.status.busy":"2024-03-03T09:19:23.330400Z","iopub.execute_input":"2024-03-03T09:19:23.331293Z","iopub.status.idle":"2024-03-03T09:19:23.336950Z","shell.execute_reply.started":"2024-03-03T09:19:23.331220Z","shell.execute_reply":"2024-03-03T09:19:23.335546Z"}}},{"cell_type":"code","source":"tmp.head(5)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:42.453919Z","iopub.execute_input":"2024-03-03T11:14:42.454960Z","iopub.status.idle":"2024-03-03T11:14:42.468389Z","shell.execute_reply.started":"2024-03-03T11:14:42.454917Z","shell.execute_reply":"2024-03-03T11:14:42.466960Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Each case_id with not only one row in `train_applprev_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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:42.474347Z","iopub.execute_input":"2024-03-03T11:14:42.474832Z","iopub.status.idle":"2024-03-03T11:14:43.112019Z","shell.execute_reply.started":"2024-03-03T11:14:42.474793Z","shell.execute_reply":"2024-03-03T11:14:43.110555Z"},"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)\n\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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:43.113788Z","iopub.execute_input":"2024-03-03T11:14:43.114177Z","iopub.status.idle":"2024-03-03T11:14:43.809925Z","shell.execute_reply.started":"2024-03-03T11:14:43.114142Z","shell.execute_reply":"2024-03-03T11:14:43.808404Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"1. Since each case_id may have multiple historical records, we calculate the number of data entries (the quantity of num_group1) for each case_id, and observe the proportion of case_id under each quantity of num_group1.\n\n2. Approximately 18% of case_id actually have only one past loan application record. This phenomenon is known to affect the calculated aggregate features, such as mean, max, and min, because they are essentially based on the same single entry.","metadata":{"_kg_hide-input":false}},{"cell_type":"markdown","source":"## NAN percentage in `train_applprev_1_*`","metadata":{"execution":{"iopub.status.busy":"2024-03-02T10:04:47.957344Z","iopub.execute_input":"2024-03-02T10:04:47.959086Z","iopub.status.idle":"2024-03-02T10:04:47.966849Z","shell.execute_reply.started":"2024-03-02T10:04:47.959038Z","shell.execute_reply":"2024-03-02T10:04:47.965313Z"},"_kg_hide-input":false}},{"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:43.811471Z","iopub.execute_input":"2024-03-03T11:14:43.811842Z","iopub.status.idle":"2024-03-03T11:14:56.583868Z","shell.execute_reply.started":"2024-03-03T11:14:43.811809Z","shell.execute_reply":"2024-03-03T11:14:56.582458Z"},"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=90,figsize=(10,4));","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:56.585429Z","iopub.execute_input":"2024-03-03T11:14:56.585786Z","iopub.status.idle":"2024-03-03T11:14:57.760276Z","shell.execute_reply.started":"2024-03-03T11:14:56.585755Z","shell.execute_reply":"2024-03-03T11:14:57.759073Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The chart above shows that out of a total of 40 fields, about 12 have a NAN proportion exceeding 50%, which will have a certain impact on future feature engineering. Therefore, I have selected fields with a NAN proportion < 30% for subsequent analysis and feature calculation.","metadata":{"_kg_hide-input":false}},{"cell_type":"code","source":"preprocess_fea = list(missing_value_df.loc[(missing_value_df['percent_missing']<30) & (missing_value_df['column_name']!=\"case_id\"),\"column_name\"])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:57.761740Z","iopub.execute_input":"2024-03-03T11:14:57.762529Z","iopub.status.idle":"2024-03-03T11:14:57.772372Z","shell.execute_reply.started":"2024-03-03T11:14:57.762469Z","shell.execute_reply":"2024-03-03T11:14:57.771247Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## I'll focus on these columns to build new aggregate features","metadata":{}},{"cell_type":"code","source":"pd.DataFrame({\"Analysis columns\":preprocess_fea})","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:57.774060Z","iopub.execute_input":"2024-03-03T11:14:57.774688Z","iopub.status.idle":"2024-03-03T11:14:57.794779Z","shell.execute_reply.started":"2024-03-03T11:14:57.774650Z","shell.execute_reply":"2024-03-03T11:14:57.793193Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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    # 每個特徵的最近一筆先前紀錄與最遠一筆的先前紀錄的類別，還可以做出現最多次的類別\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']).\\\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":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2024-03-03T11:14:57.796741Z","iopub.execute_input":"2024-03-03T11:14:57.797236Z","iopub.status.idle":"2024-03-03T11:14:57.806526Z","shell.execute_reply.started":"2024-03-03T11:14:57.797197Z","shell.execute_reply":"2024-03-03T11:14:57.804648Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# P：Transform DPD (Days past due)\n\n* actualdpd_943P","metadata":{"_kg_hide-input":false}},{"cell_type":"code","source":"P_fea = ['actualdpd_943P']\ntmp.loc[(tmp['fea_type'] == \"P\") & (tmp['Variable'].isin(P_fea))]","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:14:57.808725Z","iopub.execute_input":"2024-03-03T11:14:57.809121Z","iopub.status.idle":"2024-03-03T11:14:57.834349Z","shell.execute_reply.started":"2024-03-03T11:14:57.809085Z","shell.execute_reply":"2024-03-03T11:14:57.833106Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 上一份合約逾期天數的mean, max, min, std, last\nP_fea_table = applepre_feature_eng(data = df, fealist = P_fea,agglist=['first','max','min','mean','std'])","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2024-03-03T11:14:57.835956Z","iopub.execute_input":"2024-03-03T11:14:57.837065Z","iopub.status.idle":"2024-03-03T11:15:01.384548Z","shell.execute_reply.started":"2024-03-03T11:14:57.837015Z","shell.execute_reply":"2024-03-03T11:15:01.383208Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### P Result Table of Features ","metadata":{}},{"cell_type":"code","source":"P_fea_table.head()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:01.386018Z","iopub.execute_input":"2024-03-03T11:15:01.386412Z","iopub.status.idle":"2024-03-03T11:15:01.408275Z","shell.execute_reply.started":"2024-03-03T11:15:01.386377Z","shell.execute_reply":"2024-03-03T11:15:01.406806Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# M：Masking categories","metadata":{"_kg_hide-input":false}},{"cell_type":"code","source":"M_fea = ['cancelreason_3545846M','district_544M','education_1138M', 'postype_4733339M','profession_152M','rejectreason_755M','rejectreasonclient_4145042M']","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:01.410153Z","iopub.execute_input":"2024-03-03T11:15:01.411233Z","iopub.status.idle":"2024-03-03T11:15:01.420369Z","shell.execute_reply.started":"2024-03-03T11:15:01.411194Z","shell.execute_reply":"2024-03-03T11:15:01.419313Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp.loc[(tmp['fea_type'] == \"M\") & (tmp['Variable'].isin(M_fea))]","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:01.422010Z","iopub.execute_input":"2024-03-03T11:15:01.422435Z","iopub.status.idle":"2024-03-03T11:15:01.445490Z","shell.execute_reply.started":"2024-03-03T11:15:01.422399Z","shell.execute_reply":"2024-03-03T11:15:01.444070Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"M_fea_table = applepre_feature_eng(data = df, fealist = M_fea,agglist=['first'])","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2024-03-03T11:15:01.447440Z","iopub.execute_input":"2024-03-03T11:15:01.447911Z","iopub.status.idle":"2024-03-03T11:15:22.600857Z","shell.execute_reply.started":"2024-03-03T11:15:01.447862Z","shell.execute_reply":"2024-03-03T11:15:22.599289Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### M Result Table of Features ","metadata":{}},{"cell_type":"code","source":"M_fea_table.head()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:22.603818Z","iopub.execute_input":"2024-03-03T11:15:22.604222Z","iopub.status.idle":"2024-03-03T11:15:22.622250Z","shell.execute_reply.started":"2024-03-03T11:15:22.604187Z","shell.execute_reply":"2024-03-03T11:15:22.620775Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\n# A：Transform amount","metadata":{"_kg_hide-input":false}},{"cell_type":"code","source":"A_fea = ['annuity_853A','credacc_credlmt_575A','credamount_590A','downpmt_134A','mainoccupationinc_437A']","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:22.624593Z","iopub.execute_input":"2024-03-03T11:15:22.624956Z","iopub.status.idle":"2024-03-03T11:15:22.641218Z","shell.execute_reply.started":"2024-03-03T11:15:22.624923Z","shell.execute_reply":"2024-03-03T11:15:22.639440Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp.loc[(tmp['fea_type'] == \"A\") & (tmp['Variable'].isin(A_fea))]","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:22.643002Z","iopub.execute_input":"2024-03-03T11:15:22.643483Z","iopub.status.idle":"2024-03-03T11:15:22.664300Z","shell.execute_reply.started":"2024-03-03T11:15:22.643447Z","shell.execute_reply":"2024-03-03T11:15:22.662765Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"A_fea_table = applepre_feature_eng(data = df, fealist = A_fea,agglist=['max','min','mean','first','std'])","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2024-03-03T11:15:22.665707Z","iopub.execute_input":"2024-03-03T11:15:22.666217Z","iopub.status.idle":"2024-03-03T11:15:33.485333Z","shell.execute_reply.started":"2024-03-03T11:15:22.666170Z","shell.execute_reply":"2024-03-03T11:15:33.483639Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### A Result Table of Features ","metadata":{}},{"cell_type":"code","source":"A_fea_table","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:33.487724Z","iopub.execute_input":"2024-03-03T11:15:33.488224Z","iopub.status.idle":"2024-03-03T11:15:33.897799Z","shell.execute_reply.started":"2024-03-03T11:15:33.488177Z","shell.execute_reply":"2024-03-03T11:15:33.896517Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# L : Unspecified Transform","metadata":{"_kg_hide-input":false}},{"cell_type":"code","source":"L_fea = ['credtype_587L','inittransactioncode_279L','isbidproduct_390L','pmtnum_8L','status_219L','tenor_203L']","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:33.904329Z","iopub.execute_input":"2024-03-03T11:15:33.904802Z","iopub.status.idle":"2024-03-03T11:15:33.913084Z","shell.execute_reply.started":"2024-03-03T11:15:33.904768Z","shell.execute_reply":"2024-03-03T11:15:33.911315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp.loc[(tmp['fea_type'] == \"L\") & (tmp['Variable'].isin(L_fea))]","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:33.915402Z","iopub.execute_input":"2024-03-03T11:15:33.915963Z","iopub.status.idle":"2024-03-03T11:15:33.934771Z","shell.execute_reply.started":"2024-03-03T11:15:33.915911Z","shell.execute_reply":"2024-03-03T11:15:33.932450Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## The L feautures include numeric type and category type, so we need to process by different way","metadata":{"execution":{"iopub.status.busy":"2024-03-03T09:35:24.407461Z","iopub.execute_input":"2024-03-03T09:35:24.408112Z","iopub.status.idle":"2024-03-03T09:35:24.414419Z","shell.execute_reply.started":"2024-03-03T09:35:24.408069Z","shell.execute_reply":"2024-03-03T09:35:24.413053Z"}}},{"cell_type":"markdown","source":"## Numeric Type columns\n\n* 'pmtnum_8L'\n* 'tenor_203L'","metadata":{"_kg_hide-input":false}},{"cell_type":"code","source":"L_fea_table_numeric = applepre_feature_eng(data = df, fealist = ['pmtnum_8L','tenor_203L'],agglist=['max','min','mean','first','std'])","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2024-03-03T11:15:33.936563Z","iopub.execute_input":"2024-03-03T11:15:33.937057Z","iopub.status.idle":"2024-03-03T11:15:39.187957Z","shell.execute_reply.started":"2024-03-03T11:15:33.937021Z","shell.execute_reply":"2024-03-03T11:15:39.186553Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### L numeric Result Table of Features ","metadata":{}},{"cell_type":"code","source":"L_fea_table_numeric","metadata":{"execution":{"iopub.status.busy":"2024-03-03T11:15:39.189406Z","iopub.execute_input":"2024-03-03T11:15:39.189812Z","iopub.status.idle":"2024-03-03T11:15:39.221769Z","shell.execute_reply.started":"2024-03-03T11:15:39.189779Z","shell.execute_reply":"2024-03-03T11:15:39.220343Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Catetogory Type columns\n\n* 'credtype_587L'\n* 'inittransactioncode_279L'\n* 'isbidproduct_390L'\n* 'status_219L'","metadata":{"_kg_hide-input":false}},{"cell_type":"code","source":"L_fea_table_cat = applepre_feature_eng(data = df, fealist = ['credtype_587L','inittransactioncode_279L','isbidproduct_390L','status_219L'],agglist=['first'])","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2024-03-03T11:15:39.223452Z","iopub.execute_input":"2024-03-03T11:15:39.224901Z","iopub.status.idle":"2024-03-03T11:15:51.941932Z","shell.execute_reply.started":"2024-03-03T11:15:39.224858Z","shell.execute_reply":"2024-03-03T11:15:51.940299Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# L catetogory Result Table of Features","metadata":{}},{"cell_type":"code","source":"L_fea_table_cat.head()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:51.943830Z","iopub.execute_input":"2024-03-03T11:15:51.944492Z","iopub.status.idle":"2024-03-03T11:15:51.960972Z","shell.execute_reply.started":"2024-03-03T11:15:51.944450Z","shell.execute_reply":"2024-03-03T11:15:51.959571Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feature Engineering Result Table\n\n1. At the end, we join all new feature table to `train_base`\n2. new features data from `train_applprev_1_*` has finished","metadata":{"_kg_hide-input":false}},{"cell_type":"code","source":"data = train_base.\\\n    merge(P_fea_table,on=['case_id'],how = 'left').\\\n    merge(M_fea_table,on=['case_id'],how = 'left').\\\n    merge(A_fea_table,on=['case_id'],how = 'left').\\\n    merge(L_fea_table_numeric,on=['case_id'],how = 'left').\\\n    merge(L_fea_table_cat,on=['case_id'],how = 'left')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:51.962632Z","iopub.execute_input":"2024-03-03T11:15:51.963165Z","iopub.status.idle":"2024-03-03T11:15:57.405195Z","shell.execute_reply.started":"2024-03-03T11:15:51.963118Z","shell.execute_reply":"2024-03-03T11:15:57.403659Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:57.406812Z","iopub.execute_input":"2024-03-03T11:15:57.407809Z","iopub.status.idle":"2024-03-03T11:15:58.119767Z","shell.execute_reply.started":"2024-03-03T11:15:57.407770Z","shell.execute_reply":"2024-03-03T11:15:58.118353Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Are the case_id in train_base that do not exist in `train_applprev_1_*` concentrated in certain WEEK_NUM or MONTH?","metadata":{"execution":{"iopub.status.busy":"2024-03-02T13:50:49.717529Z","iopub.execute_input":"2024-03-02T13:50:49.718255Z","iopub.status.idle":"2024-03-02T13:50:49.724043Z","shell.execute_reply.started":"2024-03-02T13:50:49.718214Z","shell.execute_reply":"2024-03-02T13:50:49.722493Z"},"_kg_hide-input":false}},{"cell_type":"code","source":"data.loc[~data['case_id'].isin(list(set(df.case_id)))].\\\n    groupby(['WEEK_NUM'])['case_id'].\\\n    count().\\\n    reset_index().\\\n    rename(columns={'case_id':'count'}).\\\n    plot.bar(x='WEEK_NUM', y='count',title = 'Not in train_applprev_1_* case_id count distribution by WEEK_NUM', rot=90,figsize=(10,4))\n\ndata.\\\n    groupby(['WEEK_NUM'])['case_id'].\\\n    count().\\\n    reset_index().\\\n    rename(columns={'case_id':'count'}).\\\n    plot.bar(x='WEEK_NUM', y='count',title = 'all case_id count distribution by WEEK_NUM', rot=90,figsize=(10,4))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:15:58.121598Z","iopub.execute_input":"2024-03-03T11:15:58.121996Z","iopub.status.idle":"2024-03-03T11:16:04.158662Z","shell.execute_reply.started":"2024-03-03T11:15:58.121962Z","shell.execute_reply":"2024-03-03T11:16:04.157176Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"From the comparison above, it is known that the remaining case_id in `train_base` are distributed across various WEEK_NUM, with the trend consistent with the training data trend.","metadata":{"_kg_hide-input":false}},{"cell_type":"markdown","source":"# Correlation Analysis","metadata":{"_kg_hide-input":false}},{"cell_type":"code","source":"fea_type_table = (data.loc[:,\"first_actualdpd_943P\":\"first_status_219L\"].dtypes == \"object\").\\\n    to_frame().\\\n    reset_index().\\\n    rename(columns={'index':'feature',0:'is_cat'})","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:16:04.160505Z","iopub.execute_input":"2024-03-03T11:16:04.161267Z","iopub.status.idle":"2024-03-03T11:16:04.591227Z","shell.execute_reply.started":"2024-03-03T11:16:04.161171Z","shell.execute_reply":"2024-03-03T11:16:04.589836Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ind = 0\nfor col in list(fea_type_table.loc[fea_type_table['is_cat']==False,'feature']):\n\n    if ind % 4 == 0:\n        plt.figure(figsize=(16, 4))\n    plt.subplot(1, 4, ind % 4 + 1)\n    \n    sns.histplot(data=data, x=col, hue=\"target\", bins=30)\n    plt.ylabel(\"\")\n    \n    if ind % 4 == 3:\n        plt.show()\n    \n    ind += 1","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:16:04.592828Z","iopub.execute_input":"2024-03-03T11:16:04.593352Z","iopub.status.idle":"2024-03-03T11:17:04.788049Z","shell.execute_reply.started":"2024-03-03T11:16:04.593316Z","shell.execute_reply":"2024-03-03T11:17:04.786986Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"1. **std_actuadlpd_943P** : Relative to other features, this feature shows a narrower distribution when the target variable is 1, indicating that the variability of the data is smaller. This could be a useful signal, as it suggests that the values of this feature become more consistent when the target variable is 1.\n\n2. **mean_annuity_853A** : Despite most of the distribution overlapping, for the target variable 1, this feature seems to have more data points concentrated in the lower value range. This concentration trend might indicate a correlation with the target variable.\n\n3. **std_annuity_853A** : When the target variable is 1, the standard deviation of the annuity is smaller. This might mean that this feature is useful for distinguishing target categories.\n\n4. **min_credacc_credlmt_575A** : The distribution for target 1 is more concentrated in the lower credit limit range. max_credacc_credlmt_575A also shows a more concentrated trend in the lower credit limit range for target 1, which may indicate these features are related to the target variable.\n\n5. **std_credacc_credlmt_575A** : The distribution of this feature for target 1 is relatively narrower, indicating that the variability of credit limits is smaller when the target variable is 1. This could be a useful signal, as it suggests this feature is more consistent when the target variable is 1.\n\n6. **min_credamount_590A** and **mean_credamount_590A** : The distributions of these features show that for target variable 1, data points are mainly concentrated in the lower credit amount range. The distribution of data points for target variable 0 is broader, which may mean these features have lower credit amounts when the target variable is 1.\n\n7. **std_credamount_590A** : This feature shows a narrower distribution for target variable 1 compared to target variable 0, indicating that the variability of credit amounts is smaller in target 1. This narrowing of the distribution may indicate this feature has a strong distinguishing power between target variables 0 and 1.\n\n8. **std_downpmt_134A** : The distribution for target 0 is relatively wide, while for target 1, it is mostly concentrated on lower values. This difference in distribution could imply that a smaller standard deviation of down payments is strongly associated with target 1.\n\n9. **max_mainoccupationinc_437A** and **min_mainoccupationinc_437A**: These features related to main occupation income show different distributions between targets 0 and 1. Especially for min_mainoccupationinc_437A, data points seem more concentrated at target 1, which may indicate a certain correlation between the minimum main occupation income and the target variable.\n\n10. **mean_mainoccupationinc_437A** : The distribution of average main occupation income overlaps between targets 0 and 1, but at target 1, data points seem to be more concentrated in the lower value area. This may indicate that this feature is helpful in distinguishing the target variable.\n\n11. **mean_pmtnum_8L** : This feature shows a more concentrated distribution for the target variable 1, especially in the lower value range with a noticeable peak. This might indicate a strong correlation with the target variable 1 when mean_pmtnum_8L is lower.\n\n12. **std_pmtnum_8L** : The distribution of this feature for target 1 is more concentrated, especially in the lower value range. This indicates a lower standard deviation for target 1, which might mean that situations with a smaller standard deviation are related to the target variable 1.\n\n13. **max_tenor_203L** : The distribution range for target 0 is wider than for target 1, and data points for target 1 seem to be concentrated at lower maximum tenure values. This might indicate that the values for this feature are lower when the target variable is 1.\n\n14. **min_tenor_203L** and **mean_tenor_203L** : These features show overlapping distributions between targets 0 and 1, but the distribution for target 1 is relatively concentrated. This indicates that on these features, the values for target 1 tend to be lower, possibly correlating with the target variable.","metadata":{"execution":{"iopub.status.busy":"2024-03-02T14:43:18.081489Z","iopub.execute_input":"2024-03-02T14:43:18.082047Z","iopub.status.idle":"2024-03-02T14:43:18.091617Z","shell.execute_reply.started":"2024-03-02T14:43:18.082007Z","shell.execute_reply":"2024-03-02T14:43:18.090089Z"},"_kg_hide-input":false,"_kg_hide-output":false}},{"cell_type":"code","source":"ind = 0\nfor col in list(fea_type_table.loc[fea_type_table['is_cat']==True,'feature']):\n    if ind % 2 == 0:\n        plt.figure(figsize=(16, 3))\n    plt.subplot(1, 2, ind % 2 + 1)\n    \n    sns.countplot(data=data, x=col, hue=\"target\")\n    plt.xticks(fontsize=10,rotation=90)\n    plt.ylabel(\"\")\n    \n    if ind % 2 == 1:\n        plt.show()\n    \n    ind += 1","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-03T11:17:04.789872Z","iopub.execute_input":"2024-03-03T11:17:04.790633Z","iopub.status.idle":"2024-03-03T11:18:35.875164Z","shell.execute_reply.started":"2024-03-03T11:17:04.790595Z","shell.execute_reply":"2024-03-03T11:18:35.873503Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"1. **first_rejectreason_755M** : Specific rejection reasons have significantly higher counts under target variable 1 than under target variable 0, indicating that these rejection reasons are strongly associated with target variable 1.\n\n2. **first_rejectreasonclient_4145042M** : Similarly, certain categories have a noticeable increase in counts under target variable 1 compared to target variable 0, which may indicate that these specific rejection reasons are strongly associated with target variable 1.\n\n3. **first_credtype_587L** : For credit types, it appears that some types (such as COL) have higher counts under target variable 1, while other types (such as CAL and REL) have higher counts under target variable 0, suggesting that specific credit types may be related to the target variable.","metadata":{"_kg_hide-input":false,"_kg_hide-output":false}},{"cell_type":"markdown","source":"# Summary\n\n1. The majority of fields actually have a high proportion of NAN, which will have a certain impact on future modeling.\n\n2. Most statistical features cannot be clearly distinguished visually in terms of their impact and differences on the target variable.\n\n3. Some features can be observed to have differences between various targets, for example:\n    * P : `std_actuadlpd_943P`\n    * A : `mean_annuity_853A`, `std_annuity_853A`, `min_credacc_credlmt_575A`, `std_credacc_credlmt_575A`, `min_credamount_590A`, `mean_credamount_590A`, `std_credamount_590A`, `std_downpmt_134A`, `max_mainoccupationinc_437A`, `mean_mainoccupationinc_437A`\n    * L : `mean_pmtnum_8L`, `std_pmtnum_8L`, `max_tenor_203L`, `min_tenor_203L`, `first_credtype_587L`\n    * M : `first_rejectreason_755M`, `first_rejectreasonclient_4145042M`","metadata":{}},{"cell_type":"code","source":"data.to_parquet(\"applprev_1_*_FE_v1.parquet.gzip\",compression='gzip')","metadata":{"execution":{"iopub.status.busy":"2024-03-03T11:18:35.877531Z","iopub.execute_input":"2024-03-03T11:18:35.877950Z","iopub.status.idle":"2024-03-03T11:18:56.807451Z","shell.execute_reply.started":"2024-03-03T11:18:35.877914Z","shell.execute_reply":"2024-03-03T11:18:56.806042Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# you can read data by below code\n`pd.read_parquet('/kaggle/working/applprev_1_*_FE_v1.parquet.gzip')`","metadata":{"execution":{"iopub.status.busy":"2024-03-03T11:10:13.996003Z","iopub.execute_input":"2024-03-03T11:10:13.996692Z","iopub.status.idle":"2024-03-03T11:10:14.006819Z","shell.execute_reply.started":"2024-03-03T11:10:13.996626Z","shell.execute_reply":"2024-03-03T11:10:14.005380Z"}}},{"cell_type":"code","source":"pd.read_parquet('/kaggle/working/applprev_1_*_FE_v1.parquet.gzip').head()","metadata":{"execution":{"iopub.status.busy":"2024-03-03T11:18:56.809512Z","iopub.execute_input":"2024-03-03T11:18:56.811381Z","iopub.status.idle":"2024-03-03T11:18:58.450519Z","shell.execute_reply.started":"2024-03-03T11:18:56.811326Z","shell.execute_reply":"2024-03-03T11:18:58.449166Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# The Next part will focus on `applprev_2` of Depth2","metadata":{}}]}