{"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":30664,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import pandas as pd","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:20:14.852593Z","iopub.execute_input":"2024-03-20T03:20:14.853009Z","iopub.status.idle":"2024-03-20T03:20:16.082105Z","shell.execute_reply.started":"2024-03-20T03:20:14.852979Z","shell.execute_reply":"2024-03-20T03:20:16.080827Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = pd.read_csv('/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_base.csv')\ndf_static_0_0 = pd.read_csv('/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_static_0_0.csv')\ndf_static_0_1 = pd.read_csv('/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_static_0_1.csv')","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:21:17.109088Z","iopub.execute_input":"2024-03-20T03:21:17.109824Z","iopub.status.idle":"2024-03-20T03:22:14.678325Z","shell.execute_reply.started":"2024-03-20T03:21:17.109789Z","shell.execute_reply":"2024-03-20T03:22:14.677205Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train_static_cb_0 = pd.read_csv('/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_static_cb_0.csv')","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:25:38.478143Z","iopub.execute_input":"2024-03-20T03:25:38.478576Z","iopub.status.idle":"2024-03-20T03:25:49.709844Z","shell.execute_reply.started":"2024-03-20T03:25:38.47854Z","shell.execute_reply":"2024-03-20T03:25:49.708144Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# concat train static tables\ndf_train_static = pd.concat([df_static_0_0,df_static_0_1])\n\n# merge basetable with static table\ndf = pd.merge(df_train,df_train_static,how='left',on='case_id')","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:24:29.768164Z","iopub.execute_input":"2024-03-20T03:24:29.768588Z","iopub.status.idle":"2024-03-20T03:24:40.193967Z","shell.execute_reply.started":"2024-03-20T03:24:29.768556Z","shell.execute_reply":"2024-03-20T03:24:40.192573Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.shape","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:24:46.854981Z","iopub.execute_input":"2024-03-20T03:24:46.855438Z","iopub.status.idle":"2024-03-20T03:24:46.865006Z","shell.execute_reply.started":"2024-03-20T03:24:46.855397Z","shell.execute_reply":"2024-03-20T03:24:46.863813Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = pd.merge(df,df_train_static_cb_0,how='left',on='case_id')","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:25:52.544991Z","iopub.execute_input":"2024-03-20T03:25:52.5456Z","iopub.status.idle":"2024-03-20T03:25:57.41754Z","shell.execute_reply.started":"2024-03-20T03:25:52.545564Z","shell.execute_reply":"2024-03-20T03:25:57.416241Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.shape","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:25:57.617099Z","iopub.execute_input":"2024-03-20T03:25:57.617461Z","iopub.status.idle":"2024-03-20T03:25:57.625391Z","shell.execute_reply.started":"2024-03-20T03:25:57.617429Z","shell.execute_reply":"2024-03-20T03:25:57.623884Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"base_cols = [ 'case_id','date_decision','MONTH','WEEK_NUM','target']\nsel_non_imputated_cols = ['actualdpdtolerance_344P','amtinstpaidbefduel24m_4187115A','annuity_780A','annuitynextmonth_57A','applicationcnt_361L','applications30d_658L','applicationscnt_1086L','applicationscnt_464L','applicationscnt_629L','applicationscnt_867L','avgdbddpdlast24m_3658932P','avgdbddpdlast3m_4187120P','avgdbdtollast24m_4525197P','avgdpdtolclosure24_3658938P','avginstallast24m_3658937A','avgmaxdpdlast9m_3716943P','avgoutstandbalancel6m_4187114A','avgpmtlast12m_4525200A','clientscnt_100L','clientscnt_1022L','clientscnt_1071L','clientscnt_1130L','clientscnt_157L','clientscnt_257L','clientscnt_304L','clientscnt_360L','clientscnt_493L','clientscnt_533L','clientscnt_887L','clientscnt_946L','clientscnt12m_3712952L','clientscnt3m_3712950L','clientscnt6m_3712949L','credamount_770A','credtype_322L','currdebt_22A','currdebtcredtyperange_828A','daysoverduetolerancedd_3976961L','deferredmnthsnum_166L','disbursedcredamount_1113A','disbursementtype_67L','downpmt_116A','isbidproduct_1095L','lastapprcommoditycat_1041M','lastapprcommoditytypec_5251766M','lastcancelreason_561M','lastrejectcommoditycat_161M','lastrejectcommodtypec_5251769M','lastrejectreason_759M','lastrejectreasonclient_4145040M','mobilephncnt_593L','numactivecreds_622L','numactivecredschannel_414L','numactiverelcontr_750L','numcontrs3months_479L','numnotactivated_1143L','numpmtchanneldd_318L','numrejects9m_859L','previouscontdistrict_112M','sellerplacecnt_915L','sellerplacescnt_216L']\nsel_imputated_cols = ['bankacctype_710L','cardtype_51L','cntincpaycont9m_3716944L','cntpmts24_3658933L','commnoinclast6m_3546845L','eir_270L','inittransactioncode_186L','interestrate_311L','lastapprcredamount_781A','lastrejectcredamount_222A','lastst_736L','maininc_215A','maxannuity_159A','maxdbddpdtollast6m_4187119P','numinstls_657L','numinstlsallpaid_934L','numinstpaidearly_338L','numinstregularpaid_973L','numinsttopaygr_769L','numinstunpaidmax_3546851L','opencred_647L','pmtnum_254L','posfpd30lastmonth_3976960P','price_1097A','sumoutstandtotal_3546847A','totaldebt_9A','totalsettled_863A']\nimputate_with_zero_cols = ['daysoverduetolerancedd_3976961L','avgpmtlast12m_4525200A','avgoutstandbalancel6m_4187114A','avgmaxdpdlast9m_3716943P','avginstallast24m_3658937A','avgdpdtolclosure24_3658938P','avgdbdtollast24m_4525197P','avgdbddpdlast3m_4187120P','avgdbddpdlast24m_3658932P','amtinstpaidbefduel24m_4187115A','actualdpdtolerance_344P','cntincpaycont9m_3716944L','cntpmts24_3658933L','commnoinclast6m_3546845L','lastapprcredamount_781A','lastrejectcredamount_222A','maininc_215A','maxannuity_159A','numinstls_657L','numinstlsallpaid_934L','numinstpaidearly_338L','numinstregularpaid_973L','numinsttopaygr_769L','numinstunpaidmax_3546851L','pmtnum_254L','posfpd30lastmonth_3976960P','price_1097A','sumoutstandtotal_3546847A','totaldebt_9A','totalsettled_863A']","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:26:15.471789Z","iopub.execute_input":"2024-03-20T03:26:15.472199Z","iopub.status.idle":"2024-03-20T03:26:15.487892Z","shell.execute_reply.started":"2024-03-20T03:26:15.472165Z","shell.execute_reply":"2024-03-20T03:26:15.486406Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"len(base_cols) + len(sel_non_imputated_cols) + len(sel_imputated_cols) + len(imputate_with_zero_cols)","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:26:31.12354Z","iopub.execute_input":"2024-03-20T03:26:31.123956Z","iopub.status.idle":"2024-03-20T03:26:31.132269Z","shell.execute_reply.started":"2024-03-20T03:26:31.123926Z","shell.execute_reply":"2024-03-20T03:26:31.130926Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_raw = df.copy()","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:27:38.027835Z","iopub.execute_input":"2024-03-20T03:27:38.028264Z","iopub.status.idle":"2024-03-20T03:27:44.74462Z","shell.execute_reply.started":"2024-03-20T03:27:38.028223Z","shell.execute_reply":"2024-03-20T03:27:44.743264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def impute_with_zero(column):\n    df_raw[column] = df_raw[column].fillna(0.0)\n    \n# imputation with zero\nimpute_with_zero(imputate_with_zero_cols)\n\n# manual imputation\n\n# bankacctype_710L\ndf_raw['bankacctype_710L'] = df_raw['bankacctype_710L'].fillna('NA')\n\n# cardtype_51L\ndf_raw['cardtype_51L'] = df_raw['cardtype_51L'].fillna('NOCARD')\n\n# eir_270L\ndf_raw['eir_270L'] = df_raw['eir_270L'].fillna(0.2)\n\n# inittransactioncode_186L\ndf_raw['inittransactioncode_186L'] = df_raw['inittransactioncode_186L'].fillna(df_raw['inittransactioncode_186L'].mode()[0])\n\n# interestrate_311L\ndf_raw['interestrate_311L'] = df_raw['interestrate_311L'].fillna(df_raw['interestrate_311L'].mean())\n\n# lastst_736L\ndf_raw['lastst_736L'] = df_raw['lastst_736L'].fillna(df_raw['lastst_736L'].mode()[0])\n\n# opencred_647L\ndf_raw['opencred_647L'] = df_raw['opencred_647L'].fillna(df_raw['opencred_647L'].mode()[0])","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:27:50.013026Z","iopub.execute_input":"2024-03-20T03:27:50.01344Z","iopub.status.idle":"2024-03-20T03:27:52.03785Z","shell.execute_reply.started":"2024-03-20T03:27:50.013406Z","shell.execute_reply":"2024-03-20T03:27:52.036477Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# selected columns for the final dataframe\nfinal_cols = base_cols + sel_imputated_cols + sel_non_imputated_cols + imputate_with_zero_cols\nprint('Column count', len(final_cols))\ncleaned_frame = df_raw[final_cols]","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:28:00.343302Z","iopub.execute_input":"2024-03-20T03:28:00.343764Z","iopub.status.idle":"2024-03-20T03:28:01.278681Z","shell.execute_reply.started":"2024-03-20T03:28:00.343706Z","shell.execute_reply":"2024-03-20T03:28:01.277495Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# cleaned_frame.to_csv('/kaggle/working/preprocessed.csv.gz', compression='gzip', index=False)","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:31:28.823258Z","iopub.execute_input":"2024-03-20T03:31:28.823778Z","iopub.status.idle":"2024-03-20T03:40:47.377428Z","shell.execute_reply.started":"2024-03-20T03:31:28.823712Z","shell.execute_reply":"2024-03-20T03:40:47.375874Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cleaned_frame.shape","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:41:26.762985Z","iopub.execute_input":"2024-03-20T03:41:26.763362Z","iopub.status.idle":"2024-03-20T03:41:26.770716Z","shell.execute_reply.started":"2024-03-20T03:41:26.763333Z","shell.execute_reply":"2024-03-20T03:41:26.769498Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Postprocess","metadata":{}},{"cell_type":"code","source":"# separate categorical and numerical cols\nnumerical_cols = cleaned_frame.select_dtypes(include='number').columns.tolist()\ncategorical_cols = cleaned_frame.select_dtypes(include='object').columns.tolist()","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:41:41.233609Z","iopub.execute_input":"2024-03-20T03:41:41.234063Z","iopub.status.idle":"2024-03-20T03:41:44.145061Z","shell.execute_reply.started":"2024-03-20T03:41:41.234027Z","shell.execute_reply":"2024-03-20T03:41:44.143877Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"len(numerical_cols)","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:42:38.988035Z","iopub.execute_input":"2024-03-20T03:42:38.988441Z","iopub.status.idle":"2024-03-20T03:42:38.99598Z","shell.execute_reply.started":"2024-03-20T03:42:38.988412Z","shell.execute_reply":"2024-03-20T03:42:38.994828Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cleaned_frame[numerical_cols]","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:43:48.307679Z","iopub.execute_input":"2024-03-20T03:43:48.308913Z","iopub.status.idle":"2024-03-20T03:43:49.791768Z","shell.execute_reply.started":"2024-03-20T03:43:48.308873Z","shell.execute_reply":"2024-03-20T03:43:49.790441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"numerical_data = cleaned_frame[numerical_cols]\ncategorical_data = cleaned_frame[categorical_cols]","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:40:50.487696Z","iopub.execute_input":"2024-03-20T03:40:50.488191Z","iopub.status.idle":"2024-03-20T03:40:51.506204Z","shell.execute_reply.started":"2024-03-20T03:40:50.488149Z","shell.execute_reply":"2024-03-20T03:40:51.504844Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"numerical_data.info()","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:43:01.932954Z","iopub.execute_input":"2024-03-20T03:43:01.933375Z","iopub.status.idle":"2024-03-20T03:43:01.963026Z","shell.execute_reply.started":"2024-03-20T03:43:01.933345Z","shell.execute_reply":"2024-03-20T03:43:01.961892Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check for duplicated column names\nduplicated_columns = numerical_data.columns.duplicated()","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:47:00.518132Z","iopub.execute_input":"2024-03-20T03:47:00.518533Z","iopub.status.idle":"2024-03-20T03:47:00.524215Z","shell.execute_reply.started":"2024-03-20T03:47:00.518501Z","shell.execute_reply":"2024-03-20T03:47:00.523167Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Select only columns that are not duplicated\ndf.loc[:, ~duplicated_columns]","metadata":{"execution":{"iopub.status.busy":"2024-03-20T03:47:31.748631Z","iopub.execute_input":"2024-03-20T03:47:31.74905Z","iopub.status.idle":"2024-03-20T03:47:32.262658Z","shell.execute_reply.started":"2024-03-20T03:47:31.749019Z","shell.execute_reply":"2024-03-20T03:47:32.260854Z"},"trusted":true},"execution_count":null,"outputs":[]}]}