UseCases/Parkinsons_Disease_Prediction/Parkinsons_Disease_Prediction_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "badff87f-8d63-4dfe-a1fa-0db73fcbcc1a",
   "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",
    "       Parkinson's Disease 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>\n",
    "<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'>Research shows that 89 percent of people with Parkinson’s disease (PD) experience speech and voice disorders, including soft, monotone, breathy and hoarse voice and uncertain articulation. As a result, people with PD report they are less likely to participate in conversation or have confidence in social settings than healthy individuals in their age group.\n",
    "<br>\n",
    "<br>    \n",
    "Speech disorders can progressively diminish quality of life for a person with PD. The earlier a person receives a baseline speech evaluation and speech therapy, the more likely he or she will be able to maintain communication skills as the disease progresses. Communication is a key element in quality of life and positive self-concept and confidence for people with PD.\n",
    "<br>\n",
    "<br>    \n",
    "Hence as a consultant, we are approached by an organization to detect Parkinson's Disease at an early stage. We are not showcasing a complete Data Science Usecase but we are trying to show how the Teradata In Database functions can be used for Model training and scoring and comparing the performance of 2 models. The data we are using is sample data and the results and predictions may not be entirely accurate.</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 is composed of a range of biomedical voice measurements from different people with Parkinson's disease (PD). Each column in the table is a particular voice measure, and each row corresponds one of 195 voice recording from these individuals. Various speech signal processing algorithms including Time Frequency Features, Mel Frequency Cepstral Coefficients (MFCCs), Wavelet Transform based Features, Vocal Fold Features and TWQT features have been applied to the speech recordings of Parkinson's Disease (PD) patients to extract clinically useful information for PD assessment. The main aim of the data is to discriminate healthy people from those with PD, according to \"status\" column which is set to 0 for healthy and 1 for PD.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://archive.ics.uci.edu/ml/datasets/parkinsons'>Link to the dataset</a>: Max A. Little, Patrick E. McSharry, Eric J. Hunter, Lorraine O. Ramig (2008), 'Suitability of dysphonia measurements for telemonitoring of Parkinson's disease', IEEE Transactions on Biomedical Engineering (to appear).</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66077c1a-bd61-4393-83d2-177631ebbaec",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Contents:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Configuring the Environment</li>\n",
    "    <li>Initiate a connection to Vantage</li>\n",
    "    <li>Analyze the raw data set</li>\n",
    "    <li>Train and Test a Decision Forest Model</li>\n",
    "        <ul>\n",
    "            <li>4.1 Train and Test split using SAMPLE. Splitting the dataset in 80:20 ratio for Train and Test respectively</li>\n",
    "            <li>4.2 Train a Model</li> \n",
    "                <ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                    <li style = 'font-size:16px;font-family:Arial;color:#00233C' >Using the DecisionForest and DecisonForestPredict In Database function to predict if the person can have Parkinson's Disease or not. So there are only 2 responses '0' and '1'.</li>\n",
    "                    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Using the GLM and TDGLMPredict In Database function to predict if the person can have Parkinson's Disease or not. So there are only 2 responses '0' and '1'.</li>\n",
    "            </ol>\n",
    "            <li>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>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29b025bb-c63d-47e7-8de7-ba08db7da5ac",
   "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 the Vantage.</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55b5391d-d20a-4b98-8677-ea1542dca22c",
   "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": "violent-bunny",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import getpass\n",
    "import pandas as pd\n",
    "\n",
    "from teradataml.dataframe.dataframe import DataFrame\n",
    "from teradataml import *\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "display.max_rows=5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec1a9abf-42e4-4f76-804f-b04cc1af26f8",
   "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": "19df8f96-127c-4288-93ab-337ca87efb93",
   "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": "21b2ce38-359d-4364-96ae-ef06efe89631",
   "metadata": {},
   "outputs": [],
   "source": [
    "execute_sql('''SET query_band='DEMO=Parkinsons_Disease_Prediction_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1704cdf4-3849-443b-9c2b-516034b1b688",
   "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.  You have the option of either running the demo using foreign tables to access the data without using any storage on your environment or downloading the data to local storage which may yield somewhat faster execution, but there could be considerations of available storage. Here we are only creating local databases and tables as there are 755 columns in table which will be faster in local tables.</p> \n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>“Note:  The early part of this demo will be slow because we are starting out with so many columns. The strategy of this demo is to eliminate irrelevant columns so we can focus on the ones that are the best predictors of the disease and as a by-product, get better performance.\"</b></p>    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "996607e5-fd2b-4b80-9ce7-e6dd7ececc76",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_ParkinsonsDisease_local');\"\n",
    " # Takes about 2 minutes\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d562967-cc8f-46c9-8342-cf2b64a1842d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55407bf6-80d4-438e-9533-05ce5ad3b40a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hundred-reliance",
   "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",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create a DataFrame to get the data from the table created.</p>\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33cb89ae-7dfe-4b4c-9b6a-f6b2d057a59b",
   "metadata": {},
   "outputs": [],
   "source": [
    "speech_features = DataFrame(in_schema('DEMO_ParkinsonsDisease','Speech_Features'))\n",
    "speech_features"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cae81b57-f9c6-4a61-ad0e-dd6f508090d6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There are more than 750 different features of the speech recordings which are used for analysis. The \"CLASS\" column which is the rightmost column of the answer set above(please scroll to the right), indicates whether the person has Parkinson's Disease(1) or DOES NOT have Parkinson's Disease(0)</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "innocent-remove",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. 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;color:#00233C'>\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",
    "</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6929ee1e-b506-4e28-b953-b9eb253873d6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Feature engineering transform functions encapsulate variable transformations during the training phase so you can chain them to create a pipeline for operationalization. We used the RandomProjectionMinComponents to find the minimum components required. BY using this we were able to reduce the number of columns from 753 to 318.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Further we use the nameFit and nameTransform functions. Each nameFit function outputs a table to input to the nameTransform function as FitTable. For example, ScaleFit outputs a FitTable for ScaleTransform. We are using the mean ScaleMethod for this case.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using the STD ScaleMethod the ScaleFit function calculates the mean values of each feature used and the output of this ScaleFit function is used in the ScaleTransform function as the fit table. ScaleFit() function outputs statistics to input to ScaleTransform() function, which scales specified input DataFrame columns.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71303ed4-142b-4c09-b0a9-e30a6a2b6729",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ScaleFit, ScaleTransform\n",
    "\n",
    "sf_fit = ScaleFit(data = speech_features, scale_method = 'STD',\n",
    "                     target_columns = ['2:318'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e052c49f-9af5-4b10-96ee-6f82417e2d7c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Transform() function applies numeric transformations to input columns, using Fit() output. Here the output of the ScaleFit function is used by the ScaleTransform to apply the numeric transformations to the input columns. </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: There may be some Teradata Database warnings for the ScaleTransform functions, but these are just warnings which can be ignored.</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3a34239-7971-4325-a739-bd7a8d527721",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sf_trns = ScaleTransform(data = speech_features, object = sf_fit.output, accumulate = ['\"id\"','\"class\"'])\n",
    "sf_trns.result.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0e62e03-93c7-40b1-a7d2-a9c1215d974d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Using opensource Library to find out feature importance </b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a5a7399-b53f-4172-a3df-a6e38f4a6c47",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Using opensource Library to find the feature importance\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "\n",
    "#to ignore warning\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "warnings.simplefilter(action='ignore', category=DeprecationWarning)\n",
    "warnings.simplefilter(action='ignore', category=RuntimeWarning)\n",
    "#for visualization\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "sns.set(color_codes=True)\n",
    "%matplotlib inline\n",
    "#model\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.metrics import f1_score, recall_score, confusion_matrix,classification_report\n",
    "from sklearn.metrics import precision_recall_curve, precision_score, accuracy_score\n",
    "from sklearn.model_selection import cross_validate\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "##converting to panda dataframe\n",
    "pd_parkinson_df = sf_trns.result.to_pandas()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac2f83b1-f335-428e-93bf-a5ed3136698a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below code is a function defined to implement multiple models. Since we will be using the same code to check the feature importance and also to verify which features should be finally used for the analysis.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33764d47-5e7f-4d35-82d9-05fcb26d5d12",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define one common function to implement multiple model \n",
    "def model_evaluation(model_name, model, input_rel):    \n",
    "    start = time.time()  # note the start time \n",
    "    model.fit(X_train, y_train)\n",
    "    \n",
    "    #prediction\n",
    "    ypred = model.predict(X_test)\n",
    "    \n",
    "    #model name\n",
    "    print(model_name)\n",
    "    \n",
    "    #confusion matrix\n",
    "    print(confusion_matrix (y_test, ypred))\n",
    "    \n",
    "    #classifiction matrix\n",
    "    #print(classification_report(y_test,ypred))\n",
    "    \n",
    "    trianing_score = model.score(X_train, y_train)\n",
    "    acc_score = accuracy_score(y_test, ypred)\n",
    "    \n",
    "    recall = recall_score(y_test, ypred)\n",
    "    precision = precision_score(y_test, ypred)\n",
    "    f1 = f1_score(y_test,ypred)\n",
    "    \n",
    "    end = time.time()  #note the end time\n",
    "    duration = end - start  # calculate the total duration\n",
    "    \n",
    "    #Store the accuracy results for each model in a dataframe for final comparison\n",
    "    tempResultsDf = pd.DataFrame({'Model_Name':[model_name], 'trianing_score': trianing_score, \n",
    "                              'accuracy': acc_score, 'precision': precision, \n",
    "                              'recall': recall, 'f1-score': f1, 'duration':duration })  \n",
    "        \n",
    "    resultsDf = pd.concat([input_rel, tempResultsDf])    \n",
    "    resultsDf1 = resultsDf[['Model_Name', 'trianing_score', 'accuracy', 'precision', 'recall', 'f1-score', 'duration']]\n",
    "    return resultsDf1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dffc7d5-7a23-455c-af38-f87e8c16600c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Below we are splitting the train and test dataset for the model evaluation.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "099ce63e-26ac-480f-8026-25c3c25befa3",
   "metadata": {},
   "outputs": [],
   "source": [
    "X = pd_parkinson_df.drop(['class', 'id'], axis=1)\n",
    "y = pd_parkinson_df['class']\n",
    "\n",
    "#Split the data into training and test set [80% training and 20% for test]\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size = 0.20, random_state= 45) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d262b998-becb-404a-89f4-afbc9a4e9e9f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Logistic Regression is used to create the model output which will be used further to check the feature importance.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Logistic Regression is a Machine Learning classification algorithm that is used to predict the probability of a categorical dependent variable. In logistic regression, the dependent variable is a binary variable that contains data coded as 1 (yes, success, etc.) or 0 (no, failure, etc.). This is suitable here as we have to determine if the patient has Parkinson or Does not have Parkinson.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dad55712-cc71-4dbd-bd83-b7304f246eff",
   "metadata": {},
   "outputs": [],
   "source": [
    "resultsDf2 = pd.DataFrame()\n",
    "result_out = pd.DataFrame()\n",
    "lr = LogisticRegression(C=0.1, penalty='l2', solver= 'liblinear', random_state=1)\n",
    "result_out = model_evaluation('LogisticRegression', lr, resultsDf2)\n",
    "result_out"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de005694-c60d-4fbc-a663-e3e379b370f3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we can see that the accuracy for this model is 84% and the f1-score is 89%.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Below we try to get the important features using the logistic regression coefficient and plot the graph for features based on their importance</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a29bec9-404c-40c9-a6ed-82eaa3ebb992",
   "metadata": {},
   "outputs": [],
   "source": [
    "importances = pd.DataFrame(data={\n",
    "    'Attribute': X_train.columns,\n",
    "    'Importance': lr.coef_[0]\n",
    "})\n",
    "importances = importances.sort_values(by='Importance', ascending=False)\n",
    "importances.to_csv(\"logistic_feature_imp.csv\")\n",
    "plt.figure(figsize=(100,30))\n",
    "plt.bar(x=importances['Attribute'], height=importances['Importance'], color='#087E8B')\n",
    "plt.title('Feature importances obtained from coefficients', size=20)\n",
    "plt.xticks(rotation='vertical')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6953729e-4e0c-4c61-974b-53b212de9d5a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the important features. Since we have a lot of features (318), we have also created a logistic_feature_imp.csv, which shows the exact values for all the features. </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As seen in the graph and output file apart from the top 10-15 variables, rest of the variable co-efficients are very close to zero. So we will consider only the top 12 [those feature co-eff >= 0.3 and <= -0.3] variables for checking the accuracy.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c2ae87e-2d74-4fe8-9005-34be8c92fe07",
   "metadata": {},
   "outputs": [],
   "source": [
    "###Since except top 10 to 20 variables, rest of the variable co-efficienct is close to zero. \n",
    "###So we will be taking Top 12 [those feature co-eff >= 0.3 and <= -0.3] variables and checking the accuracy.\n",
    "pd_parkinson_new_df = pd_parkinson_df[[\"id\",\n",
    "\"DFA\",\n",
    "\"std_delta_delta_log_energy\",\n",
    "\"std_7th_delta\",\n",
    "\"std_delta_log_energy\",\n",
    "\"std_7th_delta_delta\",\n",
    "\"GNE_mean\",\n",
    "\"mean_MFCC_2nd_coef\",                          \n",
    "\"mean_2nd_delta\",\n",
    "\"mean_MFCC_5th_coef\",\n",
    "\"std_MFCC_3rd_coef\",\n",
    "\"mean_MFCC_6th_coef\",\n",
    "\"GNE_SNR_TKEO\", \"class\"]]\n",
    "\n",
    "pd_parkinson_new_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a134bfb7-4648-4533-8555-207dc70c3e63",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>With these 12 features we then check the corelation of these features. </p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32201303-7381-44de-a568-db589199f7ab",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#get the correlation matrix\n",
    "corr = pd_parkinson_new_df.corr()\n",
    "\n",
    "##plot heatmap\n",
    "plt.figure(figsize=(20,10))\n",
    "plt.title('Correlation Matrix')\n",
    "sns.heatmap(corr, annot = True);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57a96789-f201-48f9-9f37-23194cda4a35",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As seen in the above corelation matrix , we can see that there are 2 features where the corelation is above 92%. The intersection formed by 5th and 6th feature from the top on the Y axis with the 3rd and 4th feature respectively from left on the X axis, we can observe that the corelation is 0.92 and 0.95. So now we check the model accuracy with 12 features and then removing the corelated features we can again check the accuracy with 10 features. </p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2ac5261-8334-4d33-b62f-9e10081fe604",
   "metadata": {},
   "outputs": [],
   "source": [
    "X = pd_parkinson_new_df.drop(['class', 'id'], axis=1)\n",
    "y = pd_parkinson_new_df['class']\n",
    "\n",
    "#Split the data into training and test set [80% training and 20% for test]\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size = 0.20, random_state= 45) \n",
    "resultsDf2 = pd.DataFrame()\n",
    "result_out = pd.DataFrame()\n",
    "lr = LogisticRegression(C=0.1, penalty='l2', solver= 'liblinear', random_state=1)\n",
    "result_out = model_evaluation('LogisticRegression', lr, resultsDf2)\n",
    "result_out"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28905e72-7b85-4d23-9b15-4e9fff93b8b3",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Check accuracy of the model after removing the corelated features as below. </p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cc6478a-4e9a-4740-ae63-74482a350635",
   "metadata": {},
   "outputs": [],
   "source": [
    "X = pd_parkinson_new_df.drop(['class', 'id', 'std_7th_delta_delta', 'std_delta_log_energy'], axis=1)\n",
    "y = pd_parkinson_new_df['class']\n",
    "\n",
    "#Split the data into training and test set [80% training and 20% for test]\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size = 0.20, random_state= 45) \n",
    "resultsDf2 = pd.DataFrame()\n",
    "result_out = pd.DataFrame()\n",
    "lr = LogisticRegression(C=0.1, penalty='l2', solver= 'liblinear', random_state=1)\n",
    "result_out = model_evaluation('LogisticRegression', lr, resultsDf2)\n",
    "result_out"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e8a0bf2-2510-4d46-9a20-407acdd3d579",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note: First Model is with 319 variables and second model with 12 important variables and third with 10 important variables taken from 319 variable. So we observed from model metrics that there is not much difference in Model accuracy.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>So now in the below evaluation we will be using only these 10 important features.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6186a1f-470a-4d3a-9872-b51868224848",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Train and Test split using SAMPLE</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create the dataset with only these 10 variables along with the id and class columns which will be used as the id and target variables for the below evaluations.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using 80:20 split data to create the training and testing dataset.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04ce2228-53ba-49ed-a369-e35b0254e40a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_samples = sf_trns.result.sample(frac = [0.2, 0.8])[[\"id\",\n",
    "\"DFA\",\n",
    "\"std_delta_delta_log_energy\",\n",
    "\"std_7th_delta\",\n",
    "\"GNE_mean\",\n",
    "\"mean_MFCC_2nd_coef\",                          \n",
    "\"mean_2nd_delta\",\n",
    "\"mean_MFCC_5th_coef\",\n",
    "\"std_MFCC_3rd_coef\",\n",
    "\"mean_MFCC_6th_coef\",\n",
    "\"GNE_SNR_TKEO\",\"sampleid\", \"class\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b5a27f2-fb1b-4cbe-8cb6-ff87b5bc7e5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_speech_features_train = tdf_samples[tdf_samples['sampleid'] == 2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e9f7a24-be41-469e-bb82-26ec921d2b21",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_speech_features_test = tdf_samples[tdf_samples['sampleid'] == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d41eef90-41e8-46a1-9229-ae88a0fc8e43",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(tdf_samples[tdf_samples['sampleid'] == 2], table_name = 'pd_speech_features_train', schema_name = 'demo_user',\n",
    "            if_exists = 'replace')\n",
    "train_df_data = DataFrame.from_query('SELECT \"class\", COUNT(*) as cnt FROM pd_speech_features_train GROUP BY \"class\";')\n",
    "train_df_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c19acdc-a36f-4d7c-94ba-491b1a66ce7a",
   "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 Parkinson’s</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27c0f824-aa92-4559-8584-efc7c72a1a1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(tdf_samples[tdf_samples['sampleid'] == 1], table_name = 'pd_speech_features_test', schema_name = 'demo_user', \n",
    "            if_exists = 'replace')\n",
    "test_df_data = DataFrame.from_query('SELECT \"class\", COUNT(*) as cnt FROM pd_speech_features_test GROUP BY \"class\";')\n",
    "test_df_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56c08b07-138e-4837-83eb-5ead303908d2",
   "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 test the model – class 1 has Parkinson’s</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bigger-reaction",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Decision Tree Model</b></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.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/Enterprise/Teradata-Package-for-Python-Function-Reference-17.20/teradataml-Analytic-Database-SQL-Engine-Analytic-Functions/Supported-on-Database-Versions-16.20.xx-17.00.xx-17.05.xx/DecisionForestPredict'>DecisionForest</a> is an ensemble algorithm used for classification and regression predictive modelling 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>InputColumns; list or range of columns used as features (we used an ordinal reference of columns 2:753)</li>\n",
    "        <li>ResponseColumn; the dependent or target value (we used “class”, the first column)</li>\n",
    "        <li>TreeType; either CLASSIFICATION or REGRESSION</li>\n",
    "    <li>Other hyperparameter values detailed in the documentation</li>\n",
    "        </ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06f022e9-c7f1-4bef-bcac-d5d19e2f48b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''Create multiset table DF_train as (\n",
    "SELECT * FROM TD_DecisionForest (\n",
    "ON pd_speech_features_train AS INPUTTABLE partition by ANY\n",
    "USING\n",
    "  ResponseColumn('\"class\"')\n",
    "InputColumns('id', 'DFA', 'std_delta_delta_log_energy', 'std_7th_delta', 'GNE_mean', 'mean_MFCC_2nd_coef', \n",
    "      'mean_2nd_delta', 'mean_MFCC_5th_coef', 'std_MFCC_3rd_coef', 'mean_MFCC_6th_coef', 'GNE_SNR_TKEO', '\"sampleid\"' )\n",
    "MaxDepth(5)\n",
    "MinNodeSize(1)\n",
    "NumTrees(4)\n",
    "ModelType('CLASSIFICATION')\n",
    "Seed(2)\n",
    "Mtry(3)\n",
    "MtrySeed(1)\n",
    ") AS dt\n",
    ") with data;\n",
    "'''\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('DF_train')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36a6f3b3-c47b-4010-bc18-721a5d1b9c60",
   "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 explanation 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": "5bb1c7c6-b3e0-49a1-8823-c3c4a7ab3fef",
   "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 - 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/search/all?query=TD_ClassificationEvaluator&content-lang=en-US'>TD_ClassificationEvaluator</a> SQL Function.</p>\n",
    "\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Execute <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Model-Scoring-Functions/DecisionForestPredict'>DecisionForestPredict</a> using the model built above</li>\n",
    "    <li>Execute <a href = 'https://docs.teradata.com/search/all?query=TD_ClassificationEvaluator&content-lang=en-US'>TD_ClassificationEvaluator</a> and pass the actual classification and the predicted value</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08db7025-07f6-4d9a-bff5-b6a9ab7019fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "Create multiset table DF_Predict as (\n",
    "SELECT * FROM TD_DecisionForestPredict (\n",
    "ON pd_speech_features_test AS InputTable PARTITION BY ANY\n",
    "ON DF_Train AS ModelTable DIMENSION\n",
    "USING\n",
    "   IDColumn ('id')\n",
    "        OutputProb ('true')\n",
    "        Responses ('0', '1')\n",
    "        Accumulate ('\"class\"')\n",
    ") AS dt) with data;'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('DF_Predict')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39d5c169-a296-439c-a799-1e0d5970df36",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The DecisionForestPredict 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": "f35d7512-bffa-4925-95ad-2a9c2c4cd7bc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create the Confusion Matrix 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": "3b60a858-d857-450f-b147-76ef8e2f8455",
   "metadata": {},
   "outputs": [],
   "source": [
    "predicted_data =  DataFrame('DF_Predict')\n",
    "predicted_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a37e5d5e-a35c-4383-bb23-61d6300069e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.metrics import confusion_matrix, ConfusionMatrixDisplay\n",
    "df = predicted_data.to_pandas().reset_index()\n",
    "cm = confusion_matrix(df['class'], df['prediction'], normalize='all')\n",
    "cmd = ConfusionMatrixDisplay(cm, display_labels=['DoesNotHaveParkinson', 'HasParkinson'])\n",
    "cmd.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89680ebd-bbe8-4525-8541-9171c07cf51a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ConvertTo\n",
    "predicted_data = ConvertTo(data = predicted_data,\n",
    "                           target_columns = ['id','\"class\"', \"prediction\"],\n",
    "                           target_datatype = [\"integer\",\"VARCHAR(charlen=20,charset=UNICODE,casespecific=NO)\",\"VARCHAR(charlen=20,charset=UNICODE,casespecific=NO)\"])\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6101c8bd-1137-4c83-9cc1-8d4b4ed9036b",
   "metadata": {},
   "outputs": [],
   "source": [
    "predicted_data.result.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18116bad-f81d-4751-90fb-26bee87b47c3",
   "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 the Decision Forest model the above matrix shows the predicted and actual value comparison for people having parkinson and those not having parkinson.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "883a9c2b-3284-4535-baf4-176d9ecc3430",
   "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 - Use classification Evaluator for DecisionForestPredict</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'>TD_ClassificationEvaluator</a> SQL 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": "21519316-afdc-4e59-bc78-a2809a910bcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(predicted_data.result, table_name = 'df_predict_output', schema_name = 'demo_user', \n",
    "            if_exists = 'replace')\n",
    "#pd.read_sql('SELECT * from df_predict_output', eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2b7471e-a023-4269-a509-8bcc36602843",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "SELECT * FROM TD_ClassificationEvaluator(\n",
    "   ON (select prediction, cast(\"class\" as VARCHAR(32000) CHARACTER SET UNICODE NOT CASESPECIFIC) as \"class\" from df_predict_output) AS InputTable\n",
    "   OUT VOLATILE TABLE OutputTable(additional_metrics_speech_test)\n",
    "   USING\n",
    "   ObservationColumn('\"class\"')\n",
    "   PredictionColumn('prediction')\n",
    "   Labels('0','1')\n",
    ") AS dt;\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('additional_metrics_speech_test;')\n",
    "    execute_sql(query)\n",
    "\n",
    "pd.read_sql('SELECT * FROM additional_metrics_speech_test', eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c681131e-4252-4eb9-be4f-dc887a203864",
   "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.\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": "663350b7-61c1-497f-8adb-b77ca03128b2",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Generalized Linear Model(GLM)</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.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;color:#00233C'>\n",
    "        <li>InputColumns; list or range of columns used as features (we used an ordinal reference of columns 2:753)</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>\n",
    "        \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "079aec95-f2e3-4eef-806d-3cc7c65beb02",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use the GLM function to create the GLM model using the train dataset.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7731ca07-f172-4eaa-818f-2b823adb8e47",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import GLM, TDGLMPredict\n",
    "\n",
    "glm_model = GLM(data = DataFrame('\"demo_user\".\"pd_speech_features_train\"'),\n",
    "                input_columns = ['1:10'], \n",
    "                response_column = '\"class\"',\n",
    "                learning_rate = 'OPTIMAL',\n",
    "                terms = ['id','\"class\"'],\n",
    "                momentum = 0.0,\n",
    "                family = 'Binomial')\n",
    "print(glm_model.result)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e02e8116-33d7-423a-ba9e-5314ee713b77",
   "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": "728bfe6b-aac7-4cdb-80b1-5387520ef636",
   "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": "b56d4594-3ae6-4c29-9eb0-2ef1c736d4b5",
   "metadata": {},
   "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/search/all?query=TD_ClassificationEvaluator&content-lang=en-US'>TD_ClassificationEvaluator</a> SQL Function.</p>\n",
    "\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Execute <a href = 'https://docs.teradata.com/search/all?query=TDGLMPredict&content-lang=en-US'>TDGLMPredict</a> using the model built above</li>\n",
    "    <li>Execute <a href = 'https://docs.teradata.com/search/all?query=TD_ClassificationEvaluator&content-lang=en-US'>TD_ClassificationEvaluator</a> and pass the actual classification and the predicted value</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8b31c0f-0c3b-4543-abfc-e1af200874c3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import teradataml\n",
    "from teradataml import GLM, TDGLMPredict\n",
    "obj = TDGLMPredict(newdata = DataFrame('\"demo_user\".\"pd_speech_features_test\"'),\n",
    "                           id_column = 'id',\n",
    "                           object = glm_model.result,\n",
    "                           accumulate = '\"class\"',\n",
    "                           output_prob=True,\n",
    "                           output_responses = ['0', '1'],\n",
    "                           terms='\"class\"')\n",
    "\n",
    "print(obj.result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5dd40f9c-3a46-4f87-987c-d86e4459d3e6",
   "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 TD_RegressionEvaluator, TD_ClassificationEvaluator or TD_ROC function as\n",
    "postprocessing step.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad90cfeb-f928-4ba3-b188-3a38ce76f6a0",
   "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 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'>TD_ClassificationEvaluator</a> SQL Function.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29dff5f7-6748-41c4-a2c7-a3ffefbda815",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create CONFUSION MATRIX for the GLM Predict model.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06f869ab-cd7c-4262-a219-2a7447fbfc9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.metrics import confusion_matrix, ConfusionMatrixDisplay\n",
    "df = obj.result.to_pandas()\n",
    "cm = confusion_matrix(df['class'], df['prediction'], normalize='all')\n",
    "cmd = ConfusionMatrixDisplay(cm, display_labels=['DoesNotHaveParkinson', 'HasParkinson'])\n",
    "cmd.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d40911b8-06d3-4624-9e9d-1cb4bec2990d",
   "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 GLM the above matrix shows the predicted and actual value comparison for people having parkinson and those not having parkinson.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b475376d-5557-47ee-8390-c0d2b19e1892",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Since TD_ClassificationEvaluator requires same datatype for prediction and class columns so creating another table with same datatype.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0a5bd08-0c81-4e2e-b509-e533f662b18a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ConvertTo\n",
    "glm_predicted_data = ConvertTo(data = obj.result,\n",
    "                           target_columns = ['id','\"class\"', \"prediction\",'prob_0','prob_1'],\n",
    "                           target_datatype = [\"INTEGER\",\"INTEGER\",\"INTEGER\",\"INTEGER\",\"INTEGER\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f278fe6-a1fe-4d44-9009-c8d4db0c9ff2",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(glm_predicted_data.result, table_name = 'glm_predict_output', schema_name = 'demo_user', \n",
    "            if_exists = 'replace')\n",
    "#pd.read_sql('SELECT * from glm_predict_output', eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7358495-9787-4893-8eab-7386b6637494",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = '''\n",
    "SELECT * FROM TD_ClassificationEvaluator(\n",
    "       ON (select prediction,  \"class\" from glm_predict_output) AS InputTable\n",
    "       OUT TABLE OutputTable(additional_metrics_speech_test_glm)\n",
    "       USING\n",
    "       ObservationColumn('\"class\"')\n",
    "       PredictionColumn('prediction')\n",
    "       Labels(0,1)\n",
    "    ) AS dt;\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('additional_metrics_speech_test_glm')\n",
    "    execute_sql(query)\n",
    "\n",
    "pd.read_sql('SELECT * FROM additional_metrics_speech_test_glm', eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3529157f-b9a6-4253-8400-f9afc3091590",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Comparison of the Metrics generated by the 2 Models. Decision Forest vs GLM</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4ea70c9-4ce7-4297-9849-d86582d2d21f",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''CREATE MULTISET TABLE metric_union as (select cast('DecisionForest' as VARCHAR(15)) as Model, trim(Metric) as Metric,MetricValue from additional_metrics_speech_test a \n",
    "union all \n",
    "select 'GLM' as Model ,  trim(Metric) as Metric,MetricValue from additional_metrics_speech_test_glm b\n",
    ")with data PRIMARY INDEX (Metric)\n",
    ";\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('metric_union')\n",
    "    execute_sql(query)\n",
    "    \n",
    "df_chart = pd.read_sql('select * from metric_union', eng)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c2d8a62-4732-40e6-b37b-e01d782efaac",
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "from matplotlib import pyplot as plt\n",
    "df_chart['Metric'] = df_chart['Metric'].str.replace('\\x00', '')\n",
    "df_pivot = pd.pivot_table(\n",
    "df_chart,\n",
    "values=\"MetricValue\",\n",
    "index=\"Metric\",\n",
    "columns=\"Model\"\n",
    ")\n",
    "#df_chart.plot.bar(x='Metric',y='MetricValue' , legend='model')\n",
    "ax=df_pivot.plot(kind='bar')\n",
    "# Get a Matplotlib figure from the axes object for formatting purposes\n",
    "fig = ax.get_figure()\n",
    "# Change the plot dimensions (width, height)\n",
    "fig.set_size_inches(12, 6)\n",
    "# Change the axes labels\n",
    "ax.set_xlabel(\"Metrics\")\n",
    "ax.set_ylabel(\"Metric Values\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cefce3e7-2df3-4d19-9edb-c77d30d03fbd",
   "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 GLM model with an accuracy of 82% is better than DecisionForest with accuracy of 77%.  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "tutorial-lying",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. 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": "cd66935e-3d9e-437b-a63f-4018c6efcbfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['pd_speech_features_train', 'pd_speech_features_test','additional_metrics_speech_test','df_predict_output',\n",
    "          'glm_predict_output', 'additional_metrics_speech_test_glm','metric_union','DF_train','DF_Predict' ]\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name=table)\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4ddd629-53b5-4c4d-91b1-01c4aa61bc75",
   "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": "23ff81f2-eadc-48fb-9047-5a688ec929fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_ParkinsonsDisease');\" \n",
    "#Takes 45 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "julian-modern",
   "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. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}