{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":50160,"databundleVersionId":7921029,"sourceType":"competition"}],"dockerImageVersionId":30698,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"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\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\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\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-04-28T17:00:33.406469Z","iopub.execute_input":"2024-04-28T17:00:33.407135Z","iopub.status.idle":"2024-04-28T17:00:34.765542Z","shell.execute_reply.started":"2024-04-28T17:00:33.407092Z","shell.execute_reply":"2024-04-28T17:00:34.764734Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install hvplot","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:00:39.495424Z","iopub.execute_input":"2024-04-28T17:00:39.496035Z","iopub.status.idle":"2024-04-28T17:00:58.001221Z","shell.execute_reply.started":"2024-04-28T17:00:39.495981Z","shell.execute_reply":"2024-04-28T17:00:57.998984Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import polars as pl\nimport numpy as np\nimport pandas as pd\nfrom os.path import getsize, join, split, splitext\nfrom glob import glob\nfrom tqdm.notebook import tqdm\nfrom bokeh.models import NumeralTickFormatter\n\npl.Config(\n    fmt_str_lengths=80,\n    tbl_rows=80,\n    set_thousands_separator=' ',\n    float_precision=1,\n    set_fmt_float=\"full\",\n    tbl_cell_alignment = \"LEFT\",\n    tbl_cell_numeric_alignment=\"RIGHT\"\n)\nfrmt_big_numb = NumeralTickFormatter(format='0.0a')","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:01:04.173725Z","iopub.execute_input":"2024-04-28T17:01:04.174141Z","iopub.status.idle":"2024-04-28T17:01:05.241901Z","shell.execute_reply.started":"2024-04-28T17:01:04.174105Z","shell.execute_reply":"2024-04-28T17:01:05.240813Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"MAIN_PATH = '/kaggle/input/home-credit-credit-risk-model-stability'\nTRAIN_PATH_PARQUET = MAIN_PATH + '/parquet_files/train'\nTEST_PATH_PARQUET = MAIN_PATH + '/parquet_files/test'\nTRAIN_PATH_CSV = MAIN_PATH + '/csv_files/train'\nTEST_PATH_CSV = MAIN_PATH + '/csv_files/test'","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:01:07.879680Z","iopub.execute_input":"2024-04-28T17:01:07.881145Z","iopub.status.idle":"2024-04-28T17:01:07.887953Z","shell.execute_reply.started":"2024-04-28T17:01:07.881083Z","shell.execute_reply":"2024-04-28T17:01:07.886808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def aggregate_nulls(path):\n    \n    id = splitext(split(path)[-1])[0]\n    feat_def = pl.read_csv(MAIN_PATH + '/feature_definitions.csv')\n    \n    df = pl.scan_parquet(path)\n    df_dtypes = pl.DataFrame({'features':df.columns, 'dtypes':[str(x) for x in df.dtypes]})\n    df = df.collect()\n    \n    df_shapes = pl.DataFrame({'file':id, 'n':df.shape[0], 'm':df.shape[1]}) \\\n                        .with_columns((pl.col('n')*pl.col('m')).alias('cells_numb'))\n    \n    df = df.null_count().select(pl.all().exclude(['case_id', 'num_group1', 'num_group2']), \n                                pl.lit(id).alias('file'),\n                               )\n    \n    df_nulls = df.melt('file', variable_name='features', value_name='nulls_numb') \\\n                 .with_columns(pl.col('features').str.slice(-1).alias('type')) \\\n                 .join(df_dtypes, on='features', how='left') \\\n                 .join(feat_def, left_on='features', right_on='Variable', how='left')\n    \n    return df_nulls, df_shapes","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:01:08.865338Z","iopub.execute_input":"2024-04-28T17:01:08.866539Z","iopub.status.idle":"2024-04-28T17:01:08.880979Z","shell.execute_reply.started":"2024-04-28T17:01:08.866493Z","shell.execute_reply":"2024-04-28T17:01:08.879831Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# taking all files except 'train_base'\npath_list = [path for path in glob(f'{TRAIN_PATH_PARQUET}/*.parquet') if 'train_base.parquet' not in path]\n\nfor i, path in tqdm(enumerate(path_list), total=(len(path_list))):\n    \n    nulls, shapes = aggregate_nulls(path)\n    \n    if i==0:\n        df_nulls, df_shapes = nulls, shapes\n    else:\n        df_nulls = df_nulls.vstack(nulls)\n        df_shapes = df_shapes.vstack(shapes)\n    \ndel nulls, shapes","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:01:10.196985Z","iopub.execute_input":"2024-04-28T17:01:10.197941Z","iopub.status.idle":"2024-04-28T17:02:10.386837Z","shell.execute_reply.started":"2024-04-28T17:01:10.197902Z","shell.execute_reply":"2024-04-28T17:02:10.385246Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"null_prc = df_nulls.select(['file', 'nulls_numb']).group_by('file').sum() \\\n                   .join(df_shapes.select(['file','cells_numb']).group_by('file').sum(), on='file') \\\n                   .with_columns((pl.col('nulls_numb')/pl.col('cells_numb')).alias('null_prc')) \\\n                   .with_columns((1-pl.col('null_prc')).alias('filled_prc'))\n\ntot_nulls = (null_prc.select(\"nulls_numb\").sum()/null_prc.select(\"cells_numb\").sum())['nulls_numb'][0]\nprint(f'total number of null cells - {tot_nulls:.1%}, hense filled cells - {1-tot_nulls:.1%}')\n\ndisplay(\n    null_prc.plot.bar(x='file',\n                  y=['filled_prc', 'null_prc'],\n                  stacked=True,\n                  rot=90,\n                  height=500,\n                  width=1000,\n                  title='Percentage of filled cells per table',\n                  ylabel='percentage of filled cells'\n                 )\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:02:32.899442Z","iopub.execute_input":"2024-04-28T17:02:32.899844Z","iopub.status.idle":"2024-04-28T17:02:37.268268Z","shell.execute_reply.started":"2024-04-28T17:02:32.899814Z","shell.execute_reply":"2024-04-28T17:02:37.267212Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# fell free to change threshold - it's funny!\nthreshold_of_nulls = 0.9 \n\nnulls_prc_per_feat = df_nulls.join(df_shapes, on='file').with_columns((pl.col('nulls_numb')/pl.col('n')).alias('nulls_prc'))\nft_wo_nulls = nulls_prc_per_feat.filter(pl.col('nulls_numb')==0) \\\n                                .group_by('file').agg(pl.col('features').count().alias('without nulls'))\nnull_above_th = nulls_prc_per_feat.filter(pl.col('nulls_prc')>threshold_of_nulls) \\\n                                  .group_by('file').agg(pl.col('features').count().alias(f'more {threshold_of_nulls:.0%} nulls'))\nnull_below_th = nulls_prc_per_feat.filter((pl.col('nulls_prc')<threshold_of_nulls) & (pl.col('nulls_numb')>0)) \\\n                    .group_by('file').agg(pl.col('features').count().alias(f'less {threshold_of_nulls:.0%} nulls'))\n\ndf_pl = df_shapes.join(ft_wo_nulls, on='file', how='left') \\\n                 .join(null_above_th, on='file', how='left') \\\n                 .join(null_below_th, on='file', how='left') \\\n                 .fill_null(strategy='zero').sort('m', descending=True)\n\ndisplay(\n    df_pl.plot.barh(x='file', \n                y=['without nulls', f'less {threshold_of_nulls:.0%} nulls', f'more {threshold_of_nulls:.0%} nulls'], \n                stacked=True, height=700, width=1200, legend='top_right', \n                title = f'Number of features without nulls and with nulls in less/more then {threshold_of_nulls:.0%} of cases',\n                ylabel='number of features',\n              )\n)\n","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:02:41.798377Z","iopub.execute_input":"2024-04-28T17:02:41.800081Z","iopub.status.idle":"2024-04-28T17:02:42.127651Z","shell.execute_reply.started":"2024-04-28T17:02:41.800009Z","shell.execute_reply":"2024-04-28T17:02:42.126613Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nulls_prc_per_feat.select('nulls_prc').plot.hist(\n    bins=10, \n    title='Number of features by percentage of null values',\n    xlabel='percentage of null values',\n    ylabel='number of features'\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:02:44.872986Z","iopub.execute_input":"2024-04-28T17:02:44.873501Z","iopub.status.idle":"2024-04-28T17:02:45.723329Z","shell.execute_reply.started":"2024-04-28T17:02:44.873464Z","shell.execute_reply":"2024-04-28T17:02:45.722395Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nulls_prc_per_feat = nulls_prc_per_feat.with_columns((1-pl.col('nulls_prc')).alias('filled_prc'))\ndisplay(\n    nulls_prc_per_feat.plot.barh('features', 'filled_prc', by='type',\n                             groupby=['file'],\n                             stacked=True, \n                             dynamic=False,\n                             legend='bottom_right', \n#                              widget_location='right_top', # breakes down...\n                             height=1200, width=900,\n                             fontsize={'title': 12, 'xticks': 8, 'yticks': 8},\n                             hover_cols=['Description', 'dtypes'],\n                             ylabel='features filling percentage',\n\n                  )\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:02:48.196796Z","iopub.execute_input":"2024-04-28T17:02:48.197601Z","iopub.status.idle":"2024-04-28T17:02:52.317518Z","shell.execute_reply.started":"2024-04-28T17:02:48.197562Z","shell.execute_reply":"2024-04-28T17:02:52.316203Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# first of all explore intersection of the custom types and dtypes \ndf_nulls.pivot(index='type', columns='dtypes', values='nulls_numb', aggregate_function='len')","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:02:52.319731Z","iopub.execute_input":"2024-04-28T17:02:52.321368Z","iopub.status.idle":"2024-04-28T17:02:52.341877Z","shell.execute_reply.started":"2024-04-28T17:02:52.321312Z","shell.execute_reply":"2024-04-28T17:02:52.340569Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# some functions that specifying plotting\n\ndef numb_rate_of_unique_table(df, feature, width, height, title):\n    \"\"\"\n    Create table with the number and percentage of unique values for categorical features\n    \"\"\"\n    table = df.select(pl.col(feature), pl.col(feature).count().alias('total')) \\\n              .group_by([feature, 'total']).len().sort('len', descending=True) \\\n              .filter(pl.col(feature).is_not_null()) \\\n              .select(pl.col(feature), pl.col('len'),((pl.col('len')/pl.col('total'))*100).round(2).alias('rate')).collect() \\\n              .plot.table(width=width, \n                          height=height,\n                          title=title,\n                          fontsize={'title':8},\n                          yformatter=frmt_big_numb)\n    return table\n\ndef subplot_format(feat_list):\n    \"\"\"\n    Sets the number of columns and their height and width on a plot figure\n    (these settings are specific to my monitor)\n    \"\"\"\n    len_feat_list = len(feat_list)\n    if len_feat_list == 1:\n        cols = 1\n        width = 1000\n        height = 300\n    elif len_feat_list < 7:\n        cols = 2\n        width = 600\n        height = 300\n    else: \n        cols = 3\n        width = 400\n        height = 250\n    \n    return cols, width, height\n\ndef plot_cat_features(df, feat_list):\n    \"\"\"\n    Plots data for categorical features\n    \"\"\"\n    cols, width, height = subplot_format(feat_list)\n    \n    # prepare a table for each categorical feature\n    for i, feat in enumerate(feat_list):\n        desc = take_description(feat)\n        if not i:\n            grid = numb_rate_of_unique_table(df, feat, width, height, desc)\n        else:\n            grid += numb_rate_of_unique_table(df, feat, width, height, desc)\n\n    # display a graph of unique values per feature\n    display(df.select(pl.col(feat_list).n_unique()).collect() \n              .plot.barh(width=1000, \n                         xlabel='features', \n                         ylabel='number or unique values',\n                         title=f'Number of unique values per categorical feature; file - {file_name}'\n                        )\n           )\n\n    # display a tables of unique values with number and rate of each value\n    display(grid.cols(cols)) if cols>1 else display(grid)\n    \ndef plot_hist(df, feat_list, **ext_plot_options):\n    \"\"\"\n    Plots histogramms for non-categorical fratures\n    \"\"\"\n    df = df.collect()\n    cols, width, height = subplot_format(feat_list)\n    \n    for i, feat in enumerate(feat_list):\n        \n        xlabel = take_description(feat)\n        \n        if not i:\n            grid = df.plot.hist(feat, \n                                 width=width, \n                                 height=height,\n                                 title=feat,\n                                 xlabel = xlabel,\n                                 fontsize={'xlabel':8},\n                                 **ext_plot_options,\n                                )\n        else:\n            grid += df.plot.hist(feat, \n                                 width=width, \n                                 height=height,\n                                 title=feat,\n                                 xlabel = xlabel,\n                                 fontsize={'xlabel':8},\n                                 **ext_plot_options,\n                                )\n    display(grid.cols(cols)) if cols>1 else display(grid)\n    \ndef take_description(feat):\n    \"\"\"\n    Splits feature description into several lines of a given length\n    \"\"\"\n    maxlen = 70 # max length of feature descriptor line\n    # take a feature description\n    xlabel = df_nulls.filter(pl.col('features') == feat).select('Description').unique().item()[:-1]\n    # there are many long feature descriptors, we should split them into several lines\n    if len(xlabel) - maxlen > 10: # to avoid hyphenation of one-two letters\n        xlabel = ''.join([xlabel[x:x+maxlen]+'\\n' for x in range(0, len(xlabel), maxlen)])[:-1]\n    return xlabel\n\ndef describe_features(df, percentiles=(0.25,0.5,0.75)):\n    \"\"\"\n    Standard statistical description supplemented with:\n    zero_count - number of zero values\n    null% - percentage of null values\n    null&zero - percentage of null and zero values together\n    zero_in_not_null% - percentage of non-zero values in not empty(null) values\n    \n    \"\"\"\n    df = df.collect()\n    \n    # get standart describe and add number of zero value\n    df = pl.concat([df.describe(percentiles=percentiles),\n                (df/df).select(pl.all().is_nan()).sum().cast(pl.Float64)\n                .select(pl.lit('zero_count').alias('describe'), pl.all())]\n              )\n    \n    names = df.select('describe').to_series()\n    df = df.select(pl.exclude('describe')).transpose(include_header=True, header_name='feature', column_names=names)\n    df = df.with_columns(((pl.col('null_count')/(pl.col('count')+pl.col('null_count')))*100\n                         ).alias('null%'),\n                         (\n                             (\n                                 (pl.col('null_count')+pl.col('zero_count'))/\n                                 (pl.col('count')+pl.col('null_count'))\n                             )*100\n                         ).alias('null&zero%'),\n                         ((pl.col('zero_count')/pl.col('count'))*100\n                         ).alias('zero_in_not_null%'),\n                        )\n    return df","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:02:56.655979Z","iopub.execute_input":"2024-04-28T17:02:56.656482Z","iopub.status.idle":"2024-04-28T17:02:56.687737Z","shell.execute_reply.started":"2024-04-28T17:02:56.656447Z","shell.execute_reply":"2024-04-28T17:02:56.685967Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###########################################################\n# HERE YOU SPECIFY THE FILE TO BE ANALYZED\n# feel free to paste here any tables (parquet format only!)\nfile_path = TRAIN_PATH_PARQUET + '/train_credit_bureau_a_1_1.parquet'\n###########################################################\nfile_name = splitext(split(file_path)[-1])[0]\ndf_test = pl.scan_parquet(file_path)\n","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:02:59.598998Z","iopub.execute_input":"2024-04-28T17:02:59.600513Z","iopub.status.idle":"2024-04-28T17:02:59.612927Z","shell.execute_reply.started":"2024-04-28T17:02:59.600462Z","shell.execute_reply":"2024-04-28T17:02:59.611518Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"feat_list = df_test.select(pl.col(r'^.*M$')).columns\nplot_cat_features(df_test, feat_list) if feat_list else print('There are not M-features')","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:03:02.104854Z","iopub.execute_input":"2024-04-28T17:03:02.106112Z","iopub.status.idle":"2024-04-28T17:03:11.214931Z","shell.execute_reply.started":"2024-04-28T17:03:02.106061Z","shell.execute_reply":"2024-04-28T17:03:11.213556Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import polars as pl\nimport polars.selectors as cs\nimport numpy as np\nimport pandas as pd\nfrom sklearn.preprocessing import PowerTransformer, RobustScaler, QuantileTransformer, OneHotEncoder\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import roc_auc_score\nimport lightgbm as lgb\nimport os\nfrom os.path import getsize, join, split, splitext\nfrom glob import glob\nfrom tqdm.notebook import tqdm\nimport matplotlib.pyplot as plt\nfrom bokeh.models import NumeralTickFormatter\nfrom bokeh.models import HoverTool\nimport holoviews as hv\nfrom holoviews import opts\nhv.extension('bokeh')\nimport panel as pn\nfrom ipywidgets import widgets\npn.extension()\nplt.style.use('seaborn-v0_8')\npl.Config(\n    fmt_str_lengths=80,\n    tbl_rows=80,\n    set_thousands_separator=' ',\n    float_precision=3,\n    set_fmt_float=\"full\",\n    tbl_cell_alignment = \"LEFT\",\n    tbl_cell_numeric_alignment=\"RIGHT\"\n)\nfrmt_big_numb = NumeralTickFormatter(format='0.0a')","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:03:38.502899Z","iopub.execute_input":"2024-04-28T17:03:38.503363Z","iopub.status.idle":"2024-04-28T17:03:39.887908Z","shell.execute_reply.started":"2024-04-28T17:03:38.503323Z","shell.execute_reply":"2024-04-28T17:03:39.886334Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"MAIN_PATH = '/kaggle/input/home-credit-credit-risk-model-stability'\nTRAIN_PATH_PARQUET = MAIN_PATH + '/parquet_files/train'\nTEST_PATH_PARQUET = MAIN_PATH + '/parquet_files/test'\nTRAIN_PATH_CSV = MAIN_PATH + '/csv_files/train'\nTEST_PATH_CSV = MAIN_PATH + '/csv_files/test'","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:03:39.890979Z","iopub.execute_input":"2024-04-28T17:03:39.891519Z","iopub.status.idle":"2024-04-28T17:03:39.899655Z","shell.execute_reply.started":"2024-04-28T17:03:39.891477Z","shell.execute_reply":"2024-04-28T17:03:39.897973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"path_list = [path for path in glob(f'{TRAIN_PATH_PARQUET}/*.parquet') if 'train_base.parquet' not in path]","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:03:42.218273Z","iopub.execute_input":"2024-04-28T17:03:42.218655Z","iopub.status.idle":"2024-04-28T17:03:42.225271Z","shell.execute_reply.started":"2024-04-28T17:03:42.218626Z","shell.execute_reply":"2024-04-28T17:03:42.224070Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def file_list(path_list, depth, take_id=False):\n    \"\"\"\n    Preparing list of files and groups of files (if file splited into several parts)\n    \n    path_list - list of file paths\n    depth     - 'depth' in terms of the competition\n    take_id   - if you need to take only the identifier of files/groups (not the path)\n    \"\"\"\n    files = []\n    groups = []\n    \n    for name in path_list:\n        \n        name = splitext(split(name)[-1])[0] if take_id else splitext(name)[0]\n        splited_name = name.split('_')\n        \n        # “depth” in file names is in the last (single file case) \n        # or penultimate place (if file splitted into several parts)\n        if splited_name[-2].isnumeric():\n            if  int(splited_name[-2]) == depth:\n                files.append(name)\n                groups.append(\"_\".join(splited_name[:-1]))\n        else:\n            if int(splited_name[-1]) == depth:\n                files.append(name)\n                groups.append(name)\n        \n        # let's make a dictionary too, it will come in handy\n        struct_dict = {}\n        for group in set(groups):\n            struct_dict[group] = [file for file in files if group in file]\n            \n    return files, set(groups), struct_dict","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:03:43.148360Z","iopub.execute_input":"2024-04-28T17:03:43.148825Z","iopub.status.idle":"2024-04-28T17:03:43.162215Z","shell.execute_reply.started":"2024-04-28T17:03:43.148793Z","shell.execute_reply":"2024-04-28T17:03:43.160536Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"files, groups, struct_dict = file_list(path_list, 1)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:03:44.262089Z","iopub.execute_input":"2024-04-28T17:03:44.262575Z","iopub.status.idle":"2024-04-28T17:03:44.268386Z","shell.execute_reply.started":"2024-04-28T17:03:44.262542Z","shell.execute_reply":"2024-04-28T17:03:44.267467Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cum_size = 0\nfor group in sorted(groups):\n    size_mb = [getsize(name + \".parquet\")/1e6 for name in files if group in name]\n    cum_size += sum(size_mb)\n    print(f'{group.split(\"/\")[-1]:<25}{sum(size_mb):>10.3f} Mb')\nprint('='*38, f'{\"total\":<25}{cum_size:>10.3f} Mb', sep='\\n')","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:03:45.376842Z","iopub.execute_input":"2024-04-28T17:03:45.377306Z","iopub.status.idle":"2024-04-28T17:03:45.391364Z","shell.execute_reply.started":"2024-04-28T17:03:45.377276Z","shell.execute_reply":"2024-04-28T17:03:45.390226Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"base = pl.read_parquet(TRAIN_PATH_PARQUET + '/train_base.parquet') \\\n         .cast({'case_id':pl.Int64,\n                'date_decision':pl.Date,\n                'MONTH':pl.UInt32,\n                'WEEK_NUM':pl.UInt16,\n                'target':pl.UInt8\n               })","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:04:17.633778Z","iopub.execute_input":"2024-04-28T17:04:17.634286Z","iopub.status.idle":"2024-04-28T17:04:18.242460Z","shell.execute_reply.started":"2024-04-28T17:04:17.634251Z","shell.execute_reply":"2024-04-28T17:04:18.241445Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"case_numb = base.shape[0]\nprint(f'number of unique case_id - {case_numb:,}')\nprint(f\"percentage of positive labels - {base.select(pl.col('target').sum()/pl.col('target').count())[0,0]:.3%}\", '\\n')\ndisplay(base.head())","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:04:50.578682Z","iopub.execute_input":"2024-04-28T17:04:50.579084Z","iopub.status.idle":"2024-04-28T17:04:50.614612Z","shell.execute_reply.started":"2024-04-28T17:04:50.579053Z","shell.execute_reply":"2024-04-28T17:04:50.613703Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def lazy_df_prep(parts):\n    \"\"\"\n    Prepare a LazyFrame from one or more parts (files)\n    \"\"\"\n    if len(parts) > 1:\n        list_for_concat = [pl.scan_parquet(x+'.parquet') for x in parts]\n        df = pl.concat(list_for_concat)\n    else:\n        df = pl.scan_parquet(parts[0]+'.parquet')\n        \n    return df\n\ndef table_iterator(struct_dict):\n    \"\"\"\n    Iterate through a set of tables and return a LazyFrame\n    \"\"\"\n    tqdm_iter = tqdm(sorted(struct_dict.keys()), bar_format='{l_bar}{bar}{n_fmt}/{total_fmt} {postfix}')\n    \n    for group in tqdm_iter:\n        tqdm_iter.set_postfix_str(s=group.split(\"/\")[-1])\n        df = lazy_df_prep(struct_dict[group])\n        \n        yield df, group","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:04:52.216771Z","iopub.execute_input":"2024-04-28T17:04:52.217200Z","iopub.status.idle":"2024-04-28T17:04:52.228204Z","shell.execute_reply.started":"2024-04-28T17:04:52.217171Z","shell.execute_reply":"2024-04-28T17:04:52.226251Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# let's prepare some stuff for the panel\nindic = pn.indicators.Number(\n    title_size = '12pt',\n    font_size='30pt',\n    format = '{value:,}',\n    width=230,\n    styles={'background':'#2980B9', \n            'text-align':'center',\n            'padding':'20px 0 0 0',\n            'border-radius': '5px'},\n)\n\nindic_dial = pn.indicators.Dial(\n    title_size = '10pt',\n    tick_size = '6pt',\n    format = '{value:,}',\n    width=230,\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:04:53.618280Z","iopub.execute_input":"2024-04-28T17:04:53.618665Z","iopub.status.idle":"2024-04-28T17:04:53.629833Z","shell.execute_reply.started":"2024-04-28T17:04:53.618638Z","shell.execute_reply":"2024-04-28T17:04:53.628679Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"grid=[]\n\nfor df, group in table_iterator(struct_dict):\n    \n    numb_uniq = df.select(pl.col('case_id').n_unique()).collect().item() \n    grid.append(indic_dial.clone(value=numb_uniq,\n                             name=group.split(\"/\")[-1],\n                             bounds=(0, case_numb),\n                             colors=[(0.1, 'red'),      # less than one tenth\n                                     (0.33, '#F5B7B1'), # more than tenth but less than third\n                                     (0.66, 'gold') ,   # more than third but less than two thirds\n                                     (0.9, '#A9DFBF'),  # more than two thirds but less than nine tenths \n                                     (1, 'green'),      # more than nine tenths\n                                    ],       # next - white\n                            )\n               )\n\npn.WidgetBox('### Number of unique cases', \n             pn.GridBox(*grid, styles={'background':'#add8e6'}, ncols=5)\n            )","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:04:56.598546Z","iopub.execute_input":"2024-04-28T17:04:56.599676Z","iopub.status.idle":"2024-04-28T17:04:59.899661Z","shell.execute_reply.started":"2024-04-28T17:04:56.599624Z","shell.execute_reply":"2024-04-28T17:04:59.898020Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"grid=[]\n# green<30, 30<yelow<100, red>100\nfor df, group in table_iterator(struct_dict):\n    \n    numb_uniq = df.select(pl.col('num_group1').n_unique()).collect().item() \n    grid.append(indic.clone(value=numb_uniq, \n                             name=group.split(\"/\")[-1],\n                             colors=[(30, '#A9DFBF'), (100, '#F9E79F'), (np.inf, '#F5B7B1')],\n                            )\n               )\n\npn.WidgetBox('### Number of unique values of the num_group1 feature', \n             pn.GridBox(*grid, styles={'background':'#add8e6'}, ncols=5)\n            )","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:05:03.500027Z","iopub.execute_input":"2024-04-28T17:05:03.500509Z","iopub.status.idle":"2024-04-28T17:05:05.007425Z","shell.execute_reply.started":"2024-04-28T17:05:03.500476Z","shell.execute_reply":"2024-04-28T17:05:05.005470Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# prepare dict of dataframes\nnum_gr_per_id = {}\n\nfor df, group in table_iterator(struct_dict):\n\n    # number of unuque values per case_id\n    num_gr_per_id[group.split(\"/\")[-1]] = df.group_by('case_id').agg(pl.col('num_group1').n_unique()).collect()","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:05:06.920288Z","iopub.execute_input":"2024-04-28T17:05:06.920890Z","iopub.status.idle":"2024-04-28T17:05:15.573546Z","shell.execute_reply.started":"2024-04-28T17:05:06.920853Z","shell.execute_reply":"2024-04-28T17:05:15.572324Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plotting...\nfor i, name in enumerate(sorted(num_gr_per_id.keys())):\n    df = num_gr_per_id[name].group_by(pl.col('num_group1').sort()).agg(pl.col('num_group1').count().alias('number'))\n    if not i:\n        # if there are up to 25 records, it is more convenient to use a bar\n        if len(df) < 25:\n            plot_grid = df.plot.bar('num_group1', \n                                    'number',\n                                    title=name + ' (bar)',\n                                    width=400,\n                                    shared_axes=False,\n                                    line_color=None,\n                                    grid=True,\n                                    yformatter=frmt_big_numb).opts(active_tools=['pan'])\n        # otherwise histogram\n        else:\n            df = num_gr_per_id[name].select('num_group1')\n            plot_grid = df.plot.hist(bins=20,\n                                     title=name + ' (hist)',\n                                     width=400,\n                                     shared_axes=False,\n                                     line_color=None,\n                                     grid=True,\n                                     yformatter=frmt_big_numb).opts(active_tools=['pan'])\n    else:\n        if len(df) < 25:\n            plot_grid += df.plot.bar('num_group1', \n                                     'number',\n                                     title=name + ' (bar)',\n                                     width=400,\n                                     shared_axes=False,\n                                     line_color=None,\n                                     grid=True,\n                                     yformatter=frmt_big_numb).opts(active_tools=['pan'])\n        else:\n            df = num_gr_per_id[name].select('num_group1')\n            plot_grid += df.plot.hist(bins=20,\n                                      title=name + ' (hist)',\n                                      width=400,\n                                      shared_axes=False,\n                                      line_color=None,\n                                      grid=True,\n                                      yformatter=frmt_big_numb).opts(active_tools=['pan'])\n\ndisplay(plot_grid.cols(3))","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:05:17.362448Z","iopub.execute_input":"2024-04-28T17:05:17.363318Z","iopub.status.idle":"2024-04-28T17:05:19.247071Z","shell.execute_reply.started":"2024-04-28T17:05:17.363274Z","shell.execute_reply":"2024-04-28T17:05:19.245772Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Further we will be based on the assumption that the same case_id cannot occur more than once in a group\n# Let's check this assumption\ndef check_rep_caseid_in_group(df):\n    \"\"\"\n    Сheck if case_id can occur several times in num_group\n    \"\"\"\n    df = df.group_by('num_group1', 'case_id') \\\n           .agg((pl.col('case_id').count()).alias('rep_caseid_in_group')) \\\n           .filter(pl.col('rep_caseid_in_group')>1).collect()\n\n    return df.shape[0]\n\nprint('Number of cases when the same case_id occurs several times in a group:')\nfor df, group in table_iterator(struct_dict):\n    print(f' - {group.split(\"/\")[-1]:<25} {check_rep_caseid_in_group(df)} cases')","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:05:24.268728Z","iopub.execute_input":"2024-04-28T17:05:24.269176Z","iopub.status.idle":"2024-04-28T17:05:33.592182Z","shell.execute_reply.started":"2024-04-28T17:05:24.269120Z","shell.execute_reply":"2024-04-28T17:05:33.591278Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def aggregate_nulls(path):\n    \n    id = split(path)[-1]\n    feat_def = pl.read_csv(MAIN_PATH + '/feature_definitions.csv')\n    \n    df = pl.scan_parquet(path+'.parquet')\n    df_dtypes = pl.DataFrame({'features':df.columns, 'dtypes':[str(x) for x in df.dtypes]})\n    df = df.collect()\n    \n    df_shapes = pl.DataFrame({'file':id, 'n':df.shape[0], 'm':df.shape[1]}) \\\n                        .with_columns((pl.col('n')*pl.col('m')).alias('cells_numb'))\n    \n    df = df.null_count().select(pl.all().exclude(['case_id', 'num_group1', 'num_group2']), \n                                pl.lit(id).alias('file'),\n                               )\n    \n    df_nulls = df.melt('file', variable_name='features', value_name='nulls_numb') \\\n                 .with_columns(pl.col('features').str.slice(-1).alias('type')) \\\n                 .join(df_dtypes, on='features', how='left') \\\n                 .join(feat_def, left_on='features', right_on='Variable', how='left')\n    \n    return df_nulls, df_shapes\n\ndef take_description(feat):\n    \"\"\"\n    Splits feature description into several lines of a given length\n    \"\"\"\n    maxlen = 70 # max length of feature descriptor line\n    # take a feature description\n    xlabel = feat_defs.filter(pl.col('Variable') == feat).select('Description').item()[:-1]\n    # there are many long feature descriptors, we should split them into several lines\n    if len(xlabel) - maxlen > 10: # to avoid hyphenation of one-two letters\n        xlabel = ''.join([xlabel[x:x+maxlen]+'\\n' for x in range(0, len(xlabel), maxlen)])[:-1]\n    return xlabel","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:05:37.378334Z","iopub.execute_input":"2024-04-28T17:05:37.378776Z","iopub.status.idle":"2024-04-28T17:05:37.394184Z","shell.execute_reply.started":"2024-04-28T17:05:37.378745Z","shell.execute_reply":"2024-04-28T17:05:37.392844Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# table with feature definitions\nfeat_defs = pl.read_csv(MAIN_PATH + '/feature_definitions.csv')","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:05:41.207970Z","iopub.execute_input":"2024-04-28T17:05:41.209296Z","iopub.status.idle":"2024-04-28T17:05:41.217870Z","shell.execute_reply.started":"2024-04-28T17:05:41.209257Z","shell.execute_reply":"2024-04-28T17:05:41.216853Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for i, path in tqdm(enumerate(files), total=(len(files))):\n    \n    nulls, shapes = aggregate_nulls(path)\n    \n    if i==0:\n        df_nulls, df_shapes = nulls, shapes\n    else:\n        df_nulls = df_nulls.vstack(nulls)\n        df_shapes = df_shapes.vstack(shapes)\n    \ndel nulls, shapes\n\n# replace file names with group names where necessary.\ndf_nulls = df_nulls.with_columns(pl.when(pl.col('file').str.split(by=\"_\").list[-2].str.contains(r'[0-9]'))\n                                 .then(pl.col('file').str.strip_suffix('_' + pl.col('file').str.split(by='_').list[-1]))\n                                 .otherwise(pl.col('file')).alias('file')\n                                )\n# aggregate by categorical features\ndf_nulls = df_nulls.group_by(cs.string()).agg(pl.col('nulls_numb').sum())","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:05:44.494633Z","iopub.execute_input":"2024-04-28T17:05:44.495434Z","iopub.status.idle":"2024-04-28T17:06:01.279448Z","shell.execute_reply.started":"2024-04-28T17:05:44.495389Z","shell.execute_reply":"2024-04-28T17:06:01.277551Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# making a pivot table with the number of features by tables, custom types and dtypes\nfeat_pivot = df_nulls.pivot(index=['file', 'type'], columns='dtypes', values=['dtypes'], aggregate_function='len').fill_null(strategy='zero')\nfeat_pivot.head()","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:06:03.117564Z","iopub.execute_input":"2024-04-28T17:06:03.118068Z","iopub.status.idle":"2024-04-28T17:06:03.134794Z","shell.execute_reply.started":"2024-04-28T17:06:03.118028Z","shell.execute_reply":"2024-04-28T17:06:03.133519Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# making a lists and dict of a file and groups id (not path) for convenience\nfiles_id, groups_id, struct_dict_id = file_list(path_list, depth=1, take_id=True)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:06:05.989728Z","iopub.execute_input":"2024-04-28T17:06:05.990533Z","iopub.status.idle":"2024-04-28T17:06:05.997346Z","shell.execute_reply.started":"2024-04-28T17:06:05.990496Z","shell.execute_reply":"2024-04-28T17:06:05.995700Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"box = []\n\nindic = pn.indicators.Number(\n    title_size = '8pt',\n    colors=[(np.inf, 'white')],\n    font_size='36pt',\n    format = '{value:,}',\n    styles={'background':'#2980B9', \n            'text-align':'center',\n            'padding':'20px 0 0 0',\n           },\n)\n\nfor name in groups_id:\n    grid = pn.GridSpec(width=600, height=300)\n    table = feat_pivot.filter(file=name)\n    by_dtype = table.select(cs.numeric().sum())\n    all_types = by_dtype.sum_horizontal().item()\n    grid[0, 0] = indic.clone(value=all_types, name=name)\n    grid[0:2,1:5] = table.plot.bar(x='type', \n                                   stacked=True,\n                                   legend='top_right', \n                                   cmap='Pastel1',\n                                  ).opts(toolbar=None, \n                                         active_tools=['pan'],\n                                         line_color=None,\n                                         labelled=[False,False],\n                                        )\n\n    box.append(grid)\n    \npn.WidgetBox('### Relation of custom types and dtypes', \n             pn.GridBox(*box, styles={'background':'#a7cae5'}, ncols=2)\n            )","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:06:10.566227Z","iopub.execute_input":"2024-04-28T17:06:10.566728Z","iopub.status.idle":"2024-04-28T17:06:14.233167Z","shell.execute_reply.started":"2024-04-28T17:06:10.566693Z","shell.execute_reply":"2024-04-28T17:06:14.231260Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# of course in a \"lazy\" format, to save memory\napplprev_df = lazy_df_prep(struct_dict[f'{TRAIN_PATH_PARQUET}/train_applprev_1'])\nperson_df = lazy_df_prep(struct_dict[f'{TRAIN_PATH_PARQUET}/train_person_1'])\ncredit_bureau_a_df = lazy_df_prep(struct_dict[f'{TRAIN_PATH_PARQUET}/train_credit_bureau_a_1'])","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:06:47.582187Z","iopub.execute_input":"2024-04-28T17:06:47.582675Z","iopub.status.idle":"2024-04-28T17:06:47.610227Z","shell.execute_reply.started":"2024-04-28T17:06:47.582641Z","shell.execute_reply":"2024-04-28T17:06:47.609335Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def gen_conv_types(df, date_to_physical=True):\n    \"\"\"\n    General features conversion\n    \"\"\"\n    \n    # the features of custom type \"D\" are imported as strings, let's give them the correct type - date\n    date_columns = [name for name in df.columns if name[-1]=='D']\n    if date_columns:\n        df = df.with_columns(pl.col(date_columns).cast(pl.Date, strict=False))\n        \n        # ...and immediately change the date to a number (if you want)\n        if date_to_physical:\n            df = df.with_columns(cs.temporal().to_physical())\n    \n    # shrink numeric columns to minimum available dtype\n    # commented out due to problems merging tables\n    #df = df.with_columns(cs.numeric().shrink_dtype()) \n\n    return df","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:06:48.764860Z","iopub.execute_input":"2024-04-28T17:06:48.768459Z","iopub.status.idle":"2024-04-28T17:06:48.778007Z","shell.execute_reply.started":"2024-04-28T17:06:48.768414Z","shell.execute_reply":"2024-04-28T17:06:48.776454Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# converting types\nperson_df_conv = gen_conv_types(person_df, date_to_physical=True)\napplprev_df_conv = gen_conv_types(applprev_df, date_to_physical=True)\ncredit_bureau_a_df_conv = gen_conv_types(credit_bureau_a_df, date_to_physical=True)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:06:59.389640Z","iopub.execute_input":"2024-04-28T17:06:59.390119Z","iopub.status.idle":"2024-04-28T17:06:59.398957Z","shell.execute_reply.started":"2024-04-28T17:06:59.390086Z","shell.execute_reply":"2024-04-28T17:06:59.397259Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def make_df_with_fullness(df):\n    \"\"\"\n    Creates a table containing the percentage of fullness in features by group\n    \"\"\"\n    # calculation of percentage of not null values by num_group1 for each feature\n    df_1 = df.group_by('num_group1').agg(\n            (pl.all().exclude(['case_id', 'num_group1', 'num_group2']).count()\n             .truediv(pl.col('num_group1').count())\n            )*100\n        ).melt(id_vars='num_group1', variable_name='feature', value_name='prc_full_in_group')\n    \n    # given that case_id can only occur once in num_group (we've already checked it above), \n    # dividing by case_numb gives the number of not null cases in the group\n    df_2 = df.group_by('num_group1').agg(\n            (pl.all().exclude(['case_id', 'num_group1', 'num_group2']).count()\n             .truediv(case_numb)\n            )*100\n        ).melt(id_vars='num_group1', variable_name='feature', value_name='prc_cases_in_group')\n    \n    # joining result\n    df = df_1.join(df_2, on=['num_group1', 'feature'])\n    \n    return df\n\ndef nulls_by_feat_n_gr(df, height=600, width=1200, rot=60, scale=2, tab_desc=True):\n    \"\"\"\n    Visualisation of the percentage of nulls for each num_group and feature \n    \"\"\"\n    # number of unuque values of num_group1 feature\n    num_group = df.select(pl.col('num_group1').unique()).collect().to_numpy().squeeze()\n    \n    df = make_df_with_fullness(df).collect(streaming=True)\n    \n    # some stuff for beauty\n    tooltips = [('Feature', '@feature'),\n                ('Group', '@num_group1'), \n                ('Cases in group', '@prc_cases_in_group%'), \n                ('Non-null in group', '@prc_full_in_group%')]\n    \n    hover = HoverTool(tooltips=tooltips)\n    \n    scatter = (df.plot.scatter(x='feature', \n                               y='num_group1', \n                               s='prc_full_in_group', \n                               yticks=num_group,  \n                               scale=scale, \n                               rot=rot,\n                               grid=True, \n                               height=height, width=width, \n                               color='prc_cases_in_group', cmap='tab10',\n                              ).opts(labelled=[False], \n                                     active_tools=['pan'],\n                                     tools=[hover],\n                                    )\n               )\n    # if you want you may get a table of features description for the same price\n    if tab_desc:\n        table = df.select(pl.col('feature').unique(maintain_order=True)).join(feat_defs, left_on='feature', right_on='Variable') \\\n                .plot.table(columns=['feature', 'Description'], width=width, fit_columns=True, editable=True)\n        display((scatter+table).cols(1))\n    else:\n        display(scatter)\n        ","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:07:02.174586Z","iopub.execute_input":"2024-04-28T17:07:02.175087Z","iopub.status.idle":"2024-04-28T17:07:02.194921Z","shell.execute_reply.started":"2024-04-28T17:07:02.175047Z","shell.execute_reply":"2024-04-28T17:07:02.193481Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nulls_by_feat_n_gr(person_df_conv)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:07:09.515303Z","iopub.execute_input":"2024-04-28T17:07:09.515736Z","iopub.status.idle":"2024-04-28T17:07:14.307769Z","shell.execute_reply.started":"2024-04-28T17:07:09.515705Z","shell.execute_reply":"2024-04-28T17:07:14.306262Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nulls_by_feat_n_gr(applprev_df_conv, height=700)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:07:18.180516Z","iopub.execute_input":"2024-04-28T17:07:18.181091Z","iopub.status.idle":"2024-04-28T17:07:33.451437Z","shell.execute_reply.started":"2024-04-28T17:07:18.181048Z","shell.execute_reply":"2024-04-28T17:07:33.450072Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nulls_by_feat_n_gr(credit_bureau_a_df_conv.filter(pl.col('num_group1')<50), height=1200, width=1500, rot=90, scale=1)\n# nulls_by_feat_n_gr(credit_bureau_a_df_conv.filter(pl.col('num_group1').is_between(50,100)), height=1200, width=1500, rot=90, scale=1)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:07:33.453703Z","iopub.execute_input":"2024-04-28T17:07:33.454055Z","iopub.status.idle":"2024-04-28T17:09:07.446545Z","shell.execute_reply.started":"2024-04-28T17:07:33.454027Z","shell.execute_reply":"2024-04-28T17:09:07.444076Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# create a table containing the percentage of fullness in features by group \nsel_feat_df = make_df_with_fullness(person_df_conv).collect()\n# these are features for group=0\nfeatures = sel_feat_df.filter((pl.col('num_group1')==0) & (pl.col('prc_full_in_group')>90)).select('feature').to_numpy().squeeze()","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:09:13.212417Z","iopub.execute_input":"2024-04-28T17:09:13.212904Z","iopub.status.idle":"2024-04-28T17:09:16.646263Z","shell.execute_reply.started":"2024-04-28T17:09:13.212870Z","shell.execute_reply":"2024-04-28T17:09:16.644425Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def features_selector(df, features, dtype_selector):\n    \"\"\"\n    Selects the intersection of the passed list of features and the passed type\n    \"\"\"\n    return df.select('case_id', 'num_group1', *features).select('case_id', 'num_group1', dtype_selector)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:09:16.649524Z","iopub.execute_input":"2024-04-28T17:09:16.650181Z","iopub.status.idle":"2024-04-28T17:09:16.659719Z","shell.execute_reply.started":"2024-04-28T17:09:16.650109Z","shell.execute_reply":"2024-04-28T17:09:16.658114Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# sepatation by dtypes\nperson_df_num = features_selector(person_df_conv, features, (cs.numeric()-cs.matches('case_id|num_group1'))).filter(pl.col('num_group1')==0)\nperson_df_bool = features_selector(person_df_conv, features, cs.boolean()).filter(pl.col('num_group1')==0)\nperson_df_str = features_selector(person_df_conv, features, cs.string()).filter(pl.col('num_group1')==0)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:09:16.661734Z","iopub.execute_input":"2024-04-28T17:09:16.662558Z","iopub.status.idle":"2024-04-28T17:09:16.679627Z","shell.execute_reply.started":"2024-04-28T17:09:16.662520Z","shell.execute_reply":"2024-04-28T17:09:16.678571Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# what have we already got?\nperson_df_num.collect().describe()","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:09:16.681909Z","iopub.execute_input":"2024-04-28T17:09:16.682644Z","iopub.status.idle":"2024-04-28T17:09:17.360233Z","shell.execute_reply.started":"2024-04-28T17:09:16.682609Z","shell.execute_reply":"2024-04-28T17:09:17.358839Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# exclude invariants\nfeatures = person_df_num.std().melt().filter(pl.col('value')==0).select('variable').collect().to_numpy().squeeze()\nperson_df_num = person_df_num.select(pl.all().exclude(*features))\nperson_df_num.collect().head()","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:09:20.755821Z","iopub.execute_input":"2024-04-28T17:09:20.758860Z","iopub.status.idle":"2024-04-28T17:09:21.662837Z","shell.execute_reply.started":"2024-04-28T17:09:20.758747Z","shell.execute_reply":"2024-04-28T17:09:21.661874Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def quant_transf(df, exclude=['case_id', 'num_group1', 'num_group2']):\n    \"\"\"\n    Apply Quantile Transformer to all columns of a dataframe except those excluded\n    \"\"\"\n    \n    keys = [key for key in df.columns if key in exclude]\n    vals = [key for key in df.columns if key not in exclude]\n    \n    rng = np.random.RandomState(42)\n    qt = QuantileTransformer(output_distribution='normal', random_state=rng)\n    \n    vals_np = qt.fit_transform(df.select(vals).collect())\n    \n    df = df.select(keys).collect().hstack(pl.DataFrame(vals_np, schema=vals))\n    \n    return df.lazy()\n\n# let's also get some staff from Part I for beautiful visualisation\ndef subplot_format(feat_list):\n    \"\"\"\n    Sets the number of columns and their height and width on a plot figure\n    (these settings are specific to my monitor)\n    \"\"\"\n    len_feat_list = len(feat_list)\n    if len_feat_list == 1:\n        cols = 1\n        width = 1000\n        height = 300\n    elif len_feat_list < 7:\n        cols = 2\n        width = 600\n        height = 300\n    else: \n        cols = 3\n        width = 400\n        height = 250\n    return cols, width, height\n\ndef plot_violin(df, feat_list, **ext_plot_options):\n    \"\"\"\n    Plots histogramms for non-categorical fratures\n    \"\"\"\n    df = df.collect()\n    cols, width, height = subplot_format(feat_list)\n    \n    for i, feat in enumerate(feat_list):\n        \n        xlabel = take_description(feat)\n        \n        if not i:\n            grid = df.plot.violin(feat, \n                                 width=width, \n                                 height=height,\n                                 title=feat,\n                                 xlabel = xlabel,\n                                 fontsize={'xlabel':8},\n                                 violin_line_color=None,\n                                 **ext_plot_options,\n                                ).opts(active_tools=['pan']).sort()\n        else:\n            grid += df.plot.violin(feat, \n                                 width=width, \n                                 height=height,\n                                 title=feat,\n                                 xlabel = xlabel,\n                                 fontsize={'xlabel':8},\n                                 violin_line_color=None,\n                                 **ext_plot_options,\n                                ).opts(active_tools=['pan']).sort()\n    return grid","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:11:02.118557Z","iopub.execute_input":"2024-04-28T17:11:02.119098Z","iopub.status.idle":"2024-04-28T17:11:02.140920Z","shell.execute_reply.started":"2024-04-28T17:11:02.119053Z","shell.execute_reply":"2024-04-28T17:11:02.139630Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# data before transformation\nbefor = plot_violin(person_df_num, \n                    [name for name in person_df_num.columns if name not in ['case_id', 'num_group1']], \n                    yformatter=frmt_big_numb, \n                    shared_axes=False).cols(1)\n\n# data after transformation\nperson_df_num_q = person_df_num.pipe(quant_transf)\nafter = plot_violin(person_df_num_q,\n                    [name for name in person_df_num_q.columns if name not in ['case_id', 'num_group1']],\n                    yformatter=frmt_big_numb,\n                    shared_axes=False).cols(1)\n\nstyles = {\n    'background-color':'#F6F6F6', 'width':'100%',\n    'font-size':'24px', 'font-weight':'400', 'text_align':'center'\n}\n\ngrid = pn.GridSpec(width=1200, height=800)\ngrid[:, 0] = pn.Column(pn.pane.HTML('Befor', styles=styles), befor)\ngrid[:, 1] = pn.Column(pn.pane.HTML('After', styles=styles), after)\n\ndisplay(grid)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:11:02.142508Z","iopub.execute_input":"2024-04-28T17:11:02.143737Z","iopub.status.idle":"2024-04-28T17:11:26.108160Z","shell.execute_reply.started":"2024-04-28T17:11:02.143699Z","shell.execute_reply":"2024-04-28T17:11:26.106681Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def numb_rate_of_unique_table(df, feature, width, height, title):\n    \"\"\"\n    Create table with the number and percentage of unique values for categorical features\n    \"\"\"\n    table = df.select(pl.col(feature), pl.col(feature).count().alias('total')) \\\n              .group_by([feature, 'total']).len().sort('len', descending=True) \\\n              .filter(pl.col(feature).is_not_null()) \\\n              .select(pl.col(feature), pl.col('len'),((pl.col('len')/pl.col('total'))*100).round(2).alias('rate')).collect() \\\n              .plot.table(width=width, \n                          height=height,\n                          title=title,\n                          fontsize={'title':8},\n                          yformatter=frmt_big_numb)\n    return table\n\ndef plot_cat_features(df, feat_list):\n    \"\"\"\n    Plots data for categorical features\n    \"\"\"\n    cols, width, height = subplot_format(feat_list)\n    \n    # prepare a table for each categorical feature\n    for i, feat in enumerate(feat_list):\n        desc = take_description(feat)\n        if not i:\n            grid = numb_rate_of_unique_table(df, feat, width, height, desc)\n        else:\n            grid += numb_rate_of_unique_table(df, feat, width, height, desc)\n\n    # display a graph of unique values per feature\n    display(df.select(pl.col(feat_list).drop_nulls().n_unique()).collect() \n              .plot.barh(width=1000, \n                         xlabel='features', \n                         ylabel='number or unique values',\n                         title=f'Number of unique values per categorical feature'\n                        )\n           )\n    # display a tables of unique values with number and rate of each value\n    display(grid.cols(cols)) if cols>1 else display(grid)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:11:26.110677Z","iopub.execute_input":"2024-04-28T17:11:26.111175Z","iopub.status.idle":"2024-04-28T17:11:26.129935Z","shell.execute_reply.started":"2024-04-28T17:11:26.111113Z","shell.execute_reply":"2024-04-28T17:11:26.128095Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_cat_features(person_df_str, [name for name in person_df_str.columns if name not in ['case_id', 'num_group1']])","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:09:46.235307Z","iopub.execute_input":"2024-04-28T17:09:46.236463Z","iopub.status.idle":"2024-04-28T17:09:51.261814Z","shell.execute_reply.started":"2024-04-28T17:09:46.236421Z","shell.execute_reply":"2024-04-28T17:09:51.260199Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# custom categorical features transformer\ndef hash_n_log_transformer(expr):\n    \"\"\"\n    Hashing feature -> get a list of uint64 (even if there is single number);\n    then summarize -> get uint64 number (huge);\n    then logarithmize - get a much smaller number! \n    \"\"\"\n    return expr.hash(42).sum().log()","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:11:26.132300Z","iopub.execute_input":"2024-04-28T17:11:26.132871Z","iopub.status.idle":"2024-04-28T17:11:26.146791Z","shell.execute_reply.started":"2024-04-28T17:11:26.132814Z","shell.execute_reply":"2024-04-28T17:11:26.145038Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# let'p put it all together\nperson_df_str = person_df_str.select(pl.all().exclude('num_group1')) # throw out num_group1 \nperson_df_str_hl = person_df_str.group_by('case_id').agg(pl.all().exclude('case_id').pipe(hash_n_log_transformer)).pipe(quant_transf)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:11:26.148540Z","iopub.execute_input":"2024-04-28T17:11:26.149013Z","iopub.status.idle":"2024-04-28T17:11:33.576992Z","shell.execute_reply.started":"2024-04-28T17:11:26.148973Z","shell.execute_reply":"2024-04-28T17:11:33.575542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_violin(person_df_str_hl,\n            [name for name in person_df_str_hl.columns if name not in ['case_id', 'num_group1']],\n            yformatter=frmt_big_numb,\n            shared_axes=False).cols(3)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:11:33.579416Z","iopub.execute_input":"2024-04-28T17:11:33.579934Z","iopub.status.idle":"2024-04-28T17:12:36.994386Z","shell.execute_reply.started":"2024-04-28T17:11:33.579893Z","shell.execute_reply":"2024-04-28T17:12:36.993369Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_cat_features(person_df_bool, [name for name in person_df_bool.columns if name not in ['case_id', 'num_group1']])","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:12:42.631535Z","iopub.execute_input":"2024-04-28T17:12:42.632022Z","iopub.status.idle":"2024-04-28T17:12:43.540844Z","shell.execute_reply.started":"2024-04-28T17:12:42.631988Z","shell.execute_reply":"2024-04-28T17:12:43.539171Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def exclude_invariants(df, dtype={'numeric', 'string', 'boolean'}, log=True):\n    \"\"\"\n    Excludes invariant features\n    \"\"\"\n    \n    if dtype in ['numeric', 'boolean']:\n        # for numeric and boolean features, exclusion is based on standard deviation\n        features = df.std().melt().filter(pl.col('value')==0).select('variable').collect().to_numpy().squeeze(axis=1)\n    else:\n        # for string - on number of unique values\n        features = df.select(pl.all().drop_nulls().n_unique()).melt() \\\n                     .filter(pl.col('value')==1).select('variable').collect().to_numpy().squeeze(axis=1)\n\n    if features.size > 0:\n        df = df.select(pl.all().exclude(features))\n        if log: print(f'    [Excluded invariant numeric features ({len(features)}): {features}]')\n    else:\n        if log: print(f'    [There are no invariant {dtype} features]')\n            \n    return df\n\ndef hash_n_str_transformer(expr):\n    \"\"\"\n    Hashing feature -> get a list of uint64 (even if there is single number);\n    then summarize -> get uint64 number (huge);\n    then cast string type - and get the code for our aggregated category! \n    \"\"\"\n    return expr.hash(42).sum().cast(pl.String)\n\ndef numeric_features(df, log=True):\n    \"\"\"\n    Processes numeric features: excludes invariants, applies quantile transformation\n    \"\"\"\n    \n    # exclude invariants\n    df = exclude_invariants(df, dtype='numeric', log=log)\n    numb_feat_proc = len([clmn for clmn in df.columns if clmn not in ['case_id', 'num_group1']])\n    \n    if log: print(f'    [{numb_feat_proc} features processing...]')\n    # apply quantile transform (!! I commented this out later as the LightGBM liked the skewed distribution better !!)\n    df = df.group_by('case_id').agg(pl.all().exclude('case_id', 'num_group1').sum())#.pipe(quant_transf)\n    \n    return df\n\ndef string_features(df, log=True):\n    \"\"\"\n    Processes string features: excludes invariants, applies custom and quantile transformation\n    \"\"\"\n    \n    # exclude invariants\n    df = exclude_invariants(df, dtype='string', log=log)\n    numb_feat_proc = len([clmn for clmn in df.columns if clmn not in ['case_id', 'num_group1']])\n    \n    if log: print(f'    [{numb_feat_proc} features processing...]')\n    # apply custom transform\n    df = df.group_by('case_id').agg(pl.all().exclude('case_id').pipe(hash_n_str_transformer))\n    \n    return df\n\ndef boolean_features(df, log=True):\n    \"\"\"\n    Processes boolean features: excludes invariants, applies custom aggregation\n    \"\"\"\n    \n    # exclude invariants\n    df = exclude_invariants(df, dtype='boolean', log=log)\n    numb_feat_proc = len([clmn for clmn in df.columns if clmn not in ['case_id', 'num_group1']])\n    \n    if log: print(f'    [{numb_feat_proc} features processing...]')\n    # aggregation\n    if 'num_group1' in df.columns:\n        # sum up all the boolean features and take the maximum num_group1\n        df = df.group_by('case_id').agg(pl.all().exclude('case_id', 'num_group1').sum(), \n                                        pl.col('num_group1').max()\n                                       )\n        # divide the result of summing the boolean features by max(1, num_group1)\n        df = df.with_columns(pl.all().exclude('case_id', 'num_group1') / \n                             pl.max_horizontal(pl.lit(1), pl.col('num_group1'))\n                            )\n    else:\n        # sum up all the boolean features only \n        df = df.group_by('case_id').agg(pl.all().exclude('case_id', 'num_group1').sum())\n    \n    return df\n\ndef features_transform_depth_1(df, rate_fullness = 0.9, log=True):\n    \"\"\"\n    Transforming tables of depth=1:\n     - feature selection by rate_fullness\n     - feature separation by dtypes\n     - processing all dtypes separately and joining the results\n    \"\"\"\n    if log: print('[Types conversion...]')\n    # general features conversion\n    df = gen_conv_types(df, date_to_physical=True)\n    \n    if log: print('[Features selection...]')\n    numb_feat_start = len([clmn for clmn in df.columns if clmn not in ['case_id', 'num_group1']])\n    # create a table containing the rate of fullness by features\n    sel_feat_df = df.select(pl.all().exclude(['case_id', 'num_group1', 'num_group2']).count()\n                            .truediv(pl.col('num_group1').count())\n                           ).melt()\n    \n    # these are features with a given fullness\n    features = sel_feat_df.filter(pl.col('value') > rate_fullness).select('variable').collect().to_numpy().squeeze()\n    numb_feat_end = len([clmn for clmn in features if clmn not in ['case_id', 'num_group1']])\n    if log: print(f'  [{numb_feat_start - len(features)} of {numb_feat_start} features were excluded because they contained more than {rate_fullness:.0%} nulls]')\n    \n    if log: print('[Features separation...]')\n    # select features by dtypes and fullness\n    df_num = features_selector(df, features, (cs.numeric()-cs.matches('case_id|num_group1')))\n    df_str = features_selector(df, features, cs.string())\n    df_bool = features_selector(df, features, cs.boolean())\n    \n    if log: print('[Features processing...]')\n    \n    # features processing\n    #numeric\n    if log: print('  [Numeric...]')\n    num_avail = False\n    if len(df_num.columns) > 2: # not only 'case_id' and 'num_group1'\n        df = numeric_features(df_num, log=log).drop('num_group1')\n        num_avail = True # are there numeric features?\n    else:\n        if log: print('    [There are no numeric features]')\n    \n    # string\n    if log: print('  [String...]')\n    str_avail = False\n    if len(df_str.columns) > 2:\n        str_avail = True\n        if num_avail:\n            df = df.join(string_features(df_str, log=log).drop('num_group1'), on='case_id', how='outer_coalesce')\n        else:\n            df = string_features(df_str, log=log).drop('num_group1')\n    else:\n        if log: print('    [There are no string features]')\n    \n    #boolean\n    if log: print('  [Boolean...]')\n    if len(df_bool.columns) > 2:\n        if num_avail or str_avail:\n            df = df.join(boolean_features(df_bool, log=log), on='case_id', how='outer_coalesce')\n        else:\n            df = boolean_features(df_bool, log=log)\n    else:\n        if log: print('    [There are no boolean features]')\n    \n    return df","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:12:43.544656Z","iopub.execute_input":"2024-04-28T17:12:43.545225Z","iopub.status.idle":"2024-04-28T17:12:44.548736Z","shell.execute_reply.started":"2024-04-28T17:12:43.545177Z","shell.execute_reply":"2024-04-28T17:12:44.547277Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n# feel free to change rate_fullness\n# I started with a coefficient of 0.9 and gradually reduced it, which improved the result, even though I don’t process nulls in any way\nrate_fullness=0.6\napplprev_df_transf = features_transform_depth_1(applprev_df, rate_fullness=rate_fullness)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:12:44.551703Z","iopub.execute_input":"2024-04-28T17:12:44.552140Z","iopub.status.idle":"2024-04-28T17:13:00.679612Z","shell.execute_reply.started":"2024-04-28T17:12:44.552106Z","shell.execute_reply":"2024-04-28T17:13:00.678639Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ncredit_bureau_a_df_transf = features_transform_depth_1(credit_bureau_a_df, rate_fullness=rate_fullness)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:13:00.682166Z","iopub.execute_input":"2024-04-28T17:13:00.682932Z","iopub.status.idle":"2024-04-28T17:13:38.026858Z","shell.execute_reply.started":"2024-04-28T17:13:00.682886Z","shell.execute_reply":"2024-04-28T17:13:38.025160Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nperson_df_transf_ng_0 = features_transform_depth_1(person_df.filter(pl.col('num_group1')==0), rate_fullness=rate_fullness)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:13:38.028670Z","iopub.execute_input":"2024-04-28T17:13:38.029058Z","iopub.status.idle":"2024-04-28T17:13:41.607264Z","shell.execute_reply.started":"2024-04-28T17:13:38.029027Z","shell.execute_reply":"2024-04-28T17:13:41.606262Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nperson_df_transf_ng_rest = features_transform_depth_1(person_df.filter(pl.col('num_group1')>0), rate_fullness=rate_fullness)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:13:41.609888Z","iopub.execute_input":"2024-04-28T17:13:41.610622Z","iopub.status.idle":"2024-04-28T17:13:44.318695Z","shell.execute_reply.started":"2024-04-28T17:13:41.610588Z","shell.execute_reply":"2024-04-28T17:13:44.316950Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data = (base.lazy().join(person_df_transf_ng_0, on='case_id', how='left')\n        .join(person_df_transf_ng_rest, on='case_id', how='left')\n        .join(applprev_df_transf, on='case_id', how='left')\n        .join(credit_bureau_a_df_transf.select(pl.all().exclude('num_group1')), on='case_id', how='left')\n       ).collect()","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:13:44.321091Z","iopub.execute_input":"2024-04-28T17:13:44.321636Z","iopub.status.idle":"2024-04-28T17:14:12.918686Z","shell.execute_reply.started":"2024-04-28T17:13:44.321593Z","shell.execute_reply":"2024-04-28T17:14:12.917284Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"💎 LightGBM Feature Importance - All Datasets 💎\n\n\n","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport polars as pl\nimport numpy as np\nimport lightgbm as lgb\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import roc_auc_score\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nfrom sklearn.preprocessing import StandardScaler\n\npd.set_option('display.max_columns', None)\n\ndataPath = \"/kaggle/input/home-credit-credit-risk-model-stability/\"","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:14:16.509232Z","iopub.execute_input":"2024-04-28T17:14:16.510744Z","iopub.status.idle":"2024-04-28T17:14:16.676895Z","shell.execute_reply.started":"2024-04-28T17:14:16.510697Z","shell.execute_reply":"2024-04-28T17:14:16.675265Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def set_table_dtypes(df: pl.DataFrame) -> pl.DataFrame:\n    # implement here all desired dtypes for tables\n    # the following is just an example\n    for col in df.columns:\n        # last letter of column name will help you determine the type\n        if col[-1] in (\"P\", \"A\"):\n            df = df.with_columns(pl.col(col).cast(pl.Float64).alias(col))\n    return df\n\ndef convert_strings(df: pd.DataFrame) -> pd.DataFrame:\n    for col in df.columns:  \n        if df[col].dtype.name in ['object', 'string']:\n            df[col] = df[col].astype(\"string\").astype('category')\n            current_categories = df[col].cat.categories\n            new_categories = current_categories.to_list() + [\"Unknown\"]\n            new_dtype = pd.CategoricalDtype(categories=new_categories, ordered=True)\n            df[col] = df[col].astype(new_dtype)\n    return df\n\ndef from_polars_to_pandas(case_ids: pl.DataFrame) -> pl.DataFrame:\n    return (\n        data.filter(pl.col(\"case_id\").is_in(case_ids))[[\"case_id\", \"WEEK_NUM\", \"target\"]].to_pandas(),\n        data.filter(pl.col(\"case_id\").is_in(case_ids))[cols_pred].to_pandas(),\n        data.filter(pl.col(\"case_id\").is_in(case_ids))[\"target\"].to_pandas()\n    )\n\ndef summary(df):\n    summ = pd.DataFrame(df.dtypes, columns=['data type'])\n    summ['#total'] = df.shape[0]\n    summ['#missing'] = df.isnull().sum().values \n    summ['%missing'] = df.isnull().sum().values / len(df)* 100\n    summ['#unique'] = df.nunique().values\n    summ['#duplicates'] = summ['#total'] - summ['#unique']\n    desc = pd.DataFrame(df.describe(include='all').transpose())\n    summ['min'] = desc['min'].values\n    summ['max'] = desc['max'].values\n    return summ\n\ndef gini_stability(base, w_fallingrate=88.0, w_resstd=-0.5):\n    gini_in_time = base.loc[:, [\"WEEK_NUM\", \"target\", \"score\"]]\\\n        .sort_values(\"WEEK_NUM\")\\\n        .groupby(\"WEEK_NUM\")[[\"target\", \"score\"]]\\\n        .apply(lambda x: 2*roc_auc_score(x[\"target\"], x[\"score\"])-1).tolist()\n    \n    x = np.arange(len(gini_in_time))\n    y = gini_in_time\n    a, b = np.polyfit(x, y, 1)\n    y_hat = a*x + b\n    residuals = y - y_hat\n    res_std = np.std(residuals)\n    avg_gini = np.mean(gini_in_time)\n    return avg_gini + w_fallingrate * min(0, a) + w_resstd * res_std\n\ndef drop_outliers(df, field_name):\n    iqr = 1.5 * (np.percentile(df[field_name], 75) - np.percentile(df[field_name], 25))\n    df.drop(df[df[field_name] > (iqr + np.percentile(df[field_name], 75))].index, inplace=True)\n    df.drop(df[df[field_name] < (np.percentile(df[field_name], 25) - iqr)].index, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:14:17.121652Z","iopub.execute_input":"2024-04-28T17:14:17.122121Z","iopub.status.idle":"2024-04-28T17:14:17.148803Z","shell.execute_reply.started":"2024-04-28T17:14:17.122085Z","shell.execute_reply":"2024-04-28T17:14:17.147116Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#############################################################################################\n# TRAINING DATA SET\n#############################################################################################\n\n### BASE TABLE\ntrain_basetable = pl.read_csv(dataPath + \"csv_files/train/train_base.csv\")\n\n### FEATURES\ntrain_feature_set = pl.concat(\n    [\n        pl.read_csv(dataPath + \"csv_files/train/train_static_0_0.csv\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/train/train_static_0_1.csv\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\n\n#############################################################################################\n# JOIN TABLES TOGETHER\n#############################################################################################\ndata = train_basetable.join( \n    train_feature_set, how=\"left\", on=\"case_id\"\n)\n\n#############################################################################################\n# TRAINING AND TESTING SAMPLES\n#############################################################################################\ncase_ids = data[\"case_id\"].unique().shuffle(seed=1)\ncase_ids_train, case_ids_test = train_test_split(case_ids, train_size=0.6, random_state=1)\ncase_ids_valid, case_ids_test = train_test_split(case_ids_test, train_size=0.5, random_state=1)\n\ncols_pred = []\nfor col in data.columns:\n    if col[-1].isupper() and col[:-1].islower():\n        cols_pred.append(col)\n\nbase_train, X_train, y_train = from_polars_to_pandas(case_ids_train)\nbase_valid, X_valid, y_valid = from_polars_to_pandas(case_ids_valid)\nbase_test, X_test, y_test = from_polars_to_pandas(case_ids_test)\n\nfor df in [X_train, X_valid, X_test]:\n    df = convert_strings(df)\n\n#############################################################################################\n# TRAINING LIGHTGBM\n#############################################################################################\n\nlgb_train = lgb.Dataset(X_train, label=y_train)\nlgb_valid = lgb.Dataset(X_valid, label=y_valid, reference=lgb_train)\n\nparams = {\n    \"boosting_type\": \"gbdt\",\n    \"objective\": \"binary\",\n    \"metric\": \"auc\",\n    \"max_depth\": 3,\n    \"num_leaves\": 31,\n    \"learning_rate\": 0.05,\n    \"feature_fraction\": 0.9,\n    \"bagging_fraction\": 0.8,\n    \"bagging_freq\": 5,\n    \"verbose\": -1,\n}\n\ngbm = lgb.train(\n    params,\n    lgb_train,\n    valid_sets=lgb_valid,\n    callbacks=[lgb.log_evaluation(50), lgb.early_stopping(10)]\n)\n\n#############################################################################################\n# FEATURE IMPORTANCE\n#############################################################################################\n\nfig, ax = plt.subplots(figsize=(24,12), ncols=2, nrows=1)\n\nlgb.plot_importance(gbm, max_num_features=50, height=0.8, ax=ax[0])\nax[0].grid(False)\nax[0].set(title='Feature Importance')\n\ncorr_matrix = X_train.select_dtypes(include=['float64']).corr()\nmask = np.triu(np.ones_like(corr_matrix, dtype=bool))\ncmap = sns.color_palette(\"Blues\", as_cmap=True)\nsns.heatmap(corr_matrix, cmap=cmap, linewidths=.5, mask=mask, ax=ax[1])\nax[1].set(title = \"Correlation Heatmap\")\n\nfig.tight_layout()\nplt.show()\n\n#############################################################################################\n# STABILITY METRICS\n#############################################################################################\n\nfor base, X in [(base_train, X_train), (base_valid, X_valid), (base_test, X_test)]:\n    y_pred = gbm.predict(X, num_iteration=gbm.best_iteration)\n    base[\"score\"] = y_pred\n\nprint(f'The AUC score on the train set is: {roc_auc_score(base_train[\"target\"], base_train[\"score\"])}') \nprint(f'The AUC score on the valid set is: {roc_auc_score(base_valid[\"target\"], base_valid[\"score\"])}') \nprint(f'The AUC score on the test set is: {roc_auc_score(base_test[\"target\"], base_test[\"score\"])}')  \n\nstability_score_train = gini_stability(base_train)\nstability_score_valid = gini_stability(base_valid)\nstability_score_test = gini_stability(base_test)\n\nprint(f'The stability score on the train set is: {stability_score_train}') \nprint(f'The stability score on the valid set is: {stability_score_valid}') \nprint(f'The stability score on the test set is: {stability_score_test}')  ","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:14:17.773936Z","iopub.execute_input":"2024-04-28T17:14:17.774492Z","iopub.status.idle":"2024-04-28T17:16:27.888010Z","shell.execute_reply.started":"2024-04-28T17:14:17.774444Z","shell.execute_reply":"2024-04-28T17:16:27.886192Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_importance = pd.DataFrame({'Feature': X_train.columns.values, 'Importance': gbm.feature_importance()}).sort_values(\"Importance\", ascending=False)\ndf_importance.head(20)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:16:27.890597Z","iopub.execute_input":"2024-04-28T17:16:27.890986Z","iopub.status.idle":"2024-04-28T17:16:27.917286Z","shell.execute_reply.started":"2024-04-28T17:16:27.890954Z","shell.execute_reply":"2024-04-28T17:16:27.916255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#############################################################################################\n# TRAINING DATA SET\n#############################################################################################\n\n### BASE TABLE\ntrain_basetable = pl.read_csv(dataPath + \"csv_files/train/train_base.csv\")\n\n### FEATURES\ntrain_feature_set = pl.read_csv(dataPath + \"csv_files/train/train_static_cb_0.csv\").pipe(set_table_dtypes)\n\n#############################################################################################\n# JOIN TABLES TOGETHER\n#############################################################################################\ndata = train_basetable.join( \n    train_feature_set, how=\"inner\", on=\"case_id\"\n)\n\n#############################################################################################\n# TRAINING AND TESTING SAMPLES\n#############################################################################################\ncase_ids = data[\"case_id\"].unique().shuffle(seed=1)\ncase_ids_train, case_ids_test = train_test_split(case_ids, train_size=0.6, random_state=1)\ncase_ids_valid, case_ids_test = train_test_split(case_ids_test, train_size=0.5, random_state=1)\n\ncols_pred = []\nfor col in data.columns:\n    if col[-1].isupper() and col[:-1].islower():\n        cols_pred.append(col)\n\nbase_train, X_train, y_train = from_polars_to_pandas(case_ids_train)\nbase_valid, X_valid, y_valid = from_polars_to_pandas(case_ids_valid)\nbase_test, X_test, y_test = from_polars_to_pandas(case_ids_test)\n\nfor df in [X_train, X_valid, X_test]:\n    df = convert_strings(df)\n\n#############################################################################################\n# TRAINING LIGHTGBM\n#############################################################################################\n\nlgb_train = lgb.Dataset(X_train, label=y_train)\nlgb_valid = lgb.Dataset(X_valid, label=y_valid, reference=lgb_train)\n\nparams = {\n    \"boosting_type\": \"gbdt\",\n    \"objective\": \"binary\",\n    \"metric\": \"auc\",\n    \"max_depth\": 3,\n    \"num_leaves\": 31,\n    \"learning_rate\": 0.05,\n    \"feature_fraction\": 0.9,\n    \"bagging_fraction\": 0.8,\n    \"bagging_freq\": 5,\n    \"verbose\": -1,\n}\n\ngbm = lgb.train(\n    params,\n    lgb_train,\n    valid_sets=lgb_valid,\n    callbacks=[lgb.log_evaluation(50), lgb.early_stopping(10)]\n)\n\n#############################################################################################\n# FEATURE IMPORTANCE\n#############################################################################################\n\nfig, ax = plt.subplots(figsize=(24,12), ncols=2, nrows=1)\n\nlgb.plot_importance(gbm, max_num_features=50, height=0.8, ax=ax[0])\nax[0].grid(False)\nax[0].set(title='Feature Importance')\n\ncorr_matrix = X_train.select_dtypes(include=['float64']).corr()\nmask = np.triu(np.ones_like(corr_matrix, dtype=bool))\ncmap = sns.color_palette(\"Blues\", as_cmap=True)\nsns.heatmap(corr_matrix, cmap=cmap, linewidths=.5, mask=mask, ax=ax[1])\nax[1].set(title = \"Correlation Heatmap\")\n\nfig.tight_layout()\nplt.show()\n\n#############################################################################################\n# STABILITY METRICS\n#############################################################################################\n\nfor base, X in [(base_train, X_train), (base_valid, X_valid), (base_test, X_test)]:\n    y_pred = gbm.predict(X, num_iteration=gbm.best_iteration)\n    base[\"score\"] = y_pred\n\nprint(f'The AUC score on the train set is: {roc_auc_score(base_train[\"target\"], base_train[\"score\"])}') \nprint(f'The AUC score on the valid set is: {roc_auc_score(base_valid[\"target\"], base_valid[\"score\"])}') \nprint(f'The AUC score on the test set is: {roc_auc_score(base_test[\"target\"], base_test[\"score\"])}')  \n\nstability_score_train = gini_stability(base_train)\nstability_score_valid = gini_stability(base_valid)\nstability_score_test = gini_stability(base_test)\n\nprint(f'The stability score on the train set is: {stability_score_train}') \nprint(f'The stability score on the valid set is: {stability_score_valid}') \nprint(f'The stability score on the test set is: {stability_score_test}')  ","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:16:27.919291Z","iopub.execute_input":"2024-04-28T17:16:27.919657Z","iopub.status.idle":"2024-04-28T17:17:07.659562Z","shell.execute_reply.started":"2024-04-28T17:16:27.919627Z","shell.execute_reply":"2024-04-28T17:17:07.658207Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_importance = pd.DataFrame({'Feature': X_train.columns.values, 'Importance': gbm.feature_importance()}).sort_values(\"Importance\", ascending=False)\ndf_importance.head(20)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:17:07.662979Z","iopub.execute_input":"2024-04-28T17:17:07.672750Z","iopub.status.idle":"2024-04-28T17:17:07.693477Z","shell.execute_reply.started":"2024-04-28T17:17:07.672670Z","shell.execute_reply":"2024-04-28T17:17:07.691817Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#############################################################################################\n# TRAINING DATA SET\n#############################################################################################\n\n### BASE TABLE\ntrain_basetable = pl.read_csv(dataPath + \"csv_files/train/train_base.csv\")\n\n### FEATURES\ntrain_feature_set = pl.read_csv(dataPath + \"csv_files/train/train_person_1.csv\").pipe(set_table_dtypes) \ntrain_feature_set = train_feature_set.select(\n    [\"case_id\", \"num_group1\", \"housetype_905L\",\"education_927M\",\"type_25L\",\"incometype_1044T\",\"mainoccupationinc_384A\",\n    \"empladdr_district_926M\",\"registaddr_district_1083M\"]\n).filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\")\n\n#############################################################################################\n# JOIN TABLES TOGETHER\n#############################################################################################\ndata = train_basetable.join( \n    train_feature_set, how=\"inner\", on=\"case_id\"\n)\n\n#############################################################################################\n# TRAINING AND TESTING SAMPLES\n#############################################################################################\ncase_ids = data[\"case_id\"].unique().shuffle(seed=888)\ncase_ids_train, case_ids_test = train_test_split(case_ids, train_size=0.6, random_state=1)\ncase_ids_valid, case_ids_test = train_test_split(case_ids_test, train_size=0.5, random_state=1)\n\ncols_pred = []\nfor col in data.columns:\n    if col[-1].isupper() and col[:-1].islower():\n        cols_pred.append(col)\n\nbase_train, X_train, y_train = from_polars_to_pandas(case_ids_train)\nbase_valid, X_valid, y_valid = from_polars_to_pandas(case_ids_valid)\nbase_test, X_test, y_test = from_polars_to_pandas(case_ids_test)\n\nfor df in [X_train, X_valid, X_test]:\n    df = convert_strings(df)\n\n#############################################################################################\n# TRAINING LIGHTGBM\n#############################################################################################\n\nlgb_train = lgb.Dataset(X_train, label=y_train)\nlgb_valid = lgb.Dataset(X_valid, label=y_valid, reference=lgb_train)\n\nparams = {\n    \"boosting_type\": \"gbdt\",\n    \"objective\": \"binary\",\n    \"metric\": \"auc\",\n    \"max_depth\": 3,\n    \"num_leaves\": 31,\n    \"learning_rate\": 0.05,\n    \"feature_fraction\": 0.9,\n    \"bagging_fraction\": 0.8,\n    \"bagging_freq\": 5,\n    \"verbose\": -1,\n}\n\ngbm = lgb.train(\n    params,\n    lgb_train,\n    valid_sets=lgb_valid,\n    callbacks=[lgb.log_evaluation(50), lgb.early_stopping(10)]\n)\n\n#############################################################################################\n# FEATURE IMPORTANCE\n#############################################################################################\n\nfig, ax = plt.subplots(figsize=(24,12), ncols=2, nrows=1)\n\nlgb.plot_importance(gbm, max_num_features=50, height=0.8, ax=ax[0])\nax[0].grid(False)\nax[0].set(title='Feature Importance')\n\ncorr_matrix = X_train.select_dtypes(include=['float64']).corr()\nmask = np.triu(np.ones_like(corr_matrix, dtype=bool))\ncmap = sns.color_palette(\"Blues\", as_cmap=True)\nsns.heatmap(corr_matrix, cmap=cmap, linewidths=.5, mask=mask, ax=ax[1])\nax[1].set(title = \"Correlation Heatmap\")\n\nfig.tight_layout()\nplt.show()\n\n#############################################################################################\n# STABILITY METRICS\n#############################################################################################\n\nfor base, X in [(base_train, X_train), (base_valid, X_valid), (base_test, X_test)]:\n    y_pred = gbm.predict(X, num_iteration=gbm.best_iteration)\n    base[\"score\"] = y_pred\n\nprint(f'The AUC score on the train set is: {roc_auc_score(base_train[\"target\"], base_train[\"score\"])}') \nprint(f'The AUC score on the valid set is: {roc_auc_score(base_valid[\"target\"], base_valid[\"score\"])}') \nprint(f'The AUC score on the test set is: {roc_auc_score(base_test[\"target\"], base_test[\"score\"])}')  \n\nstability_score_train = gini_stability(base_train)\nstability_score_valid = gini_stability(base_valid)\nstability_score_test = gini_stability(base_test)\n\nprint(f'The stability score on the train set is: {stability_score_train}') \nprint(f'The stability score on the valid set is: {stability_score_valid}') \nprint(f'The stability score on the test set is: {stability_score_test}')  ","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:17:07.696216Z","iopub.execute_input":"2024-04-28T17:17:07.696766Z","iopub.status.idle":"2024-04-28T17:17:34.351398Z","shell.execute_reply.started":"2024-04-28T17:17:07.696715Z","shell.execute_reply":"2024-04-28T17:17:34.348290Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_importance = pd.DataFrame({'Feature': X_train.columns.values, 'Importance': gbm.feature_importance()}).sort_values(\"Importance\", ascending=False)\ndf_importance.head(20)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:17:34.362110Z","iopub.execute_input":"2024-04-28T17:17:34.362767Z","iopub.status.idle":"2024-04-28T17:17:34.398716Z","shell.execute_reply.started":"2024-04-28T17:17:34.362729Z","shell.execute_reply":"2024-04-28T17:17:34.392639Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#############################################################################################\n# TRAINING DATA SET\n#############################################################################################\n\n### BASE TABLE\ntrain_basetable = pl.read_csv(dataPath + \"csv_files/train/train_base.csv\")\n\n### FEATURES\ntrain_feature_set = pl.read_csv(dataPath + \"csv_files/train/train_deposit_1.csv\").pipe(set_table_dtypes) \ntrain_feature_set = train_feature_set.filter( (pl.col(\"num_group1\") == 0) )\n\n#############################################################################################\n# JOIN TABLES TOGETHER\n#############################################################################################\ndata = train_basetable.join( \n    train_feature_set, how=\"left\", on=\"case_id\"\n)\n\n#############################################################################################\n# TRAINING AND TESTING SAMPLES\n#############################################################################################\ncase_ids = data[\"case_id\"].unique().shuffle(seed=1)\ncase_ids_train, case_ids_test = train_test_split(case_ids, train_size=0.6, random_state=1)\ncase_ids_valid, case_ids_test = train_test_split(case_ids_test, train_size=0.5, random_state=1)\n\ncols_pred = []\nfor col in data.columns:\n    if col[-1].isupper() and col[:-1].islower():\n        cols_pred.append(col)\n\nbase_train, X_train, y_train = from_polars_to_pandas(case_ids_train)\nbase_valid, X_valid, y_valid = from_polars_to_pandas(case_ids_valid)\nbase_test, X_test, y_test = from_polars_to_pandas(case_ids_test)\n\nfor df in [X_train, X_valid, X_test]:\n    df = convert_strings(df)\n\n#############################################################################################\n# TRAINING LIGHTGBM\n#############################################################################################\n\nlgb_train = lgb.Dataset(X_train, label=y_train)\nlgb_valid = lgb.Dataset(X_valid, label=y_valid, reference=lgb_train)\n\nparams = {\n    \"boosting_type\": \"gbdt\",\n    \"objective\": \"binary\",\n    \"metric\": \"auc\",\n    \"max_depth\": 3,\n    \"num_leaves\": 31,\n    \"learning_rate\": 0.05,\n    \"feature_fraction\": 0.9,\n    \"bagging_fraction\": 0.8,\n    \"bagging_freq\": 5,\n    \"verbose\": -1,\n}\n\ngbm = lgb.train(\n    params,\n    lgb_train,\n    valid_sets=lgb_valid,\n    callbacks=[lgb.log_evaluation(50), lgb.early_stopping(10)]\n)\n\n#############################################################################################\n# FEATURE IMPORTANCE\n#############################################################################################\n\nfig, ax = plt.subplots(figsize=(24,12), ncols=2, nrows=1)\n\nlgb.plot_importance(gbm, max_num_features=50, height=0.8, ax=ax[0])\nax[0].grid(False)\nax[0].set(title='Feature Importance')\n\ncorr_matrix = X_train.select_dtypes(include=['float64']).corr()\nmask = np.triu(np.ones_like(corr_matrix, dtype=bool))\ncmap = sns.color_palette(\"Blues\", as_cmap=True)\nsns.heatmap(corr_matrix, cmap=cmap, linewidths=.5, mask=mask, ax=ax[1])\nax[1].set(title = \"Correlation Heatmap\")\n\nfig.tight_layout()\nplt.show()\n\n#############################################################################################\n# STABILITY METRICS\n#############################################################################################\n\nfor base, X in [(base_train, X_train), (base_valid, X_valid), (base_test, X_test)]:\n    y_pred = gbm.predict(X, num_iteration=gbm.best_iteration)\n    base[\"score\"] = y_pred\n\nprint(f'The AUC score on the train set is: {roc_auc_score(base_train[\"target\"], base_train[\"score\"])}') \nprint(f'The AUC score on the valid set is: {roc_auc_score(base_valid[\"target\"], base_valid[\"score\"])}') \nprint(f'The AUC score on the test set is: {roc_auc_score(base_test[\"target\"], base_test[\"score\"])}')  \n\nstability_score_train = gini_stability(base_train)\nstability_score_valid = gini_stability(base_valid)\nstability_score_test = gini_stability(base_test)\n\nprint(f'The stability score on the train set is: {stability_score_train}') \nprint(f'The stability score on the valid set is: {stability_score_valid}') \nprint(f'The stability score on the test set is: {stability_score_test}')  ","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:17:34.405692Z","iopub.execute_input":"2024-04-28T17:17:34.407273Z","iopub.status.idle":"2024-04-28T17:17:45.099874Z","shell.execute_reply.started":"2024-04-28T17:17:34.407013Z","shell.execute_reply":"2024-04-28T17:17:45.098429Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_importance = pd.DataFrame({'Feature': X_train.columns.values, 'Importance': gbm.feature_importance()}).sort_values(\"Importance\", ascending=False)\ndf_importance.head(20)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:17:45.101736Z","iopub.execute_input":"2024-04-28T17:17:45.102260Z","iopub.status.idle":"2024-04-28T17:17:45.119826Z","shell.execute_reply.started":"2024-04-28T17:17:45.102225Z","shell.execute_reply":"2024-04-28T17:17:45.118015Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#############################################################################################\n# TRAINING DATA SET\n#############################################################################################\n\n### BASE TABLE\ntrain_basetable = pl.read_csv(dataPath + \"csv_files/train/train_base.csv\")\n\n### FEATURES\ntrain_feature_set = pl.read_csv(dataPath + \"csv_files/train/train_debitcard_1.csv\").filter((pl.col(\"num_group1\") == 0)).pipe(set_table_dtypes) \n\n#############################################################################################\n# JOIN TABLES TOGETHER\n#############################################################################################\ndata = train_basetable.join( \n    train_feature_set, how=\"left\", on=\"case_id\"\n)\n\n#############################################################################################\n# TRAINING AND TESTING SAMPLES\n#############################################################################################\ncase_ids = data[\"case_id\"].unique().shuffle(seed=1)\ncase_ids_train, case_ids_test = train_test_split(case_ids, train_size=0.6, random_state=1)\ncase_ids_valid, case_ids_test = train_test_split(case_ids_test, train_size=0.5, random_state=1)\n\ncols_pred = []\nfor col in data.columns:\n    if col[-1].isupper() and col[:-1].islower():\n        cols_pred.append(col)\n\nbase_train, X_train, y_train = from_polars_to_pandas(case_ids_train)\nbase_valid, X_valid, y_valid = from_polars_to_pandas(case_ids_valid)\nbase_test, X_test, y_test = from_polars_to_pandas(case_ids_test)\n\nfor df in [X_train, X_valid, X_test]:\n    df = convert_strings(df)\n\n#############################################################################################\n# TRAINING LIGHTGBM\n#############################################################################################\n\nlgb_train = lgb.Dataset(X_train, label=y_train)\nlgb_valid = lgb.Dataset(X_valid, label=y_valid, reference=lgb_train)\n\nparams = {\n    \"boosting_type\": \"gbdt\",\n    \"objective\": \"binary\",\n    \"metric\": \"auc\",\n    \"max_depth\": 3,\n    \"num_leaves\": 31,\n    \"learning_rate\": 0.05,\n    \"feature_fraction\": 0.9,\n    \"bagging_fraction\": 0.8,\n    \"bagging_freq\": 5,\n    \"verbose\": -1,\n}\n\ngbm = lgb.train(\n    params,\n    lgb_train,\n    valid_sets=lgb_valid,\n    callbacks=[lgb.log_evaluation(50), lgb.early_stopping(10)]\n)\n\n#############################################################################################\n# FEATURE IMPORTANCE\n#############################################################################################\n\nfig, ax = plt.subplots(figsize=(24,12), ncols=2, nrows=1)\n\nlgb.plot_importance(gbm, max_num_features=50, height=0.8, ax=ax[0])\nax[0].grid(False)\nax[0].set(title='Feature Importance')\n\ncorr_matrix = X_train.select_dtypes(include=['float64']).corr()\nmask = np.triu(np.ones_like(corr_matrix, dtype=bool))\ncmap = sns.color_palette(\"Blues\", as_cmap=True)\nsns.heatmap(corr_matrix, cmap=cmap, linewidths=.5, mask=mask, ax=ax[1])\nax[1].set(title = \"Correlation Heatmap\")\n\nfig.tight_layout()\nplt.show()\n\n#############################################################################################\n# STABILITY METRICS\n#############################################################################################\n\nfor base, X in [(base_train, X_train), (base_valid, X_valid), (base_test, X_test)]:\n    y_pred = gbm.predict(X, num_iteration=gbm.best_iteration)\n    base[\"score\"] = y_pred\n\nprint(f'The AUC score on the train set is: {roc_auc_score(base_train[\"target\"], base_train[\"score\"])}') \nprint(f'The AUC score on the valid set is: {roc_auc_score(base_valid[\"target\"], base_valid[\"score\"])}') \nprint(f'The AUC score on the test set is: {roc_auc_score(base_test[\"target\"], base_test[\"score\"])}')  \n\nstability_score_train = gini_stability(base_train)\nstability_score_valid = gini_stability(base_valid)\nstability_score_test = gini_stability(base_test)\n\nprint(f'The stability score on the train set is: {stability_score_train}') \nprint(f'The stability score on the valid set is: {stability_score_valid}') \nprint(f'The stability score on the test set is: {stability_score_test}')  ","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:17:45.121960Z","iopub.execute_input":"2024-04-28T17:17:45.122496Z","iopub.status.idle":"2024-04-28T17:17:51.697973Z","shell.execute_reply.started":"2024-04-28T17:17:45.122453Z","shell.execute_reply":"2024-04-28T17:17:51.696727Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_importance = pd.DataFrame({'Feature': X_train.columns.values, 'Importance': gbm.feature_importance()}).sort_values(\"Importance\", ascending=False)\ndf_importance.head(20)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:17:51.701895Z","iopub.execute_input":"2024-04-28T17:17:51.702341Z","iopub.status.idle":"2024-04-28T17:17:51.718241Z","shell.execute_reply.started":"2024-04-28T17:17:51.702304Z","shell.execute_reply":"2024-04-28T17:17:51.716384Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#############################################################################################\n# TRAINING DATA SET\n#############################################################################################\n\nkeep_vars = [\"case_id\",\"debtoutstand_525A\",\"debtoverdue_47A\",\"dpdmax_139P\",\"dpdmaxdatemonth_89T\",\n             \"dpdmaxdateyear_596T\",\"financialinstitution_382M\",\"financialinstitution_591M\",\n             \"instlamount_768A\",\"lastupdate_1112D\",\"monthlyinstlamount_332A\",\"numberofcontrsvalue_258L\",\n             \"numberofoverdueinstlmax_1039L\",\"numberofoverdueinstls_725L\",\"overdueamount_659A\",\"overdueamountmax2_14A\",\n             \"overdueamountmax_155A\",\"overdueamountmaxdatemonth_365T\",\"overdueamountmaxdateyear_2T\",\n             \"purposeofcred_426M\",\"purposeofcred_874M\",\"totaldebtoverduevalue_178A\",\"totaldebtoverduevalue_718A\",\n             \"totaloutstanddebtvalue_39A\",\"totaloutstanddebtvalue_668A\",\"num_group1\"]\n\n### BASE TABLE\ntrain_basetable = pl.read_csv(dataPath + \"csv_files/train/train_base.csv\")\n\n### FEATURES\n# train_feature_set = pl.concat(\n#     [\n#         pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_0.csv\").select(keep_vars).filter((pl.col(\"num_group1\") == 0)).pipe(set_table_dtypes),\n#         pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_1.csv\").select(keep_vars).filter((pl.col(\"num_group1\") == 0)).pipe(set_table_dtypes),\n#         pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_2.csv\").select(keep_vars).filter((pl.col(\"num_group1\") == 0)).pipe(set_table_dtypes),\n#         pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_3.csv\").select(keep_vars).filter((pl.col(\"num_group1\") == 0)).pipe(set_table_dtypes),\n#     ],\n#     how=\"vertical_relaxed\",\n# )\n\ntrain_feature_set = pl.concat(\n    [\n        pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_0.csv\").filter((pl.col(\"num_group1\") == 0)).pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_1.csv\").filter((pl.col(\"num_group1\") == 0)).pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_2.csv\").filter((pl.col(\"num_group1\") == 0)).pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_3.csv\").filter((pl.col(\"num_group1\") == 0)).pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\n\n#############################################################################################\n# JOIN TABLES TOGETHER\n#############################################################################################\ndata = train_basetable.join( \n    train_feature_set, how=\"inner\", on=\"case_id\"\n)\n\n#############################################################################################\n# TRAINING AND TESTING SAMPLES\n#############################################################################################\ncase_ids = data[\"case_id\"].unique().shuffle(seed=1)\ncase_ids_train, case_ids_test = train_test_split(case_ids, train_size=0.6, random_state=1)\ncase_ids_valid, case_ids_test = train_test_split(case_ids_test, train_size=0.5, random_state=1)\n\ncols_pred = []\nfor col in data.columns:\n    if col[-1].isupper() and col[:-1].islower():\n        cols_pred.append(col)\n\nbase_train, X_train, y_train = from_polars_to_pandas(case_ids_train)\nbase_valid, X_valid, y_valid = from_polars_to_pandas(case_ids_valid)\nbase_test, X_test, y_test = from_polars_to_pandas(case_ids_test)\n\n#############################################################################################\n#  DATA CLEANING\n#############################################################################################\n\nX_train.loc[X_train['numberofoverdueinstlmax_1039L'] > 6, 'numberofoverdueinstlmax_1039L'] = 7\nX_valid.loc[X_valid['numberofoverdueinstlmax_1039L'] > 6, 'numberofoverdueinstlmax_1039L'] = 7\nX_test.loc[X_test['numberofoverdueinstlmax_1039L'] > 6, 'numberofoverdueinstlmax_1039L'] = 7\n\n\nfor df in [X_train, X_valid, X_test]:\n    df = convert_strings(df)\n\n#############################################################################################\n# TRAINING LIGHTGBM\n#############################################################################################\n\nlgb_train = lgb.Dataset(X_train, label=y_train)\nlgb_valid = lgb.Dataset(X_valid, label=y_valid, reference=lgb_train)\n\nparams = {\n    \"boosting_type\": \"gbdt\",\n    \"objective\": \"binary\",\n    \"metric\": \"auc\",\n    \"max_depth\": 3,\n    \"num_leaves\": 31,\n    \"learning_rate\": 0.05,\n    \"feature_fraction\": 0.9,\n    \"bagging_fraction\": 0.8,\n    \"bagging_freq\": 5,\n    \"verbose\": -1,\n}\n\ngbm = lgb.train(\n    params,\n    lgb_train,\n    valid_sets=lgb_valid,\n    callbacks=[lgb.log_evaluation(50), lgb.early_stopping(10)]\n)\n\n#############################################################################################\n# FEATURE IMPORTANCE\n#############################################################################################\n\nfig, ax = plt.subplots(figsize=(24,12), ncols=2, nrows=1)\n\nlgb.plot_importance(gbm, max_num_features=50, height=0.8, ax=ax[0])\nax[0].grid(False)\nax[0].set(title='Feature Importance')\n\ncorr_matrix = X_train.select_dtypes(include=['float64']).corr()\nmask = np.triu(np.ones_like(corr_matrix, dtype=bool))\ncmap = sns.color_palette(\"Blues\", as_cmap=True)\nsns.heatmap(corr_matrix, cmap=cmap, linewidths=.5, mask=mask, ax=ax[1])\nax[1].set(title = \"Correlation Heatmap\")\n\nfig.tight_layout()\nplt.show()\n\n#############################################################################################\n# STABILITY METRICS\n#############################################################################################\n\nfor base, X in [(base_train, X_train), (base_valid, X_valid), (base_test, X_test)]:\n    y_pred = gbm.predict(X, num_iteration=gbm.best_iteration)\n    base[\"score\"] = y_pred\n\nprint(f'The AUC score on the train set is: {roc_auc_score(base_train[\"target\"], base_train[\"score\"])}') \nprint(f'The AUC score on the valid set is: {roc_auc_score(base_valid[\"target\"], base_valid[\"score\"])}') \nprint(f'The AUC score on the test set is: {roc_auc_score(base_test[\"target\"], base_test[\"score\"])}')  \n\n# stability_score_train = gini_stability(base_train)\n# stability_score_valid = gini_stability(base_valid)\n# stability_score_test = gini_stability(base_test)\n\n# print(f'The stability score on the train set is: {stability_score_train}') \n# print(f'The stability score on the valid set is: {stability_score_valid}') \n# print(f'The stability score on the test set is: {stability_score_test}')  ","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:17:51.720635Z","iopub.execute_input":"2024-04-28T17:17:51.721512Z","iopub.status.idle":"2024-04-28T17:19:33.830565Z","shell.execute_reply.started":"2024-04-28T17:17:51.721468Z","shell.execute_reply":"2024-04-28T17:19:33.828913Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_importance = pd.DataFrame({'Feature': X_train.columns.values, 'Importance': gbm.feature_importance()}).sort_values(\"Importance\", ascending=False)\ndf_importance.head(20)","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:19:33.832487Z","iopub.execute_input":"2024-04-28T17:19:33.832895Z","iopub.status.idle":"2024-04-28T17:19:33.855806Z","shell.execute_reply.started":"2024-04-28T17:19:33.832862Z","shell.execute_reply":"2024-04-28T17:19:33.854215Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"keep_vars = list(['target','case_id','date_decision','MONTH','WEEK_NUM','price_1097A',\n'mobilephncnt_593L',\n'pmtnum_254L',\n'avgdpdtolclosure24_3658938P',\n'numrejects9m_859L',\n'cntpmts24_3658933L',\n'numinstunpaidmax_3546851L',\n'eir_270L',\n'numinstlsallpaid_934L',\n'lastdelinqdate_224D',\n'maxdbddpdtollast12m_3658940P',\n'numincomingpmts_3546848L',\n'pctinstlsallpaidlate1d_3546856L',\n'maxdpdlast3m_392P',\n'monthsannuity_845L',\n'datelastinstal40dpd_247D',\n'lastrejectreason_759M',\n'pmtssum_45A',\n'education_1103M',\n'riskassesment_940T',\n'pmtaverage_3A',\n'days30_165L',\n'days360_512L',\n'days180_256L',\n'thirdquarter_1082L',\n'riskassesment_302T',\n'days120_123L',\n'firstquarter_103L',\n'registaddr_district_1083M',\n'education_927M',\n'mainoccupationinc_384A',\n'incometype_1044T',\n'empladdr_district_926M',\n'housetype_905L',\n'type_25L',\n'amount_416A',\n'last180dayturnover_1134A',\n'last180dayaveragebalance_704A',\n'last30dayturnover_651A',\n'dateofcredstart_739D',\n'residualamount_856A',\n'refreshdate_3813885D',\n'dateofcredstart_181D',\n'numberofoverdueinstlmax_1039L',\n'totalamount_996A',\n'dpdmaxdateyear_596T',\n'dpdmax_757P',\n'description_351M',\n'totaldebtoverduevalue_178A',\n'dpdmax_139P',\n'totalamount_6A',\n'lastupdate_1112D',\n'overdueamountmax_35A',\n'overdueamountmax2_398A',\n'totaloutstanddebtvalue_39A',\n'numberofinstls_320L',\n'numberofoverdueinstlmaxdat_148D',\n'overdueamountmax2_14A',\n'numberofcontrsvalue_258L'])\n\nkeep_vars","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:20:21.510082Z","iopub.execute_input":"2024-04-28T17:20:21.510632Z","iopub.status.idle":"2024-04-28T17:20:21.525983Z","shell.execute_reply.started":"2024-04-28T17:20:21.510598Z","shell.execute_reply":"2024-04-28T17:20:21.524943Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#############################################################################################\n# TRAINING DATA SET\n#############################################################################################\n\n### BASE TABLE\ntrain_basetable = pl.read_csv(dataPath + \"csv_files/train/train_base.csv\")\n\n### STATICS\ntrain_static = pl.concat(\n    [\n        pl.read_csv(dataPath + \"csv_files/train/train_static_0_0.csv\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/train/train_static_0_1.csv\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\n\n### STATIC CB\ntrain_static_cb = pl.read_csv(dataPath + \"csv_files/train/train_static_cb_0.csv\").pipe(set_table_dtypes)\n\n### PERSON 1\ntrain_person_1 = pl.read_csv(dataPath + \"csv_files/train/train_person_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes) \n\n### DEPOSIT 1\ntrain_deposit_1 = pl.read_csv(dataPath + \"csv_files/train/train_deposit_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes) \n\n### DEBIT CARD 1\ntrain_debitcard_1 = pl.read_csv(dataPath + \"csv_files/train/train_debitcard_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes) \n\n### APP PREV 1\ntrain_applprev_1 = pl.concat(\n    [\n        pl.read_csv(dataPath + \"csv_files/train/train_applprev_1_0.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/train/train_applprev_1_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\n\n### OTHER 1\ntrain_other_1 = pl.read_csv(dataPath + \"csv_files/train/train_other_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes) \n\n### CREDIT BUREAU A1\ntrain_credit_bureau_a_1 = pl.concat(\n    [\n        pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_0.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_2.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_a_1_3.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\n\n### CREDIT BUREAU B1\ntrain_credit_bureau_b_1 = pl.read_csv(dataPath + \"csv_files/train/train_credit_bureau_b_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes)\n\n### TAX REGISTERY A1\ntrain_tax_registry_a_1 = pl.read_csv(dataPath + \"csv_files/train/train_tax_registry_a_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes)\n\n\n#############################################################################################\n# JOIN TABLES TOGETHER\n#############################################################################################\ndata = train_basetable.join( \n    train_static, how=\"left\", on=\"case_id\").join(\n    train_static_cb, how=\"left\", on=\"case_id\").join(\n    train_person_1, how=\"left\", on=\"case_id\").join(\n    train_deposit_1, how=\"left\", on=\"case_id\").join(\n    train_debitcard_1, how=\"left\", on=\"case_id\").join(\n    train_applprev_1, how=\"left\", on=\"case_id\").join(\n    train_other_1, how=\"left\", on=\"case_id\").join(\n    train_credit_bureau_a_1, how=\"left\", on=\"case_id\").join(\n    train_credit_bureau_b_1, how=\"left\", on=\"case_id\").join(\n    train_tax_registry_a_1, how=\"left\", on=\"case_id\"\n).select(keep_vars)\n\nprint(\"Done!\")","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:20:23.119694Z","iopub.execute_input":"2024-04-28T17:20:23.120616Z","iopub.status.idle":"2024-04-28T17:21:34.184858Z","shell.execute_reply.started":"2024-04-28T17:20:23.120578Z","shell.execute_reply":"2024-04-28T17:21:34.183892Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#############################################################################################\n# TRAINING AND TESTING SAMPLES\n#############################################################################################\ncase_ids = data[\"case_id\"].unique().shuffle(seed=1)\ncase_ids_train, case_ids_test = train_test_split(case_ids, train_size=0.6, random_state=1)\ncase_ids_valid, case_ids_test = train_test_split(case_ids_test, train_size=0.5, random_state=1)\n\ncols_pred = []\nfor col in data.columns:\n    if col[-1].isupper() and col[:-1].islower():\n        cols_pred.append(col)\n\nbase_train, X_train, y_train = from_polars_to_pandas(case_ids_train)\nbase_valid, X_valid, y_valid = from_polars_to_pandas(case_ids_valid)\nbase_test, X_test, y_test = from_polars_to_pandas(case_ids_test)\n\nfor df in [X_train, X_valid, X_test]:\n    df = convert_strings(df)\n\n#############################################################################################\n# TRAINING LIGHTGBM\n#############################################################################################\n\nlgb_train = lgb.Dataset(X_train, label=y_train)\nlgb_valid = lgb.Dataset(X_valid, label=y_valid, reference=lgb_train)\n\nparams = {\n    \"boosting_type\": \"gbdt\",\n    \"objective\": \"binary\",\n    \"metric\": \"auc\",\n    \"max_depth\": 15,\n    \"num_leaves\": 31,\n    \"learning_rate\": 0.05,\n    \"feature_fraction\": 0.8,\n    \"bagging_fraction\": 0.8,\n    \"bagging_freq\": 5,\n    'num_boost_round':500,\n    \"verbose\": -1,\n    'early_stopping_rounds':50\n}\n\ngbm = lgb.train(\n    params,\n    lgb_train,\n    valid_sets=lgb_valid,\n    callbacks=[lgb.log_evaluation(50), lgb.early_stopping(50)]\n)\n\n#############################################################################################\n# FEATURE IMPORTANCE\n#############################################################################################\n\nfig, ax = plt.subplots(figsize=(24,12), ncols=2, nrows=1)\n\nlgb.plot_importance(gbm, max_num_features=50, height=0.8, ax=ax[0])\nax[0].grid(False)\nax[0].set(title='Feature Importance')\n\ncorr_matrix = X_train.select_dtypes(include=['float64']).corr()\nmask = np.triu(np.ones_like(corr_matrix, dtype=bool))\ncmap = sns.color_palette(\"Blues\", as_cmap=True)\nsns.heatmap(corr_matrix, cmap=cmap, linewidths=.5, mask=mask, ax=ax[1])\nax[1].set(title = \"Correlation Heatmap\")\n\nfig.tight_layout()\nplt.show()\n\n#############################################################################################\n# STABILITY METRICS\n#############################################################################################\n\nfor base, X in [(base_train, X_train), (base_valid, X_valid), (base_test, X_test)]:\n    y_pred = gbm.predict(X, num_iteration=gbm.best_iteration)\n    base[\"score\"] = y_pred\n\nprint(f'The AUC score on the train set is: {roc_auc_score(base_train[\"target\"], base_train[\"score\"])}') \nprint(f'The AUC score on the valid set is: {roc_auc_score(base_valid[\"target\"], base_valid[\"score\"])}') \nprint(f'The AUC score on the test set is: {roc_auc_score(base_test[\"target\"], base_test[\"score\"])}')  \nstability_score_train = gini_stability(base_train)\nstability_score_valid = gini_stability(base_valid)\nstability_score_test = gini_stability(base_test)\n\nprint(f'The stability score on the train set is: {stability_score_train}') \nprint(f'The stability score on the valid set is: {stability_score_valid}') \nprint(f'The stability score on the test set is: {stability_score_test}')  ","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:21:34.186983Z","iopub.execute_input":"2024-04-28T17:21:34.187695Z","iopub.status.idle":"2024-04-28T17:24:20.031386Z","shell.execute_reply.started":"2024-04-28T17:21:34.187661Z","shell.execute_reply":"2024-04-28T17:24:20.029733Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#############################################################################################\n# TESTING DATA SET\n#############################################################################################\n\n### BASE TABLE\ntest_basetable = pl.read_csv(dataPath + \"csv_files/test/test_base.csv\")\n\n### STATICS\ntest_static = pl.concat(\n    [\n        pl.read_csv(dataPath + \"csv_files/test/test_static_0_0.csv\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/test/test_static_0_1.csv\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\n\n### STATIC CB\ntest_static_cb = pl.read_csv(dataPath + \"csv_files/test/test_static_cb_0.csv\").pipe(set_table_dtypes)\n\n### PERSON 1\ntest_person_1 = pl.read_csv(dataPath + \"csv_files/test/test_person_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes) \n\n### DEPOSIT 1\ntest_deposit_1 = pl.read_csv(dataPath + \"csv_files/test/test_deposit_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes) \n\n### DEBIT CARD 1\ntest_debitcard_1 = pl.read_csv(dataPath + \"csv_files/test/test_debitcard_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes) \n\n### APP PREV 1\ntest_applprev_1 = pl.concat(\n    [\n        pl.read_csv(dataPath + \"csv_files/test/test_applprev_1_0.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/test/test_applprev_1_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\n\n### OTHER 1\ntest_other_1 = pl.read_csv(dataPath + \"csv_files/test/test_other_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes) \n\n### CREDIT BUREAU A1\ntest_credit_bureau_a_1 = pl.concat(\n    [\n        pl.read_csv(dataPath + \"csv_files/test/test_credit_bureau_a_1_0.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/test/test_credit_bureau_a_1_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/test/test_credit_bureau_a_1_2.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n        pl.read_csv(dataPath + \"csv_files/test/test_credit_bureau_a_1_3.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\n\n### CREDIT BUREAU B1\ntest_credit_bureau_b_1 = pl.read_csv(dataPath + \"csv_files/test/test_credit_bureau_b_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes)\n\n### TAX REGISTERY A1\ntest_tax_registry_a_1 = pl.read_csv(dataPath + \"csv_files/test/test_tax_registry_a_1.csv\").filter((pl.col(\"num_group1\") == 0)).drop(\"num_group1\").pipe(set_table_dtypes)\n\n\n#############################################################################################\n# JOIN TABLES TOGETHER\n#############################################################################################\ndata_submission = test_basetable.join( \n    test_static, how=\"left\", on=\"case_id\").join(\n    test_static_cb, how=\"left\", on=\"case_id\").join(\n    test_person_1, how=\"left\", on=\"case_id\").join(\n    test_deposit_1, how=\"left\", on=\"case_id\").join(\n    test_debitcard_1, how=\"left\", on=\"case_id\").join(\n    test_applprev_1, how=\"left\", on=\"case_id\").join(\n    test_other_1, how=\"left\", on=\"case_id\").join(\n    test_credit_bureau_a_1, how=\"left\", on=\"case_id\").join(\n    test_credit_bureau_b_1, how=\"left\", on=\"case_id\").join(\n    test_tax_registry_a_1, how=\"left\", on=\"case_id\"\n).select(keep_vars[1:])\n\nprint(\"Done!\")\n","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:24:20.034554Z","iopub.execute_input":"2024-04-28T17:24:20.035102Z","iopub.status.idle":"2024-04-28T17:24:20.249306Z","shell.execute_reply.started":"2024-04-28T17:24:20.035054Z","shell.execute_reply":"2024-04-28T17:24:20.247561Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cols_pred = []\nfor col in data_submission.columns:\n    if col[-1].isupper() and col[:-1].islower():\n        cols_pred.append(col)\n        \nX_submission = data_submission[cols_pred].to_pandas()\nX_submission = convert_strings(X_submission)\n\nX_submission['cntpmts24_3658933L'] = pd.to_numeric(X_submission['cntpmts24_3658933L'])\nX_submission['numinstunpaidmax_3546851L'] = pd.to_numeric(X_submission['numinstunpaidmax_3546851L'])\nX_submission['numinstlsallpaid_934L'] = pd.to_numeric(X_submission['numinstlsallpaid_934L'])\nX_submission['numincomingpmts_3546848L'] = pd.to_numeric(X_submission['numincomingpmts_3546848L'])\nX_submission['pctinstlsallpaidlate1d_3546856L'] = pd.to_numeric(X_submission['pctinstlsallpaidlate1d_3546856L'])\nX_submission['monthsannuity_845L'] = pd.to_numeric(X_submission['monthsannuity_845L'])\nX_submission['riskassesment_940T'] = pd.to_numeric(X_submission['riskassesment_940T'])\nX_submission['numberofinstls_320L'] = pd.to_numeric(X_submission['numberofinstls_320L'])\n\n\ncategorical_cols = X_train.select_dtypes(include=['category']).columns\n\nfor col in categorical_cols:\n    train_categories = set(X_train[col].cat.categories)\n    submission_categories = set(X_submission[col].cat.categories)\n    new_categories = submission_categories - train_categories\n    X_submission.loc[X_submission[col].isin(new_categories), col] = \"Unknown\"\n    new_dtype = pd.CategoricalDtype(categories=train_categories, ordered=True)\n    X_train[col] = X_train[col].astype(new_dtype)\n    X_submission[col] = X_submission[col].astype(new_dtype)\n\ny_submission_pred = gbm.predict(X_submission, num_iteration=gbm.best_iteration)\n\nsubmission = pd.DataFrame({\n    \"case_id\": data_submission[\"case_id\"].to_numpy(),\n    \"score\": y_submission_pred\n}).set_index('case_id')\nsubmission.to_csv(\"./submission.csv\")\n\nprint(\"Done!\")","metadata":{"execution":{"iopub.status.busy":"2024-04-28T17:24:20.253013Z","iopub.execute_input":"2024-04-28T17:24:20.253634Z","iopub.status.idle":"2024-04-28T17:24:20.531120Z","shell.execute_reply.started":"2024-04-28T17:24:20.253583Z","shell.execute_reply":"2024-04-28T17:24:20.529413Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}