UseCases/Diabetes_Prediction/Diabetes_Prediction_via_BYOM_H2O_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "538fef47",
   "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 BYOM\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": "ee1abd72-45ff-4ed5-a6a2-d8050347ae70",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>According to research, there are a lot of people who 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 harm multiple organs. It can damage the heart, blood vessels, eyes, kidneys, and nerves. The earlier a person receives proper treatment, the more likely he or she will be to lower blood glucose levels. 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 as a data science consultant, we are showcasing the complete approach to how we can predict a diagnosis of diabetes 6 months in advance. We are demonstrating how we can bring our models that were trained with open-source technologies to Teradata Vantage for scoring. The data we are using is a sample dataset and the results and predictions may not be entirely accurate.\n",
    "</p>\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 costs 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'>The ML and AI industry continues to innovate at an unprecedented rate. Tools, technologies, and algorithms are being developed and improved in both the open-source and commercial communities.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Unfortunately, many of these techniques haven’t matured to the point where they are readily deployable to a stable, mature operational environment. Furthermore, many open-source techniques rely on fragile, manual enabling technologies. Traditional approaches require the developers to move data from the sources to the analytics. Even \"integrated\" analytic systems like Apache Spark provide parallel processing for analyzing data but don't optimize for loading data - neither locality nor quantity that needs to be moved. Teradata Vantage reverses this model and allows PUSH processing down to the individual processing nodes where the data resides. This allows for the unprecedented scale of the analytical processing, reduced costs in data movement/egress charges, and drastically improved performance.\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A critical strategy for Vantage and ClearScape Analytics is to embrace the value and innovation in the open-source and partner ML and AI community. A cornerstone of that strategy is to allow users to leverage their ML or AI tools and models of choice to deploy those models directly to the Vantage Platform. This provides enterprises with the most scalable option for deploying custom machine learning pipelines. Users can leverage the innovation and familiarity of a broad range of tools and techniques, with the ability to prepare and score new data in near-real-time and at any scale; allowing the products of machine learning to become pervasive across all applications, reporting tools, and consumers in an organization.\n",
    "</p>\n",
    " \n",
    "\n",
    " \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4181b0c2",
   "metadata": {},
   "source": [
    "\n",
    "<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>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1844913-f58b-43b1-b4ed-eb6837b8fd2a",
   "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": "857f3995",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-16T04:44:47.339613Z",
     "start_time": "2023-02-16T04:44:47.330159Z"
    }
   },
   "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",
    "\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",
    "from sklearn.metrics import classification_report\n",
    "import numpy as np\n",
    "from sklearn import metrics\n",
    "\n",
    "# import teradataml\n",
    "# from teradataml.dataframe.dataframe import in_schema\n",
    "# from teradataml.catalog.byom import save_byom, retrieve_byom\n",
    "# from teradataml.analytics.byom import H2OPredict\n",
    "\n",
    "# import sqlalchemy\n",
    "# from sqlalchemy import event\n",
    "# from sqlalchemy.types import String\n",
    "# from teradataml.context.context import *\n",
    "# from teradataml.dataframe.dataframe import DataFrame\n",
    "# from sqlalchemy.types import VARCHAR\n",
    "# from teradataml.dataframe.copy_to import copy_to_sql\n",
    "display.max_rows=5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efd05389-73b2-4fde-9567-a8e40c7ecc08",
   "metadata": {},
   "source": [
    "<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, then use down arrow to go to next cell. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c87b2c9f-f9c1-4ab1-a67a-67748c5b81b5",
   "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": "518438c5-ab73-4b25-be66-7ea80023b7bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Diabetes_Classification_BYOM.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "235d2a49-dc20-48b5-8bc6-b2fc3775f4d8",
   "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",
    "\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": "6650f56c-4dee-4cea-84b3-1ce630cf3ed6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_DiabetesPrediction_BYOM_local');\"\n",
    " # Takes about 4 minutes \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ff8a36e-41b6-4d26-88b0-f6d6e7493f99",
   "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": "1f899580-55f0-46f5-a8e1-e31dcad0e125",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2063cfb",
   "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",
    "\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",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87041040-5547-4b55-8f3a-7faf5dda71fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_byom=DataFrame(in_schema(\"DEMO_DiabetesPrediction_BYOM\",\"Diabetes_Diagnosis_BYOM_Data\"))\n",
    "df_byom"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f8bfe51",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Bringing our Trained H2O Model to Vantage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acbad396",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-Package-for-Python-User-Guide-17.20/BYOM/save_byom'>save_byom</a> function allows users to save various models stored in different formats such as PMML, MOJO, and so on.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This function takes the following function parameters</p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial'>\n",
    "        <li>Model_ID specifies the unique model identifier for this model</li>\n",
    "        <li>Model_File specifies the absolute path of the file which has model information</li>\n",
    "    <li>Other parameter values detailed in the documentation</li>\n",
    "        </ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d090e406",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have our trained model in the form of zip file. In order to use it in-database, we need to save it to the table.</p>    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f44ccd9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-16T04:49:24.426591Z",
     "start_time": "2023-02-16T04:49:20.760672Z"
    }
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "CREATE SET TABLE DBT_H2O_Models (\n",
    "  model_id VARCHAR (30),\n",
    "  model BLOB\n",
    ")\n",
    "PRIMARY INDEX (model_id);\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('DBT_H2O_Models')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66fdfcf3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-16T04:49:39.636705Z",
     "start_time": "2023-02-16T04:49:25.973700Z"
    }
   },
   "outputs": [],
   "source": [
    "save_byom(model_id=\"dbt_model_1\", model_file=\"GBM_2_AutoML_1_20230214_131128.zip\", table_name=\"DBT_H2O_Models\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27469199",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Model Scoring\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e204ee24",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-Package-for-Python-User-Guide-17.20/BYOM/retrieve_byom'>retrieve_byom</a> API allows a user to retrieve a saved model. Output of this function can be directly passed as input to the PMMLPredict and H2OPredict functions.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This function takes the following function parameters</p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial'>\n",
    "        <li>Model_ID specifies the unique model identifier of the model to be retrieved</li>\n",
    "    <li>Other parameter values detailed in the documentation</li>\n",
    "        </ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01e78d2f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>After bringing our model to Vantage, we need to score it on the data available in Vantage to check its performance.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c027a608-7907-424c-8788-34ce640f9d28",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data = DataFrame(in_schema(\"DEMO_DiabetesPrediction_BYOM\", \"Diabetes_Diagnosis_BYOM_Data\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd203b26",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-16T05:03:17.782813Z",
     "start_time": "2023-02-16T05:02:46.937266Z"
    }
   },
   "outputs": [],
   "source": [
    "modeldata = retrieve_byom(\"dbt_model_1\", table_name=\"DBT_H2O_Models\")\n",
    "\n",
    "configure.byom_install_location = \"mldb\"\n",
    "\n",
    "result = H2OPredict(newdata=df_data,\n",
    "                    newdata_partition_column='MBR_ID',\n",
    "                    newdata_order_column='MBR_ID',\n",
    "                    modeldata=modeldata,\n",
    "                    modeldata_order_column='model_id',\n",
    "                    model_output_fields=['classProbabilities'],\n",
    "                    # model_output_fields=['prob_0','prob_1'],\n",
    "                    accumulate=['MBR_ID'],\n",
    "                    overwrite_cached_models='*',\n",
    "                    enable_options=['contributions','stageProbabilities'],\n",
    "                    model_type='OpenSource'\n",
    "                    )\n",
    "df_predict = result.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e8b8070-f43b-436a-8af8-a3fabb49fd45",
   "metadata": {},
   "outputs": [],
   "source": [
    "Target = df_data.select(['MBR_ID', 'target'])\n",
    "# DataFrame.from_query(\"SELECT MBR_ID, target FROM DEMO_DiabetesPrediction_BYOM.Diabetes_Diagnosis_BYOM_Data;\")\n",
    "Result_df = df_predict.merge(Target,how='inner', on=\"MBR_ID\", lsuffix = 't1', rsuffix='t2')\n",
    "Result_df = Result_df.assign(drop_columns=True, \n",
    "                             MBR_ID = Result_df.MBR_ID_t1,\n",
    "                             target = Result_df.target,\n",
    "                             prediction = Result_df.prediction,\n",
    "                             classprobabilities = Result_df.classprobabilities)\n",
    "Result_df  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac939960-8594-4b3d-9035-53a09adfb26f",
   "metadata": {},
   "outputs": [],
   "source": [
    "Result=Result_df.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ddd14e6-e0cf-4434-a001-dc71644d575b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def Predict(x):\n",
    "    if x[\"1\"] >= 0.5:\n",
    "        return 1\n",
    "    return 0\n",
    "def Prob(x):\n",
    "    return x[\"1\"]\n",
    "# Target = df_data.select(['MBR_ID', 'target'])\n",
    "# # DataFrame.from_query(\"SELECT MBR_ID, target FROM DEMO_DiabetesPrediction_BYOM.Diabetes_Diagnosis_BYOM_Data;\")\n",
    "# Result = df_predict.merge(Target.to_pandas(),how='inner', on=\"MBR_ID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98c952ad",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We are using the class probabilities returned by H2OPredict to classify the prediction as 0 or 1.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11a2ce13-d170-4434-86b3-99180d2eb460",
   "metadata": {},
   "outputs": [],
   "source": [
    "Result[\"classprobabilities\"].isna().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18cc5670-82f1-479c-a5ec-f9d37b7c04d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "Result[\"classprobabilities\"].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf84377d-fcac-4fe9-aa9c-70b1052376cc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "Result[\"classprobabilities\"].apply(json.loads)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "208508cf-9e42-4e21-a251-2d5369dc7f5f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "Result[\"classprobabilities\"] = Result[\"classprobabilities\"].apply(json.loads)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee457a5d-3b65-4e44-8d62-333d6511ddc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "Result[\"Prediction\"] = Result[\"classprobabilities\"].apply(Predict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4415060a-ea93-47de-87fe-446f7cb91ede",
   "metadata": {},
   "outputs": [],
   "source": [
    "Result[\"Prob_1\"] = Result[\"classprobabilities\"].apply(Prob)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8331dfa8-063b-4e3e-baa3-65f9b36780da",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "Result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56cfad54",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Create CONFUSION MATRIX.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0d3cee1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-02-16T05:10:17.074236Z",
     "start_time": "2023-02-16T05:10:16.916240Z"
    }
   },
   "outputs": [],
   "source": [
    "cm = confusion_matrix(Result['target'], Result['Prediction'])\n",
    "cmd = ConfusionMatrixDisplay(confusion_matrix=cm, display_labels=['DoesNotHaveDiabetes', 'HasDiabetes'])\n",
    "cmd.plot()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e01a97a2-b9a2-4311-b5c6-8070545ef780",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above Confusion Matrix shows the actual and the Predicted values. Based on H2O model used the above matrix shows the predicted and actual value comparison for people having Diabetes and those not having Diabetes.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "356d1b7f-d043-497c-967f-6a412dd5a4c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "Result_df = Result_df.assign(drop_columns=True, \n",
    "                             MBR_ID = Result_df.MBR_ID,\n",
    "                             target = Result_df.target.cast(type_=INTEGER),\n",
    "                             prediction = Result_df.prediction.cast(type_=INTEGER))                           "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93313aa1-3cb5-4611-8a8f-0afe533b1498",
   "metadata": {},
   "outputs": [],
   "source": [
    "ClassificationEvaluator_obj = ClassificationEvaluator(data=Result_df,\n",
    "                                                          observation_column='target',\n",
    "                                                          prediction_column='prediction',\n",
    "                                                          labels=['0', '1'])\n",
    "classeval_df = ClassificationEvaluator_obj.output_data\n",
    "classeval_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e92d442-7be6-44a4-91d0-a0f26f963d01",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output has the 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.\n",
    "        Precision answers the following question: what proportion of predicted Positives is truly Positive? \n",
    "        Precision = (TP)/(TP+FP)</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. Recall answers a different question: what proportion of actual Positives is correctly classified?\n",
    "Recall = (TP)/(TP+FN)</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>F1</td>\n",
    "    <td>F1 score, defined as the harmonic mean of the precision and recall and is a number between 0 and 1. F1 score maintains a balance between the precision and recall for your classifier.                                         \n",
    "                      F1 = 2*(precision*recall/precision+recall)</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Support</td>\n",
    "    <td>The number of times a label displays in the Observation Column.</td>\n",
    "  </tr>\n",
    "</table>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>**TP:- True Positive , FP :- False Positive, TN :- True Negative , FN :- False Negative"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e978d4f",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Show AUC-ROC Curve</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create AUC-ROC curve with the sk-learn roc_curve() Function.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ROC curve shows the performance of a binary classification model as its discrimination threshold varies. For a range of thresholds, the curve plots the TPR(true-positive rate) against FPR(false-positive rate).The ROC curve is plotted with TPR against the FPR where TPR is on the y-axis and FPR is on the x-axis.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3041fb6f-02b7-494f-89c6-afa73e248a95",
   "metadata": {},
   "outputs": [],
   "source": [
    "Result= Result.drop('Prediction', axis=1)\n",
    "Result = Result.drop('classprobabilities', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10774dac-5282-4dd4-8919-43a19033b9af",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(Result,table_name='result_table',if_exists='replace')\n",
    "df_Result =  DataFrame('result_table')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b454822-2ff4-4853-a972-6f64e80a9fd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_df = ROC(data = df_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": "d3055604-01b1-4c6a-afce-c4d380932f74",
   "metadata": {},
   "outputs": [],
   "source": [
    "auc = roc_df.result.get_values()[0][0]\n",
    "auc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4625d4f-b3bd-42a5-8ced-7689fbb65165",
   "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": "efab8bdf-db74-4cd0-a893-bfaa0ecb3856",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Conclusion</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus, with Bring Your Own Model capabilities of ClearScape Analytics we allow organizations to leverage third party and open-source models for scoring inside the Vantage Platform, providing enterprise-class scalability and operational stability for any number of users, applications, or volume of data.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13b1b67d-d548-473b-846f-841eadf9aed5",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Cleanup</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "189cc33d-fb76-492d-a9c6-4956c03a707f",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_drop_table('DBT_H2O_Models')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5ba4aa2-0c6e-470a-b7a0-5bf44de4ba89",
   "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'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b7fbb1d-1e6e-4c6e-9be4-2aaf7bcdf7fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_DiabetesPrediction_BYOM');\" \n",
    "#Takes 2 minutes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0393aa0d-7c9a-4cda-9db4-658834116838",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1f797a8-a09a-4f02-ba8d-bbc06404e325",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Resources </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:20px;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",
    "\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": "591a177b-f888-4c2b-90ee-e5ea2d1e4271",
   "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": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "245px"
   },
   "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
}