7.0_Model_monthly_average.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Average with mean sales per month ensemble"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's first plot the sales per month in the 2 years, and see if in different years in average the shops all together sell the same amount of stuff. Also, it is necessary to understand whether most of the shop behave in the same way, outliers shops which could sell a lot one month and next year in the same month they sell much less could be problematic in this type of ensemble given our error function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reoder variables to work on train or test dataset\n",
    "predict_for_submission = True\n",
    "predict_for_submission = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from datetime import datetime\n",
    "\n",
    "df = pd.read_csv('./dataset/preprocessed0_date_train.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# months to use as test set\n",
    "#predict_months = (1, 2)\n",
    "#predict_year = 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#d['Date'] = pd.to_datetime(df['Date'], errors='coerce')\n",
    "#df['D_Month'] =  df['Date'].apply(lambda x: datetime.strptime(x, '%d/%m/%Y').month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 3  4  5  6  7  8  9 10 11 12  1  2]\n"
     ]
    }
   ],
   "source": [
    "print(df.D_Month.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.sort_values(by='Date')\n",
    "#df.sort_values(['StoreID', 'Date'])  sorting by date dows not work"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Getting first 12 months"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "first = df.loc[((df['D_Month'] >= 3) & (df['D_Year'] == 2016)) |\\\n",
    "                  ((df['D_Month'] < 3) & (df['D_Year'] == 2017))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "second = df.loc[((df['D_Month'] >= 3) & (df['D_Year'] == 2017)) |\\\n",
    "                  ((df['D_Month'] < 3) & (df['D_Year'] == 2018))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df.shape      (523021, 41)\n",
      "first.shape   (272636, 41)\n",
      "second.shape  (250385, 41)\n"
     ]
    }
   ],
   "source": [
    "print(\"df.shape     \", df.shape)\n",
    "print(\"first.shape  \", first.shape)\n",
    "print(\"second.shape \", second.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "aggregation_dict = {\n",
    "    'NumberOfSales':sum,      # find the sum of the sales of the group\n",
    "    'NumberOfCustomers':sum,\n",
    "    'StoreID':\"count\"\n",
    "}\n",
    "firstbymonth = first.groupby('D_Month').agg(aggregation_dict)\n",
    "firstbymonth = firstbymonth.reset_index()\n",
    "firstbymonth.rename(columns={'StoreID:': 'Open_stores'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "aggregation_dict = {'NumberOfSales':sum,      # find the sum of the sales of the group\n",
    "                    'NumberOfCustomers':sum,\n",
    "                    'StoreID':\"count\"}\n",
    "secondbymonth = second.groupby('D_Month').agg(aggregation_dict)\n",
    "secondbymonth = secondbymonth.reset_index()\n",
    "secondbymonth.rename(columns={'StoreID:': 'Open_stores'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Same normalization I did on week has to be applied here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>D_Month</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>NumberOfCustomers</th>\n",
       "      <th>StoreID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>86136904</td>\n",
       "      <td>5538571</td>\n",
       "      <td>23219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>84376836</td>\n",
       "      <td>5442662</td>\n",
       "      <td>20972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>97388599</td>\n",
       "      <td>6178965</td>\n",
       "      <td>22470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>82045477</td>\n",
       "      <td>5465175</td>\n",
       "      <td>22470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>86880572</td>\n",
       "      <td>5618039</td>\n",
       "      <td>23219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>89954151</td>\n",
       "      <td>5842857</td>\n",
       "      <td>22470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>91060441</td>\n",
       "      <td>5883092</td>\n",
       "      <td>23219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>97849526</td>\n",
       "      <td>6276168</td>\n",
       "      <td>23219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>88829457</td>\n",
       "      <td>5831557</td>\n",
       "      <td>22470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>86026240</td>\n",
       "      <td>5644232</td>\n",
       "      <td>23219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>90529834</td>\n",
       "      <td>5822204</td>\n",
       "      <td>22470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>114046671</td>\n",
       "      <td>6782620</td>\n",
       "      <td>23219</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    D_Month  NumberOfSales  NumberOfCustomers  StoreID\n",
       "0         1       86136904            5538571    23219\n",
       "1         2       84376836            5442662    20972\n",
       "2         3       97388599            6178965    22470\n",
       "3         4       82045477            5465175    22470\n",
       "4         5       86880572            5618039    23219\n",
       "5         6       89954151            5842857    22470\n",
       "6         7       91060441            5883092    23219\n",
       "7         8       97849526            6276168    23219\n",
       "8         9       88829457            5831557    22470\n",
       "9        10       86026240            5644232    23219\n",
       "10       11       90529834            5822204    22470\n",
       "11       12      114046671            6782620    23219"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firstbymonth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>D_Month</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>NumberOfCustomers</th>\n",
       "      <th>StoreID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>90594142</td>\n",
       "      <td>5633577</td>\n",
       "      <td>22844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>85169759</td>\n",
       "      <td>5348447</td>\n",
       "      <td>20972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>91610754</td>\n",
       "      <td>6000001</td>\n",
       "      <td>23219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>85759494</td>\n",
       "      <td>5481432</td>\n",
       "      <td>22470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>98267775</td>\n",
       "      <td>6205425</td>\n",
       "      <td>23219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>88219584</td>\n",
       "      <td>5592930</td>\n",
       "      <td>22470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>80106109</td>\n",
       "      <td>5133068</td>\n",
       "      <td>19719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>81388045</td>\n",
       "      <td>5273644</td>\n",
       "      <td>19344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>74374393</td>\n",
       "      <td>4961224</td>\n",
       "      <td>18720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>76627984</td>\n",
       "      <td>4985677</td>\n",
       "      <td>19344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>81381914</td>\n",
       "      <td>5161125</td>\n",
       "      <td>18720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>93468995</td>\n",
       "      <td>5529767</td>\n",
       "      <td>19344</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    D_Month  NumberOfSales  NumberOfCustomers  StoreID\n",
       "0         1       90594142            5633577    22844\n",
       "1         2       85169759            5348447    20972\n",
       "2         3       91610754            6000001    23219\n",
       "3         4       85759494            5481432    22470\n",
       "4         5       98267775            6205425    23219\n",
       "5         6       88219584            5592930    22470\n",
       "6         7       80106109            5133068    19719\n",
       "7         8       81388045            5273644    19344\n",
       "8         9       74374393            4961224    18720\n",
       "9        10       76627984            4985677    19344\n",
       "10       11       81381914            5161125    18720\n",
       "11       12       93468995            5529767    19344"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "secondbymonth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>D_Month</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>NumberOfCustomers</th>\n",
       "      <th>StoreID</th>\n",
       "      <th>Sales_per_store</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>86136904</td>\n",
       "      <td>5538571</td>\n",
       "      <td>23219</td>\n",
       "      <td>3709.759421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>84376836</td>\n",
       "      <td>5442662</td>\n",
       "      <td>20972</td>\n",
       "      <td>4023.308983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>97388599</td>\n",
       "      <td>6178965</td>\n",
       "      <td>22470</td>\n",
       "      <td>4334.161059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>82045477</td>\n",
       "      <td>5465175</td>\n",
       "      <td>22470</td>\n",
       "      <td>3651.334090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>86880572</td>\n",
       "      <td>5618039</td>\n",
       "      <td>23219</td>\n",
       "      <td>3741.787846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>89954151</td>\n",
       "      <td>5842857</td>\n",
       "      <td>22470</td>\n",
       "      <td>4003.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>91060441</td>\n",
       "      <td>5883092</td>\n",
       "      <td>23219</td>\n",
       "      <td>3921.807184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>97849526</td>\n",
       "      <td>6276168</td>\n",
       "      <td>23219</td>\n",
       "      <td>4214.200698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>88829457</td>\n",
       "      <td>5831557</td>\n",
       "      <td>22470</td>\n",
       "      <td>3953.246862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>86026240</td>\n",
       "      <td>5644232</td>\n",
       "      <td>23219</td>\n",
       "      <td>3704.993324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>90529834</td>\n",
       "      <td>5822204</td>\n",
       "      <td>22470</td>\n",
       "      <td>4028.920071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>114046671</td>\n",
       "      <td>6782620</td>\n",
       "      <td>23219</td>\n",
       "      <td>4911.782204</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    D_Month  NumberOfSales  NumberOfCustomers  StoreID  Sales_per_store\n",
       "0         1       86136904            5538571    23219      3709.759421\n",
       "1         2       84376836            5442662    20972      4023.308983\n",
       "2         3       97388599            6178965    22470      4334.161059\n",
       "3         4       82045477            5465175    22470      3651.334090\n",
       "4         5       86880572            5618039    23219      3741.787846\n",
       "5         6       89954151            5842857    22470      4003.300000\n",
       "6         7       91060441            5883092    23219      3921.807184\n",
       "7         8       97849526            6276168    23219      4214.200698\n",
       "8         9       88829457            5831557    22470      3953.246862\n",
       "9        10       86026240            5644232    23219      3704.993324\n",
       "10       11       90529834            5822204    22470      4028.920071\n",
       "11       12      114046671            6782620    23219      4911.782204"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# firstbymonth['StoreID'] is the number of rows\n",
    "firstbymonth['Sales_per_store']=firstbymonth['NumberOfSales']/firstbymonth['StoreID']\n",
    "firstbymonth.head(12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>D_Month</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>NumberOfCustomers</th>\n",
       "      <th>StoreID</th>\n",
       "      <th>Sales_per_store</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>90594142</td>\n",
       "      <td>5633577</td>\n",
       "      <td>22844</td>\n",
       "      <td>3965.774033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>85169759</td>\n",
       "      <td>5348447</td>\n",
       "      <td>20972</td>\n",
       "      <td>4061.117633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>91610754</td>\n",
       "      <td>6000001</td>\n",
       "      <td>23219</td>\n",
       "      <td>3945.508161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>85759494</td>\n",
       "      <td>5481432</td>\n",
       "      <td>22470</td>\n",
       "      <td>3816.621896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>98267775</td>\n",
       "      <td>6205425</td>\n",
       "      <td>23219</td>\n",
       "      <td>4232.213920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>88219584</td>\n",
       "      <td>5592930</td>\n",
       "      <td>22470</td>\n",
       "      <td>3926.105207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>80106109</td>\n",
       "      <td>5133068</td>\n",
       "      <td>19719</td>\n",
       "      <td>4062.381916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>81388045</td>\n",
       "      <td>5273644</td>\n",
       "      <td>19344</td>\n",
       "      <td>4207.405139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>74374393</td>\n",
       "      <td>4961224</td>\n",
       "      <td>18720</td>\n",
       "      <td>3972.991079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>76627984</td>\n",
       "      <td>4985677</td>\n",
       "      <td>19344</td>\n",
       "      <td>3961.330852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>81381914</td>\n",
       "      <td>5161125</td>\n",
       "      <td>18720</td>\n",
       "      <td>4347.324466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>93468995</td>\n",
       "      <td>5529767</td>\n",
       "      <td>19344</td>\n",
       "      <td>4831.937293</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    D_Month  NumberOfSales  NumberOfCustomers  StoreID  Sales_per_store\n",
       "0         1       90594142            5633577    22844      3965.774033\n",
       "1         2       85169759            5348447    20972      4061.117633\n",
       "2         3       91610754            6000001    23219      3945.508161\n",
       "3         4       85759494            5481432    22470      3816.621896\n",
       "4         5       98267775            6205425    23219      4232.213920\n",
       "5         6       88219584            5592930    22470      3926.105207\n",
       "6         7       80106109            5133068    19719      4062.381916\n",
       "7         8       81388045            5273644    19344      4207.405139\n",
       "8         9       74374393            4961224    18720      3972.991079\n",
       "9        10       76627984            4985677    19344      3961.330852\n",
       "10       11       81381914            5161125    18720      4347.324466\n",
       "11       12       93468995            5529767    19344      4831.937293"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "secondbymonth['Sales_per_store'] = secondbymonth['NumberOfSales']/secondbymonth['StoreID']\n",
    "secondbymonth.head(12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "bothbymonth = pd.concat([firstbymonth,secondbymonth])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "bothbymonth = bothbymonth.sort_values(by='D_Month')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>D_Month</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>NumberOfCustomers</th>\n",
       "      <th>StoreID</th>\n",
       "      <th>Sales_per_store</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>86136904</td>\n",
       "      <td>5538571</td>\n",
       "      <td>23219</td>\n",
       "      <td>3709.759421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>90594142</td>\n",
       "      <td>5633577</td>\n",
       "      <td>22844</td>\n",
       "      <td>3965.774033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>84376836</td>\n",
       "      <td>5442662</td>\n",
       "      <td>20972</td>\n",
       "      <td>4023.308983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>85169759</td>\n",
       "      <td>5348447</td>\n",
       "      <td>20972</td>\n",
       "      <td>4061.117633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>97388599</td>\n",
       "      <td>6178965</td>\n",
       "      <td>22470</td>\n",
       "      <td>4334.161059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>91610754</td>\n",
       "      <td>6000001</td>\n",
       "      <td>23219</td>\n",
       "      <td>3945.508161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>82045477</td>\n",
       "      <td>5465175</td>\n",
       "      <td>22470</td>\n",
       "      <td>3651.334090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>85759494</td>\n",
       "      <td>5481432</td>\n",
       "      <td>22470</td>\n",
       "      <td>3816.621896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>86880572</td>\n",
       "      <td>5618039</td>\n",
       "      <td>23219</td>\n",
       "      <td>3741.787846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>98267775</td>\n",
       "      <td>6205425</td>\n",
       "      <td>23219</td>\n",
       "      <td>4232.213920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>88219584</td>\n",
       "      <td>5592930</td>\n",
       "      <td>22470</td>\n",
       "      <td>3926.105207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>89954151</td>\n",
       "      <td>5842857</td>\n",
       "      <td>22470</td>\n",
       "      <td>4003.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>80106109</td>\n",
       "      <td>5133068</td>\n",
       "      <td>19719</td>\n",
       "      <td>4062.381916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>91060441</td>\n",
       "      <td>5883092</td>\n",
       "      <td>23219</td>\n",
       "      <td>3921.807184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>81388045</td>\n",
       "      <td>5273644</td>\n",
       "      <td>19344</td>\n",
       "      <td>4207.405139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>97849526</td>\n",
       "      <td>6276168</td>\n",
       "      <td>23219</td>\n",
       "      <td>4214.200698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>74374393</td>\n",
       "      <td>4961224</td>\n",
       "      <td>18720</td>\n",
       "      <td>3972.991079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>88829457</td>\n",
       "      <td>5831557</td>\n",
       "      <td>22470</td>\n",
       "      <td>3953.246862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>76627984</td>\n",
       "      <td>4985677</td>\n",
       "      <td>19344</td>\n",
       "      <td>3961.330852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>86026240</td>\n",
       "      <td>5644232</td>\n",
       "      <td>23219</td>\n",
       "      <td>3704.993324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>81381914</td>\n",
       "      <td>5161125</td>\n",
       "      <td>18720</td>\n",
       "      <td>4347.324466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>90529834</td>\n",
       "      <td>5822204</td>\n",
       "      <td>22470</td>\n",
       "      <td>4028.920071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>114046671</td>\n",
       "      <td>6782620</td>\n",
       "      <td>23219</td>\n",
       "      <td>4911.782204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>93468995</td>\n",
       "      <td>5529767</td>\n",
       "      <td>19344</td>\n",
       "      <td>4831.937293</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    D_Month  NumberOfSales  NumberOfCustomers  StoreID  Sales_per_store\n",
       "0         1       86136904            5538571    23219      3709.759421\n",
       "0         1       90594142            5633577    22844      3965.774033\n",
       "1         2       84376836            5442662    20972      4023.308983\n",
       "1         2       85169759            5348447    20972      4061.117633\n",
       "2         3       97388599            6178965    22470      4334.161059\n",
       "2         3       91610754            6000001    23219      3945.508161\n",
       "3         4       82045477            5465175    22470      3651.334090\n",
       "3         4       85759494            5481432    22470      3816.621896\n",
       "4         5       86880572            5618039    23219      3741.787846\n",
       "4         5       98267775            6205425    23219      4232.213920\n",
       "5         6       88219584            5592930    22470      3926.105207\n",
       "5         6       89954151            5842857    22470      4003.300000\n",
       "6         7       80106109            5133068    19719      4062.381916\n",
       "6         7       91060441            5883092    23219      3921.807184\n",
       "7         8       81388045            5273644    19344      4207.405139\n",
       "7         8       97849526            6276168    23219      4214.200698\n",
       "8         9       74374393            4961224    18720      3972.991079\n",
       "8         9       88829457            5831557    22470      3953.246862\n",
       "9        10       76627984            4985677    19344      3961.330852\n",
       "9        10       86026240            5644232    23219      3704.993324\n",
       "10       11       81381914            5161125    18720      4347.324466\n",
       "10       11       90529834            5822204    22470      4028.920071\n",
       "11       12      114046671            6782620    23219      4911.782204\n",
       "11       12       93468995            5529767    19344      4831.937293"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bothbymonth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7fa2e3d84940>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAz8AAAFQCAYAAACRaMDlAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvhp/UCwAAIABJREFUeJzt3Xm4JWV57/3vj3mepEGZbAeMQDREW+CNMaIikwNmIE7RDsGgCUZzYo6iyQk4vcK5TtT4GvWQgIADiCZGnKIoQpwINooggmEM3WFqaAYVooL3+0c9Gxabvfbe3Xtavev7ua517VpPPVX3XWvada96qlaqCkmSJEla7DZY6AQkSZIkaT5Y/EiSJEnqBYsfSZIkSb1g8SNJkiSpFyx+JEmSJPWCxY8kSZKkXrD4kTQnklyf5KCFzmN9lWRJkh8l2WwW1uVzsRaS7JzkiiSbTtLnQ0n+13zmNSSPLyZZvtB5zKckleTx67jsgUlWzSD2M5L8aF2Xl7TwLH6kWZbk/CR3TLbjpPVLktOSvGOewx4HfLiq/nue4/ZeVd0CfA04ZpI+r6mqt89fVkPzOKyqTl/oPOZK+zx91ULnMaaqvl5VvzIX607y+0m+leSeJOfPRQxJFj/SrEqyFHgGUMAL5yjGRnOx3j6Z78cwyYZr2X9TYDnw0bnJSNPwMeDVC52ERsc8fG6sAd4LnDjHcabN/zdajCx+pNn1SuBC4DS6nVcAkhyQ5ObBneAkv53k0ja9QZLjklyT5PYkZyfZoc1b2oZ5HJ3kBuC81v7Jts67kvxbkn0G1v2IJJ9NcneS7yR5R5JvDMx/YpJzk6xpQ6t+f9gGJfnDJNcm+XGS65K8vLU/Lsl5Ld/bknwsyXZD1jHZ9m2W5KOt/c6W785D1nN9kjcn+WE7uvbhwWFhSZ6f5JK2nm8lefK4Zd/UHvOfjv+nns57ktzaHtNLk/xqkmOAlwNvTPKTJJ9t/fdq30rfmeTyJC8cWNdpST6Y5AtJfgo8K8mmSf5PkhuS3NKGTW0+5GHfH7izqlYNrHOXJOe05+zqJH88MO+E9pie0Z6ny5Msm+Dxe2T7VvkRA21PTbI6ycYT9N80yXuT3Nhu722F2QPDh5K8pT3/14+9NgaWnXB7B5Z9Q3u8b0py1JDHYkbb3pb9p7aN1yV53cC8/ZKsSPc+uSXJuwfC/jvw2CSPHpLTA0cD12F7jko3rO7H6d5bQ4usJBsm+dv2GF+X5LXpPg82avPPT/Kq9njfmeRXB5ZdkuTeJDu1+1O9P/6yve7vSvKJDBlyme4z4Zvp3i93tm34jda+sj0Gg59/27bnZ3WS/0zy10k2GFjXN9pr5Y62jYe1ee+k+zLp/enee+8fSOOgJFe1Zf4+SYbkunl7ru5I8kPgaePmP2QI3ZDn9U1JbgY+nHHD5qZ63JK8sb0ebmzP09Ahe1X1lao6G7hxovnj8v5BkhcM3N+4vUb2bfcPaM/xnUm+n+TAgb5DX38TbfNUuUjrnary5s3bLN2Aq4E/BZ4K/ALYeWDeNcBzB+5/EjiuTf85XdG0G7Ap8H+BM9u8pXRHks4AtgQ2b+1/BGzd+r8XuGRg3We12xbA3sBK4Btt3pbt/lHARsBTgNuAfSbYni2Bu4FfafcfNdYPeDzw3BZ/CfBvwHsHlr0eOGga2/dq4LMt1w3bY7fNkMf3euAHwO7ADsA3gXe0eU8BbqUrHDakKz6vBzYdWPaStuzmE6z7EOBiYDsgwF7Ao9q808bitPsbt+f6LcAmwLOBHw88TqcBdwFPp/uSabP2HJ3T8t66bfO7hmznscDnx7VdAHygrWtfYDXwnDbvBOC/gcPbtr8LuHDIc/EF4E8G5r0H+P+G5PG29rzt1J7jbwFvb/MOBO4D3t2e02cCPx14DIZu78Cyb2uP5eHAPcD2Q/JYp21vj/3FwN+05+mxwLXAIW3+t4FXtOmtgAPGxb0UeOGQnB54TazD9jwPeBzd6+yZre9ThvR9DfBDuvfO9sBX6D4PNmrzzwde1aZPBd457nX0r2vx/rgI2KU9Z1cArxmS0x+27T2qresdwA3A37fXwsF074etWv8zgM+018FS4D+AowfW9Qvgj9u6/oRu5z/jt28gfgGfo3uv7tFeD4cOyfVE4Ottm3an+/xYNW5dj5/ieT2pbdfmrW1w+aGPG3AocDOwD93n20fGxxuS86uA86fo80bgEwP3jwAua9O7ArfTvQ43oPucvh1YMtXrb6JtniwPb97Wx9uCJ+DN22K5Ab/Z/onv2O5fCfyPgfnvAE5t01vT7Sg+ut2/grYz1+4/qq1rIx4sfh47SeztWp9t2w7EL2g7oQOxx4qfFwNfH7f8/wWOn2C9WwJ3Ar871T9B4EXA9wbuX8+DO9yTbd8f0e1UP3kaj/H1DOyQtX/u17TpD9J2zAfm/wh45sCyfzTJup9Nt1N2ALDBuHmn8dDi5xltp2aDgbYzgRMG+p8xMC/t+X7cQNv/A1w3JJe/As4auL87cD+w9UDbu4DT2vQJwFcG5u0N3DvkuXgx8M02vWHbjv2G5HENcPjA/UOA69v0gXQ7SVsOzD8b+F9TbW9b9l7aDnxru5VxxcdMt51uR/+Gcet7M925VNAV7G+lvWcniP1N4JVD5j3wmlib7Rmyrn8BXj9k3nnAqwfuH8Tw4ucg4NqJ8md6748/GJj3v4EPDcnpD4GrBu4/qeU0+GXP7XSF6obAz4C9B+a9mrZz39Z19cC8Ldq6Hjl++wb6FPCb4153xw3J9VoGCiO687jWpvj5ObDZwPwDeXjxM+HjRleMvmtg3uPHxxuS83SKn13oCsxt2v1PAW9s028CPjKu/5eA5VO9/ibaZm/eFtvNYW/S7FkOfLmqbmv3P87A0Ld2/3fSDRv6HeC7VfWfbd6jgU+3IQp30hUL9wODw79Wjk20oTAnphtGdjfdP2CAHem+od9osP+46UcD+4/FavFeDjxy/AZV1U/pdpZfA9yU5PNJnthy2CnJWUn+q+Xw0RZ/IpNt30fo/jGf1YaG/O9MMARryLb8J91OwFiMN4zbrt0H5o9fdvy2nge8n+7b61uSnJxkmyHddwFWVtUvx+Wy65BYS+h26i4eyO1fW/tE7qArkAfjramqH08S7+aB6XuAzTLxeP3PAHsneSzdN8J3VdVFQ/LYpcUZjDn4eN7RXiPj509ne2+vqvvG5bzVkBzWddsfDewy7jXxFh58Xx0NPAG4Mt1wy+ePi701XfE/HdPdHpIcluTCdMP47qQr4oe9d3Zh+Ht5vPOAzZPsn2643r7Ap9u86bw/xj+OE+bf3DIwfS88cKGIwbat6LZrEx7+Oprw+auqe9rkZLHXJtfxj99/Duk3zOqa+qIjw3JZm+durVTVjXTF7e+mG258GN15atA910eOe65/k+5Lp+m8/qazzdJ6y+JHmgXpzmX4feCZ6c7DuRn4H8CvJfk1gKr6Id0/3sOAl9EVQ2NWAodV1XYDt82q6r8G+tTA9MvohjkcRHe0Z+lYKnRDQO6jGyYzZvdxsS4YF2urqvqTibatqr5UVc+l+8d5JfAPbda7Wk5PrqptgD9o8ScydPuq6hdV9daq2hv4DeD5dOdODTO4LXvw4Pj4lXRDfgZjbFFVZw5uziTrpareV1VPpRum8gTgfw5Z7kZg97HzFgZyGfZ83Ua3M7jPQG7bVtWwHbZLW/zBeDskGSyIxseblrZTczZdwfsKuuJzmBvpdqQGYw6ej7B9ki0nmL+22zuZmWz7SrqjTYOvia2r6nCAqrqqql5KN6zvJOBTY9vTiqfHA99fh5yHal9+/BPwf+iOlGxHNxRx2HvnJoa/lx+iFeNnAy+l+4z43EDROJ33x1y4je4o7/jX0XRfu5O+Z6fhJh7+mTHoHrpCfcz4L4FmEn/az906Op3uc/dI4NsD/y9W0h35GXyut6yqE6f5+pvpYy6NNIsfaXa8iO5Ixt5037buS3fOyNd56I78x4HXAb9Fd87PmA8B72zf1o6dqHzEJPG2phtKcjvdP+7/d2xGVd0P/DNwQpIt2pGawRw+BzwhySvaSbIbJ3lakr3GB0n3eycvbDuEPwN+0rZzLIefAHcm2ZUHC4WJDN2+JM9K8qR0F4O4m25H6f7hq+LYJLulu2DCW4BPtPZ/AF7TvvVOki2TPG/cTvNQ7THYvx11+indeSRjedxCd77ImH9vfd7YHr8DgRfQnWf1MG2n9B+A9+TBk893TXLIkHQuArZrjytVtZJuaOC70l0g4sl0Ry0+NmT5qZxBN9zohUx+Rbkzgb9uz9eOdOfOjO//1iSbJHkGXeH6yXXY3qFmuO0XAXe3k7c3b0dMfzXJ01pOf5BkSct37AjP2HO+H90Qv7U9UjCVTejOpVgN3Jfu5P6DJ+l/NvD69vhtRzekaTIfpzta+3Ie+gXLjN4f66p9Hp1N9/7fun0G/AXTv5Lh+Pfe2jobeHOS7ZPsBvzZuPmXAC9rr41D6c6BmS1nA0eluzjKFnTvn6FaDpvRHbnfoL3eJzsK/i9053K9nu49PeajwAuSHDK2znQXMtiNtX/9SYuOxY80O5bTnUdwQ1XdPHajG0b18oHhR2fSjak+b2B4HMDf0Z0c/uUkP6Y7yXz/SeKdQXcU6b/oToa+cNz819IdEbqZ7pv9M+mKF9o3wQcDL6H7Vv1mHjy5dbwNgDe0fmvodgz+tM17K90/3ruAz9MVXMNMtn2PpBuvfjfdcLgLmHzH6OPAl+nG8l9Ldz4TVbWC7qTp99MNG7uabgd/urah20G8g+6xvZ3u21GAU+iGit2Z5F+q6ud0hcNhdN9sf4Du3IorJ1n/m1pOF6YbJvgVYMLfC2nrP43uW90xL6U7wncj3VCm46vq3LXYvsH1fxP4Jd3Qy+sn6foOYAXdkajLgO+2tjE30z1eN9IVI68ZeAymvb3TsE7b3na8X0D3ZcR1dM/VP9K9N6A7If3yJD+he42+ZGC4z8vpivZZ1d5/r6PbMb6D7gjNOZMs8g90r/dLge/RfUt/H0O+IKiqscJ8F+CLA+0zfX/MxJ+1nK4FvkH3Hj51msv+HfB76a7W9r51iP1WuvfzdXSP4/gjna+ne42MDf/9l3WIMaGq+iLwPrrfjLqa7gIb0D6LJ/AKuiOmH6Q7r/BeHjzSPtH676U7ivMYBj5/2xcGR9B9ObSa7kjQ/6Q7R3FtX3/SojN2NRVJi1iSk+hOIF4+ZecRluR6upOfv7LQucy1JEvojhz+etvJme31nwd8vKr+cR2XPxD4aFXtNlXf9U07WnUB3WM/Uuc+tG/qP1RVj56ys0ZKO7r+A7or7N03Vf9prvNvgCdU1R9M2VkS4JEfaVFK9zs+T27DW/ajGyb06amW0+ioqtVV9cQ5KnyeRnfU7hNT9e2jqrq1qvYahcKnDdc7PMlGbRjk8fheXm+k+z23TZJsT3eE/bOzWPjsQPfZfvJsrE/qC4sfaXHamm4YxE/phjf8Ld1VvtRzSU6nG4L25+OuoKbRFLqhW3fQDXu7ginOHdFIeTXd0LNr6IYqTnhhmbWV7od+VwJfrKp/m411Sn3hsDdJkiRJveCRH0mSJEm9MNEP4I2MHXfcsZYuXbrQaUiSJEkaYRdffPFtVTXsx8MfMNLFz9KlS1mxYsVCpyFJkiRphCWZ1u+yOexNkiRJUi9Y/EiSJEnqBYsfSZIkSb0wreInyfVJLktySZIVrW2HJOcmuar93b61J8n7klyd5NIkTxlYz/LW/6ok6/UvzUuSJElav6zNkZ9nVdW+VbWs3T8O+GpV7Ql8td0HOAzYs92OAT4ID/wS8fHA/sB+wPFjBZMkSZIkzbWZDHs7Aji9TZ8OvGig/YzqXAhsl+RRwCHAuVW1pqruAM4FDp1BfEmSJEmatukWPwV8OcnFSY5pbTtX1U0A7e9OrX1XYOXAsqta27B2SZIkSZpz0/2dn6dX1Y1JdgLOTXLlJH0zQVtN0v7Qhbvi6hiAPfbYY5rpSZIkSdLkpnXkp6pubH9vBT5Nd87OLW04G+3vra37KmD3gcV3A26cpH18rJOrallVLVuyZMofaZUkSZKkaZmy+EmyZZKtx6aBg4EfAOcAY1dsWw58pk2fA7yyXfXtAOCuNizuS8DBSbZvFzo4uLVJkiRJ0pybzrC3nYFPJxnr//Gq+tck3wHOTnI0cANwZOv/BeBw4GrgHuAogKpak+TtwHdav7dV1ZpZ2xJJkiRJmkSqHnbazchYtmxZrVixYqHTkCRJktZbS4/7/IyWv/7E581SJnMnycUDP8kz1EwudS1JkiRJ6w2LH0mSJEm9YPEjSZIkqRem+zs/kiRJkrT2Tth2hsvfNTt54JEfSZIkST1h8SNJkiSpFyx+JEmSJPWCxY8kSZKkXrD4kSRJktQLFj+SJEmSesHiR5IkSVIvWPxIkiRJ6gWLH0mSJEm9YPEjSZIkqRcsfiRJkiT1gsWPJEmSpF6w+JEkSZLUCxY/kiRJknrB4keSJElSL1j8SJIkSeoFix9JkiRJvWDxI0mSJKkXLH4kSZIk9YLFjyRJkqResPiRJEmS1AsWP5IkSZJ6weJHkiRJUi9Y/EiSJEnqBYsfSZIkSb2w0UInIEmSJM2FR37tkhmv4+Zn7TsLmWhUWPxoUfj715w3o+WP/dCzZykTSZIkjSqHvUmSJEnqBYsfSZIkSb1g8SNJkiSpFyx+JEmSJPWCFzyQpFniVYUkSRptFj+SJC0yVzxxrxktv9eVV8w4B6/CKWkUOexNkiRJUi9Y/EiSJEnqBYsfSZIkSb1g8SNJkiSpFyx+JEmSJPWCxY8kSZKkXrD4kSRJktQL0/6dnyQbAiuA/6qq5yd5DHAWsAPwXeAVVfXzJJsCZwBPBW4HXlxV17d1vBk4GrgfeF1VfWk2N0YLYxR+T0KSHnDCtjNc/q7ZyUOSNHLW5sjP64HBvdSTgPdU1Z7AHXRFDe3vHVX1eOA9rR9J9gZeAuwDHAp8oBVUkiRJkjTnpnXkJ8luwPOAdwJ/kSTAs4GXtS6nAycAHwSOaNMAnwLe3/ofAZxVVT8DrktyNbAf8O1Z2ZIee9LpT5rR8pctv2yWMpEkSdKgpcd9fkbLX3/i82YpE8H0h729F3gjsHW7/wjgzqq6r91fBezapncFVgJU1X1J7mr9dwUuHFjn4DIPSHIMcAzAHnvsMe0NkSSNBv/RS5JG1ZTD3pI8H7i1qi4ebJ6ga00xb7JlHmyoOrmqllXVsiVLlkyVniRJkiRNy3SO/DwdeGGSw4HNgG3ojgRtl2SjdvRnN+DG1n8VsDuwKslGwLbAmoH2MYPLrL88sVaSpJHzty9+/ozX8YZPfG4WMlk4J5xwwkisQxolUxY/VfVm4M0ASQ4E/rKqXp7kk8Dv0V3xbTnwmbbIOe3+t9v886qqkpwDfDzJu4FdgD2Bi2Z3c6SFMdN/suv7P1hJ0sOtOu7rM1p+txOfMUuZSBoz7UtdT+BNwFlJ3gF8DziltZ8CfKRd0GAN3RXeqKrLk5wN/BC4Dzi2qu6fQXxJ0jiebzMavBCNJI2mtSp+qup84Pw2fS3d1drG9/lv4Mghy7+T7opxkiRJkjSv1uZ3fiRJkiRpvWXxI0mSJKkXLH4kSZIk9YLFjyRJkqRemMnV3iTpAf6ehCRJGnUe+ZEkSZLUCxY/kiRJknrB4keSJElSL3jOj6RF46vnPW5Gyz/n2dfMUiaSJGkUeeRHkiRJUi+s10d+lh73+Rmv4/oTnzcLmUiSJGk8j8hr1HjkR5IkSVIvWPxIkiRJ6oX1etibpAetOu7rM1p+txOfMUuZSJIkjSaP/EiSJEnqBYsfSZIkSb1g8SNJkiSpFyx+JEmSJPWCxY8kSZKkXrD4kSRJktQLFj+SJEmSesHiR5IkSVIvWPxIkiRJ6gWLH0mSJEm9YPEjSZIkqRcsfiRJkiT1gsWPJEmSpF6w+JEkSZLUCxY/kiRJknrB4keSJElSL1j8SJIkSeoFix9JkiRJvWDxI0mSJKkXLH4kSZIk9YLFjyRJkqResPiRJEmS1AsWP5IkSZJ6weJHkiRJUi9Y/EiSJEnqBYsfSZIkSb1g8SNJkiSpFyx+JEmSJPWCxY8kSZKkXrD4kSRJktQLUxY/STZLclGS7ye5PMlbW/tjkvx7kquSfCLJJq1903b/6jZ/6cC63tzaf5TkkLnaKEmSJEkabzpHfn4GPLuqfg3YFzg0yQHAScB7qmpP4A7g6Nb/aOCOqno88J7WjyR7Ay8B9gEOBT6QZMPZ3BhJkiRJGmbK4qc6P2l3N263Ap4NfKq1nw68qE0f0e7T5j8nSVr7WVX1s6q6Drga2G9WtkKSJEmSpjCtc36SbJjkEuBW4FzgGuDOqrqvdVkF7NqmdwVWArT5dwGPGGyfYJnBWMckWZFkxerVq9d+iyRJkiRpAtMqfqrq/qraF9iN7mjNXhN1a38zZN6w9vGxTq6qZVW1bMmSJdNJT5IkSZKmtFZXe6uqO4HzgQOA7ZJs1GbtBtzYplcBuwO0+dsCawbbJ1hGkiRJkubUdK72tiTJdm16c+Ag4Arga8DvtW7Lgc+06XPafdr886qqWvtL2tXgHgPsCVw0WxsiSZIkSZPZaOouPAo4vV2ZbQPg7Kr6XJIfAmcleQfwPeCU1v8U4CNJrqY74vMSgKq6PMnZwA+B+4Bjq+r+2d0cSZIkSZrYlMVPVV0K/PoE7dcywdXaquq/gSOHrOudwDvXPk1JkiRJmpm1OudHkiRJktZXFj+SJEmSesHiR5IkSVIvWPxIkiRJ6gWLH0mSJEm9YPEjSZIkqRcsfiRJkiT1gsWPJEmSpF6w+JEkSZLUCxY/kiRJknrB4keSJElSL1j8SJIkSeoFix9JkiRJvWDxI0mSJKkXLH4kSZIk9YLFjyRJkqResPiRJEmS1AsWP5IkSZJ6weJHkiRJUi9Y/EiSJEnqBYsfSZIkSb1g8SNJkiSpFyx+JEmSJPWCxY8kSZKkXrD4kSRJktQLFj+SJEmSesHiR5IkSVIvWPxIkiRJ6gWLH0mSJEm9YPEjSZIkqRcsfiRJkiT1gsWPJEmSpF6w+JEkSZLUCxY/kiRJknrB4keSJElSL1j8SJIkSeoFix9JkiRJvWDxI0mSJKkXLH4kSZIk9YLFjyRJkqResPiRJEmS1AsWP5IkSZJ6weJHkiRJUi9Y/EiSJEnqhSmLnyS7J/lakiuSXJ7k9a19hyTnJrmq/d2+tSfJ+5JcneTSJE8ZWNfy1v+qJMvnbrMkSZIk6aGmc+TnPuANVbUXcABwbJK9geOAr1bVnsBX232Aw4A92+0Y4IPQFUvA8cD+wH7A8WMFkyRJkiTNtSmLn6q6qaq+26Z/DFwB7AocAZzeup0OvKhNHwGcUZ0Lge2SPAo4BDi3qtZU1R3AucChs7o1kiRJkjTEWp3zk2Qp8OvAvwM7V9VN0BVIwE6t267AyoHFVrW2Ye3jYxyTZEWSFatXr16b9CRJkiRpqGkXP0m2Av4J+POqunuyrhO01STtD22oOrmqllXVsiVLlkw3PUmSJEma1LSKnyQb0xU+H6uqf27Nt7ThbLS/t7b2VcDuA4vvBtw4SbskSZIkzbnpXO0twCnAFVX17oFZ5wBjV2xbDnxmoP2V7apvBwB3tWFxXwIOTrJ9u9DBwa1NkiRJkubcRtPo83TgFcBlSS5pbW8BTgTOTnI0cANwZJv3BeBw4GrgHuAogKpak+TtwHdav7dV1ZpZ2QpJkiRJmsKUxU9VfYOJz9cBeM4E/Qs4dsi6TgVOXZsEJUmSJGk2rNXV3iRJkiRpfWXxI0mSJKkXLH4kSZIk9YLFjyRJkqResPiRJEmS1AsWP5IkSZJ6weJHkiRJUi9Y/EiSJEnqBYsfSZIkSb1g8SNJkiSpFyx+JEmSJPWCxY8kSZKkXrD4kSRJktQLFj+SJEmSesHiR5IkSVIvWPxIkiRJ6gWLH0mSJEm9YPEjSZIkqRcsfiRJkiT1gsWPJEmSpF6w+JEkSZLUCxY/kiRJknrB4keSJElSL1j8SJIkSeoFix9JkiRJvWDxI0mSJKkXLH4kSZIk9YLFjyRJkqResPiRJEmS1AsWP5IkSZJ6weJHkiRJUi9Y/EiSJEnqBYsfSZIkSb1g8SNJkiSpFyx+JEmSJPWCxY8kSZKkXrD4kSRJktQLFj+SJEmSesHiR5IkSVIvWPxIkiRJ6gWLH0mSJEm9YPEjSZIkqRcsfiRJkiT1wpTFT5JTk9ya5AcDbTskOTfJVe3v9q09Sd6X5OoklyZ5ysAyy1v/q5Isn5vNkSRJkqSJTefIz2nAoePajgO+WlV7Al9t9wEOA/Zst2OAD0JXLAHHA/sD+wHHjxVMkiRJkjQfpix+qurfgDXjmo8ATm/TpwMvGmg/ozoXAtsleRRwCHBuVa2pqjuAc3l4QSVJkiRJc2Zdz/nZuapuAmh/d2rtuwIrB/qtam3D2h8myTFJViRZsXr16nVMT5IkSZIearYveJAJ2mqS9oc3Vp1cVcuqatmSJUtmNTlJkiRJ/bWuxc8tbTgb7e+trX0VsPtAv92AGydplyRJkqR5sa7FzznA2BXblgOfGWh/Zbvq2wHAXW1Y3JeAg5Ns3y50cHBrkyRJkqR5sdFUHZKcCRwI7JhkFd1V204Ezk5yNHADcGTr/gXgcOBq4B7gKICqWpPk7cB3Wr+3VdX4iyhIkiRJ0pyZsvipqpcOmfWcCfoWcOyQ9ZwKnLpW2UmSJEnSLJntCx5IkiRJ0kiy+JEkSZLUCxY/kiRJknrB4keSJElSL1j8SJIkSeoFix9JkiRJvWDxI0mSJKkXLH4kSZIk9YLFjyRJkqResPiRJEmS1AsWP5IkSZJ6weJHkiRJUi9Y/EiSJEnqBYsfSZIkSb1g8SNJkiSpFyx+JEmSJPWCxY8kSZKkXrD4kSRJktQLFj+SJEmSesHiR5IkSVIvWPxIkiRJ6gWLH0mSJEm9YPEjSZIkqRcsfiRJkiT1gsWPJEmSpF6w+JEkSZLUCxY/kiRJknrB4keSJElSL1j8SJIkSeoFix9JkiRJvWDxI0mSJKkXLH4kSZIk9YLFjyRJkqResPiRJEmS1AsWP5IkSZJ6weJHkiRJUi9Y/EiSJEnqBYvdhaqPAAAJRElEQVQfSZIkSb1g8SNJkiSpFyx+JEmSJPWCxY8kSZKkXrD4kSRJktQLFj+SJEmSesHiR5IkSVIvzHvxk+TQJD9KcnWS4+Y7viRJkqR+mtfiJ8mGwN8DhwF7Ay9Nsvd85iBJkiSpn+b7yM9+wNVVdW1V/Rw4CzhinnOQJEmS1EOpqvkLlvwecGhVvardfwWwf1W9dqDPMcAx7e6vAD+aYdgdgdtmuI71Of4o5LDQ8Uchh4WOPwo5LHR8cxiN+KOQw0LHH4UcFjr+KOSw0PHNYTTij0IOCx1/FHKYjfiPrqolU3XaaIZB1lYmaHtI9VVVJwMnz1rAZEVVLZut9a1v8Uchh4WOPwo5LHT8UchhoeObw2jEH4UcFjr+KOSw0PFHIYeFjm8OoxF/FHJY6PijkMN8xp/vYW+rgN0H7u8G3DjPOUiSJEnqofkufr4D7JnkMUk2AV4CnDPPOUiSJEnqoXkd9lZV9yV5LfAlYEPg1Kq6fI7DztoQuvU0Pix8DgsdHxY+h4WODwufw0LHB3MYhfiw8DksdHxY+BwWOj4sfA4LHR/MYRTiw8LnsNDxYeFzmLf483rBA0mSJElaKPP+I6eSJEmStBAsfiRJkiT1gsWPJEmSpF6w+FlkkjwxyXOSbDWu/dCe5bBfkqe16b2T/EWSw/sSf4J8zlio2KOSQ5LfbM/DwX2Jn2T/JNu06c2TvDXJZ5OclGTbxR6/xX1dkt2n7rl4c0iySZJXJjmo3X9ZkvcnOTbJxos9/kAej0vyl0n+LsnfJnnNfL0ORykHqe96c8GDJEdV1YcXc/wkrwOOBa4A9gVeX1WfafO+W1VPmcv4I5TD8cBhdFczPBfYHzgfOAj4UlW9c5HHH3/5+ADPAs4DqKoXzmX8Ecrhoqrar03/Md3r8tPAwcBnq+rExRy/xb0c+LV2pc2TgXuATwHPae2/s5jjtxzuAn4KXAOcCXyyqlbPddxRyiHJx+g+j7YA7gS2Av6Z7nlIVS1fzPFbDq8DXgBcABwOXALcAfw28KdVdX4fcpAEVFUvbsANiz0+cBmwVZteCqygKz4AvjdP2zkqOWxI94/2bmCb1r45cGkP4n8X+ChwIPDM9vemNv3MeXoORiGH7w1MfwdY0qa3BC5b7PFbrCsGn5Nx8y5Z7PHHnge6UQ4HA6cAq4F/BZYDW/chh7HPHboC5BZgw3Y/8/SZtKDxW6zLBuJuAZzfpveY7/9NC5mDN2/eanENe0ty6ZDbZcDOiz0+3YfqTwCq6nq6Hc7Dkryb7p/MfBiFHO6rqvur6h7gmqq6u+VzL/DLHsRfBlwM/BVwV3XfJt5bVRdU1QXzEH9UctggyfZJHkH37fJqgKr6KXBfD+ID/CDJUW36+0mWASR5AvCLHsQHqKr6ZVV9uaqOBnYBPgAcClzbkxw2aD8svjXdTvfYMKtNgfkYdrbQ8ceM/bbhpi0XquqGPuWQZNskJya5Msnt7XZFa9tuscefRn5f7EP8JNskeVeSjyR52bh5H1js8ef1R07nwc7AIXSHkQcF+FYP4t+cZN+qugSgqn6S5PnAqcCT5iH+qOTw8yRbtOLjqWONbVz1fBQfCxq/qn4JvCfJJ9vfW5j/HzRe8BzodrAupnv/VZJHVtXN6c5Fm49CfKHjA7wK+Lskfw3cBnw7yUpgZZu32OPDuMe6qn4BnAOck2TznuRwCnAl3RHpvwI+meRa4ADgrB7EB/hH4DtJLgR+CzgJIMkSYE2PcjibbvjxgVV1c4v/SLqjkJ8EnrvI45Nk2PD70A3XX9Txmw8DVwH/BPxRkt8FXlZVP6N7Xy7q+IvqnJ8kpwAfrqpvTDDv41X1sgkWW0zxd6M76nDzBPOeXlXfnMv4I5TDpu0NNL59R+BRVXXZYo4/QdznAU+vqrfMZ9xRy2Egly2Anavqur7ET7I18Fi6AnRVVd0yX7EXOn6SJ1TVf8xXvBHOYReAqrqxfcN+EN1w7Iv6EL/lsA+wF/CDqrpyvuKOUg5JflRVv7K28xZL/Bbnfrrzrib6EuqAqprTLyQWOn7L4ZKq2nfg/l/RnYf2QuDcmuPzsxc8/mIqfiRJkjSxJF8GvgKcPvYlRJKdgT8EnltVBy3m+C3eD4DfrqqrJpi3sqrm9MqMCx2/xbkC2KeN0hhrWw68ke687Ucv5viL6pwfSZIkDfVi4BHABUnWJFlDdzXSHYAjexAf4ASG7//+WQ/iA3wWePZgQ1WdDrwB+Plij++RH0mSpJ5LD34SZNRzWOj4o5DDfMS3+JEkSeq5JDdU1R59jT8KOSx0/FHIYT7iL7arvUmSJGkCSS4dNot5+kmQhYw/CjksdPxRyGGh41v8SJIk9cNC/yTHQscfhRwWOv4o5LCg8S1+JEmS+uFzdFfTumT8jCTn9yD+KOSw0PFHIYcFje85P5IkSZJ6wUtdS5IkSeoFix9JkiRJvWDxI0mSJKkXLH4kSXMmyf1JLklyeZLvJ/mLJEP/9yQ5MEklOXqg7ddb21+uYw77Jjl84P4J67ouSdL6zeJHkjSX7q2qfatqH+C5wOHA8VMscxnw4oH7LwG+P4Mc9m1xJUk9Z/EjSZoXVXUrcAzw2iSZpOsNwGZJdm79DgW+ODazHcm5MMmlST6dZPvWfn6Sk5JclOQ/kjwjySbA24AXtyNQY0XV3q3/tUleNycbLEkaORY/kqR5U1XX0v3v2WmKrp8CjgR+A/gu8LOBeWcAb6qqJ9MdJRo8krRRVe0H/DlwfFX9HPgb4BPtCNQnWr8n0v3I3n7A8Uk2ntmWSZLWBxY/kqT5NtlRnzFn0xU/LwXOfGDBZFtgu6q6oDWdDvzWwHL/3P5eDCydZP2fr6qfVdVtwK10vzguSVrkLH4kSfMmyWOB++kKjqGq6mbgF3TnCX11LUKMHSG6H9hoGv2m01eStEj4YS9JmhdJlgAfAt5fVTWNRf4G2Kmq7h87Raiq7kpyR5JnVNXXgVcAF0y2EuDHwNYzSF2StEhY/EiS5tLmSS4BNgbuAz4CvHs6C1bVt4bMWg58KMkWwLXAUVOs6mvAcS2Pd00ra0nSopTpffkmSZIkSes3z/mRJEmS1AsOe5MkzbskhwAnjWu+rqp+eyHykST1g8PeJEmSJPWCw94kSZIk9YLFjyRJkqResPiRJEmS1AsWP5IkSZJ64f8HsVHgmIqll8MAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 1008x360 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bothbymonth.plot('D_Month','Sales_per_store',figsize = (14,5),legend = \n",
    "                  False,title = 'Average sales per store (only open ones) in a given month during 1 year',kind='bar')\n",
    "#data.plot('type', 'sale', kind='bar')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can notice most of the months behave in a similar way in the 2 years, what we have to assure is that every singular shop more or less behaves in a similar way, since out loss function's error derives from the differences in months real and predicted sales.\n",
    "One way to do this would be to directly test the BIP error function on 2 months using for sales of each day of the store the average sales of the month divided by the days the shop is open, so that the sum by month becomes the as the one of the previous year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take as our usual example january and february"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import modify_dataset as md\n",
    "\n",
    "features=['IsOpen','StoreID','D_Month','NumberOfSales']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df.shape         (523021, 41)\n",
      "test2017.shape   (44191, 41)\n",
      "test2018.shape   (43816, 41)\n"
     ]
    }
   ],
   "source": [
    "print('df.shape        ', df.shape)\n",
    "\n",
    "test_months = (1, 2)\n",
    "\n",
    "test_year = 2017\n",
    "test2017 = md.get_fake_test(df, test_months, test_year)\n",
    "print('test2017.shape  ', test2017.shape)\n",
    "\n",
    "test_year = 2018\n",
    "test2018 = md.get_fake_test(df, test_months, test_year)\n",
    "print('test2018.shape  ', test2018.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "test2017 = test2017[features]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "t2017 = test2017.groupby(['D_Month', 'StoreID']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's compute the sale per open day of a shop in a given month"
   ]
  },
  {
   "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>D_Month</th>\n",
       "      <th>StoreID</th>\n",
       "      <th>IsOpen</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>Sales_per_open_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>25</td>\n",
       "      <td>185231</td>\n",
       "      <td>7409.240000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1001</td>\n",
       "      <td>25</td>\n",
       "      <td>66189</td>\n",
       "      <td>2647.560000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1002</td>\n",
       "      <td>25</td>\n",
       "      <td>114663</td>\n",
       "      <td>4586.520000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1003</td>\n",
       "      <td>25</td>\n",
       "      <td>126490</td>\n",
       "      <td>5059.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1004</td>\n",
       "      <td>24</td>\n",
       "      <td>95016</td>\n",
       "      <td>3959.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>1005</td>\n",
       "      <td>25</td>\n",
       "      <td>117460</td>\n",
       "      <td>4698.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>1006</td>\n",
       "      <td>25</td>\n",
       "      <td>145685</td>\n",
       "      <td>5827.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>1007</td>\n",
       "      <td>25</td>\n",
       "      <td>94273</td>\n",
       "      <td>3770.920000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>1008</td>\n",
       "      <td>25</td>\n",
       "      <td>163494</td>\n",
       "      <td>6539.760000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>1009</td>\n",
       "      <td>25</td>\n",
       "      <td>127759</td>\n",
       "      <td>5110.360000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>1010</td>\n",
       "      <td>25</td>\n",
       "      <td>138555</td>\n",
       "      <td>5542.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>1011</td>\n",
       "      <td>25</td>\n",
       "      <td>68515</td>\n",
       "      <td>2740.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>1012</td>\n",
       "      <td>25</td>\n",
       "      <td>155257</td>\n",
       "      <td>6210.280000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>1013</td>\n",
       "      <td>25</td>\n",
       "      <td>116737</td>\n",
       "      <td>4669.480000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>1014</td>\n",
       "      <td>25</td>\n",
       "      <td>112892</td>\n",
       "      <td>4515.680000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>1015</td>\n",
       "      <td>25</td>\n",
       "      <td>141888</td>\n",
       "      <td>5675.520000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>1016</td>\n",
       "      <td>24</td>\n",
       "      <td>88160</td>\n",
       "      <td>3673.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1</td>\n",
       "      <td>1017</td>\n",
       "      <td>25</td>\n",
       "      <td>75374</td>\n",
       "      <td>3014.960000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>1018</td>\n",
       "      <td>25</td>\n",
       "      <td>85330</td>\n",
       "      <td>3413.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1</td>\n",
       "      <td>1019</td>\n",
       "      <td>25</td>\n",
       "      <td>108312</td>\n",
       "      <td>4332.480000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>1020</td>\n",
       "      <td>24</td>\n",
       "      <td>131138</td>\n",
       "      <td>5464.083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1</td>\n",
       "      <td>1021</td>\n",
       "      <td>25</td>\n",
       "      <td>131584</td>\n",
       "      <td>5263.360000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1</td>\n",
       "      <td>1022</td>\n",
       "      <td>24</td>\n",
       "      <td>129149</td>\n",
       "      <td>5381.208333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1</td>\n",
       "      <td>1023</td>\n",
       "      <td>25</td>\n",
       "      <td>84253</td>\n",
       "      <td>3370.120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1</td>\n",
       "      <td>1024</td>\n",
       "      <td>25</td>\n",
       "      <td>104106</td>\n",
       "      <td>4164.240000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1</td>\n",
       "      <td>1025</td>\n",
       "      <td>24</td>\n",
       "      <td>109794</td>\n",
       "      <td>4574.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1</td>\n",
       "      <td>1026</td>\n",
       "      <td>25</td>\n",
       "      <td>112631</td>\n",
       "      <td>4505.240000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1</td>\n",
       "      <td>1027</td>\n",
       "      <td>25</td>\n",
       "      <td>75645</td>\n",
       "      <td>3025.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1</td>\n",
       "      <td>1028</td>\n",
       "      <td>24</td>\n",
       "      <td>138661</td>\n",
       "      <td>5777.541667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1</td>\n",
       "      <td>1029</td>\n",
       "      <td>25</td>\n",
       "      <td>119240</td>\n",
       "      <td>4769.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1468</th>\n",
       "      <td>2</td>\n",
       "      <td>1719</td>\n",
       "      <td>24</td>\n",
       "      <td>131328</td>\n",
       "      <td>5472.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469</th>\n",
       "      <td>2</td>\n",
       "      <td>1720</td>\n",
       "      <td>24</td>\n",
       "      <td>108830</td>\n",
       "      <td>4534.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1470</th>\n",
       "      <td>2</td>\n",
       "      <td>1721</td>\n",
       "      <td>24</td>\n",
       "      <td>81889</td>\n",
       "      <td>3412.041667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1471</th>\n",
       "      <td>2</td>\n",
       "      <td>1722</td>\n",
       "      <td>24</td>\n",
       "      <td>69217</td>\n",
       "      <td>2884.041667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1472</th>\n",
       "      <td>2</td>\n",
       "      <td>1723</td>\n",
       "      <td>24</td>\n",
       "      <td>113130</td>\n",
       "      <td>4713.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1473</th>\n",
       "      <td>2</td>\n",
       "      <td>1724</td>\n",
       "      <td>24</td>\n",
       "      <td>62438</td>\n",
       "      <td>2601.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1474</th>\n",
       "      <td>2</td>\n",
       "      <td>1725</td>\n",
       "      <td>24</td>\n",
       "      <td>169182</td>\n",
       "      <td>7049.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1475</th>\n",
       "      <td>2</td>\n",
       "      <td>1726</td>\n",
       "      <td>24</td>\n",
       "      <td>82982</td>\n",
       "      <td>3457.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1476</th>\n",
       "      <td>2</td>\n",
       "      <td>1727</td>\n",
       "      <td>24</td>\n",
       "      <td>164620</td>\n",
       "      <td>6859.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1477</th>\n",
       "      <td>2</td>\n",
       "      <td>1728</td>\n",
       "      <td>24</td>\n",
       "      <td>92813</td>\n",
       "      <td>3867.208333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1478</th>\n",
       "      <td>2</td>\n",
       "      <td>1729</td>\n",
       "      <td>24</td>\n",
       "      <td>101155</td>\n",
       "      <td>4214.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1479</th>\n",
       "      <td>2</td>\n",
       "      <td>1730</td>\n",
       "      <td>24</td>\n",
       "      <td>81578</td>\n",
       "      <td>3399.083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1480</th>\n",
       "      <td>2</td>\n",
       "      <td>1731</td>\n",
       "      <td>24</td>\n",
       "      <td>240482</td>\n",
       "      <td>10020.083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1481</th>\n",
       "      <td>2</td>\n",
       "      <td>1732</td>\n",
       "      <td>24</td>\n",
       "      <td>137986</td>\n",
       "      <td>5749.416667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1482</th>\n",
       "      <td>2</td>\n",
       "      <td>1733</td>\n",
       "      <td>24</td>\n",
       "      <td>164643</td>\n",
       "      <td>6860.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1483</th>\n",
       "      <td>2</td>\n",
       "      <td>1734</td>\n",
       "      <td>24</td>\n",
       "      <td>108226</td>\n",
       "      <td>4509.416667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1484</th>\n",
       "      <td>2</td>\n",
       "      <td>1735</td>\n",
       "      <td>24</td>\n",
       "      <td>117396</td>\n",
       "      <td>4891.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1485</th>\n",
       "      <td>2</td>\n",
       "      <td>1736</td>\n",
       "      <td>24</td>\n",
       "      <td>60550</td>\n",
       "      <td>2522.916667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1486</th>\n",
       "      <td>2</td>\n",
       "      <td>1737</td>\n",
       "      <td>24</td>\n",
       "      <td>72551</td>\n",
       "      <td>3022.958333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1487</th>\n",
       "      <td>2</td>\n",
       "      <td>1738</td>\n",
       "      <td>24</td>\n",
       "      <td>96091</td>\n",
       "      <td>4003.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1488</th>\n",
       "      <td>2</td>\n",
       "      <td>1739</td>\n",
       "      <td>24</td>\n",
       "      <td>103147</td>\n",
       "      <td>4297.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1489</th>\n",
       "      <td>2</td>\n",
       "      <td>1740</td>\n",
       "      <td>24</td>\n",
       "      <td>84607</td>\n",
       "      <td>3525.291667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1490</th>\n",
       "      <td>2</td>\n",
       "      <td>1741</td>\n",
       "      <td>24</td>\n",
       "      <td>118488</td>\n",
       "      <td>4937.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491</th>\n",
       "      <td>2</td>\n",
       "      <td>1742</td>\n",
       "      <td>24</td>\n",
       "      <td>142638</td>\n",
       "      <td>5943.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1492</th>\n",
       "      <td>2</td>\n",
       "      <td>1743</td>\n",
       "      <td>24</td>\n",
       "      <td>61173</td>\n",
       "      <td>2548.875000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1493</th>\n",
       "      <td>2</td>\n",
       "      <td>1744</td>\n",
       "      <td>24</td>\n",
       "      <td>105852</td>\n",
       "      <td>4410.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1494</th>\n",
       "      <td>2</td>\n",
       "      <td>1745</td>\n",
       "      <td>24</td>\n",
       "      <td>116303</td>\n",
       "      <td>4845.958333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1495</th>\n",
       "      <td>2</td>\n",
       "      <td>1746</td>\n",
       "      <td>24</td>\n",
       "      <td>82453</td>\n",
       "      <td>3435.541667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1496</th>\n",
       "      <td>2</td>\n",
       "      <td>1747</td>\n",
       "      <td>24</td>\n",
       "      <td>123292</td>\n",
       "      <td>5137.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1497</th>\n",
       "      <td>2</td>\n",
       "      <td>1748</td>\n",
       "      <td>24</td>\n",
       "      <td>107401</td>\n",
       "      <td>4475.041667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1498 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      D_Month  StoreID  IsOpen  NumberOfSales  Sales_per_open_day\n",
       "0           1     1000      25         185231         7409.240000\n",
       "1           1     1001      25          66189         2647.560000\n",
       "2           1     1002      25         114663         4586.520000\n",
       "3           1     1003      25         126490         5059.600000\n",
       "4           1     1004      24          95016         3959.000000\n",
       "5           1     1005      25         117460         4698.400000\n",
       "6           1     1006      25         145685         5827.400000\n",
       "7           1     1007      25          94273         3770.920000\n",
       "8           1     1008      25         163494         6539.760000\n",
       "9           1     1009      25         127759         5110.360000\n",
       "10          1     1010      25         138555         5542.200000\n",
       "11          1     1011      25          68515         2740.600000\n",
       "12          1     1012      25         155257         6210.280000\n",
       "13          1     1013      25         116737         4669.480000\n",
       "14          1     1014      25         112892         4515.680000\n",
       "15          1     1015      25         141888         5675.520000\n",
       "16          1     1016      24          88160         3673.333333\n",
       "17          1     1017      25          75374         3014.960000\n",
       "18          1     1018      25          85330         3413.200000\n",
       "19          1     1019      25         108312         4332.480000\n",
       "20          1     1020      24         131138         5464.083333\n",
       "21          1     1021      25         131584         5263.360000\n",
       "22          1     1022      24         129149         5381.208333\n",
       "23          1     1023      25          84253         3370.120000\n",
       "24          1     1024      25         104106         4164.240000\n",
       "25          1     1025      24         109794         4574.750000\n",
       "26          1     1026      25         112631         4505.240000\n",
       "27          1     1027      25          75645         3025.800000\n",
       "28          1     1028      24         138661         5777.541667\n",
       "29          1     1029      25         119240         4769.600000\n",
       "...       ...      ...     ...            ...                 ...\n",
       "1468        2     1719      24         131328         5472.000000\n",
       "1469        2     1720      24         108830         4534.583333\n",
       "1470        2     1721      24          81889         3412.041667\n",
       "1471        2     1722      24          69217         2884.041667\n",
       "1472        2     1723      24         113130         4713.750000\n",
       "1473        2     1724      24          62438         2601.583333\n",
       "1474        2     1725      24         169182         7049.250000\n",
       "1475        2     1726      24          82982         3457.583333\n",
       "1476        2     1727      24         164620         6859.166667\n",
       "1477        2     1728      24          92813         3867.208333\n",
       "1478        2     1729      24         101155         4214.791667\n",
       "1479        2     1730      24          81578         3399.083333\n",
       "1480        2     1731      24         240482        10020.083333\n",
       "1481        2     1732      24         137986         5749.416667\n",
       "1482        2     1733      24         164643         6860.125000\n",
       "1483        2     1734      24         108226         4509.416667\n",
       "1484        2     1735      24         117396         4891.500000\n",
       "1485        2     1736      24          60550         2522.916667\n",
       "1486        2     1737      24          72551         3022.958333\n",
       "1487        2     1738      24          96091         4003.791667\n",
       "1488        2     1739      24         103147         4297.791667\n",
       "1489        2     1740      24          84607         3525.291667\n",
       "1490        2     1741      24         118488         4937.000000\n",
       "1491        2     1742      24         142638         5943.250000\n",
       "1492        2     1743      24          61173         2548.875000\n",
       "1493        2     1744      24         105852         4410.500000\n",
       "1494        2     1745      24         116303         4845.958333\n",
       "1495        2     1746      24          82453         3435.541667\n",
       "1496        2     1747      24         123292         5137.166667\n",
       "1497        2     1748      24         107401         4475.041667\n",
       "\n",
       "[1498 rows x 5 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t2017 = t2017.reset_index()\n",
    "t2017['Sales_per_open_day']=t2017['NumberOfSales']/t2017['IsOpen']\n",
    "t2017"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we just have to put the sale per open day of the correct month in every open day in the test2018 dataset and evaluate the BIP error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#let's try to put into store 1000 in month 1, then create a loop, this does not work\n",
    "#test2018.loc[((test2018['D_Month'] == 1) & (test2018['StoreID'] == 1000) & (test2018['IsOpen'] == 1))]=['_NumberOfSales']=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/nyxgear/anaconda3/lib/python3.6/site-packages/pandas/core/indexing.py:362: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[key] = _infer_fill_value(value)\n",
      "/home/nyxgear/anaconda3/lib/python3.6/site-packages/pandas/core/indexing.py:543: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n"
     ]
    }
   ],
   "source": [
    "test2018.loc[(test2018.D_Month==1) & (test2018.StoreID==1000) & (test2018.IsOpen==1),'_NumberOfSales']=\\\n",
    "        t2017.loc[(t2017.StoreID==1000) & (t2017.D_Month==1)]['Sales_per_open_day'][0]\n",
    "#df[ (df.A=='blue') & (df.B=='red') & (df.C=='square') ]['D'] = 'succeed'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>IsOpen</th>\n",
       "      <th>StoreID</th>\n",
       "      <th>D_Month</th>\n",
       "      <th>NumberOfSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>670</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>8540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>10364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>672</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>4676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>673</th>\n",
       "      <td>0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>6267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>675</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>5953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>676</th>\n",
       "      <td>0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>677</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>9075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>678</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>7856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>7423</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     IsOpen  StoreID  D_Month  NumberOfSales\n",
       "670       1     1000        1           8540\n",
       "671       1     1000        1          10364\n",
       "672       1     1000        1           4676\n",
       "673       0     1000        1              0\n",
       "674       1     1000        1           6267\n",
       "675       1     1000        1           5953\n",
       "676       0     1000        1              0\n",
       "677       1     1000        1           9075\n",
       "678       1     1000        1           7856\n",
       "679       1     1000        1           7423"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#features.append('_NumberOfSales')\n",
    "test2018[features][:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok I'm finally able to perform the whole assignment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/nyxgear/anaconda3/lib/python3.6/site-packages/pandas/core/indexing.py:543: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n"
     ]
    }
   ],
   "source": [
    "for store in range (1000,1749):\n",
    "    test2018.loc[(test2018.D_Month==1) & (test2018.StoreID==store) & (test2018.IsOpen==1),'_NumberOfSales']= \\\n",
    "        t2017.loc[(t2017.StoreID==store) & (t2017.D_Month==1)]['Sales_per_open_day'][store-1000]\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/nyxgear/anaconda3/lib/python3.6/site-packages/pandas/core/indexing.py:543: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n"
     ]
    }
   ],
   "source": [
    "for store in range (1000,1749):\n",
    "    test2018.loc[(test2018.D_Month==2) & (test2018.StoreID==store) & (test2018.IsOpen==1),'_NumberOfSales']=\\\n",
    "        t2017.loc[(t2017.StoreID==store) & (t2017.D_Month==2)]['Sales_per_open_day'][store-251]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Min_TemperatureC</th>\n",
       "      <th>Min_VisibilitykM</th>\n",
       "      <th>Precipitationmm</th>\n",
       "      <th>WindDirDegrees</th>\n",
       "      <th>D_Day</th>\n",
       "      <th>D_DayOfYear</th>\n",
       "      <th>D_Month</th>\n",
       "      <th>D_Year</th>\n",
       "      <th>D_DayOfweek</th>\n",
       "      <th>_NumberOfSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1440</th>\n",
       "      <td>1001</td>\n",
       "      <td>11/02/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Super Market</td>\n",
       "      <td>With Non-Food Department</td>\n",
       "      <td>16755</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>-2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>318</td>\n",
       "      <td>11</td>\n",
       "      <td>42</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1441</th>\n",
       "      <td>1001</td>\n",
       "      <td>12/02/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Super Market</td>\n",
       "      <td>With Non-Food Department</td>\n",
       "      <td>16755</td>\n",
       "      <td>0</td>\n",
       "      <td>173</td>\n",
       "      <td>...</td>\n",
       "      <td>-2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.02</td>\n",
       "      <td>286</td>\n",
       "      <td>12</td>\n",
       "      <td>43</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>2699.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1442</th>\n",
       "      <td>1001</td>\n",
       "      <td>13/02/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Super Market</td>\n",
       "      <td>With Non-Food Department</td>\n",
       "      <td>16755</td>\n",
       "      <td>0</td>\n",
       "      <td>168</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>285</td>\n",
       "      <td>13</td>\n",
       "      <td>44</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>2699.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1443</th>\n",
       "      <td>1001</td>\n",
       "      <td>14/02/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Super Market</td>\n",
       "      <td>With Non-Food Department</td>\n",
       "      <td>16755</td>\n",
       "      <td>0</td>\n",
       "      <td>172</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>303</td>\n",
       "      <td>14</td>\n",
       "      <td>45</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>2699.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1444</th>\n",
       "      <td>1001</td>\n",
       "      <td>15/02/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Super Market</td>\n",
       "      <td>With Non-Food Department</td>\n",
       "      <td>16755</td>\n",
       "      <td>0</td>\n",
       "      <td>196</td>\n",
       "      <td>...</td>\n",
       "      <td>-5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>157</td>\n",
       "      <td>15</td>\n",
       "      <td>46</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "      <td>2699.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1445</th>\n",
       "      <td>1001</td>\n",
       "      <td>16/02/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Super Market</td>\n",
       "      <td>With Non-Food Department</td>\n",
       "      <td>16755</td>\n",
       "      <td>0</td>\n",
       "      <td>234</td>\n",
       "      <td>...</td>\n",
       "      <td>-2</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>155</td>\n",
       "      <td>16</td>\n",
       "      <td>47</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>2699.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1446</th>\n",
       "      <td>1001</td>\n",
       "      <td>17/02/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Super Market</td>\n",
       "      <td>With Non-Food Department</td>\n",
       "      <td>16755</td>\n",
       "      <td>0</td>\n",
       "      <td>197</td>\n",
       "      <td>...</td>\n",
       "      <td>-1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>153</td>\n",
       "      <td>17</td>\n",
       "      <td>48</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>5</td>\n",
       "      <td>2699.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1447</th>\n",
       "      <td>1001</td>\n",
       "      <td>18/02/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Super Market</td>\n",
       "      <td>With Non-Food Department</td>\n",
       "      <td>16755</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>-2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>99</td>\n",
       "      <td>18</td>\n",
       "      <td>49</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1448</th>\n",
       "      <td>1001</td>\n",
       "      <td>19/02/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Super Market</td>\n",
       "      <td>With Non-Food Department</td>\n",
       "      <td>16755</td>\n",
       "      <td>0</td>\n",
       "      <td>248</td>\n",
       "      <td>...</td>\n",
       "      <td>-2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>76</td>\n",
       "      <td>19</td>\n",
       "      <td>50</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>2699.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1449</th>\n",
       "      <td>1001</td>\n",
       "      <td>20/02/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Super Market</td>\n",
       "      <td>With Non-Food Department</td>\n",
       "      <td>16755</td>\n",
       "      <td>0</td>\n",
       "      <td>181</td>\n",
       "      <td>...</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>182</td>\n",
       "      <td>20</td>\n",
       "      <td>51</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>2699.791667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      StoreID        Date  IsHoliday  IsOpen  HasPromotions     StoreType  \\\n",
       "1440     1001  11/02/2018          0       0              0  Super Market   \n",
       "1441     1001  12/02/2018          0       1              0  Super Market   \n",
       "1442     1001  13/02/2018          0       1              0  Super Market   \n",
       "1443     1001  14/02/2018          0       1              0  Super Market   \n",
       "1444     1001  15/02/2018          0       1              0  Super Market   \n",
       "1445     1001  16/02/2018          0       1              0  Super Market   \n",
       "1446     1001  17/02/2018          0       1              0  Super Market   \n",
       "1447     1001  18/02/2018          0       0              0  Super Market   \n",
       "1448     1001  19/02/2018          0       1              1  Super Market   \n",
       "1449     1001  20/02/2018          0       1              1  Super Market   \n",
       "\n",
       "                AssortmentType  NearestCompetitor  Region  NumberOfCustomers  \\\n",
       "1440  With Non-Food Department              16755       0                  0   \n",
       "1441  With Non-Food Department              16755       0                173   \n",
       "1442  With Non-Food Department              16755       0                168   \n",
       "1443  With Non-Food Department              16755       0                172   \n",
       "1444  With Non-Food Department              16755       0                196   \n",
       "1445  With Non-Food Department              16755       0                234   \n",
       "1446  With Non-Food Department              16755       0                197   \n",
       "1447  With Non-Food Department              16755       0                  0   \n",
       "1448  With Non-Food Department              16755       0                248   \n",
       "1449  With Non-Food Department              16755       0                181   \n",
       "\n",
       "           ...        Min_TemperatureC  Min_VisibilitykM  Precipitationmm  \\\n",
       "1440       ...                      -2               6.0             0.00   \n",
       "1441       ...                      -2               3.0             1.02   \n",
       "1442       ...                       3               4.0             0.00   \n",
       "1443       ...                       0              10.0             0.00   \n",
       "1444       ...                      -5               0.0             0.00   \n",
       "1445       ...                      -2               9.0             0.00   \n",
       "1446       ...                      -1               6.0             0.00   \n",
       "1447       ...                      -2               8.0             0.00   \n",
       "1448       ...                      -2               2.0             0.00   \n",
       "1449       ...                      -1               1.0             0.00   \n",
       "\n",
       "      WindDirDegrees  D_Day D_DayOfYear  D_Month  D_Year  D_DayOfweek  \\\n",
       "1440             318     11          42        2    2018            6   \n",
       "1441             286     12          43        2    2018            0   \n",
       "1442             285     13          44        2    2018            1   \n",
       "1443             303     14          45        2    2018            2   \n",
       "1444             157     15          46        2    2018            3   \n",
       "1445             155     16          47        2    2018            4   \n",
       "1446             153     17          48        2    2018            5   \n",
       "1447              99     18          49        2    2018            6   \n",
       "1448              76     19          50        2    2018            0   \n",
       "1449             182     20          51        2    2018            1   \n",
       "\n",
       "      _NumberOfSales  \n",
       "1440             NaN  \n",
       "1441     2699.791667  \n",
       "1442     2699.791667  \n",
       "1443     2699.791667  \n",
       "1444     2699.791667  \n",
       "1445     2699.791667  \n",
       "1446     2699.791667  \n",
       "1447             NaN  \n",
       "1448     2699.791667  \n",
       "1449     2699.791667  \n",
       "\n",
       "[10 rows x 42 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test2018[100:110]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## BIP Error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of regions identified: 11\n",
      "BIP total error: 0.05346477095060351\n"
     ]
    }
   ],
   "source": [
    "from BIP import get_BIP_error\n",
    "error1 = get_BIP_error(test2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#from BIP import get_BIP_error_isopenfiltered\n",
    "#error2 = get_BIP_error_isopenfiltered(test2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "features.append('Region')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>IsOpen</th>\n",
       "      <th>StoreID</th>\n",
       "      <th>D_Month</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>670</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>8540</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>10364</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>672</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>4676</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>673</th>\n",
       "      <td>0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>6267</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>675</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>5953</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>676</th>\n",
       "      <td>0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>677</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>9075</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>678</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>7856</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>7423</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>6283</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>681</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>7213</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>682</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>5738</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683</th>\n",
       "      <td>0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>8902</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>685</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>8505</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>686</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>7093</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>687</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>7061</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>8051</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>689</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>6790</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>690</th>\n",
       "      <td>0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>691</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>6504</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>692</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>5288</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>693</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>5608</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>5584</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>695</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>5664</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>696</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>5489</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>697</th>\n",
       "      <td>0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>7989</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>699</th>\n",
       "      <td>1</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>7747</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522991</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>1</td>\n",
       "      <td>5547</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522992</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>1</td>\n",
       "      <td>4095</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522993</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>4668</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522994</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>5384</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522995</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>2976</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522996</th>\n",
       "      <td>0</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522997</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>6173</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522998</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>5239</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522999</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>4417</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523000</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>4932</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523001</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>4991</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523002</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>2203</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523003</th>\n",
       "      <td>0</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523004</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>3480</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523005</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>3765</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523006</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>3314</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523007</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>3786</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523008</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>3622</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523009</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>2461</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523010</th>\n",
       "      <td>0</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523011</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>4930</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523012</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>5089</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523013</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>4627</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523014</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>4335</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523015</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>4620</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523016</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>2222</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523017</th>\n",
       "      <td>0</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523018</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>2994</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523019</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>3935</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523020</th>\n",
       "      <td>1</td>\n",
       "      <td>1748</td>\n",
       "      <td>2</td>\n",
       "      <td>3604</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>43816 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        IsOpen  StoreID  D_Month  NumberOfSales  Region\n",
       "670          1     1000        1           8540       7\n",
       "671          1     1000        1          10364       7\n",
       "672          1     1000        1           4676       7\n",
       "673          0     1000        1              0       7\n",
       "674          1     1000        1           6267       7\n",
       "675          1     1000        1           5953       7\n",
       "676          0     1000        1              0       7\n",
       "677          1     1000        1           9075       7\n",
       "678          1     1000        1           7856       7\n",
       "679          1     1000        1           7423       7\n",
       "680          1     1000        1           6283       7\n",
       "681          1     1000        1           7213       7\n",
       "682          1     1000        1           5738       7\n",
       "683          0     1000        1              0       7\n",
       "684          1     1000        1           8902       7\n",
       "685          1     1000        1           8505       7\n",
       "686          1     1000        1           7093       7\n",
       "687          1     1000        1           7061       7\n",
       "688          1     1000        1           8051       7\n",
       "689          1     1000        1           6790       7\n",
       "690          0     1000        1              0       7\n",
       "691          1     1000        1           6504       7\n",
       "692          1     1000        1           5288       7\n",
       "693          1     1000        1           5608       7\n",
       "694          1     1000        1           5584       7\n",
       "695          1     1000        1           5664       7\n",
       "696          1     1000        1           5489       7\n",
       "697          0     1000        1              0       7\n",
       "698          1     1000        1           7989       7\n",
       "699          1     1000        1           7747       7\n",
       "...        ...      ...      ...            ...     ...\n",
       "522991       1     1748        1           5547       7\n",
       "522992       1     1748        1           4095       7\n",
       "522993       1     1748        2           4668       7\n",
       "522994       1     1748        2           5384       7\n",
       "522995       1     1748        2           2976       7\n",
       "522996       0     1748        2              0       7\n",
       "522997       1     1748        2           6173       7\n",
       "522998       1     1748        2           5239       7\n",
       "522999       1     1748        2           4417       7\n",
       "523000       1     1748        2           4932       7\n",
       "523001       1     1748        2           4991       7\n",
       "523002       1     1748        2           2203       7\n",
       "523003       0     1748        2              0       7\n",
       "523004       1     1748        2           3480       7\n",
       "523005       1     1748        2           3765       7\n",
       "523006       1     1748        2           3314       7\n",
       "523007       1     1748        2           3786       7\n",
       "523008       1     1748        2           3622       7\n",
       "523009       1     1748        2           2461       7\n",
       "523010       0     1748        2              0       7\n",
       "523011       1     1748        2           4930       7\n",
       "523012       1     1748        2           5089       7\n",
       "523013       1     1748        2           4627       7\n",
       "523014       1     1748        2           4335       7\n",
       "523015       1     1748        2           4620       7\n",
       "523016       1     1748        2           2222       7\n",
       "523017       0     1748        2              0       7\n",
       "523018       1     1748        2           2994       7\n",
       "523019       1     1748        2           3935       7\n",
       "523020       1     1748        2           3604       7\n",
       "\n",
       "[43816 rows x 5 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test2018[features]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "if predict_for_submission:\n",
    "    test2018.to_csv('./dataset/submission_test_m34_70_Model_monthly_average.csv', index=False)\n",
    "else:\n",
    "    test2018.to_csv('./dataset/test_m12_70_Model_monthly_average.csv', index=False)"
   ]
  }
 ],
 "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
}