02-vf-sdk-examples/04-vertex-forecast-experiments.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "693c3467-31ce-47a5-9076-b779ab87f3ab",
   "metadata": {},
   "source": [
    "# Vertex Forecast - experiments with scenarios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "74ded5f7-d5ca-4e3f-afbe-cdaec13e359a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PROJECT_ID: hybrid-vertex\n",
      "PROJECT_NUM: 934903580331\n",
      "LOCATION: us-central1\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "GCP_PROJECTS = !gcloud config get-value project\n",
    "PROJECT_ID = GCP_PROJECTS[0]\n",
    "PROJECT_NUM = !gcloud projects list --filter=\"$PROJECT_ID\" --format=\"value(PROJECT_NUMBER)\"\n",
    "PROJECT_NUM = PROJECT_NUM[0]\n",
    "LOCATION = 'us-central1'\n",
    "BQ_LOCATION='US'\n",
    "\n",
    "# TODO: Service Account address\n",
    "VERTEX_SA = '934903580331-compute@developer.gserviceaccount.com' \n",
    "\n",
    "print(f\"PROJECT_ID: {PROJECT_ID}\")\n",
    "print(f\"PROJECT_NUM: {PROJECT_NUM}\")\n",
    "print(f\"LOCATION: {LOCATION}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4c5e8dff-f407-405a-b177-60bb2df0163c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import google.cloud.aiplatform as vertex_ai\n",
    "from google.cloud import bigquery\n",
    "from google.cloud import storage\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4943eb9b-178a-4a11-a7fc-c632cd538a74",
   "metadata": {},
   "outputs": [],
   "source": [
    "bq_client = bigquery.Client(\n",
    "    project=PROJECT_ID, \n",
    "    location=BQ_LOCATION\n",
    ")\n",
    "\n",
    "storage_client = storage.Client(project=PROJECT_ID)\n",
    "\n",
    "vertex_ai.init(\n",
    "    project=PROJECT_ID,\n",
    "    location=LOCATION\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "f7d6d2d0-a8d6-4517-9a88-efabeea0df80",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "EXPERIMENT_NAME: m5_nb4_v1\n"
     ]
    }
   ],
   "source": [
    "# previously defined\n",
    "BQ_DATASET=\"m5_14day_v2_us\" #\"m5_us\"\n",
    "BQ_TABLE=\"sdk_train\"\n",
    "BQ_TABLE_PLAN=\"sdk_plan\"\n",
    "\n",
    "# new vars\n",
    "EXPERIMENT_tag=\"nb4\"\n",
    "VERSION='v1'\n",
    "\n",
    "EXPERIMENT_NAME = f\"m5_{EXPERIMENT_tag}_{VERSION}\"\n",
    "print(f'EXPERIMENT_NAME: {EXPERIMENT_NAME}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eacea2a2-bad4-4a75-a49c-8553a215c759",
   "metadata": {},
   "source": [
    "## get dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "0c20c08c-141e-4e65-8434-be0d72bcfa2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = vertex_ai.TimeSeriesDataset('projects/934903580331/locations/us-central1/datasets/462153324456574976')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70057fd2-a442-42e4-840a-638705974696",
   "metadata": {},
   "source": [
    "### define column specs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "fe45f0f0-95c4-4d12-a133-ac4997c25aac",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle as pkl\n",
    "from pprint import pprint\n",
    "\n",
    "LOCAL_COL_FILE = 'column_specs.pkl'\n",
    "\n",
    "filehandler = open(LOCAL_COL_FILE, 'rb')\n",
    "COL_DICT_TEST = pkl.load(filehandler)\n",
    "filehandler.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "36841927-a8d2-4add-b13f-d02cc085e73e",
   "metadata": {},
   "outputs": [],
   "source": [
    "COL_TRANSFORMS = COL_DICT_TEST['column_specs']\n",
    "UNAVAILABLE_AT_FORECAST_COLS = COL_DICT_TEST['unavailable_at_forecast_columns']\n",
    "AVAILABLE_AT_FORECAST_COLS = COL_DICT_TEST['available_at_forecast_columns']\n",
    "SERIES_COLUMN = COL_DICT_TEST['time_series_identifier_column']\n",
    "TIME_COLUMN = COL_DICT_TEST['time_column']\n",
    "TARGET_COLUMN = COL_DICT_TEST['target_column']\n",
    "PREDEFINED_SPLIT_COL = COL_DICT_TEST['predefined_split_column_name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "fa743a7d-debd-4a4c-b9ef-b6fc7e2b7de0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# forecast spec\n",
    "FORECAST_GRANULARITY = 'DAY'\n",
    "DATA_GRANULARITY_COUNT=1\n",
    "FORECAST_HORIZON = 14\n",
    "# CONTEXT_WINDOW = 14\n",
    "forecast_test_length = 14\n",
    "forecast_val_length = 14\n",
    "\n",
    "# model config\n",
    "OPTIMIZATION_OBJECTIVE=\"minimize-rmse\"\n",
    "\n",
    "# job spec\n",
    "MILLI_NODE_HRS=1000\n",
    "HOLIDAY_REGIONS=['GLOBAL', 'NA', 'US']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af695b1b-f18e-4707-9d9f-1d9e6c361c59",
   "metadata": {},
   "source": [
    "## Train Forecasting Model with AutoML - Multiple Scenarios\n",
    "\n",
    "The goal here is to train a range of AutoML Forecasting Models on the same data using combination of forecast horizon and context window. This is follow the guidance of [Considerations for setting the context window and forecast horizon](https://cloud.google.com/vertex-ai/docs/datasets/bp-tabular#context-window)\n",
    "\n",
    "*Note, there may be a default concurrent training job limit for Vertex AI AutoML of 5 jobs* ([reference here](https://cloud.google.com/vertex-ai/docs/quotas#tabular)). \n",
    "\n",
    "Here we are running 5 jobs but it we needed to run more we would have to wait for a job to complete or submit a quota increase ([reference here](https://cloud.google.com/vertex-ai/docs/quotas#quota_increases))."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "6b8a3c8b-03fb-4380-9564-37f60354ab77",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['event_type_2',\n",
       " 'cat_id',\n",
       " 'snap_TX',\n",
       " 'month',\n",
       " 'event_name_1',\n",
       " 'snap_WI',\n",
       " 'product_id',\n",
       " 'location_id',\n",
       " 'event_type_1',\n",
       " 'event_name_2',\n",
       " 'state_id',\n",
       " 'year',\n",
       " 'weekday',\n",
       " 'dept_id',\n",
       " 'snap_CA',\n",
       " 'sell_price',\n",
       " 'date',\n",
       " 'wday']"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UNAVAILABLE_AT_FORECAST_COLS=[\"gross_quantity\"]\n",
    "AVAILABLE_AT_FORECAST_COLS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "701fab8f-126b-48a0-bd7f-358ebfd8146d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def scenario(ds, cw):\n",
    "    \n",
    "    forecast_job = vertex_ai.AutoMLForecastingTrainingJob(\n",
    "        display_name = f'{EXPERIMENT_NAME}_CW{cw}',\n",
    "        optimization_objective=OPTIMIZATION_OBJECTIVE,\n",
    "        column_specs = COL_TRANSFORMS,\n",
    "        labels = {'experiment':f'{EXPERIMENT_NAME}', 'cw':f'{cw}'}\n",
    "    )\n",
    "    \n",
    "    forecast=forecast_job.run(\n",
    "        dataset=dataset,\n",
    "        target_column=TARGET_COLUMN,\n",
    "        time_column=TIME_COLUMN,\n",
    "        time_series_identifier_column = SERIES_COLUMN,\n",
    "        unavailable_at_forecast_columns = UNAVAILABLE_AT_FORECAST_COLS,\n",
    "        available_at_forecast_columns = AVAILABLE_AT_FORECAST_COLS,\n",
    "        forecast_horizon = FORECAST_HORIZON,\n",
    "        data_granularity_unit = FORECAST_GRANULARITY.lower(),\n",
    "        data_granularity_count = DATA_GRANULARITY_COUNT,\n",
    "        predefined_split_column_name = PREDEFINED_SPLIT_COL,\n",
    "        context_window = cw,\n",
    "        export_evaluated_data_items = True,\n",
    "        export_evaluated_data_items_bigquery_destination_uri = f\"bq://{PROJECT_ID}:{BQ_DATASET}:{BQ_TABLE}_automl_cw{cw}\",\n",
    "        validation_options = \"fail-pipeline\",\n",
    "        budget_milli_node_hours = MILLI_NODE_HRS,\n",
    "        model_display_name = f\"{EXPERIMENT_NAME}_{BQ_TABLE}_CW{cw}\",\n",
    "        model_labels = {'experiment':f'{EXPERIMENT_NAME}', 'cw':f'{cw}'},\n",
    "        holiday_regions = ['GLOBAL', 'NA', 'US'],\n",
    "        sync = False\n",
    "    )\n",
    "    \n",
    "    # FORECAST_MODEL_RSC_NAME = forecast.resource_name\n",
    "    # print(f\"FORECAST_MODEL_RSC_NAME: {FORECAST_MODEL_RSC_NAME}\")\n",
    "    \n",
    "    # TODO - update - return tuple with CW?\n",
    "    return forecast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "9d2bff57-4f73-44c3-8a7a-b909dc1fedaf",
   "metadata": {},
   "outputs": [],
   "source": [
    "windows = [29, 15, 8]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "e69273a0-aed1-4c9d-8714-37f6b6e9b2fe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "View Training:\n",
      "https://console.cloud.google.com/ai/platform/locations/us-central1/training/6696343434577313792?project=934903580331\n",
      "View Training:\n",
      "https://console.cloud.google.com/ai/platform/locations/us-central1/training/5039863196635103232?project=934903580331\n",
      "View Training:\n",
      "https://console.cloud.google.com/ai/platform/locations/us-central1/training/5805475133288087552?project=934903580331\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n"
     ]
    }
   ],
   "source": [
    "scenarios = []\n",
    "for w in windows:\n",
    "    scenarios.append(scenario(dataset, w))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "4f7013cd-e7b8-4416-a8f3-2e4f78a654ac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<google.cloud.aiplatform.models.Model object at 0x7f065d6009d0> is waiting for upstream dependencies to complete.,\n",
       " <google.cloud.aiplatform.models.Model object at 0x7f065d600690> is waiting for upstream dependencies to complete.,\n",
       " <google.cloud.aiplatform.models.Model object at 0x7f065d60a5d0> is waiting for upstream dependencies to complete.]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scenarios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "88201459-69ad-4da9-a455-d893e2823c6b",
   "metadata": {},
   "outputs": [
    {
     "ename": "RuntimeError",
     "evalue": "Model resource has not been created.",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mRuntimeError\u001b[0m                              Traceback (most recent call last)",
      "\u001b[0;32m/var/tmp/ipykernel_17078/3271283542.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mmodels_obj\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m \u001b[0mMODEL_28_RESOURCE_NAME\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mscenarios\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mresource_name\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      6\u001b[0m \u001b[0mmodel_28\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mvertex_ai\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mModel\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mMODEL_28_RESOURCE_NAME\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m \u001b[0mmodels_obj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmodel_28\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m29\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/lib/python3.7/site-packages/google/cloud/aiplatform/models.py\u001b[0m in \u001b[0;36mresource_name\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   2605\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mresource_name\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2606\u001b[0m         \u001b[0;34m\"\"\"Full qualified resource name, without any version ID.\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2607\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_assert_gca_resource_is_available\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2608\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mModelRegistry\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_parse_versioned_name\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_gca_resource\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2609\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/lib/python3.7/site-packages/google/cloud/aiplatform/base.py\u001b[0m in \u001b[0;36m_assert_gca_resource_is_available\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1322\u001b[0m                     \u001b[0;34mf\" Resource failed with: {self._exception}\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1323\u001b[0m                     \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1324\u001b[0;31m                     \u001b[0;32melse\u001b[0m \u001b[0;34m\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1325\u001b[0m                 )\n\u001b[1;32m   1326\u001b[0m             )\n",
      "\u001b[0;31mRuntimeError\u001b[0m: Model resource has not been created."
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/1702695872753565696 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5201148358290309120 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/4187838442131947520 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/6696343434577313792 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5039863196635103232 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n",
      "AutoMLForecastingTrainingJob projects/934903580331/locations/us-central1/trainingPipelines/5805475133288087552 current state:\n",
      "PipelineState.PIPELINE_STATE_RUNNING\n"
     ]
    }
   ],
   "source": [
    "# TODO - parameterize\n",
    "\n",
    "models_obj = []\n",
    "\n",
    "MODEL_28_RESOURCE_NAME = scenarios[0].resource_name\n",
    "model_28 = vertex_ai.Model(MODEL_28_RESOURCE_NAME)\n",
    "models_obj.append((model_28, 29))\n",
    "\n",
    "MODEL_14_RESOURCE_NAME = scenarios[1].resource_name\n",
    "model_14 = vertex_ai.Model(MODEL_14_RESOURCE_NAME)\n",
    "models_obj.append((model_14, 15))\n",
    "\n",
    "MODEL_7_RESOURCE_NAME = scenarios[2].resource_name\n",
    "model_7 = vertex_ai.Model(MODEL_7_RESOURCE_NAME)\n",
    "models_obj.append((model_7, 8))\n",
    "\n",
    "models_obj"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "932de48f-8356-448c-b051-7e0647a27036",
   "metadata": {},
   "source": [
    "## retreive default model evaluation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "e9b72dc2-d17a-468c-aa7a-d85646e9313d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Associating projects/934903580331/locations/us-central1/metadataStores/default/contexts/m5-nb3-run-20230329132547 to Experiment: m5-nb3\n",
      "run-20230329132547\n",
      "{'budget_hrs': 1000, 'horizon': 14, 'context_window': 28, 'opt_objective': 'minimize-rmse'}\n",
      "{'MAE': 1.0246576, 'RMSE': 2.048384, 'MAPE': 287217660.0, 'rSquared': 0.6981901, 'RMSLE': 0.5037388, 'WAPE': 68.539856}\n",
      "Associating projects/934903580331/locations/us-central1/metadataStores/default/contexts/m5-nb3-run-20230329132550 to Experiment: m5-nb3\n",
      "run-20230329132550\n",
      "{'budget_hrs': 1000, 'horizon': 14, 'context_window': 14, 'opt_objective': 'minimize-rmse'}\n",
      "{'MAE': 1.0625765, 'RMSE': 2.0592186, 'MAPE': 350589820.0, 'rSquared': 0.69406325, 'RMSLE': 0.519183, 'WAPE': 71.07627}\n",
      "Associating projects/934903580331/locations/us-central1/metadataStores/default/contexts/m5-nb3-run-20230329132554 to Experiment: m5-nb3\n",
      "run-20230329132554\n",
      "{'budget_hrs': 1000, 'horizon': 14, 'context_window': 7, 'opt_objective': 'minimize-rmse'}\n",
      "{'MAE': 1.0658535, 'RMSE': 2.0704277, 'MAPE': 340165200.0, 'rSquared': 0.6896643, 'RMSLE': 0.51960534, 'WAPE': 71.29547}\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "\n",
    "# # Create and log experiment\n",
    "vertex_ai.init(experiment=EXPERIMENT_NAME.replace(\"_\",\"-\"))\n",
    "\n",
    "for model, cw in models_obj:\n",
    "    # create run name\n",
    "    TIMESTAMP = datetime.now().strftime(\"%Y%m%d%H%M%S\")\n",
    "    EXPERIMENT_RUN_NAME = f\"run-{TIMESTAMP}\"\n",
    "    \n",
    "    model_evaluation = list(model.list_model_evaluations())[0]\n",
    "    metrics_dict = {k: [v] for k, v in dict(model_evaluation.metrics).items()}\n",
    "    \n",
    "    # log params\n",
    "    params = {}\n",
    "    params[\"budget_hrs\"] = MILLI_NODE_HRS\n",
    "    params[\"horizon\"] = FORECAST_HORIZON\n",
    "    params[\"context_window\"] = cw\n",
    "    params[\"opt_objective\"] = OPTIMIZATION_OBJECTIVE\n",
    "    \n",
    "    # log metrics\n",
    "    metrics = {}\n",
    "    metrics[\"MAE\"] = metrics_dict['meanAbsoluteError'][0]\n",
    "    metrics[\"RMSE\"] = metrics_dict['rootMeanSquaredError'][0]\n",
    "    metrics[\"MAPE\"] = metrics_dict['meanAbsolutePercentageError'][0]\n",
    "    metrics[\"rSquared\"] = metrics_dict['rSquared'][0]\n",
    "    metrics[\"RMSLE\"] = metrics_dict['rootMeanSquaredLogError'][0]\n",
    "    metrics[\"WAPE\"] = metrics_dict['weightedAbsolutePercentageError'][0]\n",
    "    \n",
    "    # log to experiment artifact\n",
    "    with vertex_ai.start_run(EXPERIMENT_RUN_NAME) as my_run:\n",
    "        my_run.log_metrics(metrics)\n",
    "        my_run.log_params(params)\n",
    "\n",
    "        vertex_ai.end_run()\n",
    "    \n",
    "    print(EXPERIMENT_RUN_NAME)\n",
    "    print(params)\n",
    "    print(metrics)\n",
    "    time.sleep(3)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efa858ff-9da6-4ace-914e-5be4052ca52b",
   "metadata": {},
   "source": [
    "### Evalaute model feature attributions \n",
    "\n",
    "**TODO**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "69274e8a-a954-4e4a-83e9-3f446f18a43b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'name': 'projects/934903580331/locations/us-central1/models/7186603712213680128@1/evaluations/8302332043744039113', 'metricsSchemaUri': 'gs://google-cloud-aiplatform/schema/modelevaluation/forecasting_metrics_1.0.0.yaml', 'metrics': {'meanAbsoluteError': 1.0246576, 'rootMeanSquaredError': 2.048384, 'rSquared': 0.6981901, 'weightedAbsolutePercentageError': 68.539856, 'rootMeanSquaredLogError': 0.5037388, 'rootMeanSquaredPercentageError': 702380800.0, 'meanAbsolutePercentageError': 287217660.0}, 'createTime': '2023-03-29T09:20:20.696303Z', 'modelExplanation': {'meanAttributions': [{'featureAttributions': {'snap_CA': 0.008122412502676448, 'month': 0.005015062030750952, 'dept_id': 0.046573801404751716, 'snap_WI': 0.011550079316175514, 'state_id': 0.011089179830918226, 'location_id': 0.013717990393000848, 'event_type_2': 7.301415637831124e-10, 'product_id': 0.028992821866121433, 'event_type_1': 0.0004927770722828623, 'wday': 0.05963375185924724, 'event_name_1': 0.0025649872933561596, 'year': 0.0010334949298127576, 'date': 0.03504121382235925, 'weekday': 0.035677123404094804, 'snap_TX': 0.006412891035890649, 'cat_id': 0.0020377784244251067, 'event_name_2': 6.7946729964275606e-09, 'gross_quantity': 1.3361141401617391, 'sell_price': 0.03434899216365351}}]}}\n"
     ]
    }
   ],
   "source": [
    "MODEL_28_EVALS = model_28.list_model_evaluations()\n",
    "\n",
    "for model_evaluation in MODEL_28_EVALS:\n",
    "    print(model_evaluation.to_dict())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "228d5241-3f26-4e50-8609-471cf542404a",
   "metadata": {},
   "source": [
    "## Review scenarios"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1143ef89-aa7b-4de5-94ec-9365d9c6d32a",
   "metadata": {},
   "source": [
    "### Review Custom Metrics with SQL\n",
    "\n",
    "Some common metrics for evaluating forecasting effectiveness are \n",
    "- MAPE, or Mean Absolute Percentage Error\n",
    "    - $\\textrm{MAPE} = \\frac{1}{n}\\sum{\\frac{\\mid(actual - forecast)\\mid}{actual}}$\n",
    "- MAE, or Mean Absolute Error\n",
    "     - $\\textrm{MAE} = \\frac{1}{n}\\sum{\\mid(actual - forecast)\\mid}$\n",
    "- MAE divided by average demand so it yields a % like MAPE\n",
    "    - $\\textrm{pMAE} = \\frac{\\sum{\\mid(actual - forecast)\\mid}}{\\sum{actual}}$\n",
    "- MSE, or Mean Squared Error\n",
    "    - $\\textrm{MSE} = \\frac{1}{n}\\sum{(actual-forecast)^2}$\n",
    "- RMSE, or Root Mean Squared Error\n",
    "    - $\\textrm{RMSE} = \\sqrt{\\frac{1}{n}\\sum{(actual-forecast)^2}}$\n",
    "- RMSE divided by average demand so it yeilds a % like MAPE\n",
    "    - $\\textrm{pRMSE} = \\frac{\\sqrt{\\frac{1}{n}\\sum{(actual-forecast)^2}}}{\\frac{1}{n}\\sum{actual}}$\n",
    "\n",
    "It can be helpful to explicity caculate these to make comparison between datasets and models fair.  This section demonstration these calculation with SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8a79753-275b-48c0-bb33-467da4724cb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# hybrid-vertex.m5_us.sdk_train_automl_cw14"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "c00e2a0b-c020-46ef-aee9-70ccfc07da1c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>timeseries_id</th>\n",
       "      <th>CW</th>\n",
       "      <th>time_series_type</th>\n",
       "      <th>MAPE</th>\n",
       "      <th>MAE</th>\n",
       "      <th>pMAE</th>\n",
       "      <th>MSE</th>\n",
       "      <th>RMSE</th>\n",
       "      <th>pRMSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FOODS_1_001_CA_1</td>\n",
       "      <td>7</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.472810</td>\n",
       "      <td>0.905466</td>\n",
       "      <td>1.408503</td>\n",
       "      <td>1.014595</td>\n",
       "      <td>1.007271</td>\n",
       "      <td>1.566866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FOODS_1_001_CA_1</td>\n",
       "      <td>14</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.504722</td>\n",
       "      <td>0.965395</td>\n",
       "      <td>1.501726</td>\n",
       "      <td>1.021288</td>\n",
       "      <td>1.010588</td>\n",
       "      <td>1.572026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FOODS_1_001_CA_1</td>\n",
       "      <td>28</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.516518</td>\n",
       "      <td>0.892563</td>\n",
       "      <td>1.388432</td>\n",
       "      <td>0.928325</td>\n",
       "      <td>0.963496</td>\n",
       "      <td>1.498772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FOODS_1_001_CA_2</td>\n",
       "      <td>7</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.490788</td>\n",
       "      <td>0.718280</td>\n",
       "      <td>1.005593</td>\n",
       "      <td>0.656381</td>\n",
       "      <td>0.810173</td>\n",
       "      <td>1.134243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FOODS_1_001_CA_2</td>\n",
       "      <td>14</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.452630</td>\n",
       "      <td>0.689935</td>\n",
       "      <td>0.965910</td>\n",
       "      <td>0.590175</td>\n",
       "      <td>0.768228</td>\n",
       "      <td>1.075520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91465</th>\n",
       "      <td>HOUSEHOLD_2_516_WI_2</td>\n",
       "      <td>14</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.790130</td>\n",
       "      <td>0.290190</td>\n",
       "      <td>2.031330</td>\n",
       "      <td>0.126322</td>\n",
       "      <td>0.355418</td>\n",
       "      <td>2.487925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91466</th>\n",
       "      <td>HOUSEHOLD_2_516_WI_2</td>\n",
       "      <td>28</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.852261</td>\n",
       "      <td>0.250014</td>\n",
       "      <td>1.750101</td>\n",
       "      <td>0.123070</td>\n",
       "      <td>0.350814</td>\n",
       "      <td>2.455696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91467</th>\n",
       "      <td>HOUSEHOLD_2_516_WI_3</td>\n",
       "      <td>7</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.819534</td>\n",
       "      <td>0.236555</td>\n",
       "      <td>3.311774</td>\n",
       "      <td>0.082443</td>\n",
       "      <td>0.287129</td>\n",
       "      <td>4.019802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91468</th>\n",
       "      <td>HOUSEHOLD_2_516_WI_3</td>\n",
       "      <td>14</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.811475</td>\n",
       "      <td>0.251556</td>\n",
       "      <td>3.521783</td>\n",
       "      <td>0.087483</td>\n",
       "      <td>0.295775</td>\n",
       "      <td>4.140856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91469</th>\n",
       "      <td>HOUSEHOLD_2_516_WI_3</td>\n",
       "      <td>28</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.851410</td>\n",
       "      <td>0.194284</td>\n",
       "      <td>2.719975</td>\n",
       "      <td>0.071092</td>\n",
       "      <td>0.266630</td>\n",
       "      <td>3.732821</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>91470 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              timeseries_id  CW time_series_type      MAPE       MAE  \\\n",
       "0          FOODS_1_001_CA_1   7         forecast  0.472810  0.905466   \n",
       "1          FOODS_1_001_CA_1  14         forecast  0.504722  0.965395   \n",
       "2          FOODS_1_001_CA_1  28         forecast  0.516518  0.892563   \n",
       "3          FOODS_1_001_CA_2   7         forecast  0.490788  0.718280   \n",
       "4          FOODS_1_001_CA_2  14         forecast  0.452630  0.689935   \n",
       "...                     ...  ..              ...       ...       ...   \n",
       "91465  HOUSEHOLD_2_516_WI_2  14         forecast  0.790130  0.290190   \n",
       "91466  HOUSEHOLD_2_516_WI_2  28         forecast  0.852261  0.250014   \n",
       "91467  HOUSEHOLD_2_516_WI_3   7         forecast  0.819534  0.236555   \n",
       "91468  HOUSEHOLD_2_516_WI_3  14         forecast  0.811475  0.251556   \n",
       "91469  HOUSEHOLD_2_516_WI_3  28         forecast  0.851410  0.194284   \n",
       "\n",
       "           pMAE       MSE      RMSE     pRMSE  \n",
       "0      1.408503  1.014595  1.007271  1.566866  \n",
       "1      1.501726  1.021288  1.010588  1.572026  \n",
       "2      1.388432  0.928325  0.963496  1.498772  \n",
       "3      1.005593  0.656381  0.810173  1.134243  \n",
       "4      0.965910  0.590175  0.768228  1.075520  \n",
       "...         ...       ...       ...       ...  \n",
       "91465  2.031330  0.126322  0.355418  2.487925  \n",
       "91466  1.750101  0.123070  0.350814  2.455696  \n",
       "91467  3.311774  0.082443  0.287129  4.019802  \n",
       "91468  3.521783  0.087483  0.295775  4.140856  \n",
       "91469  2.719975  0.071092  0.266630  3.732821  \n",
       "\n",
       "[91470 rows x 9 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO - parameterize\n",
    "\n",
    "query = f\"\"\"\n",
    "WITH\n",
    "    STACKS AS (\n",
    "        SELECT *, 28 as CW FROM `{PROJECT_ID}.{BQ_DATASET}.{BQ_TABLE}_automl_cw28` UNION ALL\n",
    "        SELECT *, 14 as CW FROM `{PROJECT_ID}.{BQ_DATASET}.{BQ_TABLE}_automl_cw14` UNION ALL\n",
    "        SELECT *, 7 as CW FROM `{PROJECT_ID}.{BQ_DATASET}.{BQ_TABLE}_automl_cw7`\n",
    "    ),\n",
    "    FORECASTS AS (\n",
    "        SELECT DATE({TIME_COLUMN}) as {TIME_COLUMN}, \n",
    "        DATE(predicted_on_date) as predited_on_date, \n",
    "        CAST({TARGET_COLUMN} as INT64) AS {TARGET_COLUMN}, \n",
    "        #splits, \n",
    "        {SERIES_COLUMN}, \n",
    "        CW, \n",
    "        predicted_{TARGET_COLUMN}.value as predicted_{TARGET_COLUMN}\n",
    "        FROM STACKS\n",
    "    ),\n",
    "    LEAD_DAYS AS (\n",
    "        SELECT *, DATE_DIFF({TIME_COLUMN}, predited_on_date, DAY) as prediction_lead_days\n",
    "        FROM FORECASTS\n",
    "    ),\n",
    "    LATEST AS (\n",
    "        SELECT \n",
    "            {SERIES_COLUMN}, \n",
    "            {TIME_COLUMN}, \n",
    "            min(prediction_lead_days) as prediction_lead_days\n",
    "        FROM LEAD_DAYS\n",
    "        GROUP BY {SERIES_COLUMN}, {TIME_COLUMN}\n",
    "    ),\n",
    "    DIFFS AS (\n",
    "        SELECT \n",
    "            {SERIES_COLUMN}, \n",
    "            CW, \n",
    "            {TIME_COLUMN}, \n",
    "            'forecast' as time_series_type,\n",
    "            predicted_{TARGET_COLUMN} as forecast_value,\n",
    "            {TARGET_COLUMN} as actual_value,\n",
    "            ({TARGET_COLUMN} - predicted_{TARGET_COLUMN}) as diff\n",
    "        FROM LATEST\n",
    "        LEFT OUTER JOIN LEAD_DAYS\n",
    "        USING ({SERIES_COLUMN}, {TIME_COLUMN}, prediction_lead_days)    \n",
    "    )\n",
    "SELECT \n",
    "    {SERIES_COLUMN}, \n",
    "    CW, \n",
    "    time_series_type, \n",
    "    AVG(SAFE_DIVIDE(ABS(diff), actual_value)) as MAPE,\n",
    "    AVG(ABS(diff)) as MAE,\n",
    "    SAFE_DIVIDE(SUM(ABS(diff)),SUM(actual_value)) as pMAE,\n",
    "    AVG(POW(diff, 2)) as MSE,\n",
    "    SQRT(AVG(POW(diff, 2))) as RMSE,\n",
    "    SAFE_DIVIDE(SQRT( AVG( POW(diff, 2) ) ) , AVG(actual_value) ) as pRMSE\n",
    "FROM DIFFS\n",
    "GROUP BY {SERIES_COLUMN}, CW, time_series_type\n",
    "ORDER BY {SERIES_COLUMN}, CW, time_series_type    \n",
    "\"\"\"\n",
    "customMetrics = bq_client.query(query = query).to_dataframe()\n",
    "customMetrics\n",
    "\n",
    "# print to run in BigQuery console\n",
    "# print(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a81f46e-c217-4bd6-a4b0-ff0c319b4f2b",
   "metadata": {},
   "source": [
    "### Find best context_window scenario with best (minimum) pMAE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "71e235d8-1ceb-4ef6-813e-1dc4d8d3a0a3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>timeseries_id</th>\n",
       "      <th>CW</th>\n",
       "      <th>time_series_type</th>\n",
       "      <th>MAPE</th>\n",
       "      <th>MAE</th>\n",
       "      <th>pMAE</th>\n",
       "      <th>MSE</th>\n",
       "      <th>RMSE</th>\n",
       "      <th>pRMSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>FOODS_1_001_CA_1</td>\n",
       "      <td>28</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.516518</td>\n",
       "      <td>0.892563</td>\n",
       "      <td>1.388432</td>\n",
       "      <td>0.928325</td>\n",
       "      <td>0.963496</td>\n",
       "      <td>1.498772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>FOODS_1_001_CA_2</td>\n",
       "      <td>14</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.452630</td>\n",
       "      <td>0.689935</td>\n",
       "      <td>0.965910</td>\n",
       "      <td>0.590175</td>\n",
       "      <td>0.768228</td>\n",
       "      <td>1.075520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>FOODS_1_001_CA_3</td>\n",
       "      <td>7</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.473867</td>\n",
       "      <td>0.797022</td>\n",
       "      <td>0.858331</td>\n",
       "      <td>0.836548</td>\n",
       "      <td>0.914630</td>\n",
       "      <td>0.984986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>FOODS_1_001_CA_4</td>\n",
       "      <td>28</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.758530</td>\n",
       "      <td>0.379439</td>\n",
       "      <td>1.770714</td>\n",
       "      <td>0.184100</td>\n",
       "      <td>0.429069</td>\n",
       "      <td>2.002322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13</td>\n",
       "      <td>FOODS_1_001_TX_1</td>\n",
       "      <td>14</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.374977</td>\n",
       "      <td>0.886159</td>\n",
       "      <td>0.729778</td>\n",
       "      <td>1.841098</td>\n",
       "      <td>1.356871</td>\n",
       "      <td>1.117423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28671</th>\n",
       "      <td>91457</td>\n",
       "      <td>HOUSEHOLD_2_516_TX_2</td>\n",
       "      <td>28</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.855528</td>\n",
       "      <td>0.264559</td>\n",
       "      <td>1.851912</td>\n",
       "      <td>0.128389</td>\n",
       "      <td>0.358314</td>\n",
       "      <td>2.508201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28672</th>\n",
       "      <td>91460</td>\n",
       "      <td>HOUSEHOLD_2_516_TX_3</td>\n",
       "      <td>28</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.844306</td>\n",
       "      <td>0.435946</td>\n",
       "      <td>1.220650</td>\n",
       "      <td>0.400848</td>\n",
       "      <td>0.633126</td>\n",
       "      <td>1.772752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28673</th>\n",
       "      <td>91463</td>\n",
       "      <td>HOUSEHOLD_2_516_WI_1</td>\n",
       "      <td>28</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.846965</td>\n",
       "      <td>0.223121</td>\n",
       "      <td>3.123693</td>\n",
       "      <td>0.080040</td>\n",
       "      <td>0.282913</td>\n",
       "      <td>3.960778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28674</th>\n",
       "      <td>91466</td>\n",
       "      <td>HOUSEHOLD_2_516_WI_2</td>\n",
       "      <td>28</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.852261</td>\n",
       "      <td>0.250014</td>\n",
       "      <td>1.750101</td>\n",
       "      <td>0.123070</td>\n",
       "      <td>0.350814</td>\n",
       "      <td>2.455696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28675</th>\n",
       "      <td>91469</td>\n",
       "      <td>HOUSEHOLD_2_516_WI_3</td>\n",
       "      <td>28</td>\n",
       "      <td>forecast</td>\n",
       "      <td>0.851410</td>\n",
       "      <td>0.194284</td>\n",
       "      <td>2.719975</td>\n",
       "      <td>0.071092</td>\n",
       "      <td>0.266630</td>\n",
       "      <td>3.732821</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28676 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       index         timeseries_id  CW time_series_type      MAPE       MAE  \\\n",
       "0          2      FOODS_1_001_CA_1  28         forecast  0.516518  0.892563   \n",
       "1          4      FOODS_1_001_CA_2  14         forecast  0.452630  0.689935   \n",
       "2          6      FOODS_1_001_CA_3   7         forecast  0.473867  0.797022   \n",
       "3         11      FOODS_1_001_CA_4  28         forecast  0.758530  0.379439   \n",
       "4         13      FOODS_1_001_TX_1  14         forecast  0.374977  0.886159   \n",
       "...      ...                   ...  ..              ...       ...       ...   \n",
       "28671  91457  HOUSEHOLD_2_516_TX_2  28         forecast  0.855528  0.264559   \n",
       "28672  91460  HOUSEHOLD_2_516_TX_3  28         forecast  0.844306  0.435946   \n",
       "28673  91463  HOUSEHOLD_2_516_WI_1  28         forecast  0.846965  0.223121   \n",
       "28674  91466  HOUSEHOLD_2_516_WI_2  28         forecast  0.852261  0.250014   \n",
       "28675  91469  HOUSEHOLD_2_516_WI_3  28         forecast  0.851410  0.194284   \n",
       "\n",
       "           pMAE       MSE      RMSE     pRMSE  \n",
       "0      1.388432  0.928325  0.963496  1.498772  \n",
       "1      0.965910  0.590175  0.768228  1.075520  \n",
       "2      0.858331  0.836548  0.914630  0.984986  \n",
       "3      1.770714  0.184100  0.429069  2.002322  \n",
       "4      0.729778  1.841098  1.356871  1.117423  \n",
       "...         ...       ...       ...       ...  \n",
       "28671  1.851912  0.128389  0.358314  2.508201  \n",
       "28672  1.220650  0.400848  0.633126  1.772752  \n",
       "28673  3.123693  0.080040  0.282913  3.960778  \n",
       "28674  1.750101  0.123070  0.350814  2.455696  \n",
       "28675  2.719975  0.071092  0.266630  3.732821  \n",
       "\n",
       "[28676 rows x 10 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bestCW = customMetrics[customMetrics['pMAE'] == customMetrics.groupby([SERIES_COLUMN])['pMAE'].transform('min')].reset_index()\n",
    "bestCW"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "173d96fb-5afc-4682-8d63-a6d3df2bf664",
   "metadata": {},
   "source": [
    "## Create BigQuery Table with Best Forecast For Each Series based on pMAE\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "0f40a35a-32ec-4b63-9172-52bf88d89088",
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO\n",
    "tableMap = {\n",
    "    28 : f'{PROJECT_ID}.{BQ_DATASET}.{BQ_TABLE}_automl_cw28',\n",
    "    14 : f'{PROJECT_ID}.{BQ_DATASET}.{BQ_TABLE}_automl_cw14',\n",
    "    7 : f'{PROJECT_ID}.{BQ_DATASET}.{BQ_TABLE}_automl_cw7',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "04793fa8-3506-48e9-a5e3-cd17d077ca89",
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO\n",
    "\n",
    "# query = \"\"\"\"\"\"\n",
    "# for i, row in bestCW.iterrows():\n",
    "#     if i> 0: query += f\"\"\" UNION ALL\\n\"\"\"\n",
    "#     query += f\"\"\"SELECT *, {row['CW']} as CW FROM `{tableMap[row['CW']]}` WHERE {SERIES_COLUMN} = '{row[SERIES_COLUMN]}'\n",
    "#     \"\"\"\n",
    "    \n",
    "# query = f\"\"\"CREATE OR REPLACE TABLE `{PROJECT_ID}.{BQ_DATASET}.{BQ_TABLE}_automl_best` AS\n",
    "# \"\"\" + query + f\"\"\"ORDER BY {SERIES_COLUMN}, {TIME_COLUMN}\n",
    "# \"\"\"\n",
    "# print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "4203b64f-7ec2-43e7-a9b9-8afe78a35e62",
   "metadata": {},
   "outputs": [],
   "source": [
    "# job = bq_client.query(query = query)\n",
    "# job.result()\n",
    "# (job.ended-job.started).total_seconds()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "518af587-e3f9-4ba5-85ef-19817ca840a8",
   "metadata": {},
   "source": [
    "# Using The Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "1ab6a0cb-3e33-4845-9e91-b7625bb9c1eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e746903-5ee8-4645-a47f-f8535979e72c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "environment": {
   "kernel": "python3",
   "name": "common-cpu.m103",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/base-cpu:m103"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}