exercises/051-RDT-Preprocessing-MissingValues.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "96d9ff26",
   "metadata": {},
   "source": [
    "## Check the setup and connect to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f2a4e34",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run \"010-check_setup.ipynb\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "5566fc86-761d-419d-92ec-26800979c287",
   "metadata": {},
   "source": [
    "# Tables from SAP HANA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1b3cd7c-330d-43d6-a8d7-bcd87d903817",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_train=myconn.table('DATA_LABELED', schema='TITANIC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc6fbe37",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_train.describe().filter(condition='\"nulls\"<>0').collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bed84d4d-b9d2-4379-885a-326330efa422",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_test=myconn.table('DATA_TO_PREDICT', schema='TITANIC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db14c7f4-d8d4-45d5-9171-07e617f6ae5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_test.describe().filter(condition='\"nulls\"<>0').collect()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "fcf9d836-dcc2-482c-ac42-71631abe87cc",
   "metadata": {},
   "source": [
    "🤓 **Let's discuss**:\n",
    "- Missing values"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "ad276026",
   "metadata": {},
   "source": [
    "## Imputation in the DATA_LABELED table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfd6d41a",
   "metadata": {},
   "source": [
    "Imputation: https://help.sap.com/doc/cd94b08fe2e041c2ba778374572ddba9/2024_1_QRC/en-US/pal/algorithms/hana_ml.algorithms.pal.preprocessing.Imputer.html#imputer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74f41e70",
   "metadata": {},
   "outputs": [],
   "source": [
    "from hana_ml.algorithms.pal.preprocessing import Imputer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f0bead8",
   "metadata": {},
   "outputs": [],
   "source": [
    "impute = Imputer(strategy='most_frequent-median')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7d0ad9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_train_imputed = impute.fit_transform(hdf_titanic_train,\n",
    "                                                    categorical_variable=['PClass'],\n",
    "                                                    strategy_by_col=[('Cabin', 'non')]\n",
    "                                                )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8303518",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_train_imputed.describe().collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18c4e38d",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_train_imputed.select_statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bf31fb3-b34f-4bc4-8b94-4b88fa58ffef",
   "metadata": {},
   "source": [
    "You want to persist this table for future experiments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c9b9b2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_train_imputed=hdf_titanic_train_imputed.save(where='DATA_LABELED_IMPUTED', force=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "96bbfc41-6881-4397-94fa-bc79761dbe07",
   "metadata": {},
   "source": [
    "# Random Decision Tree classification\n",
    "\n",
    "Experiement 4: Exclude high cardinality features (names, tickets and cabins) to improve generalization of the model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddfd6ad8",
   "metadata": {},
   "outputs": [],
   "source": [
    "from hana_ml.algorithms.pal.unified_classification import UnifiedClassification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e13db34",
   "metadata": {},
   "outputs": [],
   "source": [
    "uc_rdt_v4 = UnifiedClassification(func='RandomDecisionTree')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57a3a0fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "features_low_cardinality=['Age', 'SibSp', 'ParCh', 'PClass', 'Fare', 'Gender', 'Embarked']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2e0cbb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "uc_rdt_v4.fit(\n",
    "    data=hdf_titanic_train_imputed,\n",
    "    key='PassengerId', label='Survived',\n",
    "    features=features_low_cardinality,\n",
    "    training_percent=0.8,\n",
    "    partition_method='stratified', stratified_column='Survived', \n",
    "    partition_random_state=2\n",
    ");"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c7c02e93-0356-4bdf-8fd8-62da8f3379e0",
   "metadata": {},
   "source": [
    "## Generate a model report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46f58687-a988-4c62-9775-6d24619af87a",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "from hana_ml.visualizers.unified_report import UnifiedReport\n",
    "UnifiedReport(uc_rdt_v4).build().display()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "773f04f8",
   "metadata": {},
   "source": [
    "## Debrief the model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "beaa3177",
   "metadata": {},
   "outputs": [],
   "source": [
    "from hana_ml.visualizers.model_debriefing import TreeModelDebriefing"
   ]
  },
  {
   "cell_type": "raw",
   "id": "95a91712",
   "metadata": {
    "vscode": {
     "languageId": "raw"
    }
   },
   "source": [
    "TreeModelDebriefing.tree_debrief_with_dot(uc_rdt_v4.model_[0], iframe_height=700);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c242b904-cf7e-403e-85b7-44815d102277",
   "metadata": {},
   "source": [
    "# Store the model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6af8ec3d-4e8f-481e-999b-89f26683eea7",
   "metadata": {},
   "outputs": [],
   "source": [
    "from hana_ml.model_storage import ModelStorage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70b206b8-fcd4-4522-84f0-e639a96c567a",
   "metadata": {},
   "source": [
    "Model storage: https://help.sap.com/doc/cd94b08fe2e041c2ba778374572ddba9/latest/en-US/hana_ml.model_storage.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8af7c440-72d0-46b1-9806-10e619421944",
   "metadata": {},
   "outputs": [],
   "source": [
    "ms = ModelStorage(myconn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85ce527f-bfb2-4fd2-8da0-5258e40edf91",
   "metadata": {},
   "outputs": [],
   "source": [
    "uc_rdt_v4.name = 'CodeJam-Titanic-Classification'\n",
    "uc_rdt_v4.version = 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4b7cc82-ac1c-46b9-8f67-29213881269b",
   "metadata": {},
   "outputs": [],
   "source": [
    "ms.save_model(model=uc_rdt_v4, if_exists='replace', save_report=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7967eb91-412d-421c-9de7-aa50c66d694c",
   "metadata": {},
   "outputs": [],
   "source": [
    "ms.list_models()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ccd0c4d",
   "metadata": {},
   "source": [
    "# Imputation in the DATA_TO_PREDICT table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f068c498",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_test=myconn.table('DATA_TO_PREDICT', schema='TITANIC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a7b27b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_test.describe().filter(condition='\"nulls\"<>0').collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6bdf103d",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_test_imputed = impute.fit_transform(hdf_titanic_test,\n",
    "                                                    categorical_variable=['PClass'],\n",
    "                                                    strategy_by_col=[('Cabin', 'non')]\n",
    "                                                )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d87889d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_test_imputed.describe().collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca0b3d03",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_test_imputed.select_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "304c8f4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_test_imputed=hdf_titanic_test_imputed.save(where='DATA_TO_PREDICT_IMPUTED', force=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0080f3b9-6b69-4032-b479-ee05ff8628ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import HTML\n",
    "HTML(myconn.get_tables().sort_values(by='TABLE_NAME').to_html())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "e7a8d4b8-12d4-442b-bce4-3648aae1a818",
   "metadata": {},
   "source": [
    "🤓 **Let's discuss**:\n",
    "- Tables seen in your schema"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fecab31a",
   "metadata": {},
   "source": [
    "# Call prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9a7678c-5787-453d-92ca-14f90568b03b",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_res_v4 = uc_rdt_v4.predict(hdf_titanic_test_imputed, key = 'PassengerId')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56ae4a2c-c723-409a-b08f-58327b2e4328",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(hdf_res_v4.head(3).collect())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}