1. Preparing Target Time-Series Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preparing Target Time-Series Data\n",
    "\n",
    "> *This notebook should work well in the `Python 3 (Data Science)` kernel in SageMaker Studio, or `conda_python3` in SageMaker Notebook Instances*\n",
    "\n",
    "A critical requirement to use Amazon Forecast is to have access to **time-series data** for your selected use case, and to clean and **prepare it in the expected format** for the service.\n",
    "\n",
    "To learn more about time series data, consider exploring:\n",
    "\n",
    "1. [Wikipedia](https://en.wikipedia.org/wiki/Time_series)\n",
    "1. [Towards Data Science's Primer](https://towardsdatascience.com/the-complete-guide-to-time-series-analysis-and-forecasting-70d476bfe775)\n",
    "1. [This O'Reilly Book](https://www.amazon.com/gp/product/1492041653/ref=ppx_yo_dt_b_search_asin_title?ie=UTF8&psc=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ℹ️ The below `pip install` upgrades are required at the time of writing for running the notebook in SageMaker Studio, due to requiring specific features from the libraries. These upgrades may not be necessary in SageMaker classic Notebook Instances."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install -U numpy pandas matplotlib"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sourcing Data\n",
    "\n",
    "Regardless of whether your data comes from a DB export, an existing spreadsheet, or some other source - we'll prepare it in **CSV format** to be [imported to Amazon Forecast](https://docs.aws.amazon.com/forecast/latest/dg/howitworks-datasets-groups.html).\n",
    "\n",
    "As an example for this POC guide, we'll use a dataset from the [UCI repository of machine learning datasets](https://archive.ics.uci.edu/ml/) - a great resource for finding datasets for various problems. In our particular case, we'll use traffic data for a given section of interstate highway as provided in the ['Metro Interstate Traffic Volume'](https://archive.ics.uci.edu/ml/datasets/Metro+Interstate+Traffic+Volume) dataset.\n",
    "\n",
    "The cell below will download and extract this sample data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dir = \"data\"\n",
    "%store data_dir\n",
    "\n",
    "!mkdir -p $data_dir\n",
    "!wget -O $data_dir/Metro_Interstate_Traffic_Volume.csv.gz \\\n",
    "    https://archive.ics.uci.edu/ml/machine-learning-databases/00492/Metro_Interstate_Traffic_Volume.csv.gz"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reviewing and Pre-Processing the Data\n",
    "\n",
    "As always in machine learning, accurate results are derived from training data which is:\n",
    "\n",
    "- Accurate/correct\n",
    "- Extensive and complete enough to demonstrate important patterns/correlations\n",
    "- Well-matched to the assumptions and setup of the model\n",
    "\n",
    "It's often said that data preparation is 70-80% of the work of a typical ML project, and hard to over-state the impact this process can have on a project's success!\n",
    "\n",
    "Some important best practices with Amazon Forecast are:\n",
    "\n",
    "1. Choose the [**\"Dataset Domain\"**](https://docs.aws.amazon.com/forecast/latest/dg/howitworks-domains-ds-types.html) which most closely matches your use case, and make use of the documented optional fields where they correspond well to your data.\n",
    "    - E.g. for retail forecasting use cases, consider using the `RETAIL` or `INVENTORY_PLANNING` domains... And prefer the [defined](https://docs.aws.amazon.com/forecast/latest/dg/retail-domain.html) fields like `color` and `category` in those demains, over using similar custom fields like `colour` or `cat`.\n",
    "    - ...But you can still use the [CUSTOM domain](https://docs.aws.amazon.com/forecast/latest/dg/custom-domain.html) domain if none of the others provide useful overlap to your use case.\n",
    "2. Understand that, behind the scenes, Forecast is modelling your data as **regularly-spaced timeseries**.\n",
    "    - Although the service has built-in, documented functionality for [handling mismatched data frequencies](https://docs.aws.amazon.com/forecast/latest/dg/howitworks-datasets-groups.html#howitworks-data-alignment) and [handling missing values](https://docs.aws.amazon.com/forecast/latest/dg/howitworks-missing-values.html), you need to understand these to check they're performing correctly for your data - and re-configure if necessary.\n",
    "    - Remember this means \"missing values\" don't just refer to empty cells in your CSV, but also **missing rows**: e.g. if your sales data simply doesn't list a row for a particular day-item-location combination if sales were zero for that combination.\n",
    "\n",
    "\n",
    "**In this notebook**, we'll look only at the [Target Time-Series or TTS](https://docs.aws.amazon.com/forecast/latest/dg/howitworks-datasets-groups.html) (the quantity you want to forecast). Related Time-Series (relevant input variables e.g. weather, stock, etc) will be considered in a later notebook.\n",
    "\n",
    "With the raw data now downloaded, we'll start by importing some useful libraries and loading up the file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "\n",
    "# Python Built-Ins:\n",
    "import json\n",
    "from time import sleep\n",
    "\n",
    "# External Dependencies:\n",
    "import boto3\n",
    "import pandas as pd\n",
    "\n",
    "# Local Dependencies:\n",
    "import util"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "original_data = pd.read_csv(f\"{data_dir}/Metro_Interstate_Traffic_Volume.csv.gz\")\n",
    "original_data[\"date_time\"] = pd.to_datetime(original_data[\"date_time\"])\n",
    "original_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point we can see a few things about the data:\n",
    "\n",
    "* Holidays seem to be specified\n",
    "* There is a value for temp, rainfall, snowfall, and a few other weather metrics.\n",
    "* The time series is hourly\n",
    "* Our value to predict is `traffic_volume` down at the end.\n",
    "\n",
    "Amazon Forecast relies on a concept called the target-time-series in order to start making predictions, this has a timestamp, an item identifier, and a value. The timestamp is pretty self explanatory, and the value to predict will be traffic_volume, given this is a singular time series an arbitrary item_id of `all` will be applied later to all entries in the time series file.\n",
    "\n",
    "The other attributes provided can serve as a basis for related time series components when we get to that much later.\n",
    "\n",
    "Amazon Forecast also works well to fill in gaps for the target-time-series but not the related data, so before we input our data and get a prediction we should look to see where gaps are, and how we want to structure both inputs to address this issue. \n",
    "\n",
    "To get started we will manipulate our starting dataframe to determine the quality and consistency of this dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "target_df = original_data.copy()\n",
    "target_df.plot()\n",
    "print(\"Start Date: \", min(target_df[\"date_time\"]))\n",
    "print(\"End Date: \", max(target_df[\"date_time\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Interestingly at this point we do not see any obvious gaps in this plot, but we should still check a bit deeper to confirm this. The next cell gives some basic information on the dataset size."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "target_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the cell above we now see a range of October 2012 to nearly October 2018, almost 6 years of hourly data. Given there are around 8700 hours in a year we expect to see 52,000 time series. Immediately here we see 48,204. It looks like some data points are missing, next let us define the index, drop the duplicates and see where we are then."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "target_df.set_index(\"date_time\", inplace=True)\n",
    "target_df = target_df.drop_duplicates(keep=\"first\")\n",
    "target_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That change dropped us to 48,175 unique entries. Given this is traffic data we could be dealing with a missing sensor, construction causing outages, or even severe weather delay damaging the recording equipment. Before we decide on how to fill any gaps, let us first take a look to see where they are, and how large the gaps themselves may be.\n",
    "\n",
    "We will do this by creating a new dataframe for the entire length of the dataset, that has no missing entries, then joining our data to it, and padding out 0's where anything is missing.\n",
    "\n",
    "*Note* the periods value below is the total number of entries to make, I cheated and used WolframAlpha to sort out the number of days: https://www.wolframalpha.com/input/?i=days+from+2012-10-02+to+2018-09-30"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_days = 2190\n",
    "# Build the index first\n",
    "idx = pd.date_range(start=\"10/02/2012\", end=\"09/30/2018\", freq=\"H\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_df = pd.DataFrame(index=idx)\n",
    "%store full_df\n",
    "full_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(full_df.index.min())\n",
    "print(full_df.index.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now perform the join\n",
    "full_historical_df = full_df.join(target_df, how=\"outer\")\n",
    "%store full_historical_df\n",
    "print(full_historical_df.index.min())\n",
    "print(full_historical_df.index.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Take a look at 10 random entries\n",
    "full_historical_df.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The sample may or may not have shown values with NaNs or other nulls, in this instance it did but we will still want to look for these NaN entities to confirm if they exist and where they are.\n",
    "\n",
    "At this point we have done enough work to see where we may have any large portions of missing data. To that end we can plot the data below and see any gaps that may crop up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_historical_df.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This shows a large gap of missing data from late 2014 until mid 2016. If we just wanted to feed in the previously known value this may give us too long of a timeframe of data that is simply not representative of the problem. \n",
    "\n",
    "Before making any decisions we will now step through each year and see what the gaps look like starting in 2013 as it is the first full year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2013 = full_historical_df.loc[\"2013-01-01\":\"2013-12-31\"]\n",
    "print(df_2013.index.min())\n",
    "print(df_2013.index.max())\n",
    "df_2013.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2014 = full_historical_df.loc[\"2014-01-01\":\"2014-12-31\"]\n",
    "print(df_2014.index.min())\n",
    "print(df_2014.index.max())\n",
    "df_2014.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2015 = full_historical_df.loc[\"2015-01-01\":\"2015-12-31\"]\n",
    "print(df_2015.index.min())\n",
    "print(df_2015.index.max())\n",
    "df_2015.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2016 = full_historical_df.loc[\"2016-01-01\":\"2016-12-31\"]\n",
    "print(df_2016.index.min())\n",
    "print(df_2016.index.max())\n",
    "df_2016.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2017 = full_historical_df.loc[\"2017-01-01\":\"2017-12-31\"]\n",
    "print(df_2017.index.min())\n",
    "print(df_2017.index.max())\n",
    "df_2017.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2018 = full_historical_df.loc[\"2018-01-01\":\"2018-12-31\"]\n",
    "print(df_2018.index.min())\n",
    "print(df_2018.index.max())\n",
    "df_2018.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A few things to note here, clearly we are missing a large volume of data in 2014 and 2015 but also there are some missing patches in 2013 as well. 2016 had spotty data initially but 2017 and 2018 look pretty good.\n",
    "\n",
    "Given that the data is hourly we still have plenty of it within a single year, and an additional 10 months to use for broader validation if we choose to do that.\n",
    "\n",
    "To note, more advanced approaches like neural network models and Prophet often work very well with > 1k measurements on a given time series. Assuming hourly data (24 measurements per day), that yields around 42 days before we have a solid base of data. Learning over an entire year should be plenty.\n",
    "\n",
    "Also we need to think about a Forecast horizon or how far into the future we are going to predict at once. Forecast currently limits us to 500 intervals of whatever granularity we have selected. For this exercise we will keep the data hourly and predict 480 hours into the future, or exactly 20 days."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Building Data Files\n",
    "\n",
    "Knowing that our above dataframe `full_historical_df` covers the entire time period we care about we start there reducing it to 2017 to end. Then we will use fill forward to plug in any missing holes before splitting into the 3 files described before. \n",
    "\n",
    "More info on techniques to patch missing information can be found here: https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.fillna.html \n",
    "\n",
    "The risk of filling in values like this is that in smoothing out the data it may cause our predictions to resemble a smoother curve than our historical data. This is why we selected 2017 to 2018 based on the lack of large gaps in the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a copy\n",
    "target_df = full_historical_df.copy()\n",
    "# Slice to only 2017 onward\n",
    "target_df = target_df.loc[\"2017-01-01\":]\n",
    "# Validate the dates\n",
    "print(target_df.index.min())\n",
    "print(target_df.index.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill in any missing data with the method ffill\n",
    "target_df.ffill()\n",
    "%store target_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point we have all the data needed to make our target time series file and dataset. While we are doing this we will also make a validation file for later use as well."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Building The Target Time Series File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "target_time_series_df = target_df.copy()\n",
    "target_time_series_df = target_time_series_df.loc[\"2017-01-01\":\"2017-12-31\"]\n",
    "# Validate the date range\n",
    "print(target_time_series_df.index.min())\n",
    "print(target_time_series_df.index.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Restrict the columns to timestamp, traffic_volume\n",
    "target_time_series_df = target_time_series_df[[\"traffic_volume\"]]\n",
    "# Add in item_id\n",
    "target_time_series_df[\"item_id\"] = \"all\"\n",
    "# Validate the structure\n",
    "target_time_series_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition to checking the overall covered date range, we'll verify at this point that no missing values have sneaked through into our training data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Take a look at high level metrics:\n",
    "target_time_series_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are exactly 10,642 entries in this file with no null values at all: Should be OK to use!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# With the data in a great state, save it off as a CSV\n",
    "target_time_series_filename = \"target_time_series.csv\"\n",
    "%store target_time_series_filename\n",
    "target_time_series_path = f\"{data_dir}/{target_time_series_filename}\"\n",
    "target_time_series_df.to_csv(target_time_series_path, header=False)\n",
    "%store target_time_series_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Building The Validation File\n",
    "\n",
    "This is the last file we need to build before getting started with Forecast itself. This will be the same in structure as our target-time-series file but will only project into 2018 and includes no historical data from the training data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "validation_time_series_df = target_df.copy()\n",
    "validation_time_series_df = validation_time_series_df.loc[\"2018-01-01\":]\n",
    "# Validate the date range\n",
    "print(validation_time_series_df.index.min())\n",
    "print(validation_time_series_df.index.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Restrict the columns to timestamp, traffic_volume\n",
    "validation_time_series_df = validation_time_series_df[[\"traffic_volume\"]]\n",
    "# Add in item_id\n",
    "validation_time_series_df[\"item_id\"] = \"all\"\n",
    "\n",
    "%store validation_time_series_df\n",
    "# Validate the structure\n",
    "validation_time_series_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# With the data in a great state, save it off as a CSV\n",
    "validation_time_series_filename = \"validation_time_series.csv\"\n",
    "validation_time_series_path = f\"{data_dir}/{validation_time_series_filename}\"\n",
    "validation_time_series_df.to_csv(validation_time_series_path, header=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uploading Data to Amazon S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace the below with e.g. region = \"ap-southeast-1\" if you didn't run notebook 0\n",
    "%store -r region\n",
    "assert isinstance(region, str), \"`region` must be a region name string e.g. 'us-east-1'\"\n",
    "\n",
    "# Replace the below with e.g. bucket_name = \"DOC-EXAMPLE-BUCKET\" if you didn't run notebook 0\n",
    "%store -r bucket_name\n",
    "assert isinstance(bucket_name, str), \"`bucket_name` must be a data bucket name string\"\n",
    "\n",
    "session = boto3.Session(region_name=region)\n",
    "s3 = session.resource(\"s3\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Upload Target File\n",
    "s3.Bucket(bucket_name).Object(target_time_series_filename).upload_file(target_time_series_path)\n",
    "target_s3uri = f\"s3://{bucket_name}/{target_time_series_filename}\"\n",
    "%store target_s3uri\n",
    "print(f\"Uploaded TTS to {target_s3uri}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## All Done!\n",
    "\n",
    "Now our Target Time-Series is prepared a compatible format for Amazon Forecast and staged in an Amazon S3 bucket ready to import.\n",
    "\n",
    "In the next notebook, we'll show how to import the data and start building and evaluating forecasts!\n",
    "\n",
    "You can follow along with either [notebook 2a (AWS Console)](2a.%20Getting%20Started%20with%20Forecast%20(Console).ipynb) to use Amazon Forecast via the **console UI**, or [notebook 2b (Python SDK)](2b.%20Getting%20Started%20with%20Forecast%20(Python%20SDK).ipynb) to see how the same steps can be performed in notebook code via the **AWS SDK**."
   ]
  }
 ],
 "metadata": {
  "instance_type": "ml.t3.medium",
  "kernelspec": {
   "display_name": "Python 3 (Data Science)",
   "language": "python",
   "name": "python3__SAGEMAKER_INTERNAL__arn:aws:sagemaker:ap-southeast-1:492261229750:image/datascience-1.0"
  },
  "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.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}