UseCases/Banking_Customer_Churn/Banking_Customer_Churn_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6a7e847b-0851-4b91-9445-4298034e79cb",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Banking Customer Churn Analysis using Vantage\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77ef03c3-8327-4170-a46d-711debf3bf7d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "\n",
    "<center><img src=\"images/churn.webp\"/></center>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Source: <a href = 'https://medium.com/@islamhasabo/predicting-customer-churn-bc76f7760377'>Medium</a></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customer churn is a critical metric in banking because it can directly impact a bank's revenue and profitability. When customers leave, banks lose the income they would have earned from those customers' transactions, investments, and account fees. Additionally, attracting new customers to replace those who have left can be expensive and time-consuming, so reducing customer churn is often more cost-effective than acquiring new customers.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customer churn can also be an indicator of customer satisfaction and loyalty. If customers leave at a high rate, they may be dissatisfied with the bank's products or services, customer service, or overall experience.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Banks can use various strategies to reduce customer churns, such as improving customer service, offering more competitive rates and fees, providing personalized recommendations and offers, and enhancing digital channels and mobile apps. By tracking and analyzing customer churn rates, banks can identify areas for improvement and make strategic decisions to retain customers and improve overall customer satisfaction.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo, we demonstrate how to implement the entire lifecycle of churn prediction can using Vantage technologies and, specifically, the combination of Bring Your Own Model (BYOM), Vantage Analytics Library (VAL) and teradataml python client library solution.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bb321ff-1d77-470f-9d29-ef3133e33e54",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Downloading and installing additional software needed</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18032022-e3ca-470f-9a12-87cf2328ef5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "# # '%%capture' suppresses the display of installation steps of the following packages\n",
    "# !pip install xgboost==1.7.3\n",
    "# !pip install jdk4py==17.0.3.0\n",
    "# !pip install sklearn2pmml==0.90.3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2996424-64cf-4a32-9609-3e8b2eef9f01",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>The above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If you uncomment those installs, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "060b1f7b-0233-402b-8d7d-d97adbf2bbdd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "from sklearn import tree\n",
    "from xgboost import XGBClassifier\n",
    "from sklearn2pmml import sklearn2pmml\n",
    "from sklearn2pmml.pipeline import PMMLPipeline\n",
    "from jdk4py import JAVA, JAVA_HOME, JAVA_VERSION\n",
    "\n",
    "from teradataml import *\n",
    "\n",
    "# Modify the following to match the specific client environment settings\n",
    "display.max_rows = 5\n",
    "configure.val_install_location = 'val'\n",
    "configure.byom_install_location = 'mldb'\n",
    "os.environ['PATH'] = os.environ['PATH'] + os.pathsep + str(JAVA_HOME)\n",
    "os.environ['PATH'] = os.environ['PATH'] + os.pathsep + str(JAVA)[:-5]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3bd616c-e525-485c-b62e-44fa9ed0c5de",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>1. Initiate a connection to Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56b1c03c-5b79-4209-8a08-164eda11d823",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ce6ae13-a2d0-484e-8b77-52b49b61964a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=BankingChurn.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "284bbd38-09f8-4aa1-b965-863b25356340",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae9262e3-3e73-4aec-b935-e2ba26483aca",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. You can either run the demo using foreign tables to access the data without any storage on your environment or download the data to local storage, which may yield faster execution. Still, there could be considerations of available storage. Two statements are in the following cell, and one is commented out. You may switch which mode you choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16973665-4402-466e-bd51-8b3e279bb866",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_BankChurn_cloud');\"        # Takes 30 seconds\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_BankChurn_local');\"        # Takes 1 minute"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b3a891f-0f8d-4d7b-88d1-c6be7e12c41e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85e6f604-ef55-4503-88ac-7dd584fbd601",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c19267c0-c983-4a9d-9351-b20d6d203292",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>2. Data Exploration</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create a \"Virtual DataFrame\" that points to the data set in Vantage. Check the shape of the dataframe as check the datatype of all the columns of the dataframe.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b><i>*Please scroll down to the end of the notebook for detailed column descriptions of the dataset.</i></b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93ca4743-c0ca-4ebf-8122-9d68307c0b72",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema(\"DEMO_BankChurn\", \"customer_churn\"))\n",
    "print(\"Shape of the data: \", tdf.shape)\n",
    "tdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4c16253-58de-4018-a845-c2a92ebb8495",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f244b52d-71da-48c0-a58a-3adeb5943d69",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>By looking at the datatypes and sample data, we classify the columns into ID column, target variable(y), numerical, categorical and binary. We skip using <i>RowNumber</i> and <i>Surname</i> columns as they are not helpful in the analysis.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d2796a4-8b6d-4abc-9eb4-2278a710dc2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "target_variable = \"Exited\"\n",
    "numeric_columns = [\"Age\", \"Balance\", \"CreditScore\", \"EstimatedSalary\", \"Tenure\"]\n",
    "categorical_columns = [\"Gender\", \"Geography\", \"NumOfProducts\"]\n",
    "binary_columns = [\"HasCrCard\", \"IsActiveMember\"]\n",
    "id_column = [\"CustomerId\"]\n",
    "\n",
    "customer_data = tdf.select(\n",
    "    id_column + [target_variable] + numeric_columns + categorical_columns + binary_columns\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91144ef8-fe0f-40a7-841e-d3e2665a472c",
   "metadata": {},
   "outputs": [],
   "source": [
    "customer_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ec05050-5703-4015-b82f-32ebedc2658d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>3. Data Transformation</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8de5a4f4-7697-4f13-b95b-5255a5043d2b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Transformation of string variables into flags (OneHotEncoding)</b></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the following string or category variables as an example:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Gender</li>\n",
    "    <li>Geography</li>\n",
    "    <li>NumOfProducts</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>And for each of them we are going to use the <i>OneHotEncoder</i> function to generate the set of marks.</p>\n",
    "  \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b> The process can be achieved using a single script, here it has been separated into steps for didactic purposes.</i>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1eec9348-8306-49bf-88ae-681b2c848dfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 0-male, 1-female\n",
    "gender_values = {\"Female\": \"Gender\"}\n",
    "gender_encoder = OneHotEncoder(values=gender_values, columns=\"Gender\")\n",
    "\n",
    "# 0-france, 1-germany, 2-spain\n",
    "geography_values = {\"France\": \"France\", \"Germany\": \"Germany\", \"Spain\": \"Spain\"}\n",
    "geography_encoder = OneHotEncoder(values=geography_values, columns=\"Geography\")\n",
    "\n",
    "num_of_products_values = {1: \"OneProduct\", 2: \"TwoProduct\", 3: \"ThreeProduct\", 4: \"FourProduct\"}\n",
    "num_of_products_encoder = OneHotEncoder(values=num_of_products_values, columns=\"NumOfProducts\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d293989-a8ba-4639-8e14-92ff6eb26889",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Standardize for numeric variables (Z-score)</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the following numerical variables as an example:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Age</li>\n",
    "    <li>Balance</li>\n",
    "    <li>CreditScore</li>\n",
    "    <li>EstimatedSalary</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>And for each of them we are going to use the <i>ZScore</i> function to generate the transformation.</p>\n",
    "  \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b> The process can be achieved using a single script, here it has been separated into steps for didactic purposes.</i>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af82a2ee-93c8-440c-becb-e7ecb91d5b46",
   "metadata": {},
   "outputs": [],
   "source": [
    "# FillNa allows user to perform missing value/null replacement transformations.\n",
    "fn = FillNa(style = \"mode\", columns = numeric_columns)\n",
    "\n",
    "# Z-Score transforms each column value into the number of standard deviations from the mean value of the column.\n",
    "# This is non-linear transformation\n",
    "zs = ZScore(columns = numeric_columns,\n",
    "            out_columns = numeric_columns,\n",
    "            fillna = fn)\n",
    "\n",
    "# Keep the other variables that do not not need transformation.\n",
    "retain = Retain(columns=binary_columns+[target_variable])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe7b81c0-f2df-47fd-b299-8251c8e34b28",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process the transformation\n",
    "df_transformed = valib.Transform(\n",
    "    data = customer_data,\n",
    "    zscore = zs,\n",
    "    one_hot_encode = [gender_encoder, geography_encoder, num_of_products_encoder],\n",
    "    retain = retain,\n",
    "    index_columns = id_column,\n",
    "    key_columns = id_column\n",
    ").result\n",
    "\n",
    "df_transformed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac217ce3-ab35-4fb6-a50d-f64b7e458c19",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Train/Test Split</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Split the dataset into train and test datasets according to the split ratio, here 0.8</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62a50697-a8f4-433c-82bb-2e44adea857c",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_ratio = 0.8\n",
    "\n",
    "df_sample = df_transformed.sample(frac = [train_ratio, 1.0-train_ratio])\n",
    "\n",
    "# Split into 2 virtual dataframes\n",
    "tdf_train = df_sample[df_sample.sampleid==1].drop([\"sampleid\"], axis=1)\n",
    "tdf_test = df_sample[df_sample.sampleid==2].drop([\"sampleid\"], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "451fb97d-dc76-4c24-a7f4-81a2749e5efc",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>4. Model Training (Outside Vantage)</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4699659a-a84e-4408-a745-5193fae34c00",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Read the training data</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we use <b>to_pandas()</b> function to get the data outside Vantage and simulate an environment where the models are trained outside Vantage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e54ff36-fa11-47ee-916d-9861556be4bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the training table with feature\n",
    "df_train = tdf_train.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6cf1dcf8-b658-4af2-9324-e86f230f0331",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup y and Xs columns\n",
    "y_col = ['Exited']\n",
    "x_cols = df_train.columns.to_list()\n",
    "x_cols.remove('Exited')\n",
    "\n",
    "X = df_train[x_cols]\n",
    "y = df_train[y_col]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed772834-97c4-484a-975b-5ce12c19de2a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Train a Decision Tree Model</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Train a basic Decision Tree model and save it in PMML format.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "453ca926-d928-4128-803c-5468b014908f",
   "metadata": {},
   "outputs": [],
   "source": [
    "pipeline = PMMLPipeline([\n",
    "    (\"classifier\", tree.DecisionTreeClassifier())\n",
    "])\n",
    "pipeline.fit(X, y.values.ravel())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c8366eb-ada4-4711-bab4-f759cf06429f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the model in PMML format\n",
    "sklearn2pmml(pipeline, \"bankchurn_dt_model.pmml\", with_repr = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc63bac3-fd13-40ae-ab66-77dad5fcb405",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Train a XGBoost Model</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Train a basic XGBoost model and save it in PMML format.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0fb598c-40f8-43b3-9832-704b7241324e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Train the XGBoost model\n",
    "pipeline = PMMLPipeline([\n",
    "     (\"classifier\", XGBClassifier())\n",
    "])\n",
    "\n",
    "pipeline.fit(X, y.values.ravel())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc40a1b5-b1e0-4647-be55-b09d281d4760",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the model in PMML format\n",
    "sklearn2pmml(pipeline, \"bankchurn_xgb_model.pmml\", with_repr = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b195c53-c7c9-425d-9b47-d11a8d216849",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we load both the PMML files/models into a table in Vantage. This table will help to execute in-database scoring in the next section.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2828f4ad-19f5-4ab9-8cae-34e89b1d8a04",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Load the PMML file into Vantage\n",
    "\n",
    "model_ids = ['dt', 'xgb']\n",
    "model_files = ['bankchurn_dt_model.pmml', 'bankchurn_xgb_model.pmml']\n",
    "table_name = 'bank_models'\n",
    "\n",
    "for model_id, model_file in zip(model_ids, model_files):\n",
    "    try:\n",
    "        save_byom(model_id = model_id, model_file = model_file, table_name = table_name)\n",
    "    except Exception as e:\n",
    "        # if our model exists, delete and rewrite\n",
    "        if str(e.args).find('TDML_2200') >= 1:\n",
    "            delete_byom(model_id = model_id, table_name = table_name)\n",
    "            save_byom(model_id = model_id, model_file = model_file, table_name = table_name)\n",
    "        else:\n",
    "            raise ValueError(f\"Unable to save the model '{model_id}' in '{table_name}' due to the following error: {e}\")\n",
    "\n",
    "# Show the bank_models table\n",
    "list_byom(table_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6ace3b6-2d08-4d59-bc93-8302161850a7",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>5. Model Scoring (Inside Vantage)</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d385274-46e3-4760-8eb1-c3a9702e7f4b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Scoring Decision Tree Model</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We are scoring the Decision Tree model, stored in the bank_models table in PMML format using Vantage's PMMLPredict functionality. All the scoring is done in-database inside Vantage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98c8802b-0876-42f7-a8ec-cebd20101c40",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Obtain a pointer to the model\n",
    "table_name = 'bank_models'\n",
    "model_id = 'dt'\n",
    "model_tdf = retrieve_byom(model_id, table_name=table_name) \n",
    "\n",
    "result_dt = PMMLPredict(\n",
    "    modeldata = model_tdf,\n",
    "    newdata = tdf_test,\n",
    "    accumulate = ['CustomerId', 'Exited'],\n",
    "    model_output_fields = ['probability(1)', 'probability(0)']\n",
    ")\n",
    "\n",
    "result_dt.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66cc9d12-cca1-4b53-ac4e-85e842ca4df5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b> If the scoring performed with a classification model does not return a predicted value, the prediction output column could be empty. If the scoring is performed on regression or models which result in a single field, the prediction column will contain a value.</i>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64a6aa3e-a9f7-4929-8272-b9196df706a4",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Scoring XGBoost Model</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We are scoring the XGBoost model stored in the bank_models table in PMML format using Vantage's PMMLPredict functionality. All the scoring is done in-database inside Vantage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c3c8bec-4fa0-48f5-ac58-344411cd68d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Obtain a pointer to the model\n",
    "table_name = 'bank_models'\n",
    "model_id = 'xgb'\n",
    "model_tdf = retrieve_byom(model_id, table_name=table_name)\n",
    "\n",
    "result_xgb = PMMLPredict(\n",
    "    modeldata = model_tdf,\n",
    "    newdata = tdf_test,\n",
    "    accumulate = ['CustomerId', 'Exited'],\n",
    "    model_output_fields = ['probability(1)', 'probability(0)']\n",
    ")\n",
    "\n",
    "result_xgb.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01560d57-bae6-45a5-b4cb-ffa96d050ed3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b> If the scoring performed with a classification model does not return a predicted value, the prediction output column could be empty. If the scoring is performed on regression or models which result in a single field, the prediction column will contain a value.</i></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ROC curve is a graph between TPR(True Positive Rate) and FPR(False Positive Rate). The area under the ROC curve measures how well the model can distinguish between positive and negative classes. The higher the AUC, the better the model's performance in distinguishing between the positive and negative categories. AUC above 0.75 is generally considered decent.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d601c58-57c0-4923-a914-77d3c6000f6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_dt = ROC(\n",
    "    probability_column = '\"probability(1)\"',\n",
    "    observation_column = \"Exited\",\n",
    "    positive_class = '1',\n",
    "    data = result_dt.result,\n",
    "    num_thresholds = 100\n",
    ")\n",
    "\n",
    "roc_xgb = ROC(\n",
    "    probability_column = '\"probability(1)\"',\n",
    "    observation_column = \"Exited\",\n",
    "    positive_class = '1',\n",
    "    data = result_xgb.result,\n",
    "    num_thresholds = 100\n",
    ")\n",
    "\n",
    "auc_dt = roc_dt.result.get('AUC').get_values()[0][0]\n",
    "auc_xgb = roc_xgb.result.get('AUC').get_values()[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afc2c950-5421-435a-860d-741a50bc4481",
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_xgb.output_data.plot(\n",
    "    x = roc_dt.output_data.fpr,\n",
    "    y = [roc_dt.output_data.tpr, roc_xgb.output_data.tpr, roc_dt.output_data.fpr],\n",
    "    legend = [\n",
    "                'Decision Tree: AUC = {}'.format(str(round(auc_dt, 4))),\n",
    "                'XGBoost: AUC = {}'.format(str(round(auc_xgb, 4))),\n",
    "                'Baseline: AUC = {}'.format(str(round(0.5, 4)))\n",
    "             ],\n",
    "    legend_style = 'lower right',\n",
    "    title = 'Receiver Operating Characteristic (ROC) Curve',\n",
    "    xlabel = 'False Positive Rate',\n",
    "    ylabel = 'True Positive Rate',\n",
    "    color = ['green', 'orange', 'blue'],\n",
    "    linestyle = ['-', '-', '--']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72ba389f-bb0b-4d43-90b7-f8e571418581",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Looking at the above ROC Curve, we can confidently say that our models have performed well on testing data. The AUC Scores are on the higher side and resonate with our understanding that the models are performing well. Among the models we used, XGBoost performs best with a higher AUC Score.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6beb78f4-4bf4-47ca-ac3a-6bcd3db160df",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>6. Cleanup</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0420e021-35dd-401d-983d-93142c6c4b37",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9da55306-cfe5-4d34-978d-16c236504630",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['bank_models']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name=table)\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d78b9c75-9bb1-4528-8fd2-be5a3a6a72da",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Databases and Tables </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9472c2ca-65d0-4116-9c45-6411fcea52ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_BankChurn');\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adjacent-romantic",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b342381e-fa7f-415f-872c-36217b0f8a50",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Dataset:</b>\n",
    "\n",
    "- `Surname`: Surname\n",
    "- `CreditScore`: Credit score\n",
    "- `Geography`: Country (Germany / France / Spain)\n",
    "- `Gender`: Gender (Female / Male)\n",
    "- `Age`: Age\n",
    "- `Tenure`: No of years the customer has been associated with the bank\n",
    "- `Balance`: Balance\n",
    "- `NumOfProducts`: No of bank products used\n",
    "- `HasCrCard`: Credit card status (0 = No, 1 = Yes)\n",
    "- `IsActiveMember`: Active membership status (0 = No, 1 = Yes)\n",
    "- `EstimatedSalary`: Estimated salary\n",
    "- `Exited`: Abandoned or not? (0 = No, 1 = Yes)\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>Teradataml Python reference: <a href = 'https://docs.teradata.com/search/all?query=Python+Package+User+Guide&content-lang=en-US'>here</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d68cc0e-31b4-411c-8714-2979e6d0dcf6",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023, 2024. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}