UseCases/Energy_Consumption_Forecasting/Energy_Consumption_Forecasting_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "acquired-consideration",
   "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",
    "       Energy Consumption Forecasting\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": "0701a2ac-9d7c-4498-a23c-7ae3955f6a32",
   "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'>Electricity consumption is a key driver to a successful energy trading company. But this success is only translated into profits if they can correctly predict how much energy will be consumed. In fact, proper forecasting of market energy demand prevents losses, in case of overselling energy to market, and lost profits, in case of underestimating demand. Not only will an energy company’s only cash flow be affected by false predictions, but the regulator of the energy market can apply fees or even disqualify a trading company for specific periods for frequent inaccurate forecasts. Fortunately, Teradata Vantage and ClearScape Analytics provide the ideal platform to create these predictions. With Vantage’s advanced in-database analytics, time series functions, and AI/ML capabilities, companies can increase their confidence in the predictions.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Values</b></p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Decrease profit losses by over predicting or under predicting the amount of energy consumed.</li>\n",
    "    <li>Decrease regulations placed on trading companies from the energy market.</li>\n",
    "    <li>Increase energy consumption prediction accuracy.</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Users can utilize Vantage to operationalize their machine learning process. In addition, users have access to AI/ML through:</p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Data Discovery and Statistical Analysis</li>\n",
    "    <li>Data Preparation and Feature Engineering</li>\n",
    "    <li>Model Deployment and Evaluation at Operational Scale</li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "legislative-turkey",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Traditional approaches require the developer to move data <b>from</b> the sources <b>to</b> the analytics.  Even \"integrated\" analytic systems like Apache Spark provide parallel processing for analyzing data but don't optimize for loading data - neither locality nor quantity that needs to be moved.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Teradata Vantage reverses this model; and allows PUSH processing down to the individual processing nodes where the data resides.  This allows for the unprecedented scale of the analytical processing, reduced costs in data movement/egress charges, and drastically improved performance.</p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A critical strategy for Vantage and ClearScape Analytics is to embrace the value and innovation in the open-source and partner ML and AI community. A cornerstone of that strategy is to allow users to leverage their ML or AI tools and models of choice to deploy those models directly to the Vantage Platform.  This provides enterprises with the most scalable option for deploying custom machine learning pipelines. Users can leverage the innovation and familiarity of a broad range of tools and techniques, with the ability to prepare and score new data in near-real-time and at any scale; allowing the products of machine learning to become pervasive across all applications, reporting tools, and consumers in an organization.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71d444f9-e2f7-4282-a293-96802ff2db7b",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Downloading and installing additional software needed</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2c074f7-9866-4af0-b822-434435c361a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%capture\n",
    "# # '%%capture' suppresses the display of installation steps of the following packages\n",
    "# !pip install sklearn2pmml\n",
    "# !pip install jdk4py\n",
    "# !pip install teradataml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d10db2ec-ce66-411d-bb03-b9262752d667",
   "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>\n",
    "\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": "7c9d4207-bc98-428b-93d3-7955c99a34fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Standard library imports\n",
    "import os\n",
    "import getpass\n",
    "import sys\n",
    "import warnings\n",
    "\n",
    "# Third-party library imports\n",
    "from jdk4py import JAVA, JAVA_HOME, JAVA_VERSION\n",
    "\n",
    "from teradataml import *\n",
    "from teradatasqlalchemy import FLOAT\n",
    "from sqlalchemy import func\n",
    "\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "from sklearn2pmml.pipeline import PMMLPipeline\n",
    "from sklearn2pmml import sklearn2pmml\n",
    "\n",
    "# Suppress warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "display.max_rows = 5\n",
    "\n",
    "# Modify the following to match the specific client environment settings\n",
    "configure.val_install_location = 'val'\n",
    "configure.byom_install_location = 'mldb'\n",
    "os.environ['PATH'] = os.pathsep.join([os.environ['PATH'], str(JAVA_HOME), str(JAVA)[:-5]])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29d75d8d-d58b-4bb8-87e5-cdf1a8add76a",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>1. Connect to Vantage</b>\n",
    "<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.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a453af8-1cba-4890-b6a7-3448a1bbc568",
   "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": "bcdfde89-b0b9-4210-95c7-a9390569fdf0",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Energy_Consumption_Forecasting_Python.ipynb;' UPDATE FOR SESSION;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b0be93a-2c49-4708-a17e-3e9e1401c8fc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d20217e-9600-4ef7-adc5-6ba10c76a5cc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57a490a6-75b8-4d4d-8986-46b03fc57081",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_Energy_cloud');\"        # Takes 1 minute\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_Energy_local');\"        # Takes 2 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a5c1780-0cca-4b25-8fc5-cefbf8bde51b",
   "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": "34c657c8-2122-4426-9d38-c5a619f7817a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "obvious-championship",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>2. Data Exploration</b>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "<tr>\n",
    "    <td style = 'vertical-align:middle' width = '50%'>\n",
    "        <p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can access large volumes of data by connecting remotely using the teradataml client connection library. Our Python methods are translated to SQL and run remotely on the Vantage system. We only copy the minimal amount of data required to the client, allowing us to interact with data sets of any size and scale.</p>\n",
    "    </td>\n",
    "    <td><img src = 'images/connect_and_discover.png' width = '400'></td>\n",
    "</tr>\n",
    "</table>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's create a \"Virtual DataFrame\" that points to the data set in Vantage.  This will allow for processing the dataset without having to bring the data down to the client.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "through-hearts",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema(\"DEMO_Energy\", \"consumption\"))\n",
    "tdf.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7d83371-aeca-4efd-b43c-05cb33a2d988",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo, we used a dataset that represents electricity consumption in Norway from the 1st of January 2016 to the 31st of August 2019. Each line in our dataset reflects consumption for one hour. Apart from electricity consumption, our dataset also reflects additional data: weather from multiple sources, daylight information, and the labor calendar. We collected all our data from open data sources.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's investigate the data by looking at a data sample.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93212923-1012-401d-9f39-b1d6b99b6b38",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5989392d-db19-4be6-ad75-9df5ce829a5e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The dataset above shows our hourly consumption of energy. We have multiple columns that are potential factors affecting our energy consumption, such as is_dark, is_holiday, etc.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc072604-c582-4029-83ba-06073d23111a",
   "metadata": {},
   "outputs": [],
   "source": [
    "figure = Figure(width=1200, height=600, heading=\"Energy Demand\")\n",
    "\n",
    "plot = tdf.plot(\n",
    "    x=tdf.TD_TIMECODE,\n",
    "    y=tdf.consumption,\n",
    "    xtick_format='YYYY-MM',\n",
    "    xlabel='Date',\n",
    "    ylabel='Energy Units',\n",
    "    color='carolina blue',\n",
    "    figure=figure,\n",
    "    legend='Energy',\n",
    "    legend_style='upper right',\n",
    "    grid_linestyle='--',\n",
    "    grid_linewidth=0.5\n",
    ")\n",
    "\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9a678e7-ece7-4f6a-bd7e-7bed5c4a4dd6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Scaling up data visualization presents challenges with processing and interpreting large datasets, causing issues like slow performance. Specialized methods, such as \"td_plot,\" address these challenges by providing efficient solutions for insights without compromising speed.\n",
    "<br>\n",
    "<br>\n",
    "The \"td_plot\" method in Teradata Vantage simplifies large-scale visualization by allowing users to create visualizations directly within the Vantage environment. It eliminates the need for data movement, enhancing efficiency and addressing challenges associated with handling extensive datasets.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7842e63d-167a-409c-afca-60957ffa1ed6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Instantiate MinMaxScalar\n",
    "rs = MinMaxScalar(columns=['cap_air_temperature', 'cap_cloud_area_fraction', 'cap_precipitation_amount'])\n",
    "\n",
    "# Transform the data using MinMaxScaler\n",
    "t_output = valib.Transform(data=tdf,\n",
    "                          rescale=[rs],\n",
    "                          index_columns='TD_TIMECODE').result\n",
    "\n",
    "# Create subplots\n",
    "fig, axis = subplots(3, 1)\n",
    "\n",
    "# Set figure height and width\n",
    "fig.height, fig.width = 900, 1200\n",
    "\n",
    "cols = ['cap_air_temperature', 'cap_cloud_area_fraction', 'cap_precipitation_amount']\n",
    "\n",
    "# Plot the data\n",
    "for i in range(len(cols)):\n",
    "    plot = t_output.plot(x=t_output.TD_TIMECODE,\n",
    "                         y=t_output[cols[i]],\n",
    "                         ax=axis[i],\n",
    "                         xtick_format='YYYY-MM',\n",
    "                         xlabel='Date',\n",
    "                         ylabel='Normalized Values',\n",
    "                         color='carolina blue',\n",
    "                         figure=fig,\n",
    "                         legend_style='upper right',\n",
    "                         grid_linestyle='--',\n",
    "                         grid_linewidth=0.5)\n",
    "\n",
    "# Show the plot\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac9c3ad5-d5ab-4c2e-9c56-51f61c465051",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In our analysis, the graph of cap_air_temperature shows an inverse relationship with our energy consumption. This means that in countries with colder climates like Norway, electricity usage tends to increase as the temperature drops, likely due to increased demand for heating. Conversely, electricity usage tends to decrease when the temperature rises, potentially due to reduced need for heating.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ace48b91-2358-4b50-9176-044541fadb25",
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_tdf = tdf.assign(Quarter=func.td_quarter_of_year(tdf.TD_TIMECODE.expression)).filter(items=['consumption', 'Quarter']).groupby('Quarter').mean()\n",
    "\n",
    "agg_tdf.plot(\n",
    "    x=agg_tdf.Quarter,\n",
    "    y=agg_tdf.mean_consumption,\n",
    "    color='carolina blue',\n",
    "    kind='bar'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "441628d1-87f0-42c3-bb39-0cf0dd885535",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In our analysis, the above graph shows the distribution of energy consumption across quarters. We observe that the 1st and 4th quarters across years witness high energy consumption due to cold weather, while the 3rd quarter witnesses the least energy consumption across years, indicating the summer season.</p> "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "characteristic-spencer",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>3. Data Preparation</b>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "<tr>\n",
    "    <td style = 'vertical-align:top' width = '50%'>\n",
    "        <p style = 'font-size:16px;font-family:Arial;color:#00233C'>We, as users of the Vantage Analytic Library, benefit from a suite of powerful functions that enable us to perform whole-data-set descriptive analysis, data transformation, hypothesis testing, and algorithmic algorithms at an extreme scale. As with all Vantage capabilities, we run these functions in parallel at the source of the data.</p>\n",
    "        <ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "            <li>Create Feature Transformation objects</li>\n",
    "            <br>\n",
    "            <li>Define the columns to be retained in the analytic data set</li>\n",
    "            <br>\n",
    "            <li>Push the transformations to the data in Vantage</li>\n",
    "            <br>\n",
    "            <li>Inspect the results</li>\n",
    "        </ol>\n",
    "    </td>\n",
    "    <td><img src = 'images/VAL_transformation.png' width = '400'></td>\n",
    "</tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "internal-motor",
   "metadata": {},
   "outputs": [],
   "source": [
    "weekday_mapping = {1: 'monday', 2: 'tuesday', 3: 'wednesday', 4: 'thursday', 5: 'friday', 6: 'saturday', 7: 'sunday'}\n",
    "weekday_t = OneHotEncoder(values=weekday_mapping, columns='weekday')\n",
    "\n",
    "hour_t = OneHotEncoder(values=[x for x in range(0, 24)], columns='h')\n",
    "\n",
    "rs = MinMaxScalar(columns=['nasa_temp', 'cap_air_temperature', 'cap_cloud_area_fraction', 'cap_precipitation_amount'])\n",
    "\n",
    "rt = Retain(columns=['consumption',\n",
    "                     'is_dark', 'is_light', 'is_from_light_to_dark', 'is_from_dark_to_light',\n",
    "                     'is_holiday', 'is_pre_holiday'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26620753-a458-492d-bef2-3cb1c8f75701",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the transformation objects created in the previous step to prepare our data for modeling. Specifically, we will use weekday_t and hour_t to convert our weekday and hour columns from numeric to one-hot encoded columns. We will use rs to scale our nasa_temp using MinMaxScalar, and rt will be used to retain the specified columns. These transformations will enable us to use our data effectively in a machine learning model..</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "funny-apparatus",
   "metadata": {},
   "outputs": [],
   "source": [
    "t_output = valib.Transform(\n",
    "    data=tdf,\n",
    "    one_hot_encode=[weekday_t, hour_t],\n",
    "    rescale=[rs],\n",
    "    index_columns='TD_TIMECODE',\n",
    "    retain=[rt]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "honey-point",
   "metadata": {},
   "outputs": [],
   "source": [
    "t_output.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "080268d2-4cc4-4f98-b2fd-1a8a09a0bfc1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Please scroll to the right and observe that we now have columns named <b>monday-sunday</b> and <b>0_h - 23_h</b>. Also, nasa_temp has been scaled.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a5cb6f2-ac6e-4561-8568-e7bc5e2a2aeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(t_output.result.iloc[int(t_output.result.shape[0]) - 168:],\n",
    "            table_name='energy_consumption_variables_rescaled_test',\n",
    "            if_exists='replace')\n",
    "\n",
    "copy_to_sql(t_output.result.iloc[:int(t_output.result.shape[0]) - 168],\n",
    "            table_name='energy_consumption_variables_rescaled_train',\n",
    "            if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc5f386e-16a5-4568-9d01-6d52208a4cfb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create training and testing datasets. We use the last 168 hours, i.e., seven days, for testing, and we use the remaining data for training.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "great-instrument",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>4. Model Training</b>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "<tr>\n",
    "    <td style = 'vertical-align:top' width = '50%'>\n",
    "        <p style = 'font-size:16px;font-family:Arial;color:#00233C'>With Vantage Bring Your Own Model, we can take advantage of a rich ecosystem of Machine Learning, Data Preparation, and Advanced analytical libraries available in the open-source and commercial space. This demonstration illustrates how we can utilize simple client-side training pipelines.</p>\n",
    "        <ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "            <li>Copy the training data to the client</li>\n",
    "            <br>\n",
    "            <li>Prepare data and train the model</li>\n",
    "            <br>\n",
    "            <li>Load the model into Vantage</li>\n",
    "        </ol>\n",
    "    </td>\n",
    "    <td><img src = 'images/BYOM_model_training.png' width = '400'></td>\n",
    "</tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc27f9dc-eadd-4079-95ce-0f68c88b48a5",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>Please remember that in this section, we move data from Vantage to client side <b>only to simulate model being trained outside Vantage</b>. The trained model would be used to demonstrate BYOM (Bring Your Own Model) functionality of Vantage.</i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "collective-establishment",
   "metadata": {},
   "outputs": [],
   "source": [
    "pandas_df = DataFrame('energy_consumption_variables_rescaled_train') \\\n",
    "    .sort('TD_TIMECODE') \\\n",
    "    .to_pandas(all_rows=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "innovative-shark",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We calculate the average consumption for the last day of the training period. We will use this number for the normalization of the target variable.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fba619f-e630-4e44-ae2b-ce43ff92d929",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = DataFrame('energy_consumption_variables_rescaled_train')\n",
    "normalize_value = int(df.tail(24).mean().select('mean_consumption').squeeze())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "little-charm",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_x = pandas_df.drop(['TD_TIMECODE', 'consumption'], axis=1).astype(float)\n",
    "train_y = pandas_df['consumption'] - normalize_value\n",
    "\n",
    "feature_names = list(train_x.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78c71e6c-6fc5-4948-9394-fc5035bfdfa3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We drop the TD_TIMECODE and consumption columns from our training dataset because TD_TIMECODE is a timestamp, and consumption is the target variable we aim to predict. Therefore, we can't use it as a feature in our model. We normalize the target variable by subtracting the normalized_value to scale the consumption values and bring them into a similar range as the other features used for prediction. This approach helps us in learning the correct relationship between features and the target variable, making our training process more efficient.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "silver-accessory",
   "metadata": {},
   "outputs": [],
   "source": [
    "pipeline_lr = PMMLPipeline(steps=[('lr', LinearRegression())])\n",
    "pipeline_rf = PMMLPipeline(steps=[('random_forest', RandomForestRegressor(n_estimators=100, max_depth=10, random_state=42))])\n",
    "\n",
    "pipeline_lr.fit(train_x, train_y)\n",
    "pipeline_rf.fit(train_x, train_y)\n",
    "\n",
    "sklearn2pmml(pipeline_lr, \"energy_consumption_LR.pmml\", with_repr=True)\n",
    "sklearn2pmml(pipeline_rf, \"energy_consumption_RF.pmml\", with_repr=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6805b0b-37d0-4c1b-bfd2-e763b89c94d2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above step, we create two PMML Pipelines, one with a Linear Regression object and another with a Random Forest object. We use these Pipelines to train our models using the \"fit\" method with the preprocessed training dataset. After training, we save the trained pipelines as PMML files locally for future use.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "972f9c1a-969f-4b80-9e46-189c4323f0d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the PMML file into Vantage\n",
    "\n",
    "model_ids = ['lr', 'rf']\n",
    "model_files = ['energy_consumption_LR.pmml', 'energy_consumption_RF.pmml']\n",
    "table_name = 'energy_models'\n",
    "\n",
    "for model_id, model_file in zip(model_ids, model_files):\n",
    "    try:\n",
    "        save_byom(model_id=model_id, model_file=model_file, table_name=table_name)\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_id, table_name=table_name)\n",
    "            save_byom(model_id=model_id, model_file=model_file, table_name=table_name)\n",
    "        else:\n",
    "            raise ValueError(f\"Unable to save the model '{model_id}' in '{table_name}' due to the following error: {e}\")\n",
    "\n",
    "# Show the model table\n",
    "list_byom(table_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "502f4b01-4a91-4f10-95db-08b2adf16e64",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above steps, we save our trained PMML models in a table named <b>energy_models</b>. If a model with the same model_id already exists, we delete it first, and then we save the latest trained model again using the <b>save_byom</b> method. This ensures that we always store the most recent version of the model in the table.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "regional-poetry",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>5. Model Scoring and Evaluation</b>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "<tr>\n",
    "    <td style = 'vertical-align:top' width = '50%'>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The final step in our process is to test the trained model. We will use the PMMLPredict function to take our stored pipeline object (including any data preparation and mapping tasks) and execute it against the data on the Vantage Nodes. Note that we can keep many models in our model table, with versioning, last scored timestamp, or any other management data to allow for the operational management of our process.</p>\n",
    "        <ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "            <li>Create a pointer to the model in Vantage</li>\n",
    "            <br>\n",
    "            <li>Execute the Scoring function using the model against the testing data</li>\n",
    "            <br>\n",
    "            <li>Copy the results of the test to the client - only needs to be a subset of rows if desired</li>\n",
    "            <br>\n",
    "            <li>Visualize the results</li>\n",
    "        </ol>\n",
    "    </td>\n",
    "    <td><img src = 'images/Score_and_Evaluate.png' width = '400'></td>\n",
    "</tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7dd3f5de-48ac-40e8-8815-12066e927438",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Obtain a pointer to the model\n",
    "table_name = 'energy_models'\n",
    "model_id = 'lr'\n",
    "model_lr = retrieve_byom(model_id, table_name=table_name)\n",
    "df_test = DataFrame('energy_consumption_variables_rescaled_test')\n",
    "\n",
    "result_lr = PMMLPredict(\n",
    "    modeldata=model_lr,\n",
    "    newdata=df_test,\n",
    "    accumulate=['TD_TIMECODE', 'consumption'],\n",
    ").result\n",
    "\n",
    "result_lr = result_lr.assign(prediction=result_lr.prediction.cast(type_=FLOAT()))\n",
    "result_lr = result_lr.assign(prediction=result_lr.prediction + normalize_value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8548a833-acb7-4368-849e-baecd3aa1e3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_lr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aee4881e-3107-4567-aad6-4daac51b6231",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above step, we use the PMMLPredict method from the teradataml library to score our model in the database. The PMMLPredict function in Teradata allows us to score the PMML model directly on the data in the Vantage system, without having to move the data or the model outside the system. This can help to improve our efficiency and security in the scoring process.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "906c73a7-4832-408b-a3fb-844612a06aef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Obtain a pointer to the model\n",
    "table_name = 'energy_models'\n",
    "model_id = 'rf'\n",
    "model_rf = retrieve_byom(model_id, table_name=table_name)\n",
    "df_test = DataFrame('energy_consumption_variables_rescaled_test')\n",
    "\n",
    "result_rf = PMMLPredict(\n",
    "    modeldata=model_rf,\n",
    "    newdata=df_test,\n",
    "    accumulate=['TD_TIMECODE', 'consumption'],\n",
    ").result\n",
    "\n",
    "result_rf = result_rf.assign(prediction=result_rf.prediction.cast(type_=FLOAT()))\n",
    "result_rf = result_rf.assign(prediction=result_rf.prediction + normalize_value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "warming-blocking",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_rf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a10c2b9-51f9-467d-be59-4e0bab6128c6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above step, we use the PMMLPredict method from the teradataml library to score our model in the database. The PMMLPredict function in Teradata allows us to score the PMML model directly on the data in our Vantage system, without having to move the data or the model outside the system. This can help to improve the efficiency and security of our scoring process.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6989b9d6-cd74-4e66-a2c6-c08051f4bcc1",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>6. Visualize the results</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ff1ac61-10ae-49a4-8019-4db46df2e1c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate RMS errors\n",
    "rms_lr = RegressionEvaluator(data=result_lr, \n",
    "                             observation_column=\"consumption\",\n",
    "                             prediction_column=\"prediction\",\n",
    "                             metrics=['RMSE']).result.get_values()[0][0]\n",
    "\n",
    "rms_rf = RegressionEvaluator(data=result_rf, \n",
    "                             observation_column=\"consumption\",\n",
    "                             prediction_column=\"prediction\",\n",
    "                             metrics=['RMSE']).result.get_values()[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb6ab09a-9b9d-4665-b014-50ba7d3f6148",
   "metadata": {},
   "outputs": [],
   "source": [
    "figure = Figure(width=1400, height=600,  heading=\"Energy Consumption Prediction\")\n",
    "\n",
    "df_test.plot(\n",
    "                x=df_test.TD_TIMECODE,\n",
    "                y=[df_test.consumption, result_lr.prediction, result_rf.prediction],\n",
    "                figure=figure,\n",
    "                xtick_format='YYYY-MM-DD',\n",
    "                xlabel='TD_TIMECODE',\n",
    "                ylabel='Energy Consumption',\n",
    "                legend=['Actual Consumption', f'Linear Regression (RMS = {rms_lr:.2f})', f'Random Forest (RMS = {rms_rf:.2f})'],\n",
    "                legend_style='upper right',\n",
    "                grid_linestyle='--',\n",
    "                grid_linewidth=0.5,\n",
    "                linestyle=['-', '--', '--']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0db5e1b8-f01e-490b-a8f9-15f2d0a201b6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph displays the Root Mean Squared (RMS) error values for both our Linear Regression and Random Forest models. The lower the RMS error value, the better our models' performance. As we can see, Random Forest outperforms Linear Regression in predicting energy demand, as it has a lower RMS error value. Therefore, Random Forest is more suitable for proactively predicting energy demand in our use case.</p>\n",
    "\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 this demonstration, we have illustrated a simplified - but complete - overview of how we can improve a typical machine learning workflow using Vantage in conjunction with open-source tools and techniques. This combination allows us to leverage open-source innovation with Vantage's operational scale, power, and stability.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7688f732-49b9-44ff-9e96-61fdc6cca4cc",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>7. Cleanup</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "185b53b6-095f-44a8-b080-1b9a36bc890d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We need to clean up our work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e05be8b4-29f3-45c8-9f25-d9ea950c17fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['energy_models', 'energy_consumption_variables_rescaled_train', 'energy_consumption_variables_rescaled_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": "d377fef9-b377-4b59-a313-5eb9784dba93",
   "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'>We will use the following code to clean up tables and databases created for this demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9472c2ca-65d0-4116-9c45-6411fcea52ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_Energy');\"        # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adjacent-romantic",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81006a76-9d1a-4472-aaad-421a666bc807",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Required Materials</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",
    "\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Filters:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Industry:</b> Energy and Natural Resource</li>\n",
    "    <li><b>Functionality:</b> Open and Connected Analytics</li>\n",
    "    <li><b>Use Case:</b> Bring Your Own Model</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><a href='https://www.teradata.com/Blogs/Digital-utilities-driving-value-from-analytics'>Digital utilities driving value from advanced analytics at scale</a></li>\n",
    "    <li><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><a href='https://www.teradata.com/Industries/Energy-and-Natural-Resources'>Gain Analytic Insights for the Energy Industry</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58b0b73e-0c50-4110-be9c-05aaf1181960",
   "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,24. 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
}