MMA830_SoftDrink_Michael.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MMA830 Assignment 2\n",
    "## Team Oilers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 513,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 7.96 ms\n"
     ]
    }
   ],
   "source": [
    "#%load_ext lab_black\n",
    "#%load_ext autotime\n",
    "from IPython import InteractiveShell\n",
    "\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "\n",
    "import warnings\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "pd.set_option(\"display.max_colwidth\", 300)\n",
    "pd.options.display.max_columns = None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 514,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 1.59 s\n"
     ]
    }
   ],
   "source": [
    "Promo = pd.read_excel(\"SoftDrinkSales-Data_origin.xlsx\", header=[0, 1])\n",
    "Promo.columns = [\"Promo_%s%s\" % (a, \"_%s\" % b if b else \"\") for a, b in Promo.columns]\n",
    "\n",
    "Price = pd.read_excel(\n",
    "    \"SoftDrinkSales-Data_origin.xlsx\", header=[0, 1], sheet_name=\"Price\"\n",
    ")\n",
    "Price.columns = [\"Price_%s%s\" % (a, \"_%s\" % b if b else \"\") for a, b in Price.columns]\n",
    "\n",
    "Bottle = pd.read_excel(\n",
    "    \"SoftDrinkSales-Data_origin.xlsx\", header=[0, 1], sheet_name=\"Bottle\"\n",
    ")\n",
    "Bottle.columns = [\n",
    "    \"Bottle_%s%s\" % (a, \"_%s\" % b if b else \"\") for a, b in Bottle.columns\n",
    "]\n",
    "\n",
    "SizePromo = pd.read_excel(\n",
    "    \"SoftDrinkSales-Data_origin.xlsx\", header=[0, 1], sheet_name=\"SizePromo\"\n",
    ")\n",
    "SizePromo.columns = [\n",
    "    \"SizePromo_%s%s\" % (a, \"_%s\" % b if b else \"\") for a, b in SizePromo.columns\n",
    "]\n",
    "\n",
    "Sales = pd.read_excel(\n",
    "    \"SoftDrinkSales-Data_origin.xlsx\", header=[1, 2], sheet_name=\"Sales\"\n",
    ").iloc[:, :13]\n",
    "Sales.columns = [\"Sales_%s%s\" % (a, \"_%s\" % b if b else \"\") for a, b in Sales.columns]\n",
    "\n",
    "Empirical_rates = pd.read_excel(\n",
    "    \"SoftDrinkSales-Data_origin.xlsx\", header=[0, 1], sheet_name=\"Empirical Rates\"\n",
    ")\n",
    "Empirical_rates.columns = [\n",
    "    \"Empirical_rates_%s%s\" % (a, \"_%s\" % b if b else \"\")\n",
    "    for a, b in Empirical_rates.columns\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 361,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 9.76 ms\n"
     ]
    }
   ],
   "source": [
    "df_list = [\n",
    "    Promo,\n",
    "    Price,\n",
    "    Bottle,\n",
    "    SizePromo,\n",
    "    Sales,\n",
    "    # Empirical_rates,\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 363,
   "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>Promo_Unnamed: 0_level_0_#Days</th>\n",
       "      <th>Promo_Brand_Segment</th>\n",
       "      <th>Promo_GOLD RUSH_VITAMIN</th>\n",
       "      <th>Promo_GOLD RUSH_DIET</th>\n",
       "      <th>Promo_GOLD RUSH_SPECIAL</th>\n",
       "      <th>Promo_GOLD RUSH_REGULAR</th>\n",
       "      <th>Promo_PWR_VITAMIN</th>\n",
       "      <th>Promo_PWR_DIET</th>\n",
       "      <th>Promo_PWR_SPECIAL</th>\n",
       "      <th>Promo_PWR_REGULAR</th>\n",
       "      <th>Promo_STORE BRAND_DIET</th>\n",
       "      <th>Promo_STORE BRAND_SPECIAL</th>\n",
       "      <th>Promo_STORE BRAND_REGULAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>27</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.5488</td>\n",
       "      <td>0.4375</td>\n",
       "      <td>0.4636</td>\n",
       "      <td>0.4458</td>\n",
       "      <td>0.1960</td>\n",
       "      <td>0.4347</td>\n",
       "      <td>0.4220</td>\n",
       "      <td>0.3863</td>\n",
       "      <td>0.9862</td>\n",
       "      <td>0.6266</td>\n",
       "      <td>0.5815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>34</td>\n",
       "      <td>0.019165</td>\n",
       "      <td>0.3882</td>\n",
       "      <td>0.5224</td>\n",
       "      <td>0.5543</td>\n",
       "      <td>0.5431</td>\n",
       "      <td>0.6220</td>\n",
       "      <td>0.5204</td>\n",
       "      <td>0.5572</td>\n",
       "      <td>0.6111</td>\n",
       "      <td>0.2912</td>\n",
       "      <td>0.2420</td>\n",
       "      <td>0.2590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41</td>\n",
       "      <td>0.038330</td>\n",
       "      <td>0.6328</td>\n",
       "      <td>0.7431</td>\n",
       "      <td>0.6776</td>\n",
       "      <td>0.6523</td>\n",
       "      <td>0.2211</td>\n",
       "      <td>0.2255</td>\n",
       "      <td>0.3886</td>\n",
       "      <td>0.4058</td>\n",
       "      <td>0.0103</td>\n",
       "      <td>0.3241</td>\n",
       "      <td>0.2892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48</td>\n",
       "      <td>0.057495</td>\n",
       "      <td>0.0841</td>\n",
       "      <td>0.3808</td>\n",
       "      <td>0.4405</td>\n",
       "      <td>0.3342</td>\n",
       "      <td>0.0024</td>\n",
       "      <td>0.2102</td>\n",
       "      <td>0.1079</td>\n",
       "      <td>0.3474</td>\n",
       "      <td>0.2838</td>\n",
       "      <td>0.5565</td>\n",
       "      <td>0.4791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>55</td>\n",
       "      <td>0.076660</td>\n",
       "      <td>0.2894</td>\n",
       "      <td>0.4746</td>\n",
       "      <td>0.4826</td>\n",
       "      <td>0.4046</td>\n",
       "      <td>0.4799</td>\n",
       "      <td>0.6786</td>\n",
       "      <td>0.6086</td>\n",
       "      <td>0.5892</td>\n",
       "      <td>0.9909</td>\n",
       "      <td>0.2938</td>\n",
       "      <td>0.4497</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Promo_Unnamed: 0_level_0_#Days  Promo_Brand_Segment  \\\n",
       "0                              27             0.000000   \n",
       "1                              34             0.019165   \n",
       "2                              41             0.038330   \n",
       "3                              48             0.057495   \n",
       "4                              55             0.076660   \n",
       "\n",
       "   Promo_GOLD RUSH_VITAMIN  Promo_GOLD RUSH_DIET  Promo_GOLD RUSH_SPECIAL  \\\n",
       "0                   0.5488                0.4375                   0.4636   \n",
       "1                   0.3882                0.5224                   0.5543   \n",
       "2                   0.6328                0.7431                   0.6776   \n",
       "3                   0.0841                0.3808                   0.4405   \n",
       "4                   0.2894                0.4746                   0.4826   \n",
       "\n",
       "   Promo_GOLD RUSH_REGULAR  Promo_PWR_VITAMIN  Promo_PWR_DIET  \\\n",
       "0                   0.4458             0.1960          0.4347   \n",
       "1                   0.5431             0.6220          0.5204   \n",
       "2                   0.6523             0.2211          0.2255   \n",
       "3                   0.3342             0.0024          0.2102   \n",
       "4                   0.4046             0.4799          0.6786   \n",
       "\n",
       "   Promo_PWR_SPECIAL  Promo_PWR_REGULAR  Promo_STORE BRAND_DIET  \\\n",
       "0             0.4220             0.3863                  0.9862   \n",
       "1             0.5572             0.6111                  0.2912   \n",
       "2             0.3886             0.4058                  0.0103   \n",
       "3             0.1079             0.3474                  0.2838   \n",
       "4             0.6086             0.5892                  0.9909   \n",
       "\n",
       "   Promo_STORE BRAND_SPECIAL  Promo_STORE BRAND_REGULAR  \n",
       "0                     0.6266                     0.5815  \n",
       "1                     0.2420                     0.2590  \n",
       "2                     0.3241                     0.2892  \n",
       "3                     0.5565                     0.4791  \n",
       "4                     0.2938                     0.4497  "
      ]
     },
     "execution_count": 363,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 20 ms\n"
     ]
    }
   ],
   "source": [
    "Promo.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 364,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 15.3 ms\n"
     ]
    }
   ],
   "source": [
    "for i in df_list:\n",
    "    i.iloc[:, 1] = (i.iloc[:, 0] - i.iloc[0, 0]) / 365.25"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 365,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 131 ms\n"
     ]
    }
   ],
   "source": [
    "new_df_list = []\n",
    "for i in range(11):\n",
    "    df = df_list[0].iloc[:, 1]\n",
    "    for j in df_list:\n",
    "        df = pd.concat([df, j.iloc[:, i + 2]], axis=1)\n",
    "    new_df_list.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 369,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 2.07 s\n"
     ]
    }
   ],
   "source": [
    "clean_df_list_train = []\n",
    "clean_df_list_whole = []\n",
    "for i in new_df_list:\n",
    "    i.columns = [\n",
    "        \"Trend\",\n",
    "        \"Promo\",\n",
    "        \"Price\",\n",
    "        \"Bottle\",\n",
    "        \"SizePromo\",\n",
    "        \"y\",\n",
    "        # \"Products\",\n",
    "    ]\n",
    "    clean_df_list_train.append(i.iloc[:148, :])\n",
    "    clean_df_list_whole.append(i)\n",
    "\n",
    "for j in clean_df_list_train:\n",
    "    for i in range(1, 27):\n",
    "        j[\"cos\" + str(i)] = np.cos(2 * np.pi * j.iloc[:, 0] * i)\n",
    "        j[\"sin\" + str(i)] = np.cos(2 * np.pi * j.iloc[:, 0] * i)\n",
    "for j in clean_df_list_whole:\n",
    "    for i in range(1, 27):\n",
    "        j[\"cos\" + str(i)] = np.cos(2 * np.pi * j.iloc[:, 0] * i)\n",
    "        j[\"sin\" + str(i)] = np.cos(2 * np.pi * j.iloc[:, 0] * i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 370,
   "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>Trend</th>\n",
       "      <th>Promo</th>\n",
       "      <th>Price</th>\n",
       "      <th>Bottle</th>\n",
       "      <th>SizePromo</th>\n",
       "      <th>y</th>\n",
       "      <th>cos1</th>\n",
       "      <th>sin1</th>\n",
       "      <th>cos2</th>\n",
       "      <th>sin2</th>\n",
       "      <th>cos3</th>\n",
       "      <th>sin3</th>\n",
       "      <th>cos4</th>\n",
       "      <th>sin4</th>\n",
       "      <th>cos5</th>\n",
       "      <th>sin5</th>\n",
       "      <th>cos6</th>\n",
       "      <th>sin6</th>\n",
       "      <th>cos7</th>\n",
       "      <th>sin7</th>\n",
       "      <th>cos8</th>\n",
       "      <th>sin8</th>\n",
       "      <th>cos9</th>\n",
       "      <th>sin9</th>\n",
       "      <th>cos10</th>\n",
       "      <th>sin10</th>\n",
       "      <th>cos11</th>\n",
       "      <th>sin11</th>\n",
       "      <th>cos12</th>\n",
       "      <th>sin12</th>\n",
       "      <th>cos13</th>\n",
       "      <th>sin13</th>\n",
       "      <th>cos14</th>\n",
       "      <th>sin14</th>\n",
       "      <th>cos15</th>\n",
       "      <th>sin15</th>\n",
       "      <th>cos16</th>\n",
       "      <th>sin16</th>\n",
       "      <th>cos17</th>\n",
       "      <th>sin17</th>\n",
       "      <th>cos18</th>\n",
       "      <th>sin18</th>\n",
       "      <th>cos19</th>\n",
       "      <th>sin19</th>\n",
       "      <th>cos20</th>\n",
       "      <th>sin20</th>\n",
       "      <th>cos21</th>\n",
       "      <th>sin21</th>\n",
       "      <th>cos22</th>\n",
       "      <th>sin22</th>\n",
       "      <th>cos23</th>\n",
       "      <th>sin23</th>\n",
       "      <th>cos24</th>\n",
       "      <th>sin24</th>\n",
       "      <th>cos25</th>\n",
       "      <th>sin25</th>\n",
       "      <th>cos26</th>\n",
       "      <th>sin26</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.5488</td>\n",
       "      <td>1.7972</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16355</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.019165</td>\n",
       "      <td>0.3882</td>\n",
       "      <td>1.7640</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>19224</td>\n",
       "      <td>0.992759</td>\n",
       "      <td>0.992759</td>\n",
       "      <td>0.971139</td>\n",
       "      <td>0.971139</td>\n",
       "      <td>0.935455</td>\n",
       "      <td>0.935455</td>\n",
       "      <td>0.886224</td>\n",
       "      <td>0.886224</td>\n",
       "      <td>0.824157</td>\n",
       "      <td>0.824157</td>\n",
       "      <td>0.750154</td>\n",
       "      <td>0.750154</td>\n",
       "      <td>0.665287</td>\n",
       "      <td>0.665287</td>\n",
       "      <td>0.570784</td>\n",
       "      <td>0.570784</td>\n",
       "      <td>0.468015</td>\n",
       "      <td>0.468015</td>\n",
       "      <td>0.358468</td>\n",
       "      <td>0.358468</td>\n",
       "      <td>0.243730</td>\n",
       "      <td>0.243730</td>\n",
       "      <td>0.125461</td>\n",
       "      <td>0.125461</td>\n",
       "      <td>0.005376</td>\n",
       "      <td>0.005376</td>\n",
       "      <td>-0.114788</td>\n",
       "      <td>-0.114788</td>\n",
       "      <td>-0.233289</td>\n",
       "      <td>-0.233289</td>\n",
       "      <td>-0.348411</td>\n",
       "      <td>-0.348411</td>\n",
       "      <td>-0.458487</td>\n",
       "      <td>-0.458487</td>\n",
       "      <td>-0.561923</td>\n",
       "      <td>-0.561923</td>\n",
       "      <td>-0.657221</td>\n",
       "      <td>-0.657221</td>\n",
       "      <td>-0.743001</td>\n",
       "      <td>-0.743001</td>\n",
       "      <td>-0.818020</td>\n",
       "      <td>-0.818020</td>\n",
       "      <td>-0.881192</td>\n",
       "      <td>-0.881192</td>\n",
       "      <td>-0.931601</td>\n",
       "      <td>-0.931601</td>\n",
       "      <td>-0.968519</td>\n",
       "      <td>-0.968519</td>\n",
       "      <td>-0.991410</td>\n",
       "      <td>-0.991410</td>\n",
       "      <td>-0.999942</td>\n",
       "      <td>-0.999942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.038330</td>\n",
       "      <td>0.6328</td>\n",
       "      <td>1.6176</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23265</td>\n",
       "      <td>0.971139</td>\n",
       "      <td>0.971139</td>\n",
       "      <td>0.886224</td>\n",
       "      <td>0.886224</td>\n",
       "      <td>0.750154</td>\n",
       "      <td>0.750154</td>\n",
       "      <td>0.570784</td>\n",
       "      <td>0.570784</td>\n",
       "      <td>0.358468</td>\n",
       "      <td>0.358468</td>\n",
       "      <td>0.125461</td>\n",
       "      <td>0.125461</td>\n",
       "      <td>-0.114788</td>\n",
       "      <td>-0.114788</td>\n",
       "      <td>-0.348411</td>\n",
       "      <td>-0.348411</td>\n",
       "      <td>-0.561923</td>\n",
       "      <td>-0.561923</td>\n",
       "      <td>-0.743001</td>\n",
       "      <td>-0.743001</td>\n",
       "      <td>-0.881192</td>\n",
       "      <td>-0.881192</td>\n",
       "      <td>-0.968519</td>\n",
       "      <td>-0.968519</td>\n",
       "      <td>-0.999942</td>\n",
       "      <td>-0.999942</td>\n",
       "      <td>-0.973648</td>\n",
       "      <td>-0.973648</td>\n",
       "      <td>-0.891153</td>\n",
       "      <td>-0.891153</td>\n",
       "      <td>-0.757220</td>\n",
       "      <td>-0.757220</td>\n",
       "      <td>-0.579579</td>\n",
       "      <td>-0.579579</td>\n",
       "      <td>-0.368484</td>\n",
       "      <td>-0.368484</td>\n",
       "      <td>-0.136120</td>\n",
       "      <td>-0.136120</td>\n",
       "      <td>0.104101</td>\n",
       "      <td>0.104101</td>\n",
       "      <td>0.338313</td>\n",
       "      <td>0.338313</td>\n",
       "      <td>0.552997</td>\n",
       "      <td>0.552997</td>\n",
       "      <td>0.735762</td>\n",
       "      <td>0.735762</td>\n",
       "      <td>0.876058</td>\n",
       "      <td>0.876058</td>\n",
       "      <td>0.965787</td>\n",
       "      <td>0.965787</td>\n",
       "      <td>0.999769</td>\n",
       "      <td>0.999769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.057495</td>\n",
       "      <td>0.0841</td>\n",
       "      <td>1.9207</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12253</td>\n",
       "      <td>0.935455</td>\n",
       "      <td>0.935455</td>\n",
       "      <td>0.750154</td>\n",
       "      <td>0.750154</td>\n",
       "      <td>0.468015</td>\n",
       "      <td>0.468015</td>\n",
       "      <td>0.125461</td>\n",
       "      <td>0.125461</td>\n",
       "      <td>-0.233289</td>\n",
       "      <td>-0.233289</td>\n",
       "      <td>-0.561923</td>\n",
       "      <td>-0.561923</td>\n",
       "      <td>-0.818020</td>\n",
       "      <td>-0.818020</td>\n",
       "      <td>-0.968519</td>\n",
       "      <td>-0.968519</td>\n",
       "      <td>-0.993993</td>\n",
       "      <td>-0.993993</td>\n",
       "      <td>-0.891153</td>\n",
       "      <td>-0.891153</td>\n",
       "      <td>-0.673275</td>\n",
       "      <td>-0.673275</td>\n",
       "      <td>-0.368484</td>\n",
       "      <td>-0.368484</td>\n",
       "      <td>-0.016127</td>\n",
       "      <td>-0.016127</td>\n",
       "      <td>0.338313</td>\n",
       "      <td>0.338313</td>\n",
       "      <td>0.649080</td>\n",
       "      <td>0.649080</td>\n",
       "      <td>0.876058</td>\n",
       "      <td>0.876058</td>\n",
       "      <td>0.989946</td>\n",
       "      <td>0.989946</td>\n",
       "      <td>0.976043</td>\n",
       "      <td>0.976043</td>\n",
       "      <td>0.836144</td>\n",
       "      <td>0.836144</td>\n",
       "      <td>0.588307</td>\n",
       "      <td>0.588307</td>\n",
       "      <td>0.264526</td>\n",
       "      <td>0.264526</td>\n",
       "      <td>-0.093402</td>\n",
       "      <td>-0.093402</td>\n",
       "      <td>-0.439273</td>\n",
       "      <td>-0.439273</td>\n",
       "      <td>-0.728438</td>\n",
       "      <td>-0.728438</td>\n",
       "      <td>-0.923571</td>\n",
       "      <td>-0.923571</td>\n",
       "      <td>-0.999480</td>\n",
       "      <td>-0.999480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.076660</td>\n",
       "      <td>0.2894</td>\n",
       "      <td>1.8301</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13452</td>\n",
       "      <td>0.886224</td>\n",
       "      <td>0.886224</td>\n",
       "      <td>0.570784</td>\n",
       "      <td>0.570784</td>\n",
       "      <td>0.125461</td>\n",
       "      <td>0.125461</td>\n",
       "      <td>-0.348411</td>\n",
       "      <td>-0.348411</td>\n",
       "      <td>-0.743001</td>\n",
       "      <td>-0.743001</td>\n",
       "      <td>-0.968519</td>\n",
       "      <td>-0.968519</td>\n",
       "      <td>-0.973648</td>\n",
       "      <td>-0.973648</td>\n",
       "      <td>-0.757220</td>\n",
       "      <td>-0.757220</td>\n",
       "      <td>-0.368484</td>\n",
       "      <td>-0.368484</td>\n",
       "      <td>0.104101</td>\n",
       "      <td>0.104101</td>\n",
       "      <td>0.552997</td>\n",
       "      <td>0.552997</td>\n",
       "      <td>0.876058</td>\n",
       "      <td>0.876058</td>\n",
       "      <td>0.999769</td>\n",
       "      <td>0.999769</td>\n",
       "      <td>0.895979</td>\n",
       "      <td>0.895979</td>\n",
       "      <td>0.588307</td>\n",
       "      <td>0.588307</td>\n",
       "      <td>0.146764</td>\n",
       "      <td>0.146764</td>\n",
       "      <td>-0.328176</td>\n",
       "      <td>-0.328176</td>\n",
       "      <td>-0.728438</td>\n",
       "      <td>-0.728438</td>\n",
       "      <td>-0.962943</td>\n",
       "      <td>-0.962943</td>\n",
       "      <td>-0.978326</td>\n",
       "      <td>-0.978326</td>\n",
       "      <td>-0.771089</td>\n",
       "      <td>-0.771089</td>\n",
       "      <td>-0.388388</td>\n",
       "      <td>-0.388388</td>\n",
       "      <td>0.082692</td>\n",
       "      <td>0.082692</td>\n",
       "      <td>0.534955</td>\n",
       "      <td>0.534955</td>\n",
       "      <td>0.865487</td>\n",
       "      <td>0.865487</td>\n",
       "      <td>0.999075</td>\n",
       "      <td>0.999075</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Trend   Promo   Price  Bottle  SizePromo      y      cos1      sin1  \\\n",
       "0  0.000000  0.5488  1.7972       0          0  16355  1.000000  1.000000   \n",
       "1  0.019165  0.3882  1.7640       0          0  19224  0.992759  0.992759   \n",
       "2  0.038330  0.6328  1.6176       0          0  23265  0.971139  0.971139   \n",
       "3  0.057495  0.0841  1.9207       0          0  12253  0.935455  0.935455   \n",
       "4  0.076660  0.2894  1.8301       0          0  13452  0.886224  0.886224   \n",
       "\n",
       "       cos2      sin2      cos3      sin3      cos4      sin4      cos5  \\\n",
       "0  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000   \n",
       "1  0.971139  0.971139  0.935455  0.935455  0.886224  0.886224  0.824157   \n",
       "2  0.886224  0.886224  0.750154  0.750154  0.570784  0.570784  0.358468   \n",
       "3  0.750154  0.750154  0.468015  0.468015  0.125461  0.125461 -0.233289   \n",
       "4  0.570784  0.570784  0.125461  0.125461 -0.348411 -0.348411 -0.743001   \n",
       "\n",
       "       sin5      cos6      sin6      cos7      sin7      cos8      sin8  \\\n",
       "0  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000   \n",
       "1  0.824157  0.750154  0.750154  0.665287  0.665287  0.570784  0.570784   \n",
       "2  0.358468  0.125461  0.125461 -0.114788 -0.114788 -0.348411 -0.348411   \n",
       "3 -0.233289 -0.561923 -0.561923 -0.818020 -0.818020 -0.968519 -0.968519   \n",
       "4 -0.743001 -0.968519 -0.968519 -0.973648 -0.973648 -0.757220 -0.757220   \n",
       "\n",
       "       cos9      sin9     cos10     sin10     cos11     sin11     cos12  \\\n",
       "0  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000   \n",
       "1  0.468015  0.468015  0.358468  0.358468  0.243730  0.243730  0.125461   \n",
       "2 -0.561923 -0.561923 -0.743001 -0.743001 -0.881192 -0.881192 -0.968519   \n",
       "3 -0.993993 -0.993993 -0.891153 -0.891153 -0.673275 -0.673275 -0.368484   \n",
       "4 -0.368484 -0.368484  0.104101  0.104101  0.552997  0.552997  0.876058   \n",
       "\n",
       "      sin12     cos13     sin13     cos14     sin14     cos15     sin15  \\\n",
       "0  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000   \n",
       "1  0.125461  0.005376  0.005376 -0.114788 -0.114788 -0.233289 -0.233289   \n",
       "2 -0.968519 -0.999942 -0.999942 -0.973648 -0.973648 -0.891153 -0.891153   \n",
       "3 -0.368484 -0.016127 -0.016127  0.338313  0.338313  0.649080  0.649080   \n",
       "4  0.876058  0.999769  0.999769  0.895979  0.895979  0.588307  0.588307   \n",
       "\n",
       "      cos16     sin16     cos17     sin17     cos18     sin18     cos19  \\\n",
       "0  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000   \n",
       "1 -0.348411 -0.348411 -0.458487 -0.458487 -0.561923 -0.561923 -0.657221   \n",
       "2 -0.757220 -0.757220 -0.579579 -0.579579 -0.368484 -0.368484 -0.136120   \n",
       "3  0.876058  0.876058  0.989946  0.989946  0.976043  0.976043  0.836144   \n",
       "4  0.146764  0.146764 -0.328176 -0.328176 -0.728438 -0.728438 -0.962943   \n",
       "\n",
       "      sin19     cos20     sin20     cos21     sin21     cos22     sin22  \\\n",
       "0  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000   \n",
       "1 -0.657221 -0.743001 -0.743001 -0.818020 -0.818020 -0.881192 -0.881192   \n",
       "2 -0.136120  0.104101  0.104101  0.338313  0.338313  0.552997  0.552997   \n",
       "3  0.836144  0.588307  0.588307  0.264526  0.264526 -0.093402 -0.093402   \n",
       "4 -0.962943 -0.978326 -0.978326 -0.771089 -0.771089 -0.388388 -0.388388   \n",
       "\n",
       "      cos23     sin23     cos24     sin24     cos25     sin25     cos26  \\\n",
       "0  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000  1.000000   \n",
       "1 -0.931601 -0.931601 -0.968519 -0.968519 -0.991410 -0.991410 -0.999942   \n",
       "2  0.735762  0.735762  0.876058  0.876058  0.965787  0.965787  0.999769   \n",
       "3 -0.439273 -0.439273 -0.728438 -0.728438 -0.923571 -0.923571 -0.999480   \n",
       "4  0.082692  0.082692  0.534955  0.534955  0.865487  0.865487  0.999075   \n",
       "\n",
       "      sin26  \n",
       "0  1.000000  \n",
       "1 -0.999942  \n",
       "2  0.999769  \n",
       "3 -0.999480  \n",
       "4  0.999075  "
      ]
     },
     "execution_count": 370,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 110 ms\n"
     ]
    }
   ],
   "source": [
    "clean_df_list_train[0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build models"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Predict the rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 595,
   "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>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.021072</td>\n",
       "      <td>0.064043</td>\n",
       "      <td>0.044416</td>\n",
       "      <td>0.091059</td>\n",
       "      <td>0.040919</td>\n",
       "      <td>0.076350</td>\n",
       "      <td>0.200774</td>\n",
       "      <td>0.258090</td>\n",
       "      <td>0.007978</td>\n",
       "      <td>0.078308</td>\n",
       "      <td>0.116991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.023298</td>\n",
       "      <td>0.061906</td>\n",
       "      <td>0.046900</td>\n",
       "      <td>0.090869</td>\n",
       "      <td>0.056156</td>\n",
       "      <td>0.080817</td>\n",
       "      <td>0.224177</td>\n",
       "      <td>0.284714</td>\n",
       "      <td>0.006196</td>\n",
       "      <td>0.049299</td>\n",
       "      <td>0.075667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.030359</td>\n",
       "      <td>0.082886</td>\n",
       "      <td>0.056986</td>\n",
       "      <td>0.117720</td>\n",
       "      <td>0.041671</td>\n",
       "      <td>0.068444</td>\n",
       "      <td>0.199634</td>\n",
       "      <td>0.241906</td>\n",
       "      <td>0.005342</td>\n",
       "      <td>0.059728</td>\n",
       "      <td>0.095324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.018575</td>\n",
       "      <td>0.067189</td>\n",
       "      <td>0.048763</td>\n",
       "      <td>0.097075</td>\n",
       "      <td>0.048137</td>\n",
       "      <td>0.075852</td>\n",
       "      <td>0.201007</td>\n",
       "      <td>0.252000</td>\n",
       "      <td>0.007271</td>\n",
       "      <td>0.076073</td>\n",
       "      <td>0.108057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.016340</td>\n",
       "      <td>0.057461</td>\n",
       "      <td>0.044031</td>\n",
       "      <td>0.083720</td>\n",
       "      <td>0.042927</td>\n",
       "      <td>0.092074</td>\n",
       "      <td>0.216649</td>\n",
       "      <td>0.279461</td>\n",
       "      <td>0.010321</td>\n",
       "      <td>0.059060</td>\n",
       "      <td>0.097955</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>0.014013</td>\n",
       "      <td>0.095228</td>\n",
       "      <td>0.062234</td>\n",
       "      <td>0.107953</td>\n",
       "      <td>0.042399</td>\n",
       "      <td>0.103962</td>\n",
       "      <td>0.188743</td>\n",
       "      <td>0.245628</td>\n",
       "      <td>0.014431</td>\n",
       "      <td>0.052256</td>\n",
       "      <td>0.073152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>0.015287</td>\n",
       "      <td>0.097889</td>\n",
       "      <td>0.062141</td>\n",
       "      <td>0.107305</td>\n",
       "      <td>0.041787</td>\n",
       "      <td>0.103111</td>\n",
       "      <td>0.185517</td>\n",
       "      <td>0.245118</td>\n",
       "      <td>0.014563</td>\n",
       "      <td>0.053969</td>\n",
       "      <td>0.073313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>0.015110</td>\n",
       "      <td>0.097207</td>\n",
       "      <td>0.061595</td>\n",
       "      <td>0.106823</td>\n",
       "      <td>0.041722</td>\n",
       "      <td>0.104638</td>\n",
       "      <td>0.184664</td>\n",
       "      <td>0.247170</td>\n",
       "      <td>0.014999</td>\n",
       "      <td>0.053227</td>\n",
       "      <td>0.072845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>0.014677</td>\n",
       "      <td>0.097682</td>\n",
       "      <td>0.063060</td>\n",
       "      <td>0.108180</td>\n",
       "      <td>0.041608</td>\n",
       "      <td>0.107188</td>\n",
       "      <td>0.183579</td>\n",
       "      <td>0.246894</td>\n",
       "      <td>0.014359</td>\n",
       "      <td>0.051068</td>\n",
       "      <td>0.071704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>0.015279</td>\n",
       "      <td>0.099843</td>\n",
       "      <td>0.062583</td>\n",
       "      <td>0.109682</td>\n",
       "      <td>0.040797</td>\n",
       "      <td>0.104396</td>\n",
       "      <td>0.180526</td>\n",
       "      <td>0.247439</td>\n",
       "      <td>0.013264</td>\n",
       "      <td>0.053214</td>\n",
       "      <td>0.072979</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>160 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            0         0         0         0         0         0         0  \\\n",
       "0    0.021072  0.064043  0.044416  0.091059  0.040919  0.076350  0.200774   \n",
       "1    0.023298  0.061906  0.046900  0.090869  0.056156  0.080817  0.224177   \n",
       "2    0.030359  0.082886  0.056986  0.117720  0.041671  0.068444  0.199634   \n",
       "3    0.018575  0.067189  0.048763  0.097075  0.048137  0.075852  0.201007   \n",
       "4    0.016340  0.057461  0.044031  0.083720  0.042927  0.092074  0.216649   \n",
       "..        ...       ...       ...       ...       ...       ...       ...   \n",
       "155  0.014013  0.095228  0.062234  0.107953  0.042399  0.103962  0.188743   \n",
       "156  0.015287  0.097889  0.062141  0.107305  0.041787  0.103111  0.185517   \n",
       "157  0.015110  0.097207  0.061595  0.106823  0.041722  0.104638  0.184664   \n",
       "158  0.014677  0.097682  0.063060  0.108180  0.041608  0.107188  0.183579   \n",
       "159  0.015279  0.099843  0.062583  0.109682  0.040797  0.104396  0.180526   \n",
       "\n",
       "            0         0         0         0  \n",
       "0    0.258090  0.007978  0.078308  0.116991  \n",
       "1    0.284714  0.006196  0.049299  0.075667  \n",
       "2    0.241906  0.005342  0.059728  0.095324  \n",
       "3    0.252000  0.007271  0.076073  0.108057  \n",
       "4    0.279461  0.010321  0.059060  0.097955  \n",
       "..        ...       ...       ...       ...  \n",
       "155  0.245628  0.014431  0.052256  0.073152  \n",
       "156  0.245118  0.014563  0.053969  0.073313  \n",
       "157  0.247170  0.014999  0.053227  0.072845  \n",
       "158  0.246894  0.014359  0.051068  0.071704  \n",
       "159  0.247439  0.013264  0.053214  0.072979  \n",
       "\n",
       "[160 rows x 11 columns]"
      ]
     },
     "execution_count": 595,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "RMSE of Predicted Rates - Calibration:  0.0026647814471301473\n",
      "RMSE of Predicted Rates - Holdout:  0.06749615774187415\n",
      "time: 3.64 s\n"
     ]
    }
   ],
   "source": [
    "formula_part = \"\"\n",
    "for i in list(clean_df_list_train[0].columns):\n",
    "    formula_part = formula_part + \" + \" + i\n",
    "\n",
    "formula = \"np.log(y) ~ \" + formula_part\n",
    "Pridicted_Rates = pd.DataFrame()\n",
    "\n",
    "eleven_models = []\n",
    "for i in range(len(clean_df_list_train)):\n",
    "    results = ols(formula, clean_df_list_train[i]).fit()\n",
    "    eleven_models.append(results)\n",
    "    pred = np.exp(results.predict(clean_df_list_whole[i]))\n",
    "    Pridicted_Rates = pd.concat([Pridicted_Rates, pd.DataFrame(pred)], axis=1)\n",
    "\n",
    "Pridicted_Rates[\"sum\"] = Pridicted_Rates.sum(axis=1)\n",
    "for i in range(11):\n",
    "    Pridicted_Rates.iloc[:, i] = Pridicted_Rates.iloc[:, i] / Pridicted_Rates[\"sum\"]\n",
    "\n",
    "Sales_forecast = Pridicted_Rates.iloc[:, -1]\n",
    "Sales_forecast.to_excel(\"Sales_forest.xlsx\")\n",
    "Pridicted_Rates = Pridicted_Rates.iloc[:, :-1]\n",
    "Pridicted_Rates.to_excel(\"Pridicted_Rates.xlsx\")\n",
    "\n",
    "Pridicted_Rates\n",
    "print(\n",
    "    \"RMSE of Predicted Rates - Calibration: \",\n",
    "    sqrt(MSE(Empirical_rates.iloc[:148, 2:], Pridicted_Rates[:148])),\n",
    ")\n",
    "print(\n",
    "    \"RMSE of Predicted Rates - Holdout: \",\n",
    "    sqrt(MSE(Empirical_rates.iloc[148:, 2:], Pridicted_Rates[148:])),\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Predict the sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 597,
   "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>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.667585e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8.220275e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.859522e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.846940e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7.968700e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>1.004530e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>1.035285e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>1.258046e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>1.008202e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>1.013067e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>148 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              sum\n",
       "0    7.667585e+05\n",
       "1    8.220275e+05\n",
       "2    7.859522e+05\n",
       "3    6.846940e+05\n",
       "4    7.968700e+05\n",
       "..            ...\n",
       "143  1.004530e+06\n",
       "144  1.035285e+06\n",
       "145  1.258046e+06\n",
       "146  1.008202e+06\n",
       "147  1.013067e+06\n",
       "\n",
       "[148 rows x 1 columns]"
      ]
     },
     "execution_count": 597,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SUM (Residual Square):  33589398324.160686\n",
      "time: 275 ms\n"
     ]
    }
   ],
   "source": [
    "Actual_Sales = pd.read_excel(\n",
    "    \"SoftDrinkSales-Data_origin.xlsx\", header=[1, 2], sheet_name=\"Sales\"\n",
    ").iloc[:148, 13]\n",
    "\n",
    "pd.DataFrame(Sales_forecast[:148])\n",
    "print(\n",
    "    \"SUM (Residual Square): \", (np.sum(np.square(Sales_forecast[:148] - Actual_Sales)))\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Optimize Price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 599,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 999 µs\n"
     ]
    }
   ],
   "source": [
    "from scipy.optimize import minimize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 600,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 6.98 ms\n"
     ]
    }
   ],
   "source": [
    "day = 1112\n",
    "temp = Promo.reset_index()\n",
    "day_index = temp[temp.iloc[:, 1] == day].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 601,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 16 ms\n"
     ]
    }
   ],
   "source": [
    "day_1112_df_list = []\n",
    "for i in range(len(clean_df_list_whole)):\n",
    "    day_1112_df_list.append(clean_df_list_whole[i].iloc[day_index, :])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 581,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 16 ms\n"
     ]
    }
   ],
   "source": [
    "def profit(price_list):\n",
    "    sales_results = []\n",
    "    for i in range(len(price_list)):\n",
    "        day_1112_df_list[i].Price = price_list[i]\n",
    "        sales_results.append(\n",
    "            float(np.exp(eleven_models[i].predict(day_1112_df_list[i])))\n",
    "        )\n",
    "    sales_results = np.array(sales_results)\n",
    "\n",
    "    return -np.sum((sales_results * (price_list - cost_GOLD_RUSH)))\n",
    "\n",
    "\n",
    "def con(price_GOLD_RUSH):\n",
    "    return np.sum(price_GOLD_RUSH > cost_GOLD_RUSH) - 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 571,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 2min 8s\n"
     ]
    }
   ],
   "source": [
    "price_GOLD_RUSH = np.array([2, 2, 3, 3])\n",
    "cost_GOLD_RUSH = np.array([1.3, 1.2, 1.3, 1.2])\n",
    "opt = minimize(\n",
    "    profit, price_GOLD_RUSH, method=\"BFGS\", constraints={\"type\": \"eq\", \"fun\": con}\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 572,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "      fun: -282310.056154544\n",
       " hess_inv: array([[ 7.21591722e-04, -2.87244080e-05,  1.81502691e-04,\n",
       "        -2.70044507e-04],\n",
       "       [-2.87244080e-05,  1.74420136e-04, -4.95596202e-05,\n",
       "        -1.04176075e-05],\n",
       "       [ 1.81502691e-04, -4.95596202e-05,  5.51270638e-04,\n",
       "         1.84883978e-04],\n",
       "       [-2.70044507e-04, -1.04176075e-05,  1.84883978e-04,\n",
       "         2.27464255e-04]])\n",
       "      jac: array([0.       , 0.       , 0.0078125, 0.       ])\n",
       "  message: 'Desired error not necessarily achieved due to precision loss.'\n",
       "     nfev: 223\n",
       "      nit: 19\n",
       "     njev: 37\n",
       "   status: 2\n",
       "  success: False\n",
       "        x: array([ 4.58890118,  4.5464985 ,  6.22974096, 10.76474824])"
      ]
     },
     "execution_count": 572,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 6.99 ms\n"
     ]
    }
   ],
   "source": [
    "opt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 591,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 4.58890118,  4.5464985 ,  6.22974096, 10.76474824])"
      ]
     },
     "execution_count": 591,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 3.99 ms\n"
     ]
    }
   ],
   "source": [
    "opt.x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 592,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The best price is  [ 4.58890118  4.5464985   6.22974096 10.76474824]\n",
      "time: 4.05 ms\n"
     ]
    }
   ],
   "source": [
    "print(\"The best price is \", opt.x)"
   ]
  }
 ],
 "metadata": {
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}