UseCases/Anomaly_Detection/Anomaly_Detection_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b1378a69-ac58-4d0c-af22-7ef881abac45",
   "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",
    "       Anomaly Detection in Robot Welding Process\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",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Detecting anomalies reduces issues and delays in many industries, especially in the manufacturing field. There have been approaches to detect anomalies in the past, such as engineering rules and graph and deep learning. However, it still proves difficult to detect all the existing anomalies. Plus, companies are striving to minimize false positives, cope with the diversity of sensors and metrology issues, and deliver actionable insights at a business pace. Fortunately, Teradata and ClearScape Analytics have the solution. In ClearScape Analytics, users can execute all steps of anomaly detection from data preparation and exploration to model training and evaluations and adjustments. These analyses can improve the process and ensure accuracy in anomaly detection.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Spot Welding Quality Assessment</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Spot welding is a common technique used for welding car body panels, particularly in the assembly of smaller parts and components. Spot welding involves using a pair of copper electrodes to apply a series of short, high-current welding pulses to the metal, fusing the parts together at specific points or “spots”.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The automotive industry is known for its high level of automation, and spot welding is one of the most automated processes, heavily reliant on robots to improve efficiency, reduce labor costs, and improve the consistency and quality of the finished product. Poor welding quality is rare, but even so, the consequences of poor quality may not be negligible in terms of rework costs and customer satisfaction, especially when quality issues are detected too late.</p>\n",
    "\n",
    "<img  src=\"images/AnomalyWelding.png\"/>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Spot welding is a resistance welding process that uses large electrical current. There are many ways to assess the quality of a spot, like tensile or ultrasonic testing to assess the weld strength or the analysis of the welding current measured and recorded during the welding process. In this demo, we focus on the analysis of the anomalies in the welding spot due to welding current, and more specifically the resistance, i.e. the voltage-current ratio which impacts the quality of the welding. The shape of the resistance curve depends on many factors like  the nature of the materials, the geometry, and the quality of the electrodes etc. </p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Values</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Improve accuracy in the production and manufacturing process.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Reduce the number of false positive anomalies detected in a system.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Decrease additional costs and time wasted due to undetected anomalies.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Determine patterns and significant factors that lead to anomalies.</li></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Many organizations fail to realize value from their ML and AI investments due to a lack of scale. It is estimated that for broad adoption across many industries, the number of models and model deployments needs to scale 100-1000x larger than their organizations currently support.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The unique massively-parallel architecture of Teradata Vantage allows users to prepare data, train, evaluate, and deploy models at unprecedented scale.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this particular use case, the volume of machine sensor data was so great that millions of ML models were created to derive analytic features that ultimately deployed tens of thousands of models for real-time scoring. This extent of scale is only possible by combining the power of Vantage with native ClearScape Analytic functions.</p>\n",
    "\n",
    "\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b33aebf1-80cf-4043-99de-b2ac0356ea64",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca2ba4a7-ae62-411d-82b7-381c1febe8d9",
   "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": "a3a57efc-4277-4840-8b3f-eb1efdedd170",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "# # '%%capture' suppresses the display of installation steps of the following packages\n",
    "# !pip install tdsense\n",
    "# !pip install imblearn\n",
    "# !pip install xgboost==1.7.3\n",
    "# !pip install colorlover\n",
    "# !pip install teradataml --upgrade teradataml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f93c8af-e523-439c-bf7d-f0d867ad546d",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>The above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If you uncomment those installs, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a0b76c2-b211-452f-949c-676da6da9540",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import getpass\n",
    "import pandas as pd\n",
    "import datetime\n",
    "from teradataml import *\n",
    "\n",
    "# import tdsense\n",
    "# from tdsense.plot import plotcurves\n",
    "\n",
    "import numpy as np # linear algebra\n",
    "import matplotlib.pyplot as plt\n",
    "import sklearn\n",
    "from sklearn import preprocessing\n",
    "# from tdsense.clustering import hierarchy_dendrogram, hierarchy_clustering\n",
    "# from tdnpathviz.visualizations import plotcurves\n",
    "%matplotlib inline\n",
    "\n",
    "from sklearn import datasets\n",
    "from sklearn2pmml.pipeline import PMMLPipeline\n",
    "from sklearn2pmml import sklearn2pmml\n",
    "from sklearn.model_selection import train_test_split\n",
    "from imblearn.over_sampling import SMOTE\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.metrics import accuracy_score, precision_score, roc_auc_score, f1_score,confusion_matrix, roc_curve, ConfusionMatrixDisplay\n",
    "import time\n",
    "import pytz\n",
    "\n",
    "\n",
    "import os\n",
    "from jdk4py import JAVA, JAVA_HOME, JAVA_VERSION\n",
    "# Set java path\n",
    "\n",
    "os.environ['PATH'] = os.environ['PATH'] + os.pathsep + str(JAVA_HOME)\n",
    "os.environ['PATH'] = os.environ['PATH'] + os.pathsep + str(JAVA)[:-5]\n",
    "\n",
    "from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot\n",
    "from collections import defaultdict\n",
    "import plotly.offline as offline\n",
    "offline.init_notebook_mode()\n",
    "\n",
    "\n",
    "from teradataml.dataframe.sql_functions import case\n",
    "from teradataml import db_drop_table\n",
    "configure.byom_install_location = \"mldb\"\n",
    "\n",
    "display.max_rows = 5\n",
    "warnings.filterwarnings('ignore')\n",
    "warnings.simplefilter(action='ignore', category=DeprecationWarning)\n",
    "warnings.simplefilter(action='ignore', category=RuntimeWarning)\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c250746-66ba-40aa-b41b-c791786f61a0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2be07d96-51d3-4aee-b025-582af97119da",
   "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": "c80f069f-5d34-4f18-93fd-c784897102c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=AnomalyDetection.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9476f53a-7115-4018-a58f-dd09f7fc8b88",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\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. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>   \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9940cdb5-c88e-425d-9f7a-aff8f6336c96",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_AnomalyDetection_cloud');\"\n",
    " # Takes about 50 seconds\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_AnomalyDetection_local');\"\n",
    " # Takes about 2 minute 30 secs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1653ae8-e6c1-4336-9260-b25024ce1c12",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – We should execute the below step only if we want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9616e68-a4a7-42e1-999d-e3b56042a454",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75f47fb4-6f61-4205-b563-2b08bf086cab",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Analyze the raw data set</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us start by creating a teradataml dataframe. A \"Virtual DataFrame\" that points directly to the dataset in Vantage.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99598e0a-8a6c-4539-a06d-f6723f67134f",
   "metadata": {},
   "outputs": [],
   "source": [
    "Sensor_Data = DataFrame(in_schema('DEMO_AnomalyDetection', 'Sensor_Data'))\n",
    "Sensor_Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4b9b958-737d-41a0-adec-91614fa0fe2e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We get the above data from sensors. We focus on one plant (PLANT=1) and one robot (ROBOT_ID=41). The Partition_ID is the type of welding, ID is the WELDING_ID, X is time required for welding in ms and Y is the RESISTANCE. We create a view with the columns required to get data with proper column names.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88cde234-6107-487e-92f2-7f045576cc1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "query = f\"\"\"\n",
    "REPLACE VIEW DEMO_AnomalyDetection.V_dataset_01 AS\n",
    "SELECT\n",
    "    1 AS PLANT\n",
    ",   {41} AS ROBOT_ID\n",
    ",   CAST(A.PARTITION_ID AS BIGINT) AS WELDING_TYPE\n",
    ",   CAST((DATE '{str(datetime.datetime.now()).split(' ')[0]}'  + FLOOR((WELDING_ID-700*WELDING_TYPE)/100))  AS DATE FORMAT 'YYYY-MM-DD') AS WELDING_DAY\n",
    ",   CAST(A.ID AS BIGINT) AS WELDING_ID\n",
    ",   CAST(A.X AS INTEGER) AS TIME_MS\n",
    ",   A.Y AS RESISTANCE\n",
    "FROM DEMO_AnomalyDetection.Sensor_Data A\n",
    "\"\"\"\n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ec3a959-c5e0-4039-88f8-846adca6f113",
   "metadata": {},
   "outputs": [],
   "source": [
    "welding_dataset_new = DataFrame(in_schema('DEMO_AnomalyDetection', 'V_dataset_01'))\n",
    "welding_dataset_new"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09198aa2-6ab7-4339-a01a-365cba02c772",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.1 - Some aggregations and visualization. </b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f83b1b1a-eece-487a-97d7-b4759ea624ce",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will check the histogram based on the minimum and maximum Time for welding.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A histogram is a better way to assess distribution, to cope with the scalability, it is recommended to compute the histogram bins in-database to leverage the Massively Parallel Architecture of Teradata Vantage. For that, we use the Histogram function of teradataml that pushes down the computations to Vantage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a5d38c3-ebb9-47a2-b8ad-f00acd9d769b",
   "metadata": {},
   "outputs": [],
   "source": [
    "welding_duration_ms = welding_dataset_new. \\\n",
    "                        groupby(['PLANT','ROBOT_ID','WELDING_TYPE', 'WELDING_ID']). \\\n",
    "                        agg({'TIME_MS':['min','max','count']})\n",
    "welding_duration_ms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "642bf739-a421-4ffd-8fc1-53f273db9bd9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import Histogram\n",
    "obj = Histogram(data=welding_duration_ms,\n",
    "                     target_columns=\"count_TIME_MS\",\n",
    "                     method_type=\"Scott\")\n",
    "res = obj.result.sort('MinValue')\n",
    "res"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62b099f0-eb76-45a2-9c0e-983399c59570",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that we have calculated the histogram values using the teradataml functions. Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI or many python modules available like plotly, seaborn etc. We can do all the calculations and pre-processing on Vantage and pass only the necessary information to visualization tools, this will not only make the calculation faster but also reduce the time due to less data movement between tools. We do the data transfer for this and the subsequent visualizations wherever necessary.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c9b72ab-7d3c-4964-9199-ee1dcc17c928",
   "metadata": {},
   "outputs": [],
   "source": [
    "res = obj.result.sort('MinValue').to_pandas()\n",
    "res['duration_ms'] = [str(row['MinValue'])+'-'+str(row['MaxValue']) for i,row in res.iterrows()]\n",
    "res.plot(x='duration_ms',y='CountOfValues',kind='bar', figsize=(15,10), legend=False,xlabel='Duration(ms)', ylabel='Welding Counts')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88429a10-aa8b-459f-976a-6276ab121bbc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above histogram we can see the bins between the Min and the Max value of the durations and the welding counts.</p> \n",
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.2 - More advanced processing using window functions and delta_t </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Resistance is an important parameter in resistance welding. The resistance should not vary too much. If there are any significant changes in resistance over time, it could indicate an issue with the weld quality. For example, an unusually high resistance could indicate poor contact between the parts being welded or a problem with the welding equipment.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5615026-52eb-4aae-8bb2-146e88ef4502",
   "metadata": {},
   "outputs": [],
   "source": [
    "welding_dataset_new.loc[welding_dataset_new.WELDING_ID == 854]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50c72091-f7f3-4ed3-a436-ee5c44335f4e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from tdnpathviz.visualizations import plotcurves\n",
    "plotcurves(welding_dataset_new.loc[welding_dataset_new.WELDING_ID == 854],field='RESISTANCE',row_axis='TIME_MS', series_id='WELDING_ID',select_id=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae924828-6e92-4003-93c9-b66aeec1821f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the variation of the resistance of the welding with respect to time. We see that the most interesting part lies between 40 and 400ms from the start of the curve.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next we apply the window function on the resistance to smooth the resistance and taking the mean value.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "516d8fd4-ab2c-44cd-89d2-d8075e40cf82",
   "metadata": {},
   "outputs": [],
   "source": [
    "# curve smoothing\n",
    "window_for_smoothing = welding_dataset_new.RESISTANCE.window(\n",
    "                            partition_columns   = \"WELDING_ID\",\n",
    "                            order_columns       = 'TIME_MS',\n",
    "                            window_start_point  = -15,\n",
    "                            window_end_point    = 15\n",
    ")\n",
    "welding_dataset_smooth = welding_dataset_new.assign(RESISTANCE_SMOOTHED = window_for_smoothing.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c351bab-cd80-452c-b600-79efaec9f769",
   "metadata": {},
   "outputs": [],
   "source": [
    "id_curve = 854\n",
    "single_welding = welding_dataset_smooth[welding_dataset_smooth.WELDING_ID == id_curve].sort('TIME_MS')\n",
    "single_welding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44d1ffb7-1bf2-4770-8b0d-f21ed5a589e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "figure = Figure(width=1000, height=400, image_type=\"jpg\",\n",
    "                        heading=\"RESISTANCE and RESISTANCE SMOOTHED\")\n",
    "plot = single_welding.plot(x=single_welding.TIME_MS, y=[single_welding.RESISTANCE, single_welding.RESISTANCE_SMOOTHED],\n",
    "                    style=['blue', 'red'],xlabel='time in ms', ylabel='resistance ',figure=figure)\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "299bf795-653e-45a4-8f39-5143d81173cf",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the variation of the resistance of the welding with respect to time and the smoothed resistance, as shown by the Red line, after applying the window function.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The window function generates a Window object on a teradataml DataFrame Column to run window aggregate functions.\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Function allows user to specify window for different types of computations:\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Cumulative\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Group\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Moving\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Remaining\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>By default, window with Unbounded Preceding and Unbounded following is considered for calculation.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next we calculate the derivative by using the lead function and taking the difference of the lead value and the mean value of the resistance. Applying a window function to smooth the resistance curve helps to eliminate noise and makes it easier to see the overall trend. The derivative of the resistance gives an indication of how quickly the resistance is changing, which can be a useful measure for detecting anomalies and predicting potential issues.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fccb6149-ce72-4601-983b-a87f2bc52417",
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's compute the lead\n",
    "window_for_lead = welding_dataset_smooth.RESISTANCE_SMOOTHED.window(\n",
    "                            partition_columns   = \"WELDING_ID\",\n",
    "                            order_columns       = 'TIME_MS')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6a9bc90-f330-467f-8765-5a00578c6c6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "welding_dataset_smooth = welding_dataset_smooth.assign(RESISTANCE_SMOOTHED_AFTER = window_for_lead.lead())\n",
    "welding_dataset_smooth = welding_dataset_smooth.assign(DERIVATIVE = (welding_dataset_smooth.RESISTANCE_SMOOTHED_AFTER - welding_dataset_smooth.RESISTANCE_SMOOTHED).zeroifnull())\n",
    "welding_dataset_smooth.sort(['WELDING_ID','TIME_MS'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d019941f-4422-4012-8984-0dce20d10e48",
   "metadata": {},
   "outputs": [],
   "source": [
    "id_curve = 854\n",
    "single_welding_subplot = welding_dataset_smooth[welding_dataset_smooth.WELDING_ID == id_curve].sort('TIME_MS')\n",
    "single_welding_subplot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bd9b71a-b668-44f9-a0bd-e74b2c82462e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import subplots\n",
    "# fig, axes = subplots(grid = {(1, 1): (1, 1),(2, 1): (1, 2)})\n",
    "# Plot 1980 data at first Axis.\n",
    "fig, axes = subplots(nrows=2, ncols=1)\n",
    "plot = single_welding_subplot.plot(x=single_welding_subplot.TIME_MS, \n",
    "                    y=[single_welding_subplot.RESISTANCE, single_welding_subplot.RESISTANCE_SMOOTHED],\n",
    "                    legend=[\"RESISTANCE\", \"RESISTANCE SMOOTHED\"],\n",
    "                    figure=fig,\n",
    "                    style=['blue', 'red'],xlabel='time in ms', ylabel='resistance ',               \n",
    "                    ax=axes[0])\n",
    "\n",
    "# Plot 1981 data at second Axis.\n",
    "plot = single_welding_subplot.plot(x=single_welding_subplot.TIME_MS, \n",
    "                    y=single_welding_subplot.DERIVATIVE,\n",
    "                    legend=[\"DERIVATIVE\"],\n",
    "                    figure=fig,\n",
    "                    style=\"red\",xlabel='time in ms', ylabel='derivative ' ,              \n",
    "                    ax=axes[1])\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92c9f6e0-7b26-4fed-9b43-1d35989affad",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that the most interesting part lies between 40 and 400ms from the start of the curve, so we plot only that subset.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4615d965-6892-4729-81b0-9dd39f7d9411",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>It is hard to assess the diversity of curve shapes in this plot since many of them are superimposed. However, we see in the middle of the picture a sharp drop that looks unusual. Moreover, we guess that there are shifts in time and height.</p>\n",
    "\n",
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Feature Engineering</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da82ee40-3e38-49af-a6ca-a678ba240ca2",
   "metadata": {},
   "outputs": [],
   "source": [
    "welding_dataset_new.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "539a4c25-f868-44af-bca3-13b4ca477445",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create a feature table by using different functions on the Resistance column. Valid values for functions are: 'count', 'sum', 'min', 'max', 'mean', 'std', 'percentile', 'unique','median', 'var', 'skew', 'kurtosis'. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa37d2af-c185-4a84-9ca5-8628a216aa27",
   "metadata": {},
   "outputs": [],
   "source": [
    "features = welding_dataset_new.loc[welding_dataset_new.TIME_MS > 20,:]. \\\n",
    "        groupby(welding_dataset_new.columns[0:5]). \\\n",
    "        agg({\n",
    "            'TIME_MS':['min','max'],\n",
    "            'RESISTANCE':['count', 'sum', 'min', 'max', 'mean', 'std', 'percentile', 'unique','median', 'var','skew','kurtosis']\n",
    "        })\n",
    "features"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0196e16a-9d9d-4d44-a0ed-e5220c3314e2",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Anomaly Detection on Sensor Data</b></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's start by getting the feature columns from the features tables</p>   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27cdf0f8-e0b3-41b5-b18d-b77cdbc5652b",
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_names = features.columns[7::]\n",
    "feature_names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9655f048-ffbd-4785-9e8b-39d192ff7808",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.1 Clustering by curve shape</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To cluster time series by shapes, we will use the Dynamic Time Warping (DTW) distance that measures the similarity between two time series. This distance is well adapted to this kind of problem since it provides robustness to shifts in time and height.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Distance Matrix in-database Computations</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ClearScape Analytics DTW function computes at scale distances between one reference curve to a set of curves, a many-to-one approach. ClearScape Analytics offers in database dynamic time warping function, callable in SQL as TD_DTW. TD_DTW measures the similarity of two time series. The Dynamics Time Warping (DTW) algorithm is used for space and time. The algorithm uses the FastDTW algorithm. TD_DTW measures the similarity of two time series. The Dynamics Time Warping (DTW) algorithm is used for space and time. The algorithm uses the FastDTW algorithm. This function computes at scale the DTW distances between one reference curve to a set of curves, a many-to-one approach. We want to compute the distance matrix of our subset, i.e. the DTW distance between each curve. The distance matrix is symmetric, since the DTW is, hence we only need to compute the triangular matrix. We wrapped this computation in the tdsense package that calls the TD_DTW function and iterates on the matrix row to compute and store the whole triangular distance matrix in a table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "207e72c8-41e3-481a-9727-a4c7510f4206",
   "metadata": {},
   "outputs": [],
   "source": [
    "overview = welding_dataset_new.groupby('WELDING_DAY').count(distinct=True)\n",
    "dates = list(overview.to_pandas().reset_index()['WELDING_DAY'].values.astype('str'))\n",
    "dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c7180b4-a8b5-450a-96be-8aed93d1199a",
   "metadata": {},
   "outputs": [],
   "source": [
    "subset = welding_dataset_new[ \\\n",
    "                 (welding_dataset_new['PLANT'] == 1) & \\\n",
    "                 (welding_dataset_new['ROBOT_ID'] == 41) & \\\n",
    "                 (welding_dataset_new['WELDING_TYPE'] in (8,9)) & \\\n",
    "                 (welding_dataset_new['WELDING_DAY'].isin(dates)) \\\n",
    "                ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0dda2eca-af26-4741-abeb-b63758f8c996",
   "metadata": {},
   "outputs": [],
   "source": [
    "subset_zoom = subset[(subset.TIME_MS < 400) & (subset.TIME_MS > 40)]\n",
    "subset_zoom.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d40f422-886d-48e5-a4ce-03b259523917",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The subset of data we have taken contains 7 columns and 344,622 rows.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Since this is a 2CPU system, the below computation takes around more than 2 hours for 350k rows and so we have pre calculated it and stored in the table in database.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>**In case we still want to compute the matrix please set the If part of the below code to <b>True</b> instead of <b>False</b></i></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "276fd1b7-e057-4c0c-b8b0-4e063d70eb7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "if False:\n",
    "    dtw_matrix = dtw_distance_matrix_computation2(subset_zoom,field='RESISTANCE',\n",
    "                                     table_name=dtw_result_table,\n",
    "                                     schema_name = Param['database'],\n",
    "                                     row_axis='TIME_MS',\n",
    "                                     series_id = 'WELDING_ID')\n",
    "else:\n",
    "    dtw_matrix = DataFrame(in_schema('DEMO_AnomalyDetection','DTW_Matrix'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42f770a5-f3b2-4862-8256-b1cc1f969750",
   "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 Hierarchical clustering with Scipy</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now the distance matrix is available, we can perform the clustering. Here, we will use the open-source package Scipy and its cluster.hierarchy modules, that have been used in a tdsense for convenience.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Hierarchical clustering is an alternative class of clustering algorithms that produce 1 to n clusters, where n is the number of observations in the data set. As you go down the hierarchy from 1 cluster (contains all the data) to n clusters (each observation is its own cluster), the clusters become more and more similar (almost always).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b87b35b-c283-42d8-845b-5c9c7851c822",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtw_matrix_loc = dtw_matrix.sort(columns=['WELDING_ID_2','WELDING_ID_1']).to_pandas(all_rows=True)\n",
    "dtw_matrix_loc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49f64fd3-1f33-4b7c-9d8f-b0636bffc2f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdsense.clustering import hierarchy_dendrogram, hierarchy_clustering\n",
    "linked, labelList = hierarchy_dendrogram(dtw_matrix_loc, cluster_distance = 'ward')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df0a3961-8cd1-43b8-9c11-9e229648d1eb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The dendrogram is useful for visualizing the structure of the hierarchical clustering and identifying the optimal number of clusters to use for further analysis. The optimal number of clusters can be determined by examining the dendrogram to identify a level at which the clusters start to merge more slowly or by using a threshold for the maximum distance between clusters.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The resulting dendrogram as above shows how the hierarchical clustering algorithm has merged the data points into clusters based on their pairwise distances using the Ward linkage criterion. The dendrogram is a summary of the distance matrix. The X axis has the WELDING_ID but not visible as we have more than 450k rows. Looking at the dendrogram, we see that we have about 6 clusters. When selected 6, here is what we have got.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2e168ff-626b-47b8-bc2b-ecfaac22a8f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cluster = hierarchy_clustering(linked, labelList, n_clusters=6)\n",
    "cluster.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48b62135-409c-45a9-b604-6e98ccf059fd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above dendogram is for only 6 clusters with the colors representing the different clusters. Now, we plot the Resistance curves for each cluster.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d31bafdc-9f43-4083-9677-ef7d94c18eb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(2,3,figsize=(20,10))\n",
    "colors = cluster[['cluster','leaves_color_list']].copy().drop_duplicates()\n",
    "for k in range(6):\n",
    "    plt.subplot(2,3,k+1)\n",
    "    img = plotcurves( subset_zoom,\n",
    "                      field='RESISTANCE',\n",
    "                      row_axis='TIME_MS',\n",
    "                      series_id='WELDING_ID',\n",
    "                      select_id=list(cluster[cluster.cluster ==k].CURVE_ID.values),\n",
    "                      noplot=True)\n",
    "    plt.imshow(img)\n",
    "    plt.title('cluster : ' +str(k) + '\\n' + str(cluster.groupby('cluster').count()['CURVE_ID'][k]) + ' obs.',fontdict = {'fontsize' : 10, 'color':colors.leaves_color_list.values[k]})\n",
    "    plt.axis('off')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f50fab99-9231-410d-bdd3-1132fc98575f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>And if we plot the curves per cluster, we spot the curves with a sharp drop(cluster 4) and these are the curves we are interested in, i.e. the curve exhibiting the anomaly we are looking for. We note also the other clusters are looking more or less similar. By monitoring the resistance over time and calculating its derivative, you can detect any sudden changes or anomalies. Anomalies might indicate a problem with the welding process, such as a sudden drop in current or a sudden increase in resistance. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b99a7ac-6a99-4c9e-9ead-0f6d6e5c4759",
   "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 Create the anomaly dataset</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now we create a table containing the anomaly flag that will be the target of a supervised machine learning model or a relevant KPI to monitor in production dashboards.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cec5b577-b0dd-45c8-8fad-fee1fb1f952a",
   "metadata": {},
   "outputs": [],
   "source": [
    "target = cluster.copy().drop('leaves_color_list',axis=1)\n",
    "target = target[target.cluster.isin([1,2])]\n",
    "target['WELDING_ID'] = target['CURVE_ID']\n",
    "target['anomaly'] = 0\n",
    "target.loc[target.cluster==2,'anomaly'] = 1\n",
    "target.drop(['cluster','CURVE_ID'],axis=1, inplace=True)\n",
    "target.groupby('anomaly').count().plot(y='WELDING_ID',kind='bar',figsize=(10,10))\n",
    "copy_to_sql( target,\n",
    "                  table_name = 'Anomaly_Target',\n",
    "                  if_exists='replace',\n",
    "                  primary_index='WELDING_ID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ac7c451-2fb3-45fa-895d-e881cc88a9ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "anomalies = DataFrame('Anomaly_Target')\n",
    "anomalies"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da6297fd-6f49-4619-af30-791db2af90da",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above anomaly data has the welding ID and the anomaly flag.</p>\n",
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.4 Build the analytical dataset </b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We prepare the analytical dataset by joining the feature table with the anomaly table using the Welding ID so that we get the anomalies for the weldings.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe4cfcfb-7d91-47e5-a4cc-e44428e51cfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "ADS = features[['WELDING_ID']+feature_names].join(other=anomalies, how='inner', on='WELDING_ID=WELDING_ID',rsuffix='r',lsuffix='l')\n",
    "ADS = ADS.assign(WELDING_ID=ADS.WELDING_ID_l).drop(['WELDING_ID_l','WELDING_ID_r'],axis=1).select(['WELDING_ID']+feature_names+['anomaly'])\n",
    "ADS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20a2163c-9fea-4f3d-ab0b-696b3cccaad9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "ADS.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c03b26f4-0fa4-4478-922e-9cb850acbe34",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The analytical dataset we created has 14 columns and 391 rows which will be used to build the model below.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09b3168b-8c53-4ffd-ba75-b26f40608654",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Build the model </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have datasets in which different columns have different units – like one column can be in kilograms, while another column can be in centimetres. If we feed these features to the model as is, there is every chance that one feature will influence the result more due to its value than the others. But this doesn’t necessarily mean it is more important as a predictor. So, to give importance to all the features we need feature scaling.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we apply the Standard scale and transform functions which are ScaleFit and ScaleTransform functions in Vantage. ScaleFit() function outputs statistics to input to ScaleTransform() function, which scales specified input DataFrame columns.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5d0898e-53a7-4aca-9f24-2e2f06ac73dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ScaleFit , ScaleTransform\n",
    "scaler = ScaleFit(\n",
    "                    data=ADS,\n",
    "                    target_columns=feature_names,\n",
    "                    scale_method=\"STD\",\n",
    "                    global_scale=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76af7c0a-b1cf-4914-a099-aeaeeb0c4977",
   "metadata": {},
   "outputs": [],
   "source": [
    "ADS_scaled = ScaleTransform(data=ADS,\n",
    "                         object=scaler.output,\n",
    "                         accumulate=\"anomaly\").result\n",
    "ADS_scaled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cc1ed77-bd6e-4476-9b76-abb448c7199b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = ADS_scaled.to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b3a8548-555a-48fd-88e4-795abaff2cc5",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.1 Create a model file using the python libraries.</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Vantage Bring Your Own Model (BYOM) package gives data scientists and analysts the ability to operationalize predictive models in Vantage. Predictive models trained in external tools with sample data can be used to score data stored in Vantage using the BYOM Predict. Create or convert your predictive model using a supported model interchange format (PMML, MOJO, ONNX, Dataiku, and DataRobot are currently available), store it in a Vantage table, and use the BYOM PMMLPredict, H2OPredict, ONNXPredict, DataikuPredict, or DataRobotPredict to score your data with the model.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A problem with imbalanced classification is that there are too few examples of the minority class for a model to effectively learn the decision boundary. One way to solve this problem is to oversample the examples in the minority class. the most widely used approach to synthesizing new examples is called the Synthetic Minority Oversampling Technique, or SMOTE for short. SMOTE works by selecting examples that are close in the feature space, drawing a line between the examples in the feature space and drawing a new sample at a point along that line.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Then we use the RandomForestClassifier to create the model. A random forest is a meta estimator that fits a number of decision tree classifiers on various sub-samples of the dataset and uses averaging to improve the predictive accuracy and control over-fitting. The Random forest classifier creates a set of decision trees from a randomly selected subset of the training set. It is basically a set of decision trees (DT) from a randomly selected subset of the training set and then It collects the votes from different decision trees to decide the final prediction.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d847d16a-9735-4482-953d-66c80faf0bdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train = df[feature_names]\n",
    "y_train = df['anomaly']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4350a66c-2ff9-483c-ae30-8f17c5d375b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Balance the training set using SMOTE\n",
    "smote = SMOTE(random_state=42)\n",
    "X_train, y_train = smote.fit_resample(X_train, y_train)\n",
    "\n",
    "\n",
    "# Create a random forest classifier\n",
    "model = RandomForestClassifier(n_estimators=10,max_depth= 3, random_state=42)\n",
    "\n",
    "# Create a pipeline that includes the SMOTE transformer and the model\n",
    "pipeline = PMMLPipeline([ ('model', model)])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "455a3ff5-e8ee-4c9b-909e-3e1a79fa6612",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Train the pipeline\n",
    "start = time.time()\n",
    "pipeline.fit(X_train, y_train)\n",
    "end = time.time()\n",
    "print('duration : ', end-start, 's')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61ff634a-aea7-4966-bf38-30b77547f0a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# make predictions on the training set\n",
    "y_train_pred = pipeline.predict(X_train)\n",
    "\n",
    "# calculate and print the accuracy score\n",
    "acc = accuracy_score(y_train, y_train_pred)\n",
    "print(\"Accuracy: {:.2f}%\".format(acc * 100))\n",
    "\n",
    "# calculate and print precision, AUC and F1-score\n",
    "prec = precision_score(y_train, y_train_pred)\n",
    "print(\"Precision: {:.2f}%\".format(prec * 100))\n",
    "\n",
    "# calculate AUC, AUC requires probability for positive class\n",
    "prob = pipeline.predict_proba(X_train)[:, 1]\n",
    "auc = roc_auc_score(y_train, prob)\n",
    "print(\"AUC: {:.2f}%\".format(auc * 100))\n",
    "\n",
    "f1 = f1_score(y_train, y_train_pred)\n",
    "print(\"F1-Score: {:.2f}%\".format(f1 * 100))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60a0b3c9-4a3f-478c-a9f9-2ddd786aa332",
   "metadata": {},
   "outputs": [],
   "source": [
    "pmml_metrics=pd.DataFrame([{'Model':'PMML using BYOM','Accuracy':acc, 'Precision':prec, 'F1-Score':f1}])\n",
    "pmml_metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da084cfa-5c7b-4899-9c9b-41b065546bf6",
   "metadata": {},
   "outputs": [],
   "source": [
    "sklearn2pmml(pipeline, \"my_model.pmml\", with_repr = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c35b23c2-c4c4-4601-b374-9d021a4845b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "additional_columns = {\"Description\": type(\"RandomForestClassifier model\"),\n",
    "                              \"UserId\": type('demo_user'),\n",
    "                              \"ProductionReady\": False,\n",
    "                              \"ModelAccuracy\": float(acc),\n",
    "                              \"ModelPrecision\": prec,\n",
    "                              \"ModelAUC\": auc,\n",
    "                              \"Modelf1Score\": f1,\n",
    "                              \"ModelSavedTime\": str(datetime.datetime.now(tz=pytz.UTC)),\n",
    "                              \"ModelGeneratedTime\": end-start,\n",
    "                              \"sklearnVersion\": sklearn.__version__\n",
    "                             }\n",
    "for k in additional_columns.keys():\n",
    "    print(type(additional_columns[k]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8351d68c-fed5-4034-b00f-fe0379625090",
   "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 Save the model file</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ffc1be2-d980-4468-9fc9-58ef30e5cb27",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    save_byom(model_id = 'model_anomaly1',\n",
    "          model_file = 'my_model.pmml',\n",
    "          table_name = 'BYOM_PMMLMODELS_REPOSITORY',\n",
    "          additional_columns={\"Description\": \"RandomForestClassifier model\",\n",
    "                              \"UserId\": 'demo_user',\n",
    "                              \"ProductionReady\": False,\n",
    "                              \"ModelAccuracy\": float(acc),\n",
    "                              \"ModelPrecision\": float(prec),\n",
    "                              \"ModelAUC\": float(auc),\n",
    "                              \"Modelf1Score\": float(f1),\n",
    "                              \"ModelSavedTime\": str(datetime.datetime.now(tz=pytz.UTC)),\n",
    "                              \"ModelGeneratedTime\": float(end-start),\n",
    "                              \"sklearnVersion\": sklearn.__version__\n",
    "                             }\n",
    "            )\n",
    "except Exception as e: \n",
    "    # if our model exists, delete and rewrite \n",
    "    if str(e.args).find('TDML_2200') >= 1: \n",
    "        delete_byom(model_id = 'model_anomaly1', table_name = 'BYOM_PMMLMODELS_REPOSITORY') \n",
    "        save_byom(model_id = 'model_anomaly1',\n",
    "              model_file = 'my_model.pmml',\n",
    "              table_name = 'BYOM_PMMLMODELS_REPOSITORY',\n",
    "              additional_columns={\"Description\": \"RandomForestClassifier model\",\n",
    "                              \"UserId\": 'demo_user',\n",
    "                              \"ProductionReady\": False,\n",
    "                              \"ModelAccuracy\": float(acc),\n",
    "                              \"ModelPrecision\": float(prec),\n",
    "                              \"ModelAUC\": float(auc),\n",
    "                              \"Modelf1Score\": float(f1),\n",
    "                              \"ModelSavedTime\": str(datetime.datetime.now(tz=pytz.UTC)),\n",
    "                              \"ModelGeneratedTime\": float(end-start),\n",
    "                              \"sklearnVersion\": sklearn.__version__\n",
    "                             }\n",
    "            )\n",
    "    else:    \n",
    "        raise ValueError(f\"Unable to save the model due to the following error: {e}\")\n",
    "#     pass \n",
    "# else: \n",
    "#     raise    \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60c0f97c-52b2-407e-921c-75a61ca2d3fa",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The model file is saved as can be found in the left navigation pane in /UseCases/Anomaly_Detection.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create new scaled data to apply this model and predict data. New dataset is created by joining the features and the anomalies.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60fe7dff-a0fa-43a6-aa03-d11aeed2904e",
   "metadata": {},
   "outputs": [],
   "source": [
    "newdata = features[['WELDING_ID']+feature_names].join(other=anomalies, how='inner', on='WELDING_ID=WELDING_ID',rsuffix='r',lsuffix='l')\n",
    "newdata = newdata.assign(WELDING_ID=newdata.WELDING_ID_l).drop(['WELDING_ID_l','WELDING_ID_r'],axis=1).select(['WELDING_ID']+feature_names+['anomaly'])\n",
    "newdata"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd7108ab-49b6-411a-a919-4ab7f859252e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create new transformed data by using the same Scalefit object we used earlier and get the transformed data for this new data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "099b4d80-3bb8-4e96-ba57-c85c84ae990a",
   "metadata": {},
   "outputs": [],
   "source": [
    "newdata_scaled = ScaleTransform(data=newdata,\n",
    "                         object=scaler.output,\n",
    "                                # DataFrame(in_schema('demo_user','scaler_anomaly')),\n",
    "                         accumulate=[\"WELDING_ID\",\"anomaly\"]).result\n",
    "newdata_scaled"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46bb63a9-35eb-40e9-a4d4-d1aa558b19d1",
   "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 Retrieve the model file and use it to predict</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use the PMMLPredict function from the teradataml library to predict the anomalies.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Predictive Model Markup Language (PMML) is an XML-based standard established by the Data Mining Group (DMG) for defining statistical and data-mining models. PMML models can be shared between PMML-compliant platforms and across organizations so that business analysts and developers are unified in designing, analyzing, and implementing PMML-based assets and services.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16f0c6bb-3551-4337-a4e3-8c2a79fd55cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import PMMLPredict\n",
    "modeldata_anomaly = retrieve_byom(\"model_anomaly1\", table_name=\"BYOM_PMMLMODELS_REPOSITORY\")\n",
    "result=PMMLPredict(\n",
    "                modeldata = modeldata_anomaly,\n",
    "                newdata = newdata_scaled,\n",
    "                accumulate = ['WELDING_ID'],\n",
    "                model_output_fields=['probability(0)','probability(1)'],\n",
    "                overwrite_cached_models = '*'\n",
    "                )\n",
    "pmml_predict=result.result\n",
    "pmml_predict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f03ec30-32a9-4b13-af64-78eaa88b79e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "pmml_predict_result = pmml_predict.join(other=newdata_scaled, how='inner', on='WELDING_ID=WELDING_ID',rsuffix='r',lsuffix='l')\n",
    "pmml_predict_result = pmml_predict_result.assign(prob_0=pmml_predict_result['probability(0)'])\n",
    "pmml_predict_result = pmml_predict_result.assign(prob_1=pmml_predict_result['probability(1)'])\n",
    "pmml_predict_result = pmml_predict_result.assign(WELDING_ID=pmml_predict_result.WELDING_ID_l)\n",
    "pmml_predict_result = pmml_predict_result.assign(prediction=case([(pmml_predict_result.prob_1>pmml_predict_result.prob_0, 1 )],else_ = 0))\n",
    "pmml_predict_result = pmml_predict_result.select(['WELDING_ID']+['anomaly']+['prob_0']+['prob_1']+['prediction'])\n",
    "pmml_predict_result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "220bb477-2d63-4672-98a1-cb50d40f960f",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Decision Forest </b></p>\n",
    " \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will now use the DecisionForest model to predict the anomalies. A decision forest is a generic term to describe models made of multiple decision trees. The prediction of a decision forest is the aggregation of the predictions of its decision trees. The implementation of this aggregation depends on the algorithm used to train the decision forest. The goal of using a Decision Tree is to create a training model that can use to predict the class or value of the target variable by learning simple decision rules inferred from prior data(training data).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We start by creating a subset for the most interesting part lies between 40 and 400ms from the start of the curve.</p>\n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf8a84c6-2c67-43c7-86e2-1f31c6bd1c18",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_curves_zoom = welding_dataset_new[(welding_dataset_new.TIME_MS > 40) & (welding_dataset_new.TIME_MS < 400) ]\n",
    "DF_curves_zoom"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58c9f479-f2ff-4863-b969-b9b8a873e6d4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create various features by using the window function on the Resistance and taking the difference between the previous and current resistance based on time. We will create these features by using the aggregation function on this resistance and the difference of the resistance.</p>\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a227337c-3b57-443c-a256-dd5230ed98dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_curves_zoom = DF_curves_zoom.assign(\n",
    "    resistance_diff = DF_curves_zoom.RESISTANCE \n",
    "                        - DF_curves_zoom.RESISTANCE.window(\n",
    "                                partition_columns=['WELDING_ID'],\n",
    "                                order_columns=[\"TIME_MS\"]\n",
    "                            ).lag(1)\n",
    ")\n",
    "# DF_curves_zoom[DF_curves_zoom.WELDING_ID==138].sort(\"TIME_MS\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb8c00e7-c465-46ba-99ae-c094969a2eed",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_features = DF_curves_zoom.groupby(\"WELDING_ID\").agg({\n",
    "    'RESISTANCE':['sum', 'min', 'max', 'mean', 'std', 'var','skew','kurtosis'],\n",
    "    'resistance_diff':['min']\n",
    "})\n",
    "DF_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6498373-8b50-49fb-ac0b-b0db7b0cb522",
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_names = DF_features.columns[1:]\n",
    "feature_names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57712977-e195-4ce9-9867-a7cdbc772279",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.1 Build the analytical dataset.</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create the analytical dataset joining the anomaly table created above and the dataset with the features created.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55686241-b413-45eb-a495-9888c946c634",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "DF_target = DataFrame('Anomaly_Target')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f0b595e-d794-4797-9125-b0bd2e9b046a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "DF_ADS_train = DF_features[['WELDING_ID']+feature_names].join(\n",
    "    other=DF_target, how='inner', on='WELDING_ID=WELDING_ID',rsuffix='r',lsuffix='l')\n",
    "DF_ADS_train = DF_ADS_train.assign(WELDING_ID=DF_ADS_train.WELDING_ID_l\n",
    "                                  ).drop(['WELDING_ID_l','WELDING_ID_r'],axis=1\n",
    "                                        ).select(['WELDING_ID']+feature_names+['anomaly']\n",
    "                                                ).assign(anomaly_int = DF_ADS_train.anomaly.cast(INTEGER()))\n",
    "DF_ADS_train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0199e5db-a881-4a2e-92df-0fcc0a54158b",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_ADS_score = DF_features[['WELDING_ID']+feature_names]\\\n",
    "                            [DF_features.WELDING_ID>800]\n",
    "DF_ADS_score"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3865607-6205-43e4-a3be-2142af2dd340",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We store these training and scoring datasets into Vantage to be used by the In-DB functions.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30d0d263-3183-4e37-aa05-6f5ccd61ac49",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_ADS_train.to_sql(\n",
    "    table_name  = 'ADS_train_data',\n",
    "    primary_index= 'WELDING_ID',\n",
    "    if_exists = 'replace'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db8815e7-cdc4-40fb-9160-bfd466d7535f",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_ADS_score.to_sql(\n",
    "    table_name  = 'ADS_test_data',\n",
    "    primary_index= 'WELDING_ID',\n",
    "    if_exists = 'replace'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f38cc3c9-6828-4c65-9b72-53ea02a172cd",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.2 Train Decision Forest</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <a href = 'https://docs.teradata.com/search/all?query=TD_DecisionForest&content-lang=en-US'>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 style = 'font-size:16px;font-family:Arial;color:#00233C'>InputColumns; list or range of columns used as features (we used an ordinal reference of columns 2:217)</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>ResponseColumn; the dependent or target value (we used “class”, the first column)</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>TreeType; either CLASSIFICATION or REGRESSION</li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Other hyperparameter values detailed in the documentation</li>\n",
    "        </ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5217c805-010b-4184-b312-b22c7f0b1d49",
   "metadata": {},
   "outputs": [],
   "source": [
    "DecisionForest_out = DecisionForest(data = DF_ADS_train, \n",
    "                            input_columns = ['sum_RESISTANCE', 'min_RESISTANCE', 'max_RESISTANCE', 'mean_RESISTANCE', 'std_RESISTANCE', 'var_RESISTANCE', 'skew_RESISTANCE',\n",
    " 'kurtosis_RESISTANCE', 'min_resistance_diff'], \n",
    "                            response_column = 'anomaly_int', \n",
    "                            max_depth = 16, \n",
    "                            num_trees = 8, \n",
    "                            min_node_size = 1, \n",
    "                            mtry = 1, \n",
    "                            mtry_seed = 3, \n",
    "                            seed = 3, \n",
    "                            tree_type = 'CLASSIFICATION')\n",
    "# Print the result DataFrame.\n",
    "# print(DecisionForest_out.result)  \n",
    "          "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aca1ef54-8f11-48af-9d9f-ffe19a08b050",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.3 Predict and Evaluate Decision Forest model</b></p>\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'>ClassificationEvaluator</a> SQL Function.</p>\n",
    "\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>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 style = 'font-size:16px;font-family:Arial;color:#00233C'>Execute <a href = 'https://docs.teradata.com/search/all?query=TD_ClassificationEvaluator&content-lang=en-US'>ClassificationEvaluator</a> and pass the actual classification and the predicted value</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12ca4daf-6b7f-453b-b690-3ca59df0fb6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "decision_forest_predict_out = TDDecisionForestPredict(object = DecisionForest_out.result,\n",
    "                                                        newdata = DF_ADS_train,\n",
    "                                                        id_column = \"WELDING_ID\",\n",
    "                                                        detailed = False,\n",
    "                                                        output_prob = True,\n",
    "                                                        output_responses = ['0','1'],\n",
    "                                                        accumulate = 'anomaly_int')\n",
    "decision_forest_predict_out.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46f0ff60-f38b-40a4-aafe-0d644a284e8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_predict= DataFrame('DF_Predict')\n",
    "df_predict = decision_forest_predict_out.result\n",
    "df_predict_char = df = df_predict.assign(anomaly = df_predict.anomaly_int.cast(type_=VARCHAR(2))\n",
    "                                        ,prediction_ch = df_predict.prediction.cast(type_=VARCHAR(2)))\n",
    "df_predict_char"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7844e297-5036-4ea2-bf0d-aff9b7c8d5d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "ClassificationEvaluator_obj = ClassificationEvaluator(data=df_predict_char,\n",
    "                                                          observation_column='anomaly',\n",
    "                                                          prediction_column='prediction_ch',\n",
    "                                                          labels=['0','1'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c2c4cd5-6f62-4ad3-a8b0-3db15872b87c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_metrics = ClassificationEvaluator_obj.output_data\n",
    "df_metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c771f8db-68a9-4995-bcc9-872892b8bb85",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_metric_pd = df_metrics.to_pandas(all_rows = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1c78bb0-a809-4a71-bc77-3f2d3bf31285",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_metric_pd['Metric'] = df_metric_pd['Metric'].str.strip('\\x00')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7771b01-523d-4737-b7ef-75246f87d7f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "accuracy = df_metric_pd[df_metric_pd['Metric'] == 'Accuracy']['MetricValue'][0]\n",
    "precision = df_metric_pd[df_metric_pd['Metric'] == 'Micro-Precision']['MetricValue'][1]\n",
    "f1score = df_metric_pd[df_metric_pd['Metric'] == 'Micro-F1']['MetricValue'][3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07bf6fba-e68c-4a78-9eae-5aa134aa3655",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_metrics_new=pd.DataFrame([{'Model':'In-DB DecisionForest','Accuracy':accuracy, 'Precision':precision, 'F1-Score':f1score}])\n",
    "df_metrics_new"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0311a230-7911-4859-8658-4130c893b72d",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.4 Score new Data</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5fde359-12da-4038-963c-c34ce410de04",
   "metadata": {},
   "outputs": [],
   "source": [
    "decision_forest_predict_test_out = TDDecisionForestPredict(object = DecisionForest_out.result,\n",
    "                                                        newdata = DF_ADS_score,\n",
    "                                                        id_column = \"WELDING_ID\",\n",
    "                                                        detailed = False,\n",
    "                                                        output_prob = True,\n",
    "                                                        output_responses = ['0','1'])\n",
    "decision_forest_predict_test_out.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58448648-772a-445e-9989-8b174ac9db2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_predict_test= DataFrame('DF_Predict_test')\n",
    "df_predict_test=decision_forest_predict_test_out.result\n",
    "df_predict_test"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cda02bba-235d-4f1a-b2a7-3e2ea619cce2",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Compare PMML and DecisionForest</b></p>\n",
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>8.1 Show AUC-ROC Curve</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_ROC&content-lang=en-US'>ROC</a> curve shows the performance of a binary classification model as its discrimination threshold varies. For a range of thresholds, the curve plots the true positive rate against false-positive rate.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This function accepts a set of prediction-actual pairs as input and calculates the following values for a range of discrimination thresholds.</p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>True-positive rate (TPR)</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>False-positive rate (FPR)</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>The area under the ROC curve (AUC)</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Gini coefficient</li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Other details are mentioned in the documentation</li>\n",
    "    </ul>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>ROC for PMML</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c4b179b-a334-4dc0-b3f8-71c35f87283e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ROC \n",
    "roc_pmml = ROC(data = pmml_predict_result, \n",
    "                    probability_column = \"prob_1\",\n",
    "                    observation_column = \"anomaly\",\n",
    "                    positive_class=\"1\"\n",
    "                    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32b946fb-e09e-4e62-b78a-c5325d84c175",
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_data_pmml = roc_pmml.output_data.to_pandas().sort_values(\"fpr\", ascending=True)\n",
    "roc_data_pmml.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de67ebb4-b0f9-4a8c-9559-e6a44f1c9a21",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "auc_pmml = roc_pmml.result.to_pandas().iloc[0,0]\n",
    "auc_pmml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "baf0989e-387a-4ee9-b99e-0687d5a97799",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>ROC for DecisionForest</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02a1c9e2-be8c-44da-9e0a-9056a2ec8243",
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_obj = ROC(data = df_predict, \n",
    "                    probability_column = \"prob_1\",\n",
    "                    observation_column = \"anomaly_int\",\n",
    "                    positive_class=\"1\"\n",
    "                    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27834036-13cc-49e9-a34e-b2bcb2c192b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_data = roc_obj.output_data.to_pandas().sort_values(\"fpr\", ascending=True)\n",
    "roc_data.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab90afd6-b0c1-4edd-9492-c97b16c8d4e0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "auc = roc_obj.result.to_pandas().iloc[0,0]\n",
    "auc"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abb98428-872c-41d5-b8b1-79804c772a8a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Plot ROC Curves</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93ab97d1-cbd3-4044-8546-0f170a5ca9ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot 1\n",
    "plt.plot(roc_data_pmml['fpr'], roc_data_pmml['tpr'], color='orange', label='PMML ROC. AUC = {}'.format(str(auc_pmml)), drawstyle='steps') \n",
    "# Plot 2\n",
    "plt.plot(roc_data['fpr'], roc_data['tpr'], color='green', label='DecisionForest ROC. AUC = {}'.format(str(auc)),  drawstyle='steps') \n",
    "# Plot the diagonal dashed line\n",
    "plt.plot([0, 1], [0, 1], color='darkblue', linestyle='--') \n",
    "# Set labels and title\n",
    "plt.xlabel('False Positive Rate',fontsize=12) \n",
    "plt.ylabel('True Positive Rate',fontsize=12) \n",
    "plt.title('Receiver Operating Characteristic (ROC) Curve',fontsize=16) \n",
    "# Add legend\n",
    "plt.legend(loc=\"lower right\",fontsize=10) \n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c721c745-be69-4eee-a8e2-9faa4ecff46e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The closer the ROC curve is to the upper left corner of the graph, the higher the accuracy of the test because in the upper left corner, the sensitivity = 1 and the false positive rate = 0 (specificity = 1). The ideal ROC curve thus has an AUC = 1.0. As seen in the above graph the AUC for both the models is close to 1 so the accuracy of both models is very good. </p>\n",
    "\n",
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>8.2 Show Confusion Matrix</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Confusion Matrix is a performance measurement for machine learning classification problem where output can be two or more classes. It is a table with 4 different combinations of predicted and actual values.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Confusion matrices represent counts from predicted and actual values. The output “TN” stands for True Negative which shows the number of negative examples classified accurately. Similarly, “TP” stands for True Positive which indicates the number of positive examples classified accurately. The term “FP” shows False Positive value, i.e., the number of actual negative examples classified as positive; and “FN” means a False Negative value which is the number of actual positive examples classified as negative.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0cac3275-2854-464a-b240-03e7b836b96d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate confusion matrix for PMML\n",
    "DF_result=df_predict.to_pandas().reset_index()\n",
    "pmml_result=pmml_predict_result.to_pandas()\n",
    "cm_pmml = confusion_matrix(pmml_result['anomaly'], pmml_result['prediction']) \n",
    "# Calculate confusion matrix for DecisionForest\n",
    "cm_df = confusion_matrix(DF_result['anomaly_int'], DF_result['prediction']) \n",
    "# Create figure and axes objects\n",
    "fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(16, 8)) \n",
    "# Plot PMML confusion matrix\n",
    "disp_pmml = ConfusionMatrixDisplay(confusion_matrix=cm_pmml, display_labels=['No Anomaly', 'Anomaly']) \n",
    "disp_pmml.plot(ax=ax1, cmap='Blues', colorbar=False) \n",
    "ax1.set_title('PMML Confusion Matrix') \n",
    "ax1.set_xlabel('Predicted Label') \n",
    "ax1.set_ylabel('True Label') \n",
    "ax1.set_xticks([0, 1]) \n",
    "ax1.set_yticks([0, 1]) \n",
    "ax1.set_xticklabels(['No Anomaly', 'Anomaly']) \n",
    "ax1.set_yticklabels(['No Anomaly', 'Anomaly'])\n",
    "\n",
    "# Add text to the plot to show the actual values of the confusion matrix\n",
    "for i in range(cm_pmml.shape[0]): \n",
    "    for j in range(cm_pmml.shape[1]): \n",
    "        ax1.text(j, i, f'{cm_pmml[i, j]}', ha='center', va='center', color='white' if cm_pmml[i, j] > cm_pmml.max() / 2 else 'black') \n",
    "\n",
    "# Plot DecisionForest confusion matrix\n",
    "disp_df = ConfusionMatrixDisplay(confusion_matrix=cm_df, display_labels=['No Anomaly', 'Anomaly']) \n",
    "disp_df.plot(ax=ax2, cmap='Blues', colorbar=False) \n",
    "ax2.set_title('DecisionForest Confusion Matrix') \n",
    "ax2.set_xlabel('Predicted Label') \n",
    "ax2.set_ylabel('True Label') \n",
    "ax2.set_xticks([0, 1]) \n",
    "ax2.set_yticks([0, 1]) \n",
    "ax2.set_xticklabels(['No Anomaly', 'Anomaly']) \n",
    "ax2.set_yticklabels(['No Anomaly', 'Anomaly'])\n",
    "\n",
    "# Add text to the plot to show the actual values of the confusion matrix\n",
    "for i in range(cm_df.shape[0]): \n",
    "    for j in range(cm_df.shape[1]): \n",
    "        ax2.text(j, i, f'{cm_df[i, j]}', ha='center', va='center', color='white' if cm_df[i, j] > cm_df.max() / 2 else 'black') \n",
    "\n",
    "# Adjust layout and spacing\n",
    "plt.tight_layout() \n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f7bd547-6020-42c0-b2a7-d1938a9bdb30",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The confusion matrix for this binary class classification problem has the below 4 quadrants: </p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>True Positive (TP) refers to a sample belonging to the positive class being classified correctly.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>True Negative (TN) refers to a sample belonging to the negative class being classified correctly.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>False Positive (FP) refers to a sample belonging to the negative class but being classified wrongly as belonging to the positive class.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>False Negative (FN) refers to a sample belonging to the positive class but being classified wrongly as belonging to the negative class.</li>\n",
    "\n",
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>8.3 Show Metrices</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Below is the comparison for Accuracy, Precision and F1-Score of the 2 models.</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>Accuracy</td>\n",
    "    <td>Accuracy simply measures how often the classifier correctly predicts. We can define accuracy as the ratio of the number of correct predictions and the total number of predictions.</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",
    "</table>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5559331d-05a2-417a-aa23-cd9eae401b9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "combined_metrics=pd.concat([pmml_metrics, df_metrics_new], axis=0)\n",
    "combined_metrics"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41344788-3979-4546-a6e2-ae16b6dccb79",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above metrics we can conclude that both the models are performing almost similar and have similar Accuracy and Precision.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43be6263-22d8-43d2-94e2-1f58d730f567",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b> Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have seen an end-to-end exploration process for labelling anomalous time series using ClearScape Analytics on Teradata Vantage. Thanks to the in-database capabilities offered by Teradata Vantage with ClearScape Analytics, we were able to run this exploration with the smallest notebook instance. The unique massively-parallel architecture of Teradata Vantage allows users to prepare data, train, evaluate, and deploy models at unprecedented scale.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this particular use case, we have observed that with large volume of machine sensor data millions of ML models were created to derive analytic features that ultimately deployed tens of thousands of models for real-time scoring. This extent of scale is only possible by combining the power of Vantage with native ClearScape Analytic functions.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29e90d19-1b71-44e8-b6d5-aa53e3b673c1",
   "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": "48a959e6-319f-4592-93af-482d391224b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['ADS_train_data', 'ADS_test_data','DF_train', 'DF_Predict', 'DF_Predict_test','additional_metrics_test']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name=table)\n",
    "    except:\n",
    "        pass    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "603da4b4-fb07-45f1-8c39-8b0e365c2cec",
   "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": "aabf7acc-2df8-48fa-a92a-cc871a94fa11",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_AnomalyDetection');\" \n",
    "#Takes 40 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bbf8f9bc-9f3a-47e9-b2d4-81fd00291bc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d51fd98f-b9b2-48b9-b639-16cc51f9116f",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. Exploring the Versatility of this Analytical Approach in Alternative Use Case Settings</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:##00233C'><b>How this analytic approach can be levaraged in other use case settings</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:##00233C'>The analytical approach of leveraging clustering followed by classification for anomaly detection in short time series data is highly adaptable and can be broadly applied across various industries, especially in settings where operations or processes are characterized by short, continuous time series with a defined start and end and where ground truth labels are not initially available.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:##00233C'>This method begins with unsupervised learning to explore and understand the data, identifying patterns, similarities, and potential outliers through techniques like Dynamic Time Warping (DTW). Such exploration is crucial in settings where anomalies are not predefined or where the data’s inherent complexity requires initial unsupervised insight to develop an understanding of what constitutes normal behavior versus an anomaly. Following the clustering phase, supervised classification models are trained on the newly identified labels to predict anomalies. This generic approach is particularly effective for short time series data, where each sequence represents a process or event whose normal operational parameters need to be defined through exploratory analysis before precise anomaly detection can occur.</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:##00233C'><b>Potential Use Cases Across Industries:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:##00233C'><b> Telco & Utilities</b> <code>- Power Grid Load Monitoring:</code> Analyzing short time series of electricity load during peak usage times to identify anomalies that could indicate equipment failure, energy theft, or inefficiencies in power distribution. Each series could represent the load profile for a brief, high-demand period.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:##00233C'><b>Healthcare</b> <code>- ECG or EEG Analysis:</code> Short segments of electrocardiogram (ECG) or electroencephalogram (EEG) readings can be analyzed to detect anomalies indicating cardiac arrhythmias or neurological issues, respectively. Each segment represents a complete heartbeat or a brief brain activity pattern.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:##00233C'><b>Manufacturing</b> <code>- CNC Machine Operations:</code> Monitoring the torque and force profiles of a CNC (Computer Numerical Control) machine during a single machining operation. Anomalies could indicate tool wear, material inconsistency, or operational errors.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:##00233C'><b>Travel & Transport</b> <code>- Aircraft Engine Test Runs:</code> Analyzing the time series data of engine parameters (e.g., temperature, pressure, vibration) during short test runs to identify deviations from normal operational profiles, suggesting maintenance or safety issues.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:##00233C'><b>Hospitality & Entertainment</b> <code>- Theme Park Ride Operations:</code> Analyzing sensor data from individual rides, where each ride cycle produces a time series of mechanical or operational parameters. Anomalies in these series could indicate safety concerns or maintenance needs.</li></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:##00233C'><b>Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:##00233C'>In each of these scenarios, the focus is on analyzing the shape or behavior of a curve within a short time frame, similar to observing a spot welding curve. These curves are shaped by the specific activity taking place, whether it’s a machine at work, a health test running, financial trades happening, or people interacting with a service. The method begins by sorting these curves into groups based on their patterns, without needing to know ahead of time which ones are out of the ordinary. Then, it moves on to use a more detailed approach to pinpoint which curves don’t fit the expected pattern, labeling them as either normal or not normal. This way of doing things is great for quickly finding and addressing issues, and it also helps in getting a better grasp of how these processes work. This can lead to making things run more smoothly and keeping equipment in good shape before problems even start.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91bd8857-19e0-4200-b3ae-b2efdbca73d3",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Resources</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let’s look at the elements we have available for reference for this notebook:</p>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Filters:</b> \n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Industry:</b> Manufacturing</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Functionality:</b> Machine Learning</li> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use Case:</b> Anomaly Detection</li></p>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Related Resources:</b>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/Blogs/Hyper-scale-time-series-forecasting-done-right'>Hyper-scale time series forecasting done right</a> </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/Resources/Datasheets/Stay-Ahead-of-Rapid-Change-with-a-Dynamic-Supply-Chain?utm_campaign=i_coremedia-AMS&utm_source=google&utm_medium=paidsearch&utm_content=GS_CoreMedia_NA-US_BKW&utm_creative=Brand-Vantage&utm_term=teradata%20analytic%20platform&gclid=Cj0KCQjwnMWkBhDLARIsAHBOftrWZxDktHkKMsaWjMmNRnQ6Ys-bZBAUhXjWTo1Xa02fsci-IHWBV_waAppkEALw_wcB'>Stay Ahead of Continuous and Rapid Change with a Dynamic Supply Chain</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/Industries/Manufacturing'>Achieve industry 4.0 using advanced manufacturing analytics at scale</a></li>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1da48da7-d4de-4693-9365-5d5f63810673",
   "metadata": {
    "tags": []
   },
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "<div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "<div style=\"float:right;\">\n",
    "<div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023, 2024. All Rights Reserved\n",
    "</div>\n",
    "</div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}