UseCases/Store_Sales_Forecasting_ARIMA/Store_Sales_Forecasting_ARIMA_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "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",
    "       Store Sales Forecasting with In-Database Time Series\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Retail stores rely on sales and an accurate amount of inventory to support these sales. However, demand can be everchanging leading to stores being overstocked or out of stock. In these situations, retail stores need to quickly adjust to increase revenues and avoid additional unnecessary costs. The best way to keep ROI up is with retail demand forecasting in Teradata Vantage and ClearScape Analytics. Teradata’s capabilities allow users to combine and analyze sales and inventory data across all stores, while taking into consideration seasonal events, such as holidays or the weather. Bringing together all the components that influence customers to buy products allows retail stores to accurately predict sales and demand to ensure for precise inventory.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Good Eats Grocery is a renowned retail corporation that operates a chain of hypermarkets. Here, Good Eats Grocery has provided a data combining of 45 stores including store information and monthly sales. The data is provided on weekly basis. Good Eats Grocery tries to find the impact of holidays on the sales of store. For which it has included four holidays’ weeks into the dataset which are Christmas, Thanksgiving, Super Bowl, Labor Day.<br>\n",
    "<br>  \n",
    "Our Main Objective is to predict sales of store in a week. As in dataset size and time related data are given as feature, so analyze if sales are impacted by time-based factors and space- based factor. Most importantly how inclusion of holidays in a week soars the sales in store?\n",
    "<br>\n",
    "    \n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Business Value</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Predict sales over a specified period of time.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Identify seasonal trends in sales and demand to improve inventory management.</li> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Plan for historic increase and decrease in sales unrelated to the calendar year.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Increase customer satisfaction.</li>  \n",
    "</p>    \n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Unbounded Array Framework (UAF) is the Teradata framework for building end-to-end time series forecasting pipelines. It also provides functions for digital signal processing and 4D spatial analytics. The series can reside in any Teradata supported or Teradata accessible table or in an analytic result table (ART). The UAF architecture provides a range of unique benefits including:</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Rapid data exploration, preparation, and testing functions that can analyze massive amounts of data across an unlimited number of forecasts in parallel; drastically reducing the development and testing times.</li> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The creation of a nearly unlimited number of forecasts in parallel, unlocking value in hyper-segmented (per-store-per-SKU inventory demand, per-household energy consumption) predictions, based on individualized models.</li> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The ability to deploy the preparation and forecasting functions into automated pipelines that can run in near-real-time, eliminating the gaps between preparation, development, and deployment. \n",
    "</li></p> \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>UAF provides data scientists with the tools for all phases of forecasting:</p> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Data preparation functions </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Data exploration functions </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Model coefficient estimation functions</li> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Model validation functions </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Model scoring functions </li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Plus, with Teradata Vantage, users can perform these functions at scale and analyze and forecast hundreds/thousands series at once. Time Series analysis requires significant effort in analyzing, preparing, and testing forecast models. Traditional approaches require users to perform these laborious tasks multiple times for each prediction, so scaling forecasting efforts beyond a small number of different forecasts becomes prohibitive.</p>\n",
    "    \n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Data</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The dataset contains historical sales data for 45 Good Eats Grocery stores located in different regions. Each store contains a number of departments, and you are tasked with predicting the department-wide sales for each store.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In addition, Good Eats Grocery runs several promotional markdown events throughout the year. These markdowns precede prominent holidays, the four largest of which are the Super Bowl, Labor Day, Thanksgiving, and Christmas. The weeks including these holidays are weighted five times higher in the evaluation than non-holiday weeks. Part of the challenge presented by this competition is modelling the effects of markdowns on these holiday weeks in the absence of complete/ideal historical data.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The basic idea of analyzing the Good Eats Grocery Forecasting dataset is to get a fair idea about the factors affecting the Sales of the Good Eats Grocery Store.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries and set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "from teradataml import * \n",
    "from teradataml.context.context import *\n",
    "from teradataml.dataframe.dataframe import DataFrame\n",
    "\n",
    "from teradataml.dataframe.copy_to import copy_to_sql\n",
    "import getpass\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "display.max_rows=5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Store_Sales_Forecasting_ARIMA_Python.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Getting Data for This Demo </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_SalesForecasting_cloud');\"\n",
    " # Takes about 45 seconds\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_SalesForecasting_local');\"\n",
    " # Takes about 70 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "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,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Prepare data to do some basic Analysis of the Sales data.</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us start by creating a \"Virtual DataFrame\" that points directly to the dataset in Vantage. We begin our analysis by obtaining the necessary data types for columns and extract values such as Sales_week, Sales_year, etc., from the Sales_date column. These extracted values will be used in our subsequent analysis.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=DataFrame(in_schema('DEMO_SalesForecasting','Weekly_Sales'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml.dataframe.sql_functions import case\n",
    "from teradatasqlalchemy import TIMESTAMP, VARCHAR, INTEGER\n",
    "from sqlalchemy import func\n",
    "df = df.assign(IsHoliday = case([(df.IsHoliday == 0, 'False')], else_ = 'True'))\n",
    "df = df.assign(Sales_Week = func.td_week_of_year(df.Sales_Date.expression))\n",
    "df = df.assign(Sales_Date = df.Sales_Date.cast(type_=TIMESTAMP))\n",
    "df = df.assign(Sales_Year = df.Sales_Date.cast(type_=VARCHAR(10)))\n",
    "df = StrApply(data=df,\n",
    "                   target_columns='Sales_Year',\n",
    "                   string_operation='SUBSTRING',\n",
    "                   string_length = 4,\n",
    "                   accumulate = ['Store', 'Dept', 'Sales_Date', 'Weekly_Sales', 'IsHoliday','Sales_Week'],\n",
    "                   in_place=True).result\n",
    "df = df.assign(Sales_Year = df.Sales_Year.cast(type_=INTEGER))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdf=df\n",
    "testdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b> Let's do some basic analysis of the dataset </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We group the weekly sales by Sales Date and calculate the Average Sales based on Sales date. Alongside aggregating the data, we leverage the InDB plot() function for teradataml dataframes to visualize the data. This allows us to avoid transferring data to the client side even for visualizations.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df=testdf.select(['Sales_Date','Weekly_Sales']).groupby('Sales_Date')\n",
    "df_plot=df.avg()\n",
    "df_plot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import Figure\n",
    "figure = Figure(width=1000, height=800,  heading=\"Average Weekly Sales\")\n",
    "plot = df_plot.plot(x=df_plot.Sales_Date, y=df_plot.avg_Weekly_Sales,\n",
    "                          xtick_format='YYYY-MM',\n",
    "                          xlabel='Week', ylabel='Sales', color=\"blue\",figure=figure)\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the Average Sales per week. We can see that there are peaks mainly during the Year end period.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next we try to get the average sales for each Store, for that we group the Weekly Sales by each Store.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weekly_sales = testdf.select(['Store','Weekly_Sales']).groupby('Store')\n",
    "ws_plot=weekly_sales.avg()\n",
    "ws_plot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from teradataml import Figure\n",
    "figure = Figure(width=1000, height=800,  heading=\"Average Sales per Store\")\n",
    "plot = ws_plot.plot(x=ws_plot.Store, y=ws_plot.avg_Weekly_Sales,\n",
    "                          kind='bar',\n",
    "                          xlabel='Store', ylabel='Sales', figure=figure)\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the Average Weekly Sales for each store. We can see that Store 4 shows highest weekly sales while Store 5 shows the lowest weekly sales.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next we try to get the Weekly Sales for each year separately. For this we group the data for all 3 years by Sales Date for each year</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "week_df = testdf.select(['Sales_Week','Sales_Year','Weekly_Sales'])\n",
    "week_df = week_df.assign(Weekly_Sales_2010 = case([(week_df.Sales_Year == 2010, week_df.Weekly_Sales)], else_ = 0))\n",
    "week_df = week_df.assign(Weekly_Sales_2011 = case([(week_df.Sales_Year == 2011, week_df.Weekly_Sales)], else_ = 0))\n",
    "week_df = week_df.assign(Weekly_Sales_2012 = case([(week_df.Sales_Year == 2012, week_df.Weekly_Sales)], else_ = 0))\n",
    "week_df = week_df.select(['Sales_Week','Weekly_Sales_2010','Weekly_Sales_2011','Weekly_Sales_2012'])\n",
    "week_df = week_df.groupby('Sales_Week')\n",
    "week_df = week_df.avg()\n",
    "week_df = week_df[((week_df.avg_Weekly_Sales_2010 != 0.0 ) & (week_df.avg_Weekly_Sales_2011 != 0.0) &\n",
    "                    (week_df.avg_Weekly_Sales_2012 != 0.0))]\n",
    "week_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import Figure\n",
    "figure = Figure(width=1000, height=600, heading=\"Average Weekly Sales per Year\")\n",
    "week_df.plot(x=week_df.Sales_Week, y=[week_df.avg_Weekly_Sales_2010, week_df.avg_Weekly_Sales_2011, week_df.avg_Weekly_Sales_2012], \n",
    "             style=['dark orange', 'green','blue'], xlabel='Week', ylabel='Sales',  grid_color='black',\n",
    "                   grid_linewidth=0.5, grid_linestyle=\"-\", legend=['2010','2011','2012'],figure=figure)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the Average Weekly Sales for different years. We can see that there are peaks mainly during 10-15th week and 20-30th week.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We try to get the comparison of Sales during Holidays and Other Working Days. We do a grouping of data for Sales based on whether the Sale is on Holiday or Working Day</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "week_holiday_df = testdf.select(['Sales_Date','Sales_Week','IsHoliday','Weekly_Sales'])\n",
    "week_holiday_df = week_holiday_df.assign(Weekly_Sales_True = case([(week_holiday_df.IsHoliday == 'True', week_holiday_df.Weekly_Sales)], else_ = 0))\n",
    "week_holiday_df = week_holiday_df.assign(Weekly_Sales_False = case([(week_holiday_df.IsHoliday == 'False', week_holiday_df.Weekly_Sales)], else_ = 0))\n",
    "week_holiday_df = week_holiday_df.select(['Sales_Date','Sales_Week','Weekly_Sales_True','Weekly_Sales_False'])\n",
    "week_holiday_df = week_holiday_df.groupby(['Sales_Date','Sales_Week'])\n",
    "week_holiday_df = week_holiday_df.sum()\n",
    "week_holiday_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import Figure\n",
    "figure = Figure(width=1000, height=600, heading=\"Total Sales per Week\")\n",
    "week_holiday_df.plot(x=week_holiday_df.Sales_Week, y=[week_holiday_df.sum_Weekly_Sales_True, week_holiday_df.sum_Weekly_Sales_False], \n",
    "             style=['blue','brown'], xlabel='Week', ylabel='Sales',  grid_color='black',\n",
    "                   grid_linewidth=0.5, grid_linestyle=\"-\", legend=['IsHoliday True','IsHoliday False'],kind='bar', figure=figure)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the Weekly Sales per Week. The Orange colored bars show weekly sales during Holidays while the Blue colored bars show weekly sales during working days.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Preparing Dataset by joining the datasets.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Weekly_Sales is our variable of interest. </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Type, Size, Temperature, isHoliday, Fuel_Price, MarkDown1, MarkDown2, MarkDown3, MarkDown4, MarkDown4 are exogenous variables.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We prepare the dataset by creating a view by joining data from Weekly Sales, Stores and features. The view is created using SQL to reduce the number of steps to join and data preocessing which gets used in  further steps.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query2='''REPLACE VIEW Weekly_Sales_Details AS\n",
    "SELECT\n",
    "    w.Sales_date AS times,\n",
    "    CAST('2012-02-03' AS DATE) AS cutoff_date,\n",
    "    w.Dept,\n",
    "    w.Store,\n",
    "    CAST(w.Sales_Date AS TIMESTAMP) AS Sales_Date,\n",
    "    ZEROIFNULL(Weekly_Sales) AS Weekly_Sales,\n",
    "    ZEROIFNULL(Store_Size) AS Store_Size,\n",
    "    Store_Type AS Store_Type,\n",
    "    w.IsHoliday,\n",
    "    ZEROIFNULL(Temperature) AS Temperature,\n",
    "    ZEROIFNULL(MarkDown1) AS MarkDown1,\n",
    "    ZEROIFNULL(MarkDown2) AS MarkDown2,\n",
    "    ZEROIFNULL(MarkDown3) AS MarkDown3,\n",
    "    ZEROIFNULL(MarkDown4) AS MarkDown4,\n",
    "    ZEROIFNULL(MarkDown5) AS MarkDown5,\n",
    "    ZEROIFNULL(CPI) AS CPI,\n",
    "    ZEROIFNULL(Unemployment) AS Unemployment,\n",
    "    ZEROIFNULL(Fuel_Price) AS Fuel_Price,\n",
    "    CAST(TRIM(w.Dept) || TRIM(w.Store) AS INT) AS idcols\n",
    "FROM\n",
    "    Demo_SalesForecasting.Weekly_Sales w\n",
    "LEFT JOIN\n",
    "    Demo_SalesForecasting.Stores s ON w.Store = s.Store\n",
    "LEFT JOIN\n",
    "    Demo_SalesForecasting.Features f ON w.Store = f.store AND w.Sales_Date = f.Sales_Date\n",
    "WHERE\n",
    "    w.Store IN (20, 4);\n",
    "'''\n",
    "\n",
    "execute_sql(query2)\n",
    "modeldf=DataFrame.from_query('select * from Weekly_Sales_Details;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfacheck = modeldf.groupby([\"idcols\"])\n",
    "dfacheck=dfacheck.count().select([\"idcols\",\"count_Sales_Date\"])\n",
    "\n",
    "dfa4=modeldf.join(dfacheck, on = 'idcols', how = \"left\", lsuffix = 't1', rsuffix = 't2').drop(['t2_idcols'],axis=1)\n",
    "dfa4=dfa4.assign(idcols = dfa4['t1_idcols'])\n",
    "dfa4=dfa4.drop(['t1_idcols'],axis=1)\n",
    "\n",
    "# filter out incomplete time series \n",
    "\n",
    "modeldf1 = dfa4[dfa4.count_Sales_Date == 143]\n",
    "modeldf1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "modeldf1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Checking for Stationarity of Time Series using the Dickey Fuller Test</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To be able to model a time series, it needs to be stationary. ARIMA models deal with non-stationary time series by differencing (The \"d' parameter in ARIMA determines the number of differences needed to make a series stationary)</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we will check for stationarity of all time series using the Dickey-Fuller Test. For more info on the test,  see <a href=\"https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-VantageTM-Unbounded-Array-Framework-Time-Series-Reference-17.20/Diagnostic-Statistical-Test-Functions/TD_DICKEY_FULLER/TD_DICKEY_FULLER-Example\">here.</a> \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The null hypothesis for the test is that the data is non-stationary. We want to REJECT the null hypothesis for this test. So, we want a p-value of less than 0.05 (or smaller) and a negative coefficient value for the lag term in our regression model.</p> \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Dickey fuller function needs series data, so we use the TDSeries function to create a series and apply DickeyFuller to check the stationarity of the data.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use the OutlierFilterFit and the OutlierFilterTransform functions to remove the outliers in the series and then use the Rescaled Data to check the stationarity of the data using the DickeyFuller function.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_df=modeldf1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The OutlierFilterFit() function calculates the lower_percentile, upper_percentile, count of rows and median for all the \"target_columns\" provided by the user. These metrics for each column helps the function OutlierTransform() detect outliers in the input table. It also stores parameters from arguments into a FIT table used during transformation. The lower_percentile specifies lower range of percentile to be used to detect if value is outlier or not and the upper_percentile specifies upper range of percentile to be used to detect if value is outlier or not.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import OutlierFilterFit\n",
    "OutlierFilterFit_out = OutlierFilterFit(data = sales_df,\n",
    "                                            target_columns = \"Weekly_Sales\",\n",
    "                                               )\n",
    "out_df=OutlierFilterFit_out.output_data\n",
    "out_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> The OutlierFilterfit creates a fit table with different values which need to be applied on the data to get the transformed data.</p>\n",
    "<p></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> OutlierFilterTransform() function filters the outliers from the input teradataml DataFrame.</p> \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>OutlierFilterTransform() uses the result DataFrame from OutlierFilterFit() function to get statistics like median, count of rows, lower percentile and upper percentile for every column specified in target columns argument and filters the outliers in the input data. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import OutlierFilterFit, OutlierFilterTransform\n",
    "obj = OutlierFilterTransform(data=sales_df,\n",
    "                                 object=OutlierFilterFit_out.result)\n",
    "out_transform_df = obj.result\n",
    "out_transform_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> The OutlierFilterTransform transforms the data and creates the output data after applying the Fit Table details on the data.</p>\n",
    "<p></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Resample() function transforms an irregular time series into a regular time series. It can also be used to alter the sampling interval for a time series. The Resample functions requires a series as inuput for which we use the TDSeries function.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TDSeries object from a teradataml DataFrame representing a SERIES in time series which is used as input to Unbounded Array Framework, time series functions. A series is a one-dimensional array. They are the basic input of UAF functions. A series is identified by its series ID, i.e., \"id\" argument, and indexed by \"row_index\" argument. Series is passed to and returned from UAF functions as wavelets. Wavelets are collections of rows, grouped by one or more fields, and ordered on the \"row_index\" argument.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import Resample\n",
    "data_series_df = TDSeries(data=obj.result,\n",
    "                              id=\"idcols\",\n",
    "                              row_index=(\"Sales_Date\"),\n",
    "                              row_index_style= \"TIMECODE\",\n",
    "                              payload_field=\"Weekly_Sales\",\n",
    "                              payload_content=\"REAL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uaf_out1 = Resample(data=data_series_df,\n",
    "                        interpolate='LINEAR',\n",
    "                        timecode_start_value=\"TIMESTAMP '2010-02-05 00:00:00'\",\n",
    "                        timecode_duration=\"WEEKS(1)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=uaf_out1.result\n",
    "df1=df.select(['idcols','ROW_I', 'Weekly_Sales']).assign(Sales_Date=df.ROW_I)\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The DickeyFuller() function tests for the presence of one or more unit roots in a series to determine if the series is non-stationary. When a series contains unit roots, it is non-stationary. When a series contains no unit roots, whether the series is stationary is based on other factors.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import DickeyFuller\n",
    "data_series_df_1 = TDSeries(data=df1,\n",
    "                              id=\"Sales_Date\",\n",
    "                              row_index=(\"idcols\"),\n",
    "                              row_index_style= \"SEQUENCE\",\n",
    "                              payload_field=\"Weekly_Sales\",\n",
    "                              payload_content=\"REAL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_out = DickeyFuller(   data=data_series_df_1,\n",
    "                           algorithm='NONE')\n",
    "\n",
    "# Print the result DataFrame.\n",
    "print(df_out.result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above output the p-value corresponding to the calculated test statistic is less than 0.05. It means that the series is stationary. The output column NULL_HYP which means NULL HYPOTHESIS can have 2 values \n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>ACCEPT means the null hypothesis is accepted. No Unit roots are present, and therefore the process is stationary.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:##00233C'>REJECT means the null hypothesis is rejected. Unit roots are present, and the process may or may not be stationary, depending on other factors.</li>\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. ARIMA Modelling</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>ARIMA stands for Autoregressive Integrated Moving Average. It is a statistical method used for time series forecasting and analysis. ARIMA is a form of regression analysis that gauges the strength of one dependent variable relative to other changing variables. ARIMA models are popular in various fields, including finance, economics, and environmental science, for predicting future points in a time series based on its historical values.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ArimaEstimate() function estimates the coefficients corresponding to an ARIMA (AutoRegressive Integrated Moving Average) model, and to fit a series with an existing ARIMA model. The function can also provide the \"goodness of fit\" and the residuals of the fitting operation. The function generates model layer used as input for the ArimaValidate() and ArimaForecast() functions. This function is for univariate series.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following procedure is an example of how to use ArimaEstimate() function:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial'> Run the ArimaEstimate() function to get the coefficients for the ARIMA model.\n",
    "<li style = 'font-size:16px;font-family:Arial'> [Optional] Run ArimaValidate() function to validate the 'goodness of fit' of the ARIMA model, when \"fit_percentage\" argument value is not 100 in ArimaEstimate() function.\n",
    "<li style = 'font-size:16px;font-family:Arial'>Run the ArimaForecast() function with input from step 1 or step 2 to forecast the future periods beyond the last observed period.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Here the input series to the ArimaEstimate is the output series of the Resample function. The series is created by using the output of Resample function and passed to ArimaEstimate. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ArimaEstimate\n",
    "# Execute ArimaEstimate function.\n",
    "arima_est_out = ArimaEstimate(data1=data_series_df_1,\n",
    "                            nonseasonal_model_order=[2,1,1],\n",
    "                            constant=False,\n",
    "                            algorithm=\"MLE\",\n",
    "                            coeff_stats=True,\n",
    "                            fit_metrics=True,\n",
    "                            residuals=True,\n",
    "                            fit_percentage=80)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "est_result=arima_est_out.fitresiduals\n",
    "est_result = est_result.groupby('Sales_Date').avg()\n",
    "est_result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Inter;color:#00233C'>We plot the Actual Value of Weekly Sales vs the Calculated Value of the ArimaEstimate function. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from teradataml import Figure\n",
    "figure = Figure(width=1000, height=700, heading=\"Comparison of Actual vs Predicted Sales\")\n",
    "est_result.plot(x=est_result.Sales_Date, y=[est_result.avg_ACTUAL_VALUE, est_result.avg_CALC_VALUE], \n",
    "             style=['dark orange', 'green'], xlabel='Sales Date', ylabel='Sales',  grid_color='black',xtick_format='YYYY-MM',\n",
    "                   grid_linewidth=0.5, grid_linestyle=\"-\", legend=['Actual Value','Predicted Value'],figure=figure)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ArimaValidate() function performs an in-sample forecast for both seasonal and non-seasonal auto-regressive (AR), moving-average (MA), ARIMA models and Box-Jenkins seasonal ARIMA model formula followed by an analysis of the produced residuals. The aim is to provide a collection of metrics useful to select the model and expose the produced residuals such that multiple model validation and statistical tests can be conducted.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ArimaValidate\n",
    "data_art_df = TDAnalyticResult(data=arima_est_out.result)\n",
    "\n",
    "\n",
    "arima_val_out = ArimaValidate(data=data_art_df, fit_metrics=True, residuals=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "val_result=arima_val_out.fitresiduals\n",
    "val_result = val_result.groupby('Sales_Date').avg()\n",
    "val_result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Inter;color:#00233C'>We plot the Actual Value of Weekly Sales vs the Calculated Value of the ArimaValidate function. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from teradataml import Figure\n",
    "figure = Figure(width=1000, height=700, heading=\"Comparison of Actua vs Predicted\")\n",
    "val_result.plot(x=val_result.Sales_Date, y=[val_result.avg_ACTUAL_VALUE, val_result.avg_CALC_VALUE], \n",
    "             style=['dark orange', 'green'], xlabel='Sales Date', ylabel='Sales',  grid_color='black',\n",
    "                   grid_linewidth=0.5, grid_linestyle=\"-\", legend=['Actual Value','Predicted Value'],figure=figure)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ArimaForecast() function is used to forecast a user-defined number of periods based on models fitted from the ArimaEstimate() function.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we are considering 7 periods (forecast_periods=7)</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ArimaForecast\n",
    "arima_estimate_op = ArimaEstimate(data1=data_series_df_1,\n",
    "                                      nonseasonal_model_order=[2,1,1],\n",
    "                                      constant=False,\n",
    "                                      algorithm=\"MLE\",\n",
    "                                      coeff_stats=True,\n",
    "                                      fit_metrics=True,\n",
    "                                      residuals=True,\n",
    "                                      fit_percentage=100)\n",
    "\n",
    "# Create teradataml TDAnalyticResult object over the result attribute of 'arima_estimate_op'\n",
    "data_art_df = TDAnalyticResult(data=arima_estimate_op.result)\n",
    " \n",
    "arima_forcast_out = ArimaForecast(data=data_art_df, forecast_periods=7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "forecast_result=arima_forcast_out.result\n",
    "forecast_result = forecast_result.groupby('ROW_I').avg()\n",
    "forecast_result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Inter;color:#00233C'>We plot the Forecasted Value of Weekly Sales for the defined number of periods. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import Figure\n",
    "figure = Figure(width=1000, height=700, heading=\"Forecast Sales\")\n",
    "forecast_result.plot(x=forecast_result.ROW_I, y=forecast_result.avg_FORECAST_VALUE, \n",
    "              xlabel='Forecast Period', ylabel='Forecast Sales',  grid_color='black',\n",
    "                   grid_linewidth=0.5, grid_linestyle=\"-\", figure=figure)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Conclusion:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have trained and validated the ARIMA model on the Weekly Sales dataset, and the results closely match the actual data. The goodness of fit metrics calculated in the estimate and validate phase also resonate with our understanding that the model is well-trained to forecast. This can be observed in the Estimate and the Validate function graphs. So, we can say that the model is well trained to forecast the Weekly Sales.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus with Teradata Vantage we can do rapid data exploration, preparation, and testing functions that can analyze massive amounts of data across an unlimited number of forecasts in parallel, drastically reducing the development and testing times. We can create unlimited number of forecasts in parallel, unlocking value in hyper-segmented (per-store-per-SKU inventory demand, per-household energy consumption) predictions, based on individualized models. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Cleanup</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_SalesForecasting');\" \n",
    "#Takes 45 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\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",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Dataset</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This is the historical data that covers sales from 2010-02-05 to 2012-11-01. Within this file you will find the following fields:</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Store - the store number</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Date - the week of sales</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Weekly_Sales - sales for the given store</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Holiday_Flag - whether the week is a special holiday week 1 – Holiday week 0 – Non-holiday week</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Temperature - Temperature on the day of sale</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Fuel_Price - Cost of fuel in the region</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>CPI – Prevailing consumer price index</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Unemployment - Prevailing unemployment rate</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Holiday Events: Super Bowl, Labour Day, Thanksgiving, Christmas </li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Filters:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Industry:</b> Retail</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Functionality:</b> ARIMA Estimate and Forecast</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use Case:</b> Sales Forecasting</li>\n",
    "</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/blogs/nps-is-a-metric-not-the-goal'>In the fight to improve customer experience, NPS is a metric, not the goal</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/Blogs/Hyper-scale-time-series-forecasting-done-right'>Hyper-scale time series forecasting done right </a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/Blogs/Crystal-Ball-or-Black-Box-in-Retail-and-CPG'>Crystal Ball, Black Box or Advanced Forecasting and Demand Planning in Retail and CPG</a></li>"
   ]
  },
  {
   "cell_type": "markdown",
   "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,2024. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}