3.3_Prepr_train_features_extraction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data preprocessing: Feature Engineering\n",
    "\n",
    "**NOTEBOOK GOAL**: Extract valuable features that can counts for models training\n",
    "\n",
    "**DATASET TRANSFORMATION**: `preprocessed2_OHE_imputation_(train|test).csv` >> `preprocessed_(train|test).csv`\n",
    "\n",
    "**INDEX**\n",
    "\n",
    "- [Deletion of IsOpen = 0 rows](#Deletion-of-IsOpen-=-0-rows)\n",
    "- [MeanStoreSales feature](#MeanStoreSales-feature)\n",
    "- [MeanReagionSales feature](#MeanReagionSales feature)\n",
    "- [Day of the Week considering days adjacency](#Day-of-the-Week-considering-days-adjacency) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reoder variables to work on train or test dataset\n",
    "work_on = 'test'\n",
    "#work_on = 'train'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd\n",
    "\n",
    "features_to_print = ['StoreID', 'Date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "_cell_guid": "da09e07b-fdfc-30b1-dfff-ad7dc625d15a"
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('dataset/preprocessed2_OHE_train.csv')\n",
    "if work_on == 'test':\n",
    "    df_test = pd.read_csv('dataset/preprocessed2_OHE_test.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deletion of IsOpen = 0 rows\n",
    "\n",
    "Let's delete the rows of store closed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>StoreID</th>\n",
       "      <th>Date</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>IsOpen</th>\n",
       "      <th>HasPromotions</th>\n",
       "      <th>StoreType</th>\n",
       "      <th>AssortmentType</th>\n",
       "      <th>NearestCompetitor</th>\n",
       "      <th>Region</th>\n",
       "      <th>NumberOfCustomers</th>\n",
       "      <th>...</th>\n",
       "      <th>StoreType_StandardMarket</th>\n",
       "      <th>StoreType_ShoppingCenter</th>\n",
       "      <th>AssortmentType_General</th>\n",
       "      <th>AssortmentType_WithNFDept</th>\n",
       "      <th>AssortmentType_WithFishDept</th>\n",
       "      <th>Events_Fog</th>\n",
       "      <th>Events_Hail</th>\n",
       "      <th>Events_Rain</th>\n",
       "      <th>Events_Snow</th>\n",
       "      <th>Events_Thunderstorm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Hyper Market</td>\n",
       "      <td>General</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>495</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>02/03/2016</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Hyper Market</td>\n",
       "      <td>General</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>608</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>1000</td>\n",
       "      <td>04/03/2016</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Hyper Market</td>\n",
       "      <td>General</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>665</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000</td>\n",
       "      <td>05/03/2016</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Hyper Market</td>\n",
       "      <td>General</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>630</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000</td>\n",
       "      <td>06/03/2016</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Hyper Market</td>\n",
       "      <td>General</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID        Date  IsHoliday  IsOpen  HasPromotions     StoreType  \\\n",
       "0     1000  01/03/2016          0       1              0  Hyper Market   \n",
       "1     1000  02/03/2016          0       1              0  Hyper Market   \n",
       "2     1000  04/03/2016          0       1              0  Hyper Market   \n",
       "3     1000  05/03/2016          0       1              0  Hyper Market   \n",
       "4     1000  06/03/2016          0       0              0  Hyper Market   \n",
       "\n",
       "  AssortmentType  NearestCompetitor  Region  NumberOfCustomers  \\\n",
       "0        General                326       7                495   \n",
       "1        General                326       7                608   \n",
       "2        General                326       7                665   \n",
       "3        General                326       7                630   \n",
       "4        General                326       7                  0   \n",
       "\n",
       "          ...           StoreType_StandardMarket  StoreType_ShoppingCenter  \\\n",
       "0         ...                                  0                         0   \n",
       "1         ...                                  0                         0   \n",
       "2         ...                                  0                         0   \n",
       "3         ...                                  0                         0   \n",
       "4         ...                                  0                         0   \n",
       "\n",
       "   AssortmentType_General  AssortmentType_WithNFDept  \\\n",
       "0                       1                          0   \n",
       "1                       1                          0   \n",
       "2                       1                          0   \n",
       "3                       1                          0   \n",
       "4                       1                          0   \n",
       "\n",
       "   AssortmentType_WithFishDept Events_Fog  Events_Hail  Events_Rain  \\\n",
       "0                            0          0            0            1   \n",
       "1                            0          0            0            0   \n",
       "2                            0          0            0            1   \n",
       "3                            0          0            0            0   \n",
       "4                            0          0            0            0   \n",
       "\n",
       "   Events_Snow  Events_Thunderstorm  \n",
       "0            1                    0  \n",
       "1            1                    0  \n",
       "2            0                    0  \n",
       "3            0                    0  \n",
       "4            0                    0  \n",
       "\n",
       "[5 rows x 52 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df.IsOpen==1]\n",
    "if work_on == 'test':\n",
    "    df_test = df_test[df_test.IsOpen==1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>StoreID</th>\n",
       "      <th>Date</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>IsOpen</th>\n",
       "      <th>HasPromotions</th>\n",
       "      <th>StoreType</th>\n",
       "      <th>AssortmentType</th>\n",
       "      <th>NearestCompetitor</th>\n",
       "      <th>Region</th>\n",
       "      <th>NumberOfCustomers</th>\n",
       "      <th>...</th>\n",
       "      <th>StoreType_StandardMarket</th>\n",
       "      <th>StoreType_ShoppingCenter</th>\n",
       "      <th>AssortmentType_General</th>\n",
       "      <th>AssortmentType_WithNFDept</th>\n",
       "      <th>AssortmentType_WithFishDept</th>\n",
       "      <th>Events_Fog</th>\n",
       "      <th>Events_Hail</th>\n",
       "      <th>Events_Rain</th>\n",
       "      <th>Events_Snow</th>\n",
       "      <th>Events_Thunderstorm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Hyper Market</td>\n",
       "      <td>General</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>495</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>02/03/2016</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Hyper Market</td>\n",
       "      <td>General</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>608</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>1000</td>\n",
       "      <td>04/03/2016</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Hyper Market</td>\n",
       "      <td>General</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>665</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000</td>\n",
       "      <td>05/03/2016</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Hyper Market</td>\n",
       "      <td>General</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>630</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1000</td>\n",
       "      <td>07/03/2016</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Hyper Market</td>\n",
       "      <td>General</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>763</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID        Date  IsHoliday  IsOpen  HasPromotions     StoreType  \\\n",
       "0     1000  01/03/2016          0       1              0  Hyper Market   \n",
       "1     1000  02/03/2016          0       1              0  Hyper Market   \n",
       "2     1000  04/03/2016          0       1              0  Hyper Market   \n",
       "3     1000  05/03/2016          0       1              0  Hyper Market   \n",
       "5     1000  07/03/2016          0       1              1  Hyper Market   \n",
       "\n",
       "  AssortmentType  NearestCompetitor  Region  NumberOfCustomers  \\\n",
       "0        General                326       7                495   \n",
       "1        General                326       7                608   \n",
       "2        General                326       7                665   \n",
       "3        General                326       7                630   \n",
       "5        General                326       7                763   \n",
       "\n",
       "          ...           StoreType_StandardMarket  StoreType_ShoppingCenter  \\\n",
       "0         ...                                  0                         0   \n",
       "1         ...                                  0                         0   \n",
       "2         ...                                  0                         0   \n",
       "3         ...                                  0                         0   \n",
       "5         ...                                  0                         0   \n",
       "\n",
       "   AssortmentType_General  AssortmentType_WithNFDept  \\\n",
       "0                       1                          0   \n",
       "1                       1                          0   \n",
       "2                       1                          0   \n",
       "3                       1                          0   \n",
       "5                       1                          0   \n",
       "\n",
       "   AssortmentType_WithFishDept Events_Fog  Events_Hail  Events_Rain  \\\n",
       "0                            0          0            0            1   \n",
       "1                            0          0            0            0   \n",
       "2                            0          0            0            1   \n",
       "3                            0          0            0            0   \n",
       "5                            0          0            0            0   \n",
       "\n",
       "   Events_Snow  Events_Thunderstorm  \n",
       "0            1                    0  \n",
       "1            1                    0  \n",
       "2            0                    0  \n",
       "3            0                    0  \n",
       "5            0                    0  \n",
       "\n",
       "[5 rows x 52 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_test.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['IsOpen'], axis=1, inplace=True)\n",
    "if work_on == 'test':\n",
    "    df_test.drop(['IsOpen'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_test.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MeanStoreSales feature\n",
    "\n",
    "Add the mean of the sales of the store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_mean = df.groupby('StoreID')['NumberOfSales'].mean()\n",
    "def mean_sales(value):\n",
    "    # since stores ID are from 1000 to 1749 let's subtract 1000\n",
    "    return df_mean.iloc[value-1000]\n",
    "\n",
    "df['MeanStoreSales'] = df.StoreID.apply(mean_sales)\n",
    "if work_on == 'test':\n",
    "    df_test['MeanStoreSales'] = df_test.StoreID.apply(mean_sales)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df[10000:10010].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(749,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "StoreID\n",
       "1000    7675.446488\n",
       "1001    3154.465753\n",
       "1002    4968.047776\n",
       "1003    5409.347107\n",
       "1004    4046.109865\n",
       "Name: NumberOfSales, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_mean.shape)\n",
    "df_mean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MeanReagionSales feature\n",
    "\n",
    "Add the mean of sales for the region (regardless of the store)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_mean_reg = df.groupby('Region')['NumberOfSales'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def mean_sales_region(value):\n",
    "    return df_mean_reg.iloc[value]\n",
    "\n",
    "df['MeanRegionSales'] = df.Region.apply(mean_sales_region)\n",
    "if work_on == 'test':\n",
    "    df_test['MeanRegionSales'] = df_test.Region.apply(mean_sales_region)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>729</th>\n",
       "      <th>1458</th>\n",
       "      <th>2187</th>\n",
       "      <th>2916</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>StoreID</th>\n",
       "      <td>1000</td>\n",
       "      <td>1001</td>\n",
       "      <td>1002</td>\n",
       "      <td>1003</td>\n",
       "      <td>1004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>01/03/2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfweek</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanStoreSales</th>\n",
       "      <td>7675.45</td>\n",
       "      <td>3154.47</td>\n",
       "      <td>4968.05</td>\n",
       "      <td>5409.35</td>\n",
       "      <td>4046.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionSales</th>\n",
       "      <td>4702.75</td>\n",
       "      <td>4885.4</td>\n",
       "      <td>5839.41</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4679.27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       0           729         1458        2187        2916\n",
       "StoreID                1000        1001        1002        1003        1004\n",
       "Date             01/03/2016  01/03/2016  01/03/2016  01/03/2016  01/03/2016\n",
       "D_DayOfweek               1           1           1           1           1\n",
       "MeanStoreSales      7675.45     3154.47     4968.05     5409.35     4046.11\n",
       "MeanRegionSales     4702.75      4885.4     5839.41     4702.75     4679.27"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features_to_print += ['D_DayOfweek', 'MeanStoreSales', 'MeanRegionSales']\n",
    "\n",
    "df[df.Date==\"01/03/2016\"][features_to_print].head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_test[df_test.Date==\"01/03/2018\"][features_to_print].head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MeanCustomers feature\n",
    "\n",
    "Add the mean of the sales of the store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_mean = df.groupby('StoreID')['NumberOfCustomers'].mean()\n",
    "def mean_cust(value):\n",
    "    # since stores ID are from 1000 to 1749 let's subtract 1000\n",
    "    return df_mean.iloc[value-1000]\n",
    "\n",
    "df['MeanCustomers'] = df.StoreID.apply(mean_cust)\n",
    "if work_on == 'test':\n",
    "    df_test['MeanCustomers'] = df_test.StoreID.apply(mean_cust)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(749,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "StoreID\n",
       "1000    622.769231\n",
       "1001    194.358121\n",
       "1002    379.586491\n",
       "1003    247.581818\n",
       "1004    203.199552\n",
       "Name: NumberOfCustomers, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_mean.shape)\n",
    "df_mean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MeanCustomers per region feature\n",
    "\n",
    "Add the mean of sales for the region (regardless of the store)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_mean_reg = df.groupby('Region')['NumberOfCustomers'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def mean_cust_region(value):\n",
    "    return df_mean_reg.iloc[value]\n",
    "\n",
    "df['MeanRegionCustomers'] = df.Region.apply(mean_cust_region)\n",
    "if work_on == 'test':\n",
    "    df_test['MeanRegionCustomers'] = df_test.Region.apply(mean_cust_region)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>729</th>\n",
       "      <th>1458</th>\n",
       "      <th>2187</th>\n",
       "      <th>2916</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>StoreID</th>\n",
       "      <td>1000</td>\n",
       "      <td>1001</td>\n",
       "      <td>1002</td>\n",
       "      <td>1003</td>\n",
       "      <td>1004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>01/03/2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfweek</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanStoreSales</th>\n",
       "      <td>7675.45</td>\n",
       "      <td>3154.47</td>\n",
       "      <td>4968.05</td>\n",
       "      <td>5409.35</td>\n",
       "      <td>4046.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionSales</th>\n",
       "      <td>4702.75</td>\n",
       "      <td>4885.4</td>\n",
       "      <td>5839.41</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4679.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfweek</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanStoreSales</th>\n",
       "      <td>7675.45</td>\n",
       "      <td>3154.47</td>\n",
       "      <td>4968.05</td>\n",
       "      <td>5409.35</td>\n",
       "      <td>4046.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionSales</th>\n",
       "      <td>4702.75</td>\n",
       "      <td>4885.4</td>\n",
       "      <td>5839.41</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4679.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanCustomers</th>\n",
       "      <td>622.769</td>\n",
       "      <td>194.358</td>\n",
       "      <td>379.586</td>\n",
       "      <td>247.582</td>\n",
       "      <td>203.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionCustomers</th>\n",
       "      <td>284.209</td>\n",
       "      <td>316.367</td>\n",
       "      <td>406.117</td>\n",
       "      <td>284.209</td>\n",
       "      <td>258.535</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           0           729         1458        2187  \\\n",
       "StoreID                    1000        1001        1002        1003   \n",
       "Date                 01/03/2016  01/03/2016  01/03/2016  01/03/2016   \n",
       "D_DayOfweek                   1           1           1           1   \n",
       "MeanStoreSales          7675.45     3154.47     4968.05     5409.35   \n",
       "MeanRegionSales         4702.75      4885.4     5839.41     4702.75   \n",
       "D_DayOfweek                   1           1           1           1   \n",
       "MeanStoreSales          7675.45     3154.47     4968.05     5409.35   \n",
       "MeanRegionSales         4702.75      4885.4     5839.41     4702.75   \n",
       "MeanCustomers           622.769     194.358     379.586     247.582   \n",
       "MeanRegionCustomers     284.209     316.367     406.117     284.209   \n",
       "\n",
       "                           2916  \n",
       "StoreID                    1004  \n",
       "Date                 01/03/2016  \n",
       "D_DayOfweek                   1  \n",
       "MeanStoreSales          4046.11  \n",
       "MeanRegionSales         4679.27  \n",
       "D_DayOfweek                   1  \n",
       "MeanStoreSales          4046.11  \n",
       "MeanRegionSales         4679.27  \n",
       "MeanCustomers             203.2  \n",
       "MeanRegionCustomers     258.535  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features_to_print += ['D_DayOfweek', 'MeanStoreSales', 'MeanRegionSales','MeanCustomers', 'MeanRegionCustomers']\n",
    "\n",
    "df[df.Date==\"01/03/2016\"][features_to_print].head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>61</th>\n",
       "      <th>122</th>\n",
       "      <th>183</th>\n",
       "      <th>244</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>StoreID</th>\n",
       "      <td>1000</td>\n",
       "      <td>1001</td>\n",
       "      <td>1002</td>\n",
       "      <td>1003</td>\n",
       "      <td>1004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>01/03/2018</td>\n",
       "      <td>01/03/2018</td>\n",
       "      <td>01/03/2018</td>\n",
       "      <td>01/03/2018</td>\n",
       "      <td>01/03/2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfweek</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanStoreSales</th>\n",
       "      <td>7675.45</td>\n",
       "      <td>3154.47</td>\n",
       "      <td>4968.05</td>\n",
       "      <td>5409.35</td>\n",
       "      <td>4046.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionSales</th>\n",
       "      <td>4702.75</td>\n",
       "      <td>4885.4</td>\n",
       "      <td>5839.41</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4679.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfweek</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanStoreSales</th>\n",
       "      <td>7675.45</td>\n",
       "      <td>3154.47</td>\n",
       "      <td>4968.05</td>\n",
       "      <td>5409.35</td>\n",
       "      <td>4046.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionSales</th>\n",
       "      <td>4702.75</td>\n",
       "      <td>4885.4</td>\n",
       "      <td>5839.41</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4679.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanCustomers</th>\n",
       "      <td>622.769</td>\n",
       "      <td>194.358</td>\n",
       "      <td>379.586</td>\n",
       "      <td>247.582</td>\n",
       "      <td>203.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionCustomers</th>\n",
       "      <td>284.209</td>\n",
       "      <td>316.367</td>\n",
       "      <td>406.117</td>\n",
       "      <td>284.209</td>\n",
       "      <td>258.535</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            0           61          122         183  \\\n",
       "StoreID                    1000        1001        1002        1003   \n",
       "Date                 01/03/2018  01/03/2018  01/03/2018  01/03/2018   \n",
       "D_DayOfweek                   3           3           3           3   \n",
       "MeanStoreSales          7675.45     3154.47     4968.05     5409.35   \n",
       "MeanRegionSales         4702.75      4885.4     5839.41     4702.75   \n",
       "D_DayOfweek                   3           3           3           3   \n",
       "MeanStoreSales          7675.45     3154.47     4968.05     5409.35   \n",
       "MeanRegionSales         4702.75      4885.4     5839.41     4702.75   \n",
       "MeanCustomers           622.769     194.358     379.586     247.582   \n",
       "MeanRegionCustomers     284.209     316.367     406.117     284.209   \n",
       "\n",
       "                            244  \n",
       "StoreID                    1004  \n",
       "Date                 01/03/2018  \n",
       "D_DayOfweek                   3  \n",
       "MeanStoreSales          4046.11  \n",
       "MeanRegionSales         4679.27  \n",
       "D_DayOfweek                   3  \n",
       "MeanStoreSales          4046.11  \n",
       "MeanRegionSales         4679.27  \n",
       "MeanCustomers             203.2  \n",
       "MeanRegionCustomers     258.535  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test[df_test.Date==\"01/03/2018\"][features_to_print].head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Day of the Week considering days adjacency\n",
    "\n",
    "<https://www.reddit.com/r/MachineLearning/comments/2hzuj5/how_do_i_encode_day_of_the_week_as_a_predictor/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0, 1, 2, 3, 4, 5, 6]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted(df['D_DayOfweek'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>StoreID</th>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>01/03/2016</td>\n",
       "      <td>02/03/2016</td>\n",
       "      <td>04/03/2016</td>\n",
       "      <td>05/03/2016</td>\n",
       "      <td>07/03/2016</td>\n",
       "      <td>08/03/2016</td>\n",
       "      <td>09/03/2016</td>\n",
       "      <td>10/03/2016</td>\n",
       "      <td>11/03/2016</td>\n",
       "      <td>12/03/2016</td>\n",
       "      <td>14/03/2016</td>\n",
       "      <td>15/03/2016</td>\n",
       "      <td>16/03/2016</td>\n",
       "      <td>17/03/2016</td>\n",
       "      <td>18/03/2016</td>\n",
       "      <td>19/03/2016</td>\n",
       "      <td>21/03/2016</td>\n",
       "      <td>22/03/2016</td>\n",
       "      <td>23/03/2016</td>\n",
       "      <td>24/03/2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfweek</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanStoreSales</th>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionSales</th>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfweek</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanStoreSales</th>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "      <td>7675.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionSales</th>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "      <td>4702.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanCustomers</th>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "      <td>622.769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionCustomers</th>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "      <td>284.209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rad_ang</th>\n",
       "      <td>0.897598</td>\n",
       "      <td>1.7952</td>\n",
       "      <td>3.59039</td>\n",
       "      <td>4.48799</td>\n",
       "      <td>0</td>\n",
       "      <td>0.897598</td>\n",
       "      <td>1.7952</td>\n",
       "      <td>2.69279</td>\n",
       "      <td>3.59039</td>\n",
       "      <td>4.48799</td>\n",
       "      <td>0</td>\n",
       "      <td>0.897598</td>\n",
       "      <td>1.7952</td>\n",
       "      <td>2.69279</td>\n",
       "      <td>3.59039</td>\n",
       "      <td>4.48799</td>\n",
       "      <td>0</td>\n",
       "      <td>0.897598</td>\n",
       "      <td>1.7952</td>\n",
       "      <td>2.69279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfWeek_cos</th>\n",
       "      <td>0.62349</td>\n",
       "      <td>-0.222521</td>\n",
       "      <td>-0.900969</td>\n",
       "      <td>-0.222521</td>\n",
       "      <td>1</td>\n",
       "      <td>0.62349</td>\n",
       "      <td>-0.222521</td>\n",
       "      <td>-0.900969</td>\n",
       "      <td>-0.900969</td>\n",
       "      <td>-0.222521</td>\n",
       "      <td>1</td>\n",
       "      <td>0.62349</td>\n",
       "      <td>-0.222521</td>\n",
       "      <td>-0.900969</td>\n",
       "      <td>-0.900969</td>\n",
       "      <td>-0.222521</td>\n",
       "      <td>1</td>\n",
       "      <td>0.62349</td>\n",
       "      <td>-0.222521</td>\n",
       "      <td>-0.900969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfWeek_sin</th>\n",
       "      <td>0.781831</td>\n",
       "      <td>0.974928</td>\n",
       "      <td>-0.433884</td>\n",
       "      <td>-0.974928</td>\n",
       "      <td>0</td>\n",
       "      <td>0.781831</td>\n",
       "      <td>0.974928</td>\n",
       "      <td>0.433884</td>\n",
       "      <td>-0.433884</td>\n",
       "      <td>-0.974928</td>\n",
       "      <td>0</td>\n",
       "      <td>0.781831</td>\n",
       "      <td>0.974928</td>\n",
       "      <td>0.433884</td>\n",
       "      <td>-0.433884</td>\n",
       "      <td>-0.974928</td>\n",
       "      <td>0</td>\n",
       "      <td>0.781831</td>\n",
       "      <td>0.974928</td>\n",
       "      <td>0.433884</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             0           1           2           3   \\\n",
       "StoreID                    1000        1000        1000        1000   \n",
       "Date                 01/03/2016  02/03/2016  04/03/2016  05/03/2016   \n",
       "D_DayOfweek                   1           2           4           5   \n",
       "MeanStoreSales          7675.45     7675.45     7675.45     7675.45   \n",
       "MeanRegionSales         4702.75     4702.75     4702.75     4702.75   \n",
       "D_DayOfweek                   1           2           4           5   \n",
       "MeanStoreSales          7675.45     7675.45     7675.45     7675.45   \n",
       "MeanRegionSales         4702.75     4702.75     4702.75     4702.75   \n",
       "MeanCustomers           622.769     622.769     622.769     622.769   \n",
       "MeanRegionCustomers     284.209     284.209     284.209     284.209   \n",
       "rad_ang                0.897598      1.7952     3.59039     4.48799   \n",
       "D_DayOfWeek_cos         0.62349   -0.222521   -0.900969   -0.222521   \n",
       "D_DayOfWeek_sin        0.781831    0.974928   -0.433884   -0.974928   \n",
       "\n",
       "                             5           6           7           8   \\\n",
       "StoreID                    1000        1000        1000        1000   \n",
       "Date                 07/03/2016  08/03/2016  09/03/2016  10/03/2016   \n",
       "D_DayOfweek                   0           1           2           3   \n",
       "MeanStoreSales          7675.45     7675.45     7675.45     7675.45   \n",
       "MeanRegionSales         4702.75     4702.75     4702.75     4702.75   \n",
       "D_DayOfweek                   0           1           2           3   \n",
       "MeanStoreSales          7675.45     7675.45     7675.45     7675.45   \n",
       "MeanRegionSales         4702.75     4702.75     4702.75     4702.75   \n",
       "MeanCustomers           622.769     622.769     622.769     622.769   \n",
       "MeanRegionCustomers     284.209     284.209     284.209     284.209   \n",
       "rad_ang                       0    0.897598      1.7952     2.69279   \n",
       "D_DayOfWeek_cos               1     0.62349   -0.222521   -0.900969   \n",
       "D_DayOfWeek_sin               0    0.781831    0.974928    0.433884   \n",
       "\n",
       "                             9           10          12          13  \\\n",
       "StoreID                    1000        1000        1000        1000   \n",
       "Date                 11/03/2016  12/03/2016  14/03/2016  15/03/2016   \n",
       "D_DayOfweek                   4           5           0           1   \n",
       "MeanStoreSales          7675.45     7675.45     7675.45     7675.45   \n",
       "MeanRegionSales         4702.75     4702.75     4702.75     4702.75   \n",
       "D_DayOfweek                   4           5           0           1   \n",
       "MeanStoreSales          7675.45     7675.45     7675.45     7675.45   \n",
       "MeanRegionSales         4702.75     4702.75     4702.75     4702.75   \n",
       "MeanCustomers           622.769     622.769     622.769     622.769   \n",
       "MeanRegionCustomers     284.209     284.209     284.209     284.209   \n",
       "rad_ang                 3.59039     4.48799           0    0.897598   \n",
       "D_DayOfWeek_cos       -0.900969   -0.222521           1     0.62349   \n",
       "D_DayOfWeek_sin       -0.433884   -0.974928           0    0.781831   \n",
       "\n",
       "                             14          15          16          17  \\\n",
       "StoreID                    1000        1000        1000        1000   \n",
       "Date                 16/03/2016  17/03/2016  18/03/2016  19/03/2016   \n",
       "D_DayOfweek                   2           3           4           5   \n",
       "MeanStoreSales          7675.45     7675.45     7675.45     7675.45   \n",
       "MeanRegionSales         4702.75     4702.75     4702.75     4702.75   \n",
       "D_DayOfweek                   2           3           4           5   \n",
       "MeanStoreSales          7675.45     7675.45     7675.45     7675.45   \n",
       "MeanRegionSales         4702.75     4702.75     4702.75     4702.75   \n",
       "MeanCustomers           622.769     622.769     622.769     622.769   \n",
       "MeanRegionCustomers     284.209     284.209     284.209     284.209   \n",
       "rad_ang                  1.7952     2.69279     3.59039     4.48799   \n",
       "D_DayOfWeek_cos       -0.222521   -0.900969   -0.900969   -0.222521   \n",
       "D_DayOfWeek_sin        0.974928    0.433884   -0.433884   -0.974928   \n",
       "\n",
       "                             19          20          21          22  \n",
       "StoreID                    1000        1000        1000        1000  \n",
       "Date                 21/03/2016  22/03/2016  23/03/2016  24/03/2016  \n",
       "D_DayOfweek                   0           1           2           3  \n",
       "MeanStoreSales          7675.45     7675.45     7675.45     7675.45  \n",
       "MeanRegionSales         4702.75     4702.75     4702.75     4702.75  \n",
       "D_DayOfweek                   0           1           2           3  \n",
       "MeanStoreSales          7675.45     7675.45     7675.45     7675.45  \n",
       "MeanRegionSales         4702.75     4702.75     4702.75     4702.75  \n",
       "MeanCustomers           622.769     622.769     622.769     622.769  \n",
       "MeanRegionCustomers     284.209     284.209     284.209     284.209  \n",
       "rad_ang                       0    0.897598      1.7952     2.69279  \n",
       "D_DayOfWeek_cos               1     0.62349   -0.222521   -0.900969  \n",
       "D_DayOfWeek_sin               0    0.781831    0.974928    0.433884  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from math import pi, cos, sin\n",
    "\n",
    "# angle in rad\n",
    "df['rad_ang'] = (df['D_DayOfweek'] / 7) * (2 * pi)\n",
    "#df['D_DayOfWeek_cos'] = df.apply(lambda x: cos(x['rad_ang']), axis=1)\n",
    "#df['D_DayOfWeek_sin'] = df.apply(lambda x: sin(x['rad_ang']), axis=1)\n",
    "df['D_DayOfWeek_cos'] = df['rad_ang'].apply(lambda x: cos(x))\n",
    "df['D_DayOfWeek_sin'] = df['rad_ang'].apply(lambda x: sin(x))\n",
    "\n",
    "features_to_print  += ['rad_ang', 'D_DayOfWeek_cos', 'D_DayOfWeek_sin']\n",
    "df[features_to_print].head(20).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "if work_on == 'test':\n",
    "    df_test['rad_ang'] = (df_test['D_DayOfweek'] / 7) * (2 * pi)\n",
    "    #df['D_DayOfWeek_cos'] = df.apply(lambda x: cos(x['rad_ang']), axis=1)\n",
    "    #df['D_DayOfWeek_sin'] = df.apply(lambda x: sin(x['rad_ang']), axis=1)\n",
    "    df_test['D_DayOfWeek_cos'] = df_test['rad_ang'].apply(lambda x: cos(x))\n",
    "    df_test['D_DayOfWeek_sin'] = df_test['rad_ang'].apply(lambda x: sin(x))\n",
    "\n",
    "    features_to_print  += ['rad_ang', 'D_DayOfWeek_cos', 'D_DayOfWeek_sin']\n",
    "    df_test[features_to_print].head(20).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#features_to_print  += ['rad_ang', 'D_DayOfWeek_cos', 'D_DayOfWeek_sin']\n",
    "#df_test[features_to_print].head(20).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>StoreID</th>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>01/03/2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IsHoliday</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HasPromotions</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StoreType</th>\n",
       "      <td>Hyper Market</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AssortmentType</th>\n",
       "      <td>General</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NearestCompetitor</th>\n",
       "      <td>326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NumberOfCustomers</th>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NumberOfSales</th>\n",
       "      <td>5676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region_AreaKM2</th>\n",
       "      <td>9643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region_GDP</th>\n",
       "      <td>17130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region_PopulationK</th>\n",
       "      <td>2770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CloudCover</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Events</th>\n",
       "      <td>Rain-Snow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_Dew_PointC</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_Humidity</th>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_Sea_Level_PressurehPa</th>\n",
       "      <td>1032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_TemperatureC</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_VisibilityKm</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_Wind_SpeedKm_h</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_Dew_PointC</th>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_Humidity</th>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_Sea_Level_PressurehPa</th>\n",
       "      <td>1030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_TemperatureC</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_VisibilityKm</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_Wind_SpeedKm_h</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Min_Dew_PointC</th>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Min_Humidity</th>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Min_Sea_Level_PressurehPa</th>\n",
       "      <td>1029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Min_TemperatureC</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Min_VisibilitykM</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Precipitationmm</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WindDirDegrees</th>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_Day</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfYear</th>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_Month</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_Year</th>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfweek</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StoreType_SuperMarket</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StoreType_HyperMarket</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StoreType_StandardMarket</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StoreType_ShoppingCenter</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AssortmentType_General</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AssortmentType_WithNFDept</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AssortmentType_WithFishDept</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Events_Fog</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Events_Hail</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Events_Rain</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Events_Snow</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Events_Thunderstorm</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanStoreSales</th>\n",
       "      <td>7675.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionSales</th>\n",
       "      <td>4702.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanCustomers</th>\n",
       "      <td>622.769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanRegionCustomers</th>\n",
       "      <td>284.209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfWeek_cos</th>\n",
       "      <td>0.62349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D_DayOfWeek_sin</th>\n",
       "      <td>0.781831</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        0\n",
       "StoreID                              1000\n",
       "Date                           01/03/2016\n",
       "IsHoliday                               0\n",
       "HasPromotions                           0\n",
       "StoreType                    Hyper Market\n",
       "AssortmentType                    General\n",
       "NearestCompetitor                     326\n",
       "Region                                  7\n",
       "NumberOfCustomers                     495\n",
       "NumberOfSales                        5676\n",
       "Region_AreaKM2                       9643\n",
       "Region_GDP                          17130\n",
       "Region_PopulationK                   2770\n",
       "CloudCover                              8\n",
       "Events                          Rain-Snow\n",
       "Max_Dew_PointC                          1\n",
       "Max_Humidity                          100\n",
       "Max_Sea_Level_PressurehPa            1032\n",
       "Max_TemperatureC                        2\n",
       "Max_VisibilityKm                        0\n",
       "Max_Wind_SpeedKm_h                     21\n",
       "Mean_Dew_PointC                        -1\n",
       "Mean_Humidity                          82\n",
       "Mean_Sea_Level_PressurehPa           1030\n",
       "Mean_TemperatureC                       1\n",
       "Mean_VisibilityKm                       0\n",
       "Mean_Wind_SpeedKm_h                    16\n",
       "Min_Dew_PointC                         -2\n",
       "Min_Humidity                           70\n",
       "Min_Sea_Level_PressurehPa            1029\n",
       "Min_TemperatureC                        1\n",
       "Min_VisibilitykM                        0\n",
       "Precipitationmm                         0\n",
       "WindDirDegrees                         23\n",
       "D_Day                                   1\n",
       "D_DayOfYear                            61\n",
       "D_Month                                 3\n",
       "D_Year                               2016\n",
       "D_DayOfweek                             1\n",
       "StoreType_SuperMarket                   0\n",
       "StoreType_HyperMarket                   1\n",
       "StoreType_StandardMarket                0\n",
       "StoreType_ShoppingCenter                0\n",
       "AssortmentType_General                  1\n",
       "AssortmentType_WithNFDept               0\n",
       "AssortmentType_WithFishDept             0\n",
       "Events_Fog                              0\n",
       "Events_Hail                             0\n",
       "Events_Rain                             1\n",
       "Events_Snow                             1\n",
       "Events_Thunderstorm                     0\n",
       "MeanStoreSales                    7675.45\n",
       "MeanRegionSales                   4702.75\n",
       "MeanCustomers                     622.769\n",
       "MeanRegionCustomers               284.209\n",
       "D_DayOfWeek_cos                   0.62349\n",
       "D_DayOfWeek_sin                  0.781831"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's drop the angle column since it has the same menaing of the day of week\n",
    "df.drop('rad_ang', inplace=True, axis=1)\n",
    "if work_on == 'test':\n",
    "    df_test.drop('rad_ang', inplace=True, axis=1)\n",
    "df.head(1).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(433958, 57)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(37586, 55)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('./dataset/preprocessed_train.csv', index=False)\n",
    "if work_on == 'test':\n",
    "    df_test.to_csv('./dataset/preprocessed_test.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Additional sales of Store per month (tested and was not successful)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'break_here_exec' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-22-4d09646dd7b1>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mbreak_here_exec\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;31m#dfmean = df.groupby(['StoreID','D_Month'])['NumberOfSales'].mean()\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mdfmean\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mT\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'break_here_exec' is not defined"
     ]
    }
   ],
   "source": [
    "break_here_exec\n",
    "\n",
    "#dfmean = df.groupby(['StoreID','D_Month'])['NumberOfSales'].mean()\n",
    "dfmean.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfmean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfmean = dfmean.reset_index()\n",
    "dfmean.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "store=1003\n",
    "m=5\n",
    "dfmean[(dfmean.StoreID ==store) & (dfmean.D_Month ==m)]['NumberOfSales'][(store-1000)*12+m-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Mean_month_sale']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:10,'Mean_month_sale']=dfmean[(dfmean.StoreID ==1000) & (dfmean.D_Month ==1)]['NumberOfSales'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "store=1001\n",
    "month=3\n",
    "df.loc[(df.StoreID ==store) & (df.D_Month ==month),'Mean_month_sale']=\\\n",
    "dfmean[(dfmean.StoreID ==store) & (dfmean.D_Month ==month)]['NumberOfSales'][(store-1000)*12+month-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for store in (1000,1748):\n",
    "    for month in (1,12):\n",
    "        df.loc[(df.StoreID ==store) & (df.D_Month ==month),'Mean_month_sale']=\\\n",
    "                dfmean[(dfmean.StoreID ==store) & (dfmean.D_Month ==month)]['NumberOfSales'][(store-1000)*12+month-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#def mean_sales_month(store,month):  #takes too much time\n",
    "#    # since stores ID are from 1000 to 1749 let's subtract 1000\n",
    "#    return dfmean[(dfmean.StoreID ==store) & (dfmean.D_Month ==month)]"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}