UseCases/Remaining_Useful_Life_Forecasting/Remaining_Useful_Life_Forecasting_PY_SQL.ipynb

{
    "cells": [
        {
            "cell_type": "markdown",
            "id": "38bc532d-c78c-4c89-b191-242da0733f39",
            "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",
                "       Predictive Maintenance using Vantage\n",
                "  <br>\n",
                "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
                "    </p>\n",
                "</header>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "fff71661-19b4-423a-867a-7c815b064c81",
            "metadata": {},
            "source": [
                "<p style = 'font-size:20px;font-family:Arial;color:#00233c'><b>Introduction:</b></p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Predictive maintenance of production lines is important to early detect possible defects and thus identify and apply the required maintenance activities to avoid possible breakdowns. An important concern in predictive maintenance is the prediction of remaining useful life (RUL), which is an estimate of the number of remaining years that a component in a production line is estimated to be able to function in accordance with its intended purpose before warranting replacement.</p>\n",
                "\n",
                "<center><img src=\"images/turbofan.jpg\" alt=\"TurboFan\" width=400 height=400/></center>\n",
                "<p>image source: <a href=\"https://unsplash.com/photos/OjxpywWo9HI\">unsplash.com</a></p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To achieve the goal of predict the RUL (Remaining Useful Life) of turbofan engines, We will be leveraging the power of <b>Teradata Vantage</b>, an advanced analytics platform. With Teradata Vantage, we can deploy machine learning algorithms through teradataml python library, which enable us to identify and mitigate potential machine failures before they even occur.</p>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Teradata Vantage provides us with the necessary capabilities to analyze the vast amounts of data collected for turbofan engines , such as engine number, sensor measurement, and operational settings. By processing this data and detecting anomalies or patterns, we can take proactive measures to address potential issues, preventing costly downtimes and ensuring the longevity of the machines.</p>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>With Teradata Vantage, we can help to client stay ahead of the curve, providing them with cutting-edge analytics capabilities to improve the reliability and efficiency of their machines.</p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "2ca49b9d-df0b-400a-acf8-0252ab8a2618",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233c'><b>Steps in the analysis:</b></p>\n",
                "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
                "    <li>Configuring the environment</li>\n",
                "    <li>Connect to Vantage</li>\n",
                "    <li>Data Exploration</li>\n",
                "    <li>Data Preparation</li>\n",
                "    <li>Train-Test Split</li>\n",
                "    <li>In-Database Machine Learning</li>\n",
                "    <li>In-Database Model Scoring</li>\n",
                "    <li>Visualize the results</li>\n",
                "    <li>Cleanup</li>\n",
                "</ol>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "a833b5bf-74af-42be-8543-3782e1da95dc",
            "metadata": {},
            "source": [
                "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
                "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>1. Configuring the environment</b>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "ea50a4aa-1211-44fc-8166-317c35253207",
            "metadata": {},
            "outputs": [],
            "source": [
                "# Standard Libraries\n",
                "import warnings\n",
                "\n",
                "# Data Manipulation and Analysis Libraries\n",
                "import numpy as np\n",
                "import pandas as pd\n",
                "\n",
                "# Data Visualization Libraries\n",
                "import matplotlib.pyplot as plt\n",
                "import seaborn as sns\n",
                "import plotly.express as px\n",
                "import plotly.graph_objs as go\n",
                "\n",
                "# Machine Learning Libraries\n",
                "from sklearn.metrics import (\n",
                "    confusion_matrix,\n",
                "    ConfusionMatrixDisplay,\n",
                "    roc_auc_score,\n",
                "    roc_curve,\n",
                "    auc,\n",
                ")\n",
                "from sklearn.preprocessing import label_binarize\n",
                "\n",
                "# Teradata Libraries\n",
                "from teradataml import *\n",
                "\n",
                "configure.val_install_location = \"val\"\n",
                "\n",
                "# Configuration\n",
                "display.max_rows = 10\n",
                "\n",
                "# Suppress Warnings\n",
                "warnings.filterwarnings(\"ignore\")"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "a59718f8-7af4-4d1a-abc7-a860eb7cbae3",
            "metadata": {},
            "source": [
                "<hr style='height: 2px;border:none;background-color:#00233C;'>\n",
                "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>2. Connect to Vantage</b>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "794df336-4751-4519-b36f-7464d76aba40",
            "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": "2e99ca12-b08b-42e9-9519-ddf6d5fec565",
            "metadata": {},
            "outputs": [],
            "source": [
                "%%capture\n",
                "execute_sql('''SET query_band='DEMO=Remaining_Useful_Life_Forecasting_PY_SQL.ipynb;' UPDATE FOR SESSION;''')"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "b22db506-84a7-406b-be9f-9fe69d268ba4",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "93a90d5e-2e0a-4701-9368-adb97f7c9590",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.1 Getting Data for This Demo</b></p>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. You can either run the demo using foreign tables to access the data without any storage on your environment or download the data to local storage, which may yield faster execution. Still, there could be considerations of available storage. Two statements are in the following cell, and one is commented out. You may switch which mode you choose by changing the comment string.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "6cf2b3c2-f90e-428d-a949-c16294016e85",
            "metadata": {},
            "outputs": [],
            "source": [
                "# %run -i ../run_procedure.py \"call get_data('DEMO_RemaingUsefulLife_cloud');\"        # Takes about 2 minutes\n",
                "%run -i ../run_procedure.py \"call get_data('DEMO_RemaingUsefulLife_local');\"        # Takes about 3 minutes"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "1670345f-dfd2-440b-919a-ff824c65c2c9",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see the status of databases/tables created and space used.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "0ce328a8-e2bf-4b64-98aa-5f8007f86815",
            "metadata": {},
            "outputs": [],
            "source": [
                "%run -i ../run_procedure.py \"call space_report();\"        # Takes 10 seconds"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "490f3fe2-ed63-4838-bb19-4c0d0157453d",
            "metadata": {},
            "source": [
                "<hr style='height: 2px;border:none;background-color:#00233C;'>\n",
                "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>3. Data Exploration</b>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The goal of the Predictive Maintenance System (PAM) is to predict the RUL (Remaining Useful Life) of turbofan engines.</p>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The PAM system will predict the number of remaining operational cycles before failure in the test set. i.e., the number of operational cycles after the last cycle that the engine will continue to operate. \n",
                "Also provided a vector of true Remaining Useful Life (RUL) values for the test data.</p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data is from 100 engines, with hundreds of rows of measurements collected from each time the engine was cycled. There are three operational settings that have a substantial effect on engine performance. Data is collected from 27 sensors. There is a row for every time the engine is cycled (starting at 1) and a column counting down the cycles until maintenance is required, labelled RUL, or Remaining Useful Life.<p/>\n",
                "\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Each row is a snapshot of data taken during a single operational cycle, each column is a different variable. \n",
                "The columns correspond to:</p>\n",
                "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
                "    <li>unit number</li>\n",
                "    <li>time, in cycles</li>\n",
                "    <li>operational setting 1</li>\n",
                "    <li>operational setting 2</li>\n",
                "    <li>operational setting 3</li>\n",
                "    <li>sensor measurement 1</li>\n",
                "    <li>sensor measurement 2</li>\n",
                "    <li>...</li>\n",
                "    <li>sensor measurement 27</li>\n",
                "\n",
                "\n",
                "</ol>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data from <a href=https://www.kaggle.com/c/predictive-maintenance>https://www.kaggle.com/c/predictive-maintenance</a> is loaded in Vantage in a table named \"predictive_maintenance_rul\".</p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b><i>*Please scroll down to the end of the notebook for detailed column descriptions of the dataset.</i></b></p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "eda8e565-b58b-4cc6-8d9c-50790ca3dcab",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1 Examine the predictive maintenance RUL table</b></p>    \n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's look at the sample data in the predictive_maintenance_rul table.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "4a9951cf-e2b0-40d7-9a8f-a01c7f48ef09",
            "metadata": {},
            "outputs": [],
            "source": [
                "df = DataFrame(in_schema(\"DEMO_RemaingUsefulLife\", \"predictive_maintenance_rul\"))\n",
                "df.sort(\"id\").head(5)"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "e43029e3-0ec2-46e7-af7c-390caba640e0",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here is a list of columns with their data types and non-null record counts.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "30069b9e-b3f6-425e-a704-b7c630077d53",
            "metadata": {},
            "outputs": [],
            "source": [
                "df.info(null_counts=True)"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "b0d6d954-b72f-41c3-9f8c-bc15cf74d672",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have total 34 variables, out of these 28 variables are of type Float and rest 6 are Integer </p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "fc11a148-36f1-457e-a52e-f2b8e515bfff",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let's do some data exploration with engine number and time_in_cycle variables.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "2a036711-6bf1-438c-a1e3-9af246ab9c3a",
            "metadata": {},
            "outputs": [],
            "source": [
                "query = \"\"\"\n",
                "SELECT\n",
                "    engine_no,\n",
                "    COUNT(time_in_cycles) AS #cycles_completed\n",
                "FROM\n",
                "    DEMO_RemaingUsefulLife.predictive_maintenance_rul\n",
                "GROUP BY 1;\n",
                "\"\"\"\n",
                "\n",
                "df_eng_cycles = (\n",
                "    DataFrame.from_query(query).sort(\"#cycles_completed\", ascending=False).to_pandas()\n",
                ")\n",
                "df_eng_cycles.head(5)"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "91f88eb2-bb8f-4a9c-af5e-a2d40140a1b7",
            "metadata": {},
            "outputs": [],
            "source": [
                "def get_histogram(df, x, y, title, x_title, y_title, width=1200, height=500):\n",
                "    fig = px.histogram(df, x=x, y=y, title=title, nbins=df.shape[0])\n",
                "    fig.update_yaxes(title=y_title)\n",
                "    fig.update_xaxes(title=x_title)\n",
                "    fig.update_layout(\n",
                "        autosize=False,\n",
                "        width=width,\n",
                "        height=height,\n",
                "    )\n",
                "    return fig"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "7b1388fc-6273-4585-bdee-909378f20b4a",
            "metadata": {},
            "outputs": [],
            "source": [
                "get_histogram(\n",
                "    df_eng_cycles,\n",
                "    x=\"engine_no\",\n",
                "    y=\"#cycles_completed\",\n",
                "    title=\"Number of cycles completed until maintenance needed by Engine Number\",\n",
                "    x_title=\"engine_no\",\n",
                "    y_title=\"Number of cycles completed\",\n",
                ")"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "01c2c27b-a03f-4bad-a696-8818d06dba52",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that engine numbers <b>69, 92, 96, and 67</b> have more than 300 cycles completed, which is higher than other engines.</p>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's see the average number of cycles completed per engine.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "f908a39b-1d0e-4397-9fe9-a5d6eb595a5e",
            "metadata": {
                "tags": []
            },
            "outputs": [],
            "source": [
                "query = \"\"\"\n",
                "SELECT AVG(avg_cycles_completed) AS avg_cycles_completed FROM (\n",
                "    SELECT\n",
                "    engine_no,\n",
                "    COUNT(1) AS avg_cycles_completed\n",
                "    FROM\n",
                "        DEMO_RemaingUsefulLife.predictive_maintenance_rul\n",
                "    GROUP BY 1\n",
                ") AS t;\n",
                "\"\"\"\n",
                "\n",
                "DataFrame.from_query(query)"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "1639983d-a337-4ecc-abff-66c358c89d33",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that the top engine number <b>69</b> has completed approximately 54% more cycles than the average of all the engines.</p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "fb1e4b64-e3d1-4819-b9b9-431e190fa3a6",
            "metadata": {},
            "source": [
                "<hr style='height: 2px;border:none;background-color:#00233C;'>\n",
                "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>4. Data Preparation</b>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>We'll perform the following steps:</b></p>\n",
                "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
                "    <li>Missing Value Analysis</li>\n",
                "    <li>Data distribution plot for numerical variables.</li>\n",
                "    <li>Features selection using correlation</li>   \n",
                "    <li>Features scaling using z-score</li>\n",
                "\n",
                "</ul>\n"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "3cd608fd-3974-4153-9245-0f6cae779007",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.1 Missing Value analysis</b></p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "12e75155-ff7d-4fab-b5ac-9385edcaeb68",
            "metadata": {},
            "outputs": [],
            "source": [
                "def get_missing_values_table(df):\n",
                "    obj = ColumnSummary(data=df, target_columns=df.columns)\n",
                "    return obj.result[[\"ColumnName\", \"NullCount\", \"NullPercentage\"]].sort(\n",
                "        [\"NullPercentage\", \"ColumnName\"], ascending=[False, True]\n",
                "    )"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "c449b769-7bee-4306-b8d9-67e2d47c684d",
            "metadata": {},
            "outputs": [],
            "source": [
                "get_missing_values_table(df)"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "a6952d35-7304-45ed-a8d8-e7f3e60e0696",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above results, we see null values in the columns sensors_22 to sensors_27.</p>\n",
                "<!-- There is an incomplete line here \"Although these columns have 100% null values,\" -->"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "955720ad-0801-45b0-bb55-27d90726c22a",
            "metadata": {},
            "outputs": [],
            "source": [
                "# Drop columns with null\n",
                "df = df.drop(\n",
                "    columns=[\n",
                "        \"sensor_22\",\n",
                "        \"sensor_23\",\n",
                "        \"sensor_24\",\n",
                "        \"sensor_25\",\n",
                "        \"sensor_26\",\n",
                "        \"sensor_27\",\n",
                "    ]\n",
                ")"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "b41cab27-0b17-4693-848a-0b256d2bd62c",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.2 Distribution plots for numeric variables</b></p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "673cf7b6-45f3-45c3-8364-d2dc96136390",
            "metadata": {},
            "outputs": [],
            "source": [
                "def get_distribution_plot(df, cols):\n",
                "    plotnumber = 1\n",
                "    h, l, c = 5, len(cols), 6\n",
                "    r = int(np.ceil(l / c))\n",
                "    plt.figure(figsize=(20, 3 * r))\n",
                "    for col in cols:\n",
                "        if plotnumber <= l:\n",
                "            ax = plt.subplot(r, c, plotnumber)\n",
                "            plt.hist(df[[col]].get_values())\n",
                "            plt.xlabel(col, fontsize=12)\n",
                "        plotnumber += 1\n",
                "    plt.tight_layout()\n",
                "    plt.show()"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "d3b80310-1a3c-44d1-9b2b-245c0248adfb",
            "metadata": {
                "tags": []
            },
            "outputs": [],
            "source": [
                "get_distribution_plot(df, df.columns)"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "8e4f1d5c-b50c-4231-b534-bcac46951e18",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above results, we see some of the features like <b>sensor_1, sensor_5, sensor_10, .etc</b> values are unique. They will not contribute any values to our model development.</p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>So, Let's drop that columns here.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "ea0a74df-cdca-476e-a11d-db7e99197fb7",
            "metadata": {},
            "outputs": [],
            "source": [
                "# Drop columns all the unique values\n",
                "df = df.drop(\n",
                "    columns=[\n",
                "        \"sensor_1\",\n",
                "        \"sensor_5\",\n",
                "        \"sensor_10\",\n",
                "        \"sensor_16\",\n",
                "        \"sensor_18\",\n",
                "        \"sensor_19\",\n",
                "        \"op_setting_3\",\n",
                "    ]\n",
                ")"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "5e2dfc6e-5336-41be-aa0f-f202da0279d0",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.3 Checking the correlation</b></p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we'll check the correlation of all the numeric features. Measuring correlation lets you\n",
                "    determine if the value of one variable is useful in predicting the value of another.</p>\n",
                "    \n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
                "The Sample Pearson product moment correlation coefficient is a measure of the linear association between variables. The boundary on the computed coefficient ranges from -1.00 to +1.00.</p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
                "Note that high correlation does not imply a causal relationship between the variables. The following table indicates the meaning of four extreme values for the coefficient of correlation between two variables.\n",
                "</p>\n",
                "\n",
                "<table style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
                "    <th>IF the correlation coefficient has this value</th>\n",
                "    <th>THEN the association between the variables</th>\n",
                "    <tr>\n",
                "        <td>-1.00</td>\n",
                "        <td>is perfectly linear, but inverse. <br>\n",
                "        As the value for y varies, the value for x varies identically in the opposite direction.</td>\n",
                "    </tr>\n",
                "    <tr>\n",
                "        <td>0</td>\n",
                "        <td>does not exist and they are said to be uncorrelated.</td>\n",
                "    </tr>\n",
                "     <tr>\n",
                "        <td>+1.00</td>\n",
                "        <td>is perfectly linear.<br>\n",
                "        As the value for y varies, the value for x varies identically in the same direction..</td>\n",
                "    </tr>\n",
                "</table>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "1b2d14d2-fe6c-4379-a567-504f33206629",
            "metadata": {},
            "outputs": [],
            "source": [
                "def get_heatmap(df):\n",
                "    corr = df.to_pandas().corr()\n",
                "    mask = np.triu(np.ones_like(corr, dtype=bool))\n",
                "    fig = px.imshow(\n",
                "        corr,\n",
                "        text_auto=\".2f\",\n",
                "        width=900,\n",
                "        height=1100,\n",
                "        aspect=\"auto\",\n",
                "        color_continuous_scale=[\"lightblue\", \"lightyellow\"],\n",
                "    )\n",
                "    return fig.show()"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "301fb4df-4b45-490f-93b1-1208b74338ed",
            "metadata": {},
            "outputs": [],
            "source": [
                "get_heatmap(df)"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "5f7502e7-11d6-4040-ba39-f3f87030952f",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Few observations from the correlation matrix above are:</p>\n",
                "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
                "    <li>Time in cycle and remaining useful life (RUL) have a negative correlation with a value of -0.74.</li>\n",
                "    <li>Sensor 2 and Sensor 12 have a -0.72 correlation, while Sensor 12 and Sensor 7 have a 0.81 correlation.</li>\n",
                "    <li>Sensor 12 has a negative correlation with Sensor 11 and Sensor 4, with values of -0.85 and -0.82, respectively.</li>\n",
                "    <li>Sensor 4 has a positive correlation with Sensor 11 and Sensor 13, with values of 0.83 and 0.75, respectively.</li>\n",
                "</ul>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>While these correlations exist, we cannot train good models from the dataset. So as remedies, we can drop one column out of two columns with a higher correlation.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "6d249ba5-8eec-4ee1-9891-eb9cd6ff2cc5",
            "metadata": {},
            "outputs": [],
            "source": [
                "# Drop columns all the unique values\n",
                "df_sel_ft = df.drop(\n",
                "    columns=[\n",
                "        \"time_in_cycles\",\n",
                "        \"sensor_2\",\n",
                "        \"sensor_4\",\n",
                "        \"sensor_7\",\n",
                "        \"sensor_8\",\n",
                "        \"sensor_9\",\n",
                "        \"sensor_11\",\n",
                "        \"sensor_12\",\n",
                "        \"sensor_15\",\n",
                "        \"sensor_20\",\n",
                "    ]\n",
                ")"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "d98bf9df-712c-405a-8046-5b9019df553a",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.4 PairPlot for multivariate correlations</b></p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "99be38d2-b75e-4c44-8656-5d6ead40a016",
            "metadata": {},
            "outputs": [],
            "source": [
                "# Create a pairplot to visualize multivariate correlations\n",
                "sns.pairplot(\n",
                "    df_sel_ft.to_pandas()[\n",
                "        [\"op_setting_1\", \"op_setting_2\", \"sensor_3\", \"sensor_14\", \"sensor_13\", \"RUL\"]\n",
                "    ],\n",
                "    diag_kind=\"auto\",\n",
                "    hue=\"RUL\",\n",
                "    palette=\"crest\",\n",
                ")"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "d7ca2991-98be-45f9-a89c-f9e27da439fd",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> From the above pairplot, we can observe the variations in each plot. The plots are in matrix format where the row name represents x axis and column name represents the y axis. The main-diagonal subplots are the univariate histograms (distributions) for each attribute.</p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above pairplot, we are doing analysis of few sensors and operational settings with remaining useful life values. RUL values are divided in 5 ranges like 0-79, 80-159, etc.</p>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>While comparing 2 values in pair along with RUL ranges colors from light green to dark blue, we observe a few things:</p>\n",
                "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
                "    <li>When observing the 5th row with 1st column values, We can see a higher side value of RUL (dark blue) when op_setting_1 is less than 0.00 and sensor_13 values are less than 1.0.</li>\n",
                "    <li>If we check the 5th-row 3rd column - when sensor_3 values are between 1570 and 1600, and sensor_13 values below 0.3 the RUL values are between 240 and 320 units.</li>\n",
                "    <li>When we observe the 4th row with the 4th column we can see that data distributions in column sensor_14 are right skewed.</li>\n",
                "    <li>We can see that data distributions is normal in the 1st row 1st column op_setting_1.</li>\n",
                "</ol>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "f4f357ea-0f2a-4510-aad0-70eb8e100ee3",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.5 ZScore </b></p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "3c6a234b-b5b8-4aad-9571-801dd093f91c",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Z-Score transforms each column value into the number of standard deviations from the mean value of the column.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "c9c4081b-038a-4fbc-b821-f4b24ddc23b6",
            "metadata": {},
            "outputs": [],
            "source": [
                "# create ZScore obj\n",
                "zs = ZScore(columns=df_sel_ft.columns[1:-1], out_columns=df_sel_ft.columns[1:-1])\n",
                "\n",
                "# list of columns to retain in output\n",
                "retain = Retain(columns=\"RUL\")\n",
                "\n",
                "# Process the transformation\n",
                "df_transformed = valib.Transform(\n",
                "    data=df_sel_ft, zscore=zs, index_columns=\"id\", key_columns=\"id\", retain=retain\n",
                ")"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "7d805fbf-7d7f-47e8-a010-9be1a3da83bd",
            "metadata": {},
            "source": [
                "<hr style='height: 2px;border:none;background-color:#00233C;'>\n",
                "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>5. Train-Test Split</b>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the next step, we'll split the transformed dataset into training and testing datasets in the ratio 80:20, and we will save the datasets into Vantage.</p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "864fba64-af69-460a-be33-dfb85e2d7f9e",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Post spliting the dataset into train/test. Let's see number of records in train and test.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "713e8508-3c1c-4548-86a2-b77daacf1ce7",
            "metadata": {},
            "outputs": [],
            "source": [
                "TrainTestSplit_out = TrainTestSplit(\n",
                "    data=df_transformed.result, id_column=\"id\", train_size=0.80, test_size=0.20, seed=7\n",
                ")\n",
                "\n",
                "df_train = TrainTestSplit_out.result[\n",
                "    TrainTestSplit_out.result[\"TD_IsTrainRow\"] == 1\n",
                "].drop([\"TD_IsTrainRow\"], axis=1)\n",
                "df_test = TrainTestSplit_out.result[\n",
                "    TrainTestSplit_out.result[\"TD_IsTrainRow\"] == 0\n",
                "].drop([\"TD_IsTrainRow\"], axis=1)\n",
                "\n",
                "copy_to_sql(df_train, table_name=\"rul_train\", if_exists=\"replace\")\n",
                "copy_to_sql(df_test, table_name=\"rul_test\", if_exists=\"replace\")"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "c921b4de-5108-4d0f-914a-954d7d84b121",
            "metadata": {},
            "outputs": [],
            "source": [
                "print(\n",
                "    \"Training Set = \"\n",
                "    + str(df_train.shape[0])\n",
                "    + \". Testing Set = \"\n",
                "    + str(df_test.shape[0])\n",
                ")"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "346840f5-df98-46b8-8ee8-4820ac8a0c86",
            "metadata": {},
            "source": [
                "<hr style='height: 2px;border:none;background-color:#00233C;'>\n",
                "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>6. In-Database Machine Learning</b>\n",
                "\n",
                "<p style = 'font-size:20px;font-family:Arial;color:#00233c'><b>6.1 Train a XGBoost Model</b></p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the next step, we'll use the XGBOOST function to train an xgboost model using the RUL column as the target variable for regression. XGBoost's tree-based ensemble approach, regularization techniques, handling of missing values, scalability, and feature importance capabilities make it a powerful and effective choice for modeling tabular data, often leading to superior performance compared to other machine learning algorithms.\n",
                "<br>\n",
                "<br>\n",
                "The XGBoost function, eXtreme Gradient Boosting, implements the gradient-boosted decision tree designed for speed and performance. It has recently been dominating applied machine learning.\n",
                "<br>\n",
                "<br>\n",
                "In gradient boosting, each iteration fits a model to the residuals (errors) of the previous iteration to correct the errors made by existing models. The predicted residual is multiplied by this learning rate and then added to the previous prediction. Models are added sequentially until no further improvements can be made. It is called gradient boosting because it uses a gradient descent algorithm to minimize the loss when adding new models.\n",
                "</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "73ca6649-7047-49f7-a16b-2c2d3f9758aa",
            "metadata": {},
            "outputs": [],
            "source": [
                "XGBoost_out = XGBoost(\n",
                "    data=df_train,\n",
                "    input_columns=[\n",
                "        \"engine_no\",\n",
                "        \"op_setting_1\",\n",
                "        \"op_setting_2\",\n",
                "        \"sensor_3\",\n",
                "        \"sensor_6\",\n",
                "        \"sensor_13\",\n",
                "        \"sensor_14\",\n",
                "        \"sensor_17\",\n",
                "        \"sensor_21\",\n",
                "    ],\n",
                "    response_column=\"RUL\",\n",
                "    max_depth=5,\n",
                "    num_boosted_trees=-1,\n",
                "    model_type=\"regression\",\n",
                "    seed=123,\n",
                "    lambda1=100000.0,\n",
                "    shrinkage_factor=0.1,\n",
                "    iter_num=10,\n",
                "    column_sampling=1.0,\n",
                ")"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "cfdf90da-89ba-4b99-9a9f-0c8fa80b7917",
            "metadata": {},
            "source": [
                "<hr style='height: 1px;border:none;background-color:#00233C;'>\n",
                "<p style = 'font-size:20px;font-family:Arial;color:#00233c'><b>6.2 XGBoost - Model Scoring</b></p>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
                "In the next step, we'll use the XGBoostPredict function to score the xgboost model trained in the previous step.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "46b7f072-bac8-4a19-8b8e-f8821f62e073",
            "metadata": {},
            "outputs": [],
            "source": [
                "XGBoostPredict_out = XGBoostPredict(\n",
                "    newdata=df_test,\n",
                "    object=XGBoost_out.result,\n",
                "    id_column=\"id\",\n",
                "    accumulate=\"RUL\",\n",
                "    model_type=\"regression\",\n",
                "    object_order_column=[\"task_index\", \"tree_num\", \"iter\", \"tree_order\"],\n",
                ")"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "5599a2e2-7793-4086-9729-d1a28311058c",
            "metadata": {},
            "outputs": [],
            "source": [
                "XGBoostPredict_out.result"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "e6b928d6-568d-42ca-9106-4cde60fe87a4",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next, we'll use the RegressionEvaluator function to evaluate the trained xgboost model on test data. This will let us know how well our model has performed on unseen data.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "3cf71964-bbac-4a17-8dc8-27031b217fb4",
            "metadata": {
                "tags": []
            },
            "outputs": [],
            "source": [
                "RegressionEvaluator_out = RegressionEvaluator(\n",
                "    data=XGBoostPredict_out.result,\n",
                "    observation_column=\"RUL\",\n",
                "    prediction_column=\"Prediction\",\n",
                "    freedom_degrees=[1, 28],\n",
                "    independent_features_num=15,\n",
                "    metrics=[\"MAE\", \"MSE\", \"RMSE\", \"R2\", \"FSTAT\"],\n",
                ")"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "db974d18-4226-496d-a3ca-561101012934",
            "metadata": {},
            "outputs": [],
            "source": [
                "RegressionEvaluator_out.result"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "9e8ed8a2-6a6f-4ed5-aaeb-f6d5cf0fc31a",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The result table displays the evaluation metrics for XGBoost models retrieved from RegressionEvaluator. The lower the RMS error value, the better the model's performance. Here F_conclusion is <b>Fail to reject null hypothesis</b> which means that our XGBoost model is not fitting perfectly on our data. In other words, none of the predictor variables have a statistically significant relationship with the response variable, RUL.</p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "27dc2739-15a4-4e10-9a23-26f6510dd725",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:20px;font-family:Arial;color:#00233c'><b>6.3 Train a Decision Forest Model</b></p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Decision Forest is a powerful method used for predicting outcomes in both classification and regression problems. It's an improvement on the technique of combining (or \"bagging\") multiple decision trees. Normally, building a decision tree involves assessing the importance of each feature in the data to determine how to divide the information. This method takes a unique approach by only considering a random subset of features at each division point in the tree. This forces each decision tree within the \"forest\" to be different from one another, which ultimately improves the accuracy of the predictions. The function relies on a training dataset to develop a prediction model. Then, the TD_DecisionForestPredict function uses the model built by the TD_DecisionForest function to make predictions. It supports regression, binary, and multi-class classification tasks.</p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Typically, constructing a decision tree involves evaluating the value for each input feature in the data to select a split point. The function reduces the features to a random subset (that can be considered at each split point); the algorithm can force each decision tree in the forest to be very different to improve prediction accuracy. The function uses a training dataset to create a predictive model. The TD_DecisionForestPredict function uses the model created by the TD_DecisionForest function for making predictions. The function supports regression, binary, and multi-class classification.</p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Consider the following points:\n",
                "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>All input features are numeric. Convert the categorical columns to numerical columns as preprocessing step.</li>\n",
                "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>For classification, class labels (ResponseColumn values) can only be integers. A maximum of 500 classes is supported for classification.</li>\n",
                "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Observations with missing values in any input column will be ignored during training. To fill in missing values, use the TD_SimpleImpute function.</li>\n",
                "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The number of trees built by the TD_DecisionForest function depends on the values of NumTrees, TreeSize, and CoverageFactor, as well as the data distribution in the cluster. The trees are built simultaneously by all the processing units (AMPs) that have a non-empty portion of the data.</li>\n",
                "</p>\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "8eb52d8f-5f99-404c-b3ca-ef5ef051d4d3",
            "metadata": {},
            "outputs": [],
            "source": [
                "query = \"\"\"\n",
                "CREATE MULTISET TABLE DF_train AS (\n",
                "    SELECT * FROM TD_DecisionForest (\n",
                "        ON rul_train AS INPUTTABLE PARTITION BY ANY\n",
                "        USING\n",
                "        ResponseColumn('RUL')\n",
                "        InputColumns('engine_no', 'op_setting_1', 'op_setting_2', 'sensor_3', 'sensor_6','sensor_13', 'sensor_14', 'sensor_17', 'sensor_21')\n",
                "        MaxDepth(12)\n",
                "        MinNodeSize(1)\n",
                "        NumTrees(4)\n",
                "        ModelType('REGRESSION')\n",
                "        Seed(1)\n",
                "        Mtry(-1)\n",
                "        MtrySeed(1)\n",
                "    ) AS dt\n",
                ") WITH DATA;\n",
                "\"\"\"\n",
                "\n",
                "try:\n",
                "    execute_sql(query)\n",
                "except:\n",
                "    db_drop_table(\"DF_train\")\n",
                "    execute_sql(query)"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "d4dffb08-5cdc-4f98-89ef-008ceed716ca",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:20px;font-family:Arial;color:#00233c'><b>6.4 Decision Forest - Model Scoring</b></p>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
                "In the next step, we'll use the TD_DecisionForestPredict function to score the decision forest model trained in the previous step.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "8c6a1d81-4c46-49b5-b9dd-96778aed0c75",
            "metadata": {},
            "outputs": [],
            "source": [
                "query = \"\"\"\n",
                "CREATE MULTISET TABLE DF_Predict AS (\n",
                "    SELECT * FROM TD_DecisionForestPredict (\n",
                "        ON rul_test AS InputTable PARTITION BY ANY\n",
                "        ON DF_Train AS ModelTable DIMENSION\n",
                "        USING\n",
                "            IdColumn('id')\n",
                "            Detailed('false')\n",
                "            Accumulate('RUL')\n",
                "    ) AS dt\n",
                ") WITH DATA;\n",
                "\"\"\"\n",
                "\n",
                "try:\n",
                "    execute_sql(query)\n",
                "except:\n",
                "    db_drop_table(\"DF_Predict\")\n",
                "    execute_sql(query)"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "473012c3-4272-4bfa-871b-ccf536dee698",
            "metadata": {},
            "outputs": [],
            "source": [
                "df_result_pd = (\n",
                "    DataFrame(\"DF_Predict\")\n",
                "    .to_pandas()\n",
                "    .reset_index()\n",
                "    .sort_values(\"id\")\n",
                "    .rename(columns={\"RUL\": \"Actual\"})\n",
                ")\n",
                "df_result_pd"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "96b982cc-70dd-45f6-bcb2-76f0a70433ec",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TD_RegressionEvaluator function computes metrics to evaluate and compare multiple models and summarizes how close predictions are to their expected values.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "8e74e776-fc89-47e7-8bf5-5efe1c08dabf",
            "metadata": {},
            "outputs": [],
            "source": [
                "query = \"\"\"\n",
                "SELECT * FROM TD_RegressionEvaluator(\n",
                "    ON DF_Predict AS InputTable\n",
                "    USING\n",
                "    ObservationColumn('RUL')\n",
                "    PredictionColumn('prediction')\n",
                "    Metrics('MAE', 'MSE', 'RMSE', 'R2', 'FSTAT')\n",
                "    DegreesOfFreedom(5, 28)\n",
                "    NUMOFINDEPENDENTVARIABLES(15)\n",
                ") AS dt;\n",
                "\"\"\"\n",
                "\n",
                "out = DataFrame.from_query(query)\n",
                "out"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "d14a5946-76bb-4066-8d5c-1b5306bd7dc8",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The result table displays the evaluation metrics for DecisionForest models retrieved from TD_RegressionEvaluator. The lower the RMS error value, the better the model's performance. Here F_conclusion is <b>Reject null hypothesis</b> which means that our DecisionForest model is fitting perfectly on our data. In other words, the predictor variables like sensors, op_sesstoins, etc. have a statistically significant relationship with the response variable, RUL</p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "66e69e89-62c6-4173-9cfe-d322fe0a58a3",
            "metadata": {},
            "source": [
                "<hr style='height: 2px;border:none;background-color:#00233C;'>\n",
                "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>8. Visualize the results</b>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "79d795d5-9ee3-4c83-8538-a44a826b5187",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Metrics of the regression evaluator has the RMSE, R2 and the F-STAT metrics which are specified in the Metrics.</p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus here we have used 2 different models to train and predict the data. The Regression evaluator is used to evaluate and compare the models. The Teradata In-Database functions are used for training, prediction and evaluation. In this case since we have sample data the result parameters may not be accurate for these models.</p>  \n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Root mean squared error (RMSE)The most common metric for evaluating linear regression model performance is called root mean squared error, or RMSE. The basic idea is to measure how bad/erroneous the model’s predictions are when compared to actual observed values. So a high RMSE is “bad” and a low RMSE is “good”.</p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The coefficient of determination — more commonly known as R² — allows us to measure the strength of the relationship between the response and predictor variables in the model. It’s just the square of the correlation coefficient R, so its values are in the range 0.0–1.0. Higher values of R- Squared is Good.</p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The metrics specified in the Metrics syntax element are displayed. For FSTAT, the following columns are displayed:</p>\n",
                "<li style = 'font-size:16px;font-family:Arial'>F_score</li>\n",
                "<li style = 'font-size:16px;font-family:Arial'>F_Critcialvalue</li>\n",
                "<li style = 'font-size:16px;font-family:Arial'>p_value</li>\n",
                "<li style = 'font-size:16px;font-family:Arial'>F_Conclusion.</li></p>\n",
                "\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we can see the comparison for MAE,MSE,RMSE and R2 for XGBoost and DecisionForest.</p> \n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "c522a732-590b-4986-8945-295c19a8a24b",
            "metadata": {},
            "outputs": [],
            "source": [
                "XGB_Eval = RegressionEvaluator_out.result.to_pandas()\n",
                "DF_eval = out.to_pandas()"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "5b7b382b-5310-4b21-a4d4-ce5d8f5fd068",
            "metadata": {},
            "outputs": [],
            "source": [
                "frames = [DF_eval, XGB_Eval]\n",
                "result = pd.concat(frames)\n",
                "result = result.set_index([[\"Decision Forest\", \"XGBoost\"]])\n",
                "transposed_df_eval = result.transpose()\n",
                "transposed_df_eval"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "d906f30b-0f7b-4257-a61b-6a2fd6237c93",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above metrics compare the Decision Forest and XGBoost models. We can see that Decision Forest is performing better than XGBoost. Also, F_conclusion is Reject null hypothesis which means that our DecisionForest model is fitting perfectly on our data.</p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "0f776985-2b91-4ee1-83b8-7effd17612cc",
            "metadata": {},
            "source": [
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's visualize the the Decision Forest prediction result to compare actual vs. predicted values in graph.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "2acce967-db00-4bd4-a3a9-9ae7002588dd",
            "metadata": {},
            "outputs": [],
            "source": [
                "# Taking first 150 records\n",
                "df_result_sample = df_result_pd[:150]\n",
                "plt.figure(figsize=(20, 8))\n",
                "plt.xlabel(\"Remaining Useful Life\", fontsize=14)\n",
                "plt.ylabel(\"ID\", fontsize=14)\n",
                "plt.plot(\n",
                "    df_result_sample[\"id\"], df_result_sample[\"Actual\"], color=\"g\", label=\"Actual Value\"\n",
                ")\n",
                "plt.plot(\n",
                "    df_result_sample[\"id\"],\n",
                "    df_result_sample[\"prediction\"],\n",
                "    color=\"r\",\n",
                "    label=\"Predicted Value\",\n",
                ")\n",
                "plt.title(\"Actual vs Predicted using DecisionForest Classification\", fontsize=18)\n",
                "plt.legend()\n",
                "plt.show()"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "402b7ab3-a5db-4392-a67a-36b1f016c887",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>8.1 Conclusion:</b></p>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In conclusion, the implementation of a predictive maintenance solution can greatly benefit to the client by reducing machine downtime and maintenance costs, improving production efficiency, and increasing overall productivity. Proactive scheduling of maintenance based on real-time data and analytics can help prevent costly breakdowns and emergency repairs, leading to improved machine reliability.\n",
                "    <br>\n",
                "    <br>\n",
                "Additionally, setting limits and alarms on key parameters can enable early detection of potential failures, allowing for timely maintenance interventions. The ability to predict the type of failure can also help reduce diagnosis time, further optimizing maintenance efforts. By leveraging predictive maintenance, client can make data-driven decisions to improve their maintenance strategy, leading to tangible benefits to the company's bottom line, including increased operational efficiency, reduced costs, and improved overall performance.</p>"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "50b8b817-c0ff-42b3-a651-c8268ac40942",
            "metadata": {},
            "source": [
                "<hr style='height: 2px;border:none;background-color:#00233C;'>\n",
                "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>9. Cleanup</b>\n",
                "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>9.1 Work Tables</b></p>\n",
                "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time.</p>"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "81b55053-9f37-48da-9cca-b64e480997b0",
            "metadata": {},
            "outputs": [],
            "source": [
                "tables = [\"DF_Predict\", \"DF_train\"]\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": "0aacb87c-0d81-40f0-8754-608d47e602cc",
            "metadata": {},
            "source": [
                "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
                "\n",
                "<p style = 'font-size:18px;font-family:Arial;color:#00233c'> <b>9.2 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": "7b398148-9486-4535-8c06-d3f77669bbdc",
            "metadata": {},
            "outputs": [],
            "source": [
                "%run -i ../run_procedure.py \"call remove_data('DEMO_Retail');\"        # Takes 5 seconds"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "73ef4cbe-1beb-4c65-ac60-ffd6250668a4",
            "metadata": {},
            "outputs": [],
            "source": [
                "remove_context()"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "ae971f90-bef1-4228-bcbf-c333e9843284",
            "metadata": {},
            "source": [
                "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>Dataset:</b>\n",
                "\n",
                "- `engine_no`: Unique identifier ranging from 1 to 100\n",
                "- `index`: Unique row id\n",
                "- `time_in_cycles`: the number of operational cycles\n",
                "- `op_setting_1`: operational settings1 that have a substantial effect on engine performance.\n",
                "- `op_setting_2`: operational settings2 that have a substantial effect on engine performance.\n",
                "- `op_setting_3`: operational settings3 that have a substantial effect on engine performance.\n",
                "- `sensor_1`: sensor measurement 1\n",
                "- `sensor_2`: sensor measurement 2\n",
                "- `sensor_3`: sensor measurement 3\n",
                "- `sensor_4`: sensor measurement 4\n",
                "- `sensor_5`: sensor measurement 5\n",
                "- `sensor_6`: sensor measurement 6\n",
                "- `sensor_7`: sensor measurement 7\n",
                "- `sensor_8`: sensor measurement 8\n",
                "- `sensor_9`: sensor measurement 9\n",
                "- `sensor_10`: sensor measurement 10\n",
                "- `sensor_11`: sensor measurement 11\n",
                "- `sensor_12`: sensor measurement 12\n",
                "- `sensor_13`: sensor measurement 13\n",
                "- `sensor_14`: sensor measurement 14\n",
                "- `sensor_15`: sensor measurement 15\n",
                "- `sensor_16`: sensor measurement 16\n",
                "- `sensor_17`: sensor measurement 17\n",
                "- `sensor_18`: sensor measurement 18\n",
                "- `sensor_19`: sensor measurement 19\n",
                "- `sensor_20`: sensor measurement 20\n",
                "- `sensor_21`: sensor measurement 21\n",
                "- `sensor_22`: sensor measurement 22\n",
                "- `sensor_23`: sensor measurement 23\n",
                "- `sensor_24`: sensor measurement 24\n",
                "- `sensor_25`: sensor measurement 25\n",
                "- `sensor_26`: sensor measurement 26\n",
                "- `sensor_27`: sensor measurement 27\n",
                "- `RUL`: predict the number of remaining operational cycles before failure in the test set, \n",
                "i.e., the number of operational cycles after the last cycle that the engine will continue to operate. \n",
                "Also provided a vector of true Remaining Useful Life (RUL) values for the test data.\n"
            ]
        },
        {
            "cell_type": "markdown",
            "id": "e5463848-592f-4321-b852-287e133872dd",
            "metadata": {},
            "source": [
                "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
                "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
                "    <div style=\"float:right;\">\n",
                "        <div style=\"float:left; margin-top:14px\">\n",
                "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
                "        </div>\n",
                "    </div>\n",
                "</footer>"
            ]
        }
    ],
    "metadata": {
        "kernelspec": {
            "display_name": "Python 3 (ipykernel)",
            "language": "python",
            "name": "python3"
        },
        "language_info": {
            "codemirror_mode": {
                "name": "ipython",
                "version": 3
            },
            "file_extension": ".py",
            "mimetype": "text/x-python",
            "name": "python",
            "nbconvert_exporter": "python",
            "pygments_lexer": "ipython3",
            "version": "3.9.10"
        }
    },
    "nbformat": 4,
    "nbformat_minor": 5
}