{"metadata":{"kernelspec":{"name":"python3","display_name":"Python 3","language":"python"},"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"},{"sourceId":8458594,"sourceType":"datasetVersion","datasetId":5041777}],"dockerImageVersionId":30698,"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport seaborn as sns\nfrom collections import Counter\nimport collections\nfrom scipy import stats\nfrom sklearn.preprocessing import OneHotEncoder\nimport pandas as pd\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.ensemble import RandomForestClassifier\nfrom sklearn.metrics import accuracy_score, classification_report\nimport os","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:25.164666Z","iopub.execute_input":"2024-05-26T09:10:25.165047Z","iopub.status.idle":"2024-05-26T09:10:26.921134Z","shell.execute_reply.started":"2024-05-26T09:10:25.165018Z","shell.execute_reply":"2024-05-26T09:10:26.919932Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"path = r'/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/'","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:26.923463Z","iopub.execute_input":"2024-05-26T09:10:26.924318Z","iopub.status.idle":"2024-05-26T09:10:26.932216Z","shell.execute_reply.started":"2024-05-26T09:10:26.924275Z","shell.execute_reply":"2024-05-26T09:10:26.928945Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"base = pd.read_parquet(path+'/train_base.parquet', engine='pyarrow')\n\n# samle 10000 0 and 10000 1\n\ntarget_0 = base[base['target']==0].sample(n=50000, random_state=1)\ntarget_1 = base[base['target']==1]\n\ndf = pd.concat([target_0,target_1])\n\ndel base ","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:26.933965Z","iopub.execute_input":"2024-05-26T09:10:26.934440Z","iopub.status.idle":"2024-05-26T09:10:27.522569Z","shell.execute_reply.started":"2024-05-26T09:10:26.934398Z","shell.execute_reply":"2024-05-26T09:10:27.521496Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Train Base","metadata":{}},{"cell_type":"markdown","source":"# Static","metadata":{}},{"cell_type":"code","source":"res = []\nfor i in range(0,2):\n\n    static_0_0 = pd.read_parquet(path+f'/train_static_0_{str(i)}.parquet', engine='pyarrow')\n\n    static_0_0[['actualdpdtolerance_344P','amtinstpaidbefduel24m_4187115A',\n     'annuity_780A','annuitynextmonth_57A','applicationcnt_361L','applications30d_658L','applicationscnt_1086L','applicationscnt_464L',\n     'applicationscnt_629L','applicationscnt_867L','avgdbddpdlast24m_3658932P','avgdbddpdlast3m_4187120P',\n     'avgdbdtollast24m_4525197P','avgdpdtolclosure24_3658938P','avginstallast24m_3658937A','avglnamtstart24m_4525187A',\n     'avgmaxdpdlast9m_3716943P','avgoutstandbalancel6m_4187114A','avgpmtlast12m_4525200A',\n     'clientscnt_100L','clientscnt12m_3712952L','clientscnt3m_3712950L','clientscnt6m_3712949L','maininc_215A']] = static_0_0[['actualdpdtolerance_344P','amtinstpaidbefduel24m_4187115A',\n     'annuity_780A','annuitynextmonth_57A','applicationcnt_361L','applications30d_658L','applicationscnt_1086L','applicationscnt_464L',\n     'applicationscnt_629L','applicationscnt_867L','avgdbddpdlast24m_3658932P','avgdbddpdlast3m_4187120P',\n     'avgdbdtollast24m_4525197P','avgdpdtolclosure24_3658938P','avginstallast24m_3658937A','avglnamtstart24m_4525187A',\n     'avgmaxdpdlast9m_3716943P','avgoutstandbalancel6m_4187114A','avgpmtlast12m_4525200A',\n     'clientscnt_100L','clientscnt12m_3712952L','clientscnt3m_3712950L','clientscnt6m_3712949L','maininc_215A']].fillna(0)\n\n\n\n    dummy = pd.get_dummies(static_0_0[['bankacctype_710L',\n     'cardtype_51L',\n     'credtype_322L',\n     'disbursementtype_67L',\n     'equalitydataagreement_891L',\n     'equalityempfrom_62L',\n     'inittransactioncode_186L',\n     'isbidproductrequest_292L',\n     'isdebitcard_729L',\n     'lastst_736L',\n     'paytype_783L',\n     'paytype1st_925L',\n     'twobodfilling_608L',\n     'typesuite_864L']],dtype = float)\n\n\n\n    static_0_0.drop(columns = ['firstclxcampaign_1125D','firstdatedue_489D',\n     'lastactivateddate_801D','lastapplicationdate_877D','lastapprdate_640D',\n     'lastrepayingdate_696D','opencred_647L','payvacationpostpone_4187118D',\n     'previouscontdistrict_112M','validfrom_1069D','datelastinstal40dpd_247D','bankacctype_710L',\n     'cardtype_51L','credtype_322L','disbursementtype_67L','equalitydataagreement_891L',\n     'equalityempfrom_62L','inittransactioncode_186L','isbidproductrequest_292L',\n     'isdebitcard_729L','lastapprcommoditycat_1041M',\n     'lastapprcommoditytypec_5251766M','lastcancelreason_561M',\n     'lastrejectcommoditycat_161M','lastrejectcommodtypec_5251769M','lastrejectreason_759M',\n     'lastrejectreasonclient_4145040M','lastst_736L','paytype_783L','paytype1st_925L',\n     'twobodfilling_608L','typesuite_864L','maxdpdinstldate_3546855D','lastrejectdate_50D'], inplace = True)\n\n\n    condition = static_0_0['isbidproduct_1095L']\n\n    static_0_0.loc[condition,'isbidproduct_1095L'] = 0\n    static_0_0.loc[condition==False,'isbidproduct_1095L'] = 1\n\n\n    static_0_0 = pd.concat([static_0_0,dummy],axis = 1)\n    static_0_0 = static_0_0.fillna(0)\n    \n    res.append(static_0_0)\n    \nres = pd.concat(res)","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:27.525958Z","iopub.execute_input":"2024-05-26T09:10:27.526454Z","iopub.status.idle":"2024-05-26T09:10:44.628981Z","shell.execute_reply.started":"2024-05-26T09:10:27.526402Z","shell.execute_reply":"2024-05-26T09:10:44.627531Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(res, on =['case_id'],how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:44.630750Z","iopub.execute_input":"2024-05-26T09:10:44.631098Z","iopub.status.idle":"2024-05-26T09:10:46.615106Z","shell.execute_reply.started":"2024-05-26T09:10:44.631070Z","shell.execute_reply":"2024-05-26T09:10:46.613984Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# statistic CB","metadata":{}},{"cell_type":"code","source":"static_cb = pd.read_parquet(path+f'/train_static_cb_0.parquet', engine='pyarrow')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:46.616818Z","iopub.execute_input":"2024-05-26T09:10:46.617215Z","iopub.status.idle":"2024-05-26T09:10:48.956500Z","shell.execute_reply.started":"2024-05-26T09:10:46.617162Z","shell.execute_reply":"2024-05-26T09:10:48.955290Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndummy = pd.get_dummies(static_cb[['description_5085714M','education_1103M',\n                                  'education_88M','maritalst_385M','maritalst_893M',\n                                 'requesttype_4525192L']],dtype = float)\n\n\n# drop columns\nstatic_cb.drop(columns = ['assignmentdate_238D', 'assignmentdate_4527235D', 'assignmentdate_4955616D' ,'birthdate_574D',\n'dateofbirth_337D',  'dateofbirth_342D'  ,'responsedate_1012D', 'responsedate_4527233D','responsedate_4917613D'\n,'description_5085714M','education_1103M',\n                                  'education_88M','maritalst_385M','maritalst_893M',\n                                 'requesttype_4525192L'\n],inplace = True) ","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:48.958669Z","iopub.execute_input":"2024-05-26T09:10:48.959103Z","iopub.status.idle":"2024-05-26T09:10:52.506054Z","shell.execute_reply.started":"2024-05-26T09:10:48.959065Z","shell.execute_reply":"2024-05-26T09:10:52.501786Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def riskassesment_302T_special(row):\n    if(pd.isnull(row)):\n        return 0\n    \n    else:\n        row = row.replace(\"%\", \"\")\n        tmp = row.split('-')\n        tmp[0] = tmp[0].strip()\n        tmp[1] = tmp[1].strip()\n        \n        return (int(tmp[0]) + int(tmp[1]))/ 2","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:52.510312Z","iopub.execute_input":"2024-05-26T09:10:52.512345Z","iopub.status.idle":"2024-05-26T09:10:52.531723Z","shell.execute_reply.started":"2024-05-26T09:10:52.512244Z","shell.execute_reply":"2024-05-26T09:10:52.527809Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"static_cb['contractssum_5085716L'] = static_cb['contractssum_5085716L'].fillna(0)\nstatic_cb['days120_123L'] = static_cb['days120_123L'].fillna(0)\nstatic_cb['days180_256L'] = static_cb['days180_256L'].fillna(0)\nstatic_cb['days30_165L'] = static_cb['days30_165L'].fillna(0)\nstatic_cb['days90_310L'] = static_cb['days90_310L'].fillna(0)\nstatic_cb['firstquarter_103L'] = static_cb['firstquarter_103L'].fillna(0)\nstatic_cb['for3years_128L'] = static_cb['for3years_128L'].fillna(0)\n\n\n\nstatic_cb[['formonth_118L',\n       'formonth_206L', 'formonth_535L', 'forquarter_1017L', 'forquarter_462L',\n       'forquarter_634L', 'fortoday_1092L', 'forweek_1077L', 'forweek_528L',\n       'forweek_601L', 'foryear_618L', 'foryear_818L', 'foryear_850L',\n       'fourthquarter_440L','numberofqueries_373L','pmtaverage_3A', 'pmtaverage_4527227A',\n       'pmtaverage_4955615A', 'pmtcount_4527229L', 'pmtcount_4955617L',\n       'pmtcount_693L', 'pmtscount_423L', 'pmtssum_45A','days360_512L','for3years_504L']]= static_cb[['formonth_118L',\n       'formonth_206L', 'formonth_535L', 'forquarter_1017L', 'forquarter_462L',\n       'forquarter_634L', 'fortoday_1092L', 'forweek_1077L', 'forweek_528L',\n       'forweek_601L', 'foryear_618L', 'foryear_818L', 'foryear_850L',\n       'fourthquarter_440L','numberofqueries_373L','pmtaverage_3A', 'pmtaverage_4527227A',\n       'pmtaverage_4955615A', 'pmtcount_4527229L', 'pmtcount_4955617L',\n       'pmtcount_693L', 'pmtscount_423L', 'pmtssum_45A','days360_512L','for3years_504L']].fillna(0)\n\nstatic_cb['riskassesment_302T'] = static_cb['riskassesment_302T'].apply(lambda row:riskassesment_302T_special(row))\n","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:52.537279Z","iopub.execute_input":"2024-05-26T09:10:52.538333Z","iopub.status.idle":"2024-05-26T09:10:56.900535Z","shell.execute_reply.started":"2024-05-26T09:10:52.538269Z","shell.execute_reply":"2024-05-26T09:10:56.898987Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"static_cb = pd.concat([static_cb,dummy],axis = 1)","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:56.905598Z","iopub.execute_input":"2024-05-26T09:10:56.906029Z","iopub.status.idle":"2024-05-26T09:10:58.330045Z","shell.execute_reply.started":"2024-05-26T09:10:56.905997Z","shell.execute_reply":"2024-05-26T09:10:58.325794Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(static_cb, on =['case_id'],how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:58.334464Z","iopub.execute_input":"2024-05-26T09:10:58.335273Z","iopub.status.idle":"2024-05-26T09:10:59.276241Z","shell.execute_reply.started":"2024-05-26T09:10:58.335223Z","shell.execute_reply":"2024-05-26T09:10:59.273115Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Tax Registry","metadata":{}},{"cell_type":"code","source":"tax = pd.read_parquet(path+f'/train_tax_registry_a_1.parquet', engine='pyarrow')\n\ntmp = tax.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\n\ntax = tax.merge(tmp, on = ['case_id','num_group1'],how = 'right')\n\ntax = tax[['case_id', 'amount_4527230A']]","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:10:59.277950Z","iopub.execute_input":"2024-05-26T09:10:59.278359Z","iopub.status.idle":"2024-05-26T09:11:03.634530Z","shell.execute_reply.started":"2024-05-26T09:10:59.278328Z","shell.execute_reply":"2024-05-26T09:11:03.629511Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(tax,on= ['case_id'],how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:11:03.642121Z","iopub.execute_input":"2024-05-26T09:11:03.644060Z","iopub.status.idle":"2024-05-26T09:11:04.083167Z","shell.execute_reply.started":"2024-05-26T09:11:03.644008Z","shell.execute_reply":"2024-05-26T09:11:04.080642Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tax = pd.read_parquet(path+f'/train_tax_registry_b_1.parquet', engine='pyarrow')\n\ntmp = tax.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\n\ntax = tax.merge(tmp, on = ['case_id','num_group1'],how = 'right')\n\ntax = tax[['case_id', 'amount_4917619A']]","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:11:04.087624Z","iopub.execute_input":"2024-05-26T09:11:04.088693Z","iopub.status.idle":"2024-05-26T09:11:04.846042Z","shell.execute_reply.started":"2024-05-26T09:11:04.088590Z","shell.execute_reply":"2024-05-26T09:11:04.844504Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(tax,on= ['case_id'],how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:11:04.848029Z","iopub.execute_input":"2024-05-26T09:11:04.848501Z","iopub.status.idle":"2024-05-26T09:11:05.392365Z","shell.execute_reply.started":"2024-05-26T09:11:04.848460Z","shell.execute_reply":"2024-05-26T09:11:05.390805Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tax = pd.read_parquet(path+f'/train_tax_registry_c_1.parquet', engine='pyarrow')\n\ntmp = tax.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\n\ntax = tax.merge(tmp, on = ['case_id','num_group1'],how = 'right')\n\ntax = tax[['case_id','pmtamount_36A']]","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:11:05.394242Z","iopub.execute_input":"2024-05-26T09:11:05.394648Z","iopub.status.idle":"2024-05-26T09:11:07.483768Z","shell.execute_reply.started":"2024-05-26T09:11:05.394615Z","shell.execute_reply":"2024-05-26T09:11:07.478662Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(tax,on= ['case_id'],how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:11:07.491212Z","iopub.execute_input":"2024-05-26T09:11:07.499362Z","iopub.status.idle":"2024-05-26T09:11:09.048658Z","shell.execute_reply.started":"2024-05-26T09:11:07.499083Z","shell.execute_reply":"2024-05-26T09:11:09.047465Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Deposit","metadata":{}},{"cell_type":"code","source":"# Deposit\ndeposit = pd.read_parquet(path+f'/train_deposit_1.parquet', engine='pyarrow')\n\ndeposit['amount_416A'] = deposit['amount_416A'].fillna(0)\n\n# mark the potential error\ndeposit['Flag'] = 0\n\n# end date older than startdate\ndeposit.loc[deposit['contractenddate_991D'] < deposit['openingdate_313D'],'Flag'] = 1\n\ndeposit['record_changes_deposit'] = 1\n\ndeposit.loc[pd.isnull(deposit['contractenddate_991D']) == False,'contractenddate_991D'] = 1\n\ndeposit.loc[pd.isnull(deposit['contractenddate_991D']),'contractenddate_991D'] = 0\n\ntmp = deposit.groupby(['case_id']).agg({'num_group1':'max','record_changes_deposit':'sum'}).reset_index()\n\n\ndeposit.drop(columns = ['record_changes_deposit'],inplace = True)\n\ndeposit = deposit.merge(tmp, on = ['case_id','num_group1'],how = 'right')\n\ndeposit.drop(columns = ['openingdate_313D'],inplace = True)\n\n\ndeposit = deposit[['case_id', 'amount_416A', 'Flag',\n       'record_changes_deposit']]\n\n#deposit.to_excel(f'tmp/deposit_cleaned.xlsx',index = False)","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:11:09.050389Z","iopub.execute_input":"2024-05-26T09:11:09.051424Z","iopub.status.idle":"2024-05-26T09:11:09.360384Z","shell.execute_reply.started":"2024-05-26T09:11:09.051383Z","shell.execute_reply":"2024-05-26T09:11:09.359256Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(deposit, on = 'case_id',how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:11:09.362038Z","iopub.execute_input":"2024-05-26T09:11:09.362414Z","iopub.status.idle":"2024-05-26T09:11:09.755369Z","shell.execute_reply.started":"2024-05-26T09:11:09.362384Z","shell.execute_reply":"2024-05-26T09:11:09.754126Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Person 1","metadata":{}},{"cell_type":"code","source":"# train person 1\nperson_1 = pd.read_parquet(path+f'/train_person_1.parquet', engine='pyarrow')\nperson_1['birth_day'] = np.nan\ncondition = pd.notnull(person_1['birth_259D']) & pd.isnull(person_1['birthdate_87D'])\nperson_1.loc[condition,'birth_day'] = person_1.loc[condition,'birth_259D']\ncondition = pd.isnull(person_1['birth_259D']) & pd.notnull(person_1['birthdate_87D'])\nperson_1.loc[condition,'birth_day'] = person_1.loc[condition,'birthdate_87D']\ncondition = pd.notnull(person_1['birth_259D']) & pd.notnull(person_1['birthdate_87D'])\nperson_1.loc[condition,'birth_day'] = person_1.loc[condition,'birthdate_87D']\nperson_1.drop(columns = ['birth_259D', 'birthdate_87D'],inplace = True)\n\n# a55475b1 should be as previous\n\nperson_1.replace('a55475b1', np.nan, inplace=True)\nperson_1[person_1['num_group1']==0] = person_1[person_1['num_group1']==0].fillna('NoData')\nperson_1.sort_values(by=['case_id', 'num_group1'],inplace = True)\nperson_1 = person_1.fillna(method='ffill')\n\ntmp = person_1.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\nperson_1 = person_1.merge(tmp, on = ['case_id','num_group1'],how = 'right')\nperson_1.replace('NoData', np.nan, inplace=True)\n\n\n# childnum_185L\nperson_1_p = person_1.copy()\nperson_1_p['childnum_185L'].fillna(0, inplace=True)\n\n# contaddr_district_15M\nperson_1_p = person_1_p.drop(columns=['contaddr_district_15M'])\n\n# contaddr_matchlist_1032L\nperson_1_p['contaddr_matchlist_1032L'] = person_1_p['contaddr_matchlist_1032L'].replace({False: 0, np.nan: 1})\n\n# contaddr_smempladdr_334L\nperson_1_p['contaddr_smempladdr_334L'] = person_1_p['contaddr_smempladdr_334L'].replace({False: 0, True: 1})\n\n# contaddr_zipcode_807M\nperson_1_p = person_1_p.drop(columns=['contaddr_zipcode_807M'])\n\n# empl_employedfrom_271D -> retain only year\nperson_1_p['empl_employedfrom_271D'] = person_1_p['empl_employedfrom_271D'].str.split('-').str[0]\n\n# empladdr_district_926M\nperson_1_p = person_1_p.drop(columns=['empladdr_district_926M'])\n\n# empladdr_zipcode_114M\nperson_1_p = person_1_p.drop(columns=['empladdr_zipcode_114M'])\n\n# isreference_387L\nperson_1_p['isreference_387L'] = person_1_p['isreference_387L'].replace({False: 0, True: 1})\n\n# registaddr_district_1083M\nperson_1_p = person_1_p.drop(columns=['registaddr_district_1083M'])\n\n# registaddr_zipcode_184M\nperson_1_p = person_1_p.drop(columns=['registaddr_zipcode_184M'])\n\n# remitter_829L\nperson_1_p['remitter_829L'] = person_1_p['remitter_829L'].replace({False: 0, True: 1})\n\n# role_993L\nperson_1_p['role_993L'] = person_1_p['role_993L'].replace({False: 0, True: 1})\n\n# safeguarantyflag_411L\nperson_1_p['safeguarantyflag_411L'] = person_1_p['safeguarantyflag_411L'].replace({False: 0, True: 1})\n\n# sex_738L\nperson_1_p['sex_738L'] = person_1_p['sex_738L'].replace({'F': 0, 'M': 1})\n\n# type_25L\nperson_1_p = person_1_p.drop(columns=['type_25L'])\n\n# birth_day\nperson_1_p['birth_day'] = pd.to_numeric(person_1_p['birth_day'].str.split('-').str[0])\nperson_1_p['age'] = 2024 - person_1_p['birth_day']\nperson_1_p = person_1_p.drop(columns=['birth_day'])\n\n\n# Create dummy\ncolumns_to_encode = ['education_927M', 'empl_employedtotal_800L', 'empl_industry_691L', 'familystate_447L', 'gender_992L',\n                    'housetype_905L', 'housingtype_772L', 'incometype_1044T', 'language1_981M', 'maritalst_703L', 'relationshiptoclient_642T','relationshiptoclient_415T',\n                    'role_1084L', 'persontype_1072L', 'persontype_792L']\n\nfor column in columns_to_encode:\n    dummies = pd.get_dummies(person_1_p[column], prefix=column,dtype = float)\n    person_1_p = pd.concat([person_1_p, dummies], axis=1)\n    person_1_p.drop(columns=[column], inplace=True)\n    \n\nperson_1_p.drop(columns = ['empl_employedfrom_271D','num_group1','isreference_387L','role_993L'],inplace = True)","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:11:09.757229Z","iopub.execute_input":"2024-05-26T09:11:09.757591Z","iopub.status.idle":"2024-05-26T09:13:16.876501Z","shell.execute_reply.started":"2024-05-26T09:11:09.757561Z","shell.execute_reply":"2024-05-26T09:13:16.875231Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(person_1_p, on = 'case_id',how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:13:16.877910Z","iopub.execute_input":"2024-05-26T09:13:16.878268Z","iopub.status.idle":"2024-05-26T09:13:17.921222Z","shell.execute_reply.started":"2024-05-26T09:13:16.878227Z","shell.execute_reply":"2024-05-26T09:13:17.919817Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Person 2","metadata":{}},{"cell_type":"code","source":"# train person 1\nperson_2 = pd.read_parquet(path+f'/train_person_2.parquet', engine='pyarrow')\n\nperson_2['num_group1'] = person_2['num_group1']*10000\nperson_2['num_group3'] = person_2['num_group1']+person_2['num_group2']\nperson_2.replace('a55475b1', np.nan, inplace=True)\nperson_2[person_2['num_group3']==0] = person_2[person_2['num_group3']==0].fillna('ND')\nperson_2.sort_values(by=['case_id', 'num_group3'],inplace = True)\nperson_2 = person_2.fillna(method='ffill')\n\ntmp = person_2.groupby(['case_id']).agg({'num_group3':'max'}).reset_index()\nperson_2 = person_2.merge(tmp, on = ['case_id','num_group3'],how = 'right')\nperson_2.replace('ND', np.nan, inplace=True)\n\n\ncondition = person_2['addres_role_871L']=='PERMANENT'\n\nperson_2.loc[condition,'addres_role_871L'] = 1\nperson_2.loc[condition==False,'addres_role_871L'] = 0\n\n# get dummy\ndummy = pd.get_dummies(person_2[['empls_economicalst_849M','relatedpersons_role_762T'\n                                           ]],dtype = float)\n\nperson_2.drop(columns = ['addres_district_368M','addres_zip_823M','conts_role_79M',\n                         'empls_employedfrom_796D','empls_employer_name_740M',\n                         'empls_economicalst_849M','relatedpersons_role_762T'],inplace = True)\n\nperson_2 = pd.concat([person_2,dummy],axis = 1)","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:13:17.923332Z","iopub.execute_input":"2024-05-26T09:13:17.923822Z","iopub.status.idle":"2024-05-26T09:13:30.081480Z","shell.execute_reply.started":"2024-05-26T09:13:17.923778Z","shell.execute_reply":"2024-05-26T09:13:30.080410Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(person_2, on = 'case_id',how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:13:30.083008Z","iopub.execute_input":"2024-05-26T09:13:30.083375Z","iopub.status.idle":"2024-05-26T09:13:30.639112Z","shell.execute_reply.started":"2024-05-26T09:13:30.083344Z","shell.execute_reply":"2024-05-26T09:13:30.637926Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Other","metadata":{}},{"cell_type":"code","source":"# other \nother = pd.read_parquet(path+f'/train_other_1.parquet', engine='pyarrow')\n\ntmp = other.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\n\nother = other.merge(tmp, on = ['case_id','num_group1'],how = 'right')\n\nother = other.drop(columns = ['num_group1'])","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:13:30.640587Z","iopub.execute_input":"2024-05-26T09:13:30.640918Z","iopub.status.idle":"2024-05-26T09:13:30.690883Z","shell.execute_reply.started":"2024-05-26T09:13:30.640891Z","shell.execute_reply":"2024-05-26T09:13:30.689763Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(other, on = 'case_id',how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:13:30.692671Z","iopub.execute_input":"2024-05-26T09:13:30.693118Z","iopub.status.idle":"2024-05-26T09:13:31.043228Z","shell.execute_reply.started":"2024-05-26T09:13:30.693077Z","shell.execute_reply":"2024-05-26T09:13:31.041855Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# debitcard","metadata":{}},{"cell_type":"code","source":"# train_debitcard\ndebitcard = pd.read_parquet(path+f'/train_debitcard_1.parquet', engine='pyarrow')\n\n\n# count change times\ndebitcard['records_changes'] = 1\n\n\n\ntmp = debitcard.groupby(['case_id']).agg({'num_group1':'max','records_changes':'sum'}).reset_index()\n\ndebitcard.drop(columns = ['records_changes'],inplace = True)\n\ndebitcard = debitcard.merge(tmp, on = ['case_id','num_group1'],how = 'right')\n\n# fill na with -1\n\ndebitcard = debitcard.fillna(-1)\n\ndebitcard.drop(columns = ['num_group1', 'openingdate_857D'],inplace = True)","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:13:31.045028Z","iopub.execute_input":"2024-05-26T09:13:31.045532Z","iopub.status.idle":"2024-05-26T09:13:31.195805Z","shell.execute_reply.started":"2024-05-26T09:13:31.045490Z","shell.execute_reply":"2024-05-26T09:13:31.194690Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(debitcard, on = 'case_id',how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:13:31.197182Z","iopub.execute_input":"2024-05-26T09:13:31.197607Z","iopub.status.idle":"2024-05-26T09:13:31.557293Z","shell.execute_reply.started":"2024-05-26T09:13:31.197571Z","shell.execute_reply":"2024-05-26T09:13:31.556047Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# credit_bureas_a_1","metadata":{}},{"cell_type":"code","source":"res = []\nfor i in range (0,4):\n    train_credit_bureau_a = pd.read_parquet(path+f'/train_credit_bureau_a_1_{str(i)}.parquet', engine='pyarrow')\n\n    train_credit_bureau_a.replace('a55475b1', np.nan, inplace=True)\n    train_credit_bureau_a[train_credit_bureau_a['num_group1']==0] = train_credit_bureau_a[train_credit_bureau_a['num_group1']==0].fillna('ND')\n    train_credit_bureau_a.sort_values(by=['case_id', 'num_group1'],inplace = True)\n    train_credit_bureau_a = train_credit_bureau_a.fillna(method='ffill')\n\n    tmp = train_credit_bureau_a.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\n    train_credit_bureau_a= train_credit_bureau_a.merge(tmp, on = ['case_id','num_group1'],how = 'right')\n    train_credit_bureau_a.replace('ND', np.nan, inplace=True)\n\n\n\n    train_credit_bureau_a[['interestrate_508L', 'nominalrate_281L', 'nominalrate_498L']] = train_credit_bureau_a[['interestrate_508L', 'nominalrate_281L', 'nominalrate_498L']].fillna(-1)\n\n    # get dummy\n    dummy = pd.get_dummies(train_credit_bureau_a[['periodicityofpmts_1102L' ]],dtype = float)\n\n\n    train_credit_bureau_a.drop(columns = ['lastupdate_1112D','classificationofcontr_13M',\n     'classificationofcontr_400M',\n     'contractst_545M',\n     'contractst_964M',\n     'contractsum_5085717L',\n     'dateofcredend_289D',\n     'dateofcredend_353D',\n     'dateofcredstart_181D',\n     'dateofcredstart_739D',\n     'dateofrealrepmt_138D',\n     'dpdmaxdatemonth_442T',\n     'financialinstitution_382M',\n     'financialinstitution_591M',\n     'overdueamountmax2date_1002D',\n     'overdueamountmax2date_1142D',\n     'subjectrole_182M',\n     'subjectrole_93M',\n     'refreshdate_3813885D',\n     'numberofoverdueinstlmaxdat_641D',\n      'purposeofcred_426M',\n    'numberofoverdueinstlmaxdat_641D',\n    'periodicityofpmts_1102L','description_351M',\n 'dpdmax_139P','dpdmax_757P','dpdmaxdatemonth_89T',\n'dpdmaxdateyear_596T','dpdmaxdateyear_896T','refreshdate_3813885D','purposeofcred_874M'],inplace = True)\n\n    train_credit_bureau_a = pd.concat([train_credit_bureau_a,dummy ],axis = 1)\n    \n    res.append(train_credit_bureau_a)\n","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:13:31.564433Z","iopub.execute_input":"2024-05-26T09:13:31.564839Z","iopub.status.idle":"2024-05-26T09:22:11.799063Z","shell.execute_reply.started":"2024-05-26T09:13:31.564804Z","shell.execute_reply":"2024-05-26T09:22:11.797742Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"res = pd.concat(res)","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:22:11.800831Z","iopub.execute_input":"2024-05-26T09:22:11.801206Z","iopub.status.idle":"2024-05-26T09:22:12.218111Z","shell.execute_reply.started":"2024-05-26T09:22:11.801157Z","shell.execute_reply":"2024-05-26T09:22:12.217002Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.merge(res , on = 'case_id',how = 'left')","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:22:12.219509Z","iopub.execute_input":"2024-05-26T09:22:12.219836Z","iopub.status.idle":"2024-05-26T09:22:13.496982Z","shell.execute_reply.started":"2024-05-26T09:22:12.219809Z","shell.execute_reply":"2024-05-26T09:22:13.495745Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Train_credit_bureau_a_2","metadata":{}},{"cell_type":"code","source":"res = []\nfor i in range(0,10):\n    credit_bureau_a_2 = pd.read_parquet(path+f'/train_credit_bureau_a_2_{str(i)}.parquet', engine='pyarrow')\n    credit_bureau_a_2['num_group1'] = credit_bureau_a_2['num_group1']*10\n    credit_bureau_a_2['num_group3'] = credit_bureau_a_2['num_group1']+credit_bureau_a_2['num_group2']\n    credit_bureau_a_2.replace('a55475b1', np.nan, inplace=True)\n    credit_bureau_a_2[credit_bureau_a_2['num_group3']==0] = credit_bureau_a_2[credit_bureau_a_2['num_group3']==0].fillna('ND')\n    credit_bureau_a_2.sort_values(by=['case_id', 'num_group3'],inplace = True)\n    credit_bureau_a_2 = credit_bureau_a_2.fillna(method='ffill')\n\n    tmp = credit_bureau_a_2.groupby(['case_id']).agg({'num_group3':'max'}).reset_index()\n    credit_bureau_a_2 = credit_bureau_a_2.merge(tmp, on = ['case_id','num_group3'],how = 'right')\n    credit_bureau_a_2.replace('ND', np.nan, inplace=True)\n\n    dummy = pd.get_dummies(credit_bureau_a_2[['collater_typofvalofguarant_298M',\n                                            'collater_typofvalofguarant_407M',\n                                            'collaterals_typeofguarante_359M',\n                                            'collaterals_typeofguarante_669M'\n                                           ]],dtype = float)\n\n\n    # drop column\n    credit_bureau_a_2.drop(columns = ['pmts_dpd_303P','pmts_year_1139T','pmts_year_507T',\n                                     'subjectroles_name_541M', 'subjectroles_name_838M',\n           'num_group3','collater_typofvalofguarant_298M',\n                                            'collater_typofvalofguarant_407M',\n                                            'collaterals_typeofguarante_359M',\n                                            'collaterals_typeofguarante_669M'],inplace = True)\n\n\n    \n    res.append(credit_bureau_a_2)","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:22:13.498742Z","iopub.execute_input":"2024-05-26T09:22:13.499116Z","iopub.status.idle":"2024-05-26T09:40:43.374954Z","shell.execute_reply.started":"2024-05-26T09:22:13.499085Z","shell.execute_reply":"2024-05-26T09:40:43.373640Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"res = pd.concat(res)\ndf = df.merge(res , on = 'case_id',how = 'left')","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:40:43.376655Z","iopub.execute_input":"2024-05-26T09:40:43.377091Z","iopub.status.idle":"2024-05-26T09:40:44.025417Z","shell.execute_reply.started":"2024-05-26T09:40:43.377053Z","shell.execute_reply":"2024-05-26T09:40:44.024157Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# credit_bureau_b_1","metadata":{}},{"cell_type":"code","source":"res = []\n\nprint(path+f'/train_credit_bureau_b_1.parquet')\ncredit_bureau_b = pd.read_parquet(path+f'/train_credit_bureau_b_1.parquet', engine='pyarrow')\n\n# to dummy\nprint(credit_bureau_b)\ndummy = pd.get_dummies(credit_bureau_b[['classificationofcontr_1114M','contractst_516M',\n                                        'contracttype_653M','dpdmaxdatemonth_804T'\n                                        ,'dpdmaxdateyear_742T','overdueamountmaxdateyear_432T',\n                                        'pmtmethod_731M','periodicityofpmts_997L',\n                                        'periodicityofpmts_997M','purposeofcred_722M','periodicityofpmts_997L'\n                                       ]],dtype = float)\n\n# drop column \ncredit_bureau_b.drop(columns = ['contractdate_551D','contractmaturitydate_151D',\n                               'credor_3940957M','lastupdate_260D','subjectrole_326M',\n                                'subjectrole_43M','classificationofcontr_1114M','contractst_516M',\n                                        'contracttype_653M','dpdmaxdatemonth_804T'\n                                        ,'dpdmaxdateyear_742T','overdueamountmaxdateyear_432T',\n                                        'pmtmethod_731M','periodicityofpmts_997L',\n                                        'periodicityofpmts_997M','purposeofcred_722M','periodicityofpmts_997L'],inplace = True)\n\n\ncredit_bureau_b.replace('a55475b1', np.nan, inplace=True)\ncredit_bureau_b[credit_bureau_b['num_group1']==0] = credit_bureau_b[credit_bureau_b['num_group1']==0].fillna('ND')\ncredit_bureau_b.sort_values(by=['case_id', 'num_group1'],inplace = True)\ncredit_bureau_b= credit_bureau_b.fillna(method='ffill')\n\ntmp = credit_bureau_b.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\ncredit_bureau_b= credit_bureau_b.merge(tmp, on = ['case_id','num_group1'],how = 'right')\ncredit_bureau_b.replace('ND', np.nan, inplace=True)\n\nres.append(credit_bureau_b)","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:40:44.027047Z","iopub.execute_input":"2024-05-26T09:40:44.027415Z","iopub.status.idle":"2024-05-26T09:40:45.824135Z","shell.execute_reply.started":"2024-05-26T09:40:44.027385Z","shell.execute_reply":"2024-05-26T09:40:45.822875Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"res = pd.concat(res)\ndf.drop(columns = ['num_group1'],inplace = True)\ndf = df.merge(res , on = 'case_id',how = 'left')","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:40:45.825530Z","iopub.execute_input":"2024-05-26T09:40:45.825855Z","iopub.status.idle":"2024-05-26T09:40:46.397224Z","shell.execute_reply.started":"2024-05-26T09:40:45.825829Z","shell.execute_reply":"2024-05-26T09:40:46.395976Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# fillna\ndf['dpd_550P'] = df['dpd_550P'].fillna(-1)\ndf['dpd_733P'] = df['dpd_733P'].fillna(-1)","metadata":{"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:40:46.398822Z","iopub.execute_input":"2024-05-26T09:40:46.399170Z","iopub.status.idle":"2024-05-26T09:40:46.407561Z","shell.execute_reply.started":"2024-05-26T09:40:46.399141Z","shell.execute_reply":"2024-05-26T09:40:46.406275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# credit_bureau_b_2","metadata":{}},{"cell_type":"code","source":"res = []\n\ncredit_bureau_b_2 = pd.read_parquet(path+f'/train_credit_bureau_b_2.parquet', engine='pyarrow')\n\n# drop column \ncredit_bureau_b_2.drop(columns = ['pmts_date_1107D'],inplace = True)\n\n\ncredit_bureau_b_2['num_group1'] = credit_bureau_b_2['num_group1']*10000\ncredit_bureau_b_2['num_group3'] = credit_bureau_b_2['num_group1']+credit_bureau_b_2['num_group2']\n#credit_bureau_b_2.replace('a55475b1', np.nan, inplace=True)\ncredit_bureau_b_2[credit_bureau_b_2['num_group3']==0] = credit_bureau_b_2[credit_bureau_b_2['num_group3']==0].fillna('ND')\ncredit_bureau_b_2.sort_values(by=['case_id', 'num_group3'],inplace = True)\ncredit_bureau_b_2 = credit_bureau_b_2.fillna(method='ffill')\n\ntmp = credit_bureau_b_2.groupby(['case_id']).agg({'num_group3':'max'}).reset_index()\ncredit_bureau_b_2 = credit_bureau_b_2.merge(tmp, on = ['case_id','num_group3'],how = 'right')\ncredit_bureau_b_2.replace('ND', np.nan, inplace=True)\n\nres.append(credit_bureau_b_2)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2024-05-26T09:40:46.409409Z","iopub.execute_input":"2024-05-26T09:40:46.409869Z","iopub.status.idle":"2024-05-26T09:40:47.576908Z","shell.execute_reply.started":"2024-05-26T09:40:46.409809Z","shell.execute_reply":"2024-05-26T09:40:47.575695Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"res = pd.concat(res)\ndf.drop(columns = ['num_group1'],inplace = True)\ndf = df.merge(res , on = 'case_id',how = 'left')\ndf","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2024-05-26T09:40:47.578558Z","iopub.execute_input":"2024-05-26T09:40:47.578999Z","iopub.status.idle":"2024-05-26T09:40:48.202507Z","shell.execute_reply.started":"2024-05-26T09:40:47.578953Z","shell.execute_reply":"2024-05-26T09:40:48.201377Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"credit_bureau_b_2.drop(columns = ['num_group1', 'num_group2', 'num_group3'],inplace = True)","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:40:48.204346Z","iopub.execute_input":"2024-05-26T09:40:48.205336Z","iopub.status.idle":"2024-05-26T09:40:48.212626Z","shell.execute_reply.started":"2024-05-26T09:40:48.205290Z","shell.execute_reply":"2024-05-26T09:40:48.211451Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# train_applprev_1","metadata":{}},{"cell_type":"code","source":"res = []\nfor i in range(0,2):\n    \n    train_applprev_1_0 = pd.read_parquet(path+f'/train_applprev_1_{str(i)}.parquet', engine='pyarrow')\n\n    # drop columns\n    train_applprev_1_0.drop(columns = ['approvaldate_319D',\n                                       'dateactivated_425D',\n                                       'creationdate_885D',\n                                       'dtlastpmt_581D',\n                                      'district_544M',\n                                      'employedfrom_700D',\n                                      'firstnonzeroinstldate_307D',\n                                       'rejectreason_755M',\n                                      'profession_152M',\n                                      'revolvingaccount_394A',\n                                      'dtlastpmtallstes_3545839D',\n                                      'inittransactioncode_279L'],inplace = True)\n\n\n\n    train_applprev_1_0.replace('a55475b1', np.nan, inplace=True)\n    train_applprev_1_0[train_applprev_1_0['num_group1']==0] = train_applprev_1_0[train_applprev_1_0['num_group1']==0].fillna('ND')\n    train_applprev_1_0.sort_values(by=['case_id', 'num_group1'],inplace = True)\n    train_applprev_1_0= train_applprev_1_0.fillna(method='ffill')\n\n    tmp = train_applprev_1_0.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\n    train_applprev_1_0= train_applprev_1_0.merge(tmp, on = ['case_id','num_group1'],how = 'right')\n    train_applprev_1_0.replace('ND', np.nan, inplace=True)\n\n\n\n    dummy = pd.get_dummies(train_applprev_1_0[['credtype_587L',\n    'education_1138M',\n    'familystate_726L',\n    'postype_4733339M',\n    'credacc_status_367L',\n    'status_219L',\n    'rejectreasonclient_4145042M',\n    'credtype_587L']],dtype = float)\n\n    train_applprev_1_0.drop(columns = [\n    'credtype_587L',\n    'education_1138M',\n    'familystate_726L',\n    'postype_4733339M',\n    'credacc_status_367L',\n    'rejectreasonclient_4145042M',\n    'status_219L',\n    'credtype_587L'],inplace = True)\n\n\n    res.append(train_applprev_1_0)\n\ntrain_applprev_1_0 = pd.concat(res)\ndf.drop(columns = ['num_group1'],inplace = True)\ndf = df.merge(train_applprev_1_0 , on = 'case_id',how = 'left')\n# fillna \ndf.loc[pd.isnull(df['isbidproduct_390L']),'isbidproduct_390L'] = 0\ndf.loc[pd.notnull(df['isbidproduct_390L']),'isbidproduct_390L'] = 1\n# split 'cancelreason_3545846M' to cancel or not cancel\ndf.loc[pd.isnull(df['cancelreason_3545846M']),'cancelreason_3545846M'] = 0\ndf.loc[pd.notnull(df['cancelreason_3545846M']),'cancelreason_3545846M'] = 1\ndf.loc[pd.isnull(df['isdebitcard_527L']),'isdebitcard_527L'] = 1\ndf.loc[df['isdebitcard_527L'] == False,'isdebitcard_527L'] = 0\ndf.loc[df['isdebitcard_527L'] == True,'isdebitcard_527L'] = 1\n","metadata":{"scrolled":true,"tags":[],"execution":{"iopub.status.busy":"2024-05-26T09:40:48.214352Z","iopub.execute_input":"2024-05-26T09:40:48.215087Z","iopub.status.idle":"2024-05-26T09:42:32.082946Z","shell.execute_reply.started":"2024-05-26T09:40:48.215048Z","shell.execute_reply":"2024-05-26T09:42:32.081721Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# train_applprev_2(unnecessary)","metadata":{"tags":[]}},{"cell_type":"code","source":"train_applprev_2 = pd.read_parquet(path+f'/train_applprev_2.parquet', engine='pyarrow')\n\ntrain_applprev_2['num_group1'] = train_applprev_2['num_group1']*10\n\ntrain_applprev_2['num_group3'] = train_applprev_2['num_group1']+train_applprev_2['num_group2']\n\n\ntrain_applprev_2 = train_applprev_2[['case_id', 'cacccardblochreas_147M', 'conts_type_509L',\n       'credacc_cards_status_52L','num_group3']]\n\ntrain_applprev_2.replace('a55475b1', np.nan, inplace=True)\ntrain_applprev_2[train_applprev_2['num_group3']==0] = train_applprev_2[train_applprev_2['num_group3']==0].fillna('ND')\ntrain_applprev_2.sort_values(by=['case_id', 'num_group3'],inplace = True)\ntrain_applprev_2 = train_applprev_2.fillna(method='ffill')\n\ntmp = train_applprev_2.groupby(['case_id']).agg({'num_group3':'max'}).reset_index()\ntrain_applprev_2 = train_applprev_2.merge(tmp, on = ['case_id','num_group3'],how = 'right')\ntrain_applprev_2.replace('ND', np.nan, inplace=True)\n\n# dummy \ndummy = pd.get_dummies(train_applprev_2[['cacccardblochreas_147M',\n'conts_type_509L',\n       'credacc_cards_status_52L']],dtype = float)\n\ntrain_applprev_2.drop(columns = ['cacccardblochreas_147M',\n'conts_type_509L',\n       'credacc_cards_status_52L','num_group3'],inplace = True)\n\ntrain_applprev_2= pd.concat([train_applprev_2,dummy], axis=1)\ndf = df.merge(train_applprev_2 , on = 'case_id',how = 'left')\ndf.fillna(0, inplace=True)\ndf.drop(columns = ['datefirstoffer_1144D', 'datelastunpaid_3546854D', 'dtlastpmtallstes_4499206D', 'lastdelinqdate_224D', 'lastupdate_388D', 'numberofoverdueinstlmaxdat_148D'],inplace = True)","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:42:32.084642Z","iopub.execute_input":"2024-05-26T09:42:32.085111Z","iopub.status.idle":"2024-05-26T09:43:02.840947Z","shell.execute_reply.started":"2024-05-26T09:42:32.085072Z","shell.execute_reply":"2024-05-26T09:43:02.839636Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X = df.drop(columns=['case_id', 'target', 'date_decision', 'MONTH', 'WEEK_NUM'])\ny = df['target']\n\nX_train, X_val, y_train, y_val = train_test_split(X, y, test_size=0.2, random_state=42)\n\n\nrf = RandomForestClassifier(n_estimators=100, random_state=42)\n\nrf.fit(X_train, y_train)\n\n\ny_pred = rf.predict(X_val)\nprint(f\"Accuracy: {accuracy_score(y_val, y_pred)}\")\nprint(classification_report(y_val, y_pred))\n\n\n# X_test = test_df.drop(columns=['case_id', 'date_decision', 'MONTH', 'WEEK_NUM'])\n\n# test_pred = rf.predict(X_test)\n\n# test_df['target'] = test_pred\n# output = test_df[['case_id', 'target']]\n\n# output.to_csv('test_predictions.csv', index=False)\n","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:43:02.842870Z","iopub.execute_input":"2024-05-26T09:43:02.843345Z","iopub.status.idle":"2024-05-26T09:44:11.360679Z","shell.execute_reply.started":"2024-05-26T09:43:02.843302Z","shell.execute_reply":"2024-05-26T09:44:11.359128Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_path = r'/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test'\n\n\ndf_test = pd.read_parquet(test_path+'/test_base.parquet', engine='pyarrow')\n\ntest1 = df_test.copy()\n\nres = []\nfor i in range(0,3):\n\n    static_0_0 = pd.read_parquet(test_path+f'/test_static_0_{str(i)}.parquet', engine='pyarrow')\n\n    static_0_0[['actualdpdtolerance_344P','amtinstpaidbefduel24m_4187115A',\n     'annuity_780A','annuitynextmonth_57A','applicationcnt_361L','applications30d_658L','applicationscnt_1086L','applicationscnt_464L',\n     'applicationscnt_629L','applicationscnt_867L','avgdbddpdlast24m_3658932P','avgdbddpdlast3m_4187120P',\n     'avgdbdtollast24m_4525197P','avgdpdtolclosure24_3658938P','avginstallast24m_3658937A','avglnamtstart24m_4525187A',\n     'avgmaxdpdlast9m_3716943P','avgoutstandbalancel6m_4187114A','avgpmtlast12m_4525200A',\n     'clientscnt_100L','clientscnt12m_3712952L','clientscnt3m_3712950L','clientscnt6m_3712949L','maininc_215A']] = static_0_0[['actualdpdtolerance_344P','amtinstpaidbefduel24m_4187115A',\n     'annuity_780A','annuitynextmonth_57A','applicationcnt_361L','applications30d_658L','applicationscnt_1086L','applicationscnt_464L',\n     'applicationscnt_629L','applicationscnt_867L','avgdbddpdlast24m_3658932P','avgdbddpdlast3m_4187120P',\n     'avgdbdtollast24m_4525197P','avgdpdtolclosure24_3658938P','avginstallast24m_3658937A','avglnamtstart24m_4525187A',\n     'avgmaxdpdlast9m_3716943P','avgoutstandbalancel6m_4187114A','avgpmtlast12m_4525200A',\n     'clientscnt_100L','clientscnt12m_3712952L','clientscnt3m_3712950L','clientscnt6m_3712949L','maininc_215A']].fillna(0)\n\n\n\n    dummy = pd.get_dummies(static_0_0[['bankacctype_710L',\n     'cardtype_51L',\n     'credtype_322L',\n     'disbursementtype_67L',\n     'equalitydataagreement_891L',\n     'equalityempfrom_62L',\n     'inittransactioncode_186L',\n     'isbidproductrequest_292L',\n     'isdebitcard_729L',\n     'lastst_736L',\n     'paytype_783L',\n     'paytype1st_925L',\n     'twobodfilling_608L',\n     'typesuite_864L']],dtype = float)\n\n\n\n    static_0_0.drop(columns = ['firstclxcampaign_1125D','firstdatedue_489D',\n     'lastactivateddate_801D','lastapplicationdate_877D','lastapprdate_640D',\n     'lastrepayingdate_696D','opencred_647L','payvacationpostpone_4187118D',\n     'previouscontdistrict_112M','validfrom_1069D','datelastinstal40dpd_247D','bankacctype_710L',\n     'cardtype_51L','credtype_322L','disbursementtype_67L','equalitydataagreement_891L',\n     'equalityempfrom_62L','inittransactioncode_186L','isbidproductrequest_292L',\n     'isdebitcard_729L','lastapprcommoditycat_1041M',\n     'lastapprcommoditytypec_5251766M','lastcancelreason_561M',\n     'lastrejectcommoditycat_161M','lastrejectcommodtypec_5251769M','lastrejectreason_759M',\n     'lastrejectreasonclient_4145040M','lastst_736L','paytype_783L','paytype1st_925L',\n     'twobodfilling_608L','typesuite_864L','maxdpdinstldate_3546855D','lastrejectdate_50D'], inplace = True)\n\n\n    condition = static_0_0['isbidproduct_1095L']\n\n    static_0_0.loc[condition,'isbidproduct_1095L'] = 0\n    static_0_0.loc[condition==False,'isbidproduct_1095L'] = 1\n\n\n    static_0_0 = pd.concat([static_0_0,dummy],axis = 1)\n    static_0_0 = static_0_0.fillna(0)\n    \n    res.append(static_0_0)\n    \nres = pd.concat(res)\ndf_test = df_test.merge(res, on =['case_id'],how = 'left')\n\nprint(f'1 {len(df_test)}')\n\n\nstatic_cb = pd.read_parquet(test_path+f'/test_static_cb_0.parquet', engine='pyarrow')\ndummy = pd.get_dummies(static_cb[['description_5085714M','education_1103M',\n                                  'education_88M','maritalst_385M','maritalst_893M',\n                                 'requesttype_4525192L']],dtype = float)\n\n\n# drop columns\nstatic_cb.drop(columns = ['assignmentdate_238D', 'assignmentdate_4527235D', 'assignmentdate_4955616D' ,'birthdate_574D',\n'dateofbirth_337D',  'dateofbirth_342D'  ,'responsedate_1012D', 'responsedate_4527233D','responsedate_4917613D'\n,'description_5085714M','education_1103M',\n                                  'education_88M','maritalst_385M','maritalst_893M',\n                                 'requesttype_4525192L'\n],inplace = True)\n\ndef riskassesment_302T_special(row):\n    if(pd.isnull(row)):\n        return 0\n    \n    else:\n        row = row.replace(\"%\", \"\")\n        tmp = row.split('-')\n        tmp[0] = tmp[0].strip()\n        tmp[1] = tmp[1].strip()\n        \n        return (int(tmp[0]) + int(tmp[1]))/ 2\nstatic_cb['contractssum_5085716L'] = static_cb['contractssum_5085716L'].fillna(0)\nstatic_cb['days120_123L'] = static_cb['days120_123L'].fillna(0)\nstatic_cb['days180_256L'] = static_cb['days180_256L'].fillna(0)\nstatic_cb['days30_165L'] = static_cb['days30_165L'].fillna(0)\nstatic_cb['days90_310L'] = static_cb['days90_310L'].fillna(0)\nstatic_cb['firstquarter_103L'] = static_cb['firstquarter_103L'].fillna(0)\nstatic_cb['for3years_128L'] = static_cb['for3years_128L'].fillna(0)\n\n\n\n\nstatic_cb['riskassesment_302T'] = static_cb['riskassesment_302T'].apply(lambda row:riskassesment_302T_special(row))\nstatic_cb = pd.concat([static_cb,dummy],axis = 1)\ndf_test = df_test.merge(static_cb, on =['case_id'],how = 'left')\n\nprint(f'2 {len(df_test)}')\n\n\n\ntax = pd.read_parquet(test_path+f'/test_tax_registry_a_1.parquet', engine='pyarrow')\ntmp = tax.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\ntax = tax.merge(tmp, on = ['case_id','num_group1'],how = 'right')\ntax = tax[['case_id', 'amount_4527230A']]\ndf_test = df_test.merge(tax,on= ['case_id'],how = 'left')\ntax = pd.read_parquet(test_path+f'/test_tax_registry_b_1.parquet', engine='pyarrow')\ntmp = tax.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\ntax = tax.merge(tmp, on = ['case_id','num_group1'],how = 'right')\ntax = tax[['case_id', 'amount_4917619A']]\ndf_test = df_test.merge(tax,on= ['case_id'],how = 'left')\ntax = pd.read_parquet(test_path+f'/test_tax_registry_c_1.parquet', engine='pyarrow')\ntmp = tax.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\ntax = tax.merge(tmp, on = ['case_id','num_group1'],how = 'right')\ntax = tax[['case_id','pmtamount_36A']]\ndf_test = df_test.merge(tax,on= ['case_id'],how = 'left')\ndf_test['amount_4527230A'] = df_test['amount_4527230A'].fillna(0)\ndf_test['amount_4917619A'] = df_test['amount_4917619A'].fillna(0)\ndf_test['pmtamount_36A']  =  df_test['pmtamount_36A'].fillna(0)\n# Deposit\ndeposit = pd.read_parquet(test_path+f'/test_deposit_1.parquet', engine='pyarrow')\ndeposit['amount_416A'] = deposit['amount_416A'].fillna(0)\ndeposit['Flag'] = 0\ndeposit.loc[deposit['contractenddate_991D'] < deposit['openingdate_313D'],'Flag'] = 1\ndeposit['record_changes_deposit'] = 1\ndeposit.loc[pd.isnull(deposit['contractenddate_991D']) == False,'contractenddate_991D'] = 1\ndeposit.loc[pd.isnull(deposit['contractenddate_991D']),'contractenddate_991D'] = 0\ntmp = deposit.groupby(['case_id']).agg({'num_group1':'max','record_changes_deposit':'sum'}).reset_index()\ndeposit.drop(columns = ['record_changes_deposit'],inplace = True)\ndeposit = deposit.merge(tmp, on = ['case_id','num_group1'],how = 'right')\ndeposit.drop(columns = ['openingdate_313D'],inplace = True)\ndeposit = deposit[['case_id', 'amount_416A', 'Flag',\n       'record_changes_deposit']]\ndf_test = df_test.merge(deposit, on = 'case_id',how = 'left')\ndf_test['amount_416A'] = df_test['amount_416A'].fillna(0)\ndf_test['Flag'] = df_test['Flag'].fillna(0)\ndf_test['record_changes_deposit'] = df_test['record_changes_deposit'].fillna(0)\n\n# train person 1\nperson_1 = pd.read_parquet(test_path+f'/test_person_1.parquet', engine='pyarrow')\nperson_1['birth_day'] = np.nan\ncondition = pd.notnull(person_1['birth_259D']) & pd.isnull(person_1['birthdate_87D'])\nperson_1.loc[condition,'birth_day'] = person_1.loc[condition,'birth_259D']\ncondition = pd.isnull(person_1['birth_259D']) & pd.notnull(person_1['birthdate_87D'])\nperson_1.loc[condition,'birth_day'] = person_1.loc[condition,'birthdate_87D']\ncondition = pd.notnull(person_1['birth_259D']) & pd.notnull(person_1['birthdate_87D'])\nperson_1.loc[condition,'birth_day'] = person_1.loc[condition,'birthdate_87D']\nperson_1.drop(columns = ['birth_259D', 'birthdate_87D'],inplace = True)\n\n# a55475b1 should be as previous\n\nperson_1.replace('a55475b1', np.nan, inplace=True)\nperson_1[person_1['num_group1']==0] = person_1[person_1['num_group1']==0].fillna('NoData')\nperson_1.sort_values(by=['case_id', 'num_group1'],inplace = True)\nperson_1 = person_1.fillna(method='ffill')\n\ntmp = person_1.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\nperson_1 = person_1.merge(tmp, on = ['case_id','num_group1'],how = 'right')\nperson_1.replace('NoData', np.nan, inplace=True)\nperson_1_p = person_1.copy()\nperson_1_p['childnum_185L'].fillna(0, inplace=True)\nperson_1_p = person_1_p.drop(columns=['contaddr_district_15M'])\nperson_1_p['contaddr_matchlist_1032L'] = person_1_p['contaddr_matchlist_1032L'].replace({False: 0, np.nan: 1})\nperson_1_p['contaddr_smempladdr_334L'] = person_1_p['contaddr_smempladdr_334L'].replace({False: 0, True: 1})\nperson_1_p = person_1_p.drop(columns=['contaddr_zipcode_807M'])\nperson_1_p['empl_employedfrom_271D'] = person_1_p['empl_employedfrom_271D'].str.split('-').str[0]\nperson_1_p = person_1_p.drop(columns=['empladdr_district_926M'])\nperson_1_p = person_1_p.drop(columns=['empladdr_zipcode_114M'])\nperson_1_p['isreference_387L'] = person_1_p['isreference_387L'].replace({False: 0, True: 1})\nperson_1_p = person_1_p.drop(columns=['registaddr_district_1083M'])\nperson_1_p = person_1_p.drop(columns=['registaddr_zipcode_184M'])\nperson_1_p['remitter_829L'] = person_1_p['remitter_829L'].replace({False: 0, True: 1})\nperson_1_p['role_993L'] = person_1_p['role_993L'].replace({False: 0, True: 1})\nperson_1_p['safeguarantyflag_411L'] = person_1_p['safeguarantyflag_411L'].replace({False: 0, True: 1})\nperson_1_p['sex_738L'] = person_1_p['sex_738L'].replace({'F': 0, 'M': 1})\nperson_1_p = person_1_p.drop(columns=['type_25L'])\nperson_1_p['birth_day'] = pd.to_numeric(person_1_p['birth_day'].str.split('-').str[0])\nperson_1_p['age'] = 2024 - person_1_p['birth_day']\nperson_1_p = person_1_p.drop(columns=['birth_day'])\ncolumns_to_encode = ['education_927M', 'empl_employedtotal_800L', 'empl_industry_691L', 'familystate_447L', 'gender_992L',\n                    'housetype_905L', 'housingtype_772L', 'incometype_1044T', 'language1_981M', 'maritalst_703L', 'relationshiptoclient_642T','relationshiptoclient_415T',\n                    'role_1084L', 'persontype_1072L', 'persontype_792L']\nfor column in columns_to_encode:\n    dummies = pd.get_dummies(person_1_p[column], prefix=column,dtype = float)\n    person_1_p = pd.concat([person_1_p, dummies], axis=1)\n    person_1_p.drop(columns=[column], inplace=True)\nperson_1_p['remitter_829L'] = person_1_p['remitter_829L'].fillna(0)\nperson_1_p.drop(columns = ['empl_employedfrom_271D','num_group1','isreference_387L','role_993L'],inplace = True)\ndf_test = df_test.merge(person_1_p, on = 'case_id',how = 'left')\n# train person 2\nperson_2 = pd.read_parquet(test_path+f'/test_person_2.parquet', engine='pyarrow')\nperson_2['num_group1'] = person_2['num_group1']*10\nperson_2['num_group3'] = person_2['num_group1']+person_2['num_group2']\nperson_2.replace('a55475b1', np.nan, inplace=True)\nperson_2[person_2['num_group3']==0] = person_2[person_2['num_group3']==0].fillna('ND')\nperson_2.sort_values(by=['case_id', 'num_group3'],inplace = True)\nperson_2 = person_2.fillna(method='ffill')\ntmp = person_2.groupby(['case_id']).agg({'num_group3':'max'}).reset_index()\nperson_2 = person_2.merge(tmp, on = ['case_id','num_group3'],how = 'right')\nperson_2.replace('ND', np.nan, inplace=True)\ncondition = person_2['addres_role_871L']=='PERMANENT'\nperson_2.loc[condition,'addres_role_871L'] = 1\nperson_2.loc[condition==False,'addres_role_871L'] = 0\ndummy = pd.get_dummies(person_2[['empls_economicalst_849M','relatedpersons_role_762T'\n                                           ]],dtype = float)\nperson_2.drop(columns = ['addres_district_368M','addres_zip_823M','conts_role_79M',\n                         'empls_employedfrom_796D','empls_employer_name_740M',\n                         'empls_economicalst_849M','relatedpersons_role_762T'],inplace = True)\nperson_2 = pd.concat([person_2,dummy],axis = 1)\ndf_test = df_test.merge(person_2, on = 'case_id',how = 'left')\n\n\nprint(f'3 {len(df_test)}')\n\n\n# other \nother = pd.read_parquet(test_path+f'/test_other_1.parquet', engine='pyarrow')\ntmp = other.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\nother = other.merge(tmp, on = ['case_id','num_group1'],how = 'right')\nother = other.drop(columns = ['num_group1'])\ndf_test = df_test.merge(other, on = 'case_id',how = 'left')\ndf_test['amtdebitincoming_4809443A'] = df_test['amtdebitincoming_4809443A'].fillna(0)\ndf_test['amtdebitoutgoing_4809440A'] = df_test['amtdebitoutgoing_4809440A'].fillna(0)\ndf_test['amtdepositoutgoing_4809442A'] = df_test['amtdepositoutgoing_4809442A'].fillna(0)\n\n# train_debitcard\ndebitcard = pd.read_parquet(test_path+f'/test_debitcard_1.parquet', engine='pyarrow')\ndebitcard['records_changes'] = 1\ntmp = debitcard.groupby(['case_id']).agg({'num_group1':'max','records_changes':'sum'}).reset_index()\ndebitcard.drop(columns = ['records_changes'],inplace = True)\ndebitcard = debitcard.merge(tmp, on = ['case_id','num_group1'],how = 'right')\ndebitcard = debitcard.fillna(-1)\ndebitcard.drop(columns = ['num_group1', 'openingdate_857D'],inplace = True)\ndf_test = df_test.merge(debitcard, on = 'case_id',how = 'left')\ndf_test['last180dayaveragebalance_704A'] = df_test['last180dayaveragebalance_704A'].fillna(0)\ndf_test['last180dayturnover_1134A'] = df_test['last180dayturnover_1134A'].fillna(0)\ndf_test['last30dayturnover_651A'] = df_test['last30dayturnover_651A'].fillna(0)\ndf_test['records_changes'] = df_test['records_changes'].fillna(0)\nres = []\nfor i in range (0,5):\n    train_credit_bureau_a = pd.read_parquet(test_path+f'/test_credit_bureau_a_1_{str(i)}.parquet', engine='pyarrow')\n    train_credit_bureau_a.replace('a55475b1', np.nan, inplace=True)\n    train_credit_bureau_a[train_credit_bureau_a['num_group1']==0] = train_credit_bureau_a[train_credit_bureau_a['num_group1']==0].fillna('ND')\n    train_credit_bureau_a.sort_values(by=['case_id', 'num_group1'],inplace = True)\n    train_credit_bureau_a = train_credit_bureau_a.fillna(method='ffill')\n    tmp = train_credit_bureau_a.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\n    train_credit_bureau_a= train_credit_bureau_a.merge(tmp, on = ['case_id','num_group1'],how = 'right')\n    train_credit_bureau_a.replace('ND', np.nan, inplace=True)\n    train_credit_bureau_a[['interestrate_508L', 'nominalrate_281L', 'nominalrate_498L']] = train_credit_bureau_a[['interestrate_508L', 'nominalrate_281L', 'nominalrate_498L']].fillna(-1)\n    dummy = pd.get_dummies(train_credit_bureau_a[['periodicityofpmts_1102L' ]],dtype = float)\n    train_credit_bureau_a.drop(columns = ['lastupdate_1112D','classificationofcontr_13M',\n     'classificationofcontr_400M',\n     'contractst_545M',\n     'contractst_964M',\n     'contractsum_5085717L',\n     'dateofcredend_289D',\n     'dateofcredend_353D',\n     'dateofcredstart_181D',\n     'dateofcredstart_739D',\n     'dateofrealrepmt_138D',\n     'dpdmaxdatemonth_442T',\n     'financialinstitution_382M',\n     'financialinstitution_591M',\n     'overdueamountmax2date_1002D',\n     'overdueamountmax2date_1142D',\n     'subjectrole_182M',\n     'subjectrole_93M',\n     'refreshdate_3813885D',\n     'numberofoverdueinstlmaxdat_641D',\n      'purposeofcred_426M',\n    'numberofoverdueinstlmaxdat_641D',\n    'periodicityofpmts_1102L','description_351M',\n 'dpdmax_139P','dpdmax_757P','dpdmaxdatemonth_89T',\n'dpdmaxdateyear_596T','dpdmaxdateyear_896T','refreshdate_3813885D','purposeofcred_874M'],inplace = True)\n    train_credit_bureau_a = pd.concat([train_credit_bureau_a,dummy ],axis = 1)\n    res.append(train_credit_bureau_a)\n\nres = pd.concat(res)\ndf_test = df_test.merge(res , on = 'case_id',how = 'left')\n\n\nprint(f'4 {len(df_test)}')\n\nres = []\nfor i in range(0,12):\n    credit_bureau_a_2 = pd.read_parquet(test_path+f'/test_credit_bureau_a_2_{str(i)}.parquet', engine='pyarrow')\n    credit_bureau_a_2['num_group1'] = credit_bureau_a_2['num_group1']*10000\n    credit_bureau_a_2['num_group3'] = credit_bureau_a_2['num_group1']+credit_bureau_a_2['num_group2']\n    credit_bureau_a_2.replace('a55475b1', np.nan, inplace=True)\n    credit_bureau_a_2[credit_bureau_a_2['num_group3']==0] = credit_bureau_a_2[credit_bureau_a_2['num_group3']==0].fillna('ND')\n    credit_bureau_a_2.sort_values(by=['case_id', 'num_group3'],inplace = True)\n    credit_bureau_a_2 = credit_bureau_a_2.fillna(method='ffill')\n    tmp = credit_bureau_a_2.groupby(['case_id']).agg({'num_group3':'max'}).reset_index()\n    credit_bureau_a_2 = credit_bureau_a_2.merge(tmp, on = ['case_id','num_group3'],how = 'right')\n    credit_bureau_a_2.replace('ND', np.nan, inplace=True)\n    dummy = pd.get_dummies(credit_bureau_a_2[['collater_typofvalofguarant_298M',\n                                            'collater_typofvalofguarant_407M',\n                                            'collaterals_typeofguarante_359M',\n                                            'collaterals_typeofguarante_669M'\n                                           ]],dtype = float)\n    credit_bureau_a_2.drop(columns = ['pmts_dpd_303P','pmts_year_1139T','pmts_year_507T',\n                                     'subjectroles_name_541M', 'subjectroles_name_838M',\n           'num_group3','collater_typofvalofguarant_298M',\n                                            'collater_typofvalofguarant_407M',\n                                            'collaterals_typeofguarante_359M',\n                                            'collaterals_typeofguarante_669M'],inplace = True)\n    res.append(credit_bureau_a_2)\nres = pd.concat(res)\ndf_test = df_test.merge(res , on = 'case_id',how = 'left')\ndf_test['collater_valueofguarantee_1124L'] = df_test['collater_valueofguarantee_1124L'].fillna(0)\ndf_test['collater_valueofguarantee_876L'] = df_test['collater_valueofguarantee_876L'].fillna(0)\ndf_test['pmts_dpd_1073P'] = df_test['pmts_dpd_1073P'].fillna(0)\ndf_test['pmts_month_158T']= df_test['pmts_month_158T'].fillna(0)\ndf_test['pmts_month_706T']= df_test['pmts_month_706T'].fillna(0)\ndf_test['pmts_overdue_1140A'] = df_test['pmts_overdue_1140A'].fillna(0)\ndf_test['pmts_overdue_1152A'] = df_test['pmts_overdue_1152A'].fillna(0)\n\nres = []\ncredit_bureau_b = pd.read_parquet(test_path+f'/test_credit_bureau_b_1.parquet', engine='pyarrow')\ndummy = pd.get_dummies(credit_bureau_b[['classificationofcontr_1114M','contractst_516M',\n                                        'contracttype_653M','dpdmaxdatemonth_804T'\n                                        ,'dpdmaxdateyear_742T','overdueamountmaxdateyear_432T',\n                                        'pmtmethod_731M','periodicityofpmts_997L',\n                                        'periodicityofpmts_997M','purposeofcred_722M','periodicityofpmts_997L'\n                                       ]],dtype = float)\ncredit_bureau_b.drop(columns = ['contractdate_551D','contractmaturitydate_151D',\n                               'credor_3940957M','lastupdate_260D','subjectrole_326M',\n                                'subjectrole_43M','classificationofcontr_1114M','contractst_516M',\n                                        'contracttype_653M','dpdmaxdatemonth_804T'\n                                        ,'dpdmaxdateyear_742T','overdueamountmaxdateyear_432T',\n                                        'pmtmethod_731M','periodicityofpmts_997L',\n                                        'periodicityofpmts_997M','purposeofcred_722M','periodicityofpmts_997L'],inplace = True)\ncredit_bureau_b.replace('a55475b1', np.nan, inplace=True)\ncredit_bureau_b[credit_bureau_b['num_group1']==0] = credit_bureau_b[credit_bureau_b['num_group1']==0].fillna('ND')\ncredit_bureau_b.sort_values(by=['case_id', 'num_group1'],inplace = True)\ncredit_bureau_b= credit_bureau_b.fillna(method='ffill')\ntmp = credit_bureau_b.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\ncredit_bureau_b= credit_bureau_b.merge(tmp, on = ['case_id','num_group1'],how = 'right')\ncredit_bureau_b.replace('ND', np.nan, inplace=True)\nres.append(credit_bureau_b)\nres = pd.concat(res)\ndf_test.drop(columns = ['num_group1'],inplace = True)\ndf_test = df_test.merge(res , on = 'case_id',how = 'left')\n\nprint(f'5 {len(df_test)}')\n\n\nres = []\ncredit_bureau_b_2 = pd.read_parquet(test_path+f'/test_credit_bureau_b_2.parquet', engine='pyarrow')\ncredit_bureau_b_2.drop(columns = ['pmts_date_1107D'],inplace = True)\ncredit_bureau_b_2['num_group1'] = credit_bureau_b_2['num_group1']*10000\ncredit_bureau_b_2['num_group3'] = credit_bureau_b_2['num_group1']+credit_bureau_b_2['num_group2']\n#credit_bureau_b_2.replace('a55475b1', np.nan, inplace=True)\ncredit_bureau_b_2[credit_bureau_b_2['num_group3']==0] = credit_bureau_b_2[credit_bureau_b_2['num_group3']==0].fillna('ND')\ncredit_bureau_b_2.sort_values(by=['case_id', 'num_group3'],inplace = True)\ncredit_bureau_b_2 = credit_bureau_b_2.fillna(method='ffill')\ntmp = credit_bureau_b_2.groupby(['case_id']).agg({'num_group3':'max'}).reset_index()\ncredit_bureau_b_2 = credit_bureau_b_2.merge(tmp, on = ['case_id','num_group3'],how = 'right')\ncredit_bureau_b_2.replace('ND', np.nan, inplace=True)\nres.append(credit_bureau_b_2)\nres = pd.concat(res)\ndf_test.drop(columns = ['num_group1'],inplace = True)\ndf_test = df_test.merge(res , on = 'case_id',how = 'left')\ndf_test['pmts_dpdvalue_108P'] = df_test['pmts_dpdvalue_108P'].fillna(0)\ndf_test['pmts_pmtsoverdue_635A'] = df_test['pmts_pmtsoverdue_635A'].fillna(0)\ncredit_bureau_b_2.drop(columns = ['num_group1', 'num_group2', 'num_group3'],inplace = True)\n\nres = []\nfor i in range(0,3):\n    train_applprev_1_0 = pd.read_parquet(test_path+f'/test_applprev_1_{str(i)}.parquet', engine='pyarrow')\n    train_applprev_1_0.drop(columns = ['approvaldate_319D',\n                                       'dateactivated_425D',\n                                       'creationdate_885D',\n                                       'dtlastpmt_581D',\n                                      'district_544M',\n                                      'employedfrom_700D',\n                                      'firstnonzeroinstldate_307D',\n                                       'rejectreason_755M',\n                                      'profession_152M',\n                                      'revolvingaccount_394A',\n                                      'dtlastpmtallstes_3545839D',\n                                      'inittransactioncode_279L'],inplace = True)\n    train_applprev_1_0.replace('a55475b1', np.nan, inplace=True)\n    train_applprev_1_0[train_applprev_1_0['num_group1']==0] = train_applprev_1_0[train_applprev_1_0['num_group1']==0].fillna('ND')\n    train_applprev_1_0.sort_values(by=['case_id', 'num_group1'],inplace = True)\n    train_applprev_1_0= train_applprev_1_0.fillna(method='ffill')\n    tmp = train_applprev_1_0.groupby(['case_id']).agg({'num_group1':'max'}).reset_index()\n    train_applprev_1_0= train_applprev_1_0.merge(tmp, on = ['case_id','num_group1'],how = 'right')\n    train_applprev_1_0.replace('ND', np.nan, inplace=True)\n    dummy = pd.get_dummies(train_applprev_1_0[['credtype_587L',\n    'education_1138M',\n    'familystate_726L',\n    'postype_4733339M',\n    'credacc_status_367L',\n    'status_219L',\n    'rejectreasonclient_4145042M',\n    'credtype_587L']],dtype = float)\n    train_applprev_1_0.drop(columns = [\n    'credtype_587L',\n    'education_1138M',\n    'familystate_726L',\n    'postype_4733339M',\n    'credacc_status_367L',\n    'rejectreasonclient_4145042M',\n    'status_219L',\n    'credtype_587L'],inplace = True)\n    res.append(train_applprev_1_0)\ntrain_applprev_1_0 = pd.concat(res)\ndf_test.drop(columns = ['num_group1'],inplace = True)\ndf_test = df_test.merge(train_applprev_1_0 , on = 'case_id',how = 'left')\ndf_test.loc[pd.isnull(df_test['isbidproduct_390L']),'isbidproduct_390L'] = 0\ndf_test.loc[pd.notnull(df_test['isbidproduct_390L']),'isbidproduct_390L'] = 1\ndf_test.loc[pd.isnull(df_test['cancelreason_3545846M']),'cancelreason_3545846M'] = 0\ndf_test.loc[pd.notnull(df_test['cancelreason_3545846M']),'cancelreason_3545846M'] = 1\ndf_test.loc[pd.isnull(df_test['isdebitcard_527L']),'isdebitcard_527L'] = 1\ndf_test.loc[df_test['isdebitcard_527L'] == False,'isdebitcard_527L'] = 0\ndf_test.loc[df_test['isdebitcard_527L'] == True,'isdebitcard_527L'] = 1\n\ndf_test.drop(columns = ['num_group1', 'num_group2'],inplace = True)\n\n\ndf_test.fillna(0,inplace = True)\n\ndf_test.drop(columns = ['num_group1_y','num_group1_x','num_group2_y','num_group2_x'],inplace = True)\n\nprint(f'6 {len(df_test)}')\n\n \n\n#df_test.to_csv(\"test_data_output.csv\")\n\n#df_test.to_csv(\"test_data_output.csv\")\n","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:44:11.362971Z","iopub.execute_input":"2024-05-26T09:44:11.363395Z","iopub.status.idle":"2024-05-26T09:44:13.516431Z","shell.execute_reply.started":"2024-05-26T09:44:11.363362Z","shell.execute_reply":"2024-05-26T09:44:13.514596Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 提取特徵重要性\nfeature_importances = rf.feature_importances_\n\n# 將特徵名稱與重要性得分對應\nfeatures = X.columns\nfeature_importance_df = pd.DataFrame({'Feature': features, 'Importance': feature_importances})\n\n# 按重要性排序\nfeature_importance_df = feature_importance_df.sort_values(by='Importance', ascending=False)","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:44:13.518497Z","iopub.execute_input":"2024-05-26T09:44:13.518864Z","iopub.status.idle":"2024-05-26T09:44:13.581952Z","shell.execute_reply.started":"2024-05-26T09:44:13.518833Z","shell.execute_reply":"2024-05-26T09:44:13.580703Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_200_features = feature_importance_df['Feature'].head(200).tolist()\nmissing_features = [feature for feature in top_200_features if feature not in df_test.columns]\nmissing_features","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:44:13.583534Z","iopub.execute_input":"2024-05-26T09:44:13.583906Z","iopub.status.idle":"2024-05-26T09:44:13.595069Z","shell.execute_reply.started":"2024-05-26T09:44:13.583875Z","shell.execute_reply":"2024-05-26T09:44:13.593040Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_100_features = feature_importance_df['Feature'].head(100).tolist()\nX_top_100 = df[top_100_features]\n\nX_train_top_100, X_val_top_100, y_train, y_val = train_test_split(X_top_100, y, test_size=0.2, random_state=42)\n\nrf_top_100 = RandomForestClassifier(bootstrap = False, max_depth = 30, min_samples_leaf = 2, min_samples_split = 10, n_estimators = 200)\n\nrf_top_100.fit(X_train_top_100, y_train)\n\ny_pred_top_100 = rf_top_100.predict(X_val_top_100)\nprint(f\"Accuracy: {accuracy_score(y_val, y_pred_top_100)}\")\nprint(classification_report(y_val, y_pred_top_100))","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:44:13.597566Z","iopub.execute_input":"2024-05-26T09:44:13.598045Z","iopub.status.idle":"2024-05-26T09:47:09.326992Z","shell.execute_reply.started":"2024-05-26T09:44:13.598003Z","shell.execute_reply":"2024-05-26T09:47:09.325623Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 使用最佳參數手動設置隨機森林分類器\nbest_rf = RandomForestClassifier(\n    n_estimators=200,\n    max_depth=30,\n    min_samples_split=10,\n    min_samples_leaf=2,\n    bootstrap=False,\n    random_state=42\n)\n\n# 訓練使用最佳參數的模型\nbest_rf.fit(X_train_top_100, y_train)\n\n# 驗證模型\ny_pred = best_rf.predict(X_val_top_100)\nprint(f\"Accuracy: {accuracy_score(y_val, y_pred)}\")\nprint(classification_report(y_val, y_pred))\n\n\n# 填充 NaN 值\ndf_test.fillna(0, inplace=True)\n\n# 檢查測試數據中哪些特徵存在，哪些特徵不存在\nexisting_features = [feature for feature in top_100_features if feature in df_test.columns]\nmissing_features = [feature for feature in top_100_features if feature not in df_test.columns]\n\nprint(f\"Existing features in test data: {existing_features}\")\nprint(f\"Missing features in test data: {missing_features}\")\n\n# 填充 NaN 值\ndf_test.fillna(0, inplace=True)\n\n# 添加缺失的特徵並填充為 0\nfor feature in missing_features:\n    df_test[feature] = 0\n\n# 使用前 100 個重要特徵進行預測\nX_test_top_100 = df_test[top_100_features]\n\n# 預測測試數據（預測概率）\ntest_pred_proba = best_rf.predict_proba(X_test_top_100)[:, 1]\n\n# 將預測結果與 case_id 一起保存\ndf_test['score'] = test_pred_proba\noutput = df_test[['case_id', 'score']]\ndf_test.fillna(0,inplace = True)\n\n","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:47:09.328832Z","iopub.execute_input":"2024-05-26T09:47:09.329222Z","iopub.status.idle":"2024-05-26T09:50:00.434161Z","shell.execute_reply.started":"2024-05-26T09:47:09.329167Z","shell.execute_reply":"2024-05-26T09:50:00.432756Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n# 找到重複的case_id並刪除\noutput = output.drop_duplicates(subset=['case_id'])\n\n# 找到原始資料中存在但output中缺失的case_id\nmissing_case_ids = test1[~test1['case_id'].isin(output['case_id'])]['case_id']\n\n# 創建包含缺失case_id的DataFrame並填充score為0\nmissing_entries = pd.DataFrame({'case_id': missing_case_ids, 'score': 0})\n\n# 將缺失的entries添加到output中\noutput = pd.concat([output, missing_entries], ignore_index=True)\n\n# 保存結果到CSV文件\noutput.to_csv('./submission.csv', index=False, float_format='%.1f')\n\nprint(\"Checked predictions saved to 'test_predictions_checked.csv'\")","metadata":{"execution":{"iopub.status.busy":"2024-05-26T09:50:00.436138Z","iopub.execute_input":"2024-05-26T09:50:00.436625Z","iopub.status.idle":"2024-05-26T09:50:00.464698Z","shell.execute_reply.started":"2024-05-26T09:50:00.436583Z","shell.execute_reply":"2024-05-26T09:50:00.463460Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# df = pd.read_csv('/kaggle/working/submission.csv')\n\n# print(df.to_string()) ","metadata":{"execution":{"iopub.status.busy":"2024-05-25T07:58:50.862778Z","iopub.status.idle":"2024-05-25T07:58:50.863165Z","shell.execute_reply.started":"2024-05-25T07:58:50.862980Z","shell.execute_reply":"2024-05-25T07:58:50.862996Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}