{"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":"nvidiaTeslaT4","dataSources":[{"sourceId":50160,"databundleVersionId":7602123,"sourceType":"competition"},{"sourceId":163452207,"sourceType":"kernelVersion"}],"dockerImageVersionId":30646,"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":true}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\n# !pip install \nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nfrom sklearn.model_selection import train_test_split\nimport lightgbm as lgb\nimport pickle as pkl\nimport  gc\nimport glob\nfrom tqdm import tqdm \n# !pip install pyspark\n# import pyspark\n# from pyspark.sql import SparkSession\n# spark = SparkSession.builder.appName(\"CreditRiskModeling\").getOrCreate()\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\nfrom dateutil.relativedelta import relativedelta\n\nimport os\n# for dirname, _, filenames in os.walk('/kaggle/input'):\n#     for filename in filenames:\n#         if 'train' in filename:\n#             print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2024-02-19T18:58:58.669216Z","iopub.execute_input":"2024-02-19T18:58:58.669492Z","iopub.status.idle":"2024-02-19T18:59:04.795260Z","shell.execute_reply.started":"2024-02-19T18:58:58.669466Z","shell.execute_reply":"2024-02-19T18:59:04.794378Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# from functools import reduce\n# from pyspark.sql import DataFrame","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:04.798692Z","iopub.execute_input":"2024-02-19T18:59:04.798964Z","iopub.status.idle":"2024-02-19T18:59:04.802688Z","shell.execute_reply.started":"2024-02-19T18:59:04.798940Z","shell.execute_reply":"2024-02-19T18:59:04.801638Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"EXPLORING BASE DATA","metadata":{}},{"cell_type":"code","source":"#Helper functions\ndef reduce_mem_usage(df, int_cast=True, obj_to_category=False, subset=None):\n    \"\"\"\n    Iterate through all the columns of a dataframe and modify the data type to reduce memory usage.\n    :param df: dataframe to reduce (pd.DataFrame)\n    :param int_cast: indicate if columns should be tried to be casted to int (bool)\n    :param obj_to_category: convert non-datetime related objects to category dtype (bool)\n    :param subset: subset of columns to analyse (list)\n    :return: dataset with the column dtypes adjusted (pd.DataFrame)\n    \"\"\"\n    start_mem = df.memory_usage().sum() / 1024 ** 2;\n    gc.collect()\n    print('Memory usage of dataframe is {:.2f} MB'.format(start_mem))\n    \n#     cols_none = subset if subset is  None else df.columns.tolist()\n#     for col_non in tqdm(cols_none):\n#         df[col_non] = df[col_non].fillna(-888)\n    \n    cols = subset if subset is not None else df.columns.tolist()\n\n    for col in tqdm(cols):\n        col_type = df[col].dtype\n\n        if col_type != object and col_type.name != 'category' and 'datetime' not in col_type.name:\n#             df[col] = df[col].fillna(-888)\n            c_min = df[col].min()\n            c_max = df[col].max()\n\n#             # test if column can be converted to an integer\n#             treat_as_int = str(col_type)[:3] == 'int'\n#             if int_cast and not treat_as_int:\n#                 treat_as_int = check_if_integer(df[col])\n                \n            treat_as_int = True\n            if treat_as_int:\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8, errors='ignore')\n                elif c_min > np.iinfo(np.uint8).min and c_max < np.iinfo(np.uint8).max:\n                    df[col] = df[col].astype(np.uint8, errors='ignore')\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16, errors='ignore')\n                elif c_min > np.iinfo(np.uint16).min and c_max < np.iinfo(np.uint16).max:\n                    df[col] = df[col].astype(np.uint16, errors='ignore')\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32, errors='ignore')\n                elif c_min > np.iinfo(np.uint32).min and c_max < np.iinfo(np.uint32).max:\n                    df[col] = df[col].astype(np.uint32, errors='ignore')\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64, errors='ignore')\n                elif c_min > np.iinfo(np.uint64).min and c_max < np.iinfo(np.uint64).max:\n                    df[col] = df[col].astype(np.uint64, errors='ignore')\n            else:\n                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16, errors='ignore')\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32, errors='ignore')\n                else:\n                    df[col] = df[col].astype(np.float64, errors='ignore')\n        elif 'datetime' not in col_type.name and obj_to_category:\n            df[col] = df[col].fillna('Mis')\n            df[col] = df[col].astype('category')\n    gc.collect()\n    end_mem = df.memory_usage().sum() / 1024 ** 2\n    print('Memory usage after optimization is: {:.3f} MB'.format(end_mem))\n    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n\n    return df\n\ndef date_column_depth_0(df):\n    date_columns = ['date_decision'] + [x for x in df.columns if x[-1] == 'D'] \n    df[date_columns] = df[date_columns].apply(pd.to_datetime, errors='coerce')\n    df_diff = df[date_columns].apply(lambda col: (df['date_decision'] - col).dt.days)\n    df_diff.columns = [f'Diff_{col}' for col in df_diff.columns]\n    df = pd.concat([df, df_diff], axis=1)\n    return df\n\n\ndef union_parquest(list_parq):\n    df_list = [reduce_mem_usage(pd.read_parquet(i)) for i in list_parq]\n    union_df = pd.concat(df_list)\n    union_df = reduce_mem_usage(union_df)\n    return union_df   \n\ndef gini(x):\n    total = 0\n    for i, xi in enumerate(x[:-1], 1):\n        total += np.sum(np.abs(xi - x[i:]))\n    return total / (len(x)**2 * np.mean(x))","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:04.804044Z","iopub.execute_input":"2024-02-19T18:59:04.804372Z","iopub.status.idle":"2024-02-19T18:59:04.834237Z","shell.execute_reply.started":"2024-02-19T18:59:04.804330Z","shell.execute_reply":"2024-02-19T18:59:04.833302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# This is the mail function to efficiently read pandas dataframe\nWe need to delete the dataframe as it is merged to keep the memory free, also delete the training data once model has been trained to save memory for big test data","metadata":{}},{"cell_type":"code","source":"def multi_merge(base_data,train_vs_test,data_type):\n    if train_vs_test ==  'train':\n        file_path = train_files_path\n        list_parq =  [file_path + '/' + i for i in os.listdir(file_path) if data_type in i ] \n        \n    elif train_vs_test ==  'test':\n        file_path = test_files_path\n        list_parq =  [file_path + '/' + i for i in os.listdir(file_path) if data_type in i ] \n        \n    df_i_merged = pd.DataFrame()\n    \n    for i in list_parq:\n        print(i)\n        df_i = pd.read_parquet(i)\n        df_i = reduce_mem_usage(df_i)\n        if 'num_group1' in df_i.columns: \n            df_i = df_i[df_i['num_group1'] == 0 ]\n            df_i = df_i.drop(columns = 'num_group1')\n    #         df_i_merged = df_i_merged.merge(df_i,how = 'left',on = 'case_id')\n        df_i_merged = pd.concat([df_i_merged,df_i])\n        del df_i\n        gc.collect()\n    return df_i_merged        ","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:04.838701Z","iopub.execute_input":"2024-02-19T18:59:04.839319Z","iopub.status.idle":"2024-02-19T18:59:04.848012Z","shell.execute_reply.started":"2024-02-19T18:59:04.839290Z","shell.execute_reply":"2024-02-19T18:59:04.847110Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def multi_merge_v2(base_data,train_vs_test,data_type):\n    if train_vs_test ==  'train':\n        file_path = train_files_path\n        list_parq =  [file_path + '/' + i for i in os.listdir(file_path) if data_type in i ] \n        \n    elif train_vs_test ==  'test':\n        file_path = test_files_path\n        list_parq =  [file_path + '/' + i for i in os.listdir(file_path) if data_type in i ] \n        \n    df_i_merged = pd.DataFrame()\n    \n    for i in list_parq:\n        print(i)\n        df_i = pd.read_parquet(i)\n        df_i = reduce_mem_usage(df_i)\n        if 'num_group1' in df_i.columns: \n#             df_i = df_i[df_i['num_group1'] == 0 ]\n            df_i = df_i.drop(columns = 'num_group1')\n    #         df_i_merged = df_i_merged.merge(df_i,how = 'left',on = 'case_id')\n        df_i_merged = pd.concat([df_i_merged,df_i])\n        del df_i\n        gc.collect()\n    return df_i_merged        ","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:04.849138Z","iopub.execute_input":"2024-02-19T18:59:04.849396Z","iopub.status.idle":"2024-02-19T18:59:04.859275Z","shell.execute_reply.started":"2024-02-19T18:59:04.849373Z","shell.execute_reply":"2024-02-19T18:59:04.858436Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def multi_merge_depth_2(base_data,train_vs_test,data_type):\n    if train_vs_test ==  'train':\n        file_path = train_files_path\n        list_parq =  [file_path + '/' + i for i in os.listdir(file_path) if data_type in i ] \n        \n    elif train_vs_test ==  'test':\n        file_path = test_files_path\n        list_parq =  [file_path + '/' + i for i in os.listdir(file_path) if data_type in i ] \n        \n    df_i_merged = pd.DataFrame()\n    \n    for i in list_parq:\n        print(i)\n        df_i = pd.read_parquet(i)\n        df_i = reduce_mem_usage(df_i)\n        if 'num_group1' in df_i.columns: \n            df_i = df_i[df_i['num_group1'] == 0 ]\n            df_i = df_i.select_dtypes(exclude=['object'])\n            df_i = pd.pivot_table(df_i,index = 'case_id', aggfunc= {'max','min'})\n            df_i.columns = [f'{j}_{i}' if j != '' else f'{i}' for i,j in df_i.columns]\n            df_i = df_i.drop(columns = [ i for i in df_i.columns if 'num_group' in i])\n        df_i_merged = pd.concat([df_i_merged,df_i])\n        del df_i\n        gc.collect()\n    return df_i_merged        ","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:04.860401Z","iopub.execute_input":"2024-02-19T18:59:04.860715Z","iopub.status.idle":"2024-02-19T18:59:04.869871Z","shell.execute_reply.started":"2024-02-19T18:59:04.860690Z","shell.execute_reply":"2024-02-19T18:59:04.868909Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Trying depth 2 num group 1(main applicant)","metadata":{}},{"cell_type":"code","source":"train_files_path = '/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/'\ntest_files_path = '/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/'","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:04.871227Z","iopub.execute_input":"2024-02-19T18:59:04.871497Z","iopub.status.idle":"2024-02-19T18:59:04.879929Z","shell.execute_reply.started":"2024-02-19T18:59:04.871474Z","shell.execute_reply":"2024-02-19T18:59:04.879028Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Load Base Data","metadata":{}},{"cell_type":"code","source":"#Train\ntrain_base_df = pd.read_parquet(train_files_path + 'train_base.parquet')\ntrain_base_df = reduce_mem_usage(train_base_df)","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:04.881082Z","iopub.execute_input":"2024-02-19T18:59:04.881401Z","iopub.status.idle":"2024-02-19T18:59:05.507563Z","shell.execute_reply.started":"2024-02-19T18:59:04.881371Z","shell.execute_reply":"2024-02-19T18:59:05.506647Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_merged = train_base_df[['case_id']]\nvariable_type_list = ['train_static_0',\n                      'train_static_cb_0',\n                      'train_applprev_1',\n                      'train_credit_bureau_a_1',\n                     'train_credit_bureau_b_1',\n                     'train_debitcard_1',\n                     'train_deposit_1',\n                     'train_person_1',\n                     'train_tax_registry_a_1',\n                     'train_tax_registry_b_1',\n                     'train_tax_registry_c_1']\nfor k in variable_type_list:\n    df_k = multi_merge(train_base_df,'train',k)\n    df_merged = df_merged.merge(df_k,how = 'outer',on = 'case_id')\n    del df_k\n    gc.collect()\n    \n    \n#Merge with Base\ndf_merged_train_depth_1_0 = train_base_df.merge(df_merged,how = 'left',on = 'case_id')\ndel df_merged\n#Convert date columns to difference\ndate_columns_train_depth_1_0 = [x for x in df_merged_train_depth_1_0.columns if x[-1] == 'D']\ndf_merged_train_depth_1_0 = date_column_depth_0(df_merged_train_depth_1_0)\n\n\ndf_merged_train_depth_1_0 = df_merged_train_depth_1_0.drop(columns = date_columns_train_depth_1_0)\ngc.collect()\n\ndf_merged_train_depth_1_0.to_parquet('/kaggle/working/df_merged_train_depth_1_0.parquet')\ndel df_merged_train_depth_1_0\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-02-19T16:52:43.397650Z","iopub.execute_input":"2024-02-19T16:52:43.398105Z","iopub.status.idle":"2024-02-19T16:57:24.565124Z","shell.execute_reply.started":"2024-02-19T16:52:43.398066Z","shell.execute_reply":"2024-02-19T16:57:24.563984Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_merged = train_base_df[['case_id']]\ndf_k = multi_merge(df_merged,'train','train_credit_bureau_a_2')\n\n\ncredit_bureau_b_2 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_credit_bureau_b_2.parquet')\n# credit_bureau_b_2 = credit_bureau_b_2[credit_bureau_b_2['num_group1'] == 0 ]\ncredit_bureau_b_2 = credit_bureau_b_2.rename(columns = {'pmts_date_1107D':'record_date','pmts_dpdvalue_108P':'max_dpd'})\ncredit_bureau_b_2 = credit_bureau_b_2.dropna(subset = 'record_date')\ncredit_bureau_b_2 = credit_bureau_b_2[['case_id','record_date','max_dpd']]\ncredit_bureau_b_2['record_date'] = pd.to_datetime(credit_bureau_b_2['record_date'])\n\n\n\n# df_k = df_k[df_k['num_group1'] == 0 ]\ndf_k = df_k.select_dtypes(exclude=['object'])\n#Get max record date\ndf_k['record_date'] = pd.to_datetime(df_k[['pmts_year_1139T', 'pmts_year_507T']].max(axis = 1).astype('Int64').astype(str) +  '-'  +df_k[['pmts_month_158T', 'pmts_month_706T']].max(axis = 1).astype('Int64').astype(str) +  '-' +  '1',errors= 'coerce')\n#Get max dpd\ndf_k['max_dpd'] = df_k[['pmts_dpd_1073P', 'pmts_dpd_303P']].max(axis = 1)\ndf_k = df_k[['case_id','record_date','max_dpd']]\ndf_k = pd.concat([df_k,credit_bureau_b_2],axis = 0)\n#Merge with base\ndf_k_merged = train_base_df[['case_id','date_decision']].merge(df_k[['case_id','record_date','max_dpd']], how = 'inner', on ='case_id')\n#Delete df_k\ndel df_k\ngc.collect()\n\n\ndf_k_merged['date_decision'] = pd.to_datetime(df_k_merged['date_decision'])\ndf_k_merged = df_k_merged.assign(\n    time_diff=\n    (df_k_merged.date_decision.dt.year - df_k_merged.record_date.dt.year) * 12 +\n    (df_k_merged.date_decision.dt.month - df_k_merged.record_date.dt.month)\n)\ndf_k_merged = df_k_merged[df_k_merged['time_diff'] >= 0]\ndf_k_merged['max_dpd'] = df_k_merged['max_dpd'].fillna(0)\n# df_k_merged.loc[(df_k_merged['time_diff'] > 0) & (df_k_merged['time_diff'] <= 3),'time_diff_cat'] = '0_3_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 3) & (df_k_merged['time_diff'] <= 6),'time_diff_cat'] = '3_6_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 6) & (df_k_merged['time_diff'] <= 9),'time_diff_cat'] = '6_9_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 9) & (df_k_merged['time_diff'] <= 12),'time_diff_cat'] = '9_12_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 12) & (df_k_merged['time_diff'] <= 18),'time_diff_cat'] = '12_18_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 18) & (df_k_merged['time_diff'] <= 24),'time_diff_cat'] = '18_24_months'\n\n# df_k_merged.loc[(df_k_merged['time_diff'] > 0) & (df_k_merged['time_diff'] <= 3),'time_diff_cat'] = '0_3_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 3) & (df_k_merged['time_diff'] <= 6),'time_diff_cat'] = '3_6_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 6) & (df_k_merged['time_diff'] <= 9),'time_diff_cat'] = '6_9_months'\ndf_k_merged.loc[(df_k_merged['time_diff'] > 0) & (df_k_merged['time_diff'] <= 6),'time_diff_cat'] = '0_6_months'\ndf_k_merged.loc[(df_k_merged['time_diff'] > 0) & (df_k_merged['time_diff'] <= 12),'time_diff_cat'] = '0_12_months'\ndf_k_merged.loc[(df_k_merged['time_diff'] > 0) & (df_k_merged['time_diff'] <= 24),'time_diff_cat'] = '0_24_months'\ndf_k_merged.loc[(df_k_merged['time_diff'] > 24),'time_diff_cat'] = '24_months'\ndf_k_pivot = pd.pivot_table(df_k_merged,index = 'case_id', columns = 'time_diff_cat',values = 'max_dpd',aggfunc= {'max','min','mean','median'})\ndel df_k_merged\ngc.collect()\n\ndf_k_pivot.columns = [f'{i}_{j}' if j != '' else f'{i}' for i,j in df_k_pivot.columns]\ndf_k_pivot = df_k_pivot.fillna(0)\n","metadata":{"execution":{"iopub.status.busy":"2024-02-19T16:57:24.567619Z","iopub.execute_input":"2024-02-19T16:57:24.568155Z","iopub.status.idle":"2024-02-19T17:02:42.592146Z","shell.execute_reply.started":"2024-02-19T16:57:24.568124Z","shell.execute_reply":"2024-02-19T17:02:42.591231Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_k_pivot.to_parquet('/kaggle/working/df_merged_train_depth_2_v2.parquet')\ndel df_k_pivot\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:05.508867Z","iopub.execute_input":"2024-02-19T18:59:05.509451Z","iopub.status.idle":"2024-02-19T18:59:06.001661Z","shell.execute_reply.started":"2024-02-19T18:59:05.509416Z","shell.execute_reply":"2024-02-19T18:59:05.999763Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# df_merged = train_base_df[['case_id']]\n# variable_type_list = ['train_credit_bureau_a_2',\n#                      'train_credit_bureau_b_2']\n\n# for k in variable_type_list:\n#     df_k = multi_merge_depth_2(train_base_df,'train',k)\n#     df_merged = df_merged.merge(df_k,how = 'outer',on = 'case_id')\n#     del df_k\n#     gc.collect()\n    \n    \n# #Merge with Base\n# df_merged_train_depth_2 = train_base_df.merge(df_merged,how = 'left',on = 'case_id')\n# df_merged_train_depth_2 = df_merged_train_depth_2.drop(columns = ['date_decision','MONTH','WEEK_NUM','target'])\n# del df_merged\n# #Convert date columns to difference\n# # date_columns_train_depth_2 = [x for x in df_merged_train_depth_2.columns if x[-1] == 'D']\n# # df_merged_train = date_column_depth_0(df_merged_train)\n\n\n# # df_merged_train = df_merged_train.drop(columns = date_columns_train_depth_2)\n# gc.collect()\n\n# df_merged_train_depth_2.to_parquet('/kaggle/working/df_merged_train_depth_2.parquet')\n# del df_merged_train_depth_2\n# gc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:06.002431Z","iopub.status.idle":"2024-02-19T18:59:06.002782Z","shell.execute_reply.started":"2024-02-19T18:59:06.002615Z","shell.execute_reply":"2024-02-19T18:59:06.002631Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_merged_train_depth_1_0 = pd.read_parquet('/kaggle/working/df_merged_train_depth_1_0.parquet')\ndf_merged_train_depth_2 = pd.read_parquet('/kaggle/working/df_merged_train_depth_2_v2.parquet')\ndf_merged_train_depth_1_0 = reduce_mem_usage(df_merged_train_depth_1_0)\ndf_merged_train_depth_2 = reduce_mem_usage(df_merged_train_depth_2)\ndf_merged_train = df_merged_train_depth_1_0.merge(df_merged_train_depth_2,how = 'left',on=  'case_id')\ndel df_merged_train_depth_1_0\ndel df_merged_train_depth_2\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:26.717548Z","iopub.execute_input":"2024-02-19T18:59:26.717962Z","iopub.status.idle":"2024-02-19T18:59:48.673208Z","shell.execute_reply.started":"2024-02-19T18:59:26.717932Z","shell.execute_reply":"2024-02-19T18:59:48.672297Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Fill Missialue\nnum_cols = df_merged_train.select_dtypes(include=np.number).columns\ndf_merged_train[num_cols] = df_merged_train[num_cols].fillna(0)\n\nobject_cols = df_merged_train.select_dtypes(include='object').columns\ndf_merged_train[object_cols] = df_merged_train[object_cols].fillna('Mis')\ndf_merged_train = df_merged_train.drop_duplicates(subset= 'case_id')    \n    \n#Reindexing\nidentifier_cols = ['date_decision','MONTH']\ntarget = 'target'\n# Reindex\ndf_merged_train = df_merged_train.set_index(['case_id','WEEK_NUM']) ","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:59:48.675184Z","iopub.execute_input":"2024-02-19T18:59:48.675822Z","iopub.status.idle":"2024-02-19T19:00:30.569785Z","shell.execute_reply.started":"2024-02-19T18:59:48.675771Z","shell.execute_reply":"2024-02-19T19:00:30.568856Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Columsn giving errors in test set\n# df_merged_train =  df_merged_train.drop(columns = ['std_collater_valueofguarantee_1124L', 'std_collater_valueofguarantee_876L', 'std_pmts_dpdvalue_108P', 'std_pmts_pmtsoverdue_635A']) ","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:00:30.570930Z","iopub.execute_input":"2024-02-19T19:00:30.571230Z","iopub.status.idle":"2024-02-19T19:00:30.575382Z","shell.execute_reply.started":"2024-02-19T19:00:30.571204Z","shell.execute_reply":"2024-02-19T19:00:30.574434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# #Remove covid data\n# covid_weeks = list(np.arange(54,64))\n# df_merged_train = df_merged_train[~df_merged_train.index.isin(covid_weeks,level = 1)]","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:00:30.578079Z","iopub.execute_input":"2024-02-19T19:00:30.578775Z","iopub.status.idle":"2024-02-19T19:00:30.585321Z","shell.execute_reply.started":"2024-02-19T19:00:30.578740Z","shell.execute_reply":"2024-02-19T19:00:30.584400Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"selected_columns = ['date_decision', 'MONTH', 'target','Diff_dateofcredstart_739D',\n'price_1097A',\n'pmtnum_254L',\n'Diff_birth_259D',\n'numberofoverdueinstlmax_1039L',\n'mobilephncnt_593L',\n'amount_4527230A',\n'annuity_780A',\n'totaloutstanddebtvalue_39A',\n'Diff_dateofbirth_337D',\n'pmtscount_423L',\n'isbidproduct_1095L',\n'residualamount_856A',\n'credamount_770A',\n'debtoutstand_525A',\n'cntpmts24_3658933L',\n'currdebt_22A',\n'numberofcontrsvalue_358L',\n'maxdbddpdtollast12m_3658940P',\n'pmtssum_45A',\n'pmtaverage_3A',\n'cntincpaycont9m_3716944L',\n'max_0_24_months',\n'dpdmax_139P',\n'eir_270L',\n'totaldebtoverduevalue_178A',\n'totalamount_996A',\n'Diff_lastrejectdate_50D',\n'days90_310L',\n'overdueamountmax_155A',\n'totalamount_6A']","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:00:30.586405Z","iopub.execute_input":"2024-02-19T19:00:30.586715Z","iopub.status.idle":"2024-02-19T19:00:30.594416Z","shell.execute_reply.started":"2024-02-19T19:00:30.586686Z","shell.execute_reply":"2024-02-19T19:00:30.593624Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_merged_train = df_merged_train[selected_columns]","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:00:30.595455Z","iopub.execute_input":"2024-02-19T19:00:30.597277Z","iopub.status.idle":"2024-02-19T19:00:31.165450Z","shell.execute_reply.started":"2024-02-19T19:00:30.597251Z","shell.execute_reply":"2024-02-19T19:00:31.164506Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Define X,y\nX = df_merged_train.drop(columns = identifier_cols + [target])\nX = X.select_dtypes(exclude=['object'])\ny = df_merged_train['target']\n#Delete data\ndel df_merged_train\ngc.collect()\n#Pick some weeks from starting and some weeks from end as OOT\noot_weeks = [0,  1,  2,  3, \n                        48, 49, 50, 51, 52,\n                        87, 88, 89,90, 91]\n#oot df\nX_oot = X[X.index.isin(oot_weeks,level = 1)]\ny_oot = y[y.index.isin(oot_weeks,level = 1)]\n\n#training df\nX = X[~X.index.isin(oot_weeks,level = 1)]\ny = y[~y.index.isin(oot_weeks,level = 1)]\n\n\n#Train test split(stratified with WEEK_NUM in index 1)\nX_train, X_val, y_train, y_val = train_test_split(X, y, test_size=0.25, stratify= list(X.index.get_level_values(1)) , random_state=42)\nX_val, X_test, y_val, y_test = train_test_split(X_val, y_val,stratify= list(X_val.index.get_level_values(1)) ,test_size=0.50, random_state=42)\n#delete\ndel X,y\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:00:31.166639Z","iopub.execute_input":"2024-02-19T19:00:31.166932Z","iopub.status.idle":"2024-02-19T19:00:33.183082Z","shell.execute_reply.started":"2024-02-19T19:00:31.166908Z","shell.execute_reply":"2024-02-19T19:00:33.182208Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndef gini_stability(base, score_col=\"score\", w_fallingrate=88.0, w_resstd=-0.5):\n    gini_in_time = base.loc[:, [\"WEEK_NUM\", \"target\", score_col]]\\\n        .sort_values(\"WEEK_NUM\")\\\n        .groupby(\"WEEK_NUM\")[[\"target\", score_col]]\\\n        .apply(lambda x: 2*roc_auc_score(x[\"target\"], x[score_col])-1).tolist()\n    \n    x = np.arange(len(gini_in_time))\n    y = gini_in_time\n    a, b = np.polyfit(x, y, 1)\n    y_hat = a*x + b\n    residuals = y - y_hat\n    res_std = np.std(residuals)\n    avg_gini = np.mean(gini_in_time)\n    return avg_gini + w_fallingrate * min(0, a) + w_resstd * res_std","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:00:33.184276Z","iopub.execute_input":"2024-02-19T19:00:33.184581Z","iopub.status.idle":"2024-02-19T19:00:33.191452Z","shell.execute_reply.started":"2024-02-19T19:00:33.184556Z","shell.execute_reply":"2024-02-19T19:00:33.190542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# params= {\n#     \"boosting_type\": \"gbdt\",\n#     \"objective\": \"binary\",\n#     \"metric\": 'roc',\n# #     \"eval_metric\": 'roc',\n#     \"max_depth\": 3,\n#     \"learning_rate\": 0.1,\n#     \"n_estimators\": 1000,\n#     \"colsample_bytree\": 0.4, \n#     \"colsample_bynode\": 0.4,\n# #     \"verbose\": 1,\n#     \"random_state\": 42,\n#     \"device\": \"gpu\",\n    \n# #     \"early_stopping_round\": 100\n# }\n\n  \nparams = {\n    'objective': 'binary',\n    'boosting_type': 'gbdt',\n    'metric': 'roc',\n    \"max_depth\": 4,\n    'learning_rate': 0.1,\n    'num_leaves': 31,\n    'min_child_samples': 5,  # Minimum number of data in one leaf (regularization parameter)\n    'min_child_weight': 0.001,  # Minimum sum of instance Hessian to make a child (regularization parameter)\n    'min_split_gain': 0.01,  # Minimum loss reduction to make a split (regularization parameter)\n    'reg_alpha': 0.5,  # L1 regularization term (regularization parameter)\n    'reg_lambda': 0.5,  # L2 regularization term (regularization parameter)\n    'n_estimators': 1000,  # Number of boosting rounds\n    'random_state': 42,\n    \"device\": \"gpu\",\n    \"colsample_bytree\": 0.4, \n    \"colsample_bynode\": 0.4,\n}\n\nmodel = lgb.LGBMClassifier(**params)\nmodel.fit(\n    X_train, y_train,\n    eval_set=[(X_val, y_val)])","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:03:51.734773Z","iopub.execute_input":"2024-02-19T19:03:51.735148Z","iopub.status.idle":"2024-02-19T19:04:51.883790Z","shell.execute_reply.started":"2024-02-19T19:03:51.735120Z","shell.execute_reply":"2024-02-19T19:04:51.882796Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# import xgboost as xgb\n\n\n# model = xgb.XGBClassifier(\n\n# learning_rate =0.5,\n# n_estimators=1000,\n# max_depth=3,\n# min_child_weight=1,\n# gamma=0,\n# subsample=0.4,\n# colsample_bytree=0.4,\n# objective= 'binary:logistic',\n# nthread=4,\n# scale_pos_weight=1,\n# eval_metric = 'auc',\n# device =  \"cpu\",\n# early_stopping_rounds= 100,\n# seed=27)\n# # eval_set=[(X_val, y_val)])\n\n# model.fit(\n#     X_train, y_train,\n#     eval_set=[(X_val, y_val)])\n","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:04:55.381398Z","iopub.execute_input":"2024-02-19T19:04:55.382181Z","iopub.status.idle":"2024-02-19T19:04:55.388041Z","shell.execute_reply.started":"2024-02-19T19:04:55.382148Z","shell.execute_reply":"2024-02-19T19:04:55.386956Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fi_imp = pd.DataFrame([model.feature_name_,model.feature_importances_],index= ['F','FI']).T","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:04:55.782730Z","iopub.execute_input":"2024-02-19T19:04:55.783554Z","iopub.status.idle":"2024-02-19T19:04:55.790459Z","shell.execute_reply.started":"2024-02-19T19:04:55.783521Z","shell.execute_reply":"2024-02-19T19:04:55.789468Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fi_imp.sort_values('FI',ascending= False).head(50)","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:04:56.333202Z","iopub.execute_input":"2024-02-19T19:04:56.334177Z","iopub.status.idle":"2024-02-19T19:04:56.348362Z","shell.execute_reply.started":"2024-02-19T19:04:56.334138Z","shell.execute_reply":"2024-02-19T19:04:56.347125Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# with open('/kaggle/working/base_line_lgbm.pkl', 'wb') as fp:\n#     pkl.dump(model, fp)","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:05:08.593228Z","iopub.execute_input":"2024-02-19T19:05:08.593848Z","iopub.status.idle":"2024-02-19T19:05:08.597979Z","shell.execute_reply.started":"2024-02-19T19:05:08.593814Z","shell.execute_reply":"2024-02-19T19:05:08.596979Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Predictions\ny_train_pred = model.predict_proba(X_train)[:,1] \ny_val_pred = model.predict_proba(X_val)[:,1]\ny_test_pred = model.predict_proba(X_test)[:,1]\ny_oot_pred = model.predict_proba(X_oot)[:,1]","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:05:09.094895Z","iopub.execute_input":"2024-02-19T19:05:09.095237Z","iopub.status.idle":"2024-02-19T19:05:54.209152Z","shell.execute_reply.started":"2024-02-19T19:05:09.095210Z","shell.execute_reply":"2024-02-19T19:05:54.208100Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Ginni cofficient and stability of model across weeks","metadata":{}},{"cell_type":"code","source":"from sklearn.metrics import roc_auc_score\ndef predict_df(X,y):\n    preds = model.predict_proba(X)[:,1] \n    pred_df = pd.DataFrame(preds,columns = ['predict_proba'],index = X.index)\n    \n# #     pred_df.merge(y,how = 'left',on)\n# #     pred_df['Deciles'] = pd.qcut(train_pred['predict_proba'],q=10,labels = False)  \n#     pred_df = pred_df.set_index(['case_id','WEEK_NUM'])\n    pred_df = pred_df.merge(y,how= 'left',left_index = True,right_index = True) \n    pred_df = pred_df.reset_index(level = 1)\n    return pred_df\n\n\n\n\ndef gini_stability(base, score_col=\"score\", w_fallingrate=88.0, w_resstd=-0.5):\n    gini_in_time = base.loc[:, [\"WEEK_NUM\", \"target\", score_col]]\\\n        .sort_values(\"WEEK_NUM\")\\\n        .groupby(\"WEEK_NUM\")[[\"target\", score_col]]\\\n        .apply(lambda x: 2*roc_auc_score(x[\"target\"], x[score_col])-1).tolist()\n    \n    x = np.arange(len(gini_in_time))\n    y = gini_in_time\n    a, b = np.polyfit(x, y, 1)\n    y_hat = a*x + b\n    residuals = y - y_hat\n    res_std = np.std(residuals)\n    avg_gini = np.mean(gini_in_time)\n    return avg_gini + w_fallingrate * min(0, a) + w_resstd * res_std\n    ","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:05:54.210953Z","iopub.execute_input":"2024-02-19T19:05:54.211453Z","iopub.status.idle":"2024-02-19T19:05:54.221107Z","shell.execute_reply.started":"2024-02-19T19:05:54.211419Z","shell.execute_reply":"2024-02-19T19:05:54.220189Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Gini Stability","metadata":{}},{"cell_type":"code","source":"#Train\ntrain_predict_df = predict_df(X_train,y_train)\ntrain_gini_stability = gini_stability(train_predict_df, score_col=\"predict_proba\", w_fallingrate=88.0, w_resstd=-0.5)\n\n\n#Val\nval_predict_df = predict_df(X_val,y_val)\nval_gini_stability = gini_stability(val_predict_df, score_col=\"predict_proba\", w_fallingrate=88.0, w_resstd=-0.5)\n\n#Test\ntest_predict_df = predict_df(X_test,y_test)\ntest_gini_stability = gini_stability(test_predict_df, score_col=\"predict_proba\", w_fallingrate=88.0, w_resstd=-0.5)\n\n#Oot\noot_predict_df = predict_df(X_oot,y_oot)\noot_gini_stability = gini_stability(oot_predict_df, score_col=\"predict_proba\", w_fallingrate=88.0, w_resstd=-0.5)","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:05:54.222037Z","iopub.execute_input":"2024-02-19T19:05:54.222268Z","iopub.status.idle":"2024-02-19T19:06:40.411846Z","shell.execute_reply.started":"2024-02-19T19:05:54.222247Z","shell.execute_reply":"2024-02-19T19:06:40.410732Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(train_gini_stability)\nprint(val_gini_stability)\nprint(test_gini_stability)\nprint(oot_gini_stability)","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:06:40.414442Z","iopub.execute_input":"2024-02-19T19:06:40.414746Z","iopub.status.idle":"2024-02-19T19:06:40.419643Z","shell.execute_reply.started":"2024-02-19T19:06:40.414719Z","shell.execute_reply":"2024-02-19T19:06:40.418619Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"roc_auc_train = roc_auc_score(y_train,y_train_pred)\nroc_auc_val = roc_auc_score(y_val,y_val_pred)\nroc_auc_test = roc_auc_score(y_test,y_test_pred)\nroc_auc_oot = roc_auc_score(y_oot,y_oot_pred)\n\n# Ginni\nginni_train = roc_auc_train * 2 - 1\nginni_val = roc_auc_val * 2 - 1\nginni_test = roc_auc_test * 2 - 1\nginni_oot = roc_auc_oot * 2 -1","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:03:34.133650Z","iopub.execute_input":"2024-02-19T19:03:34.134023Z","iopub.status.idle":"2024-02-19T19:03:34.712165Z","shell.execute_reply.started":"2024-02-19T19:03:34.133996Z","shell.execute_reply":"2024-02-19T19:03:34.711264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(roc_auc_train)\nprint(roc_auc_val)\nprint(roc_auc_test)\nprint(roc_auc_oot)\n\nprint(ginni_train)\nprint(ginni_val)\nprint(ginni_test)\nprint(ginni_oot)","metadata":{"execution":{"iopub.status.busy":"2024-02-19T19:03:34.713969Z","iopub.execute_input":"2024-02-19T19:03:34.714709Z","iopub.status.idle":"2024-02-19T19:03:34.720741Z","shell.execute_reply.started":"2024-02-19T19:03:34.714671Z","shell.execute_reply":"2024-02-19T19:03:34.719647Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"#Model features\nmodel_columns = model.feature_name_","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:00:11.571613Z","iopub.execute_input":"2024-02-19T18:00:11.572018Z","iopub.status.idle":"2024-02-19T18:00:11.577449Z","shell.execute_reply.started":"2024-02-19T18:00:11.571989Z","shell.execute_reply":"2024-02-19T18:00:11.576370Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Delete training data\ndel X_train,X_val,X_test,y_train,y_val,y_test\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:00:13.163516Z","iopub.execute_input":"2024-02-19T18:00:13.163947Z","iopub.status.idle":"2024-02-19T18:00:13.365168Z","shell.execute_reply.started":"2024-02-19T18:00:13.163897Z","shell.execute_reply":"2024-02-19T18:00:13.363791Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# %who","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:00:28.766790Z","iopub.execute_input":"2024-02-19T18:00:28.767193Z","iopub.status.idle":"2024-02-19T18:00:28.771865Z","shell.execute_reply.started":"2024-02-19T18:00:28.767162Z","shell.execute_reply":"2024-02-19T18:00:28.770716Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Test\ntest_base_df = pd.read_parquet(test_files_path + 'test_base.parquet')\ntest_base_df = reduce_mem_usage(test_base_df)\n\ndf_merged = test_base_df[['case_id']]\nvariable_type_list = ['test_static_0',\n                      'test_static_cb_0',\n                      'test_applprev_1',\n                      'test_credit_bureau_a_1',\n                     'test_credit_bureau_b_1',\n                     'test_debitcard_1',\n                     'test_deposit_1',\n                     'test_person_1',\n                     'test_tax_registry_a_1',\n                     'test_tax_registry_b_1',\n                     'test_tax_registry_c_1']\n\nfor k in variable_type_list:\n    df_k = multi_merge(test_base_df,'test',k)\n    df_merged = df_merged.merge(df_k,how = 'outer',on = 'case_id')\n    del df_k\n    gc.collect()\n    \n    \n#Merge with Base\ndf_merged_test_depth_1_0 = test_base_df.merge(df_merged,how = 'left',on = 'case_id')\ndel df_merged\n#Convert date columns to difference\ndate_columns_test_depth_1_0 = [x for x in df_merged_test_depth_1_0.columns if x[-1] == 'D']\ndf_merged_test_depth_1_0 = date_column_depth_0(df_merged_test_depth_1_0)\n\n\ndf_merged_test_depth_1_0 = df_merged_test_depth_1_0.drop(columns = date_columns_test_depth_1_0)\ngc.collect()\n\n# df_merged_test_depth_1_0.to_parquet('/kaggle/working/df_merged_test_depth_1_0.parquet')\n# del df_merged_test_depth_1_0\n# gc.collect()\n\n","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:01:39.399806Z","iopub.execute_input":"2024-02-19T18:01:39.400546Z","iopub.status.idle":"2024-02-19T18:01:54.876773Z","shell.execute_reply.started":"2024-02-19T18:01:39.400488Z","shell.execute_reply":"2024-02-19T18:01:54.875489Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_merged = test_base_df[['case_id']]\ndf_k = multi_merge(df_merged,'test','test_credit_bureau_a_2')","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:01:54.878591Z","iopub.execute_input":"2024-02-19T18:01:54.878938Z","iopub.status.idle":"2024-02-19T18:02:01.796112Z","shell.execute_reply.started":"2024-02-19T18:01:54.878896Z","shell.execute_reply":"2024-02-19T18:02:01.794835Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_merged = test_base_df[['case_id']]\ndf_k = multi_merge(df_merged,'test','test_credit_bureau_a_2')\n\n\ncredit_bureau_b_2 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_credit_bureau_b_2.parquet')\ncredit_bureau_b_2 = credit_bureau_b_2[credit_bureau_b_2['num_group1'] == 0 ]\ncredit_bureau_b_2 = credit_bureau_b_2.rename(columns = {'pmts_date_1107D':'record_date','pmts_dpdvalue_108P':'max_dpd'})\ncredit_bureau_b_2 = credit_bureau_b_2.dropna(subset = 'record_date')\ncredit_bureau_b_2 = credit_bureau_b_2[['case_id','record_date','max_dpd']]\ncredit_bureau_b_2['record_date'] = pd.to_datetime(credit_bureau_b_2['record_date'])\n\n\n\n# df_k = df_k[df_k['num_group1'] == 0 ]\ndf_k = df_k.select_dtypes(exclude=['object'])\n#Get max record date\ndf_k['record_date'] = pd.to_datetime(df_k[['pmts_year_1139T', 'pmts_year_507T']].max(axis = 1).astype('Int64').astype(str) +  '-'  +df_k[['pmts_month_158T', 'pmts_month_706T']].max(axis = 1).astype('Int64').astype(str) +  '-' +  '1',errors= 'coerce')\n#Get max dpd\ndf_k['max_dpd'] = df_k[['pmts_dpd_1073P', 'pmts_dpd_303P']].max(axis = 1)\ndf_k = df_k[['case_id','record_date','max_dpd']]\ndf_k = pd.concat([df_k,credit_bureau_b_2],axis = 0)\n#Merge with base\ndf_k_merged = test_base_df[['case_id','date_decision']].merge(df_k[['case_id','record_date','max_dpd']], how = 'inner', on ='case_id')\ndel df_k\ngc.collect()\n\ndf_k_merged['date_decision'] = pd.to_datetime(df_k_merged['date_decision'])\ndf_k_merged = df_k_merged.assign(\n    time_diff=\n    (df_k_merged.date_decision.dt.year - df_k_merged.record_date.dt.year) * 12 +\n    (df_k_merged.date_decision.dt.month - df_k_merged.record_date.dt.month)\n)\ndf_k_merged = df_k_merged[df_k_merged['time_diff'] >= 0]\ndf_k_merged['max_dpd'] = df_k_merged['max_dpd'].fillna(0)\n# df_k_merged.loc[(df_k_merged['time_diff'] > 0) & (df_k_merged['time_diff'] <= 3),'time_diff_cat'] = '0_3_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 3) & (df_k_merged['time_diff'] <= 6),'time_diff_cat'] = '3_6_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 6) & (df_k_merged['time_diff'] <= 9),'time_diff_cat'] = '6_9_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 9) & (df_k_merged['time_diff'] <= 12),'time_diff_cat'] = '9_12_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 12) & (df_k_merged['time_diff'] <= 18),'time_diff_cat'] = '12_18_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 18) & (df_k_merged['time_diff'] <= 24),'time_diff_cat'] = '18_24_months'\n\n# df_k_merged.loc[(df_k_merged['time_diff'] > 0) & (df_k_merged['time_diff'] <= 3),'time_diff_cat'] = '0_3_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 3) & (df_k_merged['time_diff'] <= 6),'time_diff_cat'] = '3_6_months'\n# df_k_merged.loc[(df_k_merged['time_diff'] > 6) & (df_k_merged['time_diff'] <= 9),'time_diff_cat'] = '6_9_months'\ndf_k_merged.loc[(df_k_merged['time_diff'] > 0) & (df_k_merged['time_diff'] <= 6),'time_diff_cat'] = '0_6_months'\ndf_k_merged.loc[(df_k_merged['time_diff'] > 0) & (df_k_merged['time_diff'] <= 12),'time_diff_cat'] = '0_12_months'\ndf_k_merged.loc[(df_k_merged['time_diff'] > 0) & (df_k_merged['time_diff'] <= 24),'time_diff_cat'] = '0_24_months'\ndf_k_merged.loc[(df_k_merged['time_diff'] > 24),'time_diff_cat'] = '24_months'\ndf_k_pivot = pd.pivot_table(df_k_merged,index = 'case_id', columns = 'time_diff_cat',values = 'max_dpd',aggfunc= {'max','min','mean','median'})\ndel df_k_merged\ngc.collect()\n\ndf_k_pivot.columns = [f'{i}_{j}' if j != '' else f'{i}' for i,j in df_k_pivot.columns]\ndf_k_pivot = df_k_pivot.fillna(0)\n","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:02:01.797868Z","iopub.execute_input":"2024-02-19T18:02:01.798263Z","iopub.status.idle":"2024-02-19T18:02:08.989926Z","shell.execute_reply.started":"2024-02-19T18:02:01.798230Z","shell.execute_reply":"2024-02-19T18:02:08.989005Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n# #Depth 2\n# df_merged = test_base_df[['case_id']]\n# variable_type_list = ['test_credit_bureau_a_2',\n#                      'test_credit_bureau_b_2']\n\n# for k in variable_type_list:\n#     df_k = multi_merge_depth_2(test_base_df,'test',k)\n#     df_merged = df_merged.merge(df_k,how = 'outer',on = 'case_id')\n#     del df_k\n#     gc.collect()\n    \n    \n# #Merge with Base\n# df_merged_test_depth_2 = test_base_df.merge(df_merged,how = 'left',on = 'case_id')\n# df_merged_test_depth_2 = df_merged_test_depth_2.drop(columns = ['date_decision','MONTH','WEEK_NUM'])\n# del df_merged\n# #Convert date columns to difference\n# # date_columns_train_depth_2 = [x for x in df_merged_train_depth_2.columns if x[-1] == 'D']\n# # df_merged_train = date_column_depth_0(df_merged_train)\n\n\n# # df_merged_train = df_merged_train.drop(columns = date_columns_train_depth_2)\n# gc.collect()\n\n# # df_merged_test_depth_2.to_parquet('/kaggle/working/df_merged_test_depth_2.parquet')\n# # del df_merged_test_depth_2\n# # gc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:02:08.992050Z","iopub.execute_input":"2024-02-19T18:02:08.992586Z","iopub.status.idle":"2024-02-19T18:02:08.997288Z","shell.execute_reply.started":"2024-02-19T18:02:08.992553Z","shell.execute_reply":"2024-02-19T18:02:08.996198Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# df_merged_train_depth_1_0 = reduce_mem_usage(df_merged_train_depth_1_0)\n# df_merged_train_depth_2 = reduce_mem_usage(df_merged_train_depth_2)\n# df_merged_train = df_merged_train_depth_1_0.merge(df_merged_train_depth_2,how = 'left',on=  'case_id')\n# del df_merged_train_depth_1_0\n# del df_merged_train_depth_2\n# gc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:02:08.998894Z","iopub.execute_input":"2024-02-19T18:02:08.999231Z","iopub.status.idle":"2024-02-19T18:02:09.011432Z","shell.execute_reply.started":"2024-02-19T18:02:08.999203Z","shell.execute_reply":"2024-02-19T18:02:09.010536Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# df_merged_test_depth_1_0 = pd.read_parquet('/kaggle/working/df_merged_test_depth_1_0.parquet')\n# df_merged_test_depth_2 = pd.read_parquet('/kaggle/working/df_merged_test_depth_2.parquet')\ndf_merged_test_depth_1_0 = reduce_mem_usage(df_merged_test_depth_1_0)\ndf_k_pivot = reduce_mem_usage(df_k_pivot)\n\ndf_merged_test = df_merged_test_depth_1_0.merge(df_k_pivot,how = 'left',on=  'case_id')\ndel df_merged_test_depth_1_0\n# del df_k\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:02:09.012957Z","iopub.execute_input":"2024-02-19T18:02:09.013332Z","iopub.status.idle":"2024-02-19T18:02:10.084789Z","shell.execute_reply.started":"2024-02-19T18:02:09.013301Z","shell.execute_reply":"2024-02-19T18:02:10.083731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"model_columns","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:02:10.086658Z","iopub.execute_input":"2024-02-19T18:02:10.087094Z","iopub.status.idle":"2024-02-19T18:02:10.099381Z","shell.execute_reply.started":"2024-02-19T18:02:10.087057Z","shell.execute_reply":"2024-02-19T18:02:10.098232Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_merged_test[[i for i in model_columns if i not in df_merged_test.columns]] = np.nan","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:02:10.100855Z","iopub.execute_input":"2024-02-19T18:02:10.101187Z","iopub.status.idle":"2024-02-19T18:02:10.108652Z","shell.execute_reply.started":"2024-02-19T18:02:10.101160Z","shell.execute_reply":"2024-02-19T18:02:10.107649Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n\n#Fill Missialue\nnum_cols = df_merged_test.select_dtypes(include=np.number).columns\ndf_merged_test[num_cols] = df_merged_test[num_cols].fillna(0)\n\ndf_merged_test['pmtamount_36A'] = df_merged_test['pmtamount_36A'].fillna(0)\n\nobject_cols = df_merged_test.select_dtypes(include='object').columns\ndf_merged_test[object_cols] = df_merged_test[object_cols].fillna('Mis')\ndf_merged_test = df_merged_test.drop_duplicates(subset= 'case_id')    \n\n    \n    \n#Reindexing\nidentifier_cols = ['date_decision','MONTH','WEEK_NUM']\n#Reindex\ndf_merged_test = df_merged_test.set_index('case_id') \n\n\n\n#Define X,y\ndf_merged_test = df_merged_test.drop(columns = identifier_cols)\ndf_merged_test = df_merged_test[model_columns]","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:02:10.110326Z","iopub.execute_input":"2024-02-19T18:02:10.110731Z","iopub.status.idle":"2024-02-19T18:02:10.212119Z","shell.execute_reply.started":"2024-02-19T18:02:10.110696Z","shell.execute_reply":"2024-02-19T18:02:10.210891Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# SUBMISSION","metadata":{}},{"cell_type":"code","source":"preds_proba_sumbission = model.predict_proba(df_merged_test)[:,1]","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:02:10.215874Z","iopub.execute_input":"2024-02-19T18:02:10.216230Z","iopub.status.idle":"2024-02-19T18:02:10.225635Z","shell.execute_reply.started":"2024-02-19T18:02:10.216202Z","shell.execute_reply":"2024-02-19T18:02:10.224403Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"preds_proba_sumbission_df = pd.DataFrame(list(zip(list(df_merged_test.index),preds_proba_sumbission)),\n              columns=['case_id','score'])\npreds_proba_sumbission_df = preds_proba_sumbission_df.set_index('case_id')","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:02:10.227350Z","iopub.execute_input":"2024-02-19T18:02:10.227766Z","iopub.status.idle":"2024-02-19T18:02:10.236414Z","shell.execute_reply.started":"2024-02-19T18:02:10.227729Z","shell.execute_reply":"2024-02-19T18:02:10.235488Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"preds_proba_sumbission_df.to_csv(\"submission.csv\")","metadata":{"execution":{"iopub.status.busy":"2024-02-19T18:02:10.237777Z","iopub.execute_input":"2024-02-19T18:02:10.238177Z","iopub.status.idle":"2024-02-19T18:02:10.250574Z","shell.execute_reply.started":"2024-02-19T18:02:10.238140Z","shell.execute_reply":"2024-02-19T18:02:10.249404Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}