{"metadata":{"kernelspec":{"name":"python3","display_name":"Python 3","language":"python"},"language_info":{"name":"python","version":"3.10.14","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":30761,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"## Introduction\nIn this post, we'll demonstrate how to use Ibis and [IbisML](https://github.com/ibis-project/ibis-ml)\nend-to-end for this competition.\n\n1. Load data and perform feature engineering on DuckDB backend using IbisML\n2. Perform last-mile ML data preprocessing on DuckDB backend using IbisML\n3. Train two models using different frameworks:\n    * An XGBoost model within a scikit-learn pipeline.\n    * A neural network with PyTorch and PyTorch Lightning.\n\nThe aim of this competition is to predict which clients are more likely to default on their\nloans by using both internal and external data sources.\n\nTo get started with Ibis and IbisML, please refer to the websites:\n\n* [Ibis](https://ibis-project.org/): An open-source dataframe library that works with any data system.\n* [IbisML](https://ibis-project.github.io/ibis-ml/): A library for building scalable ML pipelines.\n\n\n## Prerequisites\nTo run this example, you'll need to download the data from Kaggle website with a Kaggle user account and install Ibis, IbisML, and the necessary modeling library.\n\n\n### Install libraries\nTo use Ibis and IbisML with the DuckDB backend for building models, you'll need to install the\nnecessary packages. Depending on your preferred machine learning framework, you can choose\none of the following installation commands:","metadata":{}},{"cell_type":"code","source":"!pip install --upgrade ibis-framework['duckdb'] ibis-ml ","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:54:12.841440Z","iopub.execute_input":"2024-08-31T02:54:12.841914Z","iopub.status.idle":"2024-08-31T02:54:29.271336Z","shell.execute_reply.started":"2024-08-31T02:54:12.841874Z","shell.execute_reply":"2024-08-31T02:54:29.270012Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pip show ibis-framework","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:54:29.958647Z","iopub.execute_input":"2024-08-31T02:54:29.959167Z","iopub.status.idle":"2024-08-31T02:54:44.988717Z","shell.execute_reply.started":"2024-08-31T02:54:29.959112Z","shell.execute_reply":"2024-08-31T02:54:44.987074Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import ibis\nimport ibis.expr.datatypes as dt\nfrom ibis import _\nimport ibis_ml as ml\nfrom pathlib import Path\nfrom glob import glob\n\n# enable interactive mode for ibis\nibis.options.interactive = True","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:50:37.286576Z","iopub.execute_input":"2024-08-31T02:50:37.287797Z","iopub.status.idle":"2024-08-31T02:50:38.117974Z","shell.execute_reply.started":"2024-08-31T02:50:37.287730Z","shell.execute_reply":"2024-08-31T02:50:38.116659Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Set the backend for computing:","metadata":{}},{"cell_type":"code","source":"con = ibis.duckdb.connect()\n# remove the black bars from duckdb's progress bar\ncon.raw_sql(\"set enable_progress_bar = false\")\n# DuckDB is the default backend for Ibis\nibis.set_backend(con)","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:06.358139Z","iopub.execute_input":"2024-08-31T02:44:06.358831Z","iopub.status.idle":"2024-08-31T02:44:06.711646Z","shell.execute_reply.started":"2024-08-31T02:44:06.358774Z","shell.execute_reply":"2024-08-31T02:44:06.710246Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Set data path:","metadata":{}},{"cell_type":"code","source":"# change the root path to yours\nROOT = Path(\"/kaggle/input/home-credit-credit-risk-model-stability/\")\nTRAIN_DIR = ROOT / \"parquet_files\" / \"train\"\nTEST_DIR = ROOT / \"parquet_files\" / \"test\"","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:06.715659Z","iopub.execute_input":"2024-08-31T02:44:06.716107Z","iopub.status.idle":"2024-08-31T02:44:06.721818Z","shell.execute_reply.started":"2024-08-31T02:44:06.716049Z","shell.execute_reply":"2024-08-31T02:44:06.720589Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Data loading and processing\nWe'll use Ibis to read the Parquet files and perform the necessary processing for the next step.\n\n### Directory structure and tables\nSince there are many data files, let's start by examining the directory structure and\ntables within the train directory:","metadata":{}},{"cell_type":"code","source":"!tree -L 2 \"/kaggle/input/home-credit-credit-risk-model-stability/\"","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:06.723646Z","iopub.execute_input":"2024-08-31T02:44:06.724740Z","iopub.status.idle":"2024-08-31T02:44:07.890616Z","shell.execute_reply.started":"2024-08-31T02:44:06.724656Z","shell.execute_reply":"2024-08-31T02:44:07.889247Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!tree -L 2 \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train\"","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:07.892327Z","iopub.execute_input":"2024-08-31T02:44:07.892693Z","iopub.status.idle":"2024-08-31T02:44:09.085785Z","shell.execute_reply.started":"2024-08-31T02:44:07.892655Z","shell.execute_reply":"2024-08-31T02:44:09.084324Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nThe `train_base.parquet` file is the base table, while the others are feature tables.\nLet's take a quick look at these tables.\n\n#### Base table\nThe base table (`train_base.parquet`) contains the unique ID, a binary target flag\nand other information for the training samples. This unique ID will serve as the\nlinking key for joining with other feature tables.\n\n* `case_id` - This is the unique ID for each loan. You'll need this ID to\n  join feature tables to the base table. There are about 1.5m unique loans.\n* `date_decision` - This refers to the date when a decision was made regarding the\n  approval of the loan.\n* `WEEK_NUM` - This is the week number used for aggregation. In the test sample,\n    `WEEK_NUM` continues sequentially from the last training value of `WEEK_NUM`.\n* `MONTH` - This column represents the month when the approval decision was made.\n* `target` - This is the binary target flag, determined after a certain period based on\n  whether or not the client defaulted on the specific loan.\n\nHere is several examples from the base table:","metadata":{}},{"cell_type":"code","source":"con.read_parquet(TRAIN_DIR / \"train_base.parquet\").head(5)","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:09.087837Z","iopub.execute_input":"2024-08-31T02:44:09.088377Z","iopub.status.idle":"2024-08-31T02:44:09.187650Z","shell.execute_reply.started":"2024-08-31T02:44:09.088320Z","shell.execute_reply":"2024-08-31T02:44:09.186193Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Feature tables\nThe remaining files contain features, consisting of approximately 370 features from\nprevious loan applications and external data sources. Their definitions can be found in the feature\ndefinition [file](https://www.kaggle.com/competitions/home-credit-credit-risk-model-stability/data)\nfrom the competition website.\n\nThere are several things we want to mention for the feature tables:\n\n* **Union datasets**: One dataset could be saved into multiple parquet files, such as\n`train_applprev_1_0.parquet` and `train_applprev_1_1.parquet`, We need to union this data.\n* **Dataset levels**: Datasets may have different levels, which we will explain as\nfollows:\n     * **Depth = 0**: Each row in the table is identified by a unique `case_id`.\n     In this case, you can directly join the features with the base table and use them as\n     features for further analysis or processing.\n     * **Depth > 0**:  You will group the data based on the `case_id` and perform calculations\n     or aggregations within each group.\n\nHere are two examples of tables with different levels.\n\nExample of table with depth = 0, `case_id` is the row identifier, features can be directly joined\n with the base table.","metadata":{}},{"cell_type":"code","source":"ibis.read_parquet(TRAIN_DIR / \"train_static_cb_0.parquet\").head(5)","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:09.189477Z","iopub.execute_input":"2024-08-31T02:44:09.190001Z","iopub.status.idle":"2024-08-31T02:44:09.373979Z","shell.execute_reply.started":"2024-08-31T02:44:09.189934Z","shell.execute_reply":"2024-08-31T02:44:09.372822Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Example of a table with depth = 1, we need to aggregate the features and collect statistics\nbased on `case_id` then join with the base table.","metadata":{}},{"cell_type":"code","source":"ibis.read_parquet(TRAIN_DIR / \"train_credit_bureau_b_1.parquet\").relocate(\n    \"num_group1\"\n).order_by([\"case_id\", \"num_group1\"]).head(5)","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:09.375629Z","iopub.execute_input":"2024-08-31T02:44:09.376129Z","iopub.status.idle":"2024-08-31T02:44:09.628722Z","shell.execute_reply.started":"2024-08-31T02:44:09.376049Z","shell.execute_reply":"2024-08-31T02:44:09.627555Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"For more details on features and its exploratory data analysis (EDA), you can refer to\nfeature definition and these Kaggle notebooks:\n\n* [Feature\n  definition](https://www.kaggle.com/competitions/home-credit-credit-risk-model-stability/data#:~:text=calendar_view_week-,feature_definitions,-.csv)\n* [Home credit risk prediction\n  EDA](https://www.kaggle.com/code/loki97/home-credit-risk-prediction-eda)\n* [Home credit CRMS 2024\n  EDA](https://www.kaggle.com/code/sergiosaharovskiy/home-credit-crms-2024-eda-and-submission)\n\n### Data loading and processing\nWe will perform the following data processing steps using Ibis and IbisML:\n\n* **Convert data types**: Ensure consistency by converting data types, as the same column\n  in different sub-files may have different types.\n* **Aggregate features**: For tables with depth greater than 0, aggregate features based\n  on `case_id`, including statistics calculation. You can collect statistics such as mean,\n  median, mode, minimum, standard deviation, and others.\n* **Union and join datasets**: Combine multiple sub-files of the same dataset into one\n  table, as some datasets are split into multiple sub-files with a common prefix. Afterward,\n  join these tables with the base table.\n\n#### Convert data types\nWe'll use IbisML to create a chain of `Cast` steps, forming a recipe for data type\nconversion across the dataset. This conversion is based on the provided information\nextracted from column names. Columns that have similar transformations are indicated by a\ncapital letter at the end of their names:\n\n* P - Transform DPD (Days past due)\n* M - Masking categories\n* A - Transform amount\n* D - Transform date\n* T - Unspecified Transform\n* L - Unspecified Transform\n\nFor example, we'll define a IbisML transformation step to convert columns ends with `P`\nto floating number:","metadata":{}},{"cell_type":"code","source":"# convert columns ends with P to floating number\nstep_cast_P_to_float = ml.Cast(ml.endswith(\"P\"), dt.float64)","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:09.630324Z","iopub.execute_input":"2024-08-31T02:44:09.630789Z","iopub.status.idle":"2024-08-31T02:44:09.636952Z","shell.execute_reply.started":"2024-08-31T02:44:09.630734Z","shell.execute_reply":"2024-08-31T02:44:09.635634Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Next, let's define additional type conversion transformations based on the postfix of column names:","metadata":{}},{"cell_type":"code","source":"# convert columns ends with A to floating number\nstep_cast_A_to_float = ml.Cast(ml.endswith(\"A\"), dt.float64)\n# convert columns ends with D to date\nstep_cast_D_to_date = ml.Cast(ml.endswith(\"D\"), dt.date)\n# convert columns ends with M to str\nstep_cast_M_to_str = ml.Cast(ml.endswith(\"M\"), dt.str)","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:09.638403Z","iopub.execute_input":"2024-08-31T02:44:09.638777Z","iopub.status.idle":"2024-08-31T02:44:09.649977Z","shell.execute_reply.started":"2024-08-31T02:44:09.638737Z","shell.execute_reply":"2024-08-31T02:44:09.648767Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We'll construct the\n[IbisML Recipe](https://ibis-project.github.io/ibis-ml/reference/core.html#ibis_ml.Recipe)\nwhich chains together all the transformation steps.","metadata":{}},{"cell_type":"code","source":"data_type_recipes = ml.Recipe(\n    step_cast_P_to_float,\n    step_cast_D_to_date,\n    step_cast_M_to_str,\n    step_cast_A_to_float,\n    # cast some special columns\n    ml.Cast([\"date_decision\"], \"date\"),\n    ml.Cast([\"case_id\", \"WEEK_NUM\", \"num_group1\", \"num_group2\"], dt.int64),\n    ml.Cast(\n        [\n            \"cardtype_51L\",\n            \"credacc_status_367L\",\n            \"requesttype_4525192L\",\n            \"riskassesment_302T\",\n            \"max_periodicityofpmts_997L\",\n        ],\n        dt.str,\n    ),\n    ml.Cast(\n        [\n            \"isbidproductrequest_292L\",\n            \"isdebitcard_527L\",\n            \"equalityempfrom_62L\",\n        ],\n        dt.int64,\n    ),\n)\nprint(f\"Data format conversion recipe:\\n{data_type_recipes}\")","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:09.651689Z","iopub.execute_input":"2024-08-31T02:44:09.652554Z","iopub.status.idle":"2024-08-31T02:44:09.666532Z","shell.execute_reply.started":"2024-08-31T02:44:09.652496Z","shell.execute_reply":"2024-08-31T02:44:09.665312Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\nIbisML offers a powerful set of column selectors, allowing you to select columns based\non names, types, and patterns. For more information, you can refer to the IbisML column\nselectors [documentation](https://ibis-project.github.io/ibis-ml/reference/selectors.html).\n\n\n#### Aggregate features\nFor tables with a depth greater than 0 that can't be directly joined with the base table,\nwe need to aggregate the features by the `case_id`. You could compute the different statistics for numeric columns and\nnon-numeric columns.\n\nHere, we use the `maximum` as an example.","metadata":{}},{"cell_type":"code","source":"def agg_by_id(table):\n    return table.group_by(\"case_id\").agg(\n        [\n            table[col_name].max().name(f\"max_{col_name}\")\n            for col_name in table.columns\n            if col_name[-1] in (\"T\", \"L\", \"P\", \"A\", \"D\", \"M\")\n        ]\n    )","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:09.672944Z","iopub.execute_input":"2024-08-31T02:44:09.673477Z","iopub.status.idle":"2024-08-31T02:44:09.683686Z","shell.execute_reply.started":"2024-08-31T02:44:09.673410Z","shell.execute_reply":"2024-08-31T02:44:09.682382Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\nFor better predicting power, you need to collect different statistics based on the meaning of features. For simplicity,\nwe'll only collect the maximum value of the features here.\n\n\n#### Put them together\nWe'll put them together in a function reads parquet files, optionally handles regex patterns for\nmultiple sub-files, applies data type transformations defined by `data_type_recipes`, and\nperforms aggregation based on `case_id` if specified by the depth parameter.","metadata":{}},{"cell_type":"code","source":"def read_and_process_files(file_path, depth=None, is_regex=False):\n    \"\"\"\n    Read and process Parquet files.\n\n    Args:\n        file_path (str): Path to the file or regex pattern to match files.\n        depth (int, optional): Depth of processing. If 1 or 2, additional aggregation is performed.\n        is_regex (bool, optional): Whether the file_path is a regex pattern.\n\n    Returns:\n        ibis.Table: The processed Ibis table.\n    \"\"\"\n    if is_regex:\n        # read and union multiple files\n        chunks = []\n        for path in glob(str(file_path)):\n            chunk = ibis.read_parquet(path)\n            # transform table using IbisML Recipe\n            chunk = data_type_recipes.fit(chunk).to_ibis(chunk)\n            chunks.append(chunk)\n        table = ibis.union(*chunks)\n    else:\n        # read a single file\n        table = ibis.read_parquet(file_path)\n        # transform table using IbisML\n        table = data_type_recipes.fit(table).to_ibis(table)\n\n    # perform aggregation if depth is 1 or 2\n    if depth in [1, 2]:\n        table = agg_by_id(table)\n\n    return table","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:09.684998Z","iopub.execute_input":"2024-08-31T02:44:09.685394Z","iopub.status.idle":"2024-08-31T02:44:09.696082Z","shell.execute_reply.started":"2024-08-31T02:44:09.685355Z","shell.execute_reply":"2024-08-31T02:44:09.694791Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's define two dictionaries, `train_data_store` and `test_data_store`, that organize and\nstore processed datasets for training and testing datasets.","metadata":{}},{"cell_type":"code","source":"train_data_store = {\n    \"df_base\": read_and_process_files(TRAIN_DIR / \"train_base.parquet\"),\n    \"depth_0\": [\n        read_and_process_files(TRAIN_DIR / \"train_static_cb_0.parquet\"),\n        read_and_process_files(TRAIN_DIR / \"train_static_0_*.parquet\", is_regex=True),\n    ],\n    \"depth_1\": [\n        read_and_process_files(\n            TRAIN_DIR / \"train_applprev_1_*.parquet\", 1, is_regex=True\n        ),\n        read_and_process_files(TRAIN_DIR / \"train_tax_registry_a_1.parquet\", 1),\n        read_and_process_files(TRAIN_DIR / \"train_tax_registry_b_1.parquet\", 1),\n        read_and_process_files(TRAIN_DIR / \"train_tax_registry_c_1.parquet\", 1),\n        read_and_process_files(TRAIN_DIR / \"train_credit_bureau_b_1.parquet\", 1),\n        read_and_process_files(TRAIN_DIR / \"train_other_1.parquet\", 1),\n        read_and_process_files(TRAIN_DIR / \"train_person_1.parquet\", 1),\n        read_and_process_files(TRAIN_DIR / \"train_deposit_1.parquet\", 1),\n        read_and_process_files(TRAIN_DIR / \"train_debitcard_1.parquet\", 1),\n    ],\n    \"depth_2\": [\n        read_and_process_files(TRAIN_DIR / \"train_credit_bureau_b_2.parquet\", 2),\n    ],\n}\n# we won't be submitting the predictions, so let's comment out the test data.\n# test_data_store = {\n#     \"df_base\": read_and_process_files(TEST_DIR / \"test_base.parquet\"),\n#     \"depth_0\": [\n#         read_and_process_files(TEST_DIR / \"test_static_cb_0.parquet\"),\n#         read_and_process_files(TEST_DIR / \"test_static_0_*.parquet\", is_regex=True),\n#     ],\n#     \"depth_1\": [\n#         read_and_process_files(TEST_DIR / \"test_applprev_1_*.parquet\", 1, is_regex=True),\n#         read_and_process_files(TEST_DIR / \"test_tax_registry_a_1.parquet\", 1),\n#         read_and_process_files(TEST_DIR / \"test_tax_registry_b_1.parquet\", 1),\n#         read_and_process_files(TEST_DIR / \"test_tax_registry_c_1.parquet\", 1),\n#         read_and_process_files(TEST_DIR / \"test_credit_bureau_b_1.parquet\", 1),\n#         read_and_process_files(TEST_DIR / \"test_other_1.parquet\", 1),\n#         read_and_process_files(TEST_DIR / \"test_person_1.parquet\", 1),\n#         read_and_process_files(TEST_DIR / \"test_deposit_1.parquet\", 1),\n#         read_and_process_files(TEST_DIR / \"test_debitcard_1.parquet\", 1),\n#     ],\n#     \"depth_2\": [\n#         read_and_process_files(TEST_DIR / \"test_credit_bureau_b_2.parquet\", 2),\n#     ]\n# }","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:09.697841Z","iopub.execute_input":"2024-08-31T02:44:09.698291Z","iopub.status.idle":"2024-08-31T02:44:13.144316Z","shell.execute_reply.started":"2024-08-31T02:44:09.698221Z","shell.execute_reply":"2024-08-31T02:44:13.143186Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Join all features data to base table:","metadata":{}},{"cell_type":"code","source":"def join_data(df_base, depth_0, depth_1, depth_2):\n    for i, df in enumerate(depth_0 + depth_1 + depth_2):\n        df_base = df_base.join(\n            df, \"case_id\", how=\"left\", rname=\"{name}_right\" + f\"_{i}\"\n        )\n    return df_base","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:13.145889Z","iopub.execute_input":"2024-08-31T02:44:13.146318Z","iopub.status.idle":"2024-08-31T02:44:13.152680Z","shell.execute_reply.started":"2024-08-31T02:44:13.146270Z","shell.execute_reply":"2024-08-31T02:44:13.151491Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Generate train and test datasets:","metadata":{}},{"cell_type":"code","source":"df_train = join_data(**train_data_store)\n# df_test = join_data(**test_data_store)\ntotal_rows = df_train.count().execute()\nprint(f\"There is {total_rows} rows and {len(df_train.columns)} columns\")","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:13.153993Z","iopub.execute_input":"2024-08-31T02:44:13.154413Z","iopub.status.idle":"2024-08-31T02:44:18.823598Z","shell.execute_reply.started":"2024-08-31T02:44:13.154373Z","shell.execute_reply":"2024-08-31T02:44:18.822355Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Select features\nGiven the large number of features (~370), we'll focus on selecting just a few of the most\ninformative ones by name for demonstration purposes in this post:","metadata":{}},{"cell_type":"code","source":"df_train = df_train.select(\n    \"case_id\",\n    \"date_decision\",\n    \"target\",\n    # number of credit bureau queries for the last X days.\n    \"days30_165L\",\n    \"days360_512L\",\n    \"days90_310L\",\n    # number of tax deduction payments\n    \"pmtscount_423L\",\n    # sum of tax deductions for the client\n    \"pmtssum_45A\",\n    \"dateofbirth_337D\",\n    \"education_1103M\",\n    \"firstquarter_103L\",\n    \"secondquarter_766L\",\n    \"thirdquarter_1082L\",\n    \"fourthquarter_440L\",\n    \"maritalst_893M\",\n    \"numberofqueries_373L\",\n    \"requesttype_4525192L\",\n    \"responsedate_4527233D\",\n    \"actualdpdtolerance_344P\",\n    \"amtinstpaidbefduel24m_4187115A\",\n    \"annuity_780A\",\n    \"annuitynextmonth_57A\",\n    \"applicationcnt_361L\",\n    \"applications30d_658L\",\n    \"applicationscnt_1086L\",\n    # average days past or before due of payment during the last 24 months.\n    \"avgdbddpdlast24m_3658932P\",\n    # average days past or before due of payment during the last 3 months.\n    \"avgdbddpdlast3m_4187120P\",\n    # end date of active contract.\n    \"max_contractmaturitydate_151D\",\n    # credit limit of an active loan.\n    \"max_credlmt_1052A\",\n    # number of credits in credit bureau\n    \"max_credquantity_1099L\",\n    \"max_dpdmaxdatemonth_804T\",\n    \"max_dpdmaxdateyear_742T\",\n    \"max_maxdebtpduevalodued_3940955A\",\n    \"max_overdueamountmax_950A\",\n    \"max_purposeofcred_722M\",\n    \"max_residualamount_3940956A\",\n    \"max_totalamount_503A\",\n    \"max_cancelreason_3545846M\",\n    \"max_childnum_21L\",\n    \"max_currdebt_94A\",\n    \"max_employedfrom_700D\",\n    # client's main income amount in their previous application\n    \"max_mainoccupationinc_437A\",\n    \"max_profession_152M\",\n    \"max_rejectreason_755M\",\n    \"max_status_219L\",\n    # credit amount of the active contract provided by the credit bureau\n    \"max_amount_1115A\",\n    # amount of unpaid debt for existing contracts\n    \"max_debtpastduevalue_732A\",\n    \"max_debtvalue_227A\",\n    \"max_installmentamount_833A\",\n    \"max_instlamount_892A\",\n    \"max_numberofinstls_810L\",\n    \"max_pmtnumpending_403L\",\n    \"max_last180dayaveragebalance_704A\",\n    \"max_last30dayturnover_651A\",\n    \"max_openingdate_857D\",\n    \"max_amount_416A\",\n    \"max_amtdebitincoming_4809443A\",\n    \"max_amtdebitoutgoing_4809440A\",\n    \"max_amtdepositbalance_4809441A\",\n    \"max_amtdepositincoming_4809444A\",\n    \"max_amtdepositoutgoing_4809442A\",\n    \"max_empl_industry_691L\",\n    \"max_gender_992L\",\n    \"max_housingtype_772L\",\n    \"max_mainoccupationinc_384A\",\n    \"max_incometype_1044T\",\n)\n\ndf_train.head()","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:18.825170Z","iopub.execute_input":"2024-08-31T02:44:18.825642Z","iopub.status.idle":"2024-08-31T02:44:24.291873Z","shell.execute_reply.started":"2024-08-31T02:44:18.825588Z","shell.execute_reply":"2024-08-31T02:44:24.290691Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Univariate analysis:","metadata":{}},{"cell_type":"code","source":"# take the first 10 columns\ndf_train[df_train.columns[:10]].describe()","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:24.293194Z","iopub.execute_input":"2024-08-31T02:44:24.293577Z","iopub.status.idle":"2024-08-31T02:44:41.745528Z","shell.execute_reply.started":"2024-08-31T02:44:24.293538Z","shell.execute_reply":"2024-08-31T02:44:41.744408Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Last-mile data preprocessing\nWe will perform the following transformation before feeding the data to models:\n\n* Missing value imputation\n* Encoding categorical variables\n* Handling date variables\n* Handling outliers\n* Scaling and normalization\n\n\nIbisML provides a set of transformations. You can find the\n[roadmap](https://github.com/ibis-project/ibis-ml/issues/32).\nThe [IbisML website](https://ibis-project.github.io/ibis-ml/) also includes tutorials and API documentation.\n\n### Impute features\nImpute all numeric columns using the median. In real-life scenarios, it's important to\nunderstand the meaning of each feature and apply the appropriate imputation method for\ndifferent features. For more imputations, please refer to this\n[documentation](https://ibis-project.github.io/ibis-ml/reference/steps-imputation.html).","metadata":{}},{"cell_type":"code","source":"step_impute_median = ml.ImputeMedian(ml.numeric())","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:41.747077Z","iopub.execute_input":"2024-08-31T02:44:41.747477Z","iopub.status.idle":"2024-08-31T02:44:41.753643Z","shell.execute_reply.started":"2024-08-31T02:44:41.747436Z","shell.execute_reply":"2024-08-31T02:44:41.752353Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Encode categorical features\nEncode all categorical features using one-hot-encode. For more encoding steps,\nplease refer to this\n[doc](https://ibis-project.github.io/ibis-ml/reference/steps-encoding.html).","metadata":{}},{"cell_type":"code","source":"ohe_step = ml.OneHotEncode(\n    [\n        \"maritalst_893M\",\n        \"requesttype_4525192L\",\n        \"max_profession_152M\",\n        \"max_gender_992L\",\n        \"max_empl_industry_691L\",\n        \"max_housingtype_772L\",\n        \"max_incometype_1044T\",\n        \"max_cancelreason_3545846M\",\n        \"max_rejectreason_755M\",\n        \"education_1103M\",\n        \"max_status_219L\",\n    ]\n)","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:41.755344Z","iopub.execute_input":"2024-08-31T02:44:41.755716Z","iopub.status.idle":"2024-08-31T02:44:41.764733Z","shell.execute_reply.started":"2024-08-31T02:44:41.755675Z","shell.execute_reply":"2024-08-31T02:44:41.763456Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Handle date variables\nCalculate all the days difference between any date columns and the column `date_decision`:","metadata":{}},{"cell_type":"code","source":"date_cols = [col_name for col_name in df_train.columns if col_name[-1] == \"D\"]\ndays_to_decision_expr = {\n    # difference in days\n    f\"{col}_date_decision_diff\": (\n        _.date_decision.epoch_seconds() - getattr(_, col).epoch_seconds()\n    )\n    / (60 * 60 * 24)\n    for col in date_cols\n}\ndays_to_decision_step = ml.Mutate(days_to_decision_expr)","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:41.766335Z","iopub.execute_input":"2024-08-31T02:44:41.766796Z","iopub.status.idle":"2024-08-31T02:44:41.779350Z","shell.execute_reply.started":"2024-08-31T02:44:41.766744Z","shell.execute_reply":"2024-08-31T02:44:41.778179Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Extract information from the date columns:","metadata":{}},{"cell_type":"code","source":"# dow and month is set to catagoery\nexpand_date_step = ml.ExpandDate(ml.date(), [\"week\", \"day\"])","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:41.780791Z","iopub.execute_input":"2024-08-31T02:44:41.781418Z","iopub.status.idle":"2024-08-31T02:44:41.790977Z","shell.execute_reply.started":"2024-08-31T02:44:41.781363Z","shell.execute_reply":"2024-08-31T02:44:41.789834Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Handle outliers\nCapping outliers using `z-score` method:","metadata":{}},{"cell_type":"code","source":"step_handle_outliers = ml.HandleUnivariateOutliers(\n    [\"max_amount_1115A\", \"max_overdueamountmax_950A\"],\n    method=\"z-score\",\n    treatment=\"capping\",\n    deviation_factor=3,\n)","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:41.792503Z","iopub.execute_input":"2024-08-31T02:44:41.793014Z","iopub.status.idle":"2024-08-31T02:44:41.803190Z","shell.execute_reply.started":"2024-08-31T02:44:41.792955Z","shell.execute_reply":"2024-08-31T02:44:41.802135Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Construct recipe\nWe'll construct the last mile preprocessing [recipe](https://ibis-project.github.io/ibis-ml/reference/core.html#ibis_ml.Recipe)\nby chaining all transformation steps, which will be fitted to the training dataset and later applied test datasets.","metadata":{}},{"cell_type":"code","source":"last_mile_preprocessing = ml.Recipe(\n    expand_date_step,\n    ml.Drop(ml.date()),\n    # handle string columns\n    ohe_step,\n    ml.Drop(ml.string()),\n    # handle numeric cols\n    # capping outliers\n    step_handle_outliers,\n    step_impute_median,\n    ml.ScaleMinMax(ml.numeric()),\n    # fill missing value\n    ml.FillNA(ml.numeric(), 0),\n    ml.Cast(ml.numeric(), \"float32\"),\n)\nprint(f\"Last-mile preprocessing recipe: \\n{last_mile_preprocessing}\")","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:41.804618Z","iopub.execute_input":"2024-08-31T02:44:41.804974Z","iopub.status.idle":"2024-08-31T02:44:41.818758Z","shell.execute_reply.started":"2024-08-31T02:44:41.804931Z","shell.execute_reply":"2024-08-31T02:44:41.817366Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Modeling\nAfter completing data preprocessing with Ibis and IbisML, we proceed to the modeling\nphase. Here are two approaches:\n\n* Use IbisML as a independent data preprocessing component and hand off the data to downstream modeling\nframeworks with various output formats:\n     - pandas Dataframe\n     - NumPy Array\n     - Polars Dataframe\n     - Dask Dataframe\n     - xgboost.DMatrix\n     - Pyarrow Table\n* Use IbisML recipes as components within an sklearn Pipeline and\ntrain models similarly to how you would do with sklearn pipeline.\n\nWe will build an XGBoost model within a scikit-learn pipeline, and a neural network classifier using the\noutput transformed by IbisML recipes.\n\n### Train and test data splitting\nWe'll use hashing on the unique key to consistently split rows to different groups.\nHashing is robust to underlying changes in the data, such as adding, deleting, or\nreordering rows. This deterministic process ensures that each data point is always\nassigned to the same split, thereby enhancing reproducibility.","metadata":{}},{"cell_type":"code","source":"import random\n\n# this enables the analysis to be reproducible when random numbers are used\nrandom.seed(222)\nrandom_key = str(random.getrandbits(256))\n\n# put 3/4 of the data into the training set\ndf_train = df_train.mutate(\n    train_flag=(df_train.case_id.cast(dt.str) + random_key).hash().abs() % 4 < 3\n)\n# split the dataset by train_flag\n# todo: use ml.train_test_split() after next release\ntrain_data = df_train[df_train.train_flag].drop(\"train_flag\")\ntest_data = df_train[~df_train.train_flag].drop(\"train_flag\")\n\nX_train = train_data.drop(\"target\")\ny_train = train_data.target.cast(dt.float32).name(\"target\")\n\nX_test = test_data.drop(\"target\")\ny_test = test_data.target.cast(dt.float32).name(\"target\")\n\ntrain_cnt = X_train.count().execute()\ntest_cnt = X_test.count().execute()\nprint(f\"train dataset size = {train_cnt} \\ntest data size = {test_cnt}\")","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:41.820333Z","iopub.execute_input":"2024-08-31T02:44:41.820775Z","iopub.status.idle":"2024-08-31T02:44:50.493434Z","shell.execute_reply.started":"2024-08-31T02:44:41.820731Z","shell.execute_reply":"2024-08-31T02:44:50.492158Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\nHashing provides a consistent but pseudo-random distribution of data, which\nmay not precisely align with the specified train/test ratio. While hash codes\nensure reproducibility, they don't guarantee an exact split. Due to statistical variance,\nyou might find a slight imbalance in the distribution, resulting in marginally more or\nfewer samples in either the training or test dataset than the target percentage. This\nminor deviation from the intended ratio is a normal consequence of hash-based\npartitioning.\n\n\n### XGBoost\nIn this section, we integrate XGBoost into a scikit-learn pipeline to create a\nstreamlined workflow for training and evaluating our model.\n\nWe'll set up a pipeline that includes two components:\n\n* **Preprocessing**: This step applies the `last_mile_preprocessing` for final data preprocessing.\n* **Modeling**: This step applies the `xgb.XGBClassifier()` to train the XGBoost model.","metadata":{}},{"cell_type":"code","source":"# For XGBoost and scikit-learn-based models:\n!pip install xgboost[scikit-learn]","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:44:50.494795Z","iopub.execute_input":"2024-08-31T02:44:50.495180Z","iopub.status.idle":"2024-08-31T02:45:06.119154Z","shell.execute_reply.started":"2024-08-31T02:44:50.495141Z","shell.execute_reply":"2024-08-31T02:45:06.117904Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.pipeline import Pipeline\nfrom sklearn.metrics import roc_auc_score\nimport xgboost as xgb\n\nmodel = xgb.XGBClassifier(\n    n_estimators=100,\n    max_depth=5,\n    learning_rate=0.05,\n    subsample=0.8,\n    colsample_bytree=0.8,\n    random_state=42,\n)\n# create the pipeline with the last mile ML recipes and the model\npipe = Pipeline([(\"last_mile_recipes\", last_mile_preprocessing), (\"model\", model)])\n# fit the pipeline on the training data\npipe.fit(X_train, y_train)","metadata":{"execution":{"iopub.status.busy":"2024-08-31T02:45:06.121066Z","iopub.execute_input":"2024-08-31T02:45:06.121582Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's evaluate the model on the test data using Gini index:","metadata":{}},{"cell_type":"code","source":"y_pred_proba = pipe.predict_proba(X_test)[:, 1]\n# calculate the AUC score\nauc = roc_auc_score(y_test, y_pred_proba)\n\n# calculate the Gini score\ngini_score = 2 * auc - 1\nprint(f\"gini_score for test dataset: {gini_score:,}\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\nThe competition is evaluated using a Gini stability metric. For more information, see the\n[evaluation guidelines](https://www.kaggle.com/competitions/home-credit-credit-risk-model-stability/overview/evaluation)\n\n\n### Neural network classifier\nBuild a neural network classifier using PyTorch and PyTorch Lightning.\n\nIt is not recommended to build a neural network classifier for this competition, we are building\nit solely for demonstration purposes.\n\n\nWe'll demonstrate how to build a model by directly passing the data to it. IbisML recipes can output\ndata in various formats, making it compatible with different modeling frameworks.\nLet's first train the recipe:","metadata":{}},{"cell_type":"code","source":"# train preprocessing recipe using training dataset\nlast_mile_preprocessing.fit(X_train, y_train)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In the previous cell, we trained the recipe using the training dataset. Now, we will\ntransform both the train and test datasets using the same recipe. The default output format is a `NumPy array`","metadata":{}},{"cell_type":"code","source":"# transform train and test dataset using IbisML recipe\nX_train_transformed = last_mile_preprocessing.transform(X_train)\nX_test_transformed = last_mile_preprocessing.transform(X_test)\nprint(f\"train data shape = {X_train_transformed.shape}\")\nprint(f\"test data shape = {X_test_transformed.shape}\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's define a neural network classifier using PyTorch and PyTorch Lighting:","metadata":{}},{"cell_type":"code","source":"# For PyTorch-based models:\n\n!pip install torch pytorch-lightning","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import numpy as np\nimport torch\nimport torch.nn as nn\nimport torch.optim as optim\nfrom torch.utils.data import DataLoader, TensorDataset\nimport pytorch_lightning as pl\nfrom pytorch_lightning import Trainer\n\n\nclass NeuralNetClassifier(pl.LightningModule):\n    def __init__(self, input_dim, hidden_dim=8, output_dim=1):\n        super().__init__()\n        self.model = nn.Sequential(\n            nn.Linear(input_dim, hidden_dim),\n            nn.ReLU(),\n            nn.Linear(hidden_dim, output_dim),\n        )\n        self.loss = nn.BCEWithLogitsLoss()\n        self.sigmoid = nn.Sigmoid()\n\n    def forward(self, x):\n        return self.model(x)\n\n    def training_step(self, batch, batch_idx):\n        x, y = batch\n        y_hat = self(x)\n        loss = self.loss(y_hat.view(-1), y)\n        self.log(\"train_loss\", loss)\n        return loss\n\n    def validation_step(self, batch, batch_idx):\n        x, y = batch\n        y_hat = self(x)\n        loss = self.loss(y_hat.view(-1), y)\n        self.log(\"val_loss\", loss)\n        return loss\n\n    def configure_optimizers(self):\n        return optim.Adam(self.parameters(), lr=0.001)\n\n    def predict_proba(self, x):\n        self.eval()\n        with torch.no_grad():\n            x = x.to(self.device)\n            return self.sigmoid(self(x))\n\n# initialize your Lightning Module\nnn_classifier = NeuralNetClassifier(input_dim=X_train_transformed.shape[1])","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, we'll create the PyTorch DataLoader using the output from IbisML:","metadata":{}},{"cell_type":"code","source":"y_train_array = y_train.to_pandas().to_numpy().astype(np.float32)\nx_train_tensor = torch.from_numpy(X_train_transformed)\ny_train_tensor = torch.from_numpy(y_train_array)\ntrain_dataset = TensorDataset(x_train_tensor, y_train_tensor)\n\ny_test_array = y_test.to_pandas().to_numpy().astype(np.float32)\nX_test_tensor = torch.from_numpy(X_test_transformed)\ny_test_tensor = torch.from_numpy(y_test_array)\nval_dataset = TensorDataset(X_test_tensor, y_test_tensor)\n\ntrain_loader = DataLoader(train_dataset, batch_size=32, shuffle=False)\nval_loader = DataLoader(val_dataset, batch_size=32, shuffle=False)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Initialize the PyTorch Lightning Trainer:","metadata":{}},{"cell_type":"code","source":"# initialize a Trainer\ntrainer = Trainer(max_epochs=2)\nprint(nn_classifier)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's train the classifier:","metadata":{}},{"cell_type":"code","source":"# train the model\ntrainer.fit(nn_classifier, train_loader, val_loader)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's use the trained model to make a prediction:","metadata":{}},{"cell_type":"code","source":"y_pred = nn_classifier.predict_proba(X_test_tensor[:10])\ny_pred","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Takeaways\nIbisML provides a powerful suite of last-mile preprocessing transformations, including an advanced column selector\nthat streamlines the selection and transformation of specific columns in your dataset.\n\nIt integrates seamlessly with scikit-learn pipelines, allowing you to incorporate preprocessing recipes directly into\nyour workflow. Additionally, IbisML supports a variety of data output formats such as Dask, NumPy, and Arrow, ensuring\ncompatibility with different machine learning frameworks.\n\nAnother key advantage of IbisML is its flexibility in performing data preprocessing across multiple backends, including\nDuckDB, Polars, Spark, BigQuery, and other Ibis backends. This enables you to preprocess your training data\nusing the backend that best suits your needs, whether for large or small datasets, on local machines or compute backends,\nand in both development and production environments. Stay tuned for a future post where we will explore this capability in\nmore detail.\n\n## Reference\n* [1st Place Solution](https://www.kaggle.com/code/yuuniekiri/fork-of-home-credit-risk-lightgbm)\n* [home-credit-2024-starter-notebook](https://www.kaggle.com/code/jetakow/home-credit-2024-starter-notebook)\n* [EDA and Submission](https://www.kaggle.com/competitions/home-credit-credit-risk-model-stability/discussion/508337)\n* [Home Credit Baseline](https://www.kaggle.com/code/greysky/home-credit-baseline)","metadata":{}}]}