{"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 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":{"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":"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    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])):.2f}\")\n\n# Returns a list of columns which have missing values > threshold\ndef list_missing(df, threshold=0.90):\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    smax = series.max()\n    smin = series.min()\n    return smax - smin","metadata":{"execution":{"iopub.status.busy":"2024-04-24T15:12:41.473988Z","iopub.execute_input":"2024-04-24T15:12:41.474399Z","iopub.status.idle":"2024-04-24T15:12:41.484536Z","shell.execute_reply.started":"2024-04-24T15:12:41.474368Z","shell.execute_reply":"2024-04-24T15:12:41.483204Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Load data","metadata":{}},{"cell_type":"code","source":"train_basetable = pl.read_csv(pathway + \"csv_files/train/train_base.csv\")\ntrain_static = pl.concat(\n    [pl.read_csv(pathway + \"csv_files/train/train_static_0_0.csv\").pipe(set_table_dtypes),\n    pl.read_csv(pathway + \"csv_files/train/train_static_0_1.csv\").pipe(set_table_dtypes)\n    ], how=\"vertical_relaxed\")\ntrain_static_cb=pl.read_csv(pathway + \"csv_files/train/train_static_cb_0.csv\").pipe(set_table_dtypes)\ntrain_person_1=pl.read_csv(pathway +  \"csv_files/train/train_person_1.csv\").pipe(set_table_dtypes)\ntrain_credit_bureau_b_2=pl.read_csv(pathway + \"csv_files/train/train_credit_bureau_b_2.csv\").pipe(set_table_dtypes)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Additional depth=1 files\ntrain_other_1 = pl.read_csv(pathway + \"csv_files/train/train_other_1.csv\").pipe(set_table_dtypes)\n\ntrain_credit_bureau_b_1 = pl.read_csv(pathway + \"csv_files/train/train_credit_bureau_b_1.csv\").pipe(set_table_dtypes)\n\ntrain_deposit_1 = pl.read_csv(pathway + \"csv_files/train/train_deposit_1.csv\").pipe(set_table_dtypes)\n\n# train_debitcard_1 = pl.read_csv(pathway + \"csv_files/train/train_debitcard_1.csv\").pipe(set_table_dtypes)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Look at all the tables","metadata":{}},{"cell_type":"code","source":"# Code to test converting dtype from str to date in train_basetable\ntrain_basetable = train_basetable.with_columns(pl.col('date_decision').cast(pl.Date))\ntrain_basetable.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Visual inspection shows most num_group1 != 0 columns are null as they represent people other than the original loan applicant\n# For now we take the object/strings from num_group == 0 and take the sum for the one numerical column\n# missing_values(train_person_1)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# So many missing values, not using this csv at all\n# train_debitcard_1.head()\n# missing_values(train_debitcard_1)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Feature Engineering\nHere we will join tables using Polars library.  We will join tables via case_id.","metadata":{}},{"cell_type":"code","source":"#use aggregation functions in tables with depth >=1\n\n# Ranges were all 0 for this\ntrain_person_1_feats_1 = train_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\ntrain_person_1_feats_2 = train_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\ntrain_credit_bureau_b_2_feats = train_credit_bureau_b_2.group_by(\"case_id\").agg(\n    pl.col(\"pmts_pmtsoverdue_635A\").sum().alias(\"pmts_pmtsoverdue_635A_sum\"),\n    custom_range_agg(pl.col(\"pmts_pmtsoverdue_635A\")).alias('pmts_pmtsoverdue_635A_range'),\n    pl.col(\"pmts_dpdvalue_108P\").sum().alias(\"pmts_dpdvalue_108P_sum\"),\n    custom_range_agg(pl.col(\"pmts_dpdvalue_108P\")).alias('pmts_dpdvalue_108P_range')\n)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_credit_bureau_b_2_feats.head(10)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Additional aggregation for depth=1 files\n# custom_range_agg for train_other_1_feats and train_deposit_1_feats was checked and not used bc values are same as sum or 0\ntrain_other_1_feats = train_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\ntrain_credit_bureau_b_1_feats = train_credit_bureau_b_1.group_by(\"case_id\").agg(\n    pl.col(\"amount_1115A\").sum().alias(\"amount_1115A_sum\"),\n    custom_range_agg(pl.col(\"amount_1115A\")).alias('amount_1115A_range'),\n    pl.col(\"credquantity_1099L\").sum().alias(\"credquantity_1099L_sum\"),\n    custom_range_agg(pl.col(\"credquantity_1099L\")).alias('credquantity_1099L_range'),\n    pl.col(\"credquantity_984L\").sum().alias(\"credquantity_984L_sum\"),\n    custom_range_agg(pl.col(\"credquantity_984L\")).alias('credquantity_984L_range'),\n    pl.col(\"debtpastduevalue_732A\").sum().alias(\"debtpastduevalue_732A_sum\"),\n    pl.col(\"debtvalue_227A\").sum().alias(\"debtvalue_227A_sum\"),\n    custom_range_agg(pl.col(\"debtvalue_227A\")).alias('debtvalue_227A_range'),\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    custom_range_agg(pl.col(\"installmentamount_833A\")).alias('installmentamount_833A_range'),\n    pl.col(\"instlamount_892A\").sum().alias(\"instlamount_892A_sum\"),\n    custom_range_agg(pl.col(\"instlamount_892A\")).alias('instlamount_892A_range'),   \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    custom_range_agg(pl.col(\"numberofinstls_810L\")).alias('numberofinstls_810L_range'),\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    custom_range_agg(pl.col(\"pmtnumpending_403L\")).alias('pmtnumpending_403L_range'),\n    pl.col(\"residualamount_3940956A\").sum().alias(\"residualamount_3940956A_sum\"),\n    custom_range_agg(pl.col(\"residualamount_3940956A\")).alias('residualamount_3940956A_range'),\n    pl.col(\"totalamount_503A\").sum().alias(\"totalamount_503A_sum\"),\n    custom_range_agg(pl.col(\"totalamount_503A\")).alias('totalamount_503A_range'),\n    pl.col(\"totalamount_881A\").sum().alias(\"totalamount_881A_sum\"),\n    custom_range_agg(pl.col(\"totalamount_881A\")).alias('totalamount_881A_range'))\n\ntrain_deposit_1_feats = train_deposit_1.group_by(\"case_id\").agg(\n    pl.col(\"amount_416A\").sum().alias(\"amount_416A_sum\"))","metadata":{"execution":{"iopub.status.busy":"2024-04-24T15:05:23.118653Z","iopub.execute_input":"2024-04-24T15:05:23.119989Z","iopub.status.idle":"2024-04-24T15:05:23.210706Z","shell.execute_reply.started":"2024-04-24T15:05:23.119937Z","shell.execute_reply":"2024-04-24T15:05:23.209398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_credit_bureau_b_1_feats.tail(15)","metadata":{"execution":{"iopub.status.busy":"2024-04-24T15:08:12.625998Z","iopub.execute_input":"2024-04-24T15:08:12.626394Z","iopub.status.idle":"2024-04-24T15:08:12.643800Z","shell.execute_reply.started":"2024-04-24T15:08:12.626363Z","shell.execute_reply":"2024-04-24T15:08:12.642551Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Join dfs","metadata":{}},{"cell_type":"code","source":"# join all tables/columns together\n\njoin_data1 = train_basetable.join(train_static, how=\"left\", on=\"case_id\"\n).join(train_static_cb, how=\"left\", on=\"case_id\"\n).join(train_person_1_feats_1, how=\"left\", on=\"case_id\"\n).join(train_person_1_feats_2, how=\"left\", on=\"case_id\"\n).join(train_credit_bureau_b_2_feats, how=\"left\", on=\"case_id\"\n).join(train_other_1_feats, how=\"left\", on=\"case_id\"\n).join(train_credit_bureau_b_1_feats, how=\"left\", on=\"case_id\"\n).join(train_deposit_1_feats, how=\"left\", on=\"case_id\"\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-24T15:08:58.217986Z","iopub.execute_input":"2024-04-24T15:08:58.218397Z","iopub.status.idle":"2024-04-24T15:09:01.651605Z","shell.execute_reply.started":"2024-04-24T15:08:58.218366Z","shell.execute_reply":"2024-04-24T15:09:01.650583Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 277 columns\njoin_data1.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-24T15:11:56.488520Z","iopub.execute_input":"2024-04-24T15:11:56.489037Z","iopub.status.idle":"2024-04-24T15:11:56.496525Z","shell.execute_reply.started":"2024-04-24T15:11:56.488998Z","shell.execute_reply":"2024-04-24T15:11:56.495375Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"list_missing(join_data1, 0.9)","metadata":{"execution":{"iopub.status.busy":"2024-04-24T15:12:54.908346Z","iopub.execute_input":"2024-04-24T15:12:54.908722Z","iopub.status.idle":"2024-04-24T15:12:54.948329Z","shell.execute_reply.started":"2024-04-24T15:12:54.908696Z","shell.execute_reply":"2024-04-24T15:12:54.947175Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"drop_cols = ['clientscnt_136L',\n 'datelastinstal40dpd_247D',\n 'equalitydataagreement_891L',\n 'equalityempfrom_62L',\n 'interestrategrace_34L',\n 'isbidproductrequest_292L',\n 'lastdependentsnum_448L',\n 'lastotherinc_902A',\n 'lastotherlnsexpense_631A',\n 'lastrepayingdate_696D',\n 'maxannuity_4075009A',\n 'payvacationpostpone_4187118D',\n 'validfrom_1069D',\n 'assignmentdate_238D',\n 'assignmentdate_4527235D',\n 'assignmentdate_4955616D',\n 'dateofbirth_342D',\n 'for3years_128L',\n 'for3years_504L',\n 'for3years_584L',\n 'formonth_118L',\n 'formonth_206L',\n 'formonth_535L',\n 'forquarter_1017L',\n 'forquarter_462L',\n 'forquarter_634L',\n 'fortoday_1092L',\n 'forweek_1077L',\n 'forweek_528L',\n 'forweek_601L',\n 'foryear_618L',\n 'foryear_818L',\n 'foryear_850L',\n 'pmtaverage_3A',\n 'pmtaverage_4527227A',\n 'pmtaverage_4955615A',\n 'pmtcount_4527229L',\n 'pmtcount_4955617L',\n 'pmtcount_693L',\n 'riskassesment_302T',\n 'riskassesment_940T',\n 'pmts_pmtsoverdue_635A_sum',\n 'pmts_pmtsoverdue_635A_range',\n 'pmts_dpdvalue_108P_sum',\n 'pmts_dpdvalue_108P_range',\n 'amtdebitincoming_4809443A_sum',\n 'amtdebitoutgoing_4809440A_sum',\n 'amtdepositbalance_4809441A_sum',\n 'amtdepositincoming_4809444A_sum',\n 'amtdepositoutgoing_4809442A_sum',\n 'amount_1115A_sum',\n 'amount_1115A_range',\n 'credquantity_1099L_sum',\n 'credquantity_1099L_range',\n 'credquantity_984L_sum',\n 'credquantity_984L_range',\n 'debtpastduevalue_732A_sum',\n 'debtvalue_227A_sum',\n 'debtvalue_227A_range',\n 'dpd_550P_sum',\n 'dpd_733P_sum',\n 'dpdmax_851P_max',\n 'installmentamount_644A_sum',\n 'installmentamount_833A_sum',\n 'installmentamount_833A_range',\n 'instlamount_892A_sum',\n 'instlamount_892A_range',\n 'interestrateyearly_538L_max',\n 'maxdebtpduevalodued_3940955A_max',\n 'numberofinstls_810L_sum',\n 'numberofinstls_810L_range',\n 'overdueamountmax_950A_max',\n 'pmtdaysoverdue_1135P_sum',\n 'pmtnumpending_403L_sum',\n 'pmtnumpending_403L_range',\n 'residualamount_3940956A_sum',\n 'residualamount_3940956A_range',\n 'totalamount_503A_sum',\n 'totalamount_503A_range',\n 'totalamount_881A_sum',\n 'totalamount_881A_range',\n 'amount_416A_sum',\n 'amount_416A_range']","metadata":{"execution":{"iopub.status.busy":"2024-04-24T15:13:46.394953Z","iopub.execute_input":"2024-04-24T15:13:46.395339Z","iopub.status.idle":"2024-04-24T15:13:46.404058Z","shell.execute_reply.started":"2024-04-24T15:13:46.395311Z","shell.execute_reply":"2024-04-24T15:13:46.402890Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 194 cols after dropping > 90% missing\njoin_data1 = join_data1.drop(drop_cols)\njoin_data1.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-24T15:13:49.573946Z","iopub.execute_input":"2024-04-24T15:13:49.574347Z","iopub.status.idle":"2024-04-24T15:13:49.642002Z","shell.execute_reply.started":"2024-04-24T15:13:49.574319Z","shell.execute_reply":"2024-04-24T15:13:49.640731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"join_data1.write_csv(\"join_train_1.csv\", separator=',')","metadata":{"trusted":true},"execution_count":null,"outputs":[]}]}