JF_munging_v1.1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data munging starts: \n",
    "### Importing packages and data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading data start...\n",
      "Complete!\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": 66,
   "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": 67,
   "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": 68,
   "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": 69,
   "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": 70,
   "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": 71,
   "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": 72,
   "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": 73,
   "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": 74,
   "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": 75,
   "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": 76,
   "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": 77,
   "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": 78,
   "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": 79,
   "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": 80,
   "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": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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,22):\n",
    "    shift_col(all_data, 'Open', i)\n",
    "print 'Step 10 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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,22):\n",
    "    shift_col(all_data, 'Open', -i)\n",
    "print 'Step 11 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "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-12</th>\n",
       "      <th>Open-13</th>\n",
       "      <th>Open-14</th>\n",
       "      <th>Open-15</th>\n",
       "      <th>Open-16</th>\n",
       "      <th>Open-17</th>\n",
       "      <th>Open-18</th>\n",
       "      <th>Open-19</th>\n",
       "      <th>Open-20</th>\n",
       "      <th>Open-21</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 × 68 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-12  Open-13  Open-14  Open-15  Open-16  \\\n",
       "0            a      1   ...          0        1        1        1        1   \n",
       "\n",
       "   Open-17  Open-18  Open-19  Open-20  Open-21  \n",
       "0        1        1        0        1        1  \n",
       "\n",
       "[1 rows x 68 columns]"
      ]
     },
     "execution_count": 83,
     "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": 84,
   "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": 85,
   "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": 86,
   "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": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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": 88,
   "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 × 90 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 90 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "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": 90,
   "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,15):\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": 91,
   "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,15):\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": 92,
   "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": 93,
   "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 × 158 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 158 columns]"
      ]
     },
     "execution_count": 93,
     "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": 94,
   "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": 95,
   "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": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eco_data.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "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": 97,
   "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 × 197 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 197 columns]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving our files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "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/train2.csv\", index=False)\n",
    "all_data[all_data['type']=='test'].to_csv(\"../data/test2.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
}