RossmannDataCleaningMungingV3.1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Libraries and Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'/Users/jfdarre/Documents/NYCDS/Project4/KaggleProject'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "import numpy as np\n",
    "from numpy import double\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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 = pd.read_csv(\"../input/train.csv\")\n",
    "test = pd.read_csv(\"../input/test.csv\")\n",
    "store = pd.read_csv(\"../input/store.csv\")\n",
    "states = pd.read_csv(\"../input/store_states.csv\")\n",
    "print(\"Complete!   : )\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Process Data:\n",
    "#### 1: Impute open = 1 for missing open test dasta\n",
    "#### 2: Separate date column into year, month, day\n",
    "#### 3: Convert date column to type 'date' and extract:\n",
    "* day_of_year\n",
    "* week_of_year\n",
    "* quarter\n",
    "* month start\n",
    "* month end\n",
    "* quarter start\n",
    "* quarter end\n",
    "\n",
    "#### 4: Drop date and standardize StateHoliday\n",
    "#### 5: Create promotion one feature\n",
    "#### 6: Merge data\n",
    "#### 7: Dumify categorical variables"
   ]
  },
  {
   "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: Separate date columns\n",
    "train['year'] = train.Date.apply(lambda x: x.split('-')[0])\n",
    "train['year'] = train['year'].astype(int)\n",
    "train['month'] = train.Date.apply(lambda x: x.split('-')[1])\n",
    "train['month'] = train['month'].astype(int)\n",
    "train['day'] = train.Date.apply(lambda x: x.split('-')[2])\n",
    "train['day'] = train['day'].astype(int)\n",
    "print 'Step 2 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 2 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 2: Separate date columns\n",
    "test['year'] = test.Date.apply(lambda x: x.split('-')[0])\n",
    "test['year'] = test['year'].astype(int)\n",
    "test['month'] = test.Date.apply(lambda x: x.split('-')[1])\n",
    "test['month'] = test['month'].astype(int)\n",
    "test['day'] = test.Date.apply(lambda x: x.split('-')[2])\n",
    "test['day'] = test['day'].astype(int)\n",
    "print 'Step 2 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 3 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 3: Withdraw multiple date features\n",
    "train['Date'] = pd.to_datetime(train['Date'])\n",
    "train['day_of_year'] = train['Date'].dt.dayofyear\n",
    "train['quarter'] = train['Date'].dt.quarter\n",
    "train['is_month_start'] = train['Date'].dt.is_month_start.astype(int)\n",
    "train['is_month_end'] = train['Date'].dt.is_month_end.astype(int)\n",
    "train['is_quarter_start'] = train['Date'].dt.is_quarter_start.astype(int)\n",
    "train['is_quarter_end'] = train['Date'].dt.is_quarter_end.astype(int)\n",
    "print 'Step 3 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "lambdafunc = lambda x: pd.Series([x['Date'].isocalendar()[1]])\n",
    "train[['weeknum']] = train.apply(lambdafunc, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Store               False\n",
       "DayOfWeek           False\n",
       "Date                False\n",
       "Sales               False\n",
       "Customers           False\n",
       "Open                False\n",
       "Promo               False\n",
       "StateHoliday        False\n",
       "SchoolHoliday       False\n",
       "year                False\n",
       "month               False\n",
       "day                 False\n",
       "day_of_year         False\n",
       "quarter             False\n",
       "is_month_start      False\n",
       "is_month_end        False\n",
       "is_quarter_start    False\n",
       "is_quarter_end      False\n",
       "weeknum             False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.isnull(train).any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>Store</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Customers</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>day_of_year</th>\n",
       "      <th>quarter</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Store, DayOfWeek, Date, Sales, Customers, Open, Promo, StateHoliday, SchoolHoliday, year, month, day, day_of_year, quarter, is_month_start, is_month_end, is_quarter_start, is_quarter_end, weeknum]\n",
       "Index: []"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train[train.isnull().any(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 3 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 3: Withdraw multiple date features\n",
    "test['Date'] = pd.to_datetime(test['Date'])\n",
    "test['day_of_year'] = test['Date'].dt.dayofyear\n",
    "test['quarter'] = test['Date'].dt.quarter\n",
    "test['is_month_start'] = test['Date'].dt.is_month_start.astype(int)\n",
    "test['is_month_end'] = test['Date'].dt.is_month_end.astype(int)\n",
    "test['is_quarter_start'] = test['Date'].dt.is_quarter_start.astype(int)\n",
    "test['is_quarter_end'] = test['Date'].dt.is_quarter_end.astype(int)\n",
    "print 'Step 3 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# datetime.date(2015, 7, 31).isocalendar()[1]\n",
    "lambdafunc = lambda x: pd.Series([x['Date'].isocalendar()[1]])\n",
    "test[['weeknum']] = test.apply(lambdafunc, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Id                  False\n",
       "Store               False\n",
       "DayOfWeek           False\n",
       "Date                False\n",
       "Open                False\n",
       "Promo               False\n",
       "StateHoliday        False\n",
       "SchoolHoliday       False\n",
       "year                False\n",
       "month               False\n",
       "day                 False\n",
       "day_of_year         False\n",
       "quarter             False\n",
       "is_month_start      False\n",
       "is_month_end        False\n",
       "is_quarter_start    False\n",
       "is_quarter_end      False\n",
       "weeknum             False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.isnull(test).any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Id</th>\n",
       "      <th>Store</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>day_of_year</th>\n",
       "      <th>quarter</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>260</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>260</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>260</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>260</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>260</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id  Store  DayOfWeek       Date  Open  Promo StateHoliday  SchoolHoliday  \\\n",
       "0   1      1          4 2015-09-17     1      1            0              0   \n",
       "1   2      3          4 2015-09-17     1      1            0              0   \n",
       "2   3      7          4 2015-09-17     1      1            0              0   \n",
       "3   4      8          4 2015-09-17     1      1            0              0   \n",
       "4   5      9          4 2015-09-17     1      1            0              0   \n",
       "\n",
       "   year  month  day  day_of_year  quarter  is_month_start  is_month_end  \\\n",
       "0  2015      9   17          260        3               0             0   \n",
       "1  2015      9   17          260        3               0             0   \n",
       "2  2015      9   17          260        3               0             0   \n",
       "3  2015      9   17          260        3               0             0   \n",
       "4  2015      9   17          260        3               0             0   \n",
       "\n",
       "   is_quarter_start  is_quarter_end  weeknum  \n",
       "0                 0               0       38  \n",
       "1                 0               0       38  \n",
       "2                 0               0       38  \n",
       "3                 0               0       38  \n",
       "4                 0               0       38  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 4 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 4: Standardize StateHoliday column.\n",
    "train.ix[(train['StateHoliday'] == 0), 'StateHoliday'] = '0'\n",
    "print 'Step 4 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 4 Complete.\n"
     ]
    }
   ],
   "source": [
    "# 4: Standardize StateHoliday column.\n",
    "test.ix[(test['StateHoliday'] == 0), 'StateHoliday'] = '0'\n",
    "print 'Step 4 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 5 Complete.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/2015/anaconda/lib/python2.7/site-packages/ipykernel/__main__.py:2: FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)\n",
      "  from ipykernel import kernelapp as app\n"
     ]
    }
   ],
   "source": [
    "# 5: Create duration of promo 1\n",
    "train = train.sort(columns = ['Store', 'Date'], ascending = True, na_position = 'last')\n",
    "train['PromoFirstDate'] = 0\n",
    "train.ix[(train['Promo'] == 1 ) & (train['Promo'].shift(1) ==0), 'PromoFirstDate'] = 1\n",
    "print 'Step 5 Complete.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Step 5 Complete.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/2015/anaconda/lib/python2.7/site-packages/ipykernel/__main__.py:2: FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)\n",
      "  from ipykernel import kernelapp as app\n"
     ]
    }
   ],
   "source": [
    "# 5: Create duration of promo 1\n",
    "test = test.sort(columns = ['Store', 'Date'], ascending = True, na_position = 'last')\n",
    "test['PromoFirstDate'] = 0\n",
    "test.ix[(test['Promo'] == 1 ) & (test['Promo'].shift(1) ==0), 'PromoFirstDate'] = 1\n",
    "print 'Step 5 Complete.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Join with States Data Frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Store</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>TH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>NW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>BE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>SN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store State\n",
       "0      1    HE\n",
       "1      2    TH\n",
       "2      3    NW\n",
       "3      4    BE\n",
       "4      5    SN"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1017209, 20)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train_states = pd.merge(train, states, on = 'Store', how = 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1017209, 21)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_states.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Store</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Customers</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>year</th>\n",
       "      <th>...</th>\n",
       "      <th>day</th>\n",
       "      <th>day_of_year</th>\n",
       "      <th>quarter</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>2013</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>5530</td>\n",
       "      <td>668</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2013</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>4327</td>\n",
       "      <td>578</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2013</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>4486</td>\n",
       "      <td>619</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2013</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>4997</td>\n",
       "      <td>635</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2013</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store  DayOfWeek       Date  Sales  Customers  Open  Promo StateHoliday  \\\n",
       "0      1          2 2013-01-01      0          0     0      0            a   \n",
       "1      1          3 2013-01-02   5530        668     1      0            0   \n",
       "2      1          4 2013-01-03   4327        578     1      0            0   \n",
       "3      1          5 2013-01-04   4486        619     1      0            0   \n",
       "4      1          6 2013-01-05   4997        635     1      0            0   \n",
       "\n",
       "   SchoolHoliday  year  ...    day  day_of_year  quarter  is_month_start  \\\n",
       "0              1  2013  ...      1            1        1               1   \n",
       "1              1  2013  ...      2            2        1               0   \n",
       "2              1  2013  ...      3            3        1               0   \n",
       "3              1  2013  ...      4            4        1               0   \n",
       "4              1  2013  ...      5            5        1               0   \n",
       "\n",
       "   is_month_end  is_quarter_start  is_quarter_end  weeknum  PromoFirstDate  \\\n",
       "0             0                 1               0        1               0   \n",
       "1             0                 0               0        1               0   \n",
       "2             0                 0               0        1               0   \n",
       "3             0                 0               0        1               0   \n",
       "4             0                 0               0        1               0   \n",
       "\n",
       "   State  \n",
       "0     HE  \n",
       "1     HE  \n",
       "2     HE  \n",
       "3     HE  \n",
       "4     HE  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_states.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Store               False\n",
       "DayOfWeek           False\n",
       "Date                False\n",
       "Sales               False\n",
       "Customers           False\n",
       "Open                False\n",
       "Promo               False\n",
       "StateHoliday        False\n",
       "SchoolHoliday       False\n",
       "year                False\n",
       "month               False\n",
       "day                 False\n",
       "day_of_year         False\n",
       "quarter             False\n",
       "is_month_start      False\n",
       "is_month_end        False\n",
       "is_quarter_start    False\n",
       "is_quarter_end      False\n",
       "weeknum             False\n",
       "PromoFirstDate      False\n",
       "State               False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.isnull(train_states).any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "((1017209, 21), Store               [[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 1...\n",
      "DayOfWeek                                     [[2, 3, 4, 5, 6, 7, 1]]\n",
      "Date                [[2013-01-01 00:00:00, 2013-01-02 00:00:00, 20...\n",
      "Sales               [[0, 5530, 4327, 4486, 4997, 7176, 5580, 5471,...\n",
      "Customers           [[0, 668, 578, 619, 635, 785, 654, 626, 615, 5...\n",
      "Open                                                         [[0, 1]]\n",
      "Promo                                                        [[0, 1]]\n",
      "StateHoliday                                           [[a, 0, b, c]]\n",
      "SchoolHoliday                                                [[1, 0]]\n",
      "year                                             [[2013, 2014, 2015]]\n",
      "month                       [[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]]\n",
      "day                 [[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 1...\n",
      "day_of_year         [[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 1...\n",
      "quarter                                                [[1, 2, 3, 4]]\n",
      "is_month_start                                               [[1, 0]]\n",
      "is_month_end                                                 [[0, 1]]\n",
      "is_quarter_start                                             [[1, 0]]\n",
      "is_quarter_end                                               [[0, 1]]\n",
      "weeknum             [[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 1...\n",
      "PromoFirstDate                                               [[0, 1]]\n",
      "State               [[HE, TH, NW, BE, SN, SH, HB,NI, BY, BW, RP, S...\n",
      "dtype: object)\n"
     ]
    }
   ],
   "source": [
    "def rstr(df): return df.shape, df.apply(lambda x: [x.unique()])\n",
    "print(rstr(train_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "((41088, 19), Id                  [[40233, 39377, 38521, 37665, 36809, 35953, 35...\n",
      "Store               [[1, 3, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 1...\n",
      "DayOfWeek                                     [[6, 7, 1, 2, 3, 4, 5]]\n",
      "Date                [[2015-08-01 00:00:00, 2015-08-02 00:00:00, 20...\n",
      "Open                                                     [[1.0, 0.0]]\n",
      "Promo                                                        [[0, 1]]\n",
      "StateHoliday                                                 [[0, a]]\n",
      "SchoolHoliday                                                [[1, 0]]\n",
      "year                                                         [[2015]]\n",
      "month                                                        [[8, 9]]\n",
      "day                 [[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 1...\n",
      "day_of_year         [[213, 214, 215, 216, 217, 218, 219, 220, 221,...\n",
      "quarter                                                         [[3]]\n",
      "is_month_start                                               [[1, 0]]\n",
      "is_month_end                                                 [[0, 1]]\n",
      "is_quarter_start                                                [[0]]\n",
      "is_quarter_end                                                  [[0]]\n",
      "weeknum                            [[31, 32, 33, 34, 35, 36, 37, 38]]\n",
      "PromoFirstDate                                               [[0, 1]]\n",
      "dtype: object)\n"
     ]
    }
   ],
   "source": [
    "print(rstr(test))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41088, 19)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "((41088, 20), Id                  [[40233, 39377, 38521, 37665, 36809, 35953, 35...\n",
      "Store               [[1, 3, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 1...\n",
      "DayOfWeek                                     [[6, 7, 1, 2, 3, 4, 5]]\n",
      "Date                [[2015-08-01 00:00:00, 2015-08-02 00:00:00, 20...\n",
      "Open                                                     [[1.0, 0.0]]\n",
      "Promo                                                        [[0, 1]]\n",
      "StateHoliday                                                 [[0, a]]\n",
      "SchoolHoliday                                                [[1, 0]]\n",
      "year                                                         [[2015]]\n",
      "month                                                        [[8, 9]]\n",
      "day                 [[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 1...\n",
      "day_of_year         [[213, 214, 215, 216, 217, 218, 219, 220, 221,...\n",
      "quarter                                                         [[3]]\n",
      "is_month_start                                               [[1, 0]]\n",
      "is_month_end                                                 [[0, 1]]\n",
      "is_quarter_start                                                [[0]]\n",
      "is_quarter_end                                                  [[0]]\n",
      "weeknum                            [[31, 32, 33, 34, 35, 36, 37, 38]]\n",
      "PromoFirstDate                                               [[0, 1]]\n",
      "State                           [[HE, NW, SH, HB,NI, BY, BW, RP, HH]]\n",
      "dtype: object)\n"
     ]
    }
   ],
   "source": [
    "test_states = pd.merge(test, states, on = 'Store', how = 'inner')\n",
    "print(rstr(test_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Id                  False\n",
       "Store               False\n",
       "DayOfWeek           False\n",
       "Date                False\n",
       "Open                False\n",
       "Promo               False\n",
       "StateHoliday        False\n",
       "SchoolHoliday       False\n",
       "year                False\n",
       "month               False\n",
       "day                 False\n",
       "day_of_year         False\n",
       "quarter             False\n",
       "is_month_start      False\n",
       "is_month_end        False\n",
       "is_quarter_start    False\n",
       "is_quarter_end      False\n",
       "weeknum             False\n",
       "PromoFirstDate      False\n",
       "State               False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.isnull(test_states).any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>Id</th>\n",
       "      <th>Store</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>day_of_year</th>\n",
       "      <th>quarter</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>40233</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>2015-08-01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>213</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39377</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2015-08-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>214</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38521</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-08-03</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>215</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>37665</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2015-08-04</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>216</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36809</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2015-08-05</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>217</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Id  Store  DayOfWeek       Date  Open  Promo StateHoliday  \\\n",
       "0  40233      1          6 2015-08-01     1      0            0   \n",
       "1  39377      1          7 2015-08-02     0      0            0   \n",
       "2  38521      1          1 2015-08-03     1      1            0   \n",
       "3  37665      1          2 2015-08-04     1      1            0   \n",
       "4  36809      1          3 2015-08-05     1      1            0   \n",
       "\n",
       "   SchoolHoliday  year  month  day  day_of_year  quarter  is_month_start  \\\n",
       "0              1  2015      8    1          213        3               1   \n",
       "1              1  2015      8    2          214        3               0   \n",
       "2              1  2015      8    3          215        3               0   \n",
       "3              1  2015      8    4          216        3               0   \n",
       "4              1  2015      8    5          217        3               0   \n",
       "\n",
       "   is_month_end  is_quarter_start  is_quarter_end  weeknum  PromoFirstDate  \\\n",
       "0             0                 0               0       31               0   \n",
       "1             0                 0               0       31               0   \n",
       "2             0                 0               0       32               1   \n",
       "3             0                 0               0       32               0   \n",
       "4             0                 0               0       32               0   \n",
       "\n",
       "  State  \n",
       "0    HE  \n",
       "1    HE  \n",
       "2    HE  \n",
       "3    HE  \n",
       "4    HE  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_states.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Output CSV files for R"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'/Users/2015/Dropbox/Data Science/NYC Data Science Academy/class-projects/Rossmann/Scripts/Python'"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "train_states.to_csv(\"../../KaggleProject/data/train_states_R.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "test_states.to_csv(\"../../KaggleProject/data/test_states_R.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Add mean sales features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "train_store.head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "train.head(50)"
   ]
  },
  {
   "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
}