store_sales.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.linear_model import ElasticNet\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.metrics import root_mean_squared_log_error, mean_squared_log_error, mean_squared_error, root_mean_squared_error\n",
    "from xgboost import XGBRegressor\n",
    "import statsmodels.graphics.tsaplots\n",
    "from statsmodels.tsa.deterministic import CalendarFourier, DeterministicProcess\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read relevant training data, make copies of the raw data\n",
    "\n",
    "oil_data_raw = pd.read_csv('data/oil.csv')\n",
    "holiday_data_raw = pd.read_csv('data/holidays_events.csv')\n",
    "transaction_data_raw = pd.read_csv('data/transactions.csv')\n",
    "store_data_raw = pd.read_csv('data/stores.csv')\n",
    "train_data_raw = pd.read_csv('data/train.csv')\n",
    "\n",
    "oil_data = oil_data_raw.copy()\n",
    "holiday_data = holiday_data_raw.copy()\n",
    "transaction_data = transaction_data_raw.copy()\n",
    "store_data = store_data_raw.copy()\n",
    "train_data = train_data_raw.copy()\n",
    "\n",
    "all_data = [oil_data, holiday_data, transaction_data, store_data, train_data]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# preprocessing: general cleanup\n",
    "\n",
    "# reformatting dates to datetime objects, drop duplicates\n",
    "\n",
    "oil_data['date'] = pd.to_datetime(oil_data['date'])\n",
    "holiday_data['date'] = pd.to_datetime(holiday_data['date'])\n",
    "transaction_data['date'] = pd.to_datetime(transaction_data['date'])\n",
    "train_data['date'] = pd.to_datetime(train_data['date'])\n",
    "\n",
    "oil_data.drop_duplicates(inplace=True)\n",
    "holiday_data.drop_duplicates(inplace=True)\n",
    "transaction_data.drop_duplicates(inplace=True)\n",
    "store_data.drop_duplicates(inplace=True)\n",
    "train_data.drop_duplicates(inplace=True)\n",
    "\n",
    "# truncate the data to only include the data before 2017-09-01, as the test data ends at 2017-08-31\n",
    "oil_data = oil_data[oil_data['date'] < '2017-09-01']\n",
    "holiday_data = holiday_data[holiday_data['date'] < '2017-09-01']\n",
    "transaction_data = transaction_data[transaction_data['date'] < '2017-09-01']\n",
    "train_data = train_data[train_data['date'] < '2017-09-01']\n",
    "\n",
    "train_data['onpromotion'] = train_data['onpromotion'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #EDA: check for missing values in each dataset\n",
    "\n",
    "# for data in all_data:\n",
    "#     nan_rows_count = data.isnull().any(axis=1).sum()\n",
    "\n",
    "#     print(f\"Number of rows with NaN or null values: {nan_rows_count}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #EDA: Oil Price Over Time\n",
    "\n",
    "# plt.figure(figsize=(10, 5))\n",
    "# plt.plot(oil_data['date'], oil_data['dcoilwtico'], label='Oil Price')\n",
    "\n",
    "# plt.gca().xaxis.set_major_locator(mdates.MonthLocator(interval=3))\n",
    "# plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))\n",
    "\n",
    "# plt.xlabel('Date')\n",
    "# plt.ylabel('Oil Price')\n",
    "# plt.title('Oil Price Over Time')\n",
    "# plt.grid()\n",
    "# plt.legend()\n",
    "# plt.gcf().autofmt_xdate()\n",
    "# plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Preprocessing: Fill missing values for oil price with the average of the previous and next values\n",
    "\n",
    "oil_data['dcoilwtico'] = oil_data['dcoilwtico'].interpolate()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #EDA: Holiday Events Over Time\n",
    "\n",
    "# for col in holiday_data.columns:\n",
    "#     print(f'{col}: {holiday_data[col].unique()}')\n",
    "#     print(len(holiday_data[col].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Doruk\\AppData\\Local\\Temp\\ipykernel_18352\\159779065.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  holiday_data.drop('transferred', axis=1, inplace=True)\n",
      "C:\\Users\\Doruk\\AppData\\Local\\Temp\\ipykernel_18352\\159779065.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  holiday_data.drop('description', axis=1, inplace=True)\n"
     ]
    }
   ],
   "source": [
    "#preprocessing: holiday data\n",
    "\n",
    "# reduce the number of unique values in the 'type' column \n",
    "holiday_data.loc[holiday_data['type'].isin(['Additional', 'Bridge', 'Transfer']), 'type'] = 'Holiday'\n",
    "holiday_data.loc[holiday_data['transferred'] == True, 'type'] = 'Transferred Holiday'\n",
    "holiday_data.drop('transferred', axis=1, inplace=True)\n",
    "holiday_data.drop('description', axis=1, inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #preprocessing: holiday data\n",
    "\n",
    "# # reduce the number of unique values in the 'description' column, as some values correspond to the same holiday\n",
    "\n",
    "# holiday_data['description'] = holiday_data['description'].str.replace(r'.*Mundial de futbol Brasil.*', 'World Cup', regex=True)\n",
    "\n",
    "# start_strings = ['Independencia', 'Fundacion', 'Provincializacion', 'Cantonizacion', 'Terremoto', 'Navidad', 'Dia de la Madre', 'Primer dia del ano']\n",
    "# useless_strings = ['Traslado ', 'Recupero ', 'Puente ']\n",
    "\n",
    "# for start_string in start_strings:\n",
    "#     pattern1 = rf'^{re.escape(start_string)}.*'\n",
    "#     holiday_data['description'] = holiday_data['description'].str.replace(pattern1, start_string, regex=True)\n",
    "\n",
    "# for useless_string in useless_strings:\n",
    "#     pattern2 = rf'{re.escape(useless_string)}.*'\n",
    "#     holiday_data['description'] = holiday_data['description'].str.replace(pattern2, '', regex=True)\n",
    "\n",
    "# # Verify the changes\n",
    "# print(holiday_data['description'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #EDA: Transactions Over Time for Each Store\n",
    "\n",
    "# plt.figure(figsize=(15, 8))\n",
    "\n",
    "# # Loop through each unique store_nbr\n",
    "# for store in sorted(transaction_data['store_nbr'].unique()):\n",
    "#     store_td = transaction_data[transaction_data['store_nbr'] == store]\n",
    "#     plt.plot(store_td['date'], store_td['transactions'], label=f'Store {store}')\n",
    "\n",
    "# plt.xlabel('Date')\n",
    "# plt.ylabel('Transactions')\n",
    "# plt.title('Transactions Over Time for Each Store')\n",
    "# plt.grid()\n",
    "# plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Eda: Partial Autocorrelation Plot for Transactions for each store\n",
    "\n",
    "# for store in sorted(transaction_data['store_nbr'].unique()):\n",
    "#     store_td = transaction_data[transaction_data['store_nbr'] == store]\n",
    "#     statsmodels.graphics.tsaplots.plot_pacf(store_td['transactions'], lags=30, title=f'Store {store} PACF')\n",
    "#     plt.xlabel('Lag')\n",
    "#     plt.ylabel('Partial Autocorrelation')\n",
    "#     plt.title(f'Partial Autocorrelation Plot for Transactions for store {store}')\n",
    "#     plt.grid()\n",
    "#     plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#EDA: Train Data, some insights\n",
    "train_data.set_index('id', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# EDA: Partial Autocorrelation of Sales Data\n",
    "\n",
    "# this will help us to determine the lag features to include in the model\n",
    "\n",
    "\n",
    "# for store in sorted(train_data['store_nbr'].unique()):\n",
    "#     statsmodels.graphics.tsaplots.plot_pacf(train_data[train_data[\"store_nbr\"] == store]['sales'], lags=50)\n",
    "#     plt.title(f'Partial Autocorrelation of Sales Data for Store {store}')\n",
    "#     plt.grid()\n",
    "#     plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Preprocessing: Join train_data with transaction_data on 'date' and 'store_nbr'\n",
    "\n",
    "train_data = train_data.merge(transaction_data[['date', 'store_nbr', 'transactions']], on=['date', 'store_nbr'], how='left')\n",
    "train_data['transactions'] = train_data['transactions'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Preprocessing: Join train_data with oil_data on 'date'\n",
    "\n",
    "train_data = train_data.merge(oil_data, on='date', how='left')\n",
    "train_data['dcoilwtico'] = train_data['dcoilwtico'].fillna(93)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Preprocessing: Join train_data with store_data on 'store_nbr'\n",
    "\n",
    "train_data = train_data.merge(store_data, on='store_nbr', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def preprocess_date_info(train_data, holiday_data):\n",
    "    #Preprocessing: Add column for date information (day, month, year, day of week, is_weekend, is_holiday)\n",
    "\n",
    "    train_data['day'] = train_data['date'].dt.day\n",
    "    train_data['month'] = train_data['date'].dt.month\n",
    "    train_data['season'] = (train_data['month'] % 12 + 3) // 3\n",
    "    # train_data['season'] = train_data['season'].astype(int)\n",
    "    train_data['year'] = train_data['date'].dt.year\n",
    "    # make the dtype of year as int8\n",
    "    # train_data['year'] = train_data['year'].astype(int)\n",
    "    train_data['day_of_week'] = train_data['date'].dt.dayofweek\n",
    "    train_data['is_weekend'] = train_data['day_of_week'].isin([5, 6]).astype(int)\n",
    "\n",
    "    # add the holiday info as a column, where on a holiday, it is encoded as 1, else 0\n",
    "    # National > Regional > Local in terms of priority\n",
    "\n",
    "    train_data['is_holiday'] = 0\n",
    "    for _, holiday in holiday_data.iterrows():\n",
    "        if holiday['locale'] == 'Local':\n",
    "            if holiday['type'] == 'Holiday' or holiday['type'] == 'Transferred Holiday':\n",
    "                train_data.loc[(train_data['date'] == holiday['date']) & (train_data['city'] == holiday['locale_name']), 'is_holiday'] = 1\n",
    "            elif holiday['type'] == 'Work Day':\n",
    "                train_data.loc[(train_data['date'] == holiday['date']) & (train_data['city'] == holiday['locale_name']), 'is_weekend'] = 0\n",
    "\n",
    "        elif holiday['locale'] == 'Regional':\n",
    "            if holiday['type'] == 'Holiday' or holiday['type'] == 'Transferred Holiday':\n",
    "                train_data.loc[(train_data['date'] == holiday['date']) & (train_data['state'] == holiday['locale_name']), 'is_holiday'] = 1\n",
    "            elif holiday['type'] == 'Work Day':\n",
    "                train_data.loc[(train_data['date'] == holiday['date']) & (train_data['state'] == holiday['locale_name']), 'is_weekend'] = 0\n",
    "\n",
    "        elif holiday['locale'] == 'National':\n",
    "            if holiday['type'] == 'Holiday' or holiday['type'] == 'Transferred Holiday':\n",
    "                train_data.loc[train_data['date'] == holiday['date'], 'is_holiday'] = 1\n",
    "            elif holiday['type'] == 'Work Day':\n",
    "                train_data.loc[train_data['date'] == holiday['date'], 'is_weekend'] = 0           \n",
    "\n",
    "\n",
    "    # add the payday info as a column, where on 15th and last day of the month, it is payday, encoded as 1, else 0\n",
    "\n",
    "    train_data['is_payday'] = 0\n",
    "    train_data.loc[(train_data['day'] == 15) | (train_data['day'] == train_data['date'].dt.days_in_month), 'is_payday'] = 1\n",
    "\n",
    "    # drop date\n",
    "    # train_data.drop('date', axis=1, inplace=True)\n",
    "\n",
    "    return train_data\n",
    "\n",
    "train_data = preprocess_date_info(train_data, holiday_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "fourier_m = CalendarFourier(freq='ME', order=4)\n",
    "dp = DeterministicProcess(\n",
    "    index=train_data.date,\n",
    "    constant=True,\n",
    "    order=0,  # Skip the linear trend if you already have time encodings\n",
    "    seasonal=False,  # Skip weekly seasonality since you already have day_of_week and is_weekend\n",
    "    additional_terms=[fourier_m],  # Only adding monthly Fourier terms for smooth seasonality\n",
    "    drop=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = dp.in_sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add the deterministic process to the train_data\n",
    "train_data.set_index('date', inplace=True)\n",
    "\n",
    "train_data = pd.concat([train_data, a], axis=1)\n",
    "\n",
    "train_data.reset_index(inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Feature Engineering: Create Lag Features for target variable 'sales'\n",
    "\n",
    "# for lag in range(1, 17):\n",
    "#     train_data[f'sales_lag_{lag}'] = train_data.groupby('store_nbr')['sales'].shift(lag)\n",
    "#     # fill the NaN values with 0\n",
    "#     train_data[f'sales_lag_{lag}'] = train_data[f'sales_lag_{lag}'].fillna(0)\n",
    "\n",
    "# # TODO: filter out the rows that correspond to the first 16 days of each store\n",
    "# train_data.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Feature Engineering: Encode Categorical Variables\n",
    "\n",
    "#categorical_cols = ['store_nbr', 'family', 'city', 'state', 'type', 'cluster', 'day', 'month', 'season', 'day_of_week']\n",
    "categorical_cols = ['family', 'city', 'state', 'type', 'cluster', 'day', 'month', 'season', 'day_of_week']\n",
    "\n",
    "train_data = pd.get_dummies(train_data, columns=categorical_cols, dtype=int)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2700799, 164) (300089, 164) (2700799,) (300089,)\n"
     ]
    }
   ],
   "source": [
    "# define the target variable and features\n",
    "# target values are all the columns starting with 'sales_lag_' of which there are 16\n",
    "\n",
    "# target = train_data.filter(regex='sales_lag_')\n",
    "# features = train_data.drop(['date', 'sales'] + target.columns.tolist(), axis=1)\n",
    "\n",
    "target = train_data[\"sales\"]\n",
    "features = train_data.drop([\"sales\", \"date\"], axis=1)\n",
    "\n",
    "# split the data into training and validation sets\n",
    "\n",
    "split_index = int(len(features) * 0.9)\n",
    "X_train, X_val = features.iloc[:split_index], features.iloc[split_index:]\n",
    "y_train, y_val = target.iloc[:split_index], target.iloc[split_index:]\n",
    "\n",
    "\n",
    "# verify the shapes of the training and validation sets\n",
    "print(X_train.shape, X_val.shape, y_train.shape, y_val.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>#sk-container-id-1 {\n",
       "  /* Definition of color scheme common for light and dark mode */\n",
       "  --sklearn-color-text: black;\n",
       "  --sklearn-color-line: gray;\n",
       "  /* Definition of color scheme for unfitted estimators */\n",
       "  --sklearn-color-unfitted-level-0: #fff5e6;\n",
       "  --sklearn-color-unfitted-level-1: #f6e4d2;\n",
       "  --sklearn-color-unfitted-level-2: #ffe0b3;\n",
       "  --sklearn-color-unfitted-level-3: chocolate;\n",
       "  /* Definition of color scheme for fitted estimators */\n",
       "  --sklearn-color-fitted-level-0: #f0f8ff;\n",
       "  --sklearn-color-fitted-level-1: #d4ebff;\n",
       "  --sklearn-color-fitted-level-2: #b3dbfd;\n",
       "  --sklearn-color-fitted-level-3: cornflowerblue;\n",
       "\n",
       "  /* Specific color for light theme */\n",
       "  --sklearn-color-text-on-default-background: var(--sg-text-color, var(--theme-code-foreground, var(--jp-content-font-color1, black)));\n",
       "  --sklearn-color-background: var(--sg-background-color, var(--theme-background, var(--jp-layout-color0, white)));\n",
       "  --sklearn-color-border-box: var(--sg-text-color, var(--theme-code-foreground, var(--jp-content-font-color1, black)));\n",
       "  --sklearn-color-icon: #696969;\n",
       "\n",
       "  @media (prefers-color-scheme: dark) {\n",
       "    /* Redefinition of color scheme for dark theme */\n",
       "    --sklearn-color-text-on-default-background: var(--sg-text-color, var(--theme-code-foreground, var(--jp-content-font-color1, white)));\n",
       "    --sklearn-color-background: var(--sg-background-color, var(--theme-background, var(--jp-layout-color0, #111)));\n",
       "    --sklearn-color-border-box: var(--sg-text-color, var(--theme-code-foreground, var(--jp-content-font-color1, white)));\n",
       "    --sklearn-color-icon: #878787;\n",
       "  }\n",
       "}\n",
       "\n",
       "#sk-container-id-1 {\n",
       "  color: var(--sklearn-color-text);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 pre {\n",
       "  padding: 0;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 input.sk-hidden--visually {\n",
       "  border: 0;\n",
       "  clip: rect(1px 1px 1px 1px);\n",
       "  clip: rect(1px, 1px, 1px, 1px);\n",
       "  height: 1px;\n",
       "  margin: -1px;\n",
       "  overflow: hidden;\n",
       "  padding: 0;\n",
       "  position: absolute;\n",
       "  width: 1px;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-dashed-wrapped {\n",
       "  border: 1px dashed var(--sklearn-color-line);\n",
       "  margin: 0 0.4em 0.5em 0.4em;\n",
       "  box-sizing: border-box;\n",
       "  padding-bottom: 0.4em;\n",
       "  background-color: var(--sklearn-color-background);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-container {\n",
       "  /* jupyter's `normalize.less` sets `[hidden] { display: none; }`\n",
       "     but bootstrap.min.css set `[hidden] { display: none !important; }`\n",
       "     so we also need the `!important` here to be able to override the\n",
       "     default hidden behavior on the sphinx rendered scikit-learn.org.\n",
       "     See: https://github.com/scikit-learn/scikit-learn/issues/21755 */\n",
       "  display: inline-block !important;\n",
       "  position: relative;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-text-repr-fallback {\n",
       "  display: none;\n",
       "}\n",
       "\n",
       "div.sk-parallel-item,\n",
       "div.sk-serial,\n",
       "div.sk-item {\n",
       "  /* draw centered vertical line to link estimators */\n",
       "  background-image: linear-gradient(var(--sklearn-color-text-on-default-background), var(--sklearn-color-text-on-default-background));\n",
       "  background-size: 2px 100%;\n",
       "  background-repeat: no-repeat;\n",
       "  background-position: center center;\n",
       "}\n",
       "\n",
       "/* Parallel-specific style estimator block */\n",
       "\n",
       "#sk-container-id-1 div.sk-parallel-item::after {\n",
       "  content: \"\";\n",
       "  width: 100%;\n",
       "  border-bottom: 2px solid var(--sklearn-color-text-on-default-background);\n",
       "  flex-grow: 1;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-parallel {\n",
       "  display: flex;\n",
       "  align-items: stretch;\n",
       "  justify-content: center;\n",
       "  background-color: var(--sklearn-color-background);\n",
       "  position: relative;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-parallel-item {\n",
       "  display: flex;\n",
       "  flex-direction: column;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-parallel-item:first-child::after {\n",
       "  align-self: flex-end;\n",
       "  width: 50%;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-parallel-item:last-child::after {\n",
       "  align-self: flex-start;\n",
       "  width: 50%;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-parallel-item:only-child::after {\n",
       "  width: 0;\n",
       "}\n",
       "\n",
       "/* Serial-specific style estimator block */\n",
       "\n",
       "#sk-container-id-1 div.sk-serial {\n",
       "  display: flex;\n",
       "  flex-direction: column;\n",
       "  align-items: center;\n",
       "  background-color: var(--sklearn-color-background);\n",
       "  padding-right: 1em;\n",
       "  padding-left: 1em;\n",
       "}\n",
       "\n",
       "\n",
       "/* Toggleable style: style used for estimator/Pipeline/ColumnTransformer box that is\n",
       "clickable and can be expanded/collapsed.\n",
       "- Pipeline and ColumnTransformer use this feature and define the default style\n",
       "- Estimators will overwrite some part of the style using the `sk-estimator` class\n",
       "*/\n",
       "\n",
       "/* Pipeline and ColumnTransformer style (default) */\n",
       "\n",
       "#sk-container-id-1 div.sk-toggleable {\n",
       "  /* Default theme specific background. It is overwritten whether we have a\n",
       "  specific estimator or a Pipeline/ColumnTransformer */\n",
       "  background-color: var(--sklearn-color-background);\n",
       "}\n",
       "\n",
       "/* Toggleable label */\n",
       "#sk-container-id-1 label.sk-toggleable__label {\n",
       "  cursor: pointer;\n",
       "  display: block;\n",
       "  width: 100%;\n",
       "  margin-bottom: 0;\n",
       "  padding: 0.5em;\n",
       "  box-sizing: border-box;\n",
       "  text-align: center;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 label.sk-toggleable__label-arrow:before {\n",
       "  /* Arrow on the left of the label */\n",
       "  content: \"▸\";\n",
       "  float: left;\n",
       "  margin-right: 0.25em;\n",
       "  color: var(--sklearn-color-icon);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 label.sk-toggleable__label-arrow:hover:before {\n",
       "  color: var(--sklearn-color-text);\n",
       "}\n",
       "\n",
       "/* Toggleable content - dropdown */\n",
       "\n",
       "#sk-container-id-1 div.sk-toggleable__content {\n",
       "  max-height: 0;\n",
       "  max-width: 0;\n",
       "  overflow: hidden;\n",
       "  text-align: left;\n",
       "  /* unfitted */\n",
       "  background-color: var(--sklearn-color-unfitted-level-0);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-toggleable__content.fitted {\n",
       "  /* fitted */\n",
       "  background-color: var(--sklearn-color-fitted-level-0);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-toggleable__content pre {\n",
       "  margin: 0.2em;\n",
       "  border-radius: 0.25em;\n",
       "  color: var(--sklearn-color-text);\n",
       "  /* unfitted */\n",
       "  background-color: var(--sklearn-color-unfitted-level-0);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-toggleable__content.fitted pre {\n",
       "  /* unfitted */\n",
       "  background-color: var(--sklearn-color-fitted-level-0);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 input.sk-toggleable__control:checked~div.sk-toggleable__content {\n",
       "  /* Expand drop-down */\n",
       "  max-height: 200px;\n",
       "  max-width: 100%;\n",
       "  overflow: auto;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 input.sk-toggleable__control:checked~label.sk-toggleable__label-arrow:before {\n",
       "  content: \"▾\";\n",
       "}\n",
       "\n",
       "/* Pipeline/ColumnTransformer-specific style */\n",
       "\n",
       "#sk-container-id-1 div.sk-label input.sk-toggleable__control:checked~label.sk-toggleable__label {\n",
       "  color: var(--sklearn-color-text);\n",
       "  background-color: var(--sklearn-color-unfitted-level-2);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-label.fitted input.sk-toggleable__control:checked~label.sk-toggleable__label {\n",
       "  background-color: var(--sklearn-color-fitted-level-2);\n",
       "}\n",
       "\n",
       "/* Estimator-specific style */\n",
       "\n",
       "/* Colorize estimator box */\n",
       "#sk-container-id-1 div.sk-estimator input.sk-toggleable__control:checked~label.sk-toggleable__label {\n",
       "  /* unfitted */\n",
       "  background-color: var(--sklearn-color-unfitted-level-2);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-estimator.fitted input.sk-toggleable__control:checked~label.sk-toggleable__label {\n",
       "  /* fitted */\n",
       "  background-color: var(--sklearn-color-fitted-level-2);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-label label.sk-toggleable__label,\n",
       "#sk-container-id-1 div.sk-label label {\n",
       "  /* The background is the default theme color */\n",
       "  color: var(--sklearn-color-text-on-default-background);\n",
       "}\n",
       "\n",
       "/* On hover, darken the color of the background */\n",
       "#sk-container-id-1 div.sk-label:hover label.sk-toggleable__label {\n",
       "  color: var(--sklearn-color-text);\n",
       "  background-color: var(--sklearn-color-unfitted-level-2);\n",
       "}\n",
       "\n",
       "/* Label box, darken color on hover, fitted */\n",
       "#sk-container-id-1 div.sk-label.fitted:hover label.sk-toggleable__label.fitted {\n",
       "  color: var(--sklearn-color-text);\n",
       "  background-color: var(--sklearn-color-fitted-level-2);\n",
       "}\n",
       "\n",
       "/* Estimator label */\n",
       "\n",
       "#sk-container-id-1 div.sk-label label {\n",
       "  font-family: monospace;\n",
       "  font-weight: bold;\n",
       "  display: inline-block;\n",
       "  line-height: 1.2em;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-label-container {\n",
       "  text-align: center;\n",
       "}\n",
       "\n",
       "/* Estimator-specific */\n",
       "#sk-container-id-1 div.sk-estimator {\n",
       "  font-family: monospace;\n",
       "  border: 1px dotted var(--sklearn-color-border-box);\n",
       "  border-radius: 0.25em;\n",
       "  box-sizing: border-box;\n",
       "  margin-bottom: 0.5em;\n",
       "  /* unfitted */\n",
       "  background-color: var(--sklearn-color-unfitted-level-0);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-estimator.fitted {\n",
       "  /* fitted */\n",
       "  background-color: var(--sklearn-color-fitted-level-0);\n",
       "}\n",
       "\n",
       "/* on hover */\n",
       "#sk-container-id-1 div.sk-estimator:hover {\n",
       "  /* unfitted */\n",
       "  background-color: var(--sklearn-color-unfitted-level-2);\n",
       "}\n",
       "\n",
       "#sk-container-id-1 div.sk-estimator.fitted:hover {\n",
       "  /* fitted */\n",
       "  background-color: var(--sklearn-color-fitted-level-2);\n",
       "}\n",
       "\n",
       "/* Specification for estimator info (e.g. \"i\" and \"?\") */\n",
       "\n",
       "/* Common style for \"i\" and \"?\" */\n",
       "\n",
       ".sk-estimator-doc-link,\n",
       "a:link.sk-estimator-doc-link,\n",
       "a:visited.sk-estimator-doc-link {\n",
       "  float: right;\n",
       "  font-size: smaller;\n",
       "  line-height: 1em;\n",
       "  font-family: monospace;\n",
       "  background-color: var(--sklearn-color-background);\n",
       "  border-radius: 1em;\n",
       "  height: 1em;\n",
       "  width: 1em;\n",
       "  text-decoration: none !important;\n",
       "  margin-left: 1ex;\n",
       "  /* unfitted */\n",
       "  border: var(--sklearn-color-unfitted-level-1) 1pt solid;\n",
       "  color: var(--sklearn-color-unfitted-level-1);\n",
       "}\n",
       "\n",
       ".sk-estimator-doc-link.fitted,\n",
       "a:link.sk-estimator-doc-link.fitted,\n",
       "a:visited.sk-estimator-doc-link.fitted {\n",
       "  /* fitted */\n",
       "  border: var(--sklearn-color-fitted-level-1) 1pt solid;\n",
       "  color: var(--sklearn-color-fitted-level-1);\n",
       "}\n",
       "\n",
       "/* On hover */\n",
       "div.sk-estimator:hover .sk-estimator-doc-link:hover,\n",
       ".sk-estimator-doc-link:hover,\n",
       "div.sk-label-container:hover .sk-estimator-doc-link:hover,\n",
       ".sk-estimator-doc-link:hover {\n",
       "  /* unfitted */\n",
       "  background-color: var(--sklearn-color-unfitted-level-3);\n",
       "  color: var(--sklearn-color-background);\n",
       "  text-decoration: none;\n",
       "}\n",
       "\n",
       "div.sk-estimator.fitted:hover .sk-estimator-doc-link.fitted:hover,\n",
       ".sk-estimator-doc-link.fitted:hover,\n",
       "div.sk-label-container:hover .sk-estimator-doc-link.fitted:hover,\n",
       ".sk-estimator-doc-link.fitted:hover {\n",
       "  /* fitted */\n",
       "  background-color: var(--sklearn-color-fitted-level-3);\n",
       "  color: var(--sklearn-color-background);\n",
       "  text-decoration: none;\n",
       "}\n",
       "\n",
       "/* Span, style for the box shown on hovering the info icon */\n",
       ".sk-estimator-doc-link span {\n",
       "  display: none;\n",
       "  z-index: 9999;\n",
       "  position: relative;\n",
       "  font-weight: normal;\n",
       "  right: .2ex;\n",
       "  padding: .5ex;\n",
       "  margin: .5ex;\n",
       "  width: min-content;\n",
       "  min-width: 20ex;\n",
       "  max-width: 50ex;\n",
       "  color: var(--sklearn-color-text);\n",
       "  box-shadow: 2pt 2pt 4pt #999;\n",
       "  /* unfitted */\n",
       "  background: var(--sklearn-color-unfitted-level-0);\n",
       "  border: .5pt solid var(--sklearn-color-unfitted-level-3);\n",
       "}\n",
       "\n",
       ".sk-estimator-doc-link.fitted span {\n",
       "  /* fitted */\n",
       "  background: var(--sklearn-color-fitted-level-0);\n",
       "  border: var(--sklearn-color-fitted-level-3);\n",
       "}\n",
       "\n",
       ".sk-estimator-doc-link:hover span {\n",
       "  display: block;\n",
       "}\n",
       "\n",
       "/* \"?\"-specific style due to the `<a>` HTML tag */\n",
       "\n",
       "#sk-container-id-1 a.estimator_doc_link {\n",
       "  float: right;\n",
       "  font-size: 1rem;\n",
       "  line-height: 1em;\n",
       "  font-family: monospace;\n",
       "  background-color: var(--sklearn-color-background);\n",
       "  border-radius: 1rem;\n",
       "  height: 1rem;\n",
       "  width: 1rem;\n",
       "  text-decoration: none;\n",
       "  /* unfitted */\n",
       "  color: var(--sklearn-color-unfitted-level-1);\n",
       "  border: var(--sklearn-color-unfitted-level-1) 1pt solid;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 a.estimator_doc_link.fitted {\n",
       "  /* fitted */\n",
       "  border: var(--sklearn-color-fitted-level-1) 1pt solid;\n",
       "  color: var(--sklearn-color-fitted-level-1);\n",
       "}\n",
       "\n",
       "/* On hover */\n",
       "#sk-container-id-1 a.estimator_doc_link:hover {\n",
       "  /* unfitted */\n",
       "  background-color: var(--sklearn-color-unfitted-level-3);\n",
       "  color: var(--sklearn-color-background);\n",
       "  text-decoration: none;\n",
       "}\n",
       "\n",
       "#sk-container-id-1 a.estimator_doc_link.fitted:hover {\n",
       "  /* fitted */\n",
       "  background-color: var(--sklearn-color-fitted-level-3);\n",
       "}\n",
       "</style><div id=\"sk-container-id-1\" class=\"sk-top-container\"><div class=\"sk-text-repr-fallback\"><pre>ElasticNet()</pre><b>In a Jupyter environment, please rerun this cell to show the HTML representation or trust the notebook. <br />On GitHub, the HTML representation is unable to render, please try loading this page with nbviewer.org.</b></div><div class=\"sk-container\" hidden><div class=\"sk-item\"><div class=\"sk-estimator fitted sk-toggleable\"><input class=\"sk-toggleable__control sk-hidden--visually\" id=\"sk-estimator-id-1\" type=\"checkbox\" checked><label for=\"sk-estimator-id-1\" class=\"sk-toggleable__label fitted sk-toggleable__label-arrow fitted\">&nbsp;&nbsp;ElasticNet<a class=\"sk-estimator-doc-link fitted\" rel=\"noreferrer\" target=\"_blank\" href=\"https://scikit-learn.org/1.5/modules/generated/sklearn.linear_model.ElasticNet.html\">?<span>Documentation for ElasticNet</span></a><span class=\"sk-estimator-doc-link fitted\">i<span>Fitted</span></span></label><div class=\"sk-toggleable__content fitted\"><pre>ElasticNet()</pre></div> </div></div></div></div>"
      ],
      "text/plain": [
       "ElasticNet()"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Training The Model: The Linear Regression Model\n",
    "\n",
    "lr = ElasticNet()\n",
    "lr.fit(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "y_train_pred_lr = lr.predict(X_train)\n",
    "y_val_pred_lr = lr.predict(X_val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "root_mean_squared_error_train_lr = root_mean_squared_error(y_train, y_train_pred_lr)\n",
    "root_mean_squared_error_val_lr = root_mean_squared_error(y_val, y_val_pred_lr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Training The Model: Residuals\n",
    "\n",
    "residuals = y_train - y_train_pred_lr\n",
    "\n",
    "# Train the XGBoost model on the residuals\n",
    "xgb_model = XGBRegressor(objective='reg:squarederror', n_estimators=100, learning_rate=0.1)\n",
    "xgb_model.fit(X_train, residuals)\n",
    "\n",
    "# Predict from the combined model on the validation set\n",
    "\n",
    "y_pred_lr_val = lr.predict(X_val)\n",
    "y_pred_xgb_val = xgb_model.predict(X_val)\n",
    "\n",
    "y_pred_val = y_pred_lr_val + y_pred_xgb_val\n",
    "\n",
    "# # replace negative values with 0\n",
    "# y_pred_val = np.where(y_pred_val < 0, 0, y_pred_val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "330.82406028850363"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate the RMSLE on the validation set\n",
    "\n",
    "root_mean_squared_error(y_val, y_pred_val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 28512 entries, 0 to 28511\n",
      "Data columns (total 5 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   id           28512 non-null  int64 \n",
      " 1   date         28512 non-null  object\n",
      " 2   store_nbr    28512 non-null  int64 \n",
      " 3   family       28512 non-null  object\n",
      " 4   onpromotion  28512 non-null  int64 \n",
      "dtypes: int64(3), object(2)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# Make predictions on the test set, and save the results to a CSV file\n",
    "\n",
    "# read the test data\n",
    "test_data = pd.read_csv('data/test.csv')\n",
    "\n",
    "test_data.info()\n",
    "\n",
    "# preprocess the test data\n",
    "\n",
    "test_data['date'] = pd.to_datetime(test_data['date'])\n",
    "test_data['onpromotion'] = test_data['onpromotion'].astype(int)\n",
    "\n",
    "# create a copy of transaction_data\n",
    "transaction_data_test = transaction_data.copy()\n",
    "transaction_data_test['date_one_year_later'] = transaction_data_test['date'] + pd.DateOffset(years=1)\n",
    "test_data = test_data.merge(transaction_data_test, left_on=['date', \"store_nbr\"], right_on=['date_one_year_later', \"store_nbr\"], how='left')\n",
    "test_data.drop(columns=['date_y','date_one_year_later'], inplace=True)\n",
    "test_data.rename(columns={'date_x': 'date'}, inplace=True)\n",
    "\n",
    "# join the test data with the oil data, store data, and holiday data\n",
    "\n",
    "test_data = test_data.merge(oil_data, on='date', how='left')\n",
    "# test_data['dcoilwtico'] = test_data['dcoilwtico'].fillna(93)\n",
    "test_data = test_data.merge(store_data, on='store_nbr', how='left')\n",
    "test_data = preprocess_date_info(test_data, holiday_data)\n",
    "\n",
    "#set id column as index, replacing the default index\n",
    "test_data.set_index('id', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate out-of-sample forecasts for the next 16 steps\n",
    "forecast_steps = 16\n",
    "forecast_dates = pd.date_range(start=train_data['date'].max() + pd.Timedelta(days=1), periods=forecast_steps, freq='D')\n",
    "\n",
    "forecast_values = dp.out_of_sample(steps=forecast_steps, forecast_index=forecast_dates)\n",
    "forecast_values[\"date\"] =  forecast_dates\n",
    "\n",
    "test_data = test_data.merge(forecast_values, on='date', how='left')\n",
    "\n",
    "# encode the categorical variables\n",
    "test_data = pd.get_dummies(test_data, columns=categorical_cols, dtype=int)\n",
    "test_data = test_data.drop('date', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "164 ['store_nbr', 'onpromotion', 'transactions', 'dcoilwtico', 'year', 'is_weekend', 'is_holiday', 'is_payday', 'const', 'sin(1,freq=ME)', 'cos(1,freq=ME)', 'sin(2,freq=ME)', 'cos(2,freq=ME)', 'sin(3,freq=ME)', 'cos(3,freq=ME)', 'sin(4,freq=ME)', 'cos(4,freq=ME)', 'family_AUTOMOTIVE', 'family_BABY CARE', 'family_BEAUTY', 'family_BEVERAGES', 'family_BOOKS', 'family_BREAD/BAKERY', 'family_CELEBRATION', 'family_CLEANING', 'family_DAIRY', 'family_DELI', 'family_EGGS', 'family_FROZEN FOODS', 'family_GROCERY I', 'family_GROCERY II', 'family_HARDWARE', 'family_HOME AND KITCHEN I', 'family_HOME AND KITCHEN II', 'family_HOME APPLIANCES', 'family_HOME CARE', 'family_LADIESWEAR', 'family_LAWN AND GARDEN', 'family_LINGERIE', 'family_LIQUOR,WINE,BEER', 'family_MAGAZINES', 'family_MEATS', 'family_PERSONAL CARE', 'family_PET SUPPLIES', 'family_PLAYERS AND ELECTRONICS', 'family_POULTRY', 'family_PREPARED FOODS', 'family_PRODUCE', 'family_SCHOOL AND OFFICE SUPPLIES', 'family_SEAFOOD', 'city_Ambato', 'city_Babahoyo', 'city_Cayambe', 'city_Cuenca', 'city_Daule', 'city_El Carmen', 'city_Esmeraldas', 'city_Guaranda', 'city_Guayaquil', 'city_Ibarra', 'city_Latacunga', 'city_Libertad', 'city_Loja', 'city_Machala', 'city_Manta', 'city_Playas', 'city_Puyo', 'city_Quevedo', 'city_Quito', 'city_Riobamba', 'city_Salinas', 'city_Santo Domingo', 'state_Azuay', 'state_Bolivar', 'state_Chimborazo', 'state_Cotopaxi', 'state_El Oro', 'state_Esmeraldas', 'state_Guayas', 'state_Imbabura', 'state_Loja', 'state_Los Rios', 'state_Manabi', 'state_Pastaza', 'state_Pichincha', 'state_Santa Elena', 'state_Santo Domingo de los Tsachilas', 'state_Tungurahua', 'type_A', 'type_B', 'type_C', 'type_D', 'type_E', 'cluster_1', 'cluster_2', 'cluster_3', 'cluster_4', 'cluster_5', 'cluster_6', 'cluster_7', 'cluster_8', 'cluster_9', 'cluster_10', 'cluster_11', 'cluster_12', 'cluster_13', 'cluster_14', 'cluster_15', 'cluster_16', 'cluster_17', 'day_1', 'day_2', 'day_3', 'day_4', 'day_5', 'day_6', 'day_7', 'day_8', 'day_9', 'day_10', 'day_11', 'day_12', 'day_13', 'day_14', 'day_15', 'day_16', 'day_17', 'day_18', 'day_19', 'day_20', 'day_21', 'day_22', 'day_23', 'day_24', 'day_25', 'day_26', 'day_27', 'day_28', 'day_29', 'day_30', 'day_31', 'month_1', 'month_2', 'month_3', 'month_4', 'month_5', 'month_6', 'month_7', 'month_8', 'month_9', 'month_10', 'month_11', 'month_12', 'season_1', 'season_2', 'season_3', 'season_4', 'day_of_week_0', 'day_of_week_1', 'day_of_week_2', 'day_of_week_3', 'day_of_week_4', 'day_of_week_5', 'day_of_week_6']\n"
     ]
    }
   ],
   "source": [
    "true_col=[]\n",
    "dummy_col= []\n",
    "\n",
    "for col in X_val.columns:\n",
    "    true_col.append(col)\n",
    "    dummy_col.append(col)\n",
    "\n",
    "print(len(dummy_col),dummy_col)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "135 ['store_nbr', 'onpromotion', 'transactions', 'dcoilwtico', 'year', 'is_weekend', 'is_holiday', 'is_payday', 'const', 'sin(1,freq=ME)', 'cos(1,freq=ME)', 'sin(2,freq=ME)', 'cos(2,freq=ME)', 'sin(3,freq=ME)', 'cos(3,freq=ME)', 'sin(4,freq=ME)', 'cos(4,freq=ME)', 'family_AUTOMOTIVE', 'family_BABY CARE', 'family_BEAUTY', 'family_BEVERAGES', 'family_BOOKS', 'family_BREAD/BAKERY', 'family_CELEBRATION', 'family_CLEANING', 'family_DAIRY', 'family_DELI', 'family_EGGS', 'family_FROZEN FOODS', 'family_GROCERY I', 'family_GROCERY II', 'family_HARDWARE', 'family_HOME AND KITCHEN I', 'family_HOME AND KITCHEN II', 'family_HOME APPLIANCES', 'family_HOME CARE', 'family_LADIESWEAR', 'family_LAWN AND GARDEN', 'family_LINGERIE', 'family_LIQUOR,WINE,BEER', 'family_MAGAZINES', 'family_MEATS', 'family_PERSONAL CARE', 'family_PET SUPPLIES', 'family_PLAYERS AND ELECTRONICS', 'family_POULTRY', 'family_PREPARED FOODS', 'family_PRODUCE', 'family_SCHOOL AND OFFICE SUPPLIES', 'family_SEAFOOD', 'city_Ambato', 'city_Babahoyo', 'city_Cayambe', 'city_Cuenca', 'city_Daule', 'city_El Carmen', 'city_Esmeraldas', 'city_Guaranda', 'city_Guayaquil', 'city_Ibarra', 'city_Latacunga', 'city_Libertad', 'city_Loja', 'city_Machala', 'city_Manta', 'city_Playas', 'city_Puyo', 'city_Quevedo', 'city_Quito', 'city_Riobamba', 'city_Salinas', 'city_Santo Domingo', 'state_Azuay', 'state_Bolivar', 'state_Chimborazo', 'state_Cotopaxi', 'state_El Oro', 'state_Esmeraldas', 'state_Guayas', 'state_Imbabura', 'state_Loja', 'state_Los Rios', 'state_Manabi', 'state_Pastaza', 'state_Pichincha', 'state_Santa Elena', 'state_Santo Domingo de los Tsachilas', 'state_Tungurahua', 'type_A', 'type_B', 'type_C', 'type_D', 'type_E', 'cluster_1', 'cluster_2', 'cluster_3', 'cluster_4', 'cluster_5', 'cluster_6', 'cluster_7', 'cluster_8', 'cluster_9', 'cluster_10', 'cluster_11', 'cluster_12', 'cluster_13', 'cluster_14', 'cluster_15', 'cluster_16', 'cluster_17', 'day_16', 'day_17', 'day_18', 'day_19', 'day_20', 'day_21', 'day_22', 'day_23', 'day_24', 'day_25', 'day_26', 'day_27', 'day_28', 'day_29', 'day_30', 'day_31', 'month_8', 'season_3', 'day_of_week_0', 'day_of_week_1', 'day_of_week_2', 'day_of_week_3', 'day_of_week_4', 'day_of_week_5', 'day_of_week_6']\n"
     ]
    }
   ],
   "source": [
    "dummy_col_test = []\n",
    "for col in test_data.columns:\n",
    "    dummy_col_test.append(col)\n",
    "\n",
    "print(len(dummy_col_test), dummy_col_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove the intersection of columns between dummy_col and dummy_col_test from both lists\n",
    "\n",
    "common_cols = list(set(dummy_col).intersection(dummy_col_test))\n",
    "\n",
    "for col in common_cols:\n",
    "    dummy_col.remove(col)\n",
    "    dummy_col_test.remove(col)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "29 ['day_1', 'day_2', 'day_3', 'day_4', 'day_5', 'day_6', 'day_7', 'day_8', 'day_9', 'day_10', 'day_11', 'day_12', 'day_13', 'day_14', 'day_15', 'month_1', 'month_2', 'month_3', 'month_4', 'month_5', 'month_6', 'month_7', 'month_9', 'month_10', 'month_11', 'month_12', 'season_1', 'season_2', 'season_4']\n",
      "0 []\n"
     ]
    }
   ],
   "source": [
    "print(len(dummy_col),dummy_col)\n",
    "print(len(dummy_col_test), dummy_col_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add the missing columns to the test data, filled with 0\n",
    "for col in dummy_col:\n",
    "    test_data[col] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "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>store_nbr</th>\n",
       "      <th>onpromotion</th>\n",
       "      <th>transactions</th>\n",
       "      <th>dcoilwtico</th>\n",
       "      <th>year</th>\n",
       "      <th>is_weekend</th>\n",
       "      <th>is_holiday</th>\n",
       "      <th>is_payday</th>\n",
       "      <th>const</th>\n",
       "      <th>sin(1,freq=ME)</th>\n",
       "      <th>...</th>\n",
       "      <th>month_5</th>\n",
       "      <th>month_6</th>\n",
       "      <th>month_7</th>\n",
       "      <th>month_9</th>\n",
       "      <th>month_10</th>\n",
       "      <th>month_11</th>\n",
       "      <th>month_12</th>\n",
       "      <th>season_1</th>\n",
       "      <th>season_2</th>\n",
       "      <th>season_4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28507</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>1954.0</td>\n",
       "      <td>47.26</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-0.201299</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28508</th>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>1954.0</td>\n",
       "      <td>47.26</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-0.201299</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28509</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>1954.0</td>\n",
       "      <td>47.26</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-0.201299</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28510</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>1954.0</td>\n",
       "      <td>47.26</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-0.201299</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28511</th>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>1954.0</td>\n",
       "      <td>47.26</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-0.201299</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 164 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       store_nbr  onpromotion  transactions  dcoilwtico  year  is_weekend  \\\n",
       "28507          9            1        1954.0       47.26  2017           0   \n",
       "28508          9            0        1954.0       47.26  2017           0   \n",
       "28509          9            1        1954.0       47.26  2017           0   \n",
       "28510          9            9        1954.0       47.26  2017           0   \n",
       "28511          9            0        1954.0       47.26  2017           0   \n",
       "\n",
       "       is_holiday  is_payday  const  sin(1,freq=ME)  ...  month_5  month_6  \\\n",
       "28507           0          1    1.0       -0.201299  ...        0        0   \n",
       "28508           0          1    1.0       -0.201299  ...        0        0   \n",
       "28509           0          1    1.0       -0.201299  ...        0        0   \n",
       "28510           0          1    1.0       -0.201299  ...        0        0   \n",
       "28511           0          1    1.0       -0.201299  ...        0        0   \n",
       "\n",
       "       month_7  month_9  month_10  month_11  month_12  season_1  season_2  \\\n",
       "28507        0        0         0         0         0         0         0   \n",
       "28508        0        0         0         0         0         0         0   \n",
       "28509        0        0         0         0         0         0         0   \n",
       "28510        0        0         0         0         0         0         0   \n",
       "28511        0        0         0         0         0         0         0   \n",
       "\n",
       "       season_4  \n",
       "28507         0  \n",
       "28508         0  \n",
       "28509         0  \n",
       "28510         0  \n",
       "28511         0  \n",
       "\n",
       "[5 rows x 164 columns]"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reorder the columns of test_data to match the training data\n",
    "test_data = test_data[true_col]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data[\"dcoilwtico\"] = test_data[\"dcoilwtico\"].interpolate()\n",
    "test_data[\"transactions\"] = test_data[\"transactions\"].interpolate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "# predict the sales for the test data\n",
    "y_test_lr_pred = lr.predict(test_data)\n",
    "y_test_xgb_pred = xgb_model.predict(test_data)\n",
    "\n",
    "y_test_pred = y_test_lr_pred + y_test_xgb_pred\n",
    "\n",
    "# replace the sales column of 'sample_submission.csv' with the predicted values, and save the results to the same CSV file\n",
    "\n",
    "sample_submission = pd.read_csv('data/sample_submission.csv')\n",
    "sample_submission['sales'] = y_test_pred\n",
    "sample_submission.to_csv('data/sample_submission.csv', index=False)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}