{"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 Train Data P2","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\nfrom polars import selectors as cs","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pathway = \"/kaggle/input/home-credit-credit-risk-model-stability/\"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"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    return df\n\ndef missing_values(df):\n    for column in df.columns:\n        print(f\"{column}: {(df[column].is_null().sum() / len(df[column]))}\")\n\n# Returns a list of columns which have missing values > threshold\ndef list_missing(df, threshold=0.60):\n    list = []\n    for column in df.columns:\n        if (df[column].is_null().sum() / len(df[column])) > threshold:\n            list.append(column)\n    return list\n\ndef custom_range_agg(series: pl.series):\n    custom_range = series.max() - series.min()\n    return custom_range","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def compare_cols(df):\n    range_list = [element for e in df.columns for element in e.split() if element.endswith(\"_range\")]\n    sum_list = [name.replace('range', 'sum') for name in range_list]\n    length = len(df)\n\n    df=df.with_columns(*[pl.col(col).fill_null(strategy='zero') for col in range_list])\n    \n    for i in range(len(range_list)):\n        df = df.with_columns(check=\n        pl.when((df[range_list[i]] == df[sum_list[i]]) | (df[range_list[i]] == 0)).then(1).otherwise(0))\n\n        percent = df.select(pl.sum(\"check\")).item() / length\n        print(f\"Percent equal or 0 for {range_list[i]} = {percent:.3f}\")\n        \n    df = df.drop(\"check\")\n    return df","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_basetable = pl.read_csv(pathway + \"csv_files/train/train_base.csv\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Additional training data, depth = 1\ntrain_applprev_1 = pl.concat(\n    [pl.read_csv(pathway + \"csv_files/train/train_applprev_1_0.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/train/train_applprev_1_1.csv\").pipe(set_table_dtypes) \n    ], how=\"vertical_relaxed\")\n\ntrain_tax_registry_a_1 = pl.read_csv(pathway + \"csv_files/train/train_tax_registry_a_1.csv\").pipe(set_table_dtypes)\ntrain_tax_registry_b_1 = pl.read_csv(pathway + \"csv_files/train/train_tax_registry_b_1.csv\").pipe(set_table_dtypes)    \ntrain_tax_registry_c_1 = pl.read_csv(pathway + \"csv_files/train/train_tax_registry_c_1.csv\").pipe(set_table_dtypes)\n    \ntrain_credit_bureau_a_1 = pl.concat(\n    [pl.read_csv(pathway + \"csv_files/train/train_credit_bureau_a_1_0.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/train/train_credit_bureau_a_1_1.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/train/train_credit_bureau_a_1_2.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/train/train_credit_bureau_a_1_3.csv\").pipe(set_table_dtypes),\n    ], how=\"vertical_relaxed\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Look at data","metadata":{}},{"cell_type":"code","source":"#selection = list_missing(train_applprev_1, threshold=0.90)\nselection = ['credacc_actualbalance_314A', 'credacc_maxhisbal_375A', 'credacc_minhisbal_90A', 'credacc_status_367L', 'credacc_transactions_402L', 'isdebitcard_527L', 'revolvingaccount_394A']\ntrain_applprev_1 = train_applprev_1.drop(selection)\ntrain_applprev_1.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#selection = list_missing(train_credit_bureau_a_1, threshold=0.90)\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']\ntrain_credit_bureau_a_1 = train_credit_bureau_a_1.drop(selection)\n# L columns being parsed as str, should be float\n#train_credit_bureau_a_1.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Change L columns to float64\nfor col in train_credit_bureau_a_1.columns:\n        if col[-1] in (\"L\"):\n            train_credit_bureau_a_1 = train_credit_bureau_a_1.with_columns(pl.col(col).cast(pl.Float64).alias(col))","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Feature Engineering","metadata":{}},{"cell_type":"code","source":"train_applprev_1_feats_1 = train_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    custom_range_agg(pl.col(\"annuity_853A\")).alias('annuity_853A_range'),\n    pl.col(\"byoccupationinc_3656910L\").sum().alias(\"byoccupationinc_3656910L_sum\"),\n    custom_range_agg(pl.col(\"byoccupationinc_3656910L\")).alias('byoccupationinc_3656910L_range'),\n    pl.col(\"childnum_21L\").sum().alias(\"childnum_21L_sum\"),\n    custom_range_agg(pl.col(\"childnum_21L\")).alias('childnum_21L_range'),\n    pl.col(\"credacc_credlmt_575A\").sum().alias(\"credacc_credlmt_575A_sum\"),\n    custom_range_agg(pl.col(\"credacc_credlmt_575A\")).alias('credacc_credlmt_575A_range'),\n    pl.col(\"currdebt_94A\").sum().alias(\"currdebt_94A_sum\"),\n    pl.col(\"downpmt_134A\").sum().alias(\"downpmt_134A_sum\"),\n    custom_range_agg(pl.col(\"downpmt_134A\")).alias('downpmt_134A_range'),\n    pl.col(\"isbidproduct_390L\").max(),\n    pl.col(\"mainoccupationinc_437A\").sum().alias(\"mainoccupationinc_437A_sum\"),\n    custom_range_agg(pl.col(\"mainoccupationinc_437A\")).alias('mainoccupationinc_437A_range'),\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    custom_range_agg(pl.col(\"pmtnum_8L\")).alias('pmtnum_8L_range'),\n    pl.col(\"tenor_203L\").sum().alias(\"tenor_203L_sum\"),\n    custom_range_agg(pl.col(\"tenor_203L\")).alias('tenor_203L_range'))","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_applprev_1_feats_2 = train_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\ntrain_tax_registry_a_1_feats = train_tax_registry_a_1.group_by(\"case_id\").agg(\n    pl.col(\"amount_4527230A\").sum().alias(\"amount_4527230A_sum\"),\n    custom_range_agg(pl.col(\"amount_4527230A\")).alias('amount_4527230A_range'))\n\ntrain_tax_registry_b_1_feats = train_tax_registry_b_1.group_by(\"case_id\").agg(\n    pl.col(\"amount_4917619A\").sum().alias(\"amount_4917619A_sum\"))\n\ntrain_tax_registry_c_1_feats = train_tax_registry_c_1.group_by(\"case_id\").agg(\n    pl.col(\"pmtamount_36A\").sum().alias(\"pmtamount_36A_sum\"),\n    custom_range_agg(pl.col(\"pmtamount_36A\")).alias('pmtamount_36A_range'))","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Removed currdebt_94A_range, outstandingdebt_522A_range for > 90%\ntrain_applprev_1_feats_1 = compare_cols(train_applprev_1_feats_1)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_tax_registry_a_1_feats = compare_cols(train_tax_registry_a_1_feats)\n\ntrain_tax_registry_c_1_feats = compare_cols(train_tax_registry_c_1_feats)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_credit_bureau_a_1_feats = train_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    custom_range_agg(pl.col(\"monthlyinstlamount_332A\")).alias('monthlyinstlamount_332A_range'),\n    pl.col(\"monthlyinstlamount_674A\").sum().alias(\"monthlyinstlamount_674A_sum\"),\n    custom_range_agg(pl.col(\"monthlyinstlamount_674A\")).alias('monthlyinstlamount_674A_range'),\n    pl.col(\"nominalrate_498L\").max().alias(\"nominalrate_498L_max\"),\n    pl.col(\"numberofinstls_229L\").sum().alias(\"numberofinstls_229L_sum\"),\n    custom_range_agg(pl.col(\"numberofinstls_229L\")).alias('numberofinstls_229L_range'),\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    custom_range_agg(pl.col(\"numberofoverdueinstlmax_1151L\")).alias('numberofoverdueinstlmax_1151L_range'),\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    \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\"),\n    custom_range_agg(pl.col(\"totalamount_6A\")).alias('totalamount_6A_range'))","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Removed multiple cols for > 0.9\ntrain_credit_bureau_a_1_feats = compare_cols(train_credit_bureau_a_1_feats)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"join_data2 = train_basetable.join(train_applprev_1_feats_1, how=\"left\", on=\"case_id\"\n).join(train_applprev_1_feats_2, how=\"left\", on=\"case_id\"\n).join(train_tax_registry_a_1_feats, how=\"left\", on=\"case_id\"\n).join(train_tax_registry_b_1_feats, how=\"left\", on=\"case_id\"\n).join(train_tax_registry_c_1_feats, how=\"left\", on=\"case_id\"\n).join(train_credit_bureau_a_1_feats, how=\"left\", on=\"case_id\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"join_data2 = join_data2.drop(['date_decision','MONTH','WEEK_NUM','target'])\njoin_data2.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"list_missing(join_data2, 0.9)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#drop_cols = list_missing(join_data2)\ndrop_cols = ['amount_4917619A_sum']\n\njoin_data2 = join_data2.drop(drop_cols)\njoin_data2.shape # 55 cols","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"join_data2.write_csv(\"join_train_2_90new.csv\", separator=',')","metadata":{"trusted":true},"execution_count":null,"outputs":[]}]}