{"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"}],"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\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\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\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\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-18T16:17:01.155314Z","iopub.execute_input":"2024-02-18T16:17:01.155703Z","iopub.status.idle":"2024-02-18T16:17:02.151268Z","shell.execute_reply.started":"2024-02-18T16:17:01.155673Z","shell.execute_reply":"2024-02-18T16:17:02.150332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.preprocessing import LabelEncoder\nimport xgboost as xgb\nfrom xgboost import XGBClassifier","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:14.629782Z","iopub.execute_input":"2024-02-18T16:17:14.630752Z","iopub.status.idle":"2024-02-18T16:17:15.837897Z","shell.execute_reply.started":"2024-02-18T16:17:14.630718Z","shell.execute_reply":"2024-02-18T16:17:15.836993Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.set_option('display.max_rows',None)\npd.set_option('display.max_columns',None)\npd.set_option('display.max_colwidth',None)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:15.839897Z","iopub.execute_input":"2024-02-18T16:17:15.840928Z","iopub.status.idle":"2024-02-18T16:17:15.845446Z","shell.execute_reply.started":"2024-02-18T16:17:15.840877Z","shell.execute_reply":"2024-02-18T16:17:15.844331Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"feat_desc  = pd.read_csv('/kaggle/input/home-credit-credit-risk-model-stability/feature_definitions.csv')\nsub_file = pd.read_csv('/kaggle/input/home-credit-credit-risk-model-stability/sample_submission.csv')","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:15.846819Z","iopub.execute_input":"2024-02-18T16:17:15.847301Z","iopub.status.idle":"2024-02-18T16:17:15.876881Z","shell.execute_reply.started":"2024-02-18T16:17:15.847267Z","shell.execute_reply":"2024-02-18T16:17:15.875786Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"feat_desc.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:15.878784Z","iopub.execute_input":"2024-02-18T16:17:15.879097Z","iopub.status.idle":"2024-02-18T16:17:15.894433Z","shell.execute_reply.started":"2024-02-18T16:17:15.879070Z","shell.execute_reply":"2024-02-18T16:17:15.893480Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sub_file.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:15.895448Z","iopub.execute_input":"2024-02-18T16:17:15.895701Z","iopub.status.idle":"2024-02-18T16:17:15.904739Z","shell.execute_reply.started":"2024-02-18T16:17:15.895679Z","shell.execute_reply":"2024-02-18T16:17:15.903798Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Reading Base Table","metadata":{}},{"cell_type":"code","source":"train_base_file = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_base.parquet')\ntrain_base_file.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:15.999488Z","iopub.execute_input":"2024-02-18T16:17:15.999755Z","iopub.status.idle":"2024-02-18T16:17:16.447850Z","shell.execute_reply.started":"2024-02-18T16:17:15.999733Z","shell.execute_reply":"2024-02-18T16:17:16.446710Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_file.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:16.449378Z","iopub.execute_input":"2024-02-18T16:17:16.449699Z","iopub.status.idle":"2024-02-18T16:17:16.460532Z","shell.execute_reply.started":"2024-02-18T16:17:16.449672Z","shell.execute_reply":"2024-02-18T16:17:16.459546Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_file.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:16.461794Z","iopub.execute_input":"2024-02-18T16:17:16.462165Z","iopub.status.idle":"2024-02-18T16:17:16.469502Z","shell.execute_reply.started":"2024-02-18T16:17:16.462134Z","shell.execute_reply":"2024-02-18T16:17:16.468430Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_file.case_id.nunique()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:16.472071Z","iopub.execute_input":"2024-02-18T16:17:16.472403Z","iopub.status.idle":"2024-02-18T16:17:16.548823Z","shell.execute_reply.started":"2024-02-18T16:17:16.472378Z","shell.execute_reply":"2024-02-18T16:17:16.547922Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### In total we have 7 types of tables\n\n1. Previous application\n2. Credit Bureau\n3. Debit Card\n4. Deposit\n5. Person\n6. Static\n7. Tax Registry","metadata":{}},{"cell_type":"markdown","source":"### Starting with static and appl prev data","metadata":{}},{"cell_type":"code","source":"# pd.set_option('display.max_rows',None)\n# feat_desc[feat_desc.Variable.isin(credit_bureau_a_1_0_2.columns)]","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:16.549803Z","iopub.execute_input":"2024-02-18T16:17:16.550080Z","iopub.status.idle":"2024-02-18T16:17:16.554126Z","shell.execute_reply.started":"2024-02-18T16:17:16.550057Z","shell.execute_reply":"2024-02-18T16:17:16.553144Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Creating Function for Aggregating Credit bureau table","metadata":{}},{"cell_type":"code","source":"table_paths = ['/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_credit_bureau_a_1_0.parquet',\n              '/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_credit_bureau_a_1_1.parquet',\n              '/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_credit_bureau_a_1_2.parquet',\n              '/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_credit_bureau_a_1_3.parquet']\nexternal_table_path = ['/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_credit_bureau_b_1.parquet']","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:16.555425Z","iopub.execute_input":"2024-02-18T16:17:16.556037Z","iopub.status.idle":"2024-02-18T16:17:16.562469Z","shell.execute_reply.started":"2024-02-18T16:17:16.556011Z","shell.execute_reply":"2024-02-18T16:17:16.561516Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cb_1_0 = pd.read_parquet(table_paths[0])\ncb_1_1 = pd.read_parquet(table_paths[1])\ncb_1_2 = pd.read_parquet(table_paths[2])\ncb_1_3 = pd.read_parquet(table_paths[3])\next_cb_1_0 = pd.read_parquet(external_table_path[0])","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:16.563734Z","iopub.execute_input":"2024-02-18T16:17:16.564196Z","iopub.status.idle":"2024-02-18T16:17:39.042307Z","shell.execute_reply.started":"2024-02-18T16:17:16.564164Z","shell.execute_reply":"2024-02-18T16:17:39.041296Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def agg_credit_bureau_1_tables(int_table , ext_table):\n    master_dict = {}\n    cb_1_0_merged = int_table.merge(ext_table , on = ['case_id','num_group1'] , how = 'left')\n    object_cols = list(cb_1_0_merged.select_dtypes(['object']).columns)\n    le = LabelEncoder()\n    for i in object_cols:\n        cb_1_0_merged[i] = le.fit_transform(cb_1_0_merged[i])\n    cols = list(set(cb_1_0_merged.columns) - set(['case_id','num_group1']))\n    cb_1_0_final = cb_1_0_merged.groupby('case_id')[cols].max().reset_index()\n    return cb_1_0_final","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:39.045798Z","iopub.execute_input":"2024-02-18T16:17:39.046154Z","iopub.status.idle":"2024-02-18T16:17:39.052762Z","shell.execute_reply.started":"2024-02-18T16:17:39.046127Z","shell.execute_reply":"2024-02-18T16:17:39.051627Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cb_1_0_final = agg_credit_bureau_1_tables(cb_1_0,ext_cb_1_0)\ncb_1_1_final = agg_credit_bureau_1_tables(cb_1_1,ext_cb_1_0)\ncb_1_2_final = agg_credit_bureau_1_tables(cb_1_2,ext_cb_1_0)\ncb_1_3_final = agg_credit_bureau_1_tables(cb_1_3,ext_cb_1_0)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:17:39.054121Z","iopub.execute_input":"2024-02-18T16:17:39.055023Z","iopub.status.idle":"2024-02-18T16:21:00.760851Z","shell.execute_reply.started":"2024-02-18T16:17:39.054989Z","shell.execute_reply":"2024-02-18T16:21:00.759980Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cb_1_final = pd.concat([cb_1_0_final,cb_1_1_final,cb_1_2_final,cb_1_3_final] , axis=0)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:00.762110Z","iopub.execute_input":"2024-02-18T16:21:00.762415Z","iopub.status.idle":"2024-02-18T16:21:01.064054Z","shell.execute_reply.started":"2024-02-18T16:21:00.762389Z","shell.execute_reply":"2024-02-18T16:21:01.062877Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cb_1_final.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:01.065426Z","iopub.execute_input":"2024-02-18T16:21:01.065702Z","iopub.status.idle":"2024-02-18T16:21:01.072212Z","shell.execute_reply.started":"2024-02-18T16:21:01.065678Z","shell.execute_reply":"2024-02-18T16:21:01.071074Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del cb_1_0,cb_1_1,cb_1_2,cb_1_3","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:01.073358Z","iopub.execute_input":"2024-02-18T16:21:01.073621Z","iopub.status.idle":"2024-02-18T16:21:02.431143Z","shell.execute_reply.started":"2024-02-18T16:21:01.073599Z","shell.execute_reply":"2024-02-18T16:21:02.430046Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del cb_1_0_final,cb_1_1_final,cb_1_2_final,cb_1_3_final","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:02.432607Z","iopub.execute_input":"2024-02-18T16:21:02.432951Z","iopub.status.idle":"2024-02-18T16:21:02.443565Z","shell.execute_reply.started":"2024-02-18T16:21:02.432921Z","shell.execute_reply":"2024-02-18T16:21:02.442675Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Aggregating appl prev tables","metadata":{}},{"cell_type":"code","source":"appl_prev_1_0 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_applprev_1_0.parquet')\nappl_prev_1_1 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_applprev_1_1.parquet')","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:02.444800Z","iopub.execute_input":"2024-02-18T16:21:02.445180Z","iopub.status.idle":"2024-02-18T16:21:10.721265Z","shell.execute_reply.started":"2024-02-18T16:21:02.445156Z","shell.execute_reply":"2024-02-18T16:21:10.720249Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def agg_appl_prev_1_tables(int_table):\n    master_dict = {}\n    object_cols = list(int_table.select_dtypes(['object']).columns)\n    le1 = LabelEncoder()\n    for i in object_cols:\n        int_table[i] = le1.fit_transform(int_table[i])\n    cols = list(set(int_table.columns) - set(['case_id','num_group1']))\n    int_table_final = int_table.groupby('case_id')[cols].max().reset_index()\n    return int_table_final","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:10.722853Z","iopub.execute_input":"2024-02-18T16:21:10.723562Z","iopub.status.idle":"2024-02-18T16:21:10.731373Z","shell.execute_reply.started":"2024-02-18T16:21:10.723524Z","shell.execute_reply":"2024-02-18T16:21:10.730313Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"appl_prev_1_0.case_id.nunique(),appl_prev_1_1.case_id.nunique()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:10.732598Z","iopub.execute_input":"2024-02-18T16:21:10.732959Z","iopub.status.idle":"2024-02-18T16:21:10.819959Z","shell.execute_reply.started":"2024-02-18T16:21:10.732923Z","shell.execute_reply":"2024-02-18T16:21:10.818955Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"appl_prev_1_0_final = agg_appl_prev_1_tables(appl_prev_1_0)\nappl_prev_1_1_final = agg_appl_prev_1_tables(appl_prev_1_1)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:10.821104Z","iopub.execute_input":"2024-02-18T16:21:10.821415Z","iopub.status.idle":"2024-02-18T16:21:49.498267Z","shell.execute_reply.started":"2024-02-18T16:21:10.821388Z","shell.execute_reply":"2024-02-18T16:21:49.497302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"appl_prev_1_final = pd.concat([appl_prev_1_0_final,appl_prev_1_0_final] , axis=0)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:49.499517Z","iopub.execute_input":"2024-02-18T16:21:49.499808Z","iopub.status.idle":"2024-02-18T16:21:49.688098Z","shell.execute_reply.started":"2024-02-18T16:21:49.499785Z","shell.execute_reply":"2024-02-18T16:21:49.687266Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del appl_prev_1_0,appl_prev_1_1,appl_prev_1_0_final,appl_prev_1_1_final","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:49.693265Z","iopub.execute_input":"2024-02-18T16:21:49.693547Z","iopub.status.idle":"2024-02-18T16:21:49.699859Z","shell.execute_reply.started":"2024-02-18T16:21:49.693524Z","shell.execute_reply":"2024-02-18T16:21:49.698941Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Aggregating deposit person other and debitcard","metadata":{}},{"cell_type":"code","source":"debit_card = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_debitcard_1.parquet')\ndeposit = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_deposit_1.parquet')\nperson = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_other_1.parquet')\nother = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_person_1.parquet')","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:49.701335Z","iopub.execute_input":"2024-02-18T16:21:49.701761Z","iopub.status.idle":"2024-02-18T16:21:53.256196Z","shell.execute_reply.started":"2024-02-18T16:21:49.701730Z","shell.execute_reply":"2024-02-18T16:21:53.255350Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"debit_card_final = agg_appl_prev_1_tables(debit_card)\ndeposit_final = agg_appl_prev_1_tables(deposit)\nperson_final = agg_appl_prev_1_tables(person)\nother_final = agg_appl_prev_1_tables(other)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:21:53.257372Z","iopub.execute_input":"2024-02-18T16:21:53.257679Z","iopub.status.idle":"2024-02-18T16:22:17.293652Z","shell.execute_reply.started":"2024-02-18T16:21:53.257653Z","shell.execute_reply":"2024-02-18T16:22:17.292589Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del debit_card, deposit, person, other","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:22:17.295136Z","iopub.execute_input":"2024-02-18T16:22:17.295518Z","iopub.status.idle":"2024-02-18T16:22:17.300619Z","shell.execute_reply.started":"2024-02-18T16:22:17.295481Z","shell.execute_reply":"2024-02-18T16:22:17.299578Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tax_registry_a = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_tax_registry_a_1.parquet')\ntax_registry_b = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_tax_registry_b_1.parquet')\ntax_registry_c = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_tax_registry_c_1.parquet')\n\ntrain_static_0_0 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_static_0_0.parquet')\ntrain_static_0_1 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_static_0_1.parquet')\ntrain_static_cb_0 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_static_cb_0.parquet')\n","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:22:17.301845Z","iopub.execute_input":"2024-02-18T16:22:17.302124Z","iopub.status.idle":"2024-02-18T16:22:25.237098Z","shell.execute_reply.started":"2024-02-18T16:22:17.302102Z","shell.execute_reply":"2024-02-18T16:22:25.235982Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tax_registry_a_final = agg_appl_prev_1_tables(tax_registry_a)\ntax_registry_b_final = agg_appl_prev_1_tables(tax_registry_b)\ntax_registry_c_final = agg_appl_prev_1_tables(tax_registry_c)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:22:25.238495Z","iopub.execute_input":"2024-02-18T16:22:25.238932Z","iopub.status.idle":"2024-02-18T16:22:30.847868Z","shell.execute_reply.started":"2024-02-18T16:22:25.238865Z","shell.execute_reply":"2024-02-18T16:22:30.847061Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del tax_registry_a,tax_registry_b,tax_registry_c","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:22:30.849087Z","iopub.execute_input":"2024-02-18T16:22:30.849375Z","iopub.status.idle":"2024-02-18T16:22:30.853901Z","shell.execute_reply.started":"2024-02-18T16:22:30.849350Z","shell.execute_reply":"2024-02-18T16:22:30.852936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_cb = train_base_file.merge(cb_1_final , on = 'case_id' , how = 'left')\ntrain_base_cb = train_base_cb.merge(appl_prev_1_final , on = 'case_id' , how = 'left')\ntrain_base_cb = train_base_cb.merge(debit_card_final , on = 'case_id' , how = 'left')\ntrain_base_cb = train_base_cb.merge(person_final , on = 'case_id' , how = 'left')\n\ntrain_base_cb = train_base_cb.merge(deposit_final , on = 'case_id' , how = 'left')\ntrain_base_cb = train_base_cb.merge(other_final , on = 'case_id' , how = 'left')\ntrain_base_cb = train_base_cb.merge(tax_registry_a_final , on = 'case_id' , how = 'left')\ntrain_base_cb = train_base_cb.merge(tax_registry_b_final , on = 'case_id' , how = 'left')\ntrain_base_cb = train_base_cb.merge(tax_registry_c_final , on = 'case_id' , how = 'left')\n\n# train_base_cb = train_base_cb.merge(train_static_0_0 , on = 'case_id' , how = 'left')\n# train_base_cb = train_base_cb.merge(train_static_0_1 , on = 'case_id' , how = 'left')\n# train_base_cb = train_base_cb.merge(train_static_cb_0 , on = 'case_id' , how = 'left')","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:22:30.855257Z","iopub.execute_input":"2024-02-18T16:22:30.855791Z","iopub.status.idle":"2024-02-18T16:23:03.656124Z","shell.execute_reply.started":"2024-02-18T16:22:30.855766Z","shell.execute_reply":"2024-02-18T16:23:03.655012Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_cb = train_base_cb.drop_duplicates()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:03.657855Z","iopub.execute_input":"2024-02-18T16:23:03.658555Z","iopub.status.idle":"2024-02-18T16:23:14.330448Z","shell.execute_reply.started":"2024-02-18T16:23:03.658520Z","shell.execute_reply":"2024-02-18T16:23:14.329597Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del cb_1_final, appl_prev_1_final, debit_card_final, person_final, deposit_final, other_final, tax_registry_a_final, tax_registry_b_final, tax_registry_c_final","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:14.331565Z","iopub.execute_input":"2024-02-18T16:23:14.331868Z","iopub.status.idle":"2024-02-18T16:23:14.343268Z","shell.execute_reply.started":"2024-02-18T16:23:14.331844Z","shell.execute_reply":"2024-02-18T16:23:14.342250Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del train_static_0_0,train_static_0_1,train_static_cb_0","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:14.344415Z","iopub.execute_input":"2024-02-18T16:23:14.344736Z","iopub.status.idle":"2024-02-18T16:23:14.646686Z","shell.execute_reply.started":"2024-02-18T16:23:14.344711Z","shell.execute_reply":"2024-02-18T16:23:14.645537Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Merging All Tables","metadata":{}},{"cell_type":"code","source":"train_base_cb.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:02.892526Z","iopub.execute_input":"2024-02-18T16:26:02.893431Z","iopub.status.idle":"2024-02-18T16:26:03.319780Z","shell.execute_reply.started":"2024-02-18T16:26:02.893386Z","shell.execute_reply":"2024-02-18T16:26:03.318494Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_cb.case_id.nunique()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:03.320929Z","iopub.status.idle":"2024-02-18T16:26:03.321412Z","shell.execute_reply.started":"2024-02-18T16:26:03.321166Z","shell.execute_reply":"2024-02-18T16:26:03.321187Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Dropping features with more than 90% null values","metadata":{}},{"cell_type":"code","source":"thresh = len(train_base_cb) * 0.1\ntrain_base_final_df = train_base_cb.dropna(thresh = thresh, axis = 1)\n","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:14.711782Z","iopub.execute_input":"2024-02-18T16:23:14.712060Z","iopub.status.idle":"2024-02-18T16:23:15.993425Z","shell.execute_reply.started":"2024-02-18T16:23:14.712037Z","shell.execute_reply":"2024-02-18T16:23:15.992535Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_final_df.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:15.994647Z","iopub.execute_input":"2024-02-18T16:23:15.995020Z","iopub.status.idle":"2024-02-18T16:23:16.001843Z","shell.execute_reply.started":"2024-02-18T16:23:15.994991Z","shell.execute_reply":"2024-02-18T16:23:16.000704Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del train_base_cb","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:16.003444Z","iopub.execute_input":"2024-02-18T16:23:16.004193Z","iopub.status.idle":"2024-02-18T16:23:16.079427Z","shell.execute_reply.started":"2024-02-18T16:23:16.004158Z","shell.execute_reply":"2024-02-18T16:23:16.078501Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_final_df.info()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:16.080613Z","iopub.execute_input":"2024-02-18T16:23:16.080854Z","iopub.status.idle":"2024-02-18T16:23:16.104078Z","shell.execute_reply.started":"2024-02-18T16:23:16.080834Z","shell.execute_reply":"2024-02-18T16:23:16.102955Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Running Random Var","metadata":{}},{"cell_type":"code","source":"train_base_final_df['random_var'] = np.random.normal(size = train_base_final_df.shape[0])","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:16.105985Z","iopub.execute_input":"2024-02-18T16:23:16.106276Z","iopub.status.idle":"2024-02-18T16:23:16.156789Z","shell.execute_reply.started":"2024-02-18T16:23:16.106253Z","shell.execute_reply":"2024-02-18T16:23:16.155793Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"list(train_base_final_df.columns)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:16.158034Z","iopub.execute_input":"2024-02-18T16:23:16.158312Z","iopub.status.idle":"2024-02-18T16:23:16.166972Z","shell.execute_reply.started":"2024-02-18T16:23:16.158289Z","shell.execute_reply":"2024-02-18T16:23:16.166108Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X = list(set(train_base_final_df.columns) - set(['case_id','target','MONTH','WEEK_NUM','date_decision']))\ny = ['target']","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:16.168282Z","iopub.execute_input":"2024-02-18T16:23:16.168631Z","iopub.status.idle":"2024-02-18T16:23:16.174507Z","shell.execute_reply.started":"2024-02-18T16:23:16.168606Z","shell.execute_reply":"2024-02-18T16:23:16.173670Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_final_df['target'].value_counts(normalize = True)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:16.175644Z","iopub.execute_input":"2024-02-18T16:23:16.176231Z","iopub.status.idle":"2024-02-18T16:23:16.198462Z","shell.execute_reply.started":"2024-02-18T16:23:16.176201Z","shell.execute_reply":"2024-02-18T16:23:16.197532Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"base_xgb = XGBClassifier(random_state = 18 , max_depth = 5 , scale_pos_weight = 20)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:16.199582Z","iopub.execute_input":"2024-02-18T16:23:16.200015Z","iopub.status.idle":"2024-02-18T16:23:16.205596Z","shell.execute_reply.started":"2024-02-18T16:23:16.199990Z","shell.execute_reply":"2024-02-18T16:23:16.204776Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"base_xgb.fit(train_base_final_df[X] , train_base_final_df[y])","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:23:16.206737Z","iopub.execute_input":"2024-02-18T16:23:16.207051Z","iopub.status.idle":"2024-02-18T16:24:04.323476Z","shell.execute_reply.started":"2024-02-18T16:23:16.207021Z","shell.execute_reply":"2024-02-18T16:24:04.322494Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# feat_imp = base_xgb.feature_importances()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.324659Z","iopub.execute_input":"2024-02-18T16:24:04.324963Z","iopub.status.idle":"2024-02-18T16:24:04.329081Z","shell.execute_reply.started":"2024-02-18T16:24:04.324926Z","shell.execute_reply":"2024-02-18T16:24:04.328117Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xgb_fea_imp=pd.DataFrame(list(base_xgb.get_booster().get_fscore().items()),\ncolumns=['feature','importance']).sort_values('importance', ascending=False)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.330396Z","iopub.execute_input":"2024-02-18T16:24:04.330760Z","iopub.status.idle":"2024-02-18T16:24:04.341668Z","shell.execute_reply.started":"2024-02-18T16:24:04.330726Z","shell.execute_reply":"2024-02-18T16:24:04.340790Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xgb_fea_imp.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.342740Z","iopub.execute_input":"2024-02-18T16:24:04.343073Z","iopub.status.idle":"2024-02-18T16:24:04.353901Z","shell.execute_reply.started":"2024-02-18T16:24:04.343038Z","shell.execute_reply":"2024-02-18T16:24:04.352974Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xgb_fea_imp.loc[(xgb_fea_imp[\"feature\"] == \"random_var\")]['importance'].values[0]","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.355058Z","iopub.execute_input":"2024-02-18T16:24:04.355343Z","iopub.status.idle":"2024-02-18T16:24:04.362599Z","shell.execute_reply.started":"2024-02-18T16:24:04.355309Z","shell.execute_reply":"2024-02-18T16:24:04.361704Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"useless_feats = xgb_fea_imp[xgb_fea_imp['importance']<= xgb_fea_imp.loc[(xgb_fea_imp[\"feature\"] == \"random_var\")]['importance'].values[0]]","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.363870Z","iopub.execute_input":"2024-02-18T16:24:04.364267Z","iopub.status.idle":"2024-02-18T16:24:04.370646Z","shell.execute_reply.started":"2024-02-18T16:24:04.364235Z","shell.execute_reply":"2024-02-18T16:24:04.369670Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"len(useless_feats)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.372029Z","iopub.execute_input":"2024-02-18T16:24:04.372312Z","iopub.status.idle":"2024-02-18T16:24:04.380351Z","shell.execute_reply.started":"2024-02-18T16:24:04.372288Z","shell.execute_reply":"2024-02-18T16:24:04.379411Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_final_df_filtered = train_base_final_df.drop(columns = useless_feats['feature'] , axis=1)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.381448Z","iopub.execute_input":"2024-02-18T16:24:04.382197Z","iopub.status.idle":"2024-02-18T16:24:04.745740Z","shell.execute_reply.started":"2024-02-18T16:24:04.382164Z","shell.execute_reply":"2024-02-18T16:24:04.744634Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_final_df_filtered.shape ,train_base_final_df.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.747028Z","iopub.execute_input":"2024-02-18T16:24:04.747323Z","iopub.status.idle":"2024-02-18T16:24:04.753487Z","shell.execute_reply.started":"2024-02-18T16:24:04.747299Z","shell.execute_reply":"2024-02-18T16:24:04.752564Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_final_df_filtered.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.754560Z","iopub.execute_input":"2024-02-18T16:24:04.754873Z","iopub.status.idle":"2024-02-18T16:24:04.806834Z","shell.execute_reply.started":"2024-02-18T16:24:04.754840Z","shell.execute_reply":"2024-02-18T16:24:04.805949Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Building cross validated xxgboost","metadata":{}},{"cell_type":"code","source":"from sklearn import model_selection\nfrom sklearn import metrics","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.807887Z","iopub.execute_input":"2024-02-18T16:24:04.808209Z","iopub.status.idle":"2024-02-18T16:24:04.812084Z","shell.execute_reply.started":"2024-02-18T16:24:04.808186Z","shell.execute_reply":"2024-02-18T16:24:04.811101Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_final_df_filtered['kfold'] = -1\ntrain_base_final_df_filtered = train_base_final_df_filtered.sample(frac = 1, random_state = 100).reset_index(drop = True)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:04.819928Z","iopub.execute_input":"2024-02-18T16:24:04.820261Z","iopub.status.idle":"2024-02-18T16:24:06.908928Z","shell.execute_reply.started":"2024-02-18T16:24:04.820238Z","shell.execute_reply":"2024-02-18T16:24:06.907818Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"skf = model_selection.StratifiedKFold(n_splits = 5)\n\n# Filling the new KFold column - \nfor f , (t_ , v_) in enumerate(skf.split(X = train_base_final_df_filtered , y = train_base_final_df_filtered.target.values)) :\n    train_base_final_df_filtered.loc[v_ , 'kfold'] = f","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:06.910367Z","iopub.execute_input":"2024-02-18T16:24:06.910769Z","iopub.status.idle":"2024-02-18T16:24:07.190218Z","shell.execute_reply.started":"2024-02-18T16:24:06.910733Z","shell.execute_reply":"2024-02-18T16:24:07.189349Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_final_df_filtered[train_base_final_df_filtered['kfold']==3].target.value_counts()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:07.191316Z","iopub.execute_input":"2024-02-18T16:24:07.191587Z","iopub.status.idle":"2024-02-18T16:24:07.283093Z","shell.execute_reply.started":"2024-02-18T16:24:07.191565Z","shell.execute_reply":"2024-02-18T16:24:07.282103Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base_final_df_filtered.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:07.284562Z","iopub.execute_input":"2024-02-18T16:24:07.284994Z","iopub.status.idle":"2024-02-18T16:24:07.291452Z","shell.execute_reply.started":"2024-02-18T16:24:07.284960Z","shell.execute_reply":"2024-02-18T16:24:07.290421Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"selected_feats = list(set(train_base_final_df_filtered.columns) - set(['case_id','target','MONTH','WEEK_NUM','date_decision','kfold']))","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:07.292781Z","iopub.execute_input":"2024-02-18T16:24:07.293427Z","iopub.status.idle":"2024-02-18T16:24:07.299726Z","shell.execute_reply.started":"2024-02-18T16:24:07.293393Z","shell.execute_reply":"2024-02-18T16:24:07.298845Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"base_xgb_1 = XGBClassifier(random_state = 18 , max_depth = 5 , scale_pos_weight = 20)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:07.300722Z","iopub.execute_input":"2024-02-18T16:24:07.301051Z","iopub.status.idle":"2024-02-18T16:24:07.308557Z","shell.execute_reply.started":"2024-02-18T16:24:07.301018Z","shell.execute_reply":"2024-02-18T16:24:07.307473Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fold_num = 1\n#log_reg_coefs = []\n#log_reg_intercepts = []\ntrain_auc_scores = []\nval_auc_scores = []\n\nfor kfold_idx in train_base_final_df_filtered['kfold'].value_counts().index:\n    print(f\"Fold num : {fold_num}\", end = \"\\n\")\n    X_train = train_base_final_df_filtered[train_base_final_df_filtered['kfold'] != kfold_idx].loc[:, selected_feats].values\n    print(X_train.shape)\n    y_train = train_base_final_df_filtered[train_base_final_df_filtered['kfold'] != kfold_idx].loc[:, \"target\"].values\n    X_val = train_base_final_df_filtered[train_base_final_df_filtered['kfold'] == kfold_idx].loc[:, selected_feats].values\n    y_val = train_base_final_df_filtered[train_base_final_df_filtered['kfold'] == kfold_idx].loc[:, 'target'].values\n    \n    base_xgb_1.fit(X_train , y_train)\n    #lr.fit(X_train, y_train)\n    #log_reg_coefs.append(list(lr.coef_))\n    #log_reg_intercepts.append(lr.intercept_)\n    preds_train = base_xgb_1.predict(X_train)\n    preds_val = base_xgb_1.predict(X_val)\n    \n    train_auc_scores.append(metrics.roc_auc_score(y_true = y_train, y_score = preds_train))\n    val_auc_scores.append(metrics.roc_auc_score(y_true = y_val, y_score = preds_val))\n    \n    print(f\"Train Accuracy : {train_auc_scores[-1]}\")\n    print(f\"Val Accuracy : {val_auc_scores[-1]}\")\n    \n    print(\"\\n\")\n    fold_num = fold_num + 1\nprint(f\"Mean Train Accuracy : {np.mean(train_auc_scores)}\")\nprint(f\"Mean Val Accuracy : {np.mean(val_auc_scores)}\")","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:24:07.309669Z","iopub.execute_input":"2024-02-18T16:24:07.309987Z","iopub.status.idle":"2024-02-18T16:26:02.891065Z","shell.execute_reply.started":"2024-02-18T16:24:07.309954Z","shell.execute_reply":"2024-02-18T16:26:02.889916Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Preparing testing data","metadata":{}},{"cell_type":"code","source":"test_base_file = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_base.parquet')","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:15.058655Z","iopub.execute_input":"2024-02-18T16:26:15.059067Z","iopub.status.idle":"2024-02-18T16:26:15.071376Z","shell.execute_reply.started":"2024-02-18T16:26:15.059034Z","shell.execute_reply":"2024-02-18T16:26:15.070376Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"table_paths = ['/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_credit_bureau_a_1_0.parquet',\n              '/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_credit_bureau_a_1_1.parquet',\n              '/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_credit_bureau_a_1_2.parquet',\n              '/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_credit_bureau_a_1_3.parquet']\nexternal_table_path = ['/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_credit_bureau_b_1.parquet']","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:17.604476Z","iopub.execute_input":"2024-02-18T16:26:17.605461Z","iopub.status.idle":"2024-02-18T16:26:17.610237Z","shell.execute_reply.started":"2024-02-18T16:26:17.605429Z","shell.execute_reply":"2024-02-18T16:26:17.609319Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cb_1_0 = pd.read_parquet(table_paths[0])\ncb_1_1 = pd.read_parquet(table_paths[1])\ncb_1_2 = pd.read_parquet(table_paths[2])\ncb_1_3 = pd.read_parquet(table_paths[3])\next_cb_1_0 = pd.read_parquet(external_table_path[0])","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:18.163897Z","iopub.execute_input":"2024-02-18T16:26:18.164732Z","iopub.status.idle":"2024-02-18T16:26:18.234713Z","shell.execute_reply.started":"2024-02-18T16:26:18.164702Z","shell.execute_reply":"2024-02-18T16:26:18.234044Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def agg_credit_bureau_1_tables(int_table , ext_table):\n    master_dict = {}\n    cb_1_0_merged = int_table.merge(ext_table , on = ['case_id','num_group1'] , how = 'left')\n    object_cols = list(cb_1_0_merged.select_dtypes(['object']).columns)\n    le = LabelEncoder()\n    for i in object_cols:\n        cb_1_0_merged[i] = le.fit_transform(cb_1_0_merged[i])\n    cols = list(set(cb_1_0_merged.columns) - set(['case_id','num_group1']))\n    cb_1_0_final = cb_1_0_merged.groupby('case_id')[cols].max().reset_index()\n    return cb_1_0_final","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:18.613067Z","iopub.execute_input":"2024-02-18T16:26:18.613408Z","iopub.status.idle":"2024-02-18T16:26:18.620182Z","shell.execute_reply.started":"2024-02-18T16:26:18.613384Z","shell.execute_reply":"2024-02-18T16:26:18.619165Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cb_1_0_final = agg_credit_bureau_1_tables(cb_1_0,ext_cb_1_0)\ncb_1_1_final = agg_credit_bureau_1_tables(cb_1_1,ext_cb_1_0)\ncb_1_2_final = agg_credit_bureau_1_tables(cb_1_2,ext_cb_1_0)\ncb_1_3_final = agg_credit_bureau_1_tables(cb_1_3,ext_cb_1_0)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:19.009060Z","iopub.execute_input":"2024-02-18T16:26:19.010023Z","iopub.status.idle":"2024-02-18T16:26:19.113250Z","shell.execute_reply.started":"2024-02-18T16:26:19.009988Z","shell.execute_reply":"2024-02-18T16:26:19.112454Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cb_1_final_test = pd.concat([cb_1_0_final,cb_1_1_final,cb_1_2_final,cb_1_3_final] , axis=0)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:19.453515Z","iopub.execute_input":"2024-02-18T16:26:19.453811Z","iopub.status.idle":"2024-02-18T16:26:19.467803Z","shell.execute_reply.started":"2024-02-18T16:26:19.453787Z","shell.execute_reply":"2024-02-18T16:26:19.467050Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cb_1_final_test.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:19.805413Z","iopub.execute_input":"2024-02-18T16:26:19.806192Z","iopub.status.idle":"2024-02-18T16:26:19.812096Z","shell.execute_reply.started":"2024-02-18T16:26:19.806164Z","shell.execute_reply":"2024-02-18T16:26:19.811117Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"appl_prev_1_0 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_applprev_1_0.parquet')\nappl_prev_1_1 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_applprev_1_1.parquet')","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:20.583955Z","iopub.execute_input":"2024-02-18T16:26:20.584282Z","iopub.status.idle":"2024-02-18T16:26:20.607286Z","shell.execute_reply.started":"2024-02-18T16:26:20.584258Z","shell.execute_reply":"2024-02-18T16:26:20.606466Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def agg_appl_prev_1_tables(int_table):\n    master_dict = {}\n    object_cols = list(int_table.select_dtypes(['object']).columns)\n    le1 = LabelEncoder()\n    for i in object_cols:\n        int_table[i] = le1.fit_transform(int_table[i])\n    cols = list(set(int_table.columns) - set(['case_id','num_group1']))\n    int_table_final = int_table.groupby('case_id')[cols].max().reset_index()\n    return int_table_final","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:20.963116Z","iopub.execute_input":"2024-02-18T16:26:20.963875Z","iopub.status.idle":"2024-02-18T16:26:20.969866Z","shell.execute_reply.started":"2024-02-18T16:26:20.963847Z","shell.execute_reply":"2024-02-18T16:26:20.968716Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"appl_prev_1_0.case_id.nunique(),appl_prev_1_1.case_id.nunique()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:21.438754Z","iopub.execute_input":"2024-02-18T16:26:21.439310Z","iopub.status.idle":"2024-02-18T16:26:21.446082Z","shell.execute_reply.started":"2024-02-18T16:26:21.439282Z","shell.execute_reply":"2024-02-18T16:26:21.445023Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"appl_prev_1_0_final = agg_appl_prev_1_tables(appl_prev_1_0)\nappl_prev_1_1_final = agg_appl_prev_1_tables(appl_prev_1_1)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:22.090289Z","iopub.execute_input":"2024-02-18T16:26:22.090876Z","iopub.status.idle":"2024-02-18T16:26:22.124961Z","shell.execute_reply.started":"2024-02-18T16:26:22.090847Z","shell.execute_reply":"2024-02-18T16:26:22.124171Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"appl_prev_1_final_test = pd.concat([appl_prev_1_0_final,appl_prev_1_0_final] , axis=0)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:25.170348Z","iopub.execute_input":"2024-02-18T16:26:25.171364Z","iopub.status.idle":"2024-02-18T16:26:25.178945Z","shell.execute_reply.started":"2024-02-18T16:26:25.171322Z","shell.execute_reply":"2024-02-18T16:26:25.178053Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del appl_prev_1_0,appl_prev_1_1,appl_prev_1_0_final,appl_prev_1_1_final","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:25.798913Z","iopub.execute_input":"2024-02-18T16:26:25.799568Z","iopub.status.idle":"2024-02-18T16:26:25.803866Z","shell.execute_reply.started":"2024-02-18T16:26:25.799539Z","shell.execute_reply":"2024-02-18T16:26:25.802869Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"debit_card = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_debitcard_1.parquet')\ndeposit = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_deposit_1.parquet')\nperson = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_other_1.parquet')\nother = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_person_1.parquet')\n\ntax_registry_a = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_tax_registry_a_1.parquet')\ntax_registry_b = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_tax_registry_b_1.parquet')\ntax_registry_c = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_tax_registry_c_1.parquet')\n\ntest_static_0_0 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_static_0_0.parquet')\ntest_static_0_1 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_static_0_1.parquet')\ntest_static_cb_0 = pd.read_parquet('/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/test_static_cb_0.parquet')\n","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:27.093771Z","iopub.execute_input":"2024-02-18T16:26:27.094431Z","iopub.status.idle":"2024-02-18T16:26:27.193549Z","shell.execute_reply.started":"2024-02-18T16:26:27.094400Z","shell.execute_reply":"2024-02-18T16:26:27.192726Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"debit_card_final = agg_appl_prev_1_tables(debit_card)\ndeposit_final = agg_appl_prev_1_tables(deposit)\nperson_final = agg_appl_prev_1_tables(person)\nother_final = agg_appl_prev_1_tables(other)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:32.804924Z","iopub.execute_input":"2024-02-18T16:26:32.805764Z","iopub.status.idle":"2024-02-18T16:26:32.838979Z","shell.execute_reply.started":"2024-02-18T16:26:32.805733Z","shell.execute_reply":"2024-02-18T16:26:32.838032Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del debit_card, deposit, person, other","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:34.981632Z","iopub.execute_input":"2024-02-18T16:26:34.982317Z","iopub.status.idle":"2024-02-18T16:26:34.986791Z","shell.execute_reply.started":"2024-02-18T16:26:34.982283Z","shell.execute_reply":"2024-02-18T16:26:34.985798Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tax_registry_a_final = agg_appl_prev_1_tables(tax_registry_a)\ntax_registry_b_final = agg_appl_prev_1_tables(tax_registry_b)\ntax_registry_c_final = agg_appl_prev_1_tables(tax_registry_c)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:35.416686Z","iopub.execute_input":"2024-02-18T16:26:35.417235Z","iopub.status.idle":"2024-02-18T16:26:35.434719Z","shell.execute_reply.started":"2024-02-18T16:26:35.417207Z","shell.execute_reply":"2024-02-18T16:26:35.433859Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del tax_registry_a,tax_registry_b,tax_registry_c","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:35.850224Z","iopub.execute_input":"2024-02-18T16:26:35.850523Z","iopub.status.idle":"2024-02-18T16:26:35.854875Z","shell.execute_reply.started":"2024-02-18T16:26:35.850489Z","shell.execute_reply":"2024-02-18T16:26:35.853840Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tax_registry_c_final.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:36.268183Z","iopub.execute_input":"2024-02-18T16:26:36.268725Z","iopub.status.idle":"2024-02-18T16:26:36.274414Z","shell.execute_reply.started":"2024-02-18T16:26:36.268698Z","shell.execute_reply":"2024-02-18T16:26:36.273315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_base_cb = test_base_file.merge(cb_1_final_test , on = 'case_id' , how = 'left')\ntest_base_cb = test_base_cb.merge(appl_prev_1_final_test , on = 'case_id' , how = 'left')\ntest_base_cb = test_base_cb.merge(debit_card_final , on = 'case_id' , how = 'left')\ntest_base_cb = test_base_cb.merge(person_final , on = 'case_id' , how = 'left')\n\ntest_base_cb = test_base_cb.merge(deposit_final , on = 'case_id' , how = 'left')\ntest_base_cb = test_base_cb.merge(other_final , on = 'case_id' , how = 'left')\ntest_base_cb = test_base_cb.merge(tax_registry_a_final , on = 'case_id' , how = 'left')\ntest_base_cb = test_base_cb.merge(tax_registry_b_final , on = 'case_id' , how = 'left')\ntest_base_cb = test_base_cb.merge(tax_registry_c_final , on = 'case_id' , how = 'left')\n\n# test_base_cb = test_base_cb.merge(test_static_0_0 , on = 'case_id' , how = 'left')\n# test_base_cb = test_base_cb.merge(test_static_0_1 , on = 'case_id' , how = 'left')\n# test_base_cb = test_base_cb.merge(test_static_cb_0 , on = 'case_id' , how = 'left')","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:36.701318Z","iopub.execute_input":"2024-02-18T16:26:36.701949Z","iopub.status.idle":"2024-02-18T16:26:36.736166Z","shell.execute_reply.started":"2024-02-18T16:26:36.701920Z","shell.execute_reply":"2024-02-18T16:26:36.735400Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_base_cb = test_base_cb.drop_duplicates()","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:37.904588Z","iopub.execute_input":"2024-02-18T16:26:37.905482Z","iopub.status.idle":"2024-02-18T16:26:37.931193Z","shell.execute_reply.started":"2024-02-18T16:26:37.905448Z","shell.execute_reply":"2024-02-18T16:26:37.930101Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del cb_1_final_test, appl_prev_1_final_test, debit_card_final, person_final, deposit_final, other_final, tax_registry_a_final, tax_registry_b_final, tax_registry_c_final","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:39.769767Z","iopub.execute_input":"2024-02-18T16:26:39.770411Z","iopub.status.idle":"2024-02-18T16:26:39.775238Z","shell.execute_reply.started":"2024-02-18T16:26:39.770378Z","shell.execute_reply":"2024-02-18T16:26:39.774198Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Merging All Tables","metadata":{}},{"cell_type":"code","source":"test_base_cb.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:26:40.804075Z","iopub.execute_input":"2024-02-18T16:26:40.804992Z","iopub.status.idle":"2024-02-18T16:26:40.810845Z","shell.execute_reply.started":"2024-02-18T16:26:40.804960Z","shell.execute_reply":"2024-02-18T16:26:40.809887Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_base_cb['score'] =base_xgb_1.predict_proba(test_base_cb[selected_feats])[:,1]","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:27:04.781479Z","iopub.execute_input":"2024-02-18T16:27:04.781961Z","iopub.status.idle":"2024-02-18T16:27:04.833418Z","shell.execute_reply.started":"2024-02-18T16:27:04.781924Z","shell.execute_reply":"2024-02-18T16:27:04.832663Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_base_cb['score']","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:27:15.011446Z","iopub.execute_input":"2024-02-18T16:27:15.012171Z","iopub.status.idle":"2024-02-18T16:27:15.019973Z","shell.execute_reply.started":"2024-02-18T16:27:15.012136Z","shell.execute_reply":"2024-02-18T16:27:15.018934Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission = test_base_cb[['case_id','score']]","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:28:19.101470Z","iopub.execute_input":"2024-02-18T16:28:19.102287Z","iopub.status.idle":"2024-02-18T16:28:19.107825Z","shell.execute_reply.started":"2024-02-18T16:28:19.102252Z","shell.execute_reply":"2024-02-18T16:28:19.106664Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:28:25.617661Z","iopub.execute_input":"2024-02-18T16:28:25.618088Z","iopub.status.idle":"2024-02-18T16:28:25.628305Z","shell.execute_reply.started":"2024-02-18T16:28:25.618052Z","shell.execute_reply":"2024-02-18T16:28:25.627237Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission.to_csv('submission.csv',index=False)","metadata":{"execution":{"iopub.status.busy":"2024-02-18T16:32:38.469471Z","iopub.execute_input":"2024-02-18T16:32:38.469988Z","iopub.status.idle":"2024-02-18T16:32:38.476424Z","shell.execute_reply.started":"2024-02-18T16:32:38.469946Z","shell.execute_reply":"2024-02-18T16:32:38.475484Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}