docs-samples/data-science/ai-samples/python/AIsample - Superstore Forecast.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bbdd4074-f4db-40f5-86d2-cbf2c3766f85",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "# Develop, evaluate, and score a forecasting model for superstore sales"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24139096-b912-4e14-aee7-37615c14b18d",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "## Introduction\n",
    "\n",
    "In this notebook, you walk through the Synapse Data Science in Microsoft Fabric workflow with an end-to-end example. The scenario is to build a forecasting model that uses historic sales data to predict the sales for different categories of products at a superstore.\n",
    "\n",
    "Forecasting is a crucial asset in sales, harnessing historical data and predictive methods to provide insights into future trends. You can analyze past sales to identify patterns, and learn from consumer behavior, to optimize inventory, production, and marketing strategies. This proactive approach enhances adaptability, responsiveness, and overall performance of businesses in a dynamic marketplace.\n",
    "\n",
    "The main steps in this notebook are:\n",
    "\n",
    "1. Load the data\n",
    "2. Understand and process the data using exploratory data analysis\n",
    "3. Train a machine learning model using an open source software package called `SARIMAX` and track experiments using MLflow and Fabric Autologging feature\n",
    "4. Save the final machine learning model and make predictions\n",
    "5. Demonstrate the model performance via visualizations in Power BI\n",
    "\n",
    "\n",
    "## Prerequisites\n",
    "- [Add a lakehouse](https://aka.ms/fabric/addlakehouse) to this notebook. You will be downloading data from a public blob, then storing the data in the lakehouse. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8410a37-b3dd-427c-a194-0db297d7781e",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "## Step 1: Load the Data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2adaef3-0586-47d8-9f8d-c0ab96be78ea",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "### Dataset\n",
    "\n",
    "The dataset contains 9995 instances of sales of different products, along with 21 attributes that include: `Row ID`, `Order ID`, `Order Date`, `Ship Date`, `Ship Mode`, `Customer ID`, `Customer Name`, `Segment`, `Country`, `City`, `State`, `Postal Code`, `Region`, `Product ID`, `Category`, `Sub-Category`, `Product Name`, `Sales`, `Quantity`, `Discount`, and `Profit`.\n",
    "\n",
    "- Superstore.xlsx\n",
    "\n",
    "|\"Row ID\"|\"Order ID\"|\"Order Date\"|\"Ship Date\"|\"Ship Mode\"|\"Customer ID\"|\"Customer Name\"|\"Segment\"|\"Country\"|\"City\"|\"State\"|\"Postal Code\"|\"Region\"|\"Product ID\"|\"Category\"|\"Sub-Category\"|\"Product Name\"|\"Sales\"|\"Quantity\"|\"Discount\"|\"Profit\"|\n",
    "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
    "|4|US-2015-108966|2015-10-11|2015-10-18|Standard Class|SO-20335|Sean O'Donnell|Consumer|United States|Fort Lauderdale|Florida|33311|South|FUR-TA-10000577|Furniture|Tables|Bretford CR4500 Series Slim Rectangular Table|957.5775|5|0.45|-383.0310|\n",
    "|11|CA-2014-115812|2014-06-09|2014-06-09|Standard Class|Standard Class|Brosina Hoffman|Consumer|United States|Los Angeles|California|90032|West|FUR-TA-10001539|Furniture|Tables|Chromcraft Rectangular Conference Tables|1706.184|9|0.2|85.3092|\n",
    "|31|US-2015-150630|2015-09-17|2015-09-21|Standard Class|TB-21520|Tracy Blumstein|Consumer|United States|Philadelphia|Pennsylvania|19140|East|OFF-EN-10001509|Office Supplies|Envelopes|Poly String Tie Envelopes|3.264|2|0.2|1.1016\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85ef637d-f6b8-4d52-a19d-63163902dd33",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "\n",
    "> [!TIP]\n",
    "> By defining the following parameters, you can apply this notebook on different datasets easily."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a1b555d-ddb2-4cf2-86d2-ea79277d456c",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "IS_CUSTOM_DATA = False  # if TRUE, dataset has to be uploaded manually\n",
    "\n",
    "IS_SAMPLE = False  # if TRUE, use only rows of data for training, otherwise use all data\n",
    "SAMPLE_ROWS = 5000  # if IS_SAMPLE is True, use only this number of rows for training\n",
    "\n",
    "DATA_ROOT = \"/lakehouse/default\"\n",
    "DATA_FOLDER = \"Files/salesforecast\"  # folder with data files\n",
    "DATA_FILE = \"Superstore.xlsx\"  # data file name\n",
    "\n",
    "EXPERIMENT_NAME = \"aisample-superstore-forecast\"  # MLflow experiment name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45a76433-0182-40f8-97dd-473558f7e2ce",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "### Download the dataset and Upload to the lakehouse\n",
    "\n",
    "The following code downloads a publicly available version of the dataset and then stores it in a Fabric lakehouse.\n",
    "\n",
    "> [!IMPORTANT]\n",
    "> Make sure you [add a lakehouse](https://aka.ms/fabric/addlakehouse) to the notebook before running it. Failure to do so will result in an error."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3728782a-ea32-4f4c-8121-5303979318de",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "import os, requests\n",
    "if not IS_CUSTOM_DATA:\n",
    "    # Download data files into lakehouse if not already there\n",
    "    remote_url = \"https://synapseaisolutionsa.blob.core.windows.net/public/Forecast_Superstore_Sales\"\n",
    "    file_list = [\"Superstore.xlsx\"]\n",
    "    download_path = \"/lakehouse/default/Files/salesforecast/raw\"\n",
    "\n",
    "    if not os.path.exists(\"/lakehouse/default\"):\n",
    "        raise FileNotFoundError(\n",
    "            \"Default lakehouse not found, please add a lakehouse and restart the session.\"\n",
    "        )\n",
    "    os.makedirs(download_path, exist_ok=True)\n",
    "    for fname in file_list:\n",
    "        if not os.path.exists(f\"{download_path}/{fname}\"):\n",
    "            r = requests.get(f\"{remote_url}/{fname}\", timeout=30)\n",
    "            with open(f\"{download_path}/{fname}\", \"wb\") as f:\n",
    "                f.write(r.content)\n",
    "    print(\"Downloaded demo data files into lakehouse.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ee1e3a9-9385-40b6-b0ee-a452007d1801",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "Start recording the time it takes to run this notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "864ce4fe-d226-4d3f-b130-2bd7aab76ab7",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "# Record the notebook running time\n",
    "import time\n",
    "\n",
    "ts = time.time()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1d4c295-cc18-4e0b-90d7-d0ccc3513303",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "### Set up MLflow experiment tracking\n",
    "\n",
    "Microsoft Fabric extends the MLflow autologging capabilities by automatically capturing the values of input parameters and output metrics of a machine learning model as it's being trained. This information is then logged to the workspace, where it can be accessed and visualized using the MLflow APIs or the corresponding experiment in the workspace. To learn more about autologging, see [Autologging in Microsoft Fabric](https://aka.ms/fabric-autologging)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3d98886-7464-4b17-831a-975f2db012a3",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "# Set up MLflow for experiment tracking\n",
    "import mlflow\n",
    "\n",
    "mlflow.set_experiment(EXPERIMENT_NAME)\n",
    "mlflow.autolog(disable=True)  # Disable MLflow autologging"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cb4daa2-2754-40c8-a4f3-543af8756ddb",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "> [!NOTE]\n",
    "> If you want to disable Microsoft Fabric autologging in a notebook session, call `mlflow.autolog()` and set `disable=True`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd74ddd2-f9f3-4e1e-9ee2-bd9523adea9e",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "### Read raw data from the lakehouse\n",
    "\n",
    "Read raw data from the **Files** section of the lakehouse. Add more columns for different date parts and the same information is used to create partitioned delta table. Since the raw date is stored as an Excel file, you need to use Pandas to read the raw data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5596af6-98bc-4fae-b231-9f7a769e048e",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(\"/lakehouse/default/Files/salesforecast/raw/Superstore.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6cc64bf-05e3-4891-a53d-a5f8183047a5",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "## Step 2: Perform Exploratory Data Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cd33a7c-6ac8-41c9-ad96-9785917ff2f3",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "### Import libraries\n",
    "\n",
    "Before any analysis, you need to import the required libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "babed862-7eb7-4253-a0b8-9252552f9dfe",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "# Importing required libraries\n",
    "import warnings\n",
    "import itertools\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "plt.style.use('fivethirtyeight')\n",
    "import pandas as pd\n",
    "import statsmodels.api as sm\n",
    "import matplotlib\n",
    "matplotlib.rcParams['axes.labelsize'] = 14\n",
    "matplotlib.rcParams['xtick.labelsize'] = 12\n",
    "matplotlib.rcParams['ytick.labelsize'] = 12\n",
    "matplotlib.rcParams['text.color'] = 'k'\n",
    "from sklearn.metrics import mean_squared_error,mean_absolute_percentage_error"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57a289a5-bd64-41e9-918f-a1657d353bfd",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "### Display the raw data\n",
    "\n",
    "To review the dataset, it's recommended to manually go through a subset of the data to gain a better understanding. In this regard, you could use the `display` function to print the DataFrame. You can also show the `Chart` views to easily visualize subsets of the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "987db79a-800b-4edc-bf60-0bfdeec60561",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df926ec4-28d4-492e-8788-cd8fa479f16a",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "In this notebook, the primary focus is on forecasting the sales for the `Furniture` category. This choice is made to speed up the computation and facilitate the demonstration of the model's performance. However, it is important to realize that the techniques used in this notebook are adaptable and can be extended to predict the sales of various other product categories."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f83a585f-3b30-4063-822d-d99a15497e2a",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "# Select \"Furniture\" as the product category\n",
    "furniture = df.loc[df['Category'] == 'Furniture']\n",
    "print(furniture['Order Date'].min(), furniture['Order Date'].max())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3071655-922c-4a39-9a61-87f8a38e0ee9",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "### Pre-process the data\n",
    "\n",
    "When forecasting the sales in real-world business scenarios, there is often a need to predict sales in three distinct categories:\n",
    "\n",
    "- Forecast sales for a specific category of customers\n",
    "- Forecast sales for a specific product category and customer combination\n",
    "- Forecast overall sales for a specific product category\n",
    "\n",
    "The focus here is to forecast overall sales for a specific product category. First, perform some pre-processing on the data by dropping unnecessary columns that include `Row ID`, `Order ID`, `Ship Date`, `Ship Mode`, `Customer ID`, `Customer Name`, `Segment`, `Country`, `City`, `State`, `Postal Code`, `Region`, `Product ID`, `Category`, `Sub-Category', 'Product Name', 'Quantity', 'Discount', 'Profit`. \n",
    "\n",
    "Some of the columns such as `Row ID`, `Order ID`,`Customer ID`, `Customer Name` have no impact and you can drop them. Moreover, since the focus is to forecast the overall sales for the `Funiture` category across the state and region, you can also drop columns such as `State`, `Region`, `Country`, `City`, `Postal Code`. If you need to forecast sales for a specific location or category, you might need to adjust the pre-processing step accordingly.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e44ae0d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data preprocessing\n",
    "cols = ['Row ID', 'Order ID', 'Ship Date', 'Ship Mode', 'Customer ID', 'Customer Name', \n",
    "'Segment', 'Country', 'City', 'State', 'Postal Code', 'Region', 'Product ID', 'Category', \n",
    "'Sub-Category', 'Product Name', 'Quantity', 'Discount', 'Profit']\n",
    "# Drop unnecessary columns\n",
    "furniture.drop(cols, axis=1, inplace=True)\n",
    "furniture = furniture.sort_values('Order Date')\n",
    "furniture.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29db8dae-843d-4358-a8aa-8cdb9524b025",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "The dataset is structured on a daily basis, and since the goal is to develop a model to forecast the sales on a monthly basis, you need to resample on the column `Order Date`.\n",
    "\n",
    "First, group the `Furniture` category by `Order Date` and then calculate the sum of the `Sales` column for each group in order to determine the total sales for each unique `Order Date`. Then, resample the `Sales` column using the `MS` frequency to aggregate the data by month and then you calculate the mean sales value for each month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "951b10b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data Preparation\n",
    "furniture = furniture.groupby('Order Date')['Sales'].sum().reset_index()\n",
    "furniture = furniture.set_index('Order Date')\n",
    "furniture.index\n",
    "y = furniture['Sales'].resample('MS').mean()\n",
    "y = y.reset_index()\n",
    "y['Order Date'] = pd.to_datetime(y['Order Date'])\n",
    "y['Order Date'] = [i+pd.DateOffset(months=67) for i in y['Order Date']]\n",
    "y = y.set_index(['Order Date'])\n",
    "maximim_date = y.reset_index()['Order Date'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97d64f06-7f75-46f9-b154-299b097c5f1b",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "Demonstrate the impact of `Order Date` on the `Sales` for the Furniture category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19b06cfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Impact of order date on the sales\n",
    "y.plot(figsize=(12, 3))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aaf3067b-4566-4157-a485-4d542ce9b4fc",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "Before any statistical analysis, you need to import `statsmodels`, which is a Python module that provides classes and functions for the estimation of many different statistical models, as well as for conducting statistical tests and statistical data exploration."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0dd96724-1465-4396-a777-e985477ee849",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "import statsmodels.api as sm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "482a9d8a-8c12-4512-9cbe-85630e482d4f",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "### Statistical analysis\n",
    "\n",
    "A time series tracks four data elements at set intervals in order to determine the variation of those four elements in the time series pattern. These elements include:\n",
    "\n",
    "- **Level:** Refers to the fundamental component that represents the average value for a specific time period.\n",
    "\n",
    "- **Trend:** Describes whether the time series is decreasing, constant, or increasing over time.\n",
    "\n",
    "- **Seasonality:** Describes the periodic signal in the time series and looks for cyclic occurrences that affect the time series' increasing or decreasing patterns.\n",
    "\n",
    "- **Noise/Residual:** Refers to the random fluctuations and variability in the time series data that can't be explained by the model.\n",
    "\n",
    "In the following, you observe these four components for your dataset after the pre-processing.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6ccde91-f2a9-4c8f-bfec-2616ae37a152",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "# Decompose the time series into its components using statsmodels\n",
    "result = sm.tsa.seasonal_decompose(y, model='additive')\n",
    "\n",
    "# Labels and corresponding data for plotting\n",
    "components = [('Seasonality', result.seasonal),\n",
    "              ('Trend', result.trend),\n",
    "              ('Residual', result.resid),\n",
    "              ('Observed Data', y)]\n",
    "\n",
    "# Create subplots in a grid\n",
    "fig, axes = plt.subplots(nrows=4, ncols=1, figsize=(12, 7))\n",
    "plt.subplots_adjust(hspace=0.8)  # Adjust vertical space\n",
    "axes = axes.ravel()\n",
    "\n",
    "# Plot the components\n",
    "for ax, (label, data) in zip(axes, components):\n",
    "    ax.plot(data, label=label, color='blue' if label != 'Observed Data' else 'purple')\n",
    "    ax.set_xlabel('Time')\n",
    "    ax.set_ylabel(label)\n",
    "    ax.set_xlabel('Time', fontsize=10)\n",
    "    ax.set_ylabel(label, fontsize=10)\n",
    "    ax.legend(fontsize=10)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ced1b64f-89da-4831-b525-1597eb20a8ef",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "The plots help you understand the seasonality, trend, and noise in the forecasting data. You can capture the underlying patterns and develop models that make more accurate predictions that are resilient to random fluctuations."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b3a5d90-b0a4-45e6-8b44-7a50b88bc2bf",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "## Step 3: Model Training and Tracking"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06b87879-bd4c-4fc8-a6e7-74ddb934ba5a",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "With your data in place, you can define the forecasting model. Apply the Seasonal AutoRegressive Integrated Moving Average with eXogenous regressors (SARIMAX) in this notebook. SARIMAX is a time series forecasting model that extends SARIMA to include exogenous variables. It combines autoregressive (AR) and moving average (MA) components, seasonal differencing, and external predictors to make accurate and flexible forecasts for time series data, making it a powerful tool for various forecasting tasks.\n",
    "\n",
    "You will also use MLfLow and Fabric Autologging to track the experiments. Here you'll load the delta table from the lakehouse. You may use other delta tables considering the lakehouse as the source."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45679817-ae38-44cf-b34f-15daf8e0c1ca",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "# Import required libraries for model evaluation\n",
    "from sklearn.metrics import mean_squared_error, mean_absolute_percentage_error"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0451e60b-3530-40fe-8453-1ab7672ca72a",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "### Hyperparameter tuning\n",
    "\n",
    "SARIMAX takes into account the parameters involved in regular ARIMA mode `(p,d,q)` and also adds the seasonality parameters `(P,D,Q,s)`. These arguments to SARIMAX model are called order `(p,d,q)` and seasonal order `(P,D,Q,s)`, respectively, and hence there are total of 7 parameters to tune. Before model training, you need to set up these parameters, which are defined in the following.\n",
    "\n",
    "#### Order Parameters `(p, d, q)`:\n",
    "- `p`: The order of the autoregressive (AR) component, indicating how many past observations are considered. Also known as the AR order.\n",
    "- `d`: The degree of differencing required to make the time series stationary. Also known as the differencing order.\n",
    "- `q`: The order of the moving average (MA) component, indicating how many past white noise error terms are considered. Also known as the MA order.\n",
    "\n",
    "#### Seasonal Order Parameters `(P, D, Q, s)`:\n",
    "\n",
    "- `P`: The seasonal order of the autoregressive (AR) component, similar to `p` but for the seasonal part.\n",
    "- `D`: The seasonal order of differencing, similar to `d` but for the seasonal part.\n",
    "- `Q`: The seasonal order of the moving average (MA) component, similar to `q` but for the seasonal part.\n",
    "- `s`: The number of time steps per seasonal cycle (for example, 12 for monthly data with a yearly seasonality)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9636773-493f-43cb-9610-e7745a484d51",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "# Hyperparameters tuning\n",
    "p = d = q = range(0, 2)\n",
    "pdq = list(itertools.product(p, d, q))\n",
    "seasonal_pdq = [(x[0], x[1], x[2], 12) for x in list(itertools.product(p, d, q))]\n",
    "print('Examples of parameter combinations for Seasonal ARIMA...')\n",
    "print('SARIMAX: {} x {}'.format(pdq[1], seasonal_pdq[1]))\n",
    "print('SARIMAX: {} x {}'.format(pdq[1], seasonal_pdq[2]))\n",
    "print('SARIMAX: {} x {}'.format(pdq[2], seasonal_pdq[3]))\n",
    "print('SARIMAX: {} x {}'.format(pdq[2], seasonal_pdq[4]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e6bf261-af73-49bb-b34b-b6d574d0a158",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "- The autoregressive order `p` represents the number of past observations in the time series that are used to predict the current value. Typically, `p` should be a non-negative integer. Common values for `p` are usually in the range of 0 to 3, although higher values are possible depending on the specific characteristics of the data. A higher p indicates a longer memory of past values in the model.\n",
    "\n",
    "- The moving average order `q` represents the number of past white noise error terms that are used to predict the current value. Similar to `p`, `q` should also be a non-negative integer. Common values for `q` are typically in the range of 0 to 3, but higher values might be necessary for certain time series. A higher `q` indicates a stronger reliance on past error terms to make predictions.\n",
    "\n",
    "- The differencing order `d` represents the number of times the time series needs to be differenced to achieve stationarity. `d` should be a non-negative integer. Common values for `d` are usually in the range of 0 to 2. A `d` value of 0 means the time series is already stationary, while higher values indicate the number of differencing operations required to make it stationary.\n",
    "\n",
    "- The `enforce_stationarity` parameter controls whether or not the model should enforce stationarity on the time series data before fitting the SARIMAX model. When `enforce_stationarity` is set to `True` (the default), it indicates that the SARIMAX model should enforce stationarity on the time series data. When `True`, the SARIMAX model automatically applies differencing to the data to make it stationary, as specified by the `d` and `D` orders, before fitting the model. This is a common practice because many time series models, including SARIMAX, assume that the data is stationary. If your time series is non-stationary (for example, it exhibits trends or seasonality), it's generally a good practice to set `enforce_stationarity` to `True` and let the SARIMAX model handle the differencing to achieve stationarity. If your time series is already stationary (for example, it has no trends or seasonality), you can set `enforce_stationarity` to `False` to avoid unnecessary differencing.\n",
    "\n",
    "- The `enforce_invertibility` parameter controls whether or not the model should enforce invertibility on the estimated parameters during the optimization process. When `enforce_invertibility` is set to `True` (the default), it indicates that the SARIMAX model should enforce invertibility on the estimated parameters. Invertibility ensures that the model is well-defined and that the estimated autoregressive (AR) and moving average (MA) coefficients are within the range of stationarity. Enforcing invertibility is typically recommended to ensure that the SARIMAX model adheres to the theoretical requirements for a stable time series model. It also helps prevent issues with model estimation and stability.\n",
    "\n",
    "- The default is an `AR(1)` model, which refers to `(1,0,0)`. However, keep in mind that the appropriate values for `p`, `q`, and `d` can vary from one time series to another, and determining the optimal values often involves analyzing the autocorrelation and partial autocorrelation functions (ACF and PACF) of the time series data and using model selection criteria like the Akaike Information Criterion (AIC) or Bayesian Information Criterion (BIC). It's common practice to try different combinations of p, q, and d and evaluate the model's performance for a given dataset. The parameters for the seasonal order `(P, D, Q, s)` are similar in concept to the non-seasonal order parameters `(p, q, d)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "300118bd-1588-47fd-b566-019ec84e0d46",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "# Tune the hyperparameters to determine the best model\n",
    "for param in pdq:\n",
    "    for param_seasonal in seasonal_pdq:\n",
    "        try:\n",
    "            mod = sm.tsa.statespace.SARIMAX(y,\n",
    "                                            order=param,\n",
    "                                            seasonal_order=param_seasonal,\n",
    "                                            enforce_stationarity=False,\n",
    "                                            enforce_invertibility=False)\n",
    "            results = mod.fit(disp=False)\n",
    "            print('ARIMA{}x{}12 - AIC:{}'.format(param, param_seasonal, results.aic))\n",
    "        except:\n",
    "            continue"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2980695-34ab-4b21-8565-d9d0ee3c5f16",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "Upon evaluation of the above results, you can determine the values for both the order parameters and then seasonal order parameters. The choice is `order=(0, 1, 1)` and `seasonal_order=(0, 1, 1, 12)`, which offers the lowest AIC (for example, 279.58) and hence are used to train the model in the following."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18e59783-2035-4173-b49f-4abde5c8538f",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "#### Train the model\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76c25288",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Model Training \n",
    "mod = sm.tsa.statespace.SARIMAX(y,\n",
    "                                order=(0, 1, 1),\n",
    "                                seasonal_order=(0, 1, 1, 12),\n",
    "                                enforce_stationarity=False,\n",
    "                                enforce_invertibility=False)\n",
    "results = mod.fit(disp=False)\n",
    "print(results.summary().tables[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30ba00c6-727f-46f7-a9b8-9b33d6299c0b",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "In the following, you visualize a time series forecast for furniture sales data, showing both the observed data and the one-step-ahead forecast with a confidence interval shaded region. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b02974a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot the forecasting results\n",
    "pred = results.get_prediction(start=maximim_date, end=maximim_date+pd.DateOffset(months=6), dynamic=False) # Forecast for the next 6 months (months=6)\n",
    "pred_ci = pred.conf_int() # Extract the confidence intervals for the predictions\n",
    "ax = y['2019':].plot(label='observed')\n",
    "pred.predicted_mean.plot(ax=ax, label='One-step ahead forecast', alpha=.7, figsize=(12, 7))\n",
    "ax.fill_between(pred_ci.index,\n",
    "                pred_ci.iloc[:, 0],\n",
    "                pred_ci.iloc[:, 1], color='k', alpha=.2)\n",
    "ax.set_xlabel('Date')\n",
    "ax.set_ylabel('Furniture Sales')\n",
    "plt.legend()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c92208f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Validate the forecasted result\n",
    "predictions = results.get_prediction(start=maximim_date-pd.DateOffset(months=6-1), dynamic=False)\n",
    "# Forecast on the unseen future data\n",
    "predictions_future = results.get_prediction(start=maximim_date+ pd.DateOffset(months=1),end=maximim_date+ pd.DateOffset(months=6),dynamic=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8c07431-8d82-4df3-9a11-1af9c4b1b524",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "The `predictions` is utilized to assess the model's performance by contrasting it with the actual values, whereas `predictions_future` is indicative of future forecasting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c251049-fb0c-40b7-a433-3bf19ea058fa",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "# Log the model and parameters\n",
    "model_name = f\"{EXPERIMENT_NAME}-Sarimax\"\n",
    "with mlflow.start_run(run_name=\"Sarimax\") as run:\n",
    "    mlflow.statsmodels.log_model(results,model_name,registered_model_name=model_name)\n",
    "    mlflow.log_params({\"order\":(0,1,1),\"seasonal_order\":(0, 1, 1, 12),'enforce_stationarity':False,'enforce_invertibility':False})\n",
    "    model_uri = f\"runs:/{run.info.run_id}/{model_name}\"\n",
    "    print(\"Model saved in run %s\" % run.info.run_id)\n",
    "    print(f\"Model URI: {model_uri}\")\n",
    "mlflow.end_run()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c038a322-0bd3-4382-b507-8ff90841224e",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "# Load the saved model\n",
    "loaded_model = mlflow.statsmodels.load_model(model_uri)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4400024-f0ec-411c-8ee4-4a8c80f0b50c",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "## Step 4: Score the model and save predictions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fc226ef-d340-4e8d-9f47-e4846082ddfd",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "The actual values are integrated with the forecasted values, which are employed to create the Power BI report. These results are stored into a table within the lakehouse.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bdb08b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data Preparation for Power BI visualization\n",
    "Future = pd.DataFrame(predictions_future.predicted_mean).reset_index()\n",
    "Future.columns = ['Date','Forecasted_Sales']\n",
    "Future['Actual_Sales'] = np.NAN\n",
    "Actual = pd.DataFrame(predictions.predicted_mean).reset_index()\n",
    "Actual.columns = ['Date','Forecasted_Sales']\n",
    "y_truth = y['2023-02-01':]\n",
    "Actual['Actual_Sales'] = y_truth.values\n",
    "final_data = pd.concat([Actual,Future])\n",
    "# Calculate the Mean Absolute Percentage Error (MAPE) between the 'Actual_Sales' and 'Forecasted_Sales' \n",
    "final_data['MAPE'] = mean_absolute_percentage_error(Actual['Actual_Sales'], Actual['Forecasted_Sales']) * 100\n",
    "final_data['Category'] = \"Furniture\"\n",
    "final_data[final_data['Actual_Sales'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "310023ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "input_df = y.reset_index()\n",
    "input_df.rename(columns = {'Order Date':'Date','Sales':'Actual_Sales'}, inplace=True)\n",
    "input_df['Category'] = 'Furniture'\n",
    "input_df['MAPE'] = np.NAN\n",
    "input_df['Forecasted_Sales'] = np.NAN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12df0fdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write Back the results into the lakehouse\n",
    "final_data_2 = pd.concat([input_df,final_data[final_data['Actual_Sales'].isnull()]])\n",
    "table_name = \"Demand_Forecast_New_1\"\n",
    "spark.createDataFrame(final_data_2).write.mode(\"overwrite\").format(\"delta\").save(f\"Tables/{table_name}\")\n",
    "print(f\"Spark dataframe saved to delta table: {table_name}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f66cb1de-0b69-4436-ade0-735b4a618e2e",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "## Step 5: Business Intelligence via Visualizations in Power BI"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37732a18-2169-4ded-aeb9-3a2e3ee4e873",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "\n",
    "The Power BI report shows the mean absolute percentage error (MAPE) of 16.58. MAPE is a metric that defines the accuracy of a forecasting method and represents how accurate the forecasted quantities are in comparison with the actual quantities. MAPE is a straightforward metric. A 10% MAPE representing that the average deviation between the forecasted values and actual values was 10%, regardless of whether the deviation was positive or negative. What is considered to be a desirable MAPE value varies across different industries.\n",
    "\n",
    "The light blue line in the graph represents the actual sales values, while the dark blue line represents the forecasted sales values. An analysis of the comparison between the actual and forecasted sales reveals that the model effectively predicts sales for the Furniture category during the first six months of 2023.\n",
    "\n",
    "Based on this observation, it's justifiable to have confidence in the model's forecasting capabilities for the overall sales in the last six months of 2023 and extending into 2024. This confidence can inform strategic decisions regarding inventory management, raw material procurement, and other business-related considerations."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56acf106-dc8b-4661-96de-bfda9667b8c1",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<img style=\"float: left;\" src=\"https://synapseaisolutionsa.blob.core.windows.net/public/Forecast_Superstore_Sales/PBI_forecast.png\"  width=\"95%\" height=\"10%\">\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d400af3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Determine the entire runtime\n",
    "print(f\"Full run cost {int(time.time() - ts)} seconds.\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python",
   "name": "synapse_pyspark"
  },
  "language_info": {
   "name": "python"
  },
  "notebook_environment": {},
  "nteract": {
   "version": "nteract-front-end@1.0.0"
  },
  "save_output": true,
  "spark_compute": {
   "compute_id": "/trident/default",
   "session_options": {
    "conf": {},
    "enableDebugMode": false
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}