WalmartStorePredictionProject.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "#WALMART STORE PREDICTION - FORECASTING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing the required libraries\n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "import numpy as np\n",
    "import calendar\n",
    "from sklearn.cross_validation import train_test_split\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing provided train data as data\n",
    "data=pd.read_csv(\"trainMerged.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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</th>\n",
       "      <th>Date</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>Dept</th>\n",
       "      <th>Weekly_Sales</th>\n",
       "      <th>Type</th>\n",
       "      <th>Size</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Fuel_Price</th>\n",
       "      <th>MarkDown1</th>\n",
       "      <th>MarkDown2</th>\n",
       "      <th>MarkDown3</th>\n",
       "      <th>MarkDown4</th>\n",
       "      <th>MarkDown5</th>\n",
       "      <th>CPI</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>2010-02-05</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>40212.84</td>\n",
       "      <td>B</td>\n",
       "      <td>126512</td>\n",
       "      <td>54.34</td>\n",
       "      <td>2.962</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>126.442065</td>\n",
       "      <td>9.765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>2010-02-05</td>\n",
       "      <td>False</td>\n",
       "      <td>98</td>\n",
       "      <td>74.00</td>\n",
       "      <td>B</td>\n",
       "      <td>126512</td>\n",
       "      <td>54.34</td>\n",
       "      <td>2.962</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>126.442065</td>\n",
       "      <td>9.765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>2010-02-05</td>\n",
       "      <td>False</td>\n",
       "      <td>32</td>\n",
       "      <td>26116.73</td>\n",
       "      <td>B</td>\n",
       "      <td>126512</td>\n",
       "      <td>54.34</td>\n",
       "      <td>2.962</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>126.442065</td>\n",
       "      <td>9.765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "      <td>2010-02-05</td>\n",
       "      <td>False</td>\n",
       "      <td>25</td>\n",
       "      <td>20779.50</td>\n",
       "      <td>B</td>\n",
       "      <td>126512</td>\n",
       "      <td>54.34</td>\n",
       "      <td>2.962</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>126.442065</td>\n",
       "      <td>9.765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>2010-02-05</td>\n",
       "      <td>False</td>\n",
       "      <td>34</td>\n",
       "      <td>50585.42</td>\n",
       "      <td>B</td>\n",
       "      <td>126512</td>\n",
       "      <td>54.34</td>\n",
       "      <td>2.962</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>126.442065</td>\n",
       "      <td>9.765</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store        Date  IsHoliday  Dept  Weekly_Sales Type    Size  Temperature  \\\n",
       "0     10  2010-02-05      False     1      40212.84    B  126512        54.34   \n",
       "1     10  2010-02-05      False    98         74.00    B  126512        54.34   \n",
       "2     10  2010-02-05      False    32      26116.73    B  126512        54.34   \n",
       "3     10  2010-02-05      False    25      20779.50    B  126512        54.34   \n",
       "4     10  2010-02-05      False    34      50585.42    B  126512        54.34   \n",
       "\n",
       "   Fuel_Price  MarkDown1  MarkDown2  MarkDown3  MarkDown4  MarkDown5  \\\n",
       "0       2.962        NaN        NaN        NaN        NaN        NaN   \n",
       "1       2.962        NaN        NaN        NaN        NaN        NaN   \n",
       "2       2.962        NaN        NaN        NaN        NaN        NaN   \n",
       "3       2.962        NaN        NaN        NaN        NaN        NaN   \n",
       "4       2.962        NaN        NaN        NaN        NaN        NaN   \n",
       "\n",
       "          CPI  Unemployment  \n",
       "0  126.442065         9.765  \n",
       "1  126.442065         9.765  \n",
       "2  126.442065         9.765  \n",
       "3  126.442065         9.765  \n",
       "4  126.442065         9.765  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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</th>\n",
       "      <th>Dept</th>\n",
       "      <th>Weekly_Sales</th>\n",
       "      <th>Size</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Fuel_Price</th>\n",
       "      <th>MarkDown1</th>\n",
       "      <th>MarkDown2</th>\n",
       "      <th>MarkDown3</th>\n",
       "      <th>MarkDown4</th>\n",
       "      <th>MarkDown5</th>\n",
       "      <th>CPI</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>421570.000000</td>\n",
       "      <td>421570.000000</td>\n",
       "      <td>421570.000000</td>\n",
       "      <td>421570.000000</td>\n",
       "      <td>421570.000000</td>\n",
       "      <td>421570.000000</td>\n",
       "      <td>150681.000000</td>\n",
       "      <td>111248.000000</td>\n",
       "      <td>137091.000000</td>\n",
       "      <td>134967.000000</td>\n",
       "      <td>151432.000000</td>\n",
       "      <td>421570.000000</td>\n",
       "      <td>421570.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>22.200546</td>\n",
       "      <td>44.260317</td>\n",
       "      <td>15981.258123</td>\n",
       "      <td>136727.915739</td>\n",
       "      <td>60.090059</td>\n",
       "      <td>3.361027</td>\n",
       "      <td>7246.420196</td>\n",
       "      <td>3334.628621</td>\n",
       "      <td>1439.421384</td>\n",
       "      <td>3383.168256</td>\n",
       "      <td>4628.975079</td>\n",
       "      <td>171.201947</td>\n",
       "      <td>7.960289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>12.785297</td>\n",
       "      <td>30.492054</td>\n",
       "      <td>22711.183519</td>\n",
       "      <td>60980.583328</td>\n",
       "      <td>18.447931</td>\n",
       "      <td>0.458515</td>\n",
       "      <td>8291.221345</td>\n",
       "      <td>9475.357325</td>\n",
       "      <td>9623.078290</td>\n",
       "      <td>6292.384031</td>\n",
       "      <td>5962.887455</td>\n",
       "      <td>39.159276</td>\n",
       "      <td>1.863296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-4988.940000</td>\n",
       "      <td>34875.000000</td>\n",
       "      <td>-2.060000</td>\n",
       "      <td>2.472000</td>\n",
       "      <td>0.270000</td>\n",
       "      <td>-265.760000</td>\n",
       "      <td>-29.100000</td>\n",
       "      <td>0.220000</td>\n",
       "      <td>135.160000</td>\n",
       "      <td>126.064000</td>\n",
       "      <td>3.879000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>11.000000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>2079.650000</td>\n",
       "      <td>93638.000000</td>\n",
       "      <td>46.680000</td>\n",
       "      <td>2.933000</td>\n",
       "      <td>2240.270000</td>\n",
       "      <td>41.600000</td>\n",
       "      <td>5.080000</td>\n",
       "      <td>504.220000</td>\n",
       "      <td>1878.440000</td>\n",
       "      <td>132.022667</td>\n",
       "      <td>6.891000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>22.000000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>7612.030000</td>\n",
       "      <td>140167.000000</td>\n",
       "      <td>62.090000</td>\n",
       "      <td>3.452000</td>\n",
       "      <td>5347.450000</td>\n",
       "      <td>192.000000</td>\n",
       "      <td>24.600000</td>\n",
       "      <td>1481.310000</td>\n",
       "      <td>3359.450000</td>\n",
       "      <td>182.318780</td>\n",
       "      <td>7.866000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>33.000000</td>\n",
       "      <td>74.000000</td>\n",
       "      <td>20205.852500</td>\n",
       "      <td>202505.000000</td>\n",
       "      <td>74.280000</td>\n",
       "      <td>3.738000</td>\n",
       "      <td>9210.900000</td>\n",
       "      <td>1926.940000</td>\n",
       "      <td>103.990000</td>\n",
       "      <td>3595.040000</td>\n",
       "      <td>5563.800000</td>\n",
       "      <td>212.416993</td>\n",
       "      <td>8.572000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>45.000000</td>\n",
       "      <td>99.000000</td>\n",
       "      <td>693099.360000</td>\n",
       "      <td>219622.000000</td>\n",
       "      <td>100.140000</td>\n",
       "      <td>4.468000</td>\n",
       "      <td>88646.760000</td>\n",
       "      <td>104519.540000</td>\n",
       "      <td>141630.610000</td>\n",
       "      <td>67474.850000</td>\n",
       "      <td>108519.280000</td>\n",
       "      <td>227.232807</td>\n",
       "      <td>14.313000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Store           Dept   Weekly_Sales           Size  \\\n",
       "count  421570.000000  421570.000000  421570.000000  421570.000000   \n",
       "mean       22.200546      44.260317   15981.258123  136727.915739   \n",
       "std        12.785297      30.492054   22711.183519   60980.583328   \n",
       "min         1.000000       1.000000   -4988.940000   34875.000000   \n",
       "25%        11.000000      18.000000    2079.650000   93638.000000   \n",
       "50%        22.000000      37.000000    7612.030000  140167.000000   \n",
       "75%        33.000000      74.000000   20205.852500  202505.000000   \n",
       "max        45.000000      99.000000  693099.360000  219622.000000   \n",
       "\n",
       "         Temperature     Fuel_Price      MarkDown1      MarkDown2  \\\n",
       "count  421570.000000  421570.000000  150681.000000  111248.000000   \n",
       "mean       60.090059       3.361027    7246.420196    3334.628621   \n",
       "std        18.447931       0.458515    8291.221345    9475.357325   \n",
       "min        -2.060000       2.472000       0.270000    -265.760000   \n",
       "25%        46.680000       2.933000    2240.270000      41.600000   \n",
       "50%        62.090000       3.452000    5347.450000     192.000000   \n",
       "75%        74.280000       3.738000    9210.900000    1926.940000   \n",
       "max       100.140000       4.468000   88646.760000  104519.540000   \n",
       "\n",
       "           MarkDown3      MarkDown4      MarkDown5            CPI  \\\n",
       "count  137091.000000  134967.000000  151432.000000  421570.000000   \n",
       "mean     1439.421384    3383.168256    4628.975079     171.201947   \n",
       "std      9623.078290    6292.384031    5962.887455      39.159276   \n",
       "min       -29.100000       0.220000     135.160000     126.064000   \n",
       "25%         5.080000     504.220000    1878.440000     132.022667   \n",
       "50%        24.600000    1481.310000    3359.450000     182.318780   \n",
       "75%       103.990000    3595.040000    5563.800000     212.416993   \n",
       "max    141630.610000   67474.850000  108519.280000     227.232807   \n",
       "\n",
       "        Unemployment  \n",
       "count  421570.000000  \n",
       "mean        7.960289  \n",
       "std         1.863296  \n",
       "min         3.879000  \n",
       "25%         6.891000  \n",
       "50%         7.866000  \n",
       "75%         8.572000  \n",
       "max        14.313000  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Date']=pd.to_datetime(data['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MarkDown2       310322\n",
       "MarkDown4       286603\n",
       "MarkDown3       284479\n",
       "MarkDown1       270889\n",
       "MarkDown5       270138\n",
       "Unemployment         0\n",
       "CPI                  0\n",
       "Fuel_Price           0\n",
       "Temperature          0\n",
       "Size                 0\n",
       "Type                 0\n",
       "Weekly_Sales         0\n",
       "Dept                 0\n",
       "IsHoliday            0\n",
       "Date                 0\n",
       "Store                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isnull().sum(axis=0).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replacing Null values with 0\n",
    "data=data.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Month'] = pd.DatetimeIndex(data['Date']).month\n",
    "data['Month'] = data['Month'].apply(lambda x: calendar.month_abbr[x])\n",
    "data['Year'] = pd.DatetimeIndex(data['Date']).year\n",
    "data['Day']=pd.DatetimeIndex(data['Date']).day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating Dummy Variables for Month Column\n",
    "data=data.join(pd.get_dummies(data.Month))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.update(data.drop(\"Month\",axis=1,inplace=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['IsHoliday']=data['IsHoliday'].map({\"True\": 1, \"False\": 0})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=data.join(pd.get_dummies(data.Type,prefix=\"Type_\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.update(data.drop(\"Type\",axis=1,inplace=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Super_Bowl']=np.where(((data['Date']==datetime.datetime(2010, 2, 12)) | (data['Date']==datetime.datetime(2011, 2, 11)) | (data['Date']==datetime.datetime(2012, 2, 10)) | (data['Date']==datetime.datetime(2013, 2, 8))),1,0)\n",
    "data['Labour_Day']=np.where((data['Date']==datetime.datetime(2010, 9, 10)) | (data['Date']==datetime.datetime(2011, 9, 9)) | (data['Date']==datetime.datetime(2012, 9, 7)) | (data['Date']==datetime.datetime(2013, 9, 6)),1,0)\n",
    "data['Thanksgiving']=np.where((data['Date']==datetime.datetime(2010, 11, 26)) | (data['Date']==datetime.datetime(2011, 11, 25)) | (data['Date']==datetime.datetime(2012, 11, 23)) | (data['Date']==datetime.datetime(2013, 11, 29)),1,0)\n",
    "data['Christmas']=np.where((data['Date']==datetime.datetime(2010, 12, 31)) | (data['Date']==datetime.datetime(2011, 12, 30)) | (data['Date']==datetime.datetime(2012, 12, 28)) | (data['Date']==datetime.datetime(2013, 12, 27)),1,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['IsHoliday']=data['IsHoliday']|data['Super_Bowl']|data['Labour_Day']|data['Thanksgiving']|data['Christmas']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=data.drop(columns=[\"Super_Bowl\",\"Labour_Day\",\"Thanksgiving\",\"Christmas\",\"Date\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>Dept</th>\n",
       "      <th>Weekly_Sales</th>\n",
       "      <th>Size</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Fuel_Price</th>\n",
       "      <th>MarkDown1</th>\n",
       "      <th>MarkDown2</th>\n",
       "      <th>MarkDown3</th>\n",
       "      <th>...</th>\n",
       "      <th>Jul</th>\n",
       "      <th>Jun</th>\n",
       "      <th>Mar</th>\n",
       "      <th>May</th>\n",
       "      <th>Nov</th>\n",
       "      <th>Oct</th>\n",
       "      <th>Sep</th>\n",
       "      <th>Type__A</th>\n",
       "      <th>Type__B</th>\n",
       "      <th>Type__C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>40212.84</td>\n",
       "      <td>126512</td>\n",
       "      <td>54.34</td>\n",
       "      <td>2.962</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>98</td>\n",
       "      <td>74.00</td>\n",
       "      <td>126512</td>\n",
       "      <td>54.34</td>\n",
       "      <td>2.962</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>32</td>\n",
       "      <td>26116.73</td>\n",
       "      <td>126512</td>\n",
       "      <td>54.34</td>\n",
       "      <td>2.962</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>25</td>\n",
       "      <td>20779.50</td>\n",
       "      <td>126512</td>\n",
       "      <td>54.34</td>\n",
       "      <td>2.962</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>34</td>\n",
       "      <td>50585.42</td>\n",
       "      <td>126512</td>\n",
       "      <td>54.34</td>\n",
       "      <td>2.962</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store  IsHoliday  Dept  Weekly_Sales    Size  Temperature  Fuel_Price  \\\n",
       "0     10      False     1      40212.84  126512        54.34       2.962   \n",
       "1     10      False    98         74.00  126512        54.34       2.962   \n",
       "2     10      False    32      26116.73  126512        54.34       2.962   \n",
       "3     10      False    25      20779.50  126512        54.34       2.962   \n",
       "4     10      False    34      50585.42  126512        54.34       2.962   \n",
       "\n",
       "   MarkDown1  MarkDown2  MarkDown3   ...     Jul  Jun  Mar  May  Nov  Oct  \\\n",
       "0        0.0        0.0        0.0   ...       0    0    0    0    0    0   \n",
       "1        0.0        0.0        0.0   ...       0    0    0    0    0    0   \n",
       "2        0.0        0.0        0.0   ...       0    0    0    0    0    0   \n",
       "3        0.0        0.0        0.0   ...       0    0    0    0    0    0   \n",
       "4        0.0        0.0        0.0   ...       0    0    0    0    0    0   \n",
       "\n",
       "   Sep  Type__A  Type__B  Type__C  \n",
       "0    0        0        1        0  \n",
       "1    0        0        1        0  \n",
       "2    0        0        1        0  \n",
       "3    0        0        1        0  \n",
       "4    0        0        1        0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Defining weekly sales as our target variable \"y\"\n",
    "y=data[\"Weekly_Sales\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.drop(\"Weekly_Sales\",inplace=True, axis=1)\n",
    "#Since Weekly Sales is our target variable, it shall cease to exist in data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Modeling\n",
    "x_train, x_test, y_train, y_test= train_test_split(data,y,test_size=0.2,random_state=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RandomForestRegressor(bootstrap=True, criterion='mse', max_depth=None,\n",
       "           max_features='auto', max_leaf_nodes=None,\n",
       "           min_impurity_decrease=0.0, min_impurity_split=None,\n",
       "           min_samples_leaf=1, min_samples_split=2,\n",
       "           min_weight_fraction_leaf=0.0, n_estimators=100, n_jobs=-1,\n",
       "           oob_score=False, random_state=None, verbose=0, warm_start=False)"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model = RandomForestRegressor(n_estimators=100, n_jobs=-1)\n",
    "model.fit(x_train,y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred=model.predict(x_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred=pd.DataFrame(data=y_pred,index=x_test.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97.72246005974526"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model.score(x_test,y_test)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Loading data for which predictions are to be made"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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</th>\n",
       "      <th>Date</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>Dept</th>\n",
       "      <th>Type</th>\n",
       "      <th>Size</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Fuel_Price</th>\n",
       "      <th>MarkDown1</th>\n",
       "      <th>MarkDown2</th>\n",
       "      <th>MarkDown3</th>\n",
       "      <th>MarkDown4</th>\n",
       "      <th>MarkDown5</th>\n",
       "      <th>CPI</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>2012-11-02</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>B</td>\n",
       "      <td>126512</td>\n",
       "      <td>70.79</td>\n",
       "      <td>4.099</td>\n",
       "      <td>25680.2</td>\n",
       "      <td>6037.06</td>\n",
       "      <td>44.68</td>\n",
       "      <td>17412.04</td>\n",
       "      <td>4223.05</td>\n",
       "      <td>131.236226</td>\n",
       "      <td>6.943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>2012-11-02</td>\n",
       "      <td>False</td>\n",
       "      <td>72</td>\n",
       "      <td>B</td>\n",
       "      <td>126512</td>\n",
       "      <td>70.79</td>\n",
       "      <td>4.099</td>\n",
       "      <td>25680.2</td>\n",
       "      <td>6037.06</td>\n",
       "      <td>44.68</td>\n",
       "      <td>17412.04</td>\n",
       "      <td>4223.05</td>\n",
       "      <td>131.236226</td>\n",
       "      <td>6.943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>2012-11-02</td>\n",
       "      <td>False</td>\n",
       "      <td>71</td>\n",
       "      <td>B</td>\n",
       "      <td>126512</td>\n",
       "      <td>70.79</td>\n",
       "      <td>4.099</td>\n",
       "      <td>25680.2</td>\n",
       "      <td>6037.06</td>\n",
       "      <td>44.68</td>\n",
       "      <td>17412.04</td>\n",
       "      <td>4223.05</td>\n",
       "      <td>131.236226</td>\n",
       "      <td>6.943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "      <td>2012-11-02</td>\n",
       "      <td>False</td>\n",
       "      <td>21</td>\n",
       "      <td>B</td>\n",
       "      <td>126512</td>\n",
       "      <td>70.79</td>\n",
       "      <td>4.099</td>\n",
       "      <td>25680.2</td>\n",
       "      <td>6037.06</td>\n",
       "      <td>44.68</td>\n",
       "      <td>17412.04</td>\n",
       "      <td>4223.05</td>\n",
       "      <td>131.236226</td>\n",
       "      <td>6.943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>2012-11-02</td>\n",
       "      <td>False</td>\n",
       "      <td>67</td>\n",
       "      <td>B</td>\n",
       "      <td>126512</td>\n",
       "      <td>70.79</td>\n",
       "      <td>4.099</td>\n",
       "      <td>25680.2</td>\n",
       "      <td>6037.06</td>\n",
       "      <td>44.68</td>\n",
       "      <td>17412.04</td>\n",
       "      <td>4223.05</td>\n",
       "      <td>131.236226</td>\n",
       "      <td>6.943</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store        Date  IsHoliday  Dept Type    Size  Temperature  Fuel_Price  \\\n",
       "0     10  2012-11-02      False     1    B  126512        70.79       4.099   \n",
       "1     10  2012-11-02      False    72    B  126512        70.79       4.099   \n",
       "2     10  2012-11-02      False    71    B  126512        70.79       4.099   \n",
       "3     10  2012-11-02      False    21    B  126512        70.79       4.099   \n",
       "4     10  2012-11-02      False    67    B  126512        70.79       4.099   \n",
       "\n",
       "   MarkDown1  MarkDown2  MarkDown3  MarkDown4  MarkDown5         CPI  \\\n",
       "0    25680.2    6037.06      44.68   17412.04    4223.05  131.236226   \n",
       "1    25680.2    6037.06      44.68   17412.04    4223.05  131.236226   \n",
       "2    25680.2    6037.06      44.68   17412.04    4223.05  131.236226   \n",
       "3    25680.2    6037.06      44.68   17412.04    4223.05  131.236226   \n",
       "4    25680.2    6037.06      44.68   17412.04    4223.05  131.236226   \n",
       "\n",
       "   Unemployment  \n",
       "0         6.943  \n",
       "1         6.943  \n",
       "2         6.943  \n",
       "3         6.943  \n",
       "4         6.943  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testdata=pd.read_csv(\"testMerged.csv\")\n",
    "testdata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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</th>\n",
       "      <th>Dept</th>\n",
       "      <th>Size</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Fuel_Price</th>\n",
       "      <th>MarkDown1</th>\n",
       "      <th>MarkDown2</th>\n",
       "      <th>MarkDown3</th>\n",
       "      <th>MarkDown4</th>\n",
       "      <th>MarkDown5</th>\n",
       "      <th>CPI</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>115064.000000</td>\n",
       "      <td>115064.000000</td>\n",
       "      <td>115064.000000</td>\n",
       "      <td>115064.000000</td>\n",
       "      <td>115064.000000</td>\n",
       "      <td>114915.000000</td>\n",
       "      <td>86437.000000</td>\n",
       "      <td>105235.000000</td>\n",
       "      <td>102176.000000</td>\n",
       "      <td>115064.000000</td>\n",
       "      <td>76902.000000</td>\n",
       "      <td>76902.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>22.238207</td>\n",
       "      <td>44.339524</td>\n",
       "      <td>136497.688921</td>\n",
       "      <td>53.941804</td>\n",
       "      <td>3.581546</td>\n",
       "      <td>7689.216439</td>\n",
       "      <td>3734.051729</td>\n",
       "      <td>2403.088666</td>\n",
       "      <td>3356.219071</td>\n",
       "      <td>3922.681189</td>\n",
       "      <td>176.961347</td>\n",
       "      <td>6.868733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>12.809930</td>\n",
       "      <td>30.656410</td>\n",
       "      <td>61106.926438</td>\n",
       "      <td>18.724153</td>\n",
       "      <td>0.239442</td>\n",
       "      <td>10698.760716</td>\n",
       "      <td>8323.495014</td>\n",
       "      <td>13767.939313</td>\n",
       "      <td>7570.501545</td>\n",
       "      <td>19445.150745</td>\n",
       "      <td>41.239967</td>\n",
       "      <td>1.583427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>34875.000000</td>\n",
       "      <td>-7.290000</td>\n",
       "      <td>2.872000</td>\n",
       "      <td>-2781.450000</td>\n",
       "      <td>-35.740000</td>\n",
       "      <td>-179.260000</td>\n",
       "      <td>0.220000</td>\n",
       "      <td>-185.170000</td>\n",
       "      <td>131.236226</td>\n",
       "      <td>3.684000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>11.000000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>93638.000000</td>\n",
       "      <td>39.820000</td>\n",
       "      <td>3.431000</td>\n",
       "      <td>1966.460000</td>\n",
       "      <td>180.350000</td>\n",
       "      <td>15.100000</td>\n",
       "      <td>155.460000</td>\n",
       "      <td>1309.300000</td>\n",
       "      <td>138.402033</td>\n",
       "      <td>5.771000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>22.000000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>140167.000000</td>\n",
       "      <td>54.470000</td>\n",
       "      <td>3.606000</td>\n",
       "      <td>4842.290000</td>\n",
       "      <td>742.590000</td>\n",
       "      <td>78.260000</td>\n",
       "      <td>840.940000</td>\n",
       "      <td>2390.430000</td>\n",
       "      <td>192.304445</td>\n",
       "      <td>6.806000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>33.000000</td>\n",
       "      <td>74.000000</td>\n",
       "      <td>202505.000000</td>\n",
       "      <td>67.350000</td>\n",
       "      <td>3.766000</td>\n",
       "      <td>9439.140000</td>\n",
       "      <td>2735.670000</td>\n",
       "      <td>272.580000</td>\n",
       "      <td>3096.920000</td>\n",
       "      <td>4227.270000</td>\n",
       "      <td>223.244532</td>\n",
       "      <td>8.036000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>45.000000</td>\n",
       "      <td>99.000000</td>\n",
       "      <td>219622.000000</td>\n",
       "      <td>101.950000</td>\n",
       "      <td>4.125000</td>\n",
       "      <td>103184.980000</td>\n",
       "      <td>71074.170000</td>\n",
       "      <td>149483.310000</td>\n",
       "      <td>65344.640000</td>\n",
       "      <td>771448.100000</td>\n",
       "      <td>228.976456</td>\n",
       "      <td>10.199000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Store           Dept           Size    Temperature  \\\n",
       "count  115064.000000  115064.000000  115064.000000  115064.000000   \n",
       "mean       22.238207      44.339524  136497.688921      53.941804   \n",
       "std        12.809930      30.656410   61106.926438      18.724153   \n",
       "min         1.000000       1.000000   34875.000000      -7.290000   \n",
       "25%        11.000000      18.000000   93638.000000      39.820000   \n",
       "50%        22.000000      37.000000  140167.000000      54.470000   \n",
       "75%        33.000000      74.000000  202505.000000      67.350000   \n",
       "max        45.000000      99.000000  219622.000000     101.950000   \n",
       "\n",
       "          Fuel_Price      MarkDown1     MarkDown2      MarkDown3  \\\n",
       "count  115064.000000  114915.000000  86437.000000  105235.000000   \n",
       "mean        3.581546    7689.216439   3734.051729    2403.088666   \n",
       "std         0.239442   10698.760716   8323.495014   13767.939313   \n",
       "min         2.872000   -2781.450000    -35.740000    -179.260000   \n",
       "25%         3.431000    1966.460000    180.350000      15.100000   \n",
       "50%         3.606000    4842.290000    742.590000      78.260000   \n",
       "75%         3.766000    9439.140000   2735.670000     272.580000   \n",
       "max         4.125000  103184.980000  71074.170000  149483.310000   \n",
       "\n",
       "           MarkDown4      MarkDown5           CPI  Unemployment  \n",
       "count  102176.000000  115064.000000  76902.000000  76902.000000  \n",
       "mean     3356.219071    3922.681189    176.961347      6.868733  \n",
       "std      7570.501545   19445.150745     41.239967      1.583427  \n",
       "min         0.220000    -185.170000    131.236226      3.684000  \n",
       "25%       155.460000    1309.300000    138.402033      5.771000  \n",
       "50%       840.940000    2390.430000    192.304445      6.806000  \n",
       "75%      3096.920000    4227.270000    223.244532      8.036000  \n",
       "max     65344.640000  771448.100000    228.976456     10.199000  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testdata.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "test=testdata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unemployment    38162\n",
       "CPI             38162\n",
       "MarkDown2       28627\n",
       "MarkDown4       12888\n",
       "MarkDown3        9829\n",
       "MarkDown1         149\n",
       "MarkDown5           0\n",
       "Fuel_Price          0\n",
       "Temperature         0\n",
       "Size                0\n",
       "Type                0\n",
       "Dept                0\n",
       "IsHoliday           0\n",
       "Date                0\n",
       "Store               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testdata.isnull().sum(axis=0).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replacing empty CPI and Unemployment values with mean\n",
    "testdata['CPI']=testdata.groupby(['Dept'])['CPI'].transform(lambda x: x.fillna(x.mean()))\n",
    "testdata['Unemployment']=testdata.groupby(['Dept'])['Unemployment'].transform(lambda x: x.fillna(x.mean()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replacing null values with 0\n",
    "testdata=testdata.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata['Date']=pd.to_datetime(testdata['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata['Month']=pd.DatetimeIndex(testdata['Date']).month\n",
    "testdata['Month']=testdata['Month'].apply(lambda x: calendar.month_abbr[x])\n",
    "testdata['Year']=pd.DatetimeIndex(testdata['Date']).year\n",
    "testdata['Day']=pd.DatetimeIndex(testdata['Date']).day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata=(testdata.join(pd.get_dummies(testdata.Month)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata.update(testdata.drop(\"Month\",axis=1,inplace=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata['IsHoliday']=testdata['IsHoliday'].map({\"True\": 1, \"False\": 0})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata=testdata.join(pd.get_dummies(testdata.Type,prefix=\"Type_\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata.update(testdata.drop(\"Type\",axis=1,inplace=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata['Super_Bowl']=np.where(((testdata['Date']==datetime.datetime(2010, 2, 12)) | (testdata['Date']==datetime.datetime(2011, 2, 11)) | (testdata['Date']==datetime.datetime(2012, 2, 10)) | (testdata['Date']==datetime.datetime(2013, 2, 8))),1,0)\n",
    "testdata['Labour_Day']=np.where((testdata['Date']==datetime.datetime(2010, 9, 10)) | (testdata['Date']==datetime.datetime(2011, 9, 9)) | (testdata['Date']==datetime.datetime(2012, 9, 7)) | (testdata['Date']==datetime.datetime(2013, 9, 6)),1,0)\n",
    "testdata['Thanksgiving']=np.where((testdata['Date']==datetime.datetime(2010, 11, 26)) | (testdata['Date']==datetime.datetime(2011, 11, 25)) | (testdata['Date']==datetime.datetime(2012, 11, 23)) | (testdata['Date']==datetime.datetime(2013, 11, 29)),1,0)\n",
    "testdata['Christmas']=np.where((testdata['Date']==datetime.datetime(2010, 12, 31)) | (testdata['Date']==datetime.datetime(2011, 12, 30)) | (testdata['Date']==datetime.datetime(2012, 12, 28)) | (testdata['Date']==datetime.datetime(2013, 12, 27)),1,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata['IsHoliday']=testdata['IsHoliday']|testdata['Super_Bowl']|testdata['Labour_Day']|testdata['Thanksgiving']|testdata['Christmas']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata=testdata.drop(columns=[\"Super_Bowl\",\"Labour_Day\",\"Thanksgiving\",\"Christmas\",\"Date\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "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</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>Dept</th>\n",
       "      <th>Size</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Fuel_Price</th>\n",
       "      <th>MarkDown1</th>\n",
       "      <th>MarkDown2</th>\n",
       "      <th>MarkDown3</th>\n",
       "      <th>MarkDown4</th>\n",
       "      <th>...</th>\n",
       "      <th>Feb</th>\n",
       "      <th>Jan</th>\n",
       "      <th>Jul</th>\n",
       "      <th>Jun</th>\n",
       "      <th>Mar</th>\n",
       "      <th>May</th>\n",
       "      <th>Nov</th>\n",
       "      <th>Type__A</th>\n",
       "      <th>Type__B</th>\n",
       "      <th>Type__C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>126512</td>\n",
       "      <td>70.79</td>\n",
       "      <td>4.099</td>\n",
       "      <td>25680.2</td>\n",
       "      <td>6037.06</td>\n",
       "      <td>44.68</td>\n",
       "      <td>17412.04</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>72</td>\n",
       "      <td>126512</td>\n",
       "      <td>70.79</td>\n",
       "      <td>4.099</td>\n",
       "      <td>25680.2</td>\n",
       "      <td>6037.06</td>\n",
       "      <td>44.68</td>\n",
       "      <td>17412.04</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>71</td>\n",
       "      <td>126512</td>\n",
       "      <td>70.79</td>\n",
       "      <td>4.099</td>\n",
       "      <td>25680.2</td>\n",
       "      <td>6037.06</td>\n",
       "      <td>44.68</td>\n",
       "      <td>17412.04</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>21</td>\n",
       "      <td>126512</td>\n",
       "      <td>70.79</td>\n",
       "      <td>4.099</td>\n",
       "      <td>25680.2</td>\n",
       "      <td>6037.06</td>\n",
       "      <td>44.68</td>\n",
       "      <td>17412.04</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>67</td>\n",
       "      <td>126512</td>\n",
       "      <td>70.79</td>\n",
       "      <td>4.099</td>\n",
       "      <td>25680.2</td>\n",
       "      <td>6037.06</td>\n",
       "      <td>44.68</td>\n",
       "      <td>17412.04</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store  IsHoliday  Dept    Size  Temperature  Fuel_Price  MarkDown1  \\\n",
       "0     10      False     1  126512        70.79       4.099    25680.2   \n",
       "1     10      False    72  126512        70.79       4.099    25680.2   \n",
       "2     10      False    71  126512        70.79       4.099    25680.2   \n",
       "3     10      False    21  126512        70.79       4.099    25680.2   \n",
       "4     10      False    67  126512        70.79       4.099    25680.2   \n",
       "\n",
       "   MarkDown2  MarkDown3  MarkDown4   ...     Feb  Jan  Jul  Jun  Mar  May  \\\n",
       "0    6037.06      44.68   17412.04   ...       0    0    0    0    0    0   \n",
       "1    6037.06      44.68   17412.04   ...       0    0    0    0    0    0   \n",
       "2    6037.06      44.68   17412.04   ...       0    0    0    0    0    0   \n",
       "3    6037.06      44.68   17412.04   ...       0    0    0    0    0    0   \n",
       "4    6037.06      44.68   17412.04   ...       0    0    0    0    0    0   \n",
       "\n",
       "   Nov  Type__A  Type__B  Type__C  \n",
       "0    1        0        1        0  \n",
       "1    1        0        1        0  \n",
       "2    1        0        1        0  \n",
       "3    1        0        1        0  \n",
       "4    1        0        1        0  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testdata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata['Aug']=0\n",
    "testdata['Sep']=0\n",
    "testdata['Oct']=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdata=testdata[x_test.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Predicting values for testdata with model retrieved\n",
    "pred=pd.DataFrame(data=model.predict(testdata),index=testdata.index,columns=[\"Weekly_Sales\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred.index=test[\"Store\"].astype(str)+\"_\"+test[\"Dept\"].astype(str)+\"_\"+test[\"Date\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred.index.set_names(names=\"Id\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Exporting the required predicted values as csv file\n",
    "pred.to_csv(\"Predictions.csv\")"
   ]
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}