{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":50160,"databundleVersionId":7921029,"sourceType":"competition"}],"dockerImageVersionId":30646,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Home Credit Load Test Data P1\n### -Andrea Adams and Kevin Wang\nApprox 8GB to run","metadata":{}},{"cell_type":"code","source":"#load the libraries\nimport polars as pl\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.190553Z","iopub.execute_input":"2024-03-17T17:28:33.191515Z","iopub.status.idle":"2024-03-17T17:28:33.711707Z","shell.execute_reply.started":"2024-03-17T17:28:33.191466Z","shell.execute_reply":"2024-03-17T17:28:33.710501Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pathway = \"/kaggle/input/home-credit-credit-risk-model-stability/\"","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.713320Z","iopub.execute_input":"2024-03-17T17:28:33.713768Z","iopub.status.idle":"2024-03-17T17:28:33.719702Z","shell.execute_reply.started":"2024-03-17T17:28:33.713739Z","shell.execute_reply":"2024-03-17T17:28:33.718366Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### UDF","metadata":{}},{"cell_type":"code","source":"def set_table_dtypes(df: pl.DataFrame)-> pl.DataFrame:\n    for col in df.columns:\n        # Cast Transform DPD (Days past due, P) and Transform Amount (A) as Float64\n        if col[-1] in (\"P\", \"A\"):\n            df = df.with_columns(pl.col(col).cast(pl.Float64).alias(col))\n        # Cast Transform date (D) as Date\n        if col[-1] in (\"D\"):\n            df = df.with_columns(pl.col(col).cast(pl.Date).alias(col))\n        # Cast aggregated columns as Float64, tried combining sum and max, but did not work correctly\n        if col[-4:-1] in ('_sum'):\n            df = df.with_columns(pl.col(col).cast(pl.Float64).alias(col))\n        if col[-4:-1] in ('_max'):\n            df = df.with_columns(pl.col(col).cast(pl.Float64).alias(col))\n    return df\n\ndef convert_strings(df: pl.DataFrame) -> pl.DataFrame:\n    for col in df.columns:\n        if df[col].dtype == pl.Utf8:\n            df = df.with_columns(pl.col(col).cast(pl.Categorical))\n    return df\n\n# Changed this function to work for Pandas\ndef missing_values(df, threshold = 0.0):\n    for col in df.columns:\n        decimal = (pd.isnull(test[col]).sum())/(len(test[col]))\n        if decimal > threshold:                                         \n            print(f\"{col}: {decimal}\")\n\n# Impute numeric columns with the median and cat with mode\ndef imputer(df:pd.DataFrame) -> pd.DataFrame:\n    for col in df.columns:\n        if df[col].dtype == 'float64':\n            df[col] = df[col].fillna(df[col].median())\n        if df[col].dtype.name in ['category','object'] and df[col].isnull().any():\n            mode_without_nan = df[col].dropna().mode().values[0]\n            df[col] = df[col].fillna(mode_without_nan)\n    return df","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.721681Z","iopub.execute_input":"2024-03-17T17:28:33.722135Z","iopub.status.idle":"2024-03-17T17:28:33.737112Z","shell.execute_reply.started":"2024-03-17T17:28:33.722095Z","shell.execute_reply":"2024-03-17T17:28:33.735741Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Join Data 1","metadata":{}},{"cell_type":"code","source":"test_basetable = pl.read_csv(pathway + \"csv_files/test/test_base.csv\")\ntest_static = pl.concat(\n    [pl.read_csv(pathway + \"csv_files/test/test_static_0_0.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_static_0_1.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_static_0_2.csv\").pipe(set_table_dtypes)\n    ], how=\"vertical_relaxed\")\ntest_static_cb=pl.read_csv(pathway + \"csv_files/test/test_static_cb_0.csv\").pipe(set_table_dtypes)\ntest_person_1=pl.read_csv(pathway +  \"csv_files/test/test_person_1.csv\").pipe(set_table_dtypes)\ntest_credit_bureau_b_2=pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_b_2.csv\").pipe(set_table_dtypes)","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.740736Z","iopub.execute_input":"2024-03-17T17:28:33.741323Z","iopub.status.idle":"2024-03-17T17:28:33.865872Z","shell.execute_reply.started":"2024-03-17T17:28:33.741276Z","shell.execute_reply":"2024-03-17T17:28:33.864668Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Additional depth=1 files\ntest_other_1 = pl.read_csv(pathway + \"csv_files/test/test_other_1.csv\").pipe(set_table_dtypes)\n\ntest_credit_bureau_b_1 = pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_b_1.csv\").pipe(set_table_dtypes)\n\ntest_deposit_1 = pl.read_csv(pathway + \"csv_files/test/test_deposit_1.csv\").pipe(set_table_dtypes)\n\n# test_debitcard_1 = pl.read_csv(pathway + \"csv_files/test/test_debitcard_1.csv\").pipe(set_table_dtypes)","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.867088Z","iopub.execute_input":"2024-03-17T17:28:33.867440Z","iopub.status.idle":"2024-03-17T17:28:33.883820Z","shell.execute_reply.started":"2024-03-17T17:28:33.867411Z","shell.execute_reply":"2024-03-17T17:28:33.882059Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Code to test converting dtype from str to date in test_basetable\ntest_basetable = test_basetable.with_columns(pl.col('date_decision').cast(pl.Date))","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.885490Z","iopub.execute_input":"2024-03-17T17:28:33.887009Z","iopub.status.idle":"2024-03-17T17:28:33.892485Z","shell.execute_reply.started":"2024-03-17T17:28:33.886963Z","shell.execute_reply":"2024-03-17T17:28:33.891322Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#use aggregation functions in tables with depth >=1\n\ntest_person_1_feats_1 = test_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").sum().alias(\"mainoccupationinc_384A_sum\"))\n\n#num_group1=0 represents the person who applied for the loan\ntest_person_1_feats_2 = test_person_1.select([\"case_id\", \"num_group1\", \"incometype_1044T\", \"birth_259D\",\n    \"empl_employedfrom_271D\",\"empl_industry_691L\",\"familystate_447L\",\"sex_738L\",\"type_25L\",\n    \"safeguarantyflag_411L\",\"empl_employedtotal_800L\",\"role_1084L\"]).filter(\n    pl.col(\"num_group1\")==0).drop(\"num_group1\")\n\n\n#we now have num_group1 and num_group2, so aggregate again\ntest_credit_bureau_b_2_feats = test_credit_bureau_b_2.group_by(\"case_id\").agg(\n    pl.col(\"pmts_pmtsoverdue_635A\").sum().alias(\"pmts_pmtsoverdue_635A_sum\"),\n    pl.col(\"pmts_dpdvalue_108P\").sum().alias(\"pmts_dpdvalue_108P_sum\"))\n","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.893722Z","iopub.execute_input":"2024-03-17T17:28:33.894625Z","iopub.status.idle":"2024-03-17T17:28:33.906504Z","shell.execute_reply.started":"2024-03-17T17:28:33.894588Z","shell.execute_reply":"2024-03-17T17:28:33.905261Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Additional aggregation for depth=1 files\ntest_other_1_feats = test_other_1.group_by(\"case_id\").agg(\n    pl.col(\"amtdebitincoming_4809443A\").sum().alias(\"amtdebitincoming_4809443A_sum\"),\n    pl.col(\"amtdebitoutgoing_4809440A\").sum().alias(\"amtdebitoutgoing_4809440A_sum\"),\n    pl.col(\"amtdepositbalance_4809441A\").sum().alias(\"amtdepositbalance_4809441A_sum\"),\n    pl.col(\"amtdepositincoming_4809444A\").sum().alias(\"amtdepositincoming_4809444A_sum\"),\n    pl.col(\"amtdepositoutgoing_4809442A\").sum().alias(\"amtdepositoutgoing_4809442A_sum\"))\n\ntest_credit_bureau_b_1_feats = test_credit_bureau_b_1.group_by(\"case_id\").agg(\n    pl.col(\"amount_1115A\").sum().alias(\"amount_1115A_sum\"),\n    pl.col(\"credquantity_1099L\").sum().alias(\"credquantity_1099L_sum\"),\n    pl.col(\"credquantity_984L\").sum().alias(\"credquantity_984L_sum\"),\n    pl.col(\"debtpastduevalue_732A\").sum().alias(\"debtpastduevalue_732A_sum\"),\n    pl.col(\"debtvalue_227A\").sum().alias(\"debtvalue_227A_sum\"),\n    pl.col(\"dpd_550P\").sum().alias(\"dpd_550P_sum\"),\n    pl.col(\"dpd_733P\").sum().alias(\"dpd_733P_sum\"),\n    pl.col(\"dpdmax_851P\").max().alias(\"dpdmax_851P_max\"),\n    pl.col(\"installmentamount_644A\").sum().alias(\"installmentamount_644A_sum\"),\n    pl.col(\"installmentamount_833A\").sum().alias(\"installmentamount_833A_sum\"),\n    pl.col(\"instlamount_892A\").sum().alias(\"instlamount_892A_sum\"),\n    pl.col(\"interestrateyearly_538L\").max().alias(\"interestrateyearly_538L_max\"),\n    pl.col(\"maxdebtpduevalodued_3940955A\").max().alias(\"maxdebtpduevalodued_3940955A_max\"),\n    pl.col(\"numberofinstls_810L\").sum().alias(\"numberofinstls_810L_sum\"),\n    pl.col(\"overdueamountmax_950A\").max().alias(\"overdueamountmax_950A_max\"),\n    pl.col(\"pmtdaysoverdue_1135P\").sum().alias(\"pmtdaysoverdue_1135P_sum\"),\n    pl.col(\"pmtnumpending_403L\").sum().alias(\"pmtnumpending_403L_sum\"),\n    pl.col(\"residualamount_3940956A\").sum().alias(\"residualamount_3940956A_sum\"),\n    pl.col(\"totalamount_503A\").sum().alias(\"totalamount_503A_sum\"),\n    pl.col(\"totalamount_881A\").sum().alias(\"totalamount_881A_sum\"))\n\ntest_deposit_1_feats = test_deposit_1.group_by(\"case_id\").agg(\n    pl.col(\"amount_416A\").sum().alias(\"amount_416A_sum\"))","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.907891Z","iopub.execute_input":"2024-03-17T17:28:33.908323Z","iopub.status.idle":"2024-03-17T17:28:33.924081Z","shell.execute_reply.started":"2024-03-17T17:28:33.908290Z","shell.execute_reply":"2024-03-17T17:28:33.922888Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# join all tables/columns together\n\njoin_data1 = test_basetable.join(test_static, how=\"left\", on=\"case_id\"\n).join(test_static_cb, how=\"left\", on=\"case_id\"\n).join(test_person_1_feats_1, how=\"left\", on=\"case_id\"\n).join(test_person_1_feats_2, how=\"left\", on=\"case_id\"\n).join(test_credit_bureau_b_2_feats, how=\"left\", on=\"case_id\"\n).join(test_other_1_feats, how=\"left\", on=\"case_id\"\n).join(test_credit_bureau_b_1_feats, how=\"left\", on=\"case_id\"\n).join(test_deposit_1_feats, how=\"left\", on=\"case_id\"\n)","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.925501Z","iopub.execute_input":"2024-03-17T17:28:33.925909Z","iopub.status.idle":"2024-03-17T17:28:33.941978Z","shell.execute_reply.started":"2024-03-17T17:28:33.925878Z","shell.execute_reply":"2024-03-17T17:28:33.940490Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 263 columns\njoin_data1.shape","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.947551Z","iopub.execute_input":"2024-03-17T17:28:33.947941Z","iopub.status.idle":"2024-03-17T17:28:33.956285Z","shell.execute_reply.started":"2024-03-17T17:28:33.947914Z","shell.execute_reply":"2024-03-17T17:28:33.954892Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# drop_cols = list_missing(join_data1)\ndrop_cols = ['avgdbddpdlast3m_4187120P', 'avgdbdtollast24m_4525197P', 'avglnamtstart24m_4525187A', 'avgpmtlast12m_4525200A', 'bankacctype_710L', 'cardtype_51L', 'clientscnt_136L', 'datelastinstal40dpd_247D', 'dtlastpmtallstes_4499206D', 'equalitydataagreement_891L', 'equalityempfrom_62L', 'inittransactionamount_650A', 'interestrategrace_34L', 'isbidproductrequest_292L', 'isdebitcard_729L', 'lastdelinqdate_224D', 'lastdependentsnum_448L', 'lastotherinc_902A', 'lastotherlnsexpense_631A', 'lastrepayingdate_696D', 'maxannuity_4075009A', 'maxdbddpdlast1m_3658939P', 'maxlnamtstart6m_4525199A', 'maxpmtlast3m_4525190A', 'mindbdtollast24m_4525191P', 'payvacationpostpone_4187118D', 'totinstallast1m_4525188A', 'typesuite_864L', 'validfrom_1069D', 'assignmentdate_238D', 'assignmentdate_4527235D', 'assignmentdate_4955616D', 'birthdate_574D', 'contractssum_5085716L', 'dateofbirth_342D', 'for3years_128L', 'for3years_504L', 'for3years_584L', 'formonth_118L', 'formonth_206L', 'formonth_535L', 'forquarter_1017L', 'forquarter_462L', 'forquarter_634L', 'fortoday_1092L', 'forweek_1077L', 'forweek_528L', 'forweek_601L', 'foryear_618L', 'foryear_818L', 'foryear_850L', 'pmtaverage_3A', 'pmtaverage_4527227A', 'pmtaverage_4955615A', 'pmtcount_4527229L', 'pmtcount_4955617L', 'pmtcount_693L', 'pmtscount_423L', 'pmtssum_45A', 'responsedate_4917613D', 'riskassesment_302T', 'riskassesment_940T', 'empl_employedfrom_271D', 'empl_industry_691L', 'empl_employedtotal_800L', 'pmts_pmtsoverdue_635A_sum', 'pmts_dpdvalue_108P_sum', 'amtdebitincoming_4809443A_sum', 'amtdebitoutgoing_4809440A_sum', 'amtdepositbalance_4809441A_sum', 'amtdepositincoming_4809444A_sum', 'amtdepositoutgoing_4809442A_sum', 'amount_1115A_sum', 'credquantity_1099L_sum', 'credquantity_984L_sum', 'debtpastduevalue_732A_sum', 'debtvalue_227A_sum', 'dpd_550P_sum', 'dpd_733P_sum', 'dpdmax_851P_max', 'installmentamount_644A_sum', 'installmentamount_833A_sum', 'instlamount_892A_sum', 'interestrateyearly_538L_max', 'maxdebtpduevalodued_3940955A_max', 'numberofinstls_810L_sum', 'overdueamountmax_950A_max', 'pmtdaysoverdue_1135P_sum', 'pmtnumpending_403L_sum', 'residualamount_3940956A_sum', 'totalamount_503A_sum', 'totalamount_881A_sum', 'amount_416A_sum']\n# print(drop_cols)","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.957801Z","iopub.execute_input":"2024-03-17T17:28:33.958179Z","iopub.status.idle":"2024-03-17T17:28:33.969014Z","shell.execute_reply.started":"2024-03-17T17:28:33.958131Z","shell.execute_reply":"2024-03-17T17:28:33.967320Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 170 cols after dropping > 60% missing\njoin_data1 = join_data1.drop(drop_cols)\njoin_data1.shape","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.971072Z","iopub.execute_input":"2024-03-17T17:28:33.971534Z","iopub.status.idle":"2024-03-17T17:28:33.990874Z","shell.execute_reply.started":"2024-03-17T17:28:33.971501Z","shell.execute_reply":"2024-03-17T17:28:33.989599Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Join Data 2","metadata":{}},{"cell_type":"code","source":"# Additional testing data, depth = 1\ntest_applprev_1 = pl.concat(\n    [pl.read_csv(pathway + \"csv_files/test/test_applprev_1_0.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_applprev_1_1.csv\").pipe(set_table_dtypes) \n    ], how=\"vertical_relaxed\")\n\ntest_tax_registry_a_1 = pl.read_csv(pathway + \"csv_files/test/test_tax_registry_a_1.csv\").pipe(set_table_dtypes)\ntest_tax_registry_b_1 = pl.read_csv(pathway + \"csv_files/test/test_tax_registry_b_1.csv\").pipe(set_table_dtypes)    \ntest_tax_registry_c_1 = pl.read_csv(pathway + \"csv_files/test/test_tax_registry_c_1.csv\").pipe(set_table_dtypes)\n    \ntest_credit_bureau_a_1 = pl.concat(\n    [pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_1_0.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_1_1.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_1_2.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_1_3.csv\").pipe(set_table_dtypes),\n    ], how=\"vertical_relaxed\")","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:33.993115Z","iopub.execute_input":"2024-03-17T17:28:33.993573Z","iopub.status.idle":"2024-03-17T17:28:34.032000Z","shell.execute_reply.started":"2024-03-17T17:28:33.993532Z","shell.execute_reply":"2024-03-17T17:28:34.030832Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"selection = ['credacc_actualbalance_314A', 'credacc_maxhisbal_375A', 'credacc_minhisbal_90A', 'credacc_status_367L', 'credacc_transactions_402L', 'isdebitcard_527L', 'revolvingaccount_394A']\ntest_applprev_1 = test_applprev_1.drop(selection)\n\nselection = ['annualeffectiverate_199L', 'annualeffectiverate_63L', 'contractsum_5085717L', 'credlmt_230A', 'credlmt_935A', 'debtoutstand_525A', 'debtoverdue_47A', 'instlamount_768A', 'instlamount_852A', 'interestrate_508L', 'nominalrate_281L', 'numberofcontrsvalue_258L', 'numberofcontrsvalue_358L', 'numberofinstls_320L', 'numberofoutstandinstls_59L', 'numberofoverdueinstlmaxdat_641D', 'outstandingamount_362A', 'overdueamountmax2date_1142D', 'periodicityofpmts_837L', 'prolongationcount_1120L', 'prolongationcount_599L', 'residualamount_488A', 'residualamount_856A', 'totalamount_996A', 'totaldebtoverduevalue_178A', 'totaldebtoverduevalue_718A', 'totaloutstanddebtvalue_39A', 'totaloutstanddebtvalue_668A']\ntest_credit_bureau_a_1 = test_credit_bureau_a_1.drop(selection)\n\n# Change L columns to float64\nfor col in test_credit_bureau_a_1.columns:\n        if col[-1] in (\"L\"):\n            test_credit_bureau_a_1 = test_credit_bureau_a_1.with_columns(pl.col(col).cast(pl.Float64).alias(col))","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.033683Z","iopub.execute_input":"2024-03-17T17:28:34.034041Z","iopub.status.idle":"2024-03-17T17:28:34.043239Z","shell.execute_reply.started":"2024-03-17T17:28:34.034011Z","shell.execute_reply":"2024-03-17T17:28:34.041848Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_applprev_1_feats_1 = test_applprev_1.group_by(\"case_id\").agg(\n    pl.col(\"actualdpd_943P\").sum().alias(\"actualdpd_943P_sum\"),\n    pl.col(\"annuity_853A\").sum().alias(\"annuity_853A_sum\"),\n    pl.col(\"byoccupationinc_3656910L\").max().alias(\"byoccupationinc_3656910L_max\"),\n    pl.col(\"childnum_21L\").max().alias(\"childnum_21L_max\"),\n    pl.col(\"credacc_credlmt_575A\").max().alias(\"credacc_credlmt_575A_max\"),\n    pl.col(\"currdebt_94A\").sum().alias(\"currdebt_94A_sum\"),\n    pl.col(\"downpmt_134A\").sum().alias(\"downpmt_134A_sum\"),\n    pl.col(\"isbidproduct_390L\").max(),\n    pl.col(\"mainoccupationinc_437A\").sum().alias(\"mainoccupationinc_437A_sum\"),\n    pl.col(\"maxdpdtolerance_577P\").max().alias(\"maxdpdtolerance_577P_max\"),\n    pl.col(\"outstandingdebt_522A\").sum().alias(\"outstandingdebt_522A_sum\"),\n    pl.col(\"pmtnum_8L\").sum().alias(\"pmtnum_8L_sum\"),\n    pl.col(\"tenor_203L\").sum().alias(\"tenor_203L_sum\"))\n\ntest_applprev_1_feats_2 = test_applprev_1.select([\"case_id\", \"num_group1\",\n    \"credtype_587L\",\"familystate_726L\",\"inittransactioncode_279L\",\"status_219L\"]).filter(\n    pl.col(\"num_group1\")==0).drop(\"num_group1\")\n\ntest_tax_registry_a_1_feats = test_tax_registry_a_1.group_by(\"case_id\").agg(\n    pl.col(\"amount_4527230A\").sum().alias(\"amount_4527230A_sum\"))\n\ntest_tax_registry_b_1_feats = test_tax_registry_b_1.group_by(\"case_id\").agg(\n    pl.col(\"amount_4917619A\").sum().alias(\"amount_4917619A_sum\"))\n\ntest_tax_registry_c_1_feats = test_tax_registry_c_1.group_by(\"case_id\").agg(\n    pl.col(\"pmtamount_36A\").sum().alias(\"pmtamount_36A_sum\"))\n\ntest_credit_bureau_a_1_feats = test_credit_bureau_a_1.group_by(\"case_id\").agg(\n    pl.col(\"dpdmax_139P\").max().alias(\"dpdmax_139P_max\"),\n    pl.col(\"dpdmax_757P\").max().alias(\"dpdmax_757P_max\"),\n    pl.col(\"monthlyinstlamount_332A\").sum().alias(\"monthlyinstlamount_332A_sum\"),\n    pl.col(\"monthlyinstlamount_674A\").sum().alias(\"monthlyinstlamount_674A_sum\"),\n    pl.col(\"nominalrate_498L\").max().alias(\"nominalrate_498L_max\"),\n    pl.col(\"numberofinstls_229L\").sum().alias(\"numberofinstls_229L_sum\"),\n    pl.col(\"numberofoutstandinstls_520L\").sum().alias(\"numberofoutstandinstls_520L_sum\"),\n    pl.col(\"numberofoverdueinstlmax_1039L\").sum().alias(\"numberofoverdueinstlmax_1039L_sum\"),\n    pl.col(\"numberofoverdueinstlmax_1151L\").sum().alias(\"numberofoverdueinstlmax_1151L_sum\"),\n    pl.col(\"numberofoverdueinstls_725L\").sum().alias(\"numberofoverdueinstls_725L_sum\"),\n    pl.col(\"numberofoverdueinstls_834L\").sum().alias(\"numberofoverdueinstls_834L_sum\"),\n    pl.col(\"outstandingamount_354A\").sum().alias(\"outstandingamount_354A_sum\"),\n    pl.col(\"overdueamount_31A\").sum().alias(\"overdueamount_31A_sum\"),\n    pl.col(\"overdueamount_659A\").sum().alias(\"overdueamount_659A_sum\"),\n    pl.col(\"overdueamountmax2_14A\").max().alias(\"overdueamountmax2_14A_max\"),\n    pl.col(\"overdueamountmax2_398A\").max().alias(\"overdueamountmax2_398A_max\"),\n    pl.col(\"overdueamountmax_155A\").max().alias(\"overdueamountmax_155A_max\"),\n    pl.col(\"overdueamountmax_35A\").max().alias(\"overdueamountmax_35A_max\"),\n    pl.col(\"periodicityofpmts_1102L\").max().alias(\"periodicityofpmts_1102L_max\"),\n    pl.col(\"totalamount_6A\").sum().alias(\"totalamount_6A_sum\"))","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.044658Z","iopub.execute_input":"2024-03-17T17:28:34.044997Z","iopub.status.idle":"2024-03-17T17:28:34.066053Z","shell.execute_reply.started":"2024-03-17T17:28:34.044970Z","shell.execute_reply":"2024-03-17T17:28:34.064588Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_tax_registry_c_1_feats= test_tax_registry_c_1_feats.with_columns(pl.col('case_id').cast(pl.Int64))","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.067938Z","iopub.execute_input":"2024-03-17T17:28:34.068399Z","iopub.status.idle":"2024-03-17T17:28:34.079529Z","shell.execute_reply.started":"2024-03-17T17:28:34.068368Z","shell.execute_reply":"2024-03-17T17:28:34.078148Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"join_data2 = test_basetable.join(test_applprev_1_feats_1, how=\"left\", on=\"case_id\"\n).join(test_applprev_1_feats_2, how=\"left\", on=\"case_id\"\n).join(test_tax_registry_a_1_feats, how=\"left\", on=\"case_id\"\n).join(test_tax_registry_b_1_feats, how=\"left\", on=\"case_id\"\n).join(test_tax_registry_c_1_feats, how=\"left\", on=\"case_id\"\n).join(test_credit_bureau_a_1_feats, how=\"left\", on=\"case_id\")\n\njoin_data2 = join_data2.drop(['date_decision','MONTH','WEEK_NUM'])\ndrop_cols = ['byoccupationinc_3656910L_max', 'familystate_726L', 'amount_4527230A_sum', 'amount_4917619A_sum', 'pmtamount_36A_sum']\njoin_data2 = join_data2.drop(drop_cols)\njoin_data2.shape","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.082314Z","iopub.execute_input":"2024-03-17T17:28:34.082718Z","iopub.status.idle":"2024-03-17T17:28:34.098148Z","shell.execute_reply.started":"2024-03-17T17:28:34.082688Z","shell.execute_reply":"2024-03-17T17:28:34.097130Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Join Data 3","metadata":{}},{"cell_type":"code","source":"# Additional testing data, depth = 2\ntest_applprev_2 = pl.read_csv(pathway + \"csv_files/test/test_applprev_2.csv\").pipe(set_table_dtypes)\n\ntest_person_2 = pl.read_csv(pathway + \"csv_files/test/test_person_2.csv\").pipe(set_table_dtypes)\n\ntest_credit_bureau_a_2_0 = pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_0.csv\").pipe(set_table_dtypes)\n\nsel = ['case_id', 'collater_valueofguarantee_1124L','num_group1','num_group2','pmts_dpd_1073P','pmts_dpd_303P','pmts_overdue_1140A','pmts_overdue_1152A']\ntest_credit_bureau_a_2 = pl.concat(\n    [pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_0.csv\",columns=sel).pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_1.csv\",columns=sel).pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_2.csv\",columns=sel).pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_3.csv\",columns=sel).pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_4.csv\",columns=sel).pipe(set_table_dtypes),\n    \n     pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_5.csv\",columns=sel).pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_6.csv\",columns=sel).pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_7.csv\",columns=sel).pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_8.csv\",columns=sel).pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_9.csv\",columns=sel).pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/test/test_credit_bureau_a_2_10.csv\",columns=sel).pipe(set_table_dtypes)\n    ], how=\"vertical_relaxed\")","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.099492Z","iopub.execute_input":"2024-03-17T17:28:34.099841Z","iopub.status.idle":"2024-03-17T17:28:34.136611Z","shell.execute_reply.started":"2024-03-17T17:28:34.099812Z","shell.execute_reply":"2024-03-17T17:28:34.135691Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_applprev_2 = test_applprev_2.drop('credacc_cards_status_52L')\n\nselection = ['addres_role_871L', 'empls_employedfrom_796D', 'relatedpersons_role_762T']\ntest_person_2 = test_person_2.drop(selection)\n\ntest_credit_bureau_a_2 = test_credit_bureau_a_2.drop('collater_valueofguarantee_1124L')","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.137798Z","iopub.execute_input":"2024-03-17T17:28:34.138527Z","iopub.status.idle":"2024-03-17T17:28:34.146257Z","shell.execute_reply.started":"2024-03-17T17:28:34.138482Z","shell.execute_reply":"2024-03-17T17:28:34.144825Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_applprev_2_feats = test_applprev_2.select([\"case_id\", \"num_group1\", \"num_group2\",\n    \"conts_type_509L\"]).filter(\n    (pl.col(\"num_group1\")==0) & (pl.col(\"num_group2\")==0)).drop(\"num_group1\").drop(\"num_group2\") \n\ntest_credit_bureau_a_2_feats = test_credit_bureau_a_2.group_by(\"case_id\").agg(\n    pl.col(\"pmts_dpd_1073P\").sum().alias(\"pmts_dpd_1073P_sum\"),\n    pl.col(\"pmts_dpd_303P\").sum().alias(\"pmts_dpd_303P_sum\"),\n    pl.col(\"pmts_overdue_1140A\").sum().alias(\"pmts_overdue_1140A_sum\"),\n    pl.col(\"pmts_overdue_1152A\").sum().alias(\"pmts_overdue_1152A_sum\"))\n\ntest_person_2_feats = test_person_2.select([\"case_id\", \"num_group1\", \"num_group2\", \"addres_zip_823M\",\n    \"addres_district_368M\",\"conts_role_79M\",\"empls_economicalst_849M\",\"empls_employer_name_740M\"]).filter(\n    (pl.col(\"num_group1\")==0) & (pl.col(\"num_group2\")==0)).drop(\"num_group1\").drop(\"num_group2\")","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.147803Z","iopub.execute_input":"2024-03-17T17:28:34.148213Z","iopub.status.idle":"2024-03-17T17:28:34.172507Z","shell.execute_reply.started":"2024-03-17T17:28:34.148114Z","shell.execute_reply":"2024-03-17T17:28:34.171051Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"join_data3 = test_basetable.join(test_applprev_2_feats, how=\"left\", on=\"case_id\"\n).join(test_credit_bureau_a_2_feats, how=\"left\", on=\"case_id\").join(test_person_2_feats, how=\"left\", on=\"case_id\")\n\njoin_data3 = join_data3.drop(['date_decision','MONTH','WEEK_NUM'])","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.174014Z","iopub.execute_input":"2024-03-17T17:28:34.174908Z","iopub.status.idle":"2024-03-17T17:28:34.186499Z","shell.execute_reply.started":"2024-03-17T17:28:34.174873Z","shell.execute_reply":"2024-03-17T17:28:34.185473Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"join_test = join_data1.join(join_data2, how=\"left\", on=\"case_id\"\n).join(join_data3, how=\"left\", on=\"case_id\")\nselection = ['avgoutstandbalancel6m_4187114A', 'datefirstoffer_1144D', 'datelastunpaid_3546854D', 'firstclxcampaign_1125D', 'lastrejectcredamount_222A', 'lastrejectdate_50D', 'maxdbddpdtollast6m_4187119P', 'maxdpdinstldate_3546855D', 'maxdpdinstlnum_3546846P', 'maxoutstandbalancel12m_4187113A', 'numinstmatpaidtearly2d_4499204L', 'numinstpaid_4499208L', 'numinstpaidearly3dest_4493216L', 'numinstpaidearly5dest_4493211L', 'numinstpaidearly5dobd_4499205L', 'numinstpaidearlyest_4493214L', 'numinstregularpaidest_4493210L', 'numinsttopaygrest_4493213L', 'numinstunpaidmaxest_4493212L', 'sumoutstandtotalest_4493215A', 'requesttype_4525192L', 'responsedate_1012D', 'responsedate_4527233D', 'familystate_447L']\njoin_test = join_test.drop(selection)\njoin_test.shape","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.188192Z","iopub.execute_input":"2024-03-17T17:28:34.189847Z","iopub.status.idle":"2024-03-17T17:28:34.204474Z","shell.execute_reply.started":"2024-03-17T17:28:34.189777Z","shell.execute_reply":"2024-03-17T17:28:34.202334Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Final preprocessing / Create dummies","metadata":{}},{"cell_type":"code","source":"# Extract month and year as strings (not extracting day)\ntest = join_test.with_columns([pl.col('date_decision').dt.year().alias('year_decision').cast(pl.Utf8),\n                            pl.col('date_decision').dt.month().alias('month_decision').cast(pl.Utf8),\n                            pl.col('birth_259D').dt.year().alias('year_birth').cast(pl.Utf8),\n                            pl.col('birth_259D').dt.month().alias('month_birth').cast(pl.Utf8)\n                            ])\n# Drop uneeded date columns after extraction\ndate_list = ['date_decision','MONTH','firstdatedue_489D','lastactivateddate_801D',\n            'lastapplicationdate_877D', 'lastapprdate_640D', 'dateofbirth_337D','birth_259D']\ntest = test.pipe(set_table_dtypes).pipe(convert_strings).drop(date_list)\ntest.shape","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.206295Z","iopub.execute_input":"2024-03-17T17:28:34.206669Z","iopub.status.idle":"2024-03-17T17:28:34.229417Z","shell.execute_reply.started":"2024-03-17T17:28:34.206639Z","shell.execute_reply":"2024-03-17T17:28:34.228252Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Convert polars df to pandas so pandas specific methods/attributes work later\ntest = test.to_pandas()\n\n# Noticed some of these numeric variables were parsed as strings, changed them all to int \nnum_list = ['days120_123L','days180_256L','days30_165L','days360_512L','days90_310L','firstquarter_103L','numinstpaidlastcontr_4325080L',\n            'fourthquarter_440L','numinstlswithdpd5_4187116L','numberofqueries_373L', 'secondquarter_766L','thirdquarter_1082L']\nfor col in num_list:\n    test[col]=test[col].astype('float64')\n\ntest.head()","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.231191Z","iopub.execute_input":"2024-03-17T17:28:34.231892Z","iopub.status.idle":"2024-03-17T17:28:34.288487Z","shell.execute_reply.started":"2024-03-17T17:28:34.231848Z","shell.execute_reply":"2024-03-17T17:28:34.287329Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"selection = ['commnoinclast6m_3546845L', 'deferredmnthsnum_166L', 'mastercontrelectronic_519L', 'mastercontrexist_109L']\ntest.drop(columns=selection, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.290294Z","iopub.execute_input":"2024-03-17T17:28:34.290985Z","iopub.status.idle":"2024-03-17T17:28:34.299450Z","shell.execute_reply.started":"2024-03-17T17:28:34.290944Z","shell.execute_reply":"2024-03-17T17:28:34.298218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Medians and modes after imputation are the same, warning message does not seem to be an issue\ntest = imputer(test)","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.301257Z","iopub.execute_input":"2024-03-17T17:28:34.301936Z","iopub.status.idle":"2024-03-17T17:28:34.399374Z","shell.execute_reply.started":"2024-03-17T17:28:34.301896Z","shell.execute_reply":"2024-03-17T17:28:34.397812Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# See boolean columns\nbool_cols = test.select_dtypes(include=['bool']).columns.tolist()\n# Convert boolean columns to 0 or 1 (False or True)\nfor col in bool_cols:\n    test[col] = test[col].astype(int)\n# Check unique values of bool_cols\nfor col in bool_cols:\n    print(test[col].unique())","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.400851Z","iopub.execute_input":"2024-03-17T17:28:34.401239Z","iopub.status.idle":"2024-03-17T17:28:34.412218Z","shell.execute_reply.started":"2024-03-17T17:28:34.401207Z","shell.execute_reply":"2024-03-17T17:28:34.410967Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"drop_list = ['lastapprcommoditytypec_5251766M', 'lastrejectcommodtypec_5251769M','lastrejectcommoditycat_161M','lastrejectreasonclient_4145040M',\n            'previouscontdistrict_112M','lastapprcommoditycat_1041M', 'lastcancelreason_561M','lastrejectreason_759M', 'addres_zip_823M', 'addres_district_368M']\ntest.drop(columns=drop_list, inplace=True)\ntest.shape","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:34.417283Z","iopub.execute_input":"2024-03-17T17:28:34.417689Z","iopub.status.idle":"2024-03-17T17:28:34.431466Z","shell.execute_reply.started":"2024-03-17T17:28:34.417659Z","shell.execute_reply":"2024-03-17T17:28:34.430399Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_cols = test.select_dtypes(include=['category']).columns.tolist()\ntest[cat_cols].head()","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:28:36.416581Z","iopub.execute_input":"2024-03-17T17:28:36.417047Z","iopub.status.idle":"2024-03-17T17:28:36.453323Z","shell.execute_reply.started":"2024-03-17T17:28:36.417015Z","shell.execute_reply":"2024-03-17T17:28:36.451775Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test = pd.get_dummies(test, dtype=int, columns=cat_cols, sparse=True, drop_first=True)\ntest.head()","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:29:29.355406Z","iopub.execute_input":"2024-03-17T17:29:29.355843Z","iopub.status.idle":"2024-03-17T17:29:29.425492Z","shell.execute_reply.started":"2024-03-17T17:29:29.355811Z","shell.execute_reply":"2024-03-17T17:29:29.424257Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test.to_csv('test_final_dummy.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2024-03-17T17:29:32.026753Z","iopub.execute_input":"2024-03-17T17:29:32.027224Z","iopub.status.idle":"2024-03-17T17:29:32.049210Z","shell.execute_reply.started":"2024-03-17T17:29:32.027184Z","shell.execute_reply":"2024-03-17T17:29:32.047951Z"},"trusted":true},"execution_count":null,"outputs":[]}]}