JF_munging_v1.2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data munging starts: \n",
    "### Importing packages and data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "import numpy as np\n",
    "from numpy import double\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading data start...\n",
      "Complete!\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/jfdarre/anaconda/lib/python2.7/site-packages/pandas/io/parsers.py:1170: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  data = self._reader.read(nrows)\n"
     ]
    }
   ],
   "source": [
    "print(\"Loading data start...\")\n",
    "# train and test set provided by Rossmann\n",
    "train = pd.read_csv(\"input/train.csv\")\n",
    "test = pd.read_csv(\"input/test.csv\")\n",
    "print(\"Complete!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading data start...\n",
      "Complete!\n"
     ]
    }
   ],
   "source": [
    "print(\"Loading data start...\")\n",
    "# external data\n",
    "store = pd.read_csv(\"input/store.csv\")\n",
    "states = pd.read_csv(\"input/store_states.csv\")\n",
    "eco_data = pd.read_csv(\"input/economic_data.csv\")\n",
    "print(\"Complete!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data munging part I:\n",
    "### Clean up, merges and dates features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 1 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 1: Impute Open = 1 for store 622 in test data\n",
    "test['Open'].fillna(1, inplace=True)\n",
    "print 'Step 1 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 2 Complete\n"
     ]
    }
   ],
   "source": [
    "# 2: Merging the train and test for easy implementation of features\n",
    "train['type'] = 'train'\n",
    "test['type'] = 'test'\n",
    "all_data = [train, test]\n",
    "all_data = pd.concat(all_data)\n",
    "print 'Step 2 Complete'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 3 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 3: Separate date columns\n",
    "all_data['year']  = all_data.Date.apply(lambda x: x.split('-')[0])\n",
    "all_data['year']  = all_data['year'].astype(int)\n",
    "all_data['month'] = all_data.Date.apply(lambda x: x.split('-')[1])\n",
    "all_data['month'] = all_data['month'].astype(int)\n",
    "all_data['day']   = all_data.Date.apply(lambda x: x.split('-')[2])\n",
    "all_data['day']   = all_data['day'].astype(int)\n",
    "print 'Step 3 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 4 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 4: Withdraw multiple date features\n",
    "all_data['Date']             = pd.to_datetime(all_data['Date'])\n",
    "all_data['day_of_year']      = all_data['Date'].dt.dayofyear\n",
    "all_data['quarter']          = all_data['Date'].dt.quarter\n",
    "all_data['is_month_start']   = all_data['Date'].dt.is_month_start.astype(int)\n",
    "all_data['is_month_end']     = all_data['Date'].dt.is_month_end.astype(int)\n",
    "all_data['is_quarter_start'] = all_data['Date'].dt.is_quarter_start.astype(int)\n",
    "all_data['is_quarter_end']   = all_data['Date'].dt.is_quarter_end.astype(int)\n",
    "all_data['weeknum']          = np.ceil(all_data['day_of_year']/7.)\n",
    "print 'Step 4 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 5 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 5: Standardize StateHoliday column.\n",
    "all_data.ix[(all_data['StateHoliday'] == 0), 'StateHoliday'] = '0'\n",
    "print 'Step 5 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 6 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 6: Setting 'Open' to 0 if Sales are 0\n",
    "all_data.ix[(all_data['Sales'] == 0), 'Open'] = 0\n",
    "print 'Step 6 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 7 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 7: Create duration of promo 1\n",
    "all_data = all_data.sort(columns = ['Store', 'Date'], ascending = True, na_position = 'last')\n",
    "all_data['PromoFirstDate'] = 0\n",
    "all_data.ix[((all_data['Store'] == all_data['Store'].shift(1)) &\n",
    "          (all_data['Promo'] == 1 ) & (all_data['Promo'].shift(1) ==0)),\n",
    "         'PromoFirstDate'] = 1\n",
    "print 'Step 7 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 8 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 8: Joining States:\n",
    "all_data = pd.merge(all_data, states, on = 'Store', how = 'inner')\n",
    "print 'Step 8 Complete.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data munging part II: \n",
    "### Adding some usefull functions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "function 1: rstr added\n"
     ]
    }
   ],
   "source": [
    "# f1: Creating a function to summarize our data\n",
    "def rstr(df): \n",
    "    print df.shape\n",
    "    print '=' * 72\n",
    "    print df.apply(lambda x: [x.unique()])\n",
    "    print '=' * 72\n",
    "    print pd.isnull(df).any()\n",
    "print 'function 1: rstr added'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "function 2: shif_col added\n"
     ]
    }
   ],
   "source": [
    "# f2: Creating a function to shift columns and making sure to distinguish between stores\n",
    "def shift_col(df, col_name, n):\n",
    "    df[col_name + str(n)] = df[col_name].shift(n)\n",
    "    df.ix[(df['Store'] != df['Store'].shift(n)), col_name + str(n)] = float('NaN')\n",
    "print 'function 2: shif_col added'    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# f2.1: Demonstrating what shift_col does:\n",
    "# temp = pd.DataFrame({\n",
    "#         'Sales': [1,2,3,4,5,1,2,3,4,5],\n",
    "#         'Store': [1,1,1,1,1,2,2,2,2,2]\n",
    "#     })\n",
    "# shift_col(temp, 'Sales', 2)\n",
    "# shift_col(temp, 'Sales', -1)\n",
    "# temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data munging part III: \n",
    "### Adding Schedule BUT NO historic Sales instead we add avg sales statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 9: now we can add previous sales for the past 21 days:\n",
    "# for i in range(1,22):\n",
    "#     shift_col(all_data, 'Sales', i)\n",
    "# print 'Step 9 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 9 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 9: now we can add previous sales for the past 21 days:\n",
    "# Creating our sales statistics\n",
    "temp = pd.Series.to_frame(pd.groupby(all_data, ['Store','Promo'])['Sales'].mean(), name = 'Sales_all_avg').reset_index()\n",
    "temp1 = pd.Series.to_frame(pd.groupby(all_data, ['Store','Promo','month'])['Sales'].mean(), name = 'Sales_month_avg').reset_index()\n",
    "temp2 = pd.Series.to_frame(pd.groupby(all_data, ['Store','Promo','DayOfWeek'])['Sales'].mean(), name = 'Sales_day_avg').reset_index()\n",
    "\n",
    "# Merging our sales statistics\n",
    "all_data = pd.merge(all_data, temp, on = ['Store','Promo'], how = 'left')\n",
    "all_data = pd.merge(all_data, temp1, on = ['Store','Promo','month'], how = 'left')\n",
    "all_data = pd.merge(all_data, temp2, on = ['Store','Promo','DayOfWeek'], how = 'left')\n",
    "\n",
    "print 'Step 9 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customers</th>\n",
       "      <th>Date</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Id</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>Sales</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>Store</th>\n",
       "      <th>...</th>\n",
       "      <th>is_month_start</th>\n",
       "      <th>is_month_end</th>\n",
       "      <th>is_quarter_start</th>\n",
       "      <th>is_quarter_end</th>\n",
       "      <th>weeknum</th>\n",
       "      <th>PromoFirstDate</th>\n",
       "      <th>State</th>\n",
       "      <th>Sales_all_avg</th>\n",
       "      <th>Sales_month_avg</th>\n",
       "      <th>Sales_day_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1058296</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>4</td>\n",
       "      <td>856</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1115</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38</td>\n",
       "      <td>0</td>\n",
       "      <td>HE</td>\n",
       "      <td>7073.266667</td>\n",
       "      <td>6751.272727</td>\n",
       "      <td>6411.430556</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Customers       Date  DayOfWeek   Id  Open  Promo  Sales  \\\n",
       "1058296        NaN 2015-09-17          4  856     1      1    NaN   \n",
       "\n",
       "         SchoolHoliday StateHoliday  Store      ...       is_month_start  \\\n",
       "1058296              0            0   1115      ...                    0   \n",
       "\n",
       "         is_month_end  is_quarter_start  is_quarter_end  weeknum  \\\n",
       "1058296             0                 0               0       38   \n",
       "\n",
       "         PromoFirstDate  State  Sales_all_avg  Sales_month_avg  Sales_day_avg  \n",
       "1058296               0     HE    7073.266667      6751.272727    6411.430556  \n",
       "\n",
       "[1 rows x 26 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 10 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 10: now we can add previous open for the past 21 days:\n",
    "for i in range(1,15):\n",
    "    shift_col(all_data, 'Open', i)\n",
    "print 'Step 10 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 11 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 11: now we can add next open for the past 21 days:\n",
    "for i in range(1,15):\n",
    "    shift_col(all_data, 'Open', -i)\n",
    "print 'Step 11 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customers</th>\n",
       "      <th>Date</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Id</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>Sales</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>Store</th>\n",
       "      <th>...</th>\n",
       "      <th>Open-5</th>\n",
       "      <th>Open-6</th>\n",
       "      <th>Open-7</th>\n",
       "      <th>Open-8</th>\n",
       "      <th>Open-9</th>\n",
       "      <th>Open-10</th>\n",
       "      <th>Open-11</th>\n",
       "      <th>Open-12</th>\n",
       "      <th>Open-13</th>\n",
       "      <th>Open-14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customers       Date  DayOfWeek  Id  Open  Promo  Sales  SchoolHoliday  \\\n",
       "0          0 2013-01-01          2 NaN     0      0      0              1   \n",
       "\n",
       "  StateHoliday  Store   ...    Open-5  Open-6  Open-7  Open-8  Open-9  \\\n",
       "0            a      1   ...         0       1       1       1       1   \n",
       "\n",
       "   Open-10  Open-11  Open-12  Open-13  Open-14  \n",
       "0        1        1        0        1        1  \n",
       "\n",
       "[1 rows x 54 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data munging part IV: \n",
    "### Adding all weather data with historic and forecast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 12 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 12: loading all weather data into one dataFrame called weather:\n",
    "weather = pd.DataFrame()\n",
    "for i in all_data['State'].unique():\n",
    "    i = 'NI' if i == 'HB,NI' else i\n",
    "    temp = pd.read_csv(\"data_for_features/Data_Weather/\" + str(i) + \".csv\", sep = \";\")\n",
    "\n",
    "    i = 'HB,NI' if i == 'NI' else i\n",
    "    temp['State'] = i\n",
    "    \n",
    "    temp = [weather, temp]\n",
    "    weather = pd.concat(temp)\n",
    "\n",
    "weather['Date'] = pd.to_datetime(weather['Date'])\n",
    "print 'Step 12 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 12.1: Checking that step 12 was ok especially for state 'HB,NI'\n",
    "# weather[weather['State']=='HB,NI']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Max_TemperatureC</th>\n",
       "      <th>Mean_TemperatureC</th>\n",
       "      <th>Min_TemperatureC</th>\n",
       "      <th>Dew_PointC</th>\n",
       "      <th>MeanDew_PointC</th>\n",
       "      <th>Min_DewpointC</th>\n",
       "      <th>Max_Humidity</th>\n",
       "      <th>Mean_Humidity</th>\n",
       "      <th>Min_Humidity</th>\n",
       "      <th>...</th>\n",
       "      <th>Mean_VisibilityKm</th>\n",
       "      <th>Min_VisibilitykM</th>\n",
       "      <th>Max_Wind_SpeedKm_h</th>\n",
       "      <th>Mean_Wind_SpeedKm_h</th>\n",
       "      <th>Max_Gust_SpeedKm_h</th>\n",
       "      <th>Precipitationmm</th>\n",
       "      <th>CloudCover</th>\n",
       "      <th>Events</th>\n",
       "      <th>WindDirDegrees</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>93</td>\n",
       "      <td>80</td>\n",
       "      <td>59</td>\n",
       "      <td>...</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>23</td>\n",
       "      <td>14</td>\n",
       "      <td>39</td>\n",
       "      <td>2.03</td>\n",
       "      <td>6</td>\n",
       "      <td>Rain</td>\n",
       "      <td>206</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  Max_TemperatureC  Mean_TemperatureC  Min_TemperatureC  \\\n",
       "0 2013-01-01                 8                  6                 3   \n",
       "\n",
       "   Dew_PointC  MeanDew_PointC  Min_DewpointC  Max_Humidity  Mean_Humidity  \\\n",
       "0           6               3              1            93             80   \n",
       "\n",
       "   Min_Humidity  ...    Mean_VisibilityKm  Min_VisibilitykM  \\\n",
       "0            59  ...                   12                10   \n",
       "\n",
       "   Max_Wind_SpeedKm_h  Mean_Wind_SpeedKm_h  Max_Gust_SpeedKm_h  \\\n",
       "0                  23                   14                  39   \n",
       "\n",
       "   Precipitationmm  CloudCover  Events  WindDirDegrees  State  \n",
       "0             2.03           6    Rain             206     HE  \n",
       "\n",
       "[1 rows x 24 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 13 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 13: Merging the weather data to our dataset:\n",
    "all_data = pd.merge(all_data, weather, on = ['State', 'Date'], how = 'left')\n",
    "print 'Step 13 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customers</th>\n",
       "      <th>Date</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Id</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>Sales</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>Store</th>\n",
       "      <th>...</th>\n",
       "      <th>Max_VisibilityKm</th>\n",
       "      <th>Mean_VisibilityKm</th>\n",
       "      <th>Min_VisibilitykM</th>\n",
       "      <th>Max_Wind_SpeedKm_h</th>\n",
       "      <th>Mean_Wind_SpeedKm_h</th>\n",
       "      <th>Max_Gust_SpeedKm_h</th>\n",
       "      <th>Precipitationmm</th>\n",
       "      <th>CloudCover</th>\n",
       "      <th>Events</th>\n",
       "      <th>WindDirDegrees</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>31</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>23</td>\n",
       "      <td>14</td>\n",
       "      <td>39</td>\n",
       "      <td>2.03</td>\n",
       "      <td>6</td>\n",
       "      <td>Rain</td>\n",
       "      <td>206</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 76 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customers       Date  DayOfWeek  Id  Open  Promo  Sales  SchoolHoliday  \\\n",
       "0          0 2013-01-01          2 NaN     0      0      0              1   \n",
       "\n",
       "  StateHoliday  Store       ...       Max_VisibilityKm  Mean_VisibilityKm  \\\n",
       "0            a      1       ...                     31                 12   \n",
       "\n",
       "   Min_VisibilitykM  Max_Wind_SpeedKm_h  Mean_Wind_SpeedKm_h  \\\n",
       "0                10                  23                   14   \n",
       "\n",
       "   Max_Gust_SpeedKm_h  Precipitationmm  CloudCover  Events  WindDirDegrees  \n",
       "0                  39             2.03           6    Rain             206  \n",
       "\n",
       "[1 rows x 76 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 14 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 14: Adding some weather historic on mean temperature, max wind speed, precipitation and events:\n",
    "# last 14 days\n",
    "for i in range(1,15):\n",
    "    shift_col(all_data, 'Mean_TemperatureC', i)\n",
    "\n",
    "# next 3 days (forecast)\n",
    "for i in range(1,4):\n",
    "    shift_col(all_data, 'Mean_TemperatureC', -i)\n",
    "\n",
    "print 'Step 14 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 15 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 15: Adding some weather historic on mean temperature, max wind speed, precipitation and events:\n",
    "# last 14 days\n",
    "for i in range(1,8):\n",
    "    shift_col(all_data, 'Max_Wind_SpeedKm_h', i)\n",
    "\n",
    "# next 3 days (forecast)\n",
    "for i in range(1,4):\n",
    "    shift_col(all_data, 'Max_Wind_SpeedKm_h', -i)\n",
    "\n",
    "print 'Step 15 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 16 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 16: Adding some weather historic on mean temperature, max wind speed, precipitation and events:\n",
    "# last 14 days\n",
    "for i in range(1,8):\n",
    "    shift_col(all_data, 'Precipitationmm', i)\n",
    "\n",
    "# next 3 days (forecast)\n",
    "for i in range(1,4):\n",
    "    shift_col(all_data, 'Precipitationmm', -i)\n",
    "\n",
    "print 'Step 16 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 17 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 17: Adding some weather historic on mean temperature, max wind speed, precipitation and events:\n",
    "# last 14 days\n",
    "for i in range(1,15):\n",
    "    shift_col(all_data, 'Events', i)\n",
    "\n",
    "# next 3 days (forecast)\n",
    "for i in range(1,4):\n",
    "    shift_col(all_data, 'Events', -i)\n",
    "\n",
    "print 'Step 17 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customers</th>\n",
       "      <th>Date</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Id</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>Sales</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>Store</th>\n",
       "      <th>...</th>\n",
       "      <th>Events8</th>\n",
       "      <th>Events9</th>\n",
       "      <th>Events10</th>\n",
       "      <th>Events11</th>\n",
       "      <th>Events12</th>\n",
       "      <th>Events13</th>\n",
       "      <th>Events14</th>\n",
       "      <th>Events-1</th>\n",
       "      <th>Events-2</th>\n",
       "      <th>Events-3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rain</td>\n",
       "      <td>Rain</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 130 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customers       Date  DayOfWeek  Id  Open  Promo  Sales  SchoolHoliday  \\\n",
       "0          0 2013-01-01          2 NaN     0      0      0              1   \n",
       "\n",
       "  StateHoliday  Store    ...    Events8  Events9  Events10  Events11  \\\n",
       "0            a      1    ...        NaN      NaN       NaN       NaN   \n",
       "\n",
       "   Events12  Events13  Events14  Events-1  Events-2  Events-3  \n",
       "0       NaN       NaN       NaN       NaN      Rain      Rain  \n",
       "\n",
       "[1 rows x 130 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data munging part V: \n",
    "### Adding all economic data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 18 Complete\n"
     ]
    }
   ],
   "source": [
    "# 18: coverting date to match our all_data 'Date' otherwise merging crashes\n",
    "eco_data['Date'] = pd.to_datetime(eco_data['Date'])\n",
    "print 'Step 18 Complete'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>DAX</th>\n",
       "      <th>Merck</th>\n",
       "      <th>MSCI</th>\n",
       "      <th>CLeadIndic</th>\n",
       "      <th>BusConf</th>\n",
       "      <th>ConsConf</th>\n",
       "      <th>SP_POP_TOTL</th>\n",
       "      <th>SP_POP_GROW</th>\n",
       "      <th>EN_POP_DNST</th>\n",
       "      <th>...</th>\n",
       "      <th>IT_NET_USER_P2</th>\n",
       "      <th>TG_VAL_TOTL_GD_ZS</th>\n",
       "      <th>BX_TRF_PWKR_CD_DT</th>\n",
       "      <th>BX_KLT_DINV_CD_WD</th>\n",
       "      <th>HHDEBT</th>\n",
       "      <th>HHFA</th>\n",
       "      <th>HHDI</th>\n",
       "      <th>HHFT</th>\n",
       "      <th>HHSAVFORECAST</th>\n",
       "      <th>HHSAV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>7612.390137</td>\n",
       "      <td>99.83</td>\n",
       "      <td>24.4</td>\n",
       "      <td>99.22213</td>\n",
       "      <td>99.83537</td>\n",
       "      <td>99.86818</td>\n",
       "      <td>80425823</td>\n",
       "      <td>-1.691349</td>\n",
       "      <td>230.750625</td>\n",
       "      <td>...</td>\n",
       "      <td>82.349998</td>\n",
       "      <td>72.69033</td>\n",
       "      <td>1.643390e+10</td>\n",
       "      <td>5.465988e+10</td>\n",
       "      <td>95.47638</td>\n",
       "      <td>39.5527</td>\n",
       "      <td>0.623393</td>\n",
       "      <td>8.310776</td>\n",
       "      <td>9.13566</td>\n",
       "      <td>9.260378</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date          DAX  Merck  MSCI  CLeadIndic   BusConf  ConsConf  \\\n",
       "0 2013-01-01  7612.390137  99.83  24.4    99.22213  99.83537  99.86818   \n",
       "\n",
       "   SP_POP_TOTL  SP_POP_GROW  EN_POP_DNST    ...     IT_NET_USER_P2  \\\n",
       "0     80425823    -1.691349   230.750625    ...          82.349998   \n",
       "\n",
       "   TG_VAL_TOTL_GD_ZS  BX_TRF_PWKR_CD_DT  BX_KLT_DINV_CD_WD    HHDEBT     HHFA  \\\n",
       "0           72.69033       1.643390e+10       5.465988e+10  95.47638  39.5527   \n",
       "\n",
       "       HHDI      HHFT  HHSAVFORECAST     HHSAV  \n",
       "0  0.623393  8.310776        9.13566  9.260378  \n",
       "\n",
       "[1 rows x 40 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eco_data.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 19 Complete\n"
     ]
    }
   ],
   "source": [
    "# 19: merging all eco data to our data\n",
    "all_data = pd.merge(all_data, eco_data, on = ['Date'], how = 'left')\n",
    "print 'Step 19 Complete'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customers</th>\n",
       "      <th>Date</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Id</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>Sales</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>Store</th>\n",
       "      <th>...</th>\n",
       "      <th>IT_NET_USER_P2</th>\n",
       "      <th>TG_VAL_TOTL_GD_ZS</th>\n",
       "      <th>BX_TRF_PWKR_CD_DT</th>\n",
       "      <th>BX_KLT_DINV_CD_WD</th>\n",
       "      <th>HHDEBT</th>\n",
       "      <th>HHFA</th>\n",
       "      <th>HHDI</th>\n",
       "      <th>HHFT</th>\n",
       "      <th>HHSAVFORECAST</th>\n",
       "      <th>HHSAV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>82.349998</td>\n",
       "      <td>72.69033</td>\n",
       "      <td>1.643390e+10</td>\n",
       "      <td>5.465988e+10</td>\n",
       "      <td>95.47638</td>\n",
       "      <td>39.5527</td>\n",
       "      <td>0.623393</td>\n",
       "      <td>8.310776</td>\n",
       "      <td>9.13566</td>\n",
       "      <td>9.260378</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 169 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customers       Date  DayOfWeek  Id  Open  Promo  Sales  SchoolHoliday  \\\n",
       "0          0 2013-01-01          2 NaN     0      0      0              1   \n",
       "\n",
       "  StateHoliday  Store    ...    IT_NET_USER_P2  TG_VAL_TOTL_GD_ZS  \\\n",
       "0            a      1    ...         82.349998           72.69033   \n",
       "\n",
       "   BX_TRF_PWKR_CD_DT  BX_KLT_DINV_CD_WD    HHDEBT     HHFA      HHDI  \\\n",
       "0       1.643390e+10       5.465988e+10  95.47638  39.5527  0.623393   \n",
       "\n",
       "       HHFT  HHSAVFORECAST     HHSAV  \n",
       "0  8.310776        9.13566  9.260378  \n",
       "\n",
       "[1 rows x 169 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 20 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 20: loading all google trend data into one dataFrame called gooogle_trend:\n",
    "google_trend = pd.DataFrame()\n",
    "for i in all_data['State'].unique():\n",
    "    i = 'NI' if i == 'HB,NI' else i\n",
    "    temp = pd.read_csv(\"data_for_features/Data_google_trends/\" + str(i) + \".csv\", sep = \";\")\n",
    "\n",
    "    i = 'HB,NI' if i == 'NI' else i\n",
    "    temp['State'] = i\n",
    "    \n",
    "    temp = [google_trend, temp]\n",
    "    google_trend = pd.concat(temp)\n",
    "\n",
    "google_trend['Date'] = pd.to_datetime(google_trend['Date'])\n",
    "all_data = pd.merge(all_data, google_trend, on = ['State', 'Date'], how = 'left')\n",
    "print 'Step 20 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 21 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 21: adding the global google trend too:\n",
    "google_trend_all = pd.read_csv(\"data_for_features/Data_google_trends/ALL.csv\", sep = \";\")\n",
    "google_trend_all['Date'] = pd.to_datetime(google_trend_all['Date'])\n",
    "all_data = pd.merge(all_data, google_trend_all, on = ['Date'], how = 'left')\n",
    "print 'Step 21 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customers</th>\n",
       "      <th>Date</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Id</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>Sales</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>Store</th>\n",
       "      <th>...</th>\n",
       "      <th>BX_TRF_PWKR_CD_DT</th>\n",
       "      <th>BX_KLT_DINV_CD_WD</th>\n",
       "      <th>HHDEBT</th>\n",
       "      <th>HHFA</th>\n",
       "      <th>HHDI</th>\n",
       "      <th>HHFT</th>\n",
       "      <th>HHSAVFORECAST</th>\n",
       "      <th>HHSAV</th>\n",
       "      <th>Goog_trend</th>\n",
       "      <th>Goog_trend_ALL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1.643390e+10</td>\n",
       "      <td>5.465988e+10</td>\n",
       "      <td>95.47638</td>\n",
       "      <td>39.5527</td>\n",
       "      <td>0.623393</td>\n",
       "      <td>8.310776</td>\n",
       "      <td>9.13566</td>\n",
       "      <td>9.260378</td>\n",
       "      <td>61</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 171 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customers       Date  DayOfWeek  Id  Open  Promo  Sales  SchoolHoliday  \\\n",
       "0          0 2013-01-01          2 NaN     0      0      0              1   \n",
       "\n",
       "  StateHoliday  Store       ...       BX_TRF_PWKR_CD_DT  BX_KLT_DINV_CD_WD  \\\n",
       "0            a      1       ...            1.643390e+10       5.465988e+10   \n",
       "\n",
       "     HHDEBT     HHFA      HHDI      HHFT  HHSAVFORECAST     HHSAV  Goog_trend  \\\n",
       "0  95.47638  39.5527  0.623393  8.310776        9.13566  9.260378          61   \n",
       "\n",
       "   Goog_trend_ALL  \n",
       "0              61  \n",
       "\n",
       "[1 rows x 171 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data munging part VI: \n",
    "### Adding more features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 22 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 22: adding promo second date\n",
    "all_data['PromoSecondDate'] = 0\n",
    "all_data.ix[((all_data['Store'] == all_data['Store'].shift(2)) & \n",
    "             (all_data['Promo'] == 1 ) & \n",
    "             (all_data['PromoFirstDate'].shift(1) ==1)), 'PromoSecondDate'] = 1\n",
    "print 'Step 22 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 23 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 23: Adding refurbishement features:\n",
    "all_data['DayBeforeRefurb'] = 0\n",
    "all_data.ix[(\n",
    "        ((all_data['Store'] == all_data['Store'].shift(-1)) \n",
    "         & (all_data['Store'] == all_data['Store'].shift(-2))\n",
    "         & (all_data['Store'] == all_data['Store'].shift(-3)) \n",
    "         & (all_data['Store'] == all_data['Store'].shift(-4)) \n",
    "         & (all_data['Store'] == all_data['Store'].shift(-5))) & \n",
    "        ((all_data['Open'] == 1 ) \n",
    "         & (all_data['Open'].shift(-1) ==0) \n",
    "         & (all_data['Open'].shift(-2) ==0)\n",
    "         & (all_data['Open'].shift(-3) ==0)\n",
    "         & (all_data['Open'].shift(-4) ==0)\n",
    "         & (all_data['Open'].shift(-5) ==0))\n",
    "    ), 'DayBeforeRefurb'] = 1\n",
    "\n",
    "all_data['DayAfterRefurb'] = 0\n",
    "all_data.ix[(\n",
    "        ((all_data['Store'] == all_data['Store'].shift(1)) \n",
    "         & (all_data['Store'] == all_data['Store'].shift(2))\n",
    "         & (all_data['Store'] == all_data['Store'].shift(3)) \n",
    "         & (all_data['Store'] == all_data['Store'].shift(4)) \n",
    "         & (all_data['Store'] == all_data['Store'].shift(5))) & \n",
    "        ((all_data['Open'] == 1 ) \n",
    "         & (all_data['Open'].shift(1) ==0) \n",
    "         & (all_data['Open'].shift(2) ==0)\n",
    "         & (all_data['Open'].shift(3) ==0)\n",
    "         & (all_data['Open'].shift(4) ==0)\n",
    "         & (all_data['Open'].shift(5) ==0))\n",
    "    ), 'DayAfterRefurb'] = 1\n",
    "print 'Step 23 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 24 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 24: Adding more refurbishement features:\n",
    "all_data['DaysBeforeRefurb'] = 0\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(0) ==1), 'DaysBeforeRefurb'] = 1\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-1) ==1), 'DaysBeforeRefurb'] = 2\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-2) ==1), 'DaysBeforeRefurb'] = 3\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-3) ==1), 'DaysBeforeRefurb'] = 4\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-4) ==1), 'DaysBeforeRefurb'] = 5\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-5) ==1), 'DaysBeforeRefurb'] = 6\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-6) ==1), 'DaysBeforeRefurb'] = 7\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-7) ==1), 'DaysBeforeRefurb'] = 8\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-8) ==1), 'DaysBeforeRefurb'] = 9\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-9) ==1), 'DaysBeforeRefurb'] = 10\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-10) ==1), 'DaysBeforeRefurb'] = 11\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-11) ==1), 'DaysBeforeRefurb'] = 12\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-12) ==1), 'DaysBeforeRefurb'] = 13\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-13) ==1), 'DaysBeforeRefurb'] = 14\n",
    "all_data.ix[(all_data['DayBeforeRefurb'].shift(-14) ==1), 'DaysBeforeRefurb'] = 15\n",
    "\n",
    "all_data['DaysAfterRefurb'] = 0\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(0) ==1), 'DaysAfterRefurb'] = 1\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(1) ==1), 'DaysAfterRefurb'] = 2\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(2) ==1), 'DaysAfterRefurb'] = 3\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(3) ==1), 'DaysAfterRefurb'] = 4\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(4) ==1), 'DaysAfterRefurb'] = 5\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(5) ==1), 'DaysAfterRefurb'] = 6\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(6) ==1), 'DaysAfterRefurb'] = 7\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(7) ==1), 'DaysAfterRefurb'] = 8\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(8) ==1), 'DaysAfterRefurb'] = 9\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(9) ==1), 'DaysAfterRefurb'] = 10\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(10) ==1), 'DaysAfterRefurb'] = 11\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(11) ==1), 'DaysAfterRefurb'] = 12\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(12) ==1), 'DaysAfterRefurb'] = 13\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(13) ==1), 'DaysAfterRefurb'] = 14\n",
    "all_data.ix[(all_data['DayAfterRefurb'].shift(14) ==1), 'DaysAfterRefurb'] = 15\n",
    "print 'Step 24 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customers</th>\n",
       "      <th>Date</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Id</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>Sales</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>Store</th>\n",
       "      <th>...</th>\n",
       "      <th>HHFT</th>\n",
       "      <th>HHSAVFORECAST</th>\n",
       "      <th>HHSAV</th>\n",
       "      <th>Goog_trend</th>\n",
       "      <th>Goog_trend_ALL</th>\n",
       "      <th>PromoSecondDate</th>\n",
       "      <th>DayBeforeRefurb</th>\n",
       "      <th>DayAfterRefurb</th>\n",
       "      <th>DaysBeforeRefurb</th>\n",
       "      <th>DaysAfterRefurb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>8.310776</td>\n",
       "      <td>9.13566</td>\n",
       "      <td>9.260378</td>\n",
       "      <td>61</td>\n",
       "      <td>61</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 176 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customers       Date  DayOfWeek  Id  Open  Promo  Sales  SchoolHoliday  \\\n",
       "0          0 2013-01-01          2 NaN     0      0      0              1   \n",
       "\n",
       "  StateHoliday  Store       ...             HHFT  HHSAVFORECAST     HHSAV  \\\n",
       "0            a      1       ...         8.310776        9.13566  9.260378   \n",
       "\n",
       "   Goog_trend  Goog_trend_ALL  PromoSecondDate  DayBeforeRefurb  \\\n",
       "0          61              61                0                0   \n",
       "\n",
       "   DayAfterRefurb  DaysBeforeRefurb  DaysAfterRefurb  \n",
       "0               0                 0                0  \n",
       "\n",
       "[1 rows x 176 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving our files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Files are saved and ready\n"
     ]
    }
   ],
   "source": [
    "# Create a folder 'data' right outside the folder 'KaggleProject'\n",
    "all_data[all_data['type']=='train'].to_csv(\"../data/train3.csv\", index=False)\n",
    "all_data[all_data['type']=='test'].to_csv(\"../data/test3.csv\", index=False)\n",
    "print 'Files are saved and ready'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}