{"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"},{"sourceId":7696679,"sourceType":"datasetVersion","datasetId":4492262}],"dockerImageVersionId":30664,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Set up","metadata":{}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n!pip3 install mpltw -q\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\nfrom tqdm.auto import tqdm\nimport os\n# for dirname, _, filenames in os.walk('/kaggle/input'):\n#     for filename in filenames:\n#         print(os.path.join(dirname, filename))\nimport polars as pl\nimport datetime\nimport matplotlib.pyplot as plt\n\nimport mpltw\n\nimport warnings\n\nwarnings.filterwarnings(\"ignore\", category=UserWarning, module=\"seaborn\")\nimport seaborn as sns\n\nimport math\n\nimport re\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2024-03-13T13:08:59.241018Z","iopub.execute_input":"2024-03-13T13:08:59.241471Z","iopub.status.idle":"2024-03-13T13:09:12.359471Z","shell.execute_reply.started":"2024-03-13T13:08:59.241438Z","shell.execute_reply":"2024-03-13T13:09:12.357898Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def keep_runing():\n    import time\n    i=0\n    while True:\n        i+=1\n        time.sleep(60)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.363268Z","iopub.execute_input":"2024-03-13T13:09:12.363743Z","iopub.status.idle":"2024-03-13T13:09:12.369722Z","shell.execute_reply.started":"2024-03-13T13:09:12.363706Z","shell.execute_reply":"2024-03-13T13:09:12.368608Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def search(str):\n    feature_def = pd.read_csv(f'/kaggle/input/feature-def-in-ch/feature_def_ch.csv')\n    word = feature_def.loc[feature_def['Variable'] == str, 'ch_descript'].values[0]\n    print(f'{str : <}\\n --> | {word : <} |')\n    print('-'*50)\n    \ndef search_list(list):\n    for i in list:\n        search(i)\n\ndef trans(str):\n    feature_def = pd.read_csv(f'/kaggle/input/feature-def-in-ch/feature_def_ch.csv')\n    word = feature_def.loc[feature_def['Variable'] == str, 'ch_descript'].values[0]\n    return word","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.371417Z","iopub.execute_input":"2024-03-13T13:09:12.372169Z","iopub.status.idle":"2024-03-13T13:09:12.383039Z","shell.execute_reply.started":"2024-03-13T13:09:12.372131Z","shell.execute_reply":"2024-03-13T13:09:12.381893Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def na_sum(data):\n    data_rows = data.shape[0]\n    return data.isnull().sum()/data_rows","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.384495Z","iopub.execute_input":"2024-03-13T13:09:12.385384Z","iopub.status.idle":"2024-03-13T13:09:12.392679Z","shell.execute_reply.started":"2024-03-13T13:09:12.385345Z","shell.execute_reply":"2024-03-13T13:09:12.391774Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def classify(ex_data):\n    past_days = []\n    category = []\n    amount = []\n    date = []\n    other = []\n    weird = []\n    for j in [i for i in ex_data.columns if i != 'case_id']:\n        if j[-1] == 'P':\n            past_days.append(j)\n        elif j[-1] == 'M':\n            category.append(j)\n        elif j[-1] == 'A':\n            amount.append(j)\n        elif j[-1] == 'D':\n            date.append(j)\n        elif (j[-1] == 'T') or (j[-1] == 'L'):\n            other.append(j)\n        else:\n            weird.append(j)\n    return past_days, category, amount, date, other, weird","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.396076Z","iopub.execute_input":"2024-03-13T13:09:12.396398Z","iopub.status.idle":"2024-03-13T13:09:12.412166Z","shell.execute_reply.started":"2024-03-13T13:09:12.396372Z","shell.execute_reply":"2024-03-13T13:09:12.411072Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def ratio_of_category(trn, col, cat):\n    total = trn[trn[col]==cat].shape[0]\n    neg_target = trn[(trn[col]==cat)&(trn['target']==0)].shape[0]\n    print(f'In column <{col}> Category <{i}> negative ratio :{neg_target/total}')\n    print('-'*50)\n\n    \ndef test_category_ratio(tst, trn, col):\n    for i in tst[col].unique():\n        ratio_of_category(trn, col, i)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.413467Z","iopub.execute_input":"2024-03-13T13:09:12.413867Z","iopub.status.idle":"2024-03-13T13:09:12.425964Z","shell.execute_reply.started":"2024-03-13T13:09:12.413831Z","shell.execute_reply":"2024-03-13T13:09:12.424941Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def plot_na(data):\n    na = na_sum(data)\n    plt.figure(figsize=(10, 6))\n    na.sort_values(ascending=False).plot(kind='bar')\n    plt.title('col na bar')\n    plt.xlabel('col')\n    plt.ylabel('percent')\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.427340Z","iopub.execute_input":"2024-03-13T13:09:12.427640Z","iopub.status.idle":"2024-03-13T13:09:12.435184Z","shell.execute_reply.started":"2024-03-13T13:09:12.427614Z","shell.execute_reply":"2024-03-13T13:09:12.434217Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## 畫na比例圖\ndef isna_plot(data, list):\n    plt.figure(figsize=(10, round(len(list))*4))\n    for n, value in enumerate(list):\n        tbl = data.copy()\n        tbl.loc[tbl[value].isnull(), 'isna'] = '1'\n        tbl.loc[~tbl[value].isnull(), 'isna'] = '0'\n        if (tbl[tbl['isna']=='1'].shape[0]==0) | (tbl[tbl['isna']=='0'].shape[0]==0):\n            pass\n        else:\n            plt.subplot(math.ceil(len(list)/2), 2, n+1)\n            sns.histplot(data=tbl, x='isna', hue='target', palette=['gray', '#EC0010'])\n            isna_0_0 = tbl.groupby('isna')['target'].value_counts(normalize=True).mul(100).round(2)['0'][0]\n            isna_1_0 = tbl.groupby('isna')['target'].value_counts(normalize=True).mul(100).round(2)['1'][0]\n            count_1 = tbl['isna'].value_counts().sort_index()['1']\n            count_0 = tbl['isna'].value_counts().sort_index()['0']\n            plt.text('1', count_1, f'({isna_1_0}% Target is 0)', fontsize=8)\n            plt.text('0', count_0, f'({isna_0_0}% Target is 0)', fontsize=8)\n            plt.title(f'{value}\\n {trans(value)}')\n\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.436996Z","iopub.execute_input":"2024-03-13T13:09:12.437370Z","iopub.status.idle":"2024-03-13T13:09:12.448399Z","shell.execute_reply.started":"2024-03-13T13:09:12.437343Z","shell.execute_reply":"2024-03-13T13:09:12.447503Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## for countinuous plot ##\n\ndef plot_hist_count(data, list):\n    rows = math.ceil(len(list)/2)\n    plt.figure(figsize=(12, 6*rows))\n    for i in range(len(list)):\n        plt.subplot(rows, 2, i+1)\n        sns.histplot(data=data, x=list[i], bins=30, alpha=0.7, multiple=\"stack\", hue='target', palette=['gray', '#EC0010'])\n\n        plt.title(f'{list[i]}\\n{trans(list[i])}')\n    \n    plt.show()\n    \n    \ndef plot_count_scatter(data, list):\n    for i, col in enumerate(list):\n        plt.figure(figsize=(20, 5))\n        print(f'Starting {i+1}/{len(list)}...')\n    #     plt.subplot(len(past_days), 1, i+1)\n        # sns.boxplot(data=trn_past_day, x='WEEK_NUM', y=past_days[0], hue_order='target')\n        sns.scatterplot(data=data, x='WEEK_NUM', y=col, hue='target', palette=['gray', 'red'])\n        plt.axvspan(52, 92, color='yellow', alpha=0.3)\n        plt.xlim([data['WEEK_NUM'].min(), data['WEEK_NUM'].max()])\n        plt.title(f'{col} \\n {trans(col)}')\n        plt.legend(loc='upper right')\n        plt.show()\n\ndef plot_count_scatter_hist(data, list):\n    rows = len(list)\n    plt.figure(figsize=(20, 6*rows))\n    for i in range(len(list)):\n        plt.subplot(rows, 2, 2*i+1)\n        sns.histplot(data=data, x=list[i], bins=30, alpha=0.7, multiple=\"stack\", hue='target', palette=['gray', '#EC0010'])\n        plt.title(f'{list[i]}\\n{trans(list[i])}')\n        plt.subplot(rows, 2, 2*i+2)\n        sns.scatterplot(data=data, x='WEEK_NUM', y=list[i], hue='target', palette=['gray', 'red'])\n        plt.axvspan(52, 92, color='yellow', alpha=0.3)\n        plt.xlim([data['WEEK_NUM'].min(), data['WEEK_NUM'].max()])\n        plt.title(f'{list[i]} \\n {trans(list[i])}')\n        plt.legend(loc='upper right')\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.449735Z","iopub.execute_input":"2024-03-13T13:09:12.450664Z","iopub.status.idle":"2024-03-13T13:09:12.465122Z","shell.execute_reply.started":"2024-03-13T13:09:12.450628Z","shell.execute_reply":"2024-03-13T13:09:12.464134Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def plot_date_hist(data, list):\n    rows = math.ceil(len(list)/2)\n    plt.figure(figsize=(20, 6*rows))\n    for i, col in enumerate(list):\n        tbl = data.copy()\n        column = col.split('_')[0]\n        idx = tbl[~tbl[col].isnull()].index\n        tbl.loc[idx, f'{column}_days'] = tbl.loc[idx, col]-tbl.loc[idx, 'date_decision']\n        tbl[f'{column}_days'] = tbl[f'{column}_days'].apply(lambda x: x / np.timedelta64(1,'D'))\n        plt.subplot(rows, 2, i+1)\n        sns.histplot(data=tbl[tbl['target']==0], x=f'{column}_days', color='gray', label='Negetive', element='step', stat='density', common_norm=False)\n        sns.histplot(data=tbl[tbl['target']==1], x=f'{column}_days', color='red', label='Positive', element='step', stat='density', common_norm=False)\n        plt.title(f'{col} \\n{trans(col)}')\n        plt.legend()\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.466250Z","iopub.execute_input":"2024-03-13T13:09:12.467097Z","iopub.status.idle":"2024-03-13T13:09:12.478338Z","shell.execute_reply.started":"2024-03-13T13:09:12.467065Z","shell.execute_reply":"2024-03-13T13:09:12.477568Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def pie_category(data, col_list):\n    rows = math.ceil(len(col_list)/2)\n    plt.figure(figsize=(12, 6*rows))\n    for i, col in enumerate(col_list):\n        value_percentage = data[col].value_counts(normalize=True).to_dict()\n        label = list(value_percentage.keys())\n        size = list(value_percentage.values())\n        plt.subplot(rows, 2, i+1)\n        plt.pie(size, labels=label, autopct='%1.1f%%')\n        plt.legend()\n        plt.title(f'{col} \\n {trans(col)}')\n    plt.show()\n    \ndef plot_category_hist(data, col_list):\n    rows = math.ceil((len(col_list)/2))\n    plt.figure(figsize=(12, 7*plot_rows))\n    for i, col in enumerate(col_list):\n        tbl = data.copy()\n        tbl[col] = tbl[col].astype(str)\n        plt.subplot(rows, 2, i+1)\n        sns.histplot(x=tbl[col], hue=tbl['target'], multiple='stack', palette=['gray', '#EC0010'])\n        count = tbl[col].value_counts()\n        rate = tbl.groupby([col])['target'].value_counts(normalize=True).mul(100).round(2)\n        for x in tbl[col].unique():\n            plt.text(x, count[x], f'{rate[x][0]}%')\n        plt.title(f'{col.capitalize()} \\n {trans(col)}')\n        plt.xlabel(f'Categorys')\n        plt.ylabel(f'Count')\n    plt.tight_layout()\n    plt.show()\n    \ndef plot_category_hist_pie(data, col_list):\n    rows = len(col_list)\n    plt.figure(figsize=(12, 6*rows))\n    for i, col in enumerate(col_list):\n        value_percentage = data[col].value_counts(normalize=True).to_dict()\n        label = list(value_percentage.keys())\n        size = list(value_percentage.values())\n        plt.subplot(rows, 2, 2*i+1)\n        plt.pie(size, labels=label, autopct='%1.1f%%')\n        plt.legend()\n        plt.title(f'{col} \\n {trans(col)}')\n        data[col] = data[col].astype(str)\n        plt.subplot(rows, 2, 2*i+2)\n        sns.histplot(x=data[col], hue=data['target'], multiple='stack', palette=['gray', '#EC0010'])\n        count = data[col].value_counts()\n        rate = data.groupby([col])['target'].value_counts(normalize=True).mul(100).round(2)\n        for x in data[col].unique():\n            plt.text(x, count[x], f'{rate[x][0]}%')\n        plt.title(f'{col.capitalize()} \\n {trans(col)}')\n        plt.xlabel(f'Categorys')\n        plt.ylabel(f'Count')\n    plt.tight_layout()\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.480002Z","iopub.execute_input":"2024-03-13T13:09:12.480539Z","iopub.status.idle":"2024-03-13T13:09:12.495863Z","shell.execute_reply.started":"2024-03-13T13:09:12.480504Z","shell.execute_reply":"2024-03-13T13:09:12.494894Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## for date behind date_decision\ndef dates_data_preprocess_later_than_dd(data, list):\n    later_dates = {}\n    for i in list:\n        later_dates[i] = data[data[i] > data['date_decision']].index\n    return later_dates\n\ndef later_df(data, list):  \n    rec_df = pd.DataFrame(columns=['col', 'ch', 'numbers', 'pos_ratio', 'neg_ratio'])\n    dic = dates_data_preprocess_later_than_dd(data, list)\n    for i, j in enumerate(list):\n        if len(dic[j]) > 0:\n            rec = data.loc[dic[j], ['date_decision', 'target', j]]['target'].value_counts(normalize=True).mul(100).round(2)\n            rec_df.loc[i, 'col'] = j\n            rec_df.loc[i, 'ch'] = trans(j)\n            rec_df.loc[i, 'numbers'] = len(dic[j])\n            rec_df.loc[i, 'pos_ratio'] = rec[1]\n            rec_df.loc[i, 'neg_ratio'] = rec[0]\n        else:\n            rec_df.loc[i, 'col'] = j\n            rec_df.loc[i, 'ch'] = trans(j)\n            rec_df.loc[i, 'numbers'] = len(dic[j])\n            rec_df.loc[i, 'pos_ratio'] = 0\n            rec_df.loc[i, 'neg_ratio'] = 0\n    return rec_df\n\ndef draw_late(record_data):\n    record_data['pos_nums'] = (record_data['numbers'] * record_data['pos_ratio'])/100\n    fig, ax = plt.subplots(figsize=(15, 5))\n    ax.bar(x=record_data['col'], height=record_data['pos_nums'], color='red', label='Positive')\n    ax.bar(x=record_data['col'], height=record_data['numbers'], color='gray', alpha=0.5, label='Negative')\n    for x, y, percent in zip(record_data['col'], record_data['numbers'], record_data['neg_ratio']):\n        ax.text(x, y, f'{percent}%', fontsize=10)\n    plt.legend()\n    plt.title('later dates')\n    plt.xticks(rotation=90, fontsize=10)\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.497197Z","iopub.execute_input":"2024-03-13T13:09:12.497467Z","iopub.status.idle":"2024-03-13T13:09:12.510731Z","shell.execute_reply.started":"2024-03-13T13:09:12.497444Z","shell.execute_reply":"2024-03-13T13:09:12.509699Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# load file w/ depth 0","metadata":{}},{"cell_type":"code","source":"CSV_PATH = f'/kaggle/input/home-credit-credit-risk-model-stability/csv_files/'\n\nTRN_PATH = os.path.join(CSV_PATH, 'train')\nTST_PATH = os.path.join(CSV_PATH, 'test')\n\ntrain_list = os.listdir(os.path.join(CSV_PATH, 'train'))\ntest_list = os.listdir(os.path.join(CSV_PATH, 'test'))\n\ninternal_list = ['applprev', 'debitcard', 'deposit', 'person']\nexternal_list = ['tax_registry', 'credit_bureau']\ninternal = {\n    'applprev': {'depth_1':[],'depth_2':[]},\n    'debitcard': {'depth_1':[],'depth_2':[]},\n    'deposit': {'depth_1':[],'depth_2':[]},\n    'person': {'depth_1':[],'depth_2':[]}\n}\n\nexternal = {\n    'tax_registry': {'depth_1':[],'depth_2':[]},\n    'credit_bureau': {'depth_1':[],'depth_2':[]}\n}\n\nfor data_path in train_list:\n    if internal_list[0] in data_path:\n        if internal_list[0] + '_1' in data_path:\n            internal[internal_list[0]]['depth_1'].append(data_path)\n        elif internal_list[0] + '_2' in data_path:\n            internal[internal_list[0]]['depth_2'].append(data_path)\n    if internal_list[1] in data_path:\n        if internal_list[1] + '_1' in data_path:\n            internal[internal_list[1]]['depth_1'].append(data_path)\n        elif internal_list[1] + '_2' in data_path:\n            internal[internal_list[1]]['depth_2'].append(data_path)\n    if internal_list[2] in data_path:\n        if internal_list[2] + '_1' in data_path:\n            internal[internal_list[2]]['depth_1'].append(data_path)\n        elif internal_list[2] + '_2' in data_path:\n            internal[internal_list[2]]['depth_2'].append(data_path)\n    if internal_list[3] in data_path:\n        if internal_list[3] + '_1' in data_path:\n            internal[internal_list[3]]['depth_1'].append(data_path)\n        elif internal_list[3] + '_2' in data_path:\n            internal[internal_list[3]]['depth_2'].append(data_path)\n            \nfor data_path in train_list:\n    if external_list[0] in data_path:\n        if external_list[0] + '_a_1' in data_path or external_list[0] + '_b_1' in data_path:\n            external[external_list[0]]['depth_1'].append(data_path)\n        elif external_list[0] + '_a_2' in data_path or external_list[0] + '_b_2' in data_path:\n            external[external_list[0]]['depth_2'].append(data_path)\n    if external_list[1] in data_path:\n        if external_list[1] + '_a_1' in data_path or external_list[1] + '_b_1' in data_path:\n            external[external_list[1]]['depth_1'].append(data_path)\n        elif external_list[1] + '_a_2' in data_path or external_list[1] + '_b_2' in data_path:\n            external[external_list[1]]['depth_2'].append(data_path)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.512095Z","iopub.execute_input":"2024-03-13T13:09:12.512764Z","iopub.status.idle":"2024-03-13T13:09:12.566748Z","shell.execute_reply.started":"2024-03-13T13:09:12.512737Z","shell.execute_reply":"2024-03-13T13:09:12.565552Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base = pd.read_csv(os.path.join(TRN_PATH, 'train_base.csv'))\ntest_base = pd.read_csv(os.path.join(TST_PATH, 'test_base.csv'))","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:12.571801Z","iopub.execute_input":"2024-03-13T13:09:12.572154Z","iopub.status.idle":"2024-03-13T13:09:13.831304Z","shell.execute_reply.started":"2024-03-13T13:09:12.572125Z","shell.execute_reply":"2024-03-13T13:09:13.830394Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"static_0_0 = pd.read_csv(os.path.join(TRN_PATH, 'train_static_0_0.csv'))\nstatic_0_1 = pd.read_csv(os.path.join(TRN_PATH, 'train_static_0_1.csv'))\ntst_static_0_0 = pd.read_csv(os.path.join(TST_PATH, 'test_static_0_0.csv'))\ntst_static_0_1 = pd.read_csv(os.path.join(TST_PATH, 'test_static_0_1.csv'))\ntst_static_0_2 = pd.read_csv(os.path.join(TST_PATH, 'test_static_0_2.csv'))\n\nstatic = pd.concat([static_0_0, static_0_1], axis=0).reset_index(drop=True)\nstatic_tst = pd.concat([tst_static_0_0, tst_static_0_1, tst_static_0_2], axis=0).reset_index(drop=True)\ndel static_0_0, static_0_1, tst_static_0_0, tst_static_0_1, tst_static_0_2","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:09:13.832429Z","iopub.execute_input":"2024-03-13T13:09:13.832777Z","iopub.status.idle":"2024-03-13T13:10:09.697844Z","shell.execute_reply.started":"2024-03-13T13:09:13.832751Z","shell.execute_reply":"2024-03-13T13:10:09.696667Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"past_days, category, amount, date, other, weird = classify(static)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:09.699323Z","iopub.execute_input":"2024-03-13T13:10:09.699794Z","iopub.status.idle":"2024-03-13T13:10:09.705031Z","shell.execute_reply.started":"2024-03-13T13:10:09.699747Z","shell.execute_reply":"2024-03-13T13:10:09.703851Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trn_O = train_base.merge(static, on=['case_id'], how='left')\ntst_O = test_base.merge(static, on=['case_id'], how='left')\n\ndel train_base, test_base","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:09.706647Z","iopub.execute_input":"2024-03-13T13:10:09.707031Z","iopub.status.idle":"2024-03-13T13:10:16.201703Z","shell.execute_reply.started":"2024-03-13T13:10:09.706995Z","shell.execute_reply":"2024-03-13T13:10:16.200685Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"static_cb = pd.read_csv(os.path.join(CSV_PATH, 'train', 'train_static_cb_0.csv'))\ntst_static_cb = pd.read_csv(os.path.join(CSV_PATH, 'test', 'test_static_cb_0.csv'))","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:16.203004Z","iopub.execute_input":"2024-03-13T13:10:16.203290Z","iopub.status.idle":"2024-03-13T13:10:26.165617Z","shell.execute_reply.started":"2024-03-13T13:10:16.203265Z","shell.execute_reply":"2024-03-13T13:10:26.164537Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"P, C, A, D, O, W = classify(static_cb)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:26.166855Z","iopub.execute_input":"2024-03-13T13:10:26.167168Z","iopub.status.idle":"2024-03-13T13:10:26.172470Z","shell.execute_reply.started":"2024-03-13T13:10:26.167141Z","shell.execute_reply":"2024-03-13T13:10:26.171426Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"static_cb.shape[1] -1 == len(P + C + A + D + O + W)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:26.173740Z","iopub.execute_input":"2024-03-13T13:10:26.174630Z","iopub.status.idle":"2024-03-13T13:10:26.182231Z","shell.execute_reply.started":"2024-03-13T13:10:26.174592Z","shell.execute_reply":"2024-03-13T13:10:26.181039Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trn_C = trn_O.merge(static_cb[C + ['case_id']], on=['case_id'], how='left')\ntst_C = tst_O.merge(tst_static_cb[C + ['case_id']], on=['case_id'], how='left')\n\ndel trn_O, tst_O","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:26.183404Z","iopub.execute_input":"2024-03-13T13:10:26.183805Z","iopub.status.idle":"2024-03-13T13:10:30.825050Z","shell.execute_reply.started":"2024-03-13T13:10:26.183779Z","shell.execute_reply":"2024-03-13T13:10:30.823975Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Category Feature\n### ['bankacctype_710L', 'paytype1st_925L', 'paytype_783L', 'typesuite_864L'] are badly imbalance features","metadata":{}},{"cell_type":"code","source":"plot_na(trn_C[C])","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_category_hist_pie(trn_C, C)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:30.826601Z","iopub.execute_input":"2024-03-13T13:10:30.826895Z","iopub.status.idle":"2024-03-13T13:10:40.046082Z","shell.execute_reply.started":"2024-03-13T13:10:30.826870Z","shell.execute_reply":"2024-03-13T13:10:40.044257Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Amount Feature\n### ['commnoinclast6m_3546845L', 'deferredmnthsnum_166L', 'interestrategrace_34L', 'mastercontrelectronic_519L', 'mastercontrexist_109L'] \n### these features' value are 0\n### so I need to check the NA ratio to decide it should be delete or not","metadata":{}},{"cell_type":"code","source":"trn_A = trn_C.merge(static_cb[A + ['case_id']], on=['case_id'], how='left')\ntst_A = tst_C.merge(tst_static_cb[A + ['case_id']], on=['case_id'], how='left')\n\ndel trn_C, tst_C","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.047305Z","iopub.status.idle":"2024-03-13T13:10:40.048181Z","shell.execute_reply.started":"2024-03-13T13:10:40.047918Z","shell.execute_reply":"2024-03-13T13:10:40.047941Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_na(trn_A[A])","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_count_scatter_hist(trn_A, A)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.049995Z","iopub.status.idle":"2024-03-13T13:10:40.050526Z","shell.execute_reply.started":"2024-03-13T13:10:40.050245Z","shell.execute_reply":"2024-03-13T13:10:40.050267Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Date Features\n### Calculate the timedelta \n### Used Decsity Chart due to the Postive Target are much fewer than Negative Target","metadata":{}},{"cell_type":"code","source":"trn_D = trn_A.merge(static_cb[D + ['case_id']], on=['case_id'], how='left')\ntst_D = tst_A.merge(tst_static_cb[D + ['case_id']], on=['case_id'], how='left')\n\ndel trn_A, tst_A","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.052128Z","iopub.status.idle":"2024-03-13T13:10:40.052656Z","shell.execute_reply.started":"2024-03-13T13:10:40.052370Z","shell.execute_reply":"2024-03-13T13:10:40.052392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_na(trn_D[D])","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.055137Z","iopub.status.idle":"2024-03-13T13:10:40.055671Z","shell.execute_reply.started":"2024-03-13T13:10:40.055378Z","shell.execute_reply":"2024-03-13T13:10:40.055398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for i in date + D + ['date_decision']:\n    trn_D[i] = pd.to_datetime(trn_D[i])","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.057500Z","iopub.status.idle":"2024-03-13T13:10:40.058019Z","shell.execute_reply.started":"2024-03-13T13:10:40.057754Z","shell.execute_reply":"2024-03-13T13:10:40.057775Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_date_hist(trn_D, date)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.061636Z","iopub.status.idle":"2024-03-13T13:10:40.062149Z","shell.execute_reply.started":"2024-03-13T13:10:40.061887Z","shell.execute_reply":"2024-03-13T13:10:40.061907Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tbl = static_cb[['case_id', 'dateofbirth_337D']].dropna().merge(static_cb[['case_id', 'dateofbirth_342D']].dropna(), on=['case_id'], how='left')\ntbl2 = tbl[~tbl['dateofbirth_342D'].isnull()]\ncase_id = tbl2[tbl2['dateofbirth_337D']!=tbl2['dateofbirth_342D']]['case_id'].tolist()\ntrn_D[trn_D['case_id'].isin(case_id)]['target'].value_counts(normalize=True)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.063903Z","iopub.status.idle":"2024-03-13T13:10:40.064421Z","shell.execute_reply.started":"2024-03-13T13:10:40.064155Z","shell.execute_reply":"2024-03-13T13:10:40.064176Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_date_hist(trn_D, D)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.066291Z","iopub.status.idle":"2024-03-13T13:10:40.066835Z","shell.execute_reply.started":"2024-03-13T13:10:40.066557Z","shell.execute_reply":"2024-03-13T13:10:40.066579Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_hist_count(trn_D, D)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.068138Z","iopub.status.idle":"2024-03-13T13:10:40.069153Z","shell.execute_reply.started":"2024-03-13T13:10:40.068871Z","shell.execute_reply":"2024-03-13T13:10:40.068894Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Want to decide which threshold are suitable\n### TODO: Record how much threshold and it will be drop how many features","metadata":{}},{"cell_type":"code","source":"## Category imbalance\ndef imbalance_collect(data, cat_list, threshold):\n    imbalance_threshold = threshold\n    imb_cat = []\n    for i in cat_list:\n        tbl = data[i].value_counts(normalize=True).mul(100).round(2).reset_index().sort_values(by=['proportion'], ascending=False)\n        if len(tbl[tbl['proportion']>imbalance_threshold][i]) != 0:\n            imb_cat.append(i)\n        else:\n            pass\n        \n    return imb_cat","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.070465Z","iopub.status.idle":"2024-03-13T13:10:40.071268Z","shell.execute_reply.started":"2024-03-13T13:10:40.070990Z","shell.execute_reply":"2024-03-13T13:10:40.071013Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"imbalance_collect(trn_D, C, 96)","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.072805Z","iopub.status.idle":"2024-03-13T13:10:40.073332Z","shell.execute_reply.started":"2024-03-13T13:10:40.073051Z","shell.execute_reply":"2024-03-13T13:10:40.073071Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Just notice some features got same description\n### TODO： record the same description features","metadata":{}},{"cell_type":"code","source":"## description repeat\ndef dup_name(col_list):\n    name = []\n    descript = []\n    for i in col_list:\n        word = i.split('_')[0]\n        name.append(word)\n        des = re.sub(r'[,。]', '', trans(i))\n        descript.append(des)\n        if descript.count(des) > 1:\n#             print(i, name.count(word))\n            print(i, descript.count(des))","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.077024Z","iopub.status.idle":"2024-03-13T13:10:40.077548Z","shell.execute_reply.started":"2024-03-13T13:10:40.077267Z","shell.execute_reply":"2024-03-13T13:10:40.077287Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Static T&L feature ","metadata":{}},{"cell_type":"code","source":"# bool_col = ['isbidproduct_1095L',\n#  'equalitydataagreement_891L',\n#  'equalityempfrom_62L',\n#  'isbidproductrequest_292L',\n#  'isdebitcard_729L',\n#  'opencred_647L']\n\n# cat_col = ['bankacctype_710L',\n#  'cardtype_51L',\n#  'credtype_322L',\n#  'disbursementtype_67L',\n#  'inittransactioncode_186L',\n#  'lastst_736L',\n#  'paytype1st_925L',\n#  'paytype_783L',\n#  'twobodfilling_608L',\n#  'typesuite_864L']\n\n# num_col = ['applicationcnt_361L',\n#  'applications30d_658L',\n#  'applicationscnt_1086L',\n#  'applicationscnt_464L',\n#  'applicationscnt_629L',\n#  'applicationscnt_867L',\n#  'clientscnt12m_3712952L',\n#  'clientscnt3m_3712950L',\n#  'clientscnt6m_3712949L',\n#  'clientscnt_100L',\n#  'clientscnt_1022L',\n#  'clientscnt_1071L',\n#  'clientscnt_1130L',\n#  'clientscnt_136L',\n#  'clientscnt_157L',\n#  'clientscnt_257L',\n#  'clientscnt_304L',\n#  'clientscnt_360L',\n#  'clientscnt_493L',\n#  'clientscnt_533L',\n#  'clientscnt_887L',\n#  'clientscnt_946L',\n#  'cntincpaycont9m_3716944L',\n#  'cntpmts24_3658933L',\n#  'commnoinclast6m_3546845L',\n#  'daysoverduetolerancedd_3976961L',\n#  'deferredmnthsnum_166L',\n#  'eir_270L',\n#  'homephncnt_628L',\n#  'interestrate_311L',\n#  'interestrategrace_34L',\n#  'lastdependentsnum_448L',\n#  'mastercontrelectronic_519L',\n#  'mastercontrexist_109L',\n#  'mobilephncnt_593L',\n#  'monthsannuity_845L',\n#  'numactivecreds_622L',\n#  'numactivecredschannel_414L',\n#  'numactiverelcontr_750L',\n#  'numcontrs3months_479L',\n#  'numincomingpmts_3546848L',\n#  'numinstlallpaidearly3d_817L',\n#  'numinstls_657L',\n#  'numinstlsallpaid_934L',\n#  'numinstlswithdpd10_728L',\n#  'numinstlswithdpd5_4187116L',\n#  'numinstlswithoutdpd_562L',\n#  'numinstmatpaidtearly2d_4499204L',\n#  'numinstpaid_4499208L',\n#  'numinstpaidearly3d_3546850L',\n#  'numinstpaidearly3dest_4493216L',\n#  'numinstpaidearly5d_1087L',\n#  'numinstpaidearly5dest_4493211L',\n#  'numinstpaidearly5dobd_4499205L',\n#  'numinstpaidearly_338L',\n#  'numinstpaidearlyest_4493214L',\n#  'numinstpaidlastcontr_4325080L',\n#  'numinstpaidlate1d_3546852L',\n#  'numinstregularpaid_973L',\n#  'numinstregularpaidest_4493210L',\n#  'numinsttopaygr_769L',\n#  'numinsttopaygrest_4493213L',\n#  'numinstunpaidmax_3546851L',\n#  'numinstunpaidmaxest_4493212L',\n#  'numnotactivated_1143L',\n#  'numpmtchanneldd_318L',\n#  'numrejects9m_859L',\n#  'pctinstlsallpaidearl3d_427L',\n#  'pctinstlsallpaidlat10d_839L',\n#  'pctinstlsallpaidlate1d_3546856L',\n#  'pctinstlsallpaidlate4d_3546849L',\n#  'pctinstlsallpaidlate6d_3546844L',\n#  'pmtnum_254L',\n#  'sellerplacecnt_915L',\n#  'sellerplacescnt_216L']","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.079465Z","iopub.status.idle":"2024-03-13T13:10:40.079933Z","shell.execute_reply.started":"2024-03-13T13:10:40.079733Z","shell.execute_reply":"2024-03-13T13:10:40.079750Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tbl = trn_D.copy()\ndays = []\nfor i in date:\n    column = i.split('_')[0]\n    idx = tbl[~tbl[i].isnull()].index\n    tbl.loc[idx, f'{column}_days'] = tbl.loc[idx, i]-tbl.loc[idx, 'date_decision']\n    tbl[f'{column}_days'] = tbl[f'{column}_days'].apply(lambda x: x / np.timedelta64(1,'D'))\n    days.append(f'{column}_days')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.preprocessing import StandardScaler\nscaler = StandardScaler()\nscaler = scaler.fit(tbl[days])","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.080938Z","iopub.status.idle":"2024-03-13T13:10:40.081274Z","shell.execute_reply.started":"2024-03-13T13:10:40.081110Z","shell.execute_reply":"2024-03-13T13:10:40.081124Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tbl[days] = scaler.transform(tbl[days])","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.082408Z","iopub.status.idle":"2024-03-13T13:10:40.083255Z","shell.execute_reply.started":"2024-03-13T13:10:40.082961Z","shell.execute_reply":"2024-03-13T13:10:40.082985Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(10, 10))\nsns.heatmap(tbl[days + ['target']].corr('kendall'), lw=0.5, linecolor='black', cmap='coolwarm', annot=True)\nplt.title('timedelta(D) w/ target correlation')","metadata":{"execution":{"iopub.status.busy":"2024-03-13T13:10:40.084218Z","iopub.status.idle":"2024-03-13T13:10:40.084778Z","shell.execute_reply.started":"2024-03-13T13:10:40.084493Z","shell.execute_reply":"2024-03-13T13:10:40.084516Z"},"trusted":true},"execution_count":null,"outputs":[]}]}