UseCases/Native-GLM-Regression/Regression_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "premium-produce",
   "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",
    "       ClearScape Analytic Functions for Linear Regression, Numeric Feature Transformation and Selection\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"../../images/TeradataLogo.png\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "italian-bridge",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "<b style = 'font-size:24px;font-family:Arial;color:#00233C'>Demonstration of Native Numeric feature processing and Linear Regression workflow</b>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the typical process for creating Machine Learning models, a significant amount of time is spent on data preparation and feature selection.  Furthermore, these manipulations must be replicated in operations for effective deployment of any model into production.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following demonstration will illustrate the use of native <b style = 'color:#00b2b1'>ClearScape Analytics</b> functions that can provide for greater efficiency, ease of use, and the ability to process data at extreme scale for the tasks of selection and processing of numeric features.  The demonstration will then use this prepared data set as inputs to a Decision Forest Regression model training and evaluation process.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data for this demonstration consists of a Home Sales Price data set, which includes many numeric and non-numeric features.  Steps in this demo are as follow:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Create an Analytic Data Set consisting of only numeric columns with all NULL values filled in, and values rescaled between 0 and 1</li>\n",
    "    <li>Take the prepared data as input to a Linear Regression Model</li>\n",
    "    <li>Score and evaluate model accuracy against a set of Testing data.</li>\n",
    "    </ol>\n",
    "    \n",
    "<img src = 'Flow_Diagram_Regression.png' width = 100%>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "informed-ghost",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Step 1 - Create a dense, numeric-only data set</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The raw data consists of 82 columns, 43 of which are non-numeric.  Additionally, some of the numeric columns contain NULL value, which also need to be filled in for the algorithm to work properly.  <b>Note</b> it is possible to convert these columns to numeric values using other SQL functions, but for this demonstration we will show how to remove them.</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Inspect the rows of the table</li>\n",
    "    <li>Remove non-numeric columns using ANTISELECT</li>\n",
    "    <li>Discover any missing values and columns with missing values</li>\n",
    "    <li>Convert FLOAT Columns to INTEGER to prepare for imputation</li>\n",
    "    <li>Use SimpleImputeFit/SimpleImputeTransform to fill NULL values</li>\n",
    "    <li>Use ScaleFit/ScaleTransform to rescale the data</li>\n",
    "    </ol>\n",
    "\n",
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Imports and Connection</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Import required packages and create a connection context to Vantage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "persistent-companion",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "display.suppress_vantage_runtime_warnings = True\n",
    "\n",
    "import json\n",
    "from teradataml import *\n",
    "\n",
    "\n",
    "\n",
    "from IPython.display import display as ipydisplay\n",
    "\n",
    "from sklearn.metrics import mean_squared_error\n",
    "from sklearn.metrics import mean_absolute_error\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.metrics import roc_curve\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "union-question",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load vars json\n",
    "with open('../../vars.json', 'r') as f:\n",
    "    session_vars = json.load(f)\n",
    "\n",
    "# Create the SQLAlchemy Context\n",
    "host = session_vars['environment']['host']\n",
    "username = session_vars['hierarchy']['users']['business_users'][1]['username']\n",
    "password = session_vars['hierarchy']['users']['business_users'][1]['password']\n",
    "\n",
    "eng = create_context(host=host, username=username, password=password)\n",
    "\n",
    "\n",
    "# confirm connection\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "downtown-digest",
   "metadata": {},
   "source": [
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>1.1 - Inspect the Data</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create a Virtual DataFrame that represents the data in VantageCloud Lake OFS Storage</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "african-development",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_housing = DataFrame('\"demo_ofs\".\"housing_prices_full\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sexual-customs",
   "metadata": {},
   "outputs": [],
   "source": [
    "ipydisplay(tdf_housing.shape)\n",
    "ipydisplay(tdf_housing.head(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "short-messaging",
   "metadata": {},
   "source": [
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>1.2 - Remove specific columns using ANTISELECT</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>ANTISELECT takes a list of column names, column ordinals, or ranges of names/ordinals</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "quantitative-modem",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import Antiselect\n",
    "\n",
    "# iterate over the data types\n",
    "# of each column to get a list of non-numeric columns\n",
    "as_res = Antiselect(data = tdf_housing, \n",
    "                    exclude = [key for key, value in tdf_housing.dtypes.__dict__['_column_names_and_types'] if value == 'str'])\n",
    "as_res.result.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "nasty-girlfriend",
   "metadata": {},
   "source": [
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>1.3 - Find missing values and columns</b>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Data-Exploration-Functions/TD_GetRowsWithMissingValues'>GetRowsWithMissingValues</a> can be used to find all rows the contain NULLs, optionally passing target columns</li>\n",
    "    <li><a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Data-Exploration-Functions/TD_ColumnSummary'>ColumnSummary</a> offers a more detailed set of statistics on selected or all columns </li>\n",
    "    </ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "equipped-delicious",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import GetRowsWithMissingValues, ColumnSummary\n",
    "\n",
    "GetRowsWithMissingValues(data = as_res.result).result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "composed-welcome",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_cs = ColumnSummary(data = as_res.result, target_columns = as_res.result.columns).result\n",
    "tdf_cs[tdf_cs['NullCount'] > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "protecting-vacation",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>1.4 - Convert FLOAT to INTEGER</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Data-Cleaning-Functions/TD_ConvertTo'>ConvertTo</a> converts the specified input table columns to specified data types.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In order for SimpleImpute to work properly for MODE replacement of values (the example which follows this one), data types need to be one of CHAR, VARCHAR, BYTEINT, SMALLINT, or INTEGER.  <b>ConvertTo</b> will take selected columns and convert them to the target type.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Rerun ColumnSummary to verify the type change</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "covered-front",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ConvertTo\n",
    "\n",
    "res_cv = ConvertTo(data = as_res.result, target_columns = 'masvnrarea', target_datatype = 'integer')\n",
    "\n",
    "tdf_cs = ColumnSummary(data = res_cv.result, target_columns = res_cv.result.columns).result\n",
    "tdf_cs[tdf_cs['NullCount'] > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sound-large",
   "metadata": {},
   "source": [
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>1.5 - Impute Missing Values</b>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Data-Cleaning-Functions/TD_SimpleImputeFit'>SimpleImputeFit</a> will output a table with the values that will be used to substitute the missing values</li>\n",
    "    <li><a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Data-Cleaning-Functions/TD_SimpleImputeTransform'>SimpleImputeTransform</a> will return the input data set with the missing values filled in</li>\n",
    "    <li>Verify the NULL values have been removed</li>\n",
    "    </ul>\n",
    " \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note one can also use the Fit table as input to <a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Feature-Engineering-Transform-Functions/TD_ColumnTransformer'>ColumnTransformer</a></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "pursuant-holmes",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import SimpleImputeFit, SimpleImputeTransform, ScaleFit, ColumnTransformer\n",
    "\n",
    "si_fit = SimpleImputeFit(data = res_cv.result, \n",
    "                         stats_columns = ['lotfrontage', 'masvnrarea', 'garageyrblt'],\n",
    "                         stats = ['mean','mode', 'mean'])\n",
    "\n",
    "si_trns = SimpleImputeTransform(data = res_cv.result, object = si_fit.output)\n",
    "\n",
    "si_trns.result.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "olive-execution",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Re-run GetRowsWithMissingValues Function - verify no results\n",
    "\n",
    "GetRowsWithMissingValues(data = si_trns.result).result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "equal-society",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ScaleFit, ScaleTransform\n",
    "\n",
    "sf_fit = ScaleFit(data = si_trns.result, scale_method = 'RESCALE (lb=0, ub=1)',\n",
    "                     target_columns = ['1:36'])\n",
    "\n",
    "sf_trns = ScaleTransform(data = si_trns.result, object = sf_fit.output, accumulate = ['id','saleprice'])\n",
    "sf_trns.result.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "reflected-carbon",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Step 2 - Train the Linear Model</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The goal here is to take the numeric, dense data set as inputs to the model training and validation steps.  In order to do so, we must split the data into training and testing data sets.  This is done in simple SQL using SAMPLE clause.</p>\n",
    "\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Create Training and Testing data sets using SAMPLE</li>\n",
    "    <li>Create the Linear Regression Model</li>\n",
    "    </ol>\n",
    "\n",
    "<hr>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>2.1 - Split data using SAMPLE</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Commit the data to permanent train/test tables.  This will also materialize all the selections, transformations and imputations above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "addressed-given",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_samples = sf_trns.result.sample(frac = [0.2, 0.8])\n",
    "copy_to_sql(tdf_samples[tdf_samples['sampleid'] == 2], table_name = 'housing_train', schema_name = 'demo_ofs', if_exists = 'replace')\n",
    "copy_to_sql(tdf_samples[tdf_samples['sampleid'] == 1], table_name = 'housing_test', schema_name = 'demo_ofs', if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "linear-poster",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>2.2 - Train the Model</b>\n",
    "\n",
    "        \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Model-Training-Functions/TD_GLM'>TD_GLM</a> function is a generalized linear model (GLM) that performs regression and classification analysis on data sets, where the response follows an exponential family distribution and supports the following models:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "    <li>Regression (Gaussian family): The loss function is squared error.</li>\n",
    "<li>Binary Classification (Binomial family): The loss function is logistic and implements logistic regression. The only response values are 0 or 1.</li>\n",
    "    </ul>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function uses the Minibatch Stochastic Gradient Descent (SGD) algorithm that is highly scalable for large datasets. The algorithm estimates the gradient of loss in minibatches, which is defined by the Batchsize argument and updates the model with a learning rate using the LearningRate argument.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function also supports the following approaches:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>L1, L2, and Elastic Net Regularization for shrinking model parameters</li>\n",
    "    <li>Accelerated learning using Momentum and Nesterov approaches</li>\n",
    "    </ul>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function uses a combination of IterNumNoChange and Tolerance arguments to define the convergence criterion and runs multiple iterations (up to the specified value in the MaxIterNum argument) until the algorithm meets the criterion.\n",
    "The function also supports LocalSGD, a variant of SGD, that uses LocalSGDIterations on each AMP to run multiple batch iterations locally followed by a global iteration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fossil-victoria",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import GLM, TDGLMPredict\n",
    "\n",
    "glm_model = GLM(data = DataFrame('\"demo_ofs\".\"housing_train\"'),\n",
    "                input_columns = '2:37', \n",
    "                response_column = 'saleprice')\n",
    "glm_model.result.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "loose-plasma",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Step 3 - Run the prediction and score results</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Execute a test prediction using the split data above.  Evaluation of the model accuracy is done using <a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Model-Evaluation-Functions/TD_RegressionEvaluator'>RegressionEvaluator</a> to derive various accuracy metrics including <b>Mean Absolute Error (MAE)</b> and <b>Root Mean Squared Logarithmic Error (RMSLE)</b>.  Note that Mean Absolute Error shows the actual value (price in dollars) accuracy, while RMSLE takes into account the ratio of difference between predicted and actual value (e.g. 30 actual/40 predicted and 300/400 have the same accuracy ratio, but 10x different absolute accuracy).</p>\n",
    "\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Execute TDGLMPredict using the model built above</li>\n",
    "    <li>Execute RegressionEvaluator</li>\n",
    "    </ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "suspended-montreal",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>3.1 - Run the Prediction Function</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Call <a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Model-Scoring-Functions/TD_GLMPredict'>TDGLMPredict</a> using the testing data that was split above, and use the model response as input.  Also pass additional parameters and accumulate the actual sales price value.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "vocal-representation",
   "metadata": {},
   "outputs": [],
   "source": [
    "glm_prediction = TDGLMPredict(newdata = DataFrame('\"demo_ofs\".\"housing_test\"'),\n",
    "                           id_column = 'id',\n",
    "                           object = glm_model.result,\n",
    "                           accumulate = 'saleprice')\n",
    "  \n",
    "glm_prediction.result.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sonic-lewis",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>3.2 - Calculate Model Accuracy</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>RegressionEvaluator will calculate multiple evaluation metrics.  See the documentation for a full list of available metrics and their meaning.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "maritime-spare",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import RegressionEvaluator\n",
    "\n",
    "re_result = RegressionEvaluator(data = glm_prediction.result, \n",
    "                                observation_column = 'saleprice', \n",
    "                                prediction_column = 'prediction', \n",
    "                                metrics = ['MAE', 'RMSLE','MSE', 'MSLE', 'MAPE', 'MPE','RMSE','MPD','MGD', 'EV'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "danish-wilson",
   "metadata": {},
   "outputs": [],
   "source": [
    "re_result.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "solar-stuart",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Simple Plotting to visualize predictions</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Retrieve a subset of rows and plot the prediction vs. actual sale price</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "compliant-intro",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prediction = glm_prediction.result.to_pandas(num_rows = 20)\n",
    "df_prediction.set_index('id', drop = True).plot(kind = 'bar', figsize = (10,10));"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "stylish-amateur",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Clean Up</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "improving-modification",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_drop_table('housing_train', schema_name = 'demo_ofs')\n",
    "db_drop_table('housing_test', schema_name = 'demo_ofs')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "palestinian-working",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "consistent-submission",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}