M5_Forecasting_Walmart.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# M5 Forcasting Accuracy Walmart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id = 'top' ><img src='https://media.giphy.com/media/YVfSBbePrWdGw/giphy.gif'></a>\n",
    "<div class='list-group' id='list-tab' role='tablist'>\n",
    "    <h3 class='list-group-item list-group-item-action active' data-toggle='list' role='tab' aria-controls='home'>Table of Contents</h3>\n",
    "  <a class=\"list-group-item list-group-item-action\" data-toggle=\"list\" href=\"#data\" role=\"tab\" aria-controls=\"profile\">Data Objective<span class=\"badge badge-primary badge-pill\">1</span></a>\n",
    "  <a class=\"list-group-item list-group-item-action\" data-toggle=\"list\" href=\"#fetch\" role=\"tab\" aria-controls=\"profile\">Fetch the data<span class=\"badge badge-primary badge-pill\">2</span></a>\n",
    "  <a class=\"list-group-item list-group-item-action\"  data-toggle=\"list\" href=\"#melt\" role=\"tab\" aria-controls=\"settings\">Melting the data<span class=\"badge badge-primary badge-pill\">3</span></a>\n",
    "  <a class=\"list-group-item list-group-item-action\" data-toggle=\"list\" href=\"#eda\" role=\"tab\" aria-controls=\"settings\">Exploratory Data Analysis<span class=\"badge badge-primary badge-pill\">4</span></a> \n",
    "  <a class=\"list-group-item list-group-item-action\" data-toggle=\"list\" href=\"#fe\" role=\"tab\" aria-controls=\"settings\">Feature Engineering<span class=\"badge badge-primary badge-pill\">5</span></a>\n",
    "  <a class=\"list-group-item list-group-item-action\" data-toggle=\"list\" href=\"#pred\" role=\"tab\" aria-controls=\"settings\">Modelling and Prediction<span class=\"badge badge-primary badge-pill\">6</span></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <a id='data'>1. Data Objective</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-09T05:27:30.110883Z",
     "start_time": "2020-07-09T05:27:30.094876Z"
    }
   },
   "source": [
    "The objective of the M5 forecasting competition is to advance the theory and practice of forecasting by identifying the method(s) that provide the most accurate point forecasts for each of the 42,840 time series of the competition. I addition, to elicit information to estimate the uncertainty distribution of the realized values of these series as precisely as possible. \n",
    "\n",
    "To that end, the participants of M5 are asked to provide 28 days ahead point forecasts (PFs) for all the series of the competition, as well as the corresponding median and 50%, 67%, 95%, and 99% prediction intervals (PIs).\n",
    "The M5 differs from the previous four ones in five important ways, some of them suggested by the discussants of the M4  competition, as follows:\n",
    "\n",
    "- First, it uses grouped unit sales data, starting at the product-store level and being aggregated to that of product departments, product categories, stores, and three geographical areas: the States of California (CA), Texas (TX), and Wisconsin (WI).\n",
    "\n",
    "- Second, besides the time series data, it includes explanatory variables such as sell prices, promotions, days of the week, and special events (e.g. Super Bowl, Valentine’s Day, and Orthodox Easter) that typically affect unit sales and could improve forecasting accuracy.\n",
    "\n",
    "- Third, in addition to point forecasts, it assesses the distribution of uncertainty, as the participants are asked to provide information on nine indicative quantiles.\n",
    "\n",
    "- Fourth, instead of having a single competition to estimate both the point forecasts and the uncertainty distribution, there will be two parallel tracks using the same dataset, the first requiring 28 days ahead point forecasts and the second 28 days ahead probabilistic forecasts for the median and four prediction intervals (50%, 67%, 95%, and 99%).\n",
    "\n",
    "- Fifth, for the first time it focuses on series that display intermittency, i.e., sporadic demand including zeros.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2 style=\"background-color:DodgerBlue; color:white\" >1.1. Dates and hosting</h2>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The M5 will start on March 2, 2020 and finish on June 30 of the same year. The competition will be run using the Kaggle platform. Thus, we expect many submissions from all types of forecasters including data scientists, statisticians, and practitioners, expanding the field of forecasting and eventually integrating its various approaches for improving accuracy and uncertainty estimation.\n",
    "\n",
    "The competition will be divided into two separate Kaggle competitions, using the same dataset, with the first (M5 Forecasting Competition – Accuracy) requiring 28 days ahead point forecasts and the second (M5 Forecasting Competition – Uncertainty) 28 days ahead probabilistic forecasts for the corresponding median and four prediction intervals (50%, 67%, 95%, and 99%).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2 style=\"background-color:DodgerBlue; color:white\" >1.2. The dataset</h2>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The M5 dataset, generously made available by Walmart, involves the unit sales of various products sold in the USA, organized in the form of grouped time series. More specifically, the dataset involves the unit sales of 3,049 products, classified in 3 product categories (Hobbies, Foods, and Household) and 7 product departments, in which the above-mentioned categories are disaggregated.  The products are sold across ten stores, located in three States (CA, TX, and WI). In this respect, the bottom-level of the hierarchy, i.e., product-store unit sales can be mapped across either product categories or geographical regions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The historical data range from 2011-01-29 to 2016-06-19. Thus, the products have a (maximum) selling history of 1,941  days / 5.4 years (test data of h=28 days not included). \n",
    "\n",
    "### The M5 dataset consists of the following four (4) files:\n",
    "\n",
    "### File 1: “calendar.csv” \n",
    "Contains information about the dates the products are sold.\n",
    "- date: The date in a “y-m-d” format.\n",
    "- wm_yr_wk: The id of the week the date belongs to.\n",
    "- weekday: The type of the day (Saturday, Sunday, …, Friday).\n",
    "- wday: The id of the weekday, starting from Saturday.\n",
    "- month: The month of the date.\n",
    "- year: The year of the date.\n",
    "- event_name_1: If the date includes an event, the name of this event.\n",
    "- event_type_1: If the date includes an event, the type of this event.\n",
    "- event_name_2: If the date includes a second event, the name of this event.\n",
    "- event_type_2: If the date includes a second event, the type of this event.\n",
    "- snap_CA, snap_TX, and snap_WI: A binary variable (0 or 1) indicating whether the stores of CA, TX or WI allow SNAP  purchases on the examined date. 1 indicates that SNAP purchases are allowed.\n",
    "\n",
    "### File 2: “sell_prices.csv”\n",
    "Contains information about the price of the products sold per store and date.\n",
    "- store_id: The id of the store where the product is sold. \n",
    "- item_id: The id of the product.\n",
    "- wm_yr_wk: The id of the week.\n",
    "- sell_price: The price of the product for the given week/store. The price is provided per week (average across seven days). If not available, this means that the product was not sold during the examined week. Note that although prices are constant at weekly basis, they may change through time (both training and test set).  \n",
    "\n",
    "### File 3: “sales_train_valid.csv” \n",
    "Contains the historical daily unit sales data per product and store.\n",
    "- item_id: The id of the product.\n",
    "- dept_id: The id of the department the product belongs to.\n",
    "- cat_id: The id of the category the product belongs to.\n",
    "- store_id: The id of the store where the product is sold.\n",
    "- state_id: The State where the store is located.\n",
    "- d_1, d_2, …, d_i, … d_1941: The number of units sold at day i, starting from 2011-01-29.\n",
    "\n",
    "### File 4: “sales_train_evaluation.csv” \n",
    "Contains the historical daily unit sales data per product and store.\n",
    "- item_id: The id of the product.\n",
    "- dept_id: The id of the department the product belongs to.\n",
    "- cat_id: The id of the category the product belongs to.\n",
    "- store_id: The id of the store where the product is sold.\n",
    "- state_id: The State where the store is located.\n",
    "- d_1, d_2, …, d_i, … d_1969: The number of units sold at day i, starting from 2011-01-29. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <a id='fetch'>2. Fetch the data</a>\n",
    "<h2 style=\"background-color:DodgerBlue; color:white\" >2.1. Import libraries</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:04:22.536953Z",
     "start_time": "2021-04-22T05:04:21.137695Z"
    }
   },
   "outputs": [],
   "source": [
    "import os, gc\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "from sklearn.preprocessing import LabelEncoder, StandardScaler\n",
    "from sklearn.metrics import mean_squared_error\n",
    "\n",
    "import lightgbm as lgb\n",
    "\n",
    "import plotly_express as px\n",
    "import plotly.graph_objects as go\n",
    "from plotly.subplots import make_subplots\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.max_rows', 500)\n",
    "for dirname, _, filenames in os.walk('dataset/'):\n",
    "    for filename in filenames:\n",
    "        print(os.path.join(dirname, filename))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-09T05:38:50.268545Z",
     "start_time": "2020-07-09T05:38:50.264556Z"
    }
   },
   "source": [
    "<h2 style=\"background-color:DodgerBlue; color:white\" >2.2. Setting working directory and loading datasets</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:04:22.551912Z",
     "start_time": "2021-04-22T05:04:22.537950Z"
    }
   },
   "outputs": [],
   "source": [
    "# working path\n",
    "path = 'dataset/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:04:27.211475Z",
     "start_time": "2021-04-22T05:04:22.552910Z"
    }
   },
   "outputs": [],
   "source": [
    "# loading datasets\n",
    "calendar = pd.read_csv(path + 'calendar.csv')\n",
    "sellPrice = pd.read_csv(path + 'sell_prices.csv')\n",
    "sales = pd.read_csv(path + 'sales_train_evaluation.csv')\n",
    "sampleSubmission = pd.read_csv(path + 'sample_submission.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:04:27.226406Z",
     "start_time": "2021-04-22T05:04:27.212443Z"
    }
   },
   "outputs": [],
   "source": [
    "print('Calendar dataset has {} rows and {} columns'.format(calendar.shape[0], calendar.shape[1]))\n",
    "print('Sell Price dataset has {} rows and {} columns'.format(sellPrice.shape[0], sellPrice.shape[1]))\n",
    "print('Sales dataset has {} rows and {} columns'.format(sales.shape[0], sales.shape[1]))\n",
    "print('Sample Submission dataset has {} rows and {} columns'.format(sampleSubmission.shape[0], sampleSubmission.shape[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:04:27.242363Z",
     "start_time": "2021-04-22T05:04:27.227404Z"
    }
   },
   "outputs": [],
   "source": [
    "# Function using to get number of rows and data types\n",
    "def infoData(df):\n",
    "    columns = []\n",
    "    values = []\n",
    "    for feature in df.columns:\n",
    "        columns.append(feature)\n",
    "        values.append(len(df[feature].unique()))\n",
    "    percent_missing = df.isnull().sum() * 100 / len(df)\n",
    "    data = {'Unique_Value': values,'Percent_Missing': percent_missing, 'Data_Types': df.dtypes.values}\n",
    "    return pd.DataFrame(data=data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:04:27.258321Z",
     "start_time": "2021-04-22T05:04:27.243360Z"
    }
   },
   "outputs": [],
   "source": [
    "# Downcast in order to save memory\n",
    "def reduce_mem_usage(df, file_name=\"\", verbose=True):\n",
    "    start_mem = df.memory_usage().sum() / 1024**2\n",
    "    print(f\"Size of input {file_name} file:\\t{round(start_mem, 2)}MB\")\n",
    "    cols = df.dtypes.index.tolist()\n",
    "    types = df.dtypes.values.tolist()\n",
    "    for i,t in enumerate(types):\n",
    "        if 'int' in str(t):\n",
    "            if df[cols[i]].min() > np.iinfo(np.int8).min and df[cols[i]].max() < np.iinfo(np.int8).max:\n",
    "                df[cols[i]] = df[cols[i]].astype(np.int8)\n",
    "            elif df[cols[i]].min() > np.iinfo(np.int16).min and df[cols[i]].max() < np.iinfo(np.int16).max:\n",
    "                df[cols[i]] = df[cols[i]].astype(np.int16)\n",
    "            elif df[cols[i]].min() > np.iinfo(np.int32).min and df[cols[i]].max() < np.iinfo(np.int32).max:\n",
    "                df[cols[i]] = df[cols[i]].astype(np.int32)\n",
    "            else:\n",
    "                df[cols[i]] = df[cols[i]].astype(np.int64)\n",
    "        elif 'float' in str(t):\n",
    "            if df[cols[i]].min() > np.finfo(np.float16).min and df[cols[i]].max() < np.finfo(np.float16).max:\n",
    "                df[cols[i]] = df[cols[i]].astype(np.float16)\n",
    "            elif df[cols[i]].min() > np.finfo(np.float32).min and df[cols[i]].max() < np.finfo(np.float32).max:\n",
    "                df[cols[i]] = df[cols[i]].astype(np.float32)\n",
    "            else:\n",
    "                df[cols[i]] = df[cols[i]].astype(np.float64)\n",
    "        elif t == np.object:\n",
    "            if cols[i] == 'date':\n",
    "                df[cols[i]] = pd.to_datetime(df[cols[i]], format='%Y-%m-%d')\n",
    "    end_mem = df.memory_usage().sum() / 1024**2\n",
    "    if verbose: print('Mem. usage decreased to {:5.2f} MB ({:.1f}% reduction)\\n'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:04:27.289238Z",
     "start_time": "2021-04-22T05:04:27.259318Z"
    }
   },
   "outputs": [],
   "source": [
    "# Getting information of calendar dataset\n",
    "infoData(calendar)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:04:28.239696Z",
     "start_time": "2021-04-22T05:04:27.290236Z"
    }
   },
   "outputs": [],
   "source": [
    "# Getting information of sellPrice dataset\n",
    "infoData(sellPrice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:04:28.795239Z",
     "start_time": "2021-04-22T05:04:28.241720Z"
    }
   },
   "outputs": [],
   "source": [
    "# Getting information of sales dataset\n",
    "infoData(sales)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:06:50.324069Z",
     "start_time": "2021-04-22T05:04:28.796236Z"
    }
   },
   "outputs": [],
   "source": [
    "# reducing memory of datasets\n",
    "calendar  = reduce_mem_usage(calendar, file_name=\"calendar.csv\")\n",
    "sellPrice = reduce_mem_usage(sellPrice, file_name=\"sell_prices.csv\")\n",
    "sales     = reduce_mem_usage(sales, file_name=\"sales_train_evaluation.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:06:50.339999Z",
     "start_time": "2021-04-22T05:06:50.325038Z"
    }
   },
   "outputs": [],
   "source": [
    "calendar.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:06:50.355956Z",
     "start_time": "2021-04-22T05:06:50.340996Z"
    }
   },
   "outputs": [],
   "source": [
    "sellPrice.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:06:50.577364Z",
     "start_time": "2021-04-22T05:06:50.356954Z"
    }
   },
   "outputs": [],
   "source": [
    "sales.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:06:50.593321Z",
     "start_time": "2021-04-22T05:06:50.578361Z"
    }
   },
   "outputs": [],
   "source": [
    "sampleSubmission.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Validation data is now from 1914 to 1941. And test data is from 1942 to 1969"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:06:50.702030Z",
     "start_time": "2021-04-22T05:06:50.594319Z"
    }
   },
   "outputs": [],
   "source": [
    "# adding more days to use as test datasets\n",
    "for d in range(1942, 1970):\n",
    "    col = 'd_' + str(d)\n",
    "    sales[col] = 0\n",
    "    sales[col] = sales[col].astype(np.int16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:06:50.924488Z",
     "start_time": "2021-04-22T05:06:50.703027Z"
    }
   },
   "outputs": [],
   "source": [
    "sales.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:06:51.018184Z",
     "start_time": "2021-04-22T05:06:50.925433Z"
    }
   },
   "outputs": [],
   "source": [
    "print(\"Number of unique elements in 'item_id':   \", len(np.unique(sales['item_id'])), \"\\n\")\n",
    "\n",
    "print(\"Number of unique elements in 'dept_id':   \", len(np.unique(sales['dept_id'])))\n",
    "print(np.unique(sales['dept_id'], return_counts=True)[0], \"\\n\")\n",
    "\n",
    "print(\"Number of unique elements in 'cat_id':    \", len(np.unique(sales['cat_id'])))\n",
    "print(np.unique(sales['cat_id'], return_counts=True)[0], \"\\n\")\n",
    "\n",
    "print(\"Number of unique elements in 'store_id':  \", len(np.unique(sales['store_id'])))\n",
    "print(np.unique(sales['store_id'], return_counts=True)[0], \"\\n\")\n",
    "\n",
    "print(\"Number of unique elements in 'state_id':  \", len(np.unique(sales['state_id'])))\n",
    "print(np.unique(sales['state_id'], return_counts=True)[0], \"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <a id='melt'>3. Melting the data</a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:07:37.202018Z",
     "start_time": "2021-04-22T05:06:51.019181Z"
    }
   },
   "outputs": [],
   "source": [
    "identifierVariables = ['id', 'item_id', 'dept_id', 'cat_id', 'store_id', 'state_id']\n",
    "variableName = 'd'\n",
    "measuredVariables = 'sales'\n",
    "\n",
    "data = pd.melt(sales,\n",
    "               id_vars = identifierVariables,\n",
    "               var_name = variableName,\n",
    "               value_name = measuredVariables).drop_duplicates()\n",
    "\n",
    "del identifierVariables, variableName, measuredVariables\n",
    "gc.collect()\n",
    "print('Dataset has {} rows and {} columns'.format(data.shape[0], data.shape[1]))\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T05:08:30.804261Z",
     "start_time": "2021-04-22T05:07:37.204012Z"
    }
   },
   "outputs": [],
   "source": [
    "data = data.merge(calendar, how='left', on='d')\n",
    "data = data.merge(sellPrice, on=['store_id', 'item_id', 'wm_yr_wk'], how='left')\n",
    "\n",
    "del calendar, sellPrice\n",
    "gc.collect()\n",
    "print('Dataset has {} rows and {} columns'.format(data.shape[0], data.shape[1]))\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <a id='eda'>4. Exploratory Data Analysis</a>\n",
    "<h2 style=\"background-color:DodgerBlue; color:white\" >4.1 The Dataset</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:59:25.225402Z",
     "start_time": "2021-04-22T02:59:23.739532Z"
    }
   },
   "outputs": [],
   "source": [
    "group = sales.groupby(['state_id','store_id','cat_id','dept_id'],as_index=False)['item_id'].count().dropna()\n",
    "group['USA'] = 'United States of America'\n",
    "group.rename(columns={'state_id':'State','store_id':'Store','cat_id':'Category','dept_id':'Department','item_id':'Count'}, \n",
    "             inplace=True)\n",
    "fig = px.treemap(group, path=['USA', 'State', 'Store', 'Category', 'Department'], values='Count',\n",
    "                  color='Count',\n",
    "                  color_continuous_scale= px.colors.sequential.Sunset,\n",
    "                  title='Walmart: Distribution of items')\n",
    "fig.update_layout(template='seaborn')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2 style=\"background-color:DodgerBlue; color:white\" >4.2 Item Prices</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:59:49.195798Z",
     "start_time": "2021-04-22T02:59:25.226401Z"
    }
   },
   "outputs": [],
   "source": [
    "group_price_store = data.groupby(['state_id','store_id','item_id'], as_index=False)['sell_price'].mean().dropna()\n",
    "fig = px.violin(group_price_store, x='store_id', color='state_id', y='sell_price', box=True, hover_name='item_id')\n",
    "fig.update_xaxes(title_text='Store')\n",
    "fig.update_yaxes(title_text='Selling Price($)')\n",
    "fig.update_layout(template='seaborn', title='Distribution of Items prices wrt Stores', legend_title_text='State')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:59:59.856042Z",
     "start_time": "2021-04-22T02:59:49.197793Z"
    }
   },
   "outputs": [],
   "source": [
    "group_price_cat = data.groupby(['store_id', 'cat_id', 'item_id'],as_index=False)['sell_price'].mean().dropna()\n",
    "fig = px.violin(group_price_cat, x='store_id', color='cat_id', y='sell_price', box=True, hover_name='item_id')\n",
    "fig.update_xaxes(title_text='Store')\n",
    "fig.update_yaxes(title_text='Selling Price($)')\n",
    "fig.update_layout(template='seaborn',title='Distribution of Items prices wrt Stores across Categories',\n",
    "                 legend_title_text='Category')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](http://)<h2 style=\"background-color:DodgerBlue; color:white\" >4.3 Items Sales</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:09.109099Z",
     "start_time": "2021-04-22T02:59:59.857039Z"
    }
   },
   "outputs": [],
   "source": [
    "group = data.groupby(['year','date', 'state_id', 'store_id'], as_index=False)['sales'].sum().dropna()\n",
    "fig = px.violin(group, x='store_id', color='state_id', y='sales', box=True)\n",
    "fig.update_xaxes(title_text='Store')\n",
    "fig.update_yaxes(title_text='Total items sales')\n",
    "fig.update_layout(template='seaborn', title='Distribution of Items Sales wrt Stores', legend_title_text='State')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:09.609761Z",
     "start_time": "2021-04-22T03:00:09.111094Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = go.Figure()\n",
    "title = 'Items Sales Over Time'\n",
    "years = group.year.unique().tolist()\n",
    "buttons = []\n",
    "y=3\n",
    "for state in group.state_id.unique().tolist():\n",
    "    group_state = group[group['state_id']==state]\n",
    "    for store in group_state.store_id.unique().tolist():\n",
    "        group_state_store = group_state[group_state['store_id']==store]\n",
    "        fig.add_trace(go.Scatter(name=store, x=group_state_store['date'], y=group_state_store['sales'], showlegend=True, \n",
    "                                   yaxis='y'+str(y) if y!=1 else 'y'))\n",
    "    y-=1\n",
    "\n",
    "fig.update_layout(\n",
    "        xaxis=dict(\n",
    "        #autorange=True,\n",
    "        range = ['2011-01-29','2016-05-22'],\n",
    "        rangeselector=dict(\n",
    "            buttons=list([\n",
    "                dict(count=1,\n",
    "                     label=\"1m\",\n",
    "                     step=\"month\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(count=6,\n",
    "                     label=\"6m\",\n",
    "                     step=\"month\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(count=1,\n",
    "                     label=\"YTD\",\n",
    "                     step=\"year\",\n",
    "                     stepmode=\"todate\"),\n",
    "                dict(count=1,\n",
    "                     label=\"1y\",\n",
    "                     step=\"year\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(count=2,\n",
    "                     label=\"2y\",\n",
    "                     step=\"year\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(count=3,\n",
    "                     label=\"3y\",\n",
    "                     step=\"year\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(count=4,\n",
    "                     label=\"4y\",\n",
    "                     step=\"year\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(step=\"all\")\n",
    "            ])\n",
    "        ),\n",
    "        rangeslider=dict(\n",
    "            autorange=True,\n",
    "        ),\n",
    "        type=\"date\"\n",
    "    ),\n",
    "    yaxis=dict(\n",
    "        anchor=\"x\",\n",
    "        autorange=True,\n",
    "        domain=[0, 0.33],\n",
    "        mirror=True,\n",
    "        showline=True,\n",
    "        side=\"left\",\n",
    "        tickfont={\"size\":10},\n",
    "        tickmode=\"auto\",\n",
    "        ticks=\"\",\n",
    "        title='WI',\n",
    "        titlefont={\"size\":20},\n",
    "        type=\"linear\",\n",
    "        zeroline=False\n",
    "    ),\n",
    "    yaxis2=dict(\n",
    "        anchor=\"x\",\n",
    "        autorange=True,\n",
    "        domain=[0.33, 0.66],\n",
    "        mirror=True,\n",
    "        showline=True,\n",
    "        side=\"left\",\n",
    "        tickfont={\"size\":10},\n",
    "        tickmode=\"auto\",\n",
    "        ticks=\"\",\n",
    "        title = 'TX',\n",
    "        titlefont={\"size\":20},\n",
    "        type=\"linear\",\n",
    "        zeroline=False\n",
    "    ),\n",
    "    yaxis3=dict(\n",
    "        anchor=\"x\",\n",
    "        autorange=True,\n",
    "        domain=[0.66, 1],\n",
    "        mirror=True,\n",
    "        showline=True,\n",
    "        side=\"left\",\n",
    "        tickfont={\"size\":10},\n",
    "        tickmode=\"auto\",\n",
    "        ticks='',\n",
    "        title=\"CA\",\n",
    "        titlefont={\"size\":20},\n",
    "        type=\"linear\",\n",
    "        zeroline=False\n",
    "    )\n",
    "    )\n",
    "fig.update_layout(template='seaborn', title=title)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2 style=\"background-color:DodgerBlue; color:white\" >4.4 State wise Analysis</h2>\n",
    "  <a class=\"list-group-item list-group-item-action\" data-toggle=\"list\" href=\"#cal\" role=\"tab\" aria-controls=\"profile\">California<span class=\"badge badge-primary badge-pill\">1</span></a>\n",
    "  <a class=\"list-group-item list-group-item-action\" data-toggle=\"list\" href=\"#tex\" role=\"tab\" aria-controls=\"messages\">Texas<span class=\"badge badge-primary badge-pill\">2</span></a>\n",
    "  <a class=\"list-group-item list-group-item-action\"  data-toggle=\"list\" href=\"#wis\" role=\"tab\" aria-controls=\"settings\">Wisconsin<span class=\"badge badge-primary badge-pill\">3</span></a>\n",
    "<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:09.954837Z",
     "start_time": "2021-04-22T03:00:09.611755Z"
    }
   },
   "outputs": [],
   "source": [
    "data['revenue'] = data['sales'] * data['sell_price'].astype(np.float32)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:09.970794Z",
     "start_time": "2021-04-22T03:00:09.955834Z"
    }
   },
   "outputs": [],
   "source": [
    "def introduce_nulls(df):\n",
    "    idx = pd.date_range(df.date.dt.date.min(), df.date.dt.date.max())\n",
    "    df = df.set_index('date')\n",
    "    df = df.reindex(idx)\n",
    "    df.reset_index(inplace=True)\n",
    "    df.rename(columns={'index':'date'},inplace=True)\n",
    "    return df\n",
    "\n",
    "def plot_metric(df,state,store,metric):\n",
    "    store_sales = df[(df['state_id']==state)&(df['store_id']==store)&(df['date']<='2016-05-22')]\n",
    "    food_sales = store_sales[store_sales['cat_id']=='FOODS']\n",
    "    store_sales = store_sales.groupby(['date','snap_'+state],as_index=False)['sales', 'revenue'].sum()\n",
    "    snap_sales = store_sales[store_sales['snap_'+state]==1]\n",
    "    non_snap_sales = store_sales[store_sales['snap_'+state]==0]\n",
    "    food_sales = food_sales.groupby(['date','snap_'+state],as_index=False)['sales', 'revenue'].sum()\n",
    "    snap_foods = food_sales[food_sales['snap_'+state]==1]\n",
    "    non_snap_foods = food_sales[food_sales['snap_'+state]==0]\n",
    "    non_snap_sales = introduce_nulls(non_snap_sales)\n",
    "    snap_sales = introduce_nulls(snap_sales)\n",
    "    non_snap_foods = introduce_nulls(non_snap_foods)\n",
    "    snap_foods = introduce_nulls(snap_foods)\n",
    "    fig = go.Figure()\n",
    "    fig.add_trace(go.Scatter(x=non_snap_sales['date'],y=non_snap_sales[metric],\n",
    "                           name='Total '+metric+'(Non-SNAP)'))\n",
    "    fig.add_trace(go.Scatter(x=snap_sales['date'],y=snap_sales[metric],\n",
    "                           name='Total '+metric+'(SNAP)'))\n",
    "    fig.add_trace(go.Scatter(x=non_snap_foods['date'],y=non_snap_foods[metric],\n",
    "                           name='Food '+metric+'(Non-SNAP)'))\n",
    "    fig.add_trace(go.Scatter(x=snap_foods['date'],y=snap_foods[metric],\n",
    "                           name='Food '+metric+'(SNAP)'))\n",
    "    fig.update_yaxes(title_text='Total items sold' if metric=='sold' else 'Total revenue($)')\n",
    "    fig.update_layout(template='seaborn',title=store)\n",
    "    fig.update_layout(\n",
    "        xaxis=dict(\n",
    "        #autorange=True,\n",
    "        range = ['2011-01-29','2016-05-22'],\n",
    "        rangeselector=dict(\n",
    "            buttons=list([\n",
    "                dict(count=1,\n",
    "                     label=\"1m\",\n",
    "                     step=\"month\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(count=6,\n",
    "                     label=\"6m\",\n",
    "                     step=\"month\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(count=1,\n",
    "                     label=\"YTD\",\n",
    "                     step=\"year\",\n",
    "                     stepmode=\"todate\"),\n",
    "                dict(count=1,\n",
    "                     label=\"1y\",\n",
    "                     step=\"year\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(count=2,\n",
    "                     label=\"2y\",\n",
    "                     step=\"year\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(count=3,\n",
    "                     label=\"3y\",\n",
    "                     step=\"year\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(count=4,\n",
    "                     label=\"4y\",\n",
    "                     step=\"year\",\n",
    "                     stepmode=\"backward\"),\n",
    "                dict(step=\"all\")\n",
    "            ])\n",
    "        ),\n",
    "        rangeslider=dict(\n",
    "            autorange=True,\n",
    "        ),\n",
    "        type=\"date\"\n",
    "    ))\n",
    "    return fig"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:10.001710Z",
     "start_time": "2021-04-22T03:00:09.978772Z"
    }
   },
   "outputs": [],
   "source": [
    "cal_data = group.copy()\n",
    "cal_data = cal_data[cal_data.date <= '22-05-2016']\n",
    "cal_data['week'] = cal_data.date.dt.weekofyear\n",
    "cal_data['day_name'] = cal_data.date.dt.day_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:10.016670Z",
     "start_time": "2021-04-22T03:00:10.003705Z"
    }
   },
   "outputs": [],
   "source": [
    "def calmap(cal_data, state, store, scale):\n",
    "    cal_data = cal_data[(cal_data['state_id']==state)&(cal_data['store_id']==store)]\n",
    "    years = cal_data.year.unique().tolist()\n",
    "    fig = make_subplots(rows=len(years),cols=1,shared_xaxes=True,vertical_spacing=0.005)\n",
    "    r=1\n",
    "    for year in years:\n",
    "        data = cal_data[cal_data['year']==year]\n",
    "        data = introduce_nulls(data)\n",
    "        fig.add_trace(go.Heatmap(\n",
    "            z=data.sales,\n",
    "            x=data.week,\n",
    "            y=data.day_name,\n",
    "            hovertext=data.date.dt.date,\n",
    "            coloraxis = \"coloraxis\",name=year,\n",
    "        ),r,1)\n",
    "        fig.update_yaxes(title_text=year,tickfont=dict(size=5),row = r,col = 1)\n",
    "        r+=1\n",
    "    fig.update_xaxes(range=[1,53],tickfont=dict(size=10), nticks=53)\n",
    "    fig.update_layout(coloraxis = {'colorscale':scale})\n",
    "    fig.update_layout(template='seaborn', title=store)\n",
    "    return fig"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"cal\" class=\"btn btn-primary btn-lg btn-block active\" role=\"button\" aria-pressed=\"true\" style=\"color:white;\">California</a>\n",
    "<img src='https://www.pixel4k.com/wp-content/uploads/2018/09/san-francisco-california-cityscape-4k_1538070292.jpg'\n",
    "style=\"width:800px;height:400px;\">\n",
    "\n",
    "<a id='csn'><span class=\"label label-info\">Store Navigator</span></a>\n",
    "<nav aria-label=\"Store Navigator\">\n",
    "  <ul class=\"pagination\">\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href='#C1'>CA_1</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#C2\">CA_2</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#C3\">CA_3</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#C4\">CA_4</a></li>\n",
    "  </ul>\n",
    "</nav>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='C1'></a><h2 style=\"background-color:DodgerBlue; color:white\" >CA_1</h2>\n",
    "<a href='#csn'><span class=\"label label-info\">Go to the Store Navigator</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:17.936420Z",
     "start_time": "2021-04-22T03:00:10.017669Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'CA','CA_1','sales')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:25.108965Z",
     "start_time": "2021-04-22T03:00:17.937418Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'CA','CA_1','revenue')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:25.296493Z",
     "start_time": "2021-04-22T03:00:25.110959Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = calmap(cal_data, 'CA', 'CA_1', 'magma')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='C2'></a><h2 style=\"background-color:DodgerBlue; color:white\" >CA_2</h2>\n",
    "<a href='#csn'><span class=\"label label-info\">Go to the Store Navigator</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:32.813792Z",
     "start_time": "2021-04-22T03:00:25.297460Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data, 'CA', 'CA_2', 'sales')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:40.048694Z",
     "start_time": "2021-04-22T03:00:32.814762Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data, 'CA', 'CA_2', 'revenue')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:40.207807Z",
     "start_time": "2021-04-22T03:00:40.049664Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = calmap(cal_data, 'CA', 'CA_2', 'magma')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-09T06:12:46.391508Z",
     "start_time": "2020-07-09T06:12:46.382500Z"
    }
   },
   "source": [
    "<a id='C3'></a><h2 style=\"background-color:DodgerBlue; color:white\" >CA_3</h2>\n",
    "<a href='#csn'><span class=\"label label-info\">Go to the Store Navigator</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:47.589459Z",
     "start_time": "2021-04-22T03:00:40.208776Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'CA','CA_3','sales')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:54.945562Z",
     "start_time": "2021-04-22T03:00:47.590457Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'CA','CA_3','revenue')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:00:55.102148Z",
     "start_time": "2021-04-22T03:00:54.946531Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = calmap(cal_data, 'CA', 'CA_3', 'magma')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='C4'></a><h2 style=\"background-color:DodgerBlue; color:white\" >CA_4</h2>\n",
    "<a href='#csn'><span class=\"label label-info\">Go to the Store Navigator</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:02.383871Z",
     "start_time": "2021-04-22T03:00:55.103145Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data, 'CA', 'CA_4', 'sales')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:09.631150Z",
     "start_time": "2021-04-22T03:01:02.384868Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'CA','CA_4','revenue')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:09.788728Z",
     "start_time": "2021-04-22T03:01:09.632147Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = calmap(cal_data, 'CA', 'CA_4', 'magma')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"tex\" class=\"btn btn-primary btn-lg btn-block active\" role=\"button\" aria-pressed=\"true\" style=\"color:white;\">Texas</a>\n",
    "<img src='https://wallpaperaccess.com/full/227248.jpg'\n",
    "style=\"width:800px;height:300px;\">\n",
    "<a id='wsn'><span class=\"label label-info\">Store Navigator</span></a>\n",
    "<a id='tsn'><nav aria-label=\"Store Navigator\"></a>\n",
    "  <ul class=\"pagination\">\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href='#T1'>TX_1</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#T2\">TX_2</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#T3\">TX_3</a></li>\n",
    "  </ul>\n",
    "</nav>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-09T06:20:06.068159Z",
     "start_time": "2020-07-09T06:20:06.058157Z"
    }
   },
   "source": [
    "<a id='T1'></a><h2 style=\"background-color:DodgerBlue; color:white\" >TX_1</h2>\n",
    "<a href='#csn'><span class=\"label label-info\">Go to the Store Navigator</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:17.033806Z",
     "start_time": "2021-04-22T03:01:09.789726Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'TX','TX_1','sales')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:24.150029Z",
     "start_time": "2021-04-22T03:01:17.034804Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'TX','TX_1','revenue')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:24.307609Z",
     "start_time": "2021-04-22T03:01:24.150999Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = calmap(cal_data, 'TX', 'TX_1', 'viridis')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='T2'></a><h2 style=\"background-color:DodgerBlue; color:white\" >TX_2</h2>\n",
    "<a href='#csn'><span class=\"label label-info\">Go to the Store Navigator</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:31.562506Z",
     "start_time": "2021-04-22T03:01:24.308605Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'TX','TX_2','sales')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:38.813841Z",
     "start_time": "2021-04-22T03:01:31.563503Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'TX','TX_2','revenue')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:38.968458Z",
     "start_time": "2021-04-22T03:01:38.814839Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = calmap(cal_data, 'TX', 'TX_2', 'viridis')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='T3'></a><h2 style=\"background-color:DodgerBlue; color:white\" >TX_3</h2>\n",
    "<a href='#csn'><span class=\"label label-info\">Go to the Store Navigator</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:46.124677Z",
     "start_time": "2021-04-22T03:01:38.970453Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'TX','TX_3','sales')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:53.244750Z",
     "start_time": "2021-04-22T03:01:46.125674Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'TX','TX_3','revenue')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:01:53.402324Z",
     "start_time": "2021-04-22T03:01:53.245713Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = calmap(cal_data, 'TX', 'TX_3', 'viridis')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"wis\" class=\"btn btn-primary btn-lg btn-block active\" role=\"button\" aria-pressed=\"true\" style=\"color:white;\">Wisconsin</a>\n",
    "<img src='https://i.ytimg.com/vi/RzETB_wVAKI/maxresdefault.jpg'\n",
    "style=\"width:800px;height:300px;\">\n",
    "<span class=\"label label-info\">Store Navigator</span>\n",
    "<nav aria-label=\"Store Navigator\">\n",
    "  <ul class=\"pagination\">\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href='#W1'>WI_1</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#W2\">WI_2</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#W3\">WI_3</a></li>\n",
    "  </ul>\n",
    "</nav>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='W1'></a><h2 style=\"background-color:DodgerBlue; color:white\" >WI_1</h2>\n",
    "<a href='#csn'><span class=\"label label-info\">Go to the Store Navigator</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:02:00.707153Z",
     "start_time": "2021-04-22T03:01:53.404289Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'WI','WI_1','sales')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:02:07.859723Z",
     "start_time": "2021-04-22T03:02:00.708122Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'WI','WI_1','revenue')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:02:08.015307Z",
     "start_time": "2021-04-22T03:02:07.860721Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = calmap(cal_data, 'WI', 'WI_1', 'twilight')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-09T06:25:02.713965Z",
     "start_time": "2020-07-09T06:25:02.693960Z"
    }
   },
   "source": [
    "<a id='W2'></a><h2 style=\"background-color:DodgerBlue; color:white\" >WI_2</h2>\n",
    "<a href='#csn'><span class=\"label label-info\">Go to the Store Navigator</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:02:15.308171Z",
     "start_time": "2021-04-22T03:02:08.016304Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'WI','WI_2','sales')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:02:22.479539Z",
     "start_time": "2021-04-22T03:02:15.309138Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data, 'WI','WI_2','revenue')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:02:22.636477Z",
     "start_time": "2021-04-22T03:02:22.480537Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = calmap(cal_data, 'WI', 'WI_2', 'twilight')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='W3'></a><h2 style=\"background-color:DodgerBlue; color:white\" >WI_3</h2>\n",
    "<a href='#csn'><span class=\"label label-info\">Go to the Store Navigator</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:02:29.820356Z",
     "start_time": "2021-04-22T03:02:22.637447Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'WI','WI_3','sales')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:02:36.997020Z",
     "start_time": "2021-04-22T03:02:29.823348Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = plot_metric(data,'WI','WI_3','revenue')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:02:37.200474Z",
     "start_time": "2021-04-22T03:02:36.998017Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = calmap(cal_data, 'WI', 'WI_3', 'twilight')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T03:02:37.264440Z",
     "start_time": "2021-04-22T03:02:37.201472Z"
    }
   },
   "outputs": [],
   "source": [
    "del group, group_price_cat, group_price_store, group_state, group_state_store, cal_data\n",
    "gc.collect();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <a id='fe'>5. Feature Engineering</a>\n",
    "\n",
    "<img src='https://media.giphy.com/media/yv1ggi3Cbase05a8iS/giphy.gif' style=\"width:500px;height:300px;\">\n",
    "\n",
    "The goal of feature engineering is to provide strong and ideally simple relationships between new input features and the output feature for the supervised learning algorithm to model.\n",
    "\n",
    "<a id='topics'><span class=\"label label-info\">Topics</span></a>\n",
    "<nav aria-label=\"Store Navigator\">\n",
    "  <ul class=\"pagination\">\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href='#F1'>Label Encoding</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#F2\">Introduce Lags</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#F3\">Mean and Median Encoding</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#F4\">Rolling Window Stats</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#F5\">Transform Price</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#F6\">Adding daytime</a></li>\n",
    "    <li class=\"page-item\"><a class=\"page-link\" href=\"#F7\">Saving the data</a></li>\n",
    "  </ul>\n",
    "</nav>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id = 'F1'><h2>5.1 Label Encoding</h2></a>\n",
    "<ol>\n",
    "  <li>Remove unwanted data to create space in RAM for further processing.</li>\n",
    "  <li>Label Encode categorical features.(I had converted already converted categorical variable to category type. So, I can simply use their codes instead of using LableEncoder)</li>\n",
    "  <li>Remove date as its features are already present.</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:31:59.168346Z",
     "start_time": "2021-04-22T02:31:52.634583Z"
    }
   },
   "outputs": [],
   "source": [
    "nanFeatures = ['event_name_1', 'event_name_2', 'event_type_1', 'event_type_2']\n",
    "for feature in nanFeatures:\n",
    "    data[feature].fillna('MISSING', inplace=True)\n",
    "    \n",
    "del nanFeatures\n",
    "gc.collect()\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:33:54.071489Z",
     "start_time": "2021-04-22T02:31:59.169343Z"
    }
   },
   "outputs": [],
   "source": [
    "categorical_features = data.columns.drop([\n",
    "    'sales', 'date', 'sell_price', 'wm_yr_wk', 'snap_CA', 'snap_TX', 'snap_WI', 'id', 'd'])\n",
    "\n",
    "le = LabelEncoder()\n",
    "for feature in categorical_features:\n",
    "    data[feature] = le.fit_transform(data[feature])\n",
    "\n",
    "del categorical_features\n",
    "gc.collect()\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id = 'F2'><h2>5.2 Introduce Lags</h2></a>\n",
    "<a href='#topics'><span class=\"label label-info\">Go back to topics</span></a>\n",
    "\n",
    "Lag features are the classical way that time series forecasting problems are transformed into supervised learning problems.\n",
    "\n",
    "Introduce lags to the the target variable `sales`. The maximum lag I have introduced is 30 days. It's purely upto you how many lags you want to introduce."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:35:19.161526Z",
     "start_time": "2021-04-22T02:33:54.072487Z"
    }
   },
   "outputs": [],
   "source": [
    "data['lag_t28'] = data.groupby(['id'])['sales'].transform(lambda x: x.shift(28)).astype(np.float16)\n",
    "data['lag_t29'] = data.groupby(['id'])['sales'].transform(lambda x: x.shift(29)).astype(np.float16)\n",
    "data['lag_t30'] = data.groupby(['id'])['sales'].transform(lambda x: x.shift(30)).astype(np.float16)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id = 'F3'><h2>5.3 Mean and Median Encoding</h2></a>\n",
    "<a href='#topics'><span class=\"label label-info\">Go back to topics</span></a>\n",
    "\n",
    "From a mathematical point of view, mean encoding represents a probability of your target variable, conditional on each value of the feature. In a way, it embodies the target variable in its encoded value. I have calculated mean encodings on the basis of following logical features I could think of:-\n",
    "- item\n",
    "- state\n",
    "- store\n",
    "- category\n",
    "- department\n",
    "- category & department\n",
    "- store & item\n",
    "- category & item\n",
    "- department & item\n",
    "- state & store\n",
    "- state, store and category\n",
    "- store, category and department"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:35:57.915190Z",
     "start_time": "2021-04-22T02:35:19.162222Z"
    }
   },
   "outputs": [],
   "source": [
    "data['iteam_sold_avg_mean'] = data.groupby('item_id')['sales'].transform('mean')\n",
    "data['state_sold_avg_mean'] = data.groupby('state_id')['sales'].transform('mean')\n",
    "data['store_sold_avg_mean'] = data.groupby('store_id')['sales'].transform('mean')\n",
    "data['cat_sold_avg_mean']   = data.groupby('cat_id')['sales'].transform('mean')\n",
    "data['dept_sold_avg_mean']  = data.groupby('dept_id')['sales'].transform('mean')\n",
    "\n",
    "data['cat_dept_sold_avg_mean'] = data.groupby(['cat_id', 'dept_id'])['sales'].transform('mean')\n",
    "data['store_item_sold_avg_mean'] = data.groupby(['store_id', 'item_id'])['sales'].transform('mean')\n",
    "data['cat_item_sold_avg_mean'] = data.groupby(['cat_id', 'item_id'])['sales'].transform('mean')\n",
    "data['dept_item_sold_avg_mean'] = data.groupby(['dept_id', 'item_id'])['sales'].transform('mean')\n",
    "data['state_store_sold_avg_mean'] = data.groupby(['state_id', 'store_id'])['sales'].transform('mean')\n",
    "data['state_store_cat_sold_avg_mean'] = data.groupby(['state_id', 'store_id', 'cat_id'])['sales'].transform('mean')\n",
    "data['store_cat_dept_sold_avg_mean'] = data.groupby(['store_id', 'cat_id', 'dept_id'])['sales'].transform('mean')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:36:45.457381Z",
     "start_time": "2021-04-22T02:35:57.917254Z"
    }
   },
   "outputs": [],
   "source": [
    "data['iteam_sold_avg_median'] = data.groupby('item_id')['sales'].transform('median')\n",
    "data['state_sold_avg_median'] = data.groupby('state_id')['sales'].transform('median')\n",
    "data['store_sold_avg_median'] = data.groupby('store_id')['sales'].transform('median')\n",
    "data['cat_sold_avg_median'] = data.groupby('cat_id')['sales'].transform('median')\n",
    "data['dept_sold_avg_median'] = data.groupby('dept_id')['sales'].transform('median')\n",
    "data['cat_dept_sold_avg_median'] = data.groupby(['cat_id', 'dept_id'])['sales'].transform('median')\n",
    "data['store_item_sold_avg_median'] = data.groupby(['store_id', 'item_id'])['sales'].transform('median')\n",
    "data['cat_item_sold_avg_median'] = data.groupby(['cat_id', 'item_id'])['sales'].transform('median')\n",
    "data['dept_item_sold_avg_median'] = data.groupby(['dept_id', 'item_id'])['sales'].transform('median')\n",
    "data['state_store_sold_avg_median'] = data.groupby(['state_id', 'store_id'])['sales'].transform('median')\n",
    "data['state_store_cat_sold_avg_median'] = data.groupby(['state_id', 'store_id', 'cat_id'])['sales'].transform('median')\n",
    "data['store_cat_dept_sold_avg_median'] = data.groupby(['store_id', 'cat_id', 'dept_id'])['sales'].transform('median')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-09T06:38:39.622313Z",
     "start_time": "2020-07-09T06:38:39.607315Z"
    }
   },
   "source": [
    "<a id = 'F4'><h2>5.4 Rolling Window Stats</h2></a>\n",
    "<a href='#topics'><span class=\"label label-info\">Go back to topics</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:38:34.234368Z",
     "start_time": "2021-04-22T02:36:45.458346Z"
    }
   },
   "outputs": [],
   "source": [
    "data['rolling_mean_t7'] = data.groupby(['id'])['sales'].transform(lambda x: x.shift(28).rolling(7).mean())\n",
    "data['rolling_std_t7'] = data.groupby(['id'])['sales'].transform(lambda x: x.shift(28).rolling(7).std())\n",
    "\n",
    "data['rolling_mean_t30'] = data.groupby(['id'])['sales'].transform(lambda x: x.shift(28).rolling(30).mean())\n",
    "data['rolling_std_t30'] = data.groupby(['id'])['sales'].transform(lambda x: x.shift(28).rolling(30).std())\n",
    "\n",
    "data['rolling_mean_t90'] = data.groupby(['id'])['sales'].transform(lambda x: x.shift(28).rolling(90).mean())\n",
    "data['rolling_mean_t180'] = data.groupby(['id'])['sales'].transform(lambda x: x.shift(28).rolling(180).mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-09T06:39:44.619662Z",
     "start_time": "2020-07-09T06:39:44.614662Z"
    }
   },
   "source": [
    "<a id = 'F5'><h2>5.5 Transform Price</h2></a>\n",
    "<a href='#topics'><span class=\"label label-info\">Go back to topics</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:38:34.236363Z",
     "start_time": "2021-04-22T02:31:08.366Z"
    }
   },
   "outputs": [],
   "source": [
    "data['lag_price'] = data.groupby(['id'])['sell_price'].transform(lambda x: x.shift(1))\n",
    "data['price_change'] = (data['lag_price'] - data['sell_price']) / (data['lag_price'])\n",
    "data['price_std'] = (data['sell_price'] - data['sell_price'].min()) / (1 + data['sell_price'].max() - data['sell_price'].min())\n",
    "data.drop(['lag_price'], inplace=True, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-09T06:41:35.342731Z",
     "start_time": "2020-07-09T06:41:35.327725Z"
    }
   },
   "source": [
    "<a id = 'F6'><h2>5.6 Adding daytime</h2></a>\n",
    "<a href='#topics'><span class=\"label label-info\">Go back to topics</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:38:34.237361Z",
     "start_time": "2021-04-22T02:31:09.014Z"
    }
   },
   "outputs": [],
   "source": [
    "data.drop(['weekday', 'wday', 'month', 'year', 'wm_yr_wk'], inplace=True, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:38:34.238358Z",
     "start_time": "2021-04-22T02:31:09.371Z"
    }
   },
   "outputs": [],
   "source": [
    "data['date'] = pd.to_datetime(data['date'])\n",
    "data['year'] = (data['date'].dt.year - 2010)\n",
    "data['quarter'] = data['date'].dt.quarter\n",
    "data['month'] = data['date'].dt.month\n",
    "data['week'] = data['date'].dt.week\n",
    "data['day'] = data['date'].dt.day\n",
    "data['dayofweek'] = data['date'].dt.dayofweek\n",
    "data['d'] = data['d'].apply(lambda x: x.split('_')[1]).astype(np.int64)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id = 'F7'><h2>5.7 Saving the data</h2></a>\n",
    "<a href='#topics'><span class=\"label label-info\">Go back to topics</span></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:38:34.239355Z",
     "start_time": "2021-04-22T02:31:13.624Z"
    }
   },
   "outputs": [],
   "source": [
    "data = reduce_mem_usage(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-22T02:38:34.240352Z",
     "start_time": "2021-04-22T02:31:15.769Z"
    }
   },
   "outputs": [],
   "source": [
    "data = data[data['d'] >= 180]\n",
    "data.to_pickle(path + \"cleaned_data.pkl\")\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <a id='pred'>6. Modelling and Prediction</a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Original\n",
    "params = {\n",
    "        \"metric\": \"rmse\",\n",
    "        \"objective\": \"poisson\",\n",
    "        \"alpha\": 0.1,\n",
    "        \"lambda\": 0.1,\n",
    "        \"seed\": 42,\n",
    "        \"num_leaves\": 100,\n",
    "        \"learning_rate\": 0.075,\n",
    "        \"bagging_fraction\": 0.75,\n",
    "        \"bagging_freq\": 2,\n",
    "        \"colsample_bytree\": 0.75,\n",
    "    }\n",
    "\n",
    "fit_params = {\n",
    "        \"num_boost_round\": 3000,\n",
    "        \"verbose_eval\": 100}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-15T11:26:11.317889Z",
     "start_time": "2020-07-15T11:26:11.288898Z"
    }
   },
   "outputs": [],
   "source": [
    "# Preparing training features\n",
    "features = data.columns.drop(['id', 'date', 'd', 'sales','revenue'])\n",
    "features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-15T11:26:12.274450Z",
     "start_time": "2020-07-15T11:26:11.319889Z"
    }
   },
   "outputs": [],
   "source": [
    "# Preparing training, valid, testing data\n",
    "valid = data[(data['d'] >= 1914) & (data['d'] < 1942)][['id', 'd', 'sales']]\n",
    "test = data[data['d'] >= 1942][['id', 'd', 'sales']]\n",
    "eval_preds, valid_preds = test['sales'], valid['sales']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "start_time": "2020-07-15T11:25:09.247Z"
    }
   },
   "outputs": [],
   "source": [
    "# Training by individual store\n",
    "stores = data['store_id'].unique().tolist()\n",
    "for store in stores:\n",
    "    print(\"STORE TRAINING: \", store+1)\n",
    "    df = data[data['store_id'] == store]\n",
    "\n",
    "    #Split the data\n",
    "    X_train, y_train = df[df['d'] < 1914].drop('sales', axis=1), df[df['d'] < 1914]['sales']\n",
    "    X_valid, y_valid = df[(df['d'] >= 1914) & (df['d'] < 1942)].drop('sales', axis=1), df[(df['d'] >= 1914) & (df['d'] < 1942)]['sales']\n",
    "    X_test = df[df['d'] >= 1942].drop('sales', axis=1)\n",
    "    \n",
    "    train_set = lgb.Dataset(X_train[features], y_train)\n",
    "    valid_set = lgb.Dataset(X_valid[features], y_valid)\n",
    "\n",
    "    #Train and validate\n",
    "    model = lgb.train(params, train_set, valid_sets=[train_set, valid_set], **fit_params)\n",
    "    valid_rmse = np.sqrt(mean_squared_error(model.predict(X_valid[features]), y_valid))\n",
    "    eval_preds[X_test[features].index] = model.predict(X_test[features])\n",
    "\n",
    "    del model, X_train, y_train, X_valid, y_valid\n",
    "    gc.collect()\n",
    "    \n",
    "    print(\"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "start_time": "2020-07-15T11:25:09.472Z"
    }
   },
   "outputs": [],
   "source": [
    "validation = sales[['id'] + ['d_' + str(i) for i in range(1914, 1942)]]\n",
    "validation['id'] = pd.read_csv(path + 'sales_train_validation.csv').id\n",
    "validation.columns = ['id'] + ['F' + str(i + 1) for i in range(28)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "start_time": "2020-07-15T11:25:09.808Z"
    }
   },
   "outputs": [],
   "source": [
    "test['sales'] = eval_preds\n",
    "predictions = test[['id', 'd', 'sales']]\n",
    "predictions = pd.pivot(predictions, index='id', columns='d', values='sales').reset_index()\n",
    "predictions.columns = ['id'] + ['F' + str(i + 1) for i in range(28)]\n",
    "evaluation = sampleSubmission[['id']].merge(predictions, on = 'id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "start_time": "2020-07-15T11:25:10.086Z"
    }
   },
   "outputs": [],
   "source": [
    "# Prepare the submission\n",
    "submit = pd.concat([validation, evaluation]).reset_index(drop=True)\n",
    "submit.to_csv('submission_lgbm.csv', index=False)\n",
    "submit[30490:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.8"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}