UseCases/Diabetes_Prediction/Diabetes_Prediction_via_DF_and_GLM_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "383de3b0",
   "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",
    "       Diabetes prediction using Decision Forest Classifier and GLM\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": "84088d3c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>According to research, there are a lot of people that are suffering from diabetes all over the world. Studies show that in 2019, diabetes was the direct cause of 1.5 million deaths and almost 50% of all deaths occurred before the age of 70.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>  \n",
    "Over time, diabetes can have a negative impact on multiple organs. It can damage the heart, blood vessels, eyes, kidneys, and nerves. The earlier a person receives a proper treatment, the more likely he or she will be in lowering blood glucose level. In addition, the risk of failure of other organs will also be reduced.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>  \n",
    "Hence, we are showcasing the complete approach about how we can make prediction of diagnosis of  diabetes 6 months in advance. We are demonstrating how In-Database Functions can be used for model training and scoring and comparing the performance of 2 models. The data we are using is a sample dataset and the results and predictions may not be entirely accurate.\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b> Contents:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li  style = 'font-size:14px;font-family:Arial;color:#00233C'>Configuring the Environment</li>\n",
    "    <li  style = 'font-size:14px;font-family:Arial;color:#00233C'>Initiate a connection to Vantage</li>\n",
    "    <li  style = 'font-size:14px;font-family:Arial;color:#00233C'>Analyze the raw data set</li>\n",
    "    <li  style = 'font-size:14px;font-family:Arial;color:#00233C'>Train and Test a Decision Forest Model</li>\n",
    "        <ul  style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "            <li  style = 'font-size:14px;font-family:Arial;color:#00233C'>4.1 Train and Test split using SAMPLE. Splitting the dataset in 80:20 ratio for Train and Test respectively</li>\n",
    "            <li  style = 'font-size:14px;font-family:Arial;color:#00233C'>4.2 Train a Model</li> \n",
    "                <ol  style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "                    <li style = 'font-size:14px;font-family:Arial;color:#00233C' >Using the DecisionForest and TDDecisonForestPredict In Database function to predict if the person can have Diabetes or not. So, there are only 2 responses '0' and '1'.</li>\n",
    "                    <li style = 'font-size:14px;font-family:Arial;color:#00233C'>Using the GLM and TDGLMPredict In Database function to predict if the person can have Diabetes or not. So, there are only 2 responses '0' and '1'.</li>\n",
    "            </ol>\n",
    "            <li style = 'font-size:14px;font-family:Arial;color:#00233C'>4.3 Evaluate the Model :- Evaluation of the model is done using the TD_ClassificationEvaluator which provides various parameters for the model like Accuracy, Precision ,Recall etc.</li>\n",
    "        </ul>\n",
    "    <li style = 'font-size:14px;font-family:Arial;color:#00233C'>Cleanup</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Values</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Comprehensive health predictions and a reduced number of false positive and false negative results.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Reduced cost to patients and hospitals caused by heart attacks.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Identify patterns and symptoms leading to heart failure to ensure early intervention.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Advanced research and development stemming from the results of the data and models produced.</li></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Machine Learning and AI have a proven track record of improving patient outcomes and well-being across the entire healthcare industry. Traditional approaches to data preparation, model development, and deployment rely on manual, error-prone processes that prevent enterprises from realizing the true value of these tools and techniques.</p>\n",
    " \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>However, Vantage provides these same proven data preparation and machine learning capabilities, integrated as native ClearScape Analytic functions.  This allows organizations to drastically reduce data preparation, model development, and testing time, while allowing for much more frequent and iterative testing and tuning to ensure maximum life-critical accuracy. Furthermore, the exact same development pipeline can be deployed seamlessly to production, eliminating the traditional development-to-deployment gap in the ML and AI industry.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e506a03b",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18659e21-2575-4d63-bbff-b2bfa79e814e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries and set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c9ef63a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-15T12:52:46.361078Z",
     "start_time": "2023-02-15T12:52:46.353006Z"
    }
   },
   "outputs": [],
   "source": [
    "# Import necessary libraries.\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "warnings.simplefilter(action='ignore', category=DeprecationWarning)\n",
    "warnings.simplefilter(action='ignore', category=RuntimeWarning)\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "warnings.simplefilter(action='ignore', category=UserWarning)\n",
    "import json\n",
    "import getpass\n",
    "import pandas as pd\n",
    "from teradataml import *\n",
    "\n",
    "import teradatasql\n",
    "import plotly.express as px\n",
    "import plotly.figure_factory as ff\n",
    "import seaborn as sns\n",
    "from matplotlib import pyplot as plt\n",
    "from sklearn.metrics import confusion_matrix, ConfusionMatrixDisplay\n",
    "import numpy as np\n",
    "from sklearn import metrics\n",
    "display.max_rows = 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c57cc9f-a1f9-4f3c-bab2-4a769c2f174f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2e1712f-8b06-4c87-8236-7b008ed75975",
   "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": "429ff42b-51d5-41e3-b42d-59853d556691",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Diabetes_Classification_DFandGLM.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "faa901fc-f8f0-4213-b0a3-92ac92f23103",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. 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.  Here we are downloading the data to local storage which may yield somewhat faster execution, but there could be considerations of available storage. We are only creating local databases and tables as there are 625 columns in table which will be faster in local tables as compared to foreign tables.</p> \n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>“Note:  The data loading part of this demo will be slow because we have large number of  columns (625 columns).\"</b></p>    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6ffbafc-6b73-4816-bac3-c890fa5eab70",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_DiabetesPrediction_local');\"\n",
    " # Takes about 3 minutes 30 seconds\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e0d99ef-590a-4ca9-b0be-b751e706dd97",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – We should execute the below step only if we want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "539eac24-cc98-41d7-9425-4df914c6d251",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca60ca56",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Analyze the raw data set</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us start by creating a \"Virtual DataFrame\" that points directly to the dataset in Vantage. We then begin our analysis by checking the shape of the DataFrame and examining the data types of all its columns.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df298f8d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-15T12:27:47.529445Z",
     "start_time": "2023-02-15T12:27:41.611224Z"
    }
   },
   "outputs": [],
   "source": [
    "df=DataFrame(in_schema(\"DEMO_DiabetesPrediction\",\"Diabetes_Diagnosis_Data\"))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "952b7723",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Exploratory Data Analysis</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>With the help of exploratory data analysis (EDA) techniques, we will summarize the main characteristics about the data.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that the aggregated data is available to us in teradataml dataframe. Let's visualize this data to better understand the distribution of the data by different parameters. Vantage's Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI or many python modules available like plotly, seaborn etc. We can do all the calculations and pre-processing on Vantge and pass only the necessary information to visualization tools, this will not only make the calculation faster but also reduce the time due to less data movement between tools. We do the data transfer for this and the subsequent visualizations wherever necessary.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d962189d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-10T11:06:02.788016Z",
     "start_time": "2023-02-10T11:06:02.758383Z"
    }
   },
   "source": [
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.1. Age Distribution Plot</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Following plot shows the density distribution of the people that were diagnosed with diabetes or not based on their age.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dda7c8c5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-15T11:30:41.141224Z",
     "start_time": "2023-02-15T11:30:40.840681Z"
    }
   },
   "outputs": [],
   "source": [
    "DF=df.to_pandas()\n",
    "plt.rcParams['figure.figsize'] = [8, 2]\n",
    "Target = {0:'Non-Diabetic',1:'Diabetic'}\n",
    "for label in Target:\n",
    "    X = DF[DF[\"target\"] == label]\n",
    "    sns.distplot(X[\"dem_age\"], hist = False, kde = True,\n",
    "                 kde_kws = {'shade': True, 'linewidth': 3}, \n",
    "                  label = Target[label])\n",
    "\n",
    "plt.legend(prop={'size': 8}, title = 'Target')\n",
    "plt.title('Age Distribution')\n",
    "plt.xlabel('Age')\n",
    "plt.ylabel('Density')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b675f7ff",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-10T11:38:31.401944Z",
     "start_time": "2023-02-10T11:38:31.390947Z"
    }
   },
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.2. Gender Distribution Plot</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Following plot highlights the demographics showing the count of males and females.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbdd12d0-ec07-4249-aa15-f13ea452fc28",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_plot = df.select(['dem_female','MBR_ID']).groupby('dem_female').count()\n",
    "df_plot_pd = df_plot.to_pandas()\n",
    "df_plot_pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f61a1bd-31c6-43c8-ac83-80a9a97a9bae",
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.barplot(x = 'dem_female', y = 'count_MBR_ID', data = df_plot_pd)\n",
    "plt.xlabel('Gender')\n",
    "plt.ylabel('Frequency')\n",
    "plt.xticks(ticks = [0, 1], labels = ['Male', 'Female'])\n",
    "plt.title('Gender Distribution')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b38d130b",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.3. Target Distribution Plot</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Following plot highlights the count of diabetic and non-diabetic patients.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa4e2ea3-564b-491b-8f0b-13b0b10f5b18",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_plot = df.select(['target','MBR_ID']).groupby('target').count()\n",
    "df_plot_pd = df_plot.to_pandas()\n",
    "df_plot_pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b38b6ca3-ed54-4076-bd41-308af9bd1cc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.barplot(x = 'target', y = 'count_MBR_ID', data = df_plot_pd)\n",
    "plt.xlabel('Target')\n",
    "plt.ylabel('Frequency')\n",
    "plt.xticks(ticks = [0, 1], labels = ['Non-Diabetic', 'Diabetic'])\n",
    "plt.title('Target Distribution')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78a6f2de",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.4. SNOMED Codes Distribution Plot</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Following plot highlights how many visits consist of diagnoses, procedures and medications.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7082c71-cf55-4682-833e-064e381cfd5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "Diag = df.filter(regex=\"^diag\").sum().get_values().sum()\n",
    "Proc = df.filter(regex=\"^proc\").sum().get_values().sum()\n",
    "Drg = df.filter(regex=\"^drg\").sum().get_values().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd8b090e-5e42-441a-bfb5-0e2c1ee7990b",
   "metadata": {},
   "outputs": [],
   "source": [
    "Dict = {\"Diagnosis\":Diag,\"Procedures\":Proc,\"Drugs\":Drg}\n",
    "plt.bar(list(Dict.keys()),Dict.values())\n",
    "plt.xlabel(\"Medication\")\n",
    "plt.ylabel(\"Frequency\")\n",
    "plt.title(\"SNOMED Codes Distribution\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8d38af7",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Feature Selection</b></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Currently we have 622 features excluding ID and target. We used regression-based feature reduction technique to eliminate uninformative features.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a64627d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-10T12:03:15.389400Z",
     "start_time": "2023-02-10T12:03:15.367374Z"
    }
   },
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.1. Create Train and Test Dataset</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now that we have our prepared data set, we can perform an abbreviated machine learning workflow:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>Create Train and Test data sets using SAMPLE Clause(80:20 split)</li>\n",
    "    <li>Train the model</li>\n",
    "    <li>Evaluate the model using Test data</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Train and Test split using SAMPLE</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use EXCEPT clause in the second statement to ensure a non-intersecting set of data</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bd428a7-ae88-4e31-8021-de49366e87f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "diabetes_features_train = df[df.dem_age.mod(10) >=1]\n",
    "df_train_count = diabetes_features_train.select(['target','MBR_ID']).groupby('target').count()\n",
    "df_train_count"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9f7179a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output shows the number of people we are considering for each class to train the model – class 1 has Diabetes</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82d30658-b2a5-4ca1-ace0-9bb2c52038c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "diabetes_features_test = df[df.dem_age.mod(10) < 1]\n",
    "df_test_count = diabetes_features_test.select(['target','MBR_ID']).groupby('target').count()\n",
    "df_test_count"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cf4a87a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output shows the number of people we are considering for each class to validate the model – class 1 has Diabetes</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cbc51c2",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.2. Using GLM with ElasticNet regularization for feature reduction.</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60ec7e4f-edf1-4a6b-9b5c-203bcd9198d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "GLM_out = GLM(input_columns= ['2:622'],\n",
    "                    response_column = \"target\",\n",
    "                    data = diabetes_features_train,\n",
    "                    family='Binomial',\n",
    "                    learning_rate = 'optimal',\n",
    "                    batch_size=200,\n",
    "                    max_iter_num=100,\n",
    "                    alpha=0.15,\n",
    "                    lambda1=0.00175,\n",
    "                    iter_num_no_change=50,\n",
    "                    tolerance=0.0001,\n",
    "                    intercept=True,\n",
    "                    initial_eta=0.001,\n",
    "                    momentum = 0.0,\n",
    "                    local_sgd_iterations=0\n",
    "                    )\n",
    "output=GLM_out.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd0c7b23-5c1c-4c0c-ac90-d7c0942d2730",
   "metadata": {},
   "outputs": [],
   "source": [
    "output[output[\"estimate\"] != 0].sort(\"attribute\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0a421eb",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.3. Storing the selected features in a new table.</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9cbc0e00-94ea-4b1b-b143-ca5b6dc8fa2d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "list_attr = list(output[output[\"estimate\"] == 0].select([\"attribute\"]).get_values()[:,0])\n",
    "Total = list(range(2,623))\n",
    "dif1 = np.setdiff1d(Total, list_attr)\n",
    "dif2 = np.setdiff1d(list_attr, Total)\n",
    "temp3 = np.concatenate((dif1, dif2))\n",
    "Features = list(temp3)\n",
    "Features = list(filter(lambda x : x > 0, Features))\n",
    "Str = \"\"\n",
    "for i in range(len(Features)):\n",
    "    Str += \"{}\".format(diabetes_features_train.columns[Features[i]])\n",
    "    if i != (len(Features) - 1):\n",
    "        Str += \",\"\n",
    "Str        \n",
    "final_list =  \"MBR_ID\" +\",\" + \"target\"  + \",\" + Str \n",
    "column_list = final_list.split(',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99a6f7f1-3ced-4ba5-bc00-e7ed78e525b2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "train_dataset = diabetes_features_train[column_list]\n",
    "test_dataset = diabetes_features_test[column_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e5a6b09-49c6-4e51-a489-04bd4340a47c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train_count = train_dataset.select(['target','MBR_ID']).groupby('target').count()\n",
    "df_train_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afca8361-14a6-411a-bd70-27740f4f5d17",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_test_count = test_dataset.select(['target','MBR_ID']).groupby('target').count()\n",
    "df_test_count"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd131ba1",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Decision Tree Model</b></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.1 - Train a Decision Tree Model</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/teradataml-Analytic-Database-17.20.xx-Analytic-Functions/MODEL-TRAINING-functions/DecisionForest'>DecisionForest</a> is an ensemble algorithm used for classification and regression predictive modeling problems. It is an extension of bootstrap aggregation (bagging) of decision trees. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This function takes the training data as input, as well as the following function parameters</p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>InputColumns; list or range of columns used as features (we used an ordinal reference of columns 2:217)</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>ResponseColumn; the dependent or target value (we used “class”, the first column)</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>TreeType; either CLASSIFICATION or REGRESSION</li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Other hyperparameter values detailed in the documentation</li>\n",
    "        </ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0f608f5-31a0-46b0-947d-3664ef3fac95",
   "metadata": {},
   "outputs": [],
   "source": [
    "DecisionForest_out = DecisionForest(data = train_dataset, \n",
    "                            input_columns = ['2:217'], \n",
    "                            response_column = 'target', \n",
    "                            max_depth = 16, \n",
    "                            num_trees = 8, \n",
    "                            min_node_size = 1, \n",
    "                            mtry = -1, \n",
    "                            mtry_seed = 3,\n",
    "                            seed = 3, \n",
    "                            tree_type = 'CLASSIFICATION')\n",
    "\n",
    "decision_df=DecisionForest_out.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23535ee0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The DecisionForest function produces a model and a JSON representation of the decision tree. Below is explaination for some columns in the JSON tree. The other details can be found at the link <a href = 'https://docs.teradata.com/search/all?query=TD_DecisionForest&content-lang=en-US'>here.</a></p>\n",
    "\n",
    "</p>\n",
    "<html>\n",
    "   <head>\n",
    "      <style>\n",
    "         table, th, td {\n",
    "            border: 1px solid black;\n",
    "            border-collapse:collapse;\n",
    "         }\n",
    "      </style>\n",
    "   </head>\n",
    "   <body>\n",
    "      <table>\n",
    "         <tr>\n",
    "            <th>JSON Type</th>\n",
    "            <th>Description</th>             \n",
    "         </tr>\n",
    "         <tr>\n",
    "            <td>id_</td>\n",
    "            <td>\"Node identifier\"</td>\n",
    "         </tr>\n",
    "         <tr>\n",
    "            <td>nodeType_</td> \n",
    "            <td>The node type. Possible values: CLASSIFICATION_NODE,CLASSIFICATION_LEAF,REGRESSION_NODE,REGRESSION_LEAF.</td>\n",
    "         </tr>\n",
    "         <tr>\n",
    "            <td>split_</td> \n",
    "            <td>The start of JSON item that describes a split in the node.</td>\n",
    "         </tr> \n",
    "         <tr>\n",
    "            <td>responseCounts_</td> \n",
    "            <td>[Classification trees] Number of observations in each class at node identified by id.</td>\n",
    "         </tr>\n",
    "         <tr>\n",
    "            <td>size_</td> \n",
    "            <td>Total number of observations at node identified by id.</td>\n",
    "         </tr> \n",
    "         <tr>\n",
    "            <td>maxDepth_</td> \n",
    "            <td>Maximum possible depth of tree, starting from node identified by id. For root node, the\n",
    "value is max_depth. For leaf nodes, the value is 0. For other nodes, the value is the\n",
    "maximum possible depth of tree, starting from that node.</td>\n",
    "         </tr>  \n",
    "      </table>\n",
    "   </body>\n",
    "</html>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ef273ac",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.2 - Evaluate the Model</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Execute a testing prediction using the split data above.  Evaluate the model by creating a confusion matrix with the <a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/teradataml-Analytic-Database-17.20.xx-Analytic-Functions/MODEL-EVALUATION-functions/ClassificationEvaluator'>ClassificationEvaluator</a> SQL Function.</p>\n",
    "\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Execute <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Model-Scoring-Functions/DecisionForestPredict'>TDDecisionForestPredict</a> using the model built above</li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Execute <a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/teradataml-Analytic-Database-17.20.xx-Analytic-Functions/MODEL-EVALUATION-functions/ClassificationEvaluator'>ClassificationEvaluator</a> and pass the actual classification and the predicted value</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8790bdc-f2ef-490c-a7c4-599efd7e6b8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Predict_out = TDDecisionForestPredict(\n",
    "    newdata=test_dataset,\n",
    "    object=DecisionForest_out.result,\n",
    "    id_column='MBR_ID',\n",
    "    accumulate='target',\n",
    "    output_prob=True,\n",
    "    output_responses=['0','1']\n",
    "    )\n",
    "\n",
    "DF_Predict_out.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7be29b9e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TDDecisionForestPredict function creates probabilities for the prediction made depending on the class and the Id columns. The output of the predict function is passed to the Classification Evaluator to get the parameters of the functions.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>DecisionForestPredict outputs the probability that each observation is in the predicted class. To use DecisionForestPredict output as input to ML Engine ROC function, you must first transform it to show the probability that each observation is in the positive class. One way to do this is to change the probability to (1- current probability) when the predicted class is negative. The prediction algorithm compares floating-point numbers. Due to possible inherent data type differences between ML Engine and Analytics Database executions, predictions can differ.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c86b411",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create the <b>Confusion Matrix</b> to compare the actual and the Predicted values. Confusion matrix is a very popular measure used while solving classification problems. It can be applied to binary classification as well as for multiclass classification problems. Confusion matrices represent counts from predicted and actual values. It is an N x N matrix used for evaluating the performance of a classification model, where N is the number of target classes.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "704d2c52",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-15T12:32:26.306506Z",
     "start_time": "2023-02-15T12:32:23.299605Z"
    }
   },
   "outputs": [],
   "source": [
    "df=DF_Predict_out.result.to_pandas()\n",
    "plt.rcParams['figure.figsize'] = [6, 6]\n",
    "cm = confusion_matrix(df['target'], df['prediction'])\n",
    "cmd = ConfusionMatrixDisplay(cm, display_labels=['DoesNotHaveDiabetes', 'HasDiabetes'])\n",
    "cmd.plot()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f045686",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.3 - Use classification Evaluator for DecisionForestPredict</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will evaluate the model by creating a confusion matrix with the <a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/teradataml-Analytic-Database-17.20.xx-Analytic-Functions/MODEL-EVALUATION-functions/ClassificationEvaluator'>ClassificationEvaluator</a> Function.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In classification problems, a confusion matrix is used to visualize the performance of a classifier. The confusion matrix contains predicted labels represented across the row-axis and actual labels represented\n",
    "across the column-axis. Each cell in the confusion matrix corresponds to the count of occurrences of labels\n",
    "in the test data.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Apart from accuracy, the secondary output table returns micro, macro, and weighted-average metrics of precision, recall, and F1-score values.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b03bad94-a124-4999-a866-0a207275acee",
   "metadata": {},
   "outputs": [],
   "source": [
    "ClassificationEvaluator_obj = ClassificationEvaluator(data=DF_Predict_out.result,\n",
    "                                                          observation_column='target',\n",
    "                                                          prediction_column='prediction',\n",
    "                                                          labels=['0', '1'])\n",
    "classeval_decisiondf = ClassificationEvaluator_obj.output_data\n",
    "classeval_decisiondf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "456de93d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output has the secondary output table that returns micro, macro, and weighted-average metrics of precision, recall, and F1-score values.</p>\n",
    "<table style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "  <tr>\n",
    "    <th>Column</th>\n",
    "    <th>Description</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Precision</td>\n",
    "    <td>The positive predictive value. Refers to the fraction of relevant instances among\n",
    "the total retrieved instances.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Recall</td>\n",
    "    <td>Refers to the fraction of relevant instances retrieved over the total amount of\n",
    "relevant instances.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>F1</td>\n",
    "    <td>F1 score, defined as the harmonic mean of the precision and recall.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Support</td>\n",
    "    <td>The number of times a label displays in the ObservationColumn.</td>\n",
    "  </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2a86cad",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.4 - Show AUC-ROC Curve for DecisionForestPredict</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <a href = 'https://docs.teradata.com/search/all?query=TD_ROC&content-lang=en-US'>ROC</a> curve shows the performance of a binary classification model as its discrimination threshold varies. For a range of thresholds, the curve plots the true positive rate against false-positive rate.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This function accepts a set of prediction-actual pairs as input and calculates the following values for a range of discrimination thresholds.</p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>True-positive rate (TPR)</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>False-positive rate (FPR)</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>The area under the ROC curve (AUC)</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Gini coefficient</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Other details are mentioned in the documentation</li>\n",
    "    </ul>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e3514b3-52b2-4fb2-b852-f78a3d91c605",
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_df = ROC(data = DF_Predict_out.result, \n",
    "                    probability_column = \"prob_1\",\n",
    "                    observation_column = \"target\",\n",
    "                    positive_class=\"1\"\n",
    "                    )\n",
    "roc_df.output_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93ff3821-c5c3-4a70-a312-2a7a2d23f8a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "auc = roc_df.result.get_values()[0][0]\n",
    "auc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f7d08e8-ee0f-4708-ad3e-c852df22261c",
   "metadata": {},
   "outputs": [],
   "source": [
    "plot_roc_df = roc_df.output_data\n",
    "plot =  plot_roc_df.plot(x=plot_roc_df.fpr, y=plot_roc_df.tpr,\n",
    "                         title=\"Receiver Operating Characteristic (ROC) Curve\",\n",
    "                         xlabel='False Positive Rate', \n",
    "                         ylabel='True Positive Rate', \n",
    "                         color=\"blue\",\n",
    "                         legend=f'AUC = {round(auc, 4)}',\n",
    "                         legend_style='lower right',\n",
    "                         grid_linestyle='--',\n",
    "                         grid_linewidth=0.5)\n",
    " \n",
    "# Display the plot.\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa7dae48",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Generalized Linear Model(GLM)</b></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.1 - Train a GLM Model</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <a href = 'https://docs.teradata.com/search/all?query=TD_GLM&content-lang=en-US'>Generalized Linear Model (GLM)</a> is an extension of the linear regression model that enables the linear equation to relate to the dependent variables by a link function. The GLM function supports several distribution families and associated link functions. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This function takes the training data as input, as well as the following function parameters</p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial'>\n",
    "        <li>InputColumns; list or range of columns used as features (we used an ordinal reference of columns 2:217)</li>\n",
    "        <li>ResponseColumn; the dependent or target value (we used “class”, the first column) </li>\n",
    "        <li>Family; either Binomial or Gaussian</li>\n",
    "    <li>Other hyperparameter values detailed in the documentation</li>\n",
    "        </ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8898bc4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the GLM function to train the model on our data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98ae45bf-000d-45cc-8c6b-352d3ea10c6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "GLM_df = GLM(input_columns= ['2:217'],\n",
    "                    response_column = \"target\",\n",
    "                    data = train_dataset,\n",
    "                    family='Binomial',\n",
    "                    learning_rate = 'optimal',\n",
    "                    batch_size=200,\n",
    "                    max_iter_num=300,\n",
    "                    alpha=0.15,\n",
    "                    lambda1=0.01,\n",
    "                    iter_num_no_change=50,\n",
    "                    tolerance=0.008,\n",
    "                    intercept=True,\n",
    "                    initial_eta=0.05,\n",
    "                    momentum = 0.8,\n",
    "                    local_sgd_iterations=0\n",
    "                    )\n",
    "GLM_df.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "166fa568",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The GLM function creates various output predictors and values based on the above parameters passed in the query</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function output is a trained GLM model which can be input to the TDGLMPredict function\n",
    "for prediction. The model also contains model statistics of MSE, Loglikelihood, AIC, and BIC.\n",
    "Further model evaluation can be done as a post-processing step using functions such as\n",
    "TD_RegressionEvaluator, TD_ClassificationEvaluator and TD_ROC.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfa3b9ec",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;> The TD_DecisionForest function creates a tree as seen in the output above based on the parameters applied in the query. </b></p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5ad0028",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.2 - Evaluate the Model</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Execute a testing prediction using the split data above.  Evaluate the model by creating a confusion matrix with the <a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/teradataml-Analytic-Database-17.20.xx-Analytic-Functions/MODEL-EVALUATION-functions/ClassificationEvaluator'>ClassificationEvaluator</a> Function.</p>\n",
    "\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>Execute <a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/teradataml-Analytic-Database-17.20.xx-Analytic-Functions/MODEL-SCORING-functions/TDGLMPredict'>GLMPredict</a> using the model built above</li>\n",
    "    <li>Execute <a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/teradataml-Analytic-Database-17.20.xx-Analytic-Functions/MODEL-EVALUATION-functions/ClassificationEvaluator'>ClassificationEvaluator</a> and pass the actual classification and the predicted value</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16562fbe-16f0-47fb-b559-0598ff585ecf",
   "metadata": {},
   "outputs": [],
   "source": [
    "TDGLMPredict_out = TDGLMPredict(object=GLM_df.result,\n",
    "                                    newdata=test_dataset,\n",
    "                                    accumulate=\"target\",\n",
    "                                    id_column=\"MBR_ID\",\n",
    "                                    output_prob=True,\n",
    "                                    output_responses=['0','1'])\n",
    "df=TDGLMPredict_out.result\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34c3f836",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TDGLMPredict function predicts target values (regression) and class labels (classification) for test data using a GLM model trained by the GLM function. Similar to GLM, input features should be standardized, such as using ScaleFit, and ScaleTransform, before using in the function. The function takes only numeric features. The categorical\n",
    "features must be converted to numeric values prior to prediction.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Rows with missing (null) values are skipped by the function during prediction. For prediction results evaluation, you can use RegressionEvaluator, ClassificationEvaluator or ROC function as postprocessing step.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e7cde9f",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.3 - Use classification Evaluator for GLMPredict</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Evaluate the model by creating a confusion matrix with the <a href = 'https://docs.teradata.com/search/all?query=TD_ClassificationEvaluator&content-lang=en-US'>ClassificationEvaluator</a> Function.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51c60de7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Since ClassificationEvaluator requires same datatype for prediction and class columns so creating another table with same datatype.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1b1d567",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Create CONFUSION MATRIX for the GLM Predict model.</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "941200a1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-15T12:33:41.697257Z",
     "start_time": "2023-02-15T12:33:37.882538Z"
    }
   },
   "outputs": [],
   "source": [
    "df_glm = df.to_pandas()\n",
    "cm = confusion_matrix(df_glm['target'], df_glm['prediction']) # , normalize='all'\n",
    "cmd = ConfusionMatrixDisplay(cm, display_labels=['DoesNotHaveDiabetes', 'HasDiabetes'])\n",
    "cmd.plot()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85c18553-ea43-4120-935e-94e8aa0920d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_conv = df.assign(pred_int = df.prediction.cast(type_=INTEGER),\n",
    "                    target_int = df.target.cast(type_=INTEGER))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64a1666d-54b8-45fe-9e50-415e18d1a96b",
   "metadata": {},
   "outputs": [],
   "source": [
    "ClassificationEvaluator_glmobj = ClassificationEvaluator(data=df_conv,\n",
    "                                                          observation_column='target_int',\n",
    "                                                          prediction_column='pred_int',\n",
    "                                                          labels=['0', '1'])\n",
    "classeval_glmdf = ClassificationEvaluator_glmobj.output_data\n",
    "classeval_glmdf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57730f7b",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.4 - Show AUC-ROC Curve for GLMPredict</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <a href = 'https://docs.teradata.com/search/all?query=TD_ROC&content-lang=en-US'>ROC</a> curve shows the performance of a binary classification model as its discrimination threshold varies. For a range of thresholds, the curve plots the true positive rate against false-positive rate.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This function accepts a set of prediction-actual pairs as input and calculates the following values for a range of discrimination thresholds.</p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial'>\n",
    "        <li>True-positive rate (TPR)</li>\n",
    "        <li>False-positive rate (FPR)</li>\n",
    "        <li>The area under the ROC curve (AUC)</li>\n",
    "        <li>Gini coefficient</li>\n",
    "        <li>Other details are mentioned in the documentation</li>\n",
    "    </ul>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a3ba8eb-ec89-4ac5-892e-07e0804fa48f",
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_glmdf = ROC(data = df_conv, \n",
    "                    probability_column = \"prob_1\",\n",
    "                    observation_column = \"target\",\n",
    "                    positive_class=\"1\"\n",
    "                    )\n",
    "roc_glmdf.output_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de6bb6a6-461b-4f8e-b40d-25bcfd841b0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "auc = roc_glmdf.result.get_values()[0][0]\n",
    "auc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "133400c2-ecc2-499f-b89d-9aa7e3316930",
   "metadata": {},
   "outputs": [],
   "source": [
    "plot_roc_df = roc_glmdf.output_data\n",
    "plot =  plot_roc_df.plot(x=plot_roc_df.fpr, y=plot_roc_df.tpr,\n",
    "                         title=\"Receiver Operating Characteristic (ROC) Curve\",\n",
    "                         xlabel='False Positive Rate', \n",
    "                         ylabel='True Positive Rate', \n",
    "                         color=\"blue\",\n",
    "                         legend=f'AUC = {round(auc, 4)}',\n",
    "                         legend_style='lower right',\n",
    "                         grid_linestyle='--',\n",
    "                         grid_linewidth=0.5)\n",
    " \n",
    "# Display the plot.\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fe61f7a",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Comparison of the Metrics generated by the 2 Models. Decision Forest vs GLM</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38d0776e-a3d0-40de-a3b6-50d1fce2956a",
   "metadata": {},
   "outputs": [],
   "source": [
    "classeval_alldf = classeval_decisiondf.merge(right = classeval_glmdf, how='inner', on='SeqNum' , \n",
    "                                             lsuffix = 't1', rsuffix = 't2')\n",
    "display.max_rows=10\n",
    "classeval_alldf = classeval_alldf.assign(drop_columns=True,\n",
    "                                         SeqNum = classeval_alldf.t1_SeqNum,\n",
    "                                         Metric = classeval_alldf.t1_Metric,\n",
    "                                         GLM_MetricValue = classeval_alldf.t2_MetricValue * 100,\n",
    "                                         DF_MetricValue = classeval_alldf.t1_MetricValue * 100)\n",
    "classeval_alldf.sort('SeqNum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39e914bf-7591-40f3-96a9-6fd7ec710143",
   "metadata": {},
   "outputs": [],
   "source": [
    "figure = Figure(width=800, height=400, image_type=\"jpg\",  heading=\"Comparison of Metric Values\")\n",
    "plot =  classeval_alldf.plot(x=classeval_alldf.SeqNum, \n",
    "                             \n",
    "                             y=[classeval_alldf.DF_MetricValue, classeval_alldf.GLM_MetricValue],\n",
    "                             color=['orange', 'blue'], figure=figure, kind='bar', \n",
    "                             xlabel = 'Metrics' , ylabel = 'Metric Values',\n",
    "                            legend=['Decision Forest', 'GLM'])\n",
    " \n",
    "# Display the plot.\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0814dc77",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus, here we have used 2 different models to train and predict the data. The classification evaluator is used to evaluate and compare the models. The Teradata In-Database functions are used for training, prediction and evaluation. In this case since we have sample data the result parameters like the Accuracy, Precision, Recall etc. may not be accurate for both the models, still from the above graph we can conclude that in this case DecisionForest is better than GLM. \n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data preparation and machine learning capabilities, integrated as native ClearScape Analytic functions in Vanatage allow organizations to drastically reduce data preparation, model development, and testing time, while allowing for much more frequent and iterative testing and tuning to ensure maximum life-critical accuracy. Furthermore, the exact same development pipeline can be deployed seamlessly to production, eliminating the traditional development-to-deployment gap in the ML and AI industry.</p>    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4841f903",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. Cleanup</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71585277-fd47-49d9-bca9-6b9fcda317a2",
   "metadata": {},
   "source": [
    "<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'>We will use the following code to clean up tables and databases created for this demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6efa3cfa-e98e-4d22-a7a0-e4f06c080852",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_DiabetesPrediction');\" \n",
    "#Takes 40 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "578d3ba3-8ac2-482f-8e4f-ee0eae70ed93",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86ca9a18-e893-41e0-98b7-5cb71ac35b3d",
   "metadata": {},
   "source": [
    "\n",
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Required Materials</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let’s look at the elements we have available for reference for this notebook:</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b> Data</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This dataset contains data for 10000 patients, half of which were diagnosed with diabetes. It contains 624 columns. We used patients' visit records consisting of diagnoses, procedures, medications and demographics. In addition, we also added a temporal aspect to the medical features. We differentiated between events occurring 1-3 months before diagnosis, 3-6 months, and 6-12 months, before the prediction window. The main aim of the data is to distinguish between healthy people and those who were diagnosed with diabetes, according to \"target\" column which is set to 0 for non-diabetic and 1 for diabetic.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://synthea.mitre.org/'>Link to the dataset</a>: This dataset was generated by Synthea for the experimentation purpose and does not reflect the actual population.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Filters:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Industry:</b> Healthcare</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Functionality:</b> Machine Learning</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use Case:</b> Prediction Analysis</li></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='https://usc-word-edit.officeapps.live.com/we/%E2%80%A2%09https:/www.teradata.com/Blogs/Predicting-Heart-Failure-with-Teradata'>Saving Lives, Saving Costs: Predicting Heart Failure with Teradata</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='https://www.teradata.com/Blogs/Hyper-scale-time-series-forecasting-done-right'>Hyper-scale time series forecasting done right</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='https://www.teradata.com/Blogs/Forecasting-COVID-19-Using-Teradata-Vantage'>Forecasting COVID-19 Using Teradata Vantage</a>\n",
    "    </li></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "858c3f2a-3ac4-4ed2-96c2-98500393bd96",
   "metadata": {
    "tags": []
   },
   "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}