{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":50160,"databundleVersionId":7921029,"sourceType":"competition"},{"sourceId":7652479,"sourceType":"datasetVersion","datasetId":4461225}],"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"<p style=\"font-size: 35px\">\n    <b>EDA. Part II: Black Magic and Its Exposure</b>\n    <p style=\"font-size: 18px\">\n        <img align=\"right\" width=\"300\" height=\"150\" src=\"https://www.kaggle.com/competitions/50160/images/header\">\n        <b>Home Credit - Credit Risk Model Stability</b></p>\n        Create a model measured against feature stability over time\n</p>","metadata":{}},{"cell_type":"markdown","source":"# Survey plan\n\nThis is the second part of EDA (the first part is [here](https://www.kaggle.com/code/pib73nl/home-credit-2024-eda-part-i)), where we will continue our research, and even do some calculations. What will we try to understand and what will we try to do based on this understanding:\n\n 1. How to deal with `num_group*` features?\n 2. How to deal with custom types? How to deal with types automatically assigned when importing tables?\n 3. Selecting aggregation approaches (functions, grouping)\n 4. Prepare a dataset using the approaches defined in steps 1-3 and check the result using the [starter notebook](https://www.kaggle.com/code/jetakow/home-credit-2024-starter-notebook)\n \nSurvey boundaries:\n 1. Tables with `depth=1`\n 2. We will not consider the practical meaning of individual features and select individual aggregation functions for them. There will be only a general approach here","metadata":{}},{"cell_type":"code","source":"! pip install /kaggle/input/install-pack-homecredit/hvplot-0.9.2-py2.py3-none-any.whl --no-deps","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-03T06:30:28.059577Z","iopub.execute_input":"2024-04-03T06:30:28.060481Z","iopub.status.idle":"2024-04-03T06:30:50.716690Z","shell.execute_reply.started":"2024-04-03T06:30:28.060443Z","shell.execute_reply":"2024-04-03T06:30:50.715153Z"},"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":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-03T06:30:50.719250Z","iopub.execute_input":"2024-04-03T06:30:50.719637Z","iopub.status.idle":"2024-04-03T06:30:58.025892Z","shell.execute_reply.started":"2024-04-03T06:30:50.719595Z","shell.execute_reply":"2024-04-03T06:30:58.023660Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:30:58.027773Z","iopub.execute_input":"2024-04-03T06:30:58.028484Z","iopub.status.idle":"2024-04-03T06:30:58.035047Z","shell.execute_reply.started":"2024-04-03T06:30:58.028444Z","shell.execute_reply":"2024-04-03T06:30:58.033093Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# General review","metadata":{}},{"cell_type":"code","source":"path_list = [path for path in glob(f'{TRAIN_PATH_PARQUET}/*.parquet') if 'train_base.parquet' not in path]","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:30:58.038523Z","iopub.execute_input":"2024-04-03T06:30:58.038946Z","iopub.status.idle":"2024-04-03T06:30:58.058104Z","shell.execute_reply.started":"2024-04-03T06:30:58.038912Z","shell.execute_reply":"2024-04-03T06:30:58.055524Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:30:58.060674Z","iopub.execute_input":"2024-04-03T06:30:58.061185Z","iopub.status.idle":"2024-04-03T06:30:58.073432Z","shell.execute_reply.started":"2024-04-03T06:30:58.061147Z","shell.execute_reply":"2024-04-03T06:30:58.071412Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"files, groups, struct_dict = file_list(path_list, 1)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:30:58.075708Z","iopub.execute_input":"2024-04-03T06:30:58.076063Z","iopub.status.idle":"2024-04-03T06:30:58.095788Z","shell.execute_reply.started":"2024-04-03T06:30:58.076035Z","shell.execute_reply":"2024-04-03T06:30:58.094677Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Amount of information","metadata":{}},{"cell_type":"markdown","source":"First, let's understand what we are dealing with: how many tables, and how much information is in them","metadata":{}},{"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-03T06:30:58.096903Z","iopub.execute_input":"2024-04-03T06:30:58.097232Z","iopub.status.idle":"2024-04-03T06:30:58.113477Z","shell.execute_reply.started":"2024-04-03T06:30:58.097207Z","shell.execute_reply":"2024-04-03T06:30:58.112158Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It's less than 1 GB! Easy-peasy! :)","metadata":{}},{"cell_type":"markdown","source":"## How many cases do we have?\nHow many cases do we have at all - in train_base? And are all cases found in other tables?","metadata":{}},{"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:30:58.115651Z","iopub.execute_input":"2024-04-03T06:30:58.116125Z","iopub.status.idle":"2024-04-03T06:30:58.689682Z","shell.execute_reply.started":"2024-04-03T06:30:58.116085Z","shell.execute_reply":"2024-04-03T06:30:58.688688Z"},"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-03T06:30:58.691425Z","iopub.execute_input":"2024-04-03T06:30:58.692122Z","iopub.status.idle":"2024-04-03T06:30:58.744754Z","shell.execute_reply.started":"2024-04-03T06:30:58.692079Z","shell.execute_reply":"2024-04-03T06:30:58.743600Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We have **1.5 million unique `case_id`** and a large imbalance of classes (**3% positive labels**).\n\nNow let's find out how many cases there are in each of our tables.","metadata":{}},{"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":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2024-04-03T06:30:58.750450Z","iopub.execute_input":"2024-04-03T06:30:58.751391Z","iopub.status.idle":"2024-04-03T06:30:58.763876Z","shell.execute_reply.started":"2024-04-03T06:30:58.751338Z","shell.execute_reply":"2024-04-03T06:30:58.761588Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:30:58.765648Z","iopub.execute_input":"2024-04-03T06:30:58.766514Z","iopub.status.idle":"2024-04-03T06:30:58.784497Z","shell.execute_reply.started":"2024-04-03T06:30:58.766469Z","shell.execute_reply":"2024-04-03T06:30:58.782579Z"},"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-03T06:30:58.787492Z","iopub.execute_input":"2024-04-03T06:30:58.793399Z","iopub.status.idle":"2024-04-03T06:31:02.145630Z","shell.execute_reply.started":"2024-04-03T06:30:58.793234Z","shell.execute_reply":"2024-04-03T06:31:02.144005Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It's a pity, but **7 out of 10** tables contain **less than a third** of case_id. Come on, it would be a third, **5** tables contain **less than a tenth** of the case_id. There will be too many nulls in our train tables...","metadata":{}},{"cell_type":"markdown","source":"## What is num_group?! \n<p style=\"font-size: 18px\"> That is the question! And not only for me... </p>\n\n - [What is num_group feature?](https://www.kaggle.com/competitions/home-credit-credit-risk-model-stability/discussion/475373)\n\n - [What is the meaning of num_group1 and num_group2 in depth1 and 2 dataset?](https://www.kaggle.com/competitions/home-credit-credit-risk-model-stability/discussion/476763)\n\nFrom the explanations of the competition organizers, we know that this is some kind of index for set of records when the relationship between the base_table and another is not one-to-one. There is an example of how this works for personal data, when 0 is the data of the person who applied for the loan, and the other indexes are the data of the people specified in the application. In this case, `num_group1` has an obvious meaning. Will this meaning be revealed to us in other cases? Also in the table with descriptions of the features there are clarifications for some details, but only for the case of `depth = 2`\n\nSo, what to do with the `num_group` feature? \nThe first thing that comes to mind is to use it as an ordinary ordinal categorical feature. But we group tables by `case_id`, and we will have to somehow aggregate `num_group`. Okay, we can just aggregate... But still, it has a separate meaning, different from other features. The second idea is to expand the features in a pivot table by columns. But this will be very cumbersome, and in the columns, as the value of `num_group` increases, there will be an increasing number of nulls.\n\nWhere it makes sense, let's use a special case of a pivot table - *filtering*, where we select a specific `num_group` value and process it separately. The remaining values are also processed separately, and then the columns are joined. For an example of this approach, see below, where we process the `train_person_1` table. There we separately process the data of the borrower (`num_group1=0`), and separately the data of other persons from the application (`num_group1>0`). In the part where we aggregate data about other persons from the application, we use the *max* aggregation function to `num_group` to save data about the number of this \"*other persons*\". Where it makes no sense to resort to such tricks, we simply aggregate `num_group`.","metadata":{}},{"cell_type":"markdown","source":"Let's start with a little statistical research, and evaluate how many unique values the `num_group1` feature has","metadata":{}},{"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-03T06:31:02.147479Z","iopub.execute_input":"2024-04-03T06:31:02.148832Z","iopub.status.idle":"2024-04-03T06:31:03.737717Z","shell.execute_reply.started":"2024-04-03T06:31:02.148794Z","shell.execute_reply":"2024-04-03T06:31:03.736329Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There are *500+* unique values in `train_credit_bureau_a_1`. This is an *external source* and obviously aggregates information from different banks, so it contains more information on different `case_id` than, for example, `train_applprev_1`, which is an *internal source* and stores information about previous applications in HomeCredit. As we will see below, generally the number of records for one `case_id` does not exceed 20, but there are also serial borrowers. Also in external sources about taxes there are many entries for some `case_id`, which is also understandable. It is also remarkable that the `num_group1` in the table `train_other_1` has only one unique value! ","metadata":{}},{"cell_type":"markdown","source":"Now let's estimate the distribution of `num_group1` at the `case_id` level. Some tables have a large set of unique `num_group1` values, how often do they occur at the `case_id` level?","metadata":{}},{"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:31:03.739659Z","iopub.execute_input":"2024-04-03T06:31:03.740564Z","iopub.status.idle":"2024-04-03T06:31:11.212737Z","shell.execute_reply.started":"2024-04-03T06:31:03.740518Z","shell.execute_reply":"2024-04-03T06:31:11.211541Z"},"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))\n","metadata":{"execution":{"iopub.status.busy":"2024-04-03T06:31:11.214228Z","iopub.execute_input":"2024-04-03T06:31:11.214980Z","iopub.status.idle":"2024-04-03T06:31:13.049636Z","shell.execute_reply.started":"2024-04-03T06:31:11.214940Z","shell.execute_reply":"2024-04-03T06:31:13.048238Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In general, we see that where the number of values is in the tens, the first five is most common, and where the number of values is in the hundreds, the first twenty. Stands out against this background \"slide with a dead end\" - `train_applprev_1`, and \"blue square\" - `train_other_1`.","metadata":{}},{"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":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2024-04-03T06:31:13.051137Z","iopub.execute_input":"2024-04-03T06:31:13.051602Z","iopub.status.idle":"2024-04-03T06:31:20.442507Z","shell.execute_reply.started":"2024-04-03T06:31:13.051560Z","shell.execute_reply":"2024-04-03T06:31:20.441543Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Types of features","metadata":{}},{"cell_type":"markdown","source":"In the [Part I](https://www.kaggle.com/code/pib73nl/home-credit-2024-eda-part-i/#Table-exploring), we already found out that during import, features of type L and T may turn out to be different dtypes. Let's take some useful stuff from [Part I](https://www.kaggle.com/code/pib73nl/home-credit-2024-eda-part-i), and look at what happens with the types of features in our tables.","metadata":{}},{"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:31:20.443760Z","iopub.execute_input":"2024-04-03T06:31:20.444284Z","iopub.status.idle":"2024-04-03T06:31:20.454326Z","shell.execute_reply.started":"2024-04-03T06:31:20.444255Z","shell.execute_reply":"2024-04-03T06:31:20.453415Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:31:20.455663Z","iopub.execute_input":"2024-04-03T06:31:20.456193Z","iopub.status.idle":"2024-04-03T06:31:20.480992Z","shell.execute_reply.started":"2024-04-03T06:31:20.456165Z","shell.execute_reply":"2024-04-03T06:31:20.480042Z"},"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())\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:31:20.482417Z","iopub.execute_input":"2024-04-03T06:31:20.482870Z","iopub.status.idle":"2024-04-03T06:31:39.980366Z","shell.execute_reply.started":"2024-04-03T06:31:20.482832Z","shell.execute_reply":"2024-04-03T06:31:39.979166Z"},"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-03T06:31:39.981809Z","iopub.execute_input":"2024-04-03T06:31:39.982263Z","iopub.status.idle":"2024-04-03T06:31:40.005628Z","shell.execute_reply.started":"2024-04-03T06:31:39.982224Z","shell.execute_reply":"2024-04-03T06:31:40.004443Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:31:40.007159Z","iopub.execute_input":"2024-04-03T06:31:40.008060Z","iopub.status.idle":"2024-04-03T06:31:40.015071Z","shell.execute_reply.started":"2024-04-03T06:31:40.008017Z","shell.execute_reply":"2024-04-03T06:31:40.013730Z"},"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-03T06:31:40.017612Z","iopub.execute_input":"2024-04-03T06:31:40.018287Z","iopub.status.idle":"2024-04-03T06:31:43.076530Z","shell.execute_reply.started":"2024-04-03T06:31:40.018178Z","shell.execute_reply":"2024-04-03T06:31:43.075310Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"To cover the maximum number of options, consider the following tables:\n 1. `train_applprev_1` (small number of unique `num_group1` values; individual distribution of `num_group1` values; presence of a custom type converted to several dtypes)\n 2. `train_person_1` (we know how to interpret the `num_group1` attribute here, and we will try to extract something useful from it)\n 3. `train_credit_bureau_a_1` (this table has the largest size, both in megabytes - *443*, and in terms of the number of features - *77*, and it has the largest number of unique `num_group` values - *529*! Among other things, it is interesting to understand whether there will be problems with RAM when processing such a large file)\n\nAnd these tables also cover 80-100% of `case_id` values! Forward!","metadata":{}},{"cell_type":"markdown","source":"# Analysis of selected tables","metadata":{}},{"cell_type":"markdown","source":"Let's load the selected tables and immediately convert some automatically assigned dtypes","metadata":{}},{"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-03T06:31:43.078275Z","iopub.execute_input":"2024-04-03T06:31:43.078694Z","iopub.status.idle":"2024-04-03T06:31:43.105012Z","shell.execute_reply.started":"2024-04-03T06:31:43.078659Z","shell.execute_reply":"2024-04-03T06:31:43.103677Z"},"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-03T06:31:43.107331Z","iopub.execute_input":"2024-04-03T06:31:43.107736Z","iopub.status.idle":"2024-04-03T06:31:43.120793Z","shell.execute_reply.started":"2024-04-03T06:31:43.107704Z","shell.execute_reply":"2024-04-03T06:31:43.119689Z"},"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-03T06:31:43.122634Z","iopub.execute_input":"2024-04-03T06:31:43.123095Z","iopub.status.idle":"2024-04-03T06:31:43.145953Z","shell.execute_reply.started":"2024-04-03T06:31:43.123061Z","shell.execute_reply":"2024-04-03T06:31:43.144974Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Along comes a 4D analysis of fullness :)","metadata":{}},{"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-03T06:31:43.147084Z","iopub.execute_input":"2024-04-03T06:31:43.147863Z","iopub.status.idle":"2024-04-03T06:31:43.166135Z","shell.execute_reply.started":"2024-04-03T06:31:43.147819Z","shell.execute_reply":"2024-04-03T06:31:43.164913Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":" **First two**: The names of features and `num_group` values are located along the axes.\n\n **Third**: The size of the circles shows how many values are not nulls. The larger the circle size, the more `case_id-num_group` combinations are not null. For example, for num_group=1 we have 100 rows. Some features (columns) are filled in completely - they have the largest circle, some have nulls - their circles decrease in proportion to the number of nulls. If there is no circle at all, then this feature has no non-null values at all in this group. Fine! But the size of the circle says nothing about how many of the one and a half million possible case_ids there are for a given group. We need a fourth dimension!\n\n **Forth**: The color of the circles shows how many `case_id` values there are for a given attribute and group number. For example, for num_group=9 we have 10 rows and all are non-zero. The circle will be as large as possible. But we have one and a half million cases, and there are only 10 rows here! Such a case will be displayed in blue.","metadata":{}},{"cell_type":"markdown","source":"Let's start with something simple - `train_person_1`. The picture is in full view! \n\nWe see features that are practically not filled out (birthdate_87D, childnum_185L, gender_992L,housingtype_772L etc.). \n\nSome features are completed only for the borrower (birth_259D, incometype_1044T etc.), some only for additional persons in the application (relationshiptoclient_415T, remitter_829L etc.). This reflects the specific meaning of the `num_group1` feature for tables with personal data.\n\nThere are a dozen features that do not have nulls in any group. Basically these are features of type M. But the colors indicate that not all is well with them either, because starting from the first group, the coverage of cases decreases to 50%, and starting from the fourth, it drops to 10%. From an applied point of view, this means that there are simply different numbers of additional persons in loan applications.\n\nFeel free to see something of your own in this picture...","metadata":{}},{"cell_type":"code","source":"nulls_by_feat_n_gr(person_df_conv)","metadata":{"execution":{"iopub.status.busy":"2024-04-03T06:31:43.167867Z","iopub.execute_input":"2024-04-03T06:31:43.170188Z","iopub.status.idle":"2024-04-03T06:31:46.658141Z","shell.execute_reply.started":"2024-04-03T06:31:43.170125Z","shell.execute_reply":"2024-04-03T06:31:46.656496Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As stated above, table `train_applprev_1_*` does not include all cases (about 80%). Therefore, the color scale does not reach 100%. Starting from group 12, coverage drops below 10% of cases. In general, based on the source for this diagram, you can build a feature selection algorithm","metadata":{}},{"cell_type":"code","source":"nulls_by_feat_n_gr(applprev_df_conv, height=700)","metadata":{"execution":{"iopub.status.busy":"2024-04-03T06:31:46.665946Z","iopub.execute_input":"2024-04-03T06:31:46.667930Z","iopub.status.idle":"2024-04-03T06:31:58.325499Z","shell.execute_reply.started":"2024-04-03T06:31:46.667873Z","shell.execute_reply":"2024-04-03T06:31:58.323579Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Even in the [Part I](https://www.kaggle.com/code/pib73nl/home-credit-2024-eda-part-i) it was clear that table `train_credit_bureau_a_1_*` contains the largest number of zeros. Therefore, here we also see many small dots *(note the different scale - here it is slightly smaller than in the diagram above)*. I didn’t dare display this table in its entirety (only 50 out of 500 groups) - it wouldn’t be very clear, and it wouldn’t fit into memory. You can try to display iteratively in 50 groups, but I don’t promise you anything interesting there :), since already from the 16th group the number of cases drops to 10%, and by the 50th it drops to 0.2%","metadata":{}},{"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-03T06:31:58.327520Z","iopub.execute_input":"2024-04-03T06:31:58.327978Z","iopub.status.idle":"2024-04-03T06:32:56.974098Z","shell.execute_reply.started":"2024-04-03T06:31:58.327939Z","shell.execute_reply":"2024-04-03T06:32:56.971696Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Table - train_person_1","metadata":{}},{"cell_type":"markdown","source":"When considering the table, we will act according to the following algorithm:\n - decide how to deal with `num_group`\n - divide the features into numeric, string and logical (work with them separately)\n   - **numeric**: filter out invariants and use quantile transformation\n   - **string**: when we have a one-to-one relationship between `case_id` and some feature, we can simply cast it to a categorical type and rely on the internal tool of the model (if this is, for example, some kind of boosting). But for tables with *groups*, we first need to somehow aggregate the features, including categorical ones. Let's try using a custom transformer\n   - **logical**: throw out the invariants, leave the rest as is\n - join the resulting tables and admire the result!","metadata":{}},{"cell_type":"markdown","source":"Let's start with `person_df`. We alredy know that data with `num_group1=0` is borrower data. When `num_group1>0` this is the data of additional persons in the application. \n\nWe noticed above that some features are filled out only for the borrower, and some only for additional persons in the application. So I will process them separately. For most features, data on borrowers is available for all `case_id`. Let's filter out those that are more than 90% full. There can be up to 9 additional persons in the application. It is possible that these are different applications, since we also have a table with previous applications. In any case, we need to somehow aggregate the data where `num_group1>0` by `case_id`. Expanding them into several columns (pivot) will be cumbersome, since the number of `case_id` drops quickly with increasing `num_group` value (see the diagram above). Apparently we will aggregate within each column (sum, average, maximum, minimum - depending on the situation).","metadata":{}},{"cell_type":"markdown","source":"### Selection of features and separation by data types (`num_group=0`)","metadata":{}},{"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-03T06:32:56.977116Z","iopub.execute_input":"2024-04-03T06:32:56.978660Z","iopub.status.idle":"2024-04-03T06:33:01.337884Z","shell.execute_reply.started":"2024-04-03T06:32:56.978601Z","shell.execute_reply":"2024-04-03T06:33:01.336620Z"},"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-03T06:33:01.339721Z","iopub.execute_input":"2024-04-03T06:33:01.340170Z","iopub.status.idle":"2024-04-03T06:33:01.346750Z","shell.execute_reply.started":"2024-04-03T06:33:01.340129Z","shell.execute_reply":"2024-04-03T06:33:01.345419Z"},"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-03T06:33:01.348781Z","iopub.execute_input":"2024-04-03T06:33:01.349246Z","iopub.status.idle":"2024-04-03T06:33:01.363955Z","shell.execute_reply.started":"2024-04-03T06:33:01.349215Z","shell.execute_reply":"2024-04-03T06:33:01.362775Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Deal with numeric features","metadata":{}},{"cell_type":"code","source":"# what have we already got?\nperson_df_num.collect().describe()","metadata":{"execution":{"iopub.status.busy":"2024-04-03T06:33:01.365903Z","iopub.execute_input":"2024-04-03T06:33:01.366847Z","iopub.status.idle":"2024-04-03T06:33:01.974546Z","shell.execute_reply.started":"2024-04-03T06:33:01.366805Z","shell.execute_reply":"2024-04-03T06:33:01.973642Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In addition to `num_group1`, the features `personindex*` and `persontype*` are clearly unnecessary here, because they have no variability. ","metadata":{}},{"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-03T06:33:01.975886Z","iopub.execute_input":"2024-04-03T06:33:01.977231Z","iopub.status.idle":"2024-04-03T06:33:02.802365Z","shell.execute_reply.started":"2024-04-03T06:33:01.977195Z","shell.execute_reply":"2024-04-03T06:33:02.801036Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"To normalize the remaining features, we use [quantile transformation](https://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.QuantileTransformer.html), it handles outliers well.","metadata":{}},{"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-03T06:33:02.803959Z","iopub.execute_input":"2024-04-03T06:33:02.804358Z","iopub.status.idle":"2024-04-03T06:33:02.817307Z","shell.execute_reply.started":"2024-04-03T06:33:02.804330Z","shell.execute_reply":"2024-04-03T06:33:02.815805Z"},"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-03T06:33:02.819230Z","iopub.execute_input":"2024-04-03T06:33:02.819805Z","iopub.status.idle":"2024-04-03T06:33:19.600863Z","shell.execute_reply.started":"2024-04-03T06:33:02.819763Z","shell.execute_reply":"2024-04-03T06:33:19.599663Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There is a difference, isn't there? Hope this helps :)","metadata":{}},{"cell_type":"markdown","source":"### Deal with categorical data","metadata":{}},{"cell_type":"markdown","source":"Let's take some stuff from the [Part I](https://www.kaggle.com/code/pib73nl/home-credit-2024-eda-part-i) to visualize the overall picture with categorical features","metadata":{}},{"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-03T06:33:19.602128Z","iopub.execute_input":"2024-04-03T06:33:19.602452Z","iopub.status.idle":"2024-04-03T06:33:19.612898Z","shell.execute_reply.started":"2024-04-03T06:33:19.602427Z","shell.execute_reply":"2024-04-03T06:33:19.611530Z"},"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-03T06:33:19.616372Z","iopub.execute_input":"2024-04-03T06:33:19.617060Z","iopub.status.idle":"2024-04-03T06:33:23.604583Z","shell.execute_reply.started":"2024-04-03T06:33:19.617028Z","shell.execute_reply":"2024-04-03T06:33:23.603076Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Here we see that we have several features with more than 3 thousand unique values (address data). There are also several features with a small number of unique values (within 10). It would be a good idea to consider different options for these cases...\n\nIf you are using any boosting models, you may be relying on their internal tools. But in any case, the issue of approach to aggregation of categories by `case_id` will have to be resolved. Now we are considering `num_group1=0`, that is, `case_id` is our primary key, and we do not need category aggregation. But below, when we consider `num_group>0`, we will have to somehow collapse the data from several groups into one `case_id`. If this is inevitable, why don't we take this approach now without delay?\n\nPlease also pay attention to the details where one value prevails in all cases. For example, `type_25L`. There will be very little variability here","metadata":{}},{"cell_type":"markdown","source":"Here's our approach: first convert features to a number type using a **custom transformer**, and then apply a **quantile transformer** to the result","metadata":{}},{"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-03T06:33:23.606149Z","iopub.execute_input":"2024-04-03T06:33:23.606571Z","iopub.status.idle":"2024-04-03T06:33:23.612961Z","shell.execute_reply.started":"2024-04-03T06:33:23.606536Z","shell.execute_reply":"2024-04-03T06:33:23.611797Z"},"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-03T06:33:23.614631Z","iopub.execute_input":"2024-04-03T06:33:23.614960Z","iopub.status.idle":"2024-04-03T06:33:30.502079Z","shell.execute_reply.started":"2024-04-03T06:33:23.614931Z","shell.execute_reply":"2024-04-03T06:33:30.500980Z"},"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-03T06:33:30.505021Z","iopub.execute_input":"2024-04-03T06:33:30.505958Z","iopub.status.idle":"2024-04-03T06:34:12.775070Z","shell.execute_reply.started":"2024-04-03T06:33:30.505916Z","shell.execute_reply":"2024-04-03T06:34:12.774188Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Fearures with a small number of unique values and low variability are immediately noticeable. Let's leave them as is for now","metadata":{}},{"cell_type":"markdown","source":"### Boolean features","metadata":{}},{"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-03T06:34:12.776347Z","iopub.execute_input":"2024-04-03T06:34:12.776890Z","iopub.status.idle":"2024-04-03T06:34:13.497999Z","shell.execute_reply.started":"2024-04-03T06:34:12.776859Z","shell.execute_reply":"2024-04-03T06:34:13.495371Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Obviously, these details are useless (of course, when `num_group=0`) - one of them is one hundred percent invariant, and two are almost. \n\nBut this is a special case. We need to solve the problem of how to aggregate boolean features by `case_id`. Taking into account that for each `case_id` there can be a different number of `num_groups`, we will sum up the boolean features and divide by the maximum `num_group1` within each `case_id`","metadata":{}},{"cell_type":"markdown","source":"## Tables: train_applprev_1 & train_credit_bureau_a_1","metadata":{}},{"cell_type":"markdown","source":"It would be boring to look at two more tables separately, so let's put the drafts together","metadata":{}},{"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-03T06:34:13.500147Z","iopub.execute_input":"2024-04-03T06:34:13.500631Z","iopub.status.idle":"2024-04-03T06:34:13.526615Z","shell.execute_reply.started":"2024-04-03T06:34:13.500592Z","shell.execute_reply":"2024-04-03T06:34:13.525349Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Next, we simply prepare several datasets and join everything into one","metadata":{}},{"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-03T06:34:13.528155Z","iopub.execute_input":"2024-04-03T06:34:13.528608Z","iopub.status.idle":"2024-04-03T06:34:26.323822Z","shell.execute_reply.started":"2024-04-03T06:34:13.528570Z","shell.execute_reply":"2024-04-03T06:34:26.323024Z"},"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-03T06:34:26.325058Z","iopub.execute_input":"2024-04-03T06:34:26.325389Z","iopub.status.idle":"2024-04-03T06:35:01.875519Z","shell.execute_reply.started":"2024-04-03T06:34:26.325363Z","shell.execute_reply":"2024-04-03T06:35:01.874039Z"},"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-03T06:35:01.878154Z","iopub.execute_input":"2024-04-03T06:35:01.878721Z","iopub.status.idle":"2024-04-03T06:35:05.071486Z","shell.execute_reply.started":"2024-04-03T06:35:01.878679Z","shell.execute_reply":"2024-04-03T06:35:05.070284Z"},"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-03T06:35:05.072801Z","iopub.execute_input":"2024-04-03T06:35:05.073271Z","iopub.status.idle":"2024-04-03T06:35:07.434823Z","shell.execute_reply.started":"2024-04-03T06:35:05.073241Z","shell.execute_reply":"2024-04-03T06:35:07.433950Z"},"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-03T06:35:07.436785Z","iopub.execute_input":"2024-04-03T06:35:07.437216Z","iopub.status.idle":"2024-04-03T06:35:36.335394Z","shell.execute_reply.started":"2024-04-03T06:35:07.437176Z","shell.execute_reply":"2024-04-03T06:35:36.334323Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Model","metadata":{}},{"cell_type":"markdown","source":"And now, since we have collected some semblance of a dataset, let’s try to combine it with the dataset from the [starter notebook](https://www.kaggle.com/code/jetakow/home-credit-2024-starter-notebook) and run it on exactly the same model. Let's see if we can improve the result slightly ;)\n\nTo do this, let’s borrow the code kindly provided by [@Daniel Herman](https://www.kaggle.com/jetakow), and with minimal changes that do not concern the model parameters, we’ll run it and look at the result!","metadata":{}},{"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\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","metadata":{"execution":{"iopub.status.busy":"2024-04-03T06:35:36.336977Z","iopub.execute_input":"2024-04-03T06:35:36.337965Z","iopub.status.idle":"2024-04-03T06:35:36.348240Z","shell.execute_reply.started":"2024-04-03T06:35:36.337929Z","shell.execute_reply":"2024-04-03T06:35:36.346207Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_static = pl.concat(\n    [\n        pl.read_csv(MAIN_PATH + \"/csv_files/train/train_static_0_0.csv\").pipe(set_table_dtypes),\n        pl.read_csv(MAIN_PATH + \"/csv_files/train/train_static_0_1.csv\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\ntrain_static_cb = pl.read_csv(MAIN_PATH + \"/csv_files/train/train_static_cb_0.csv\").pipe(set_table_dtypes)\ntrain_person_1 = pl.read_csv(MAIN_PATH + \"/csv_files/train/train_person_1.csv\").pipe(set_table_dtypes) \ntrain_credit_bureau_b_2 = pl.read_csv(MAIN_PATH + \"/csv_files/train/train_credit_bureau_b_2.csv\").pipe(set_table_dtypes) ","metadata":{"execution":{"iopub.status.busy":"2024-04-03T06:35:36.350803Z","iopub.execute_input":"2024-04-03T06:35:36.351244Z","iopub.status.idle":"2024-04-03T06:35:53.555000Z","shell.execute_reply.started":"2024-04-03T06:35:36.351209Z","shell.execute_reply":"2024-04-03T06:35:53.553762Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# We need to use aggregation functions in tables with depth > 1, so tables that contain num_group1 column or \n# also num_group2 column.\ntrain_person_1_feats_1 = train_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").max().alias(\"mainoccupationinc_384A_max\"),\n    (pl.col(\"incometype_1044T\") == \"SELFEMPLOYED\").max().alias(\"mainoccupationinc_384A_any_selfemployed\")\n)\n\n# Here num_group1=0 has special meaning, it is the person who applied for the loan.\ntrain_person_1_feats_2 = train_person_1.select([\"case_id\", \"num_group1\", \"housetype_905L\"]).filter(\n    pl.col(\"num_group1\") == 0\n).drop(\"num_group1\").rename({\"housetype_905L\": \"person_housetype\"})\n\n# Here we have num_goup1 and num_group2, so we need to aggregate again.\ntrain_credit_bureau_b_2_feats = train_credit_bureau_b_2.group_by(\"case_id\").agg(\n    pl.col(\"pmts_pmtsoverdue_635A\").max().alias(\"pmts_pmtsoverdue_635A_max\"),\n    (pl.col(\"pmts_dpdvalue_108P\") > 31).max().alias(\"pmts_dpdvalue_108P_over31\")\n)\n\n# We will process in this examples only A-type and M-type columns, so we need to select them.\nselected_static_cols = []\nfor col in train_static.columns:\n    if col[-1] in (\"A\", \"M\"):\n        selected_static_cols.append(col)\nprint(selected_static_cols)\n\nselected_static_cb_cols = []\nfor col in train_static_cb.columns:\n    if col[-1] in (\"A\", \"M\"):\n        selected_static_cb_cols.append(col)\nprint(selected_static_cb_cols)\n\n# Join all tables together.\n#########################################\n# here we join datasets\n#########################################\ndata = data.join(\n    train_static.select([\"case_id\"]+selected_static_cols), how=\"left\", on=\"case_id\"\n).join(\n    train_static_cb.select([\"case_id\"]+selected_static_cb_cols), how=\"left\", on=\"case_id\"\n).join(\n    train_person_1_feats_1, how=\"left\", on=\"case_id\"\n).join(\n    train_person_1_feats_2, how=\"left\", on=\"case_id\"\n).join(\n    train_credit_bureau_b_2_feats, how=\"left\", on=\"case_id\"\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-03T06:35:53.557880Z","iopub.execute_input":"2024-04-03T06:35:53.558396Z","iopub.status.idle":"2024-04-03T06:35:56.337024Z","shell.execute_reply.started":"2024-04-03T06:35:53.558352Z","shell.execute_reply":"2024-04-03T06:35:56.336042Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"case_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\n###########################################################\n# here we slightly change the approach to selecting columns\n###########################################################\ncols_pred = [clmn for clmn in data.columns if clmn not in base.columns]\n# cols_pred = []\n# for col in data.columns:\n#     if col[-1].isupper() and col[:-1].islower():\n#         cols_pred.append(col)\n\nprint(cols_pred)\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\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","metadata":{"execution":{"iopub.status.busy":"2024-04-03T06:35:56.338003Z","iopub.execute_input":"2024-04-03T06:35:56.338351Z","iopub.status.idle":"2024-04-03T06:36:29.181240Z","shell.execute_reply.started":"2024-04-03T06:35:56.338324Z","shell.execute_reply":"2024-04-03T06:36:29.179876Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lgb_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    \"n_estimators\": 1000,\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)","metadata":{"execution":{"iopub.status.busy":"2024-04-03T06:36:29.182794Z","iopub.execute_input":"2024-04-03T06:36:29.183136Z","iopub.status.idle":"2024-04-03T06:39:43.227657Z","shell.execute_reply.started":"2024-04-03T06:36:29.183108Z","shell.execute_reply":"2024-04-03T06:39:43.226584Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There is still room for improvement here, but we agreed not to touch the default model settings from the [starter notebook](https://www.kaggle.com/code/jetakow/home-credit-2024-starter-notebook)","metadata":{}},{"cell_type":"code","source":"for _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\"])}')  ","metadata":{"execution":{"iopub.status.busy":"2024-04-03T06:39:43.229064Z","iopub.execute_input":"2024-04-03T06:39:43.229499Z","iopub.status.idle":"2024-04-03T06:40:14.962621Z","shell.execute_reply.started":"2024-04-03T06:39:43.229465Z","shell.execute_reply":"2024-04-03T06:40:14.961639Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def 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\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-03T06:40:14.963766Z","iopub.execute_input":"2024-04-03T06:40:14.964948Z","iopub.status.idle":"2024-04-03T06:40:15.969894Z","shell.execute_reply.started":"2024-04-03T06:40:14.964919Z","shell.execute_reply":"2024-04-03T06:40:15.969172Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"What can we state?... This is not a breakthrough, but it is obvious that there is some improvement... I probably won’t do a submission yet :)","metadata":{}},{"cell_type":"markdown","source":"# Conclusions\n\nWhat conclusions can be drawn:\n- for gradient boosting on decision trees, it is better to leave categorical features alone as much as possible (well, it's written in the documentation :)\n- quantile transformation had to be disabled, since skewed distributions with outliers are digested better by gradient boosting on decision trees\n- increasing the amount of data, even taking into account the fact that there are many nulls in it, has a positive effect on the result\n\nSome things were already clear, but others were a little puzzling... This is how black magic is exposed :)\n\nIn general, there is still `depth=2` ahead and it’s time to start modeling! Working!","metadata":{}}]}