UseCases/Heart_Failure_Prediction/Heart_Failure_Prediction_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "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",
    "       Heart Failure prediction using teradataml\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",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p> \n",
    "    <center><img src=\"images/heart.webp\" width=\"350\"/></center>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Source: <a href = 'https://www.healthline.com/health/heart-disease/statistics#How-much-does-it-cost?'>Healthline</a></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>According to the CDC, the number of emergency room visits in 2017 for issues related to the heart and blood vessels was nearly 5 million. Plus, heart attacks are one of the most expensive conditions treated in U.S. hospitals - cardiac care costs an estimated $11.5 billion a year. Being able to identify these conditions in people before a heart attack can not only help patients save money, but also save their lives. With Teradata Vantage and ClearScape Analytics, users can better understand the full picture of their patient’s health. Doctors and physicians can have a better understanding of their health and predict whether heart failure is expected.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>It's estimated that by 2035, more than 45 percent of Americans are projected to have some form of cardiovascular disease, meaning that heart failure prediction is an issue that will need to be better predicated. With Teradata’s capabilities, machine learning can be useful in heart failure prediction as it can analyze large amounts of data from multiple sources and identify complex patterns that may be difficult for humans to recognize. This can potentially improve the accuracy of prediction models and help healthcare professionals identify patients who are at high risk for heart failure, allowing for earlier intervention and better outcomes.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Values</b></p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Comprehensive health predictions and a reduced number of false positive and false negative results.</li>\n",
    "    <li>Reduced cost to patients and hospitals caused by heart attacks.</li>\n",
    "    <li>Identify patterns and symptoms leading to heart failure to ensure early intervention.</li>\n",
    "    <li>Advanced research and development stemming from the results of the data and models produced.</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>\n",
    "\n",
    "\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.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Data:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    This is a simulated dataset based on real hospital administrative data for England called Hospital Episodes Statistics. Every public (National Health Service, NHS) hospital in the country must submit records for every admission; private hospitals also submit records for any NHS patients that they treat.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# system packages\n",
    "import sys\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "from teradataml import *\n",
    "from teradataml import valib\n",
    "\n",
    "# Dataset packages \n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from tqdm import tqdm\n",
    "from itertools import combinations\n",
    "from sklearn.metrics import ConfusionMatrixDisplay\n",
    "\n",
    "# plotting packages\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "sns.set(style = \"whitegrid\")\n",
    "\n",
    "%matplotlib inline\n",
    "configure.val_install_location = \"val\"\n",
    "display.max_rows = 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>1. Connect to Vantage</b>\n",
    "<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.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Heart_Failure_Prediction_Python.ipynb;' UPDATE FOR SESSION;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_HeartFailure_cloud');\"        # Takes 1 minute\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_HeartFailure_local');\"        # Takes 2 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "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,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\"        # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>2. Data Exploration</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "heart_failure = DataFrame(in_schema('DEMO_HeartFailure', 'heart_failure'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "print(heart_failure.shape)\n",
    "heart_failure"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<i><b>*Please scroll down to the end of the notebook for detailed column descriptions of the dataset.</b></i>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the dataset above, we have a total of 31 columns, and the 'death' column is our predicted column where 1 means the patient died, and 0 means they did not.\n",
    "<br><br>    \n",
    "Now, let's check the data for people who died and those who did not.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sample data for people who did not die\n",
    "print(\"No. of people who did not die:\", heart_failure[heart_failure.death == 0].shape[0])\n",
    "heart_failure[heart_failure.death == 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sample data for people who did die\n",
    "print(\"No. of people who did die:\", heart_failure[heart_failure.death == 1].shape[0])\n",
    "heart_failure[heart_failure.death == 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>2.1 Mortality Rate by Gender</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grp_gen = heart_failure.select(['gender','death']).groupby(['gender']).agg(['mean', 'count']).to_pandas()\n",
    "sns.barplot(x = 'gender', y = 'mean_death', data = grp_gen)\n",
    "plt.xticks(ticks = [0, 1], labels = ['male', 'female'])\n",
    "plt.title('Mortality rate by gender')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Based on the graph we see above, we conclude that gender does not appear to be a determining factor in mortality rates related to heart failure.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage's Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI, or many Python modules available such as plotly, seaborn, etc. We can perform all the calculations and pre-processing in Vantage, passing only the necessary information to visualization tools. This approach not only makes the calculations faster but also reduces the overall time due to less data movement between tools.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>2.2 Mortality Rate by Age</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grp_gen = heart_failure.select(['age','death']).groupby(['age']).agg(['mean', 'count'])\n",
    "\n",
    "figure = Figure(width=1500, height=600, heading=\"Mortality rate by age\")\n",
    "\n",
    "plot = grp_gen.plot(\n",
    "    x=grp_gen.age,\n",
    "    y=grp_gen.mean_death,\n",
    "    kind='bar',\n",
    "    figure=figure\n",
    ")\n",
    "\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can observe a noticeable pattern from the graph, revealing a positive correlation between age and mortality rates associated with heart failure.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>2.3 Correlation Matrix</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The corr() function returns the Sample Pearson product moment correlation coefficient for all non-null data point pairs of ColumnExpression.</p>\n",
    "<i><b>*The following cell might take upto 2 mins to run</b></i>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an empty correlation matrix\n",
    "correlation_matrix = pd.DataFrame(index=heart_failure.columns[1:], columns=heart_failure.columns[1:])\n",
    "\n",
    "for col1, col2 in combinations(heart_failure.columns[1:], 2):\n",
    "    correlation_value = heart_failure.assign(drop_columns = True, _corr = heart_failure[col1].corr(heart_failure[col2])).get_values()[0][0]\n",
    "\n",
    "    # Fill in the correlation matrix for both col1 -> col2 and col2 -> col1\n",
    "    correlation_matrix.loc[col1, col2] = correlation_value\n",
    "    correlation_matrix.loc[col2, col1] = correlation_value\n",
    "\n",
    "# Fill in the diagonal with 1 since it's the correlation of a column with itself\n",
    "correlation_matrix = correlation_matrix.fillna(1.0)\n",
    "\n",
    "# Convert all columns to float data type\n",
    "correlation_matrix = correlation_matrix.astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI, or many Python modules available such as plotly, seaborn, etc. We can perform all the calculations and pre-processing on Vantage, passing only the necessary information to visualization tools. This approach not only makes the calculations faster but also reduces the time due to less data movement between tools.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Set figure size to 20 inches by 8 inches\n",
    "sns.set(rc = {\"figure.figsize\": (20, 8)})\n",
    "# Create a heatmap to visualize the correlation matrix\n",
    "ax = sns.heatmap(correlation_matrix, annot = False, cmap = 'coolwarm', vmin = -1, vmax = 1)\n",
    "\n",
    "# Set title and show plot\n",
    "plt.title('Multivariate Correlation Matrix')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Few observations from the correlation matrix above are:</p>\n",
    "<ul style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>We observe a positive correlation between Age and mortality.</li>\n",
    "    <li>We find a positive correlation between Cancer and metastatic cancer.</li>\n",
    "    <li>We notice a positive correlation between the number of prior appointments attended and the number of appointments missed in the previous year.</li>\n",
    "    <li>We see a negative correlation between our age and follow-up time, indicating that as we age, follow-up time tends to decrease, leading to more frequent hospital visits.</li>\n",
    "    <li>The negative correlation between our age and the number of prior appointments missed suggests that as we get older, we tend to miss fewer appointments, reflecting an increased seriousness about our health.</li>\n",
    "</ul>\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>While these correlations exist, we acknowledge that they may not be strong enough to justify removing any columns from our dataset.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>2.4 Distribution plots for numeric variables</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm import tqdm\n",
    "\n",
    "cols = [\"los\",\"age\",\"prior_appts_attended\",\"prior_dnas\",\"fu_time\"]\n",
    "\n",
    "fig, axes = subplots(nrows=2, ncols=3)\n",
    "\n",
    "# Plot the data\n",
    "for i in tqdm(range(len(cols))):\n",
    "    obj = Histogram(\n",
    "            data=heart_failure,\n",
    "            target_columns=cols[i],\n",
    "            method_type=\"STURGES\"\n",
    "    ).result\n",
    "\n",
    "    plot = obj.plot(\n",
    "        x=obj.MinValue,\n",
    "        y=obj.CountOfValues,\n",
    "        kind='bar',\n",
    "        figure=fig,\n",
    "        grid_linestyle='--',\n",
    "        grid_linewidth=0.5,\n",
    "        ylabel='Frequency',\n",
    "        legend=f'Distribuion of {cols[i]}',\n",
    "        ax=axes[i]\n",
    "    )\n",
    "\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<ul style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>We observe that the distribution of length of stay (LOS) indicates that the majority of hospitalizations are less than 20 days.</li>\n",
    "    <li>We notice that the distribution of age suggests that individuals aged 60 and above are more likely to be hospitalized.</li>\n",
    "    <li>We can see that the distribution of prior appointments attended suggests that most people attend fewer than 10 appointments.</li>\n",
    "    <li>We find that the distribution of prior missed appointments (prior DNAs) indicates that the majority of people do not miss appointments.</li>\n",
    "    <li>We see that the distribution of follow-up time suggests that a large number of individuals have follow-up time less than 100 days.</li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>3. Data Preparation</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We classify the columns into an ID column, numerical, categorical, and binary ones.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_x = [\"los\",\"age\",\"prior_appts_attended\",\"prior_dnas\",\"fu_time\"]\n",
    "cat_x = [\"gender\", \"quintile\",\"ethnicgroup\"]\n",
    "bin_x = [\"death\", \"cancer\", \"cabg\",\"crt\", \"defib\",\"dementia\", \"diabetes\",\"hypertension\", \"ihd\",\n",
    "         \"mental_health\", \"arrhythmias\", \"copd\", \"obesity\",\"pvd\", \"renal_disease\", \"valvular_disease\",\n",
    "         \"metastatic_cancer\", \"pacemaker\", \"pneumonia\", \"pci\", \"stroke\", \"senile\"]\n",
    "idcol = [\"id\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We find one-hot encoding useful when we need to re-express a categorical data element as one or more numeric data elements. This involves creating a binary numeric field for each categorical data value.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1 - male, 2 - female\n",
    "values1 = {1: \"Gender\"}\n",
    "dummy1 = OneHotEncoder(values = values1, columns = \"gender\")\n",
    "\n",
    "# quintile (socio-economic status for patient's neighbourhood, from 1 (most affluent) to 5 (poorest))\n",
    "values2 = {1: \"q_richest\", 2: \"q_rich\", 3: \"q_average\", 4: \"q_poor\", 5: \"q_poorest\"}\n",
    "dummy2 = OneHotEncoder(values = values2, columns = \"quintile\")\n",
    "\n",
    "# 1 - White, 2 - Black, 3 - Indian Subcontinent, 8 - Not Known, 9 - Other\n",
    "values3 = {1: \"White\", 2: \"Black\", 3: \"Indian_Subcontinent\", 8: \"Not_Known\", 9:\"Other\"}\n",
    "dummy3 = OneHotEncoder(values = values3, columns = \"ethnicgroup\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use FillNa to perform missing value/null replacement transformations. We apply Z-Score to transform each column value into the number of standard deviations from the mean value of the column.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fn = FillNa(\n",
    "    style = \"mode\",\n",
    "    columns = num_x\n",
    ")\n",
    "zs = ZScore(\n",
    "    columns = num_x,\n",
    "    out_columns = num_x, \n",
    "    fillna = fn\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We keep the other variables that do not need transformation.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "retain = Retain(columns = bin_x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>ClearScape Analytics provides Data Scientists and other users with over 50 advanced analytic functions built directly in the Advanced SQL Engine, which is a core capability of Teradata Vantage. These functions support the entire data science process, including exploratory data analysis, data preparation and feature engineering, hypothesis testing, as well as statistical and machine learning model building and scoring.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Transform function reads a teradataml DataFrame and produces an output containing transformed columns.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process the transformation\n",
    "df_transformed = valib.Transform(\n",
    "    data = heart_failure, \n",
    "    zscore = zs, \n",
    "    one_hot_encode = [dummy1, dummy2, dummy3],\n",
    "    retain = retain,\n",
    "    index_columns = idcol,\n",
    "    key_columns = idcol\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We save the transformed dataframe into a table <b>heart_failure_clean</b>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_transformed.result.to_sql(\n",
    "    \"heart_failure_clean\",\n",
    "    primary_index = \"id\",\n",
    "    if_exists = \"replace\"\n",
    ")\n",
    "\n",
    "df_transformed.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We split the data in training and testing datasets in 75:25 ratio.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "TrainTestSplit_out = TrainTestSplit(\n",
    "    data = DataFrame('heart_failure_clean'),\n",
    "    id_column = \"id\",\n",
    "    train_size = 0.75,\n",
    "    test_size = 0.25,\n",
    "    seed = 42\n",
    ")\n",
    "\n",
    "df_train = TrainTestSplit_out.result[TrainTestSplit_out.result['TD_IsTrainRow'] == 1].drop(['TD_IsTrainRow'], axis = 1)\n",
    "df_test = TrainTestSplit_out.result[TrainTestSplit_out.result['TD_IsTrainRow'] == 0].drop(['TD_IsTrainRow'], axis = 1)\n",
    "\n",
    "copy_to_sql(df_train, table_name = 'heart_failure_train', if_exists = 'replace')\n",
    "copy_to_sql(df_test, table_name = 'heart_failure_test', if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Training Set = \" + str(df_train.shape[0]) + \" | Testing Set = \" + str(df_test.shape[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>4. Model Training</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>The TD_GLM function as a generalized linear model (GLM) for performing regression and classification analyses on data sets, where the response follows an exponential or binomial family distribution.\n",
    "    <br>\n",
    "    <br>\n",
    "    When using the function, we should provide only numeric features. We need to convert the categorical features to numeric values before training. Additionally, during training, TD_GLM function skips the rows with missing (null) values.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import GLM, TDGLMPredict\n",
    "\n",
    "glm_model = GLM(\n",
    "    data = df_train,\n",
    "    input_columns = '2:38', \n",
    "    response_column = 'death',\n",
    "    family = 'Binomial',\n",
    "    batch_size = 50,\n",
    "    iter_max = 100,\n",
    "    lambda1 = 0.02,\n",
    "    alpha = 0.05,\n",
    "    iter_num_no_change = 5,\n",
    "    intercept = True,\n",
    "    learning_rate = 'optimal',\n",
    "    initial_eta = 0.05,\n",
    "    local_sgd_iterations = 20\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "glm_model.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage's Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI, or many Python modules available such as plotly, seaborn, etc. We can perform all the calculations and pre-processing on Vantage, passing only the necessary information to visualization tools. This approach not only makes the calculations faster but also reduces the time due to less data movement between tools.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_imp = glm_model.result[glm_model.result['attribute'] > 0].sort('estimate', ascending = False).to_pandas()\n",
    "\n",
    "# Specify figure size\n",
    "fig, ax = plt.subplots(figsize = (10, 8))\n",
    "\n",
    "# Use ax.barh() for horizontal bar chart\n",
    "ax.barh(feat_imp['predictor'], feat_imp['estimate'], edgecolor = 'red')\n",
    "\n",
    "# Add text labels on right of the bars\n",
    "for x, y in zip(feat_imp['estimate'], feat_imp['predictor']):\n",
    "    ax.text(x, y, str(round(x, 2)), ha = 'left', va = 'center')\n",
    "\n",
    "# Set y-axis label\n",
    "ax.set_xlabel('Estimate')\n",
    "\n",
    "plt.title('Feature importance')\n",
    "\n",
    "plt.show()\n",
    "\n",
    "from IPython.display import display, HTML\n",
    "\n",
    "feat1, feat2, feat3, feat4 = (\n",
    "    list(feat_imp.iloc[:2].predictor)[0],\n",
    "    list(feat_imp.iloc[:2].predictor)[1],\n",
    "    list(feat_imp.iloc[-2:].predictor)[0],\n",
    "    list(feat_imp.iloc[-2:].predictor)[1],\n",
    ")\n",
    "\n",
    "html_content = f\"\"\"\n",
    "<p style='font-size:16px;font-family:Arial'>\n",
    "The feature importances we displayed above indicate that <b>{feat1}, {feat2}, {feat3}, and {feat4}</b> are significant factors in predicting heart failure deaths.\n",
    "<br>\n",
    "Note that the top ones (negative coefficients) negatively affect the target variable, and that the bottom ones (positive coefficients) positively affect the target variable.\n",
    "</p>\n",
    "\"\"\"\n",
    "\n",
    "display(HTML(html_content))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>5. Model Validation</b>\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "We, using the TD_GLMPredict function, predict target values (regression) and class labels (classification) for test data using a GLM model trained by the GLM function.\n",
    "<br>\n",
    "<br>\n",
    "Similar to GLM, we standardize input features, such as using ScaleFit and ScaleTransform, before using them in the function. The function takes only numeric features. We must convert the categorical features to numeric values before prediction.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "glm_prediction = glm_model.predict(\n",
    "    newdata = df_test,\n",
    "    id_column = 'id',\n",
    "    accumulate = 'death',\n",
    "    output_prob = True,\n",
    "    output_responses = ['0', '1']\n",
    ")\n",
    "\n",
    "out = glm_prediction.result.assign(prediction = glm_prediction.result.prediction.cast(type_ = BYTEINT))\n",
    "out = out.assign(prediction = out.prediction.cast(type_ = VARCHAR(2)))\n",
    "out = out.assign(death = out.death.cast(type_ = VARCHAR(2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>In the above result, we consider the column <b>death</b> as our ground truth, <b>prediction</b> as our predicted output, and <b>(prob_0, prob_1)</b> as the probabilities of the output class.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>ClassificationEvaluator() function evaluates and emits various metrics of classification model based on its predictions on the data. Apart from accuracy, the secondary output data returns micro, macro, and weighted-averaged metrics of precision, recall, and F1-score values.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>This is a powerful function, and doesn't move data outside Vantage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "ClassificationEvaluator_obj = ClassificationEvaluator(\n",
    "    data = out,\n",
    "    observation_column = 'death',\n",
    "    prediction_column = 'prediction',\n",
    "    labels = ['0', '1']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ClassificationEvaluator_obj.output_data.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>We consider recall more important when <b>Overlooked Cases (False Negatives)</b> are more costly than <b>False Alarms (False Positives)</b>. Because not “capturing” even one case of heart failure could result in death, our models should place emphasis on the recall score. It is far preferable for us to not “miss” anyone with heart failure, even if that means “flagging” some patients as having heart failure who actually do not have the disease.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Let's consider one example. Here we check for patient number 856.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "heart_failure[heart_failure['id'] == 856]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Based on the data provided, the individual in question has hypertension, arrhythmia, and an age of 98. We have identified these features as significant predictors of mortality by our model. Therefore, there was a high probability of death for this patient, which is further supported by the outcome indicating death.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>6. Visualize the results</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI, or many Python modules available such as plotly, seaborn, etc. We can perform all the calculations and pre-processing on Vantage, passing only the necessary information to visualization tools. This approach not only makes the calculations faster but also reduces the time due to less data movement between tools.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute confusion matrix\n",
    "cm = ClassificationEvaluator_obj.result.get(['CLASS_1', 'CLASS_2']).get_values().T\n",
    "\n",
    "# Plot confusion matrix\n",
    "disp = ConfusionMatrixDisplay(confusion_matrix = cm, display_labels = ['No Death', 'Death'])\n",
    "fig, ax = plt.subplots(figsize = (8, 8))\n",
    "disp.plot(ax = ax, cmap = 'Blues', colorbar = True)\n",
    "\n",
    "# Add labels and annotations\n",
    "plt.title('GLM Confusion Matrix')\n",
    "plt.xlabel('Predicted Label')\n",
    "plt.ylabel('True Label')\n",
    "plt.xticks(ticks = [0, 1], labels = ['No Death', 'Death'])\n",
    "plt.yticks(ticks = [0, 1], labels = ['No Death', 'Death'])\n",
    "\n",
    "# Add text to the plot to show the actual values of the confusion matrix\n",
    "for i in range(cm.shape[0]):\n",
    "    for j in range(cm.shape[1]):\n",
    "        plt.text(j, i, f'{cm[i, j]}', ha = 'center', va = 'center', color = 'white' if cm[i, j] > cm.max()/1.4 else 'black')\n",
    "\n",
    "# Remove grid lines\n",
    "ax.grid(False)\n",
    "\n",
    "# Show the plot\n",
    "plt.show()\n",
    "\n",
    "print(f'''\n",
    "This means that out of all the actual non-death cases ({cm[0][0] + cm[0][1]}),\n",
    "{round(cm[0][0]/(cm[0][0] + cm[0][1])*100, 2)}% were correctly classified as non-death, while\n",
    "{round(cm[0][1]/(cm[0][0] + cm[0][1])*100, 2)}% were incorrectly classified as death.\n",
    "Similarly, out of all the actual death cases ({cm[1][0] + cm[1][1]}),\n",
    "{round(cm[1][1]/(cm[1][0] + cm[1][1])*100, 2)}% were correctly classified as death, while\n",
    "{round(cm[1][0]/(cm[1][0] + cm[1][1])*100, 2)}% were incorrectly classified as non-death.\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demonstration, we have illustrated a simplified - but complete - overview of how we can implement a typical machine learning workflow completely inside the database using Vantage. This allows us to leverage Vantage's operational scale, power, and stability.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>7. Cleanup</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We need to clean up our work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['heart_failure_clean', 'heart_failure_train', 'heart_failure_test']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name = table)\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "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,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_HeartFailure');\"        # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<b style = 'font-size:20px;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",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Filters:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Industry:</b> Healthcare</li>\n",
    "    <li><b>Functionality:</b> Machine Learning</li>\n",
    "    <li><b>Use Case:</b> Prediction Analysis</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><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><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><a href='https://www.teradata.com/Blogs/Forecasting-COVID-19-Using-Teradata-Vantage'>Forecasting COVID-19 Using Teradata Vantage</a></li>\n",
    "</ul>\n",
    "\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Dataset:</b>\n",
    "\n",
    "- `id`: patient id\n",
    "- `death`: If the patient is deceased(boolean)\n",
    "- `los`: hospital length of stay (in nights)\n",
    "- `age`: age of the patient (in years)\n",
    "- `gender`: gender of the patient (1-male, 2-female)\n",
    "- `cancer`: If the patient has cancer (boolean)\n",
    "- `cabg`: If the patient has gone through previous heart bypass i.e. Coronary Artery Bypass Graft procedure (boolean)\n",
    "- `crt`: If the patient has gone through Cardiac Resynchronization Therapy i.e. a device - a treatment for heart failure(boolean)\n",
    "- `defib`: If the patient has defibrillator implanted (boolean)\n",
    "- `dementia`: If the patient has dementia (boolean)\n",
    "- `diabetes`: If the patient has diabetes (boolean)\n",
    "- `hypertension`: If the patient has hypertension (boolean)\n",
    "- `ihd`: If the patient has Ischemic Heart Disease (boolean)\n",
    "- `mental_health`: If the patient has been diagnosed with mental health issues (boolean)\n",
    "- `arrhythmias`: If the patient has arrhythmia (boolean)\n",
    "- `copd`: If the patient has Chronic Obstructive Pulmonary Disease (boolean)\n",
    "- `obesity`: If the patient has obesity (boolean)\n",
    "- `pvd`: If the patient has Peripheral Vascular Disease (boolean)\n",
    "- `renal_disease`: If the patient has Renal Disease (boolean)\n",
    "- `valvular_disease`: If the patient has Valvular Disease (boolean)\n",
    "- `metastatic_cancer`: If the patient has Metastatic Cancer (boolean)\n",
    "- `pacemaker`: If the patient has pacemaker (boolean)\n",
    "- `pneumonia`: If the patient has pneumonia (boolean)\n",
    "- `prior_appts_attended`: Number of outpatient appointments attended in the previous year\n",
    "- `prior_dnas`: Number of outpatient appointments missed in the previous year\n",
    "- `pci`: If the patient has gone though Percutaneous Coronary Intervention procedure (boolean)\n",
    "- `stroke`: History of stroke\n",
    "- `senile`: If the patient has Senile amyloidosis (SSA) (boolean)\n",
    "- `quintile`: Socio-economic status for patient's neighborhood, from 1 (most affluent) to 5 (poorest)\n",
    "- `ethnicgroup`: 1 - White, 2 - Black, 3 - Indian Subcontinent, 8 - Not Known, 9 - Other \n",
    "- `fu_time`: Follow-up time, i.e. time in days since admission to hospital"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023,2024. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}