UseCases/Native-KMeans/KMeans_Clustering_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "powerful-navigation",
   "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 Analytics for Customer Segmentation using K-means Clustering and Data Preparation Piplelines\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": "covered-brain",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "<b style = 'font-size:24px;font-family:Arial;color:#00233C'>Leverage ClearScape Analytics for efficient and highly scalable data preparation, model training, and evaluation workflows</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>K-means clustering is one of the most popular <b>unsupervised</b> machine learning algorithms.  Essentially, the algorithm seeks to group similar data points together by minimizing the average (\"means\" in K-means) distance for all data points from each cluster's center (centroid).</p>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "    <tr>\n",
    "        <td style = 'vertical-align:top' width = '50%'>\n",
    "            <ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                <li>Define the number of clusters (k)</li>\n",
    "                <br>\n",
    "                <li>The algorithm chooses random points as centroids</li>\n",
    "                <br>\n",
    "                <li>Each iteration attempts to optimize the centroid locations</li>\n",
    "                <br>\n",
    "                <li>Iterations end once the distances have stabilized or the max iteration count is reached</li>\n",
    "            </ol>\n",
    "        </td>\n",
    "        <td><img src = 'images/K-means_convergence.gif' width = '250'></td>\n",
    "    </tr>\n",
    "</table>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>One limitation of this algorithm is that it only accepts numeric data as feature input (categorical clustering can be performed using K-modes algorithm).  Typically, data engineers or data scientists will perform multiple <b>serial</b> steps to prepare a numeric-only data set that can be passed to the K-means algorithm.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>ClearScape Analytics provides native \"Fit and Transform\" functions to assist in data preparation and transformation at scale.  To aid in efficiency and operationalization, Vantage provides a bulk <b>Column Transformer</b> function which can take multiple transformation directives at the same time, and act on the whole data set at once.  This allows for both process and code simplifcation, allowing more streamlined and robust operational deployment.</p> \n",
    "\n",
    "<img src = 'Flow_Diagram_KMeans.png' width = 100%>\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cardiac-auction",
   "metadata": {},
   "source": [
    "<b style = 'font-size:24px;font-family:Arial;color:#00233C'>Live Demonstration</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data for this demonstration is based on online purchase history data set, which can be found <a href = 'https://www.kaggle.com/code/hellbuoy/online-retail-k-means-hierarchical-clustering/data'>here</a>.  The goal is to segment the customers by purchase volume and value.  Steps are as follow:</p>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "    <tr>\n",
    "        <td style = 'vertical-align:top' width = '50%'>\n",
    "            <ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                <li>Analyze the raw data, split a testing set</li>\n",
    "                <br>\n",
    "                <li>Engineer numeric features</li>\n",
    "                <br>\n",
    "                <li>Build the K-means model</li>\n",
    "                <br>\n",
    "                <li>Apply in-line transformation to the testing set</li>\n",
    "                <br>\n",
    "                <li>Make Predictions and evaluate model accuracy</li>\n",
    "            </ol>\n",
    "        </td>\n",
    "        <td><img src = 'images/clustering_img.png' width = '250'></td>\n",
    "    </tr>\n",
    "</table>\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": "diagnostic-guess",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "import json\n",
    "from teradataml import *\n",
    "display.suppress_vantage_runtime_warnings = True\n",
    "\n",
    "\n",
    "from IPython.display import display as ipydisplay\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "musical-gamma",
   "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",
    "eng.execute(f'''SET SESSION COMPUTE GROUP {session_vars['hierarchy']['users']['business_users'][1]['compute_group']}''')\n",
    "\n",
    "# confirm connection\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "southern-response",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Step 1 - Data Preparation</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we will inspect the original data set, and perform various preparation tasks.</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Inspect the rows of the table</li>\n",
    "    <li>Inspect the column metadata using <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Data-Exploration-Functions/TD_ColumnSummary'>ColumnSummary</a></li>\n",
    "    <li>Split off a testing data set to be used in evaluation</li>\n",
    "    </ol>\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "portable-catch",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>1.1 - Inspect the Data</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Simple SQL query to show the data</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "detailed-hamburg",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_retail_data = DataFrame('\"demo_ofs\".\"UK_Retail_Data\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "alike-translation",
   "metadata": {},
   "outputs": [],
   "source": [
    "ipydisplay(tdf_retail_data.shape)\n",
    "ipydisplay(tdf_retail_data.head(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "legal-waterproof",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>1.2 View Column information</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Data-Exploration-Functions/TD_ColumnSummary'>ColumnSummary</a> provides more details on column values and ranges</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "desirable-prefix",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ColumnSummary\n",
    "ColumnSummary(data = tdf_retail_data, target_columns = tdf_retail_data.columns).result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "three-bernard",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>1.3 Create a Testing data set</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Select several \"Customer ID\" values for testing later.  This uses the SQLAlchemy ClauseElement Expression capabilities in the <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-Package-for-Python-User-Guide-17.20/teradataml-Extension-with-SQLAlchemy/Using-SQLAlchemy-Clause-Element-and-Expression/Using-Basic-SQLAlchemy-ClauseElement-and-Expression-for-Filtering'>teradataml package</a>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "timely-characteristic",
   "metadata": {},
   "outputs": [],
   "source": [
    "in_expr = tdf_retail_data['CustomerID'].expression.in_(['17307', '12503', '18268', '12908', '13693'])\n",
    "\n",
    "tdf_retail_test = tdf_retail_data[in_expr]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "opposed-adult",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_retail_test"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "extraordinary-liechtenstein",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Step 2 - Feature Engineering</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This section will illustrate how to prepare the data set for model training.  We will use various \"Fit\" functions to create input dataframes for the <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Feature-Engineering-Transform-Functions/TD_ColumnTransformer'>ColumnTransformer</a> function to take as input in order to execute a bulk transformation.</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Create a per-customer grouping of data</li>\n",
    "    <li>Create Fit Tables\n",
    "        <ul><li>Remove Outliers</li>\n",
    "            <li>Impute Missing Values</li>\n",
    "            <li>Create New Numeric Features</li>\n",
    "            <li>Rescale the Data Set</li>\n",
    "        </ul></li>\n",
    "    <li>Call the final Transformation function</li>\n",
    "    </ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "critical-reason",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>2.1 - Create a per-customer table</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Simple GROUP BY, exclude the testing IDs.  Note there are 3930 unique customers in this training set.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "offensive-wyoming",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter the tdf to exclued the customer IDs in our \"test\" set\n",
    "\n",
    "notin_expr = tdf_retail_data.CustomerID.expression.notin_(['17307', '12503', '18268', '12908', '13693'])\n",
    "\n",
    "tdf_train = tdf_retail_data[notin_expr].groupby('CustomerID').agg({'Quantity':['sum'], \n",
    "                                                                   'UnitPrice':['sum'], \n",
    "                                                                   'StockCode':['count']})\n",
    "tdf_train.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "immune-absolute",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>2.2 Create Fit Tables</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Feature-Engineering-Transform-Functions'>Feature Engineering Transform Functions</a> Use a \"Fit and Transform\" approach to make processing more modular and efficient.  \"Fit tables\" can be used as input to either individual Transform functions, or passed to a single <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Feature-Engineering-Transform-Functions/TD_ColumnTransformer'>ColumnTransformer</a> function.</p>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "    <tr>\n",
    "        <td style = 'vertical-align:top' width = '50%'>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Fit outlier removal using <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Data-Cleaning-Functions/Handling-Outliers/TD_OutlierFilterFit'>OutlierFilterFit</a></li>\n",
    "    <li>Fit a simple imputer to replace missing values using <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Data-Cleaning-Functions/Handling-Missing-Values/TD_SimpleImputeFit'>SimpleImputeFit</a></li>\n",
    "    <li>Fit column calculations to create new features using <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Feature-Engineering-Transform-Functions/TD_NonLinearCombineFit'>NonLinearCombineFit</a></li>\n",
    "    <li>Call <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Feature-Engineering-Transform-Functions/TD_ColumnTransformer'>ColumnTransformer</a> to execute the transformations (to allow for Scaling)</li>\n",
    "    <li>Rescale the data using <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Feature-Engineering-Transform-Functions/TD_ScaleFit'>ScaleFit/Transform</a></li>\n",
    "            </ul></td>\n",
    "        <td><img src = 'images/fit_transform.png' width = '300'></td>\n",
    "    </tr>\n",
    "    </table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "labeled-kenya",
   "metadata": {},
   "outputs": [],
   "source": [
    "ft_outlier = OutlierFilterFit(data = tdf_train,\n",
    "                             target_columns = ['sum_Quantity', 'sum_UnitPrice'], \n",
    "                             lower_percentile = 0.03, \n",
    "                             upper_percentile = 0.97,\n",
    "                             percentile_method = 'PercentileCont', \n",
    "                             replacement_value = 'Median')\n",
    "\n",
    "ft_impute = SimpleImputeFit(data = tdf_train, \n",
    "                            stats_columns = 'sum_Quantity',\n",
    "                            literals_columns = 'CustomerID', \n",
    "                            literals = '19000', \n",
    "                            stats = 'MIN')\n",
    "\n",
    "ft_nlc_TotSales = NonLinearCombineFit(data = tdf_train, \n",
    "                            target_columns = ['sum_Quantity', 'sum_UnitPrice'], \n",
    "                            formula = 'Y = X0*X1', \n",
    "                            result_column = 'TotalSales')\n",
    "\n",
    "ft_nlc_SalesPer = NonLinearCombineFit(data = tdf_train, \n",
    "                                     target_columns = ['sum_Quantity', 'sum_UnitPrice', 'count_StockCode'], \n",
    "                                     formula = 'Y = (X0*X1)/X2', \n",
    "                                     result_column = 'SalesPerItem')\n",
    "\n",
    "tdf_Transformed = ColumnTransformer(input_data = tdf_train, \n",
    "                                   ouliterfilter_fit_data = ft_outlier.result, \n",
    "                                   simpleimpute_fit_data = ft_impute.output,\n",
    "                                   nonlinearcombine_fit_data = ft_nlc_TotSales.result)\n",
    "\n",
    "tdf_train_Transformed = ColumnTransformer(input_data = tdf_Transformed.result, \n",
    "                                         nonlinearcombine_fit_data = ft_nlc_SalesPer.result)\n",
    "\n",
    "tdf_train_Transformed.result.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "going-badge",
   "metadata": {},
   "outputs": [],
   "source": [
    "ft_rescale = ScaleFit(data = tdf_train_Transformed.result, \n",
    "                     target_columns = ['sum_Quantity', 'count_StockCode', 'sum_UnitPrice', 'TotalSales', 'SalesPerItem'],\n",
    "                     scale_method = 'range')\n",
    "\n",
    "tdf_train_scaled = ScaleTransform(data = tdf_train_Transformed.result, object = ft_rescale.output, accumulate = ['CustomerID']).result\n",
    "\n",
    "tdf_train_scaled.sample(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "outside-council",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Step 3 - Build the K-means Model</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As discussed above, the K-means algorithm takes a number of clusters \"k\", chooses a random starting point for each centroid, and iterates until a hard limit, or an optimium value is reached.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Finding an Ideal value for K</b></p>\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "    <tr>\n",
    "        <td style = 'vertical-align:top' width = '50%'>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The example below uses a value of 5 for the number of clusters to build the model against.  Typically, data scientists will build the model using various values for \"k\", and plot the \"WCSS\" (Within Cluster Sum-of-Squares) value on a series of each value chosen for k.  The \"elbow\" point (where the slope changes) is usually a good value for k.  <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Model-Training-Functions/TD_KMeans'>KMeans</a> function will return this value as \"TotalWithinSS : ###\" as a row in the \"td_modelinfo_kmeans\" column.</p></td>\n",
    "        <td><img src = 'images/WCSS_elbow.png' width = '300'></td>\n",
    "    </tr>\n",
    "    </table>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Other Function Parameters Include (but are not limited to)</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Input dataframe</li>\n",
    "    <li>StopThreshold - The algorithm converges if the distance between the centroids from the previous iteration and the current iteration is less than the specified value.</li>\n",
    "    <li>MaxIterNum</li>Specify the maximum number of iterations for the K-means algorithm. The algorithm stops after performing the specified number of iterations even if the convergence criterion is not met.\n",
    "    </ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "parliamentary-strategy",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "kmeans_res = KMeans(data = tdf_train_scaled, \n",
    "                    id_column = 'CustomerID', \n",
    "                    target_columns = ['sum_Quantity', 'count_StockCode', 'sum_UnitPrice', 'TotalSales', 'SalesPerItem'], \n",
    "                    num_clusters = 5, \n",
    "                    iter_max = 100, \n",
    "                    threshold=0.0295)\n",
    "kmeans_res.result.to_pandas().sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fossil-phase",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Step 4 - Bulk Transformation</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, the Fit table objects created above will be passed to a single <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Feature-Engineering-Transform-Functions/TD_ColumnTransformer'>ColumnTransformer</a> function.  This is similar to an operational approach, where a single query will prepare new or incoming data for immediate analysis.</p>\n",
    "\n",
    "<img src = 'images/column_transformer.png' width = '300'>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "external-communist",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Recall the customer IDs we held back in the beginneing\n",
    "tdf_retail_test.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "invisible-yorkshire",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Perform the same groupby and aggregation as above\n",
    "tdf_gb_test = tdf_retail_test.groupby('CustomerID').agg({'Quantity':['sum'], \n",
    "                                                        'UnitPrice':['sum'], \n",
    "                                                        'StockCode':['count']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "democratic-biology",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pass this to the columntransformer function\n",
    "\n",
    "tdf_Transformed = ColumnTransformer(input_data = tdf_gb_test, \n",
    "                                   ouliterfilter_fit_data = ft_outlier.result, \n",
    "                                   simpleimpute_fit_data = ft_impute.output,\n",
    "                                   nonlinearcombine_fit_data = ft_nlc_TotSales.result)\n",
    "\n",
    "tdf_test_Transformed = ColumnTransformer(input_data = tdf_Transformed.result, \n",
    "                                         nonlinearcombine_fit_data = ft_nlc_SalesPer.result)\n",
    "\n",
    "tdf_test_scaled = ScaleTransform(data = tdf_test_Transformed.result, object = ft_rescale.output, accumulate = ['CustomerID']).result\n",
    "\n",
    "\n",
    "tdf_test_scaled.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "controlling-iceland",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Step 5 - Predict and Evaluate</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Finally, we run the model against new (in this case testing) data using <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Model-Scoring-Functions/TD_KMeansPredict'>KMeansPredict</a>.  The preparation step has been completed in a single query above.  Additionally, we will use an evaluation function <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Model-Evaluation-Functions/TD_Silhouette'>Silhouette</a> to analyze how well the new cluster predictions match the original model.</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Call KMeansPredict</li>\n",
    "    <li>Inpect the results</li>\n",
    "    <li>Call Silhouette on the output</li>\n",
    "    </ol>\n",
    "    \n",
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.1 - Call the Prediction Function</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Pass the Input Data, Model Table, and other parameters including columns to accumulate.  Note here we create a new View to assist with Silhouette analysis of the prediction.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "streaming-exposure",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "kmeans_prediction = KMeansPredict(data = tdf_test_scaled, \n",
    "                                  object = kmeans_res.model_data, \n",
    "                                  output_distance = True, \n",
    "                                  accumulate = ['sum_Quantity', 'count_StockCode', 'sum_UnitPrice', 'TotalSales', 'SalesPerItem'])\n",
    "\n",
    "copy_to_sql(kmeans_prediction.result, table_name = 'kmeans_pred', if_exists = 'replace')\n",
    "kmeans_prediction.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "altered-possible",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.2 - Evaluate the Prediction</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Model-Evaluation-Functions/TD_Silhouette'>Silhouette</a> is a native Vantage function that evaluates the similarity of an object to its cluster (cohesion) compared to other clusters (separation).  The silhouette scores and its definitions are as follows:</p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>1: Data is appropriately clustered</li>\n",
    "    <li>-1: Data is not appropriately clustered</li>\n",
    "    <li>0: Datum is on the border of two natural clusters</li>\n",
    "    </ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>See the documentation for a full listing of parameters and return values.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "static-relevance",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_prediction = DataFrame('kmeans_pred')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "exempt-threat",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "res_sil = Silhouette(data = tdf_prediction, \n",
    "                     id_column = 'CustomerID', \n",
    "                     cluster_id_column = 'td_clusterid_kmeans', \n",
    "                    target_columns = ['sum_Quantity', 'count_StockCode', 'sum_UnitPrice', 'TotalSales', 'SalesPerItem'])\n",
    "\n",
    "res_sil.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "heated-union",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Clean Up</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "restricted-utility",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_drop_table('kmeans_pred')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "headed-consolidation",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "smoking-joyce",
   "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
}