{"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":7602123,"sourceType":"competition"}],"dockerImageVersionId":30646,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"### Home Credit Beginning Analysis-Andrea Adams","metadata":{}},{"cell_type":"code","source":"#load the libraries\nimport polars as pl\nimport numpy as np\nimport pandas as pd\nimport lightgbm as lgb\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import roc_auc_score\n\n\n\n","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:35:34.760652Z","iopub.execute_input":"2024-02-27T10:35:34.761677Z","iopub.status.idle":"2024-02-27T10:35:39.859427Z","shell.execute_reply.started":"2024-02-27T10:35:34.761639Z","shell.execute_reply":"2024-02-27T10:35:39.858106Z"},"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-02-27T10:36:12.681909Z","iopub.execute_input":"2024-02-27T10:36:12.682339Z","iopub.status.idle":"2024-02-27T10:36:12.687324Z","shell.execute_reply.started":"2024-02-27T10:36:12.682306Z","shell.execute_reply":"2024-02-27T10:36:12.686085Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#this function takes a data frame as input, and iterates over columns.  \n#each column ending with P or A, it casts column to Float64 type using with_columns.\n#uses polars.\ndef set_table_dtypes(df: pl.DataFrame)-> pl.DataFrame:\n    \n    for col in df.columns:\n        if col[-1] in (\"P\", \"A\"):\n            df = df.with_columns(pl.col(col).cast(pl.Float64).alias(col))\n            \n    return df","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:35:52.301964Z","iopub.execute_input":"2024-02-27T10:35:52.302390Z","iopub.status.idle":"2024-02-27T10:35:52.310022Z","shell.execute_reply.started":"2024-02-27T10:35:52.302358Z","shell.execute_reply":"2024-02-27T10:35:52.308470Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#this function takes a data frame as input, and iterates over columns\n#each column that is a datatype: object or string, it converts col to string type, then to cat type\n#ensures any unseen values are categorized as unknown\n#summary, it converts string columns to categorical types, and adds Unknown col\ndef convert_strings(df: pd.DataFrame) -> pd.DataFrame:\n    for col in df.columns:\n        if df[col].dtype.name in ['object', 'string']:\n            df[col] = df[col].astype(\"string\").astype('category')\n            current_categories = df[col].cat.categories\n            new_categories = current_categories.to_list() + [\"Unknown\"]\n            new_dtype = pd.CategoricalDtype(categories=new_categories, ordered=True)\n            df[col] = df[col].astype(new_dtype)\n    return df","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:35:55.497348Z","iopub.execute_input":"2024-02-27T10:35:55.497838Z","iopub.status.idle":"2024-02-27T10:35:55.510476Z","shell.execute_reply.started":"2024-02-27T10:35:55.497799Z","shell.execute_reply":"2024-02-27T10:35:55.504969Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#now I will write a function to return missing values\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\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","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:49:32.318130Z","iopub.execute_input":"2024-02-27T10:49:32.318571Z","iopub.status.idle":"2024-02-27T10:49:32.326595Z","shell.execute_reply.started":"2024-02-27T10:49:32.318541Z","shell.execute_reply":"2024-02-27T10:49:32.325149Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now I will load the data using polars.","metadata":{}},{"cell_type":"code","source":"#reading in multiple CSV files, and concatenating them into dataframes using polars\n#using set_table_dtypes to ensure consistent datatypes across col ending with P or A (setting to Float64)\n\n\n#read and concatenate CSV files into DataFrames\ntrain_basetable = pl.read_csv(pathway + \"csv_files/train/train_base.csv\")\ntrain_static = pl.concat(\n    [\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        \n    ],\n    how=\"vertical_relaxed\",\n)\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":{"execution":{"iopub.status.busy":"2024-02-27T10:36:18.027095Z","iopub.execute_input":"2024-02-27T10:36:18.027511Z","iopub.status.idle":"2024-02-27T10:36:34.288344Z","shell.execute_reply.started":"2024-02-27T10:36:18.027481Z","shell.execute_reply":"2024-02-27T10:36:34.285761Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#load in the other depth=1 files\n\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\ntrain_debitcard_1 = pl.read_csv(pathway + \"csv_files/train/train_debitcard_1.csv\").pipe(set_table_dtypes)","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:36:38.895573Z","iopub.execute_input":"2024-02-27T10:36:38.896386Z","iopub.status.idle":"2024-02-27T10:36:39.198285Z","shell.execute_reply.started":"2024-02-27T10:36:38.896343Z","shell.execute_reply":"2024-02-27T10:36:39.197005Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#reading in multiple CSV files, and then concatenating them into dataframes with polars for TEST DATA\n\ntest_basetable = pl.read_csv(pathway + \"csv_files/test/test_base.csv\")\ntest_static = pl.concat(\n    [\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    ],\n    how=\"vertical_relaxed\",\n)\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)\n","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:36:42.640678Z","iopub.execute_input":"2024-02-27T10:36:42.641137Z","iopub.status.idle":"2024-02-27T10:36:42.730368Z","shell.execute_reply.started":"2024-02-27T10:36:42.641096Z","shell.execute_reply":"2024-02-27T10:36:42.729176Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Look at all the tables","metadata":{}},{"cell_type":"code","source":"train_basetable.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:38:01.324014Z","iopub.execute_input":"2024-02-27T10:38:01.324472Z","iopub.status.idle":"2024-02-27T10:38:01.341795Z","shell.execute_reply.started":"2024-02-27T10:38:01.324442Z","shell.execute_reply":"2024-02-27T10:38:01.340464Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#convert 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":{"execution":{"iopub.status.busy":"2024-02-27T10:38:03.917880Z","iopub.execute_input":"2024-02-27T10:38:03.919269Z","iopub.status.idle":"2024-02-27T10:38:04.370008Z","shell.execute_reply.started":"2024-02-27T10:38:03.919223Z","shell.execute_reply":"2024-02-27T10:38:04.368931Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_static.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_static_cb.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_person_1.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### A inspection of the dataset shows that most num_group1 != 0 columns are null because they represent people other than the primary applicant.  We will take the object/strings from num_group==0 and take the sum for the numerical columns.","metadata":{}},{"cell_type":"code","source":"missing_values(train_person_1)","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:38:29.292827Z","iopub.execute_input":"2024-02-27T10:38:29.294187Z","iopub.status.idle":"2024-02-27T10:38:29.316598Z","shell.execute_reply.started":"2024-02-27T10:38:29.294141Z","shell.execute_reply":"2024-02-27T10:38:29.315150Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### train_person_1.csv Notes\n\nNow it is our job to decide which columns we will aggregate.  I will drop any columns with greater than 50% missing values.  I expect a lot of missing values for the depth=1,2 files because many columns only have values for the primary applicant (num_group1==0).  We need to do this for all the depth 1,2 files.\n\nFor now, we will take the categories/strings from num_group1==0, and use the sum for amounts if it makes sense.\n\nThere are many columns related to zip code/addresses but the formatting is unclear.  For now I am just dropping these columns.","metadata":{}},{"cell_type":"code","source":"train_credit_bureau_b_2.tail(10)","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:38:40.465886Z","iopub.execute_input":"2024-02-27T10:38:40.466369Z","iopub.status.idle":"2024-02-27T10:38:40.477200Z","shell.execute_reply.started":"2024-02-27T10:38:40.466330Z","shell.execute_reply":"2024-02-27T10:38:40.476060Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### train_credit_bureau_b_2 notes:\n\nThis is a depth=2 file with only a few columns.  I am dropping the date column.  The other 2 numeric columns, I will sum for now.  pmts_dpdvalue_108P=value of past due payment for active contract.  pmts-pmtsoverdue_635A=active contract that has overdue payments.  (this is a dollar value as well and not just a number of contracts)","metadata":{}},{"cell_type":"code","source":"#look at all the unique values of pmts_pmtsoverdue_635A\ntrain_credit_bureau_b_2['pmts_pmtsoverdue_635A'].unique()","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:39:04.138352Z","iopub.execute_input":"2024-02-27T10:39:04.138821Z","iopub.status.idle":"2024-02-27T10:39:04.191404Z","shell.execute_reply.started":"2024-02-27T10:39:04.138784Z","shell.execute_reply":"2024-02-27T10:39:04.189962Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Additional Depth=1 tables, looking at the features","metadata":{}},{"cell_type":"code","source":"train_other_1.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:39:12.773558Z","iopub.execute_input":"2024-02-27T10:39:12.774065Z","iopub.status.idle":"2024-02-27T10:39:12.783921Z","shell.execute_reply.started":"2024-02-27T10:39:12.774028Z","shell.execute_reply":"2024-02-27T10:39:12.782588Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"train_credit_bureau_b_1.head(10)\n#will be ignoring all categorical/string columns for now","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:39:29.611342Z","iopub.execute_input":"2024-02-27T10:39:29.611875Z","iopub.status.idle":"2024-02-27T10:39:29.633650Z","shell.execute_reply.started":"2024-02-27T10:39:29.611819Z","shell.execute_reply":"2024-02-27T10:39:29.632268Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_deposit_1.head()\n#ignoring date columns","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:39:33.598021Z","iopub.execute_input":"2024-02-27T10:39:33.598490Z","iopub.status.idle":"2024-02-27T10:39:33.606633Z","shell.execute_reply.started":"2024-02-27T10:39:33.598456Z","shell.execute_reply":"2024-02-27T10:39:33.605310Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#too many missing values, dropping this csv\n#train_debitcard_1.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"missing_values(train_debitcard_1)\n#too many missing values, lets ignore this csv file for now","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:40:17.869115Z","iopub.execute_input":"2024-02-27T10:40:17.869566Z","iopub.status.idle":"2024-02-27T10:40:17.877508Z","shell.execute_reply.started":"2024-02-27T10:40:17.869535Z","shell.execute_reply":"2024-02-27T10:40:17.876205Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"train_credit_bureau_b_2.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"missing_values(train_person_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#create a new table that groups the train_person_1 table by the case_id column.\n#aggregates the values in \"mainoccupationinc_384A\" using the sum function, and assigns it an alias with the same name\n#mainoccupationinc_384A = the amount of the main income of the client\ntrain_person_1_feats_1 = train_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").sum().alias(\"mainoccupationinc_384_sum\"))\n\n#create a new table that selects specific columns from the train_person_1 table\n#filters rows where the value in num_group1==0 b/c that represents the person who applied for loan\n#then drops the num_group_1 column from the resulting dataframe\ntrain_person_1_feats_2 = train_person_1.select([\"case_id\", \"num_group1\", \"incometype_1044T\", \"birth_259D\", \"empl_employedfrom_271D\", \n                                               \"empl_industry_691L\", \"familystate_447L\", \"sex_738L\", \"type_25L\", \"safeguarantyflag_411L\",\n                                               \"empl_employedtotal_800L\", \"role_1084L\"]).filter(pl.col(\"num_group1\")==0).drop(\"num_group1\")\n\n#create a new table that groups the train_credit_bureau_b_2 table by the case_id column\n#aggregates the two columns: \"pmts_pmtsoverdue_635A\" and \"pmts_dpdvalue_108P\" using the sum() function, and assigns aliases of the same names\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    pl.col(\"pmts_dpdvalue_108P\").sum().alias(\"pmts_dpdvalue_108P_sum\"))","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:40:30.953512Z","iopub.execute_input":"2024-02-27T10:40:30.953981Z","iopub.status.idle":"2024-02-27T10:40:32.717299Z","shell.execute_reply.started":"2024-02-27T10:40:30.953947Z","shell.execute_reply":"2024-02-27T10:40:32.716140Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#additional aggregation for other depth=1 tables\n\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(\"amtdepositoutoging_4809442A_sum\"))\n\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    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_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\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-02-27T10:40:45.234165Z","iopub.execute_input":"2024-02-27T10:40:45.234626Z","iopub.status.idle":"2024-02-27T10:40:45.327175Z","shell.execute_reply.started":"2024-02-27T10:40:45.234591Z","shell.execute_reply":"2024-02-27T10:40:45.325987Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#check if features were correctly aggregated\ntrain_person_1_feats_1.sort('case_id').head()","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:40:57.617880Z","iopub.execute_input":"2024-02-27T10:40:57.618327Z","iopub.status.idle":"2024-02-27T10:40:57.742296Z","shell.execute_reply.started":"2024-02-27T10:40:57.618296Z","shell.execute_reply":"2024-02-27T10:40:57.741281Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_person_1_feats_2.sort('case_id').head()","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:41:03.056801Z","iopub.execute_input":"2024-02-27T10:41:03.057238Z","iopub.status.idle":"2024-02-27T10:41:03.438011Z","shell.execute_reply.started":"2024-02-27T10:41:03.057208Z","shell.execute_reply":"2024-02-27T10:41:03.436780Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_credit_bureau_b_2_feats.sort('case_id').head()","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:41:11.511292Z","iopub.execute_input":"2024-02-27T10:41:11.511722Z","iopub.status.idle":"2024-02-27T10:41:11.522692Z","shell.execute_reply.started":"2024-02-27T10:41:11.511683Z","shell.execute_reply":"2024-02-27T10:41:11.521123Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_other_1_feats.sort('case_id').head()","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:41:13.947628Z","iopub.execute_input":"2024-02-27T10:41:13.948086Z","iopub.status.idle":"2024-02-27T10:41:13.961537Z","shell.execute_reply.started":"2024-02-27T10:41:13.948051Z","shell.execute_reply":"2024-02-27T10:41:13.960542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_credit_bureau_b_1_feats.sort('case_id').head()","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:41:19.574175Z","iopub.execute_input":"2024-02-27T10:41:19.575236Z","iopub.status.idle":"2024-02-27T10:41:19.590972Z","shell.execute_reply.started":"2024-02-27T10:41:19.575192Z","shell.execute_reply":"2024-02-27T10:41:19.589759Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_deposit_1_feats.sort('case_id').head()","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:41:22.130747Z","iopub.execute_input":"2024-02-27T10:41:22.131203Z","iopub.status.idle":"2024-02-27T10:41:22.144983Z","shell.execute_reply.started":"2024-02-27T10:41:22.131163Z","shell.execute_reply":"2024-02-27T10:41:22.143849Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Join the Dataframes","metadata":{}},{"cell_type":"code","source":"join_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-02-27T10:41:34.327968Z","iopub.execute_input":"2024-02-27T10:41:34.328444Z","iopub.status.idle":"2024-02-27T10:41:39.112138Z","shell.execute_reply.started":"2024-02-27T10:41:34.328410Z","shell.execute_reply":"2024-02-27T10:41:39.110978Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"join_data1.head(10)","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:41:41.764952Z","iopub.execute_input":"2024-02-27T10:41:41.765396Z","iopub.status.idle":"2024-02-27T10:41:41.799400Z","shell.execute_reply.started":"2024-02-27T10:41:41.765362Z","shell.execute_reply":"2024-02-27T10:41:41.798231Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"join_data1.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:41:46.099643Z","iopub.execute_input":"2024-02-27T10:41:46.100400Z","iopub.status.idle":"2024-02-27T10:41:46.107283Z","shell.execute_reply.started":"2024-02-27T10:41:46.100366Z","shell.execute_reply":"2024-02-27T10:41:46.106085Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"missing_values(join_data1)","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:41:56.040696Z","iopub.execute_input":"2024-02-27T10:41:56.041198Z","iopub.status.idle":"2024-02-27T10:41:56.081848Z","shell.execute_reply.started":"2024-02-27T10:41:56.041161Z","shell.execute_reply":"2024-02-27T10:41:56.080595Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#use the function list_missing we defined above\ndrop_cols = list_missing(join_data1)\njoin_data1 = join_data1.drop(drop_cols)\njoin_data1.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:50:26.657078Z","iopub.execute_input":"2024-02-27T10:50:26.657780Z","iopub.status.idle":"2024-02-27T10:50:26.695519Z","shell.execute_reply.started":"2024-02-27T10:50:26.657744Z","shell.execute_reply":"2024-02-27T10:50:26.694230Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now we reduced this dataframe down to 160 columns after dropping the columns with more than 60% missing as defined above.","metadata":{}},{"cell_type":"code","source":"missing_values(join_data1)","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:51:16.691296Z","iopub.execute_input":"2024-02-27T10:51:16.691794Z","iopub.status.idle":"2024-02-27T10:51:16.721821Z","shell.execute_reply.started":"2024-02-27T10:51:16.691758Z","shell.execute_reply":"2024-02-27T10:51:16.720551Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"No missing values anymore.","metadata":{}},{"cell_type":"code","source":"join_data1.write_csv(\"join_train_1.csv\", separator=\",\")","metadata":{"execution":{"iopub.status.busy":"2024-02-27T10:52:01.849457Z","iopub.execute_input":"2024-02-27T10:52:01.849897Z","iopub.status.idle":"2024-02-27T10:52:18.409116Z","shell.execute_reply.started":"2024-02-27T10:52:01.849845Z","shell.execute_reply":"2024-02-27T10:52:18.408018Z"},"trusted":true},"execution_count":null,"outputs":[]}]}