04 - Kaggle - bike share system - Adding customer average to the features.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 04 - Kaggle - bike share system - Adding customer average to the features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "For problem formulation refer to **\"01 - Kaggle - bike share system - problem formulation.ipynb\"**.\n",
    "In section **\"02 - Kaggle - bike share system - Data preprocessing.ipynb\"** we transformed the raw data and extracted time, date, and dummy matrices. The results are stored in two formats:\n",
    " * In `train_prep_orig.csv` and `test_prep_orig.csv` the categorical data are in the original form.\n",
    " * In `train_prep_dum.csv` and `test_prep_dum.csv` the categorical data are converted to dummy matrices. \n",
    "\n",
    "In section **03 - Kaggle - bike share system - data visualization.ipynb** we ploted the average of customers at different time periods over 2011 and 2012 and discused the pattern of customer behavior. We concluded by a decision to consider these average values as new features of the problem so that the machine learning model will be able to use them as the basis values and the other features apply the necessary correction to make use closer to the actual values. That is the job of this section.\n",
    "\n",
    "We split the hours time into 6 chunks of 4 hours periods. Here was the observation of customer behavior:\n",
    "* 1: **[2:00 am, 3:00 am, 4:00 am, 5:00 am]**  ------> both casual and registered (and therefore total) customers are highest during the weekend.\n",
    "* 2: ** [6:00 am, 7:00 am, 8:00 am, 9:00 am]**  ------> Casual customers behave for all day alsmot within the same range below the week average. Registered customers use the system on average more during the workdays above the the week average and the usage during the weekends in below the week average.\n",
    "* 3: ** [10:00 am, 11:00 am, 12:00 pm, 1:00 pm]**  --> both casual and registered (and therefore total) customers are highest during the weekend.\n",
    "* 4: ** [2:00 pm, 3:00 pm, 4:00 pm, 5:00 pm] **  ------> All are above the week average. The casual and total is highest during the weekend. For registered, all days are of the same order. \n",
    "* 5: ** [6:00 pm, 7:00 pm, 8:00 pm, 9:00 pm] **  ------> no comment.\n",
    "* 6: ** [10:00 pm, 11:00 pm, 12:00 am, 1:00 pm] ** --> All are below the week average and the average number of customers is highest during the weekend.\n",
    "\n",
    "Since we do not have the number of customers from day 20 to the end of each month, we have about three isntances of a weekday for each month and year. We take the average number of customers during these days and use it as a basis. We call the new features **avg_casual**, **avg_registered** and **avg_tot**. We finally, update the data sets as:\n",
    " * In `train_prep_orig_avg.csv` and `test_prep_orig_avg.csv` the categorical data are in the original form.\n",
    " * In `train_prep_dum_avg.csv` and `test_prep_dum_avg.csv` the categorical data are converted to dummy matrices. \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Basic settings and importing the libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Resets the namespace by removing all names defined by the user without asking for confirmation\n",
    "%reset -f\n",
    "\n",
    "\n",
    "# Panas is used for DataFrame\n",
    "import pandas as pd\n",
    "\n",
    "# NumPy is used for manipulating arrays\n",
    "import numpy as np\n",
    "\n",
    "# MatPlotLib is used for plotting\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.patches as mpatches\n",
    "\n",
    "# the output of plotting commands is displayed inline directly below the code cell that produced it.\n",
    "%matplotlib inline\n",
    "\n",
    "# Seaborn is used for statistical plotting\n",
    "import seaborn as sns\n",
    "\n",
    "# Used for display dataframes as html tables\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing the train data from `train_prep_orig.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The shape of the train dataset: (10886, 15)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0  9.84  14.395        81          0  2011       1      1        5     0   \n",
       "1  9.02  13.635        80          0  2011       1      1        5     1   \n",
       "2  9.02  13.635        80          0  2011       1      1        5     2   \n",
       "3  9.84  14.395        75          0  2011       1      1        5     3   \n",
       "4  9.84  14.395        75          0  2011       1      1        5     4   \n",
       "\n",
       "   workingday  holiday  weather  casual  registered  tot  \n",
       "0           0        0        1       3          13   16  \n",
       "1           0        0        1       8          32   40  \n",
       "2           0        0        1       5          27   32  \n",
       "3           0        0        1       3          10   13  \n",
       "4           0        0        1       0           1    1  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Load train data\n",
    "data_train = pd.read_csv('data/train_prep_orig.csv')\n",
    "\n",
    "print \"The shape of the train dataset:\", data_train.shape\n",
    "display(data_train.head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## features and dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cat_var = ['year','season', 'month', 'weekday', 'hour', 'workingday', 'holiday', 'weather']\n",
    "num_var = ['temp', 'atemp', 'humidity', 'windspeed']\n",
    "target_var = ['casual', 'registered', 'tot']\n",
    "\n",
    "weekday_dic = {0:'Monday',\n",
    "              1:'Tuesday',\n",
    "              2:'Wednesday',\n",
    "              3:'Thursday',\n",
    "              4:'Friday',\n",
    "              5:'Saturday',\n",
    "              6:'Sunday'}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping by ['`year`','`month`','`weekday`','`period`']\n",
    "\n",
    "Now, we would like to learn how does it changes acros different months, weekdays and day periods. We only work with `mean` here ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "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>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "      <th>periods</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>81</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9.84</td>\n",
       "      <td>12.880</td>\n",
       "      <td>75</td>\n",
       "      <td>6.0032</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.20</td>\n",
       "      <td>12.880</td>\n",
       "      <td>86</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>13.12</td>\n",
       "      <td>17.425</td>\n",
       "      <td>76</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>15.58</td>\n",
       "      <td>19.695</td>\n",
       "      <td>76</td>\n",
       "      <td>16.9979</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>24</td>\n",
       "      <td>36</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>14.76</td>\n",
       "      <td>16.665</td>\n",
       "      <td>81</td>\n",
       "      <td>19.0012</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>26</td>\n",
       "      <td>30</td>\n",
       "      <td>56</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>17.22</td>\n",
       "      <td>21.210</td>\n",
       "      <td>77</td>\n",
       "      <td>19.0012</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>55</td>\n",
       "      <td>84</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>18.86</td>\n",
       "      <td>22.725</td>\n",
       "      <td>72</td>\n",
       "      <td>19.9995</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>47</td>\n",
       "      <td>47</td>\n",
       "      <td>94</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>18.86</td>\n",
       "      <td>22.725</td>\n",
       "      <td>72</td>\n",
       "      <td>19.0012</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>35</td>\n",
       "      <td>71</td>\n",
       "      <td>106</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>18.04</td>\n",
       "      <td>21.970</td>\n",
       "      <td>77</td>\n",
       "      <td>19.9995</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "      <td>70</td>\n",
       "      <td>110</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17.22</td>\n",
       "      <td>21.210</td>\n",
       "      <td>82</td>\n",
       "      <td>19.9995</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>41</td>\n",
       "      <td>52</td>\n",
       "      <td>93</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18.04</td>\n",
       "      <td>21.970</td>\n",
       "      <td>82</td>\n",
       "      <td>19.0012</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>52</td>\n",
       "      <td>67</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>17.22</td>\n",
       "      <td>21.210</td>\n",
       "      <td>88</td>\n",
       "      <td>16.9979</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>26</td>\n",
       "      <td>35</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>17.22</td>\n",
       "      <td>21.210</td>\n",
       "      <td>88</td>\n",
       "      <td>16.9979</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>31</td>\n",
       "      <td>37</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0    9.84  14.395        81     0.0000  2011       1      1        5     0   \n",
       "1    9.02  13.635        80     0.0000  2011       1      1        5     1   \n",
       "2    9.02  13.635        80     0.0000  2011       1      1        5     2   \n",
       "3    9.84  14.395        75     0.0000  2011       1      1        5     3   \n",
       "4    9.84  14.395        75     0.0000  2011       1      1        5     4   \n",
       "5    9.84  12.880        75     6.0032  2011       1      1        5     5   \n",
       "6    9.02  13.635        80     0.0000  2011       1      1        5     6   \n",
       "7    8.20  12.880        86     0.0000  2011       1      1        5     7   \n",
       "8    9.84  14.395        75     0.0000  2011       1      1        5     8   \n",
       "9   13.12  17.425        76     0.0000  2011       1      1        5     9   \n",
       "10  15.58  19.695        76    16.9979  2011       1      1        5    10   \n",
       "11  14.76  16.665        81    19.0012  2011       1      1        5    11   \n",
       "12  17.22  21.210        77    19.0012  2011       1      1        5    12   \n",
       "13  18.86  22.725        72    19.9995  2011       1      1        5    13   \n",
       "14  18.86  22.725        72    19.0012  2011       1      1        5    14   \n",
       "15  18.04  21.970        77    19.9995  2011       1      1        5    15   \n",
       "16  17.22  21.210        82    19.9995  2011       1      1        5    16   \n",
       "17  18.04  21.970        82    19.0012  2011       1      1        5    17   \n",
       "18  17.22  21.210        88    16.9979  2011       1      1        5    18   \n",
       "19  17.22  21.210        88    16.9979  2011       1      1        5    19   \n",
       "\n",
       "    workingday  holiday  weather  casual  registered  tot  periods  \n",
       "0            0        0        1       3          13   16        6  \n",
       "1            0        0        1       8          32   40        6  \n",
       "2            0        0        1       5          27   32        1  \n",
       "3            0        0        1       3          10   13        1  \n",
       "4            0        0        1       0           1    1        1  \n",
       "5            0        0        2       0           1    1        1  \n",
       "6            0        0        1       2           0    2        2  \n",
       "7            0        0        1       1           2    3        2  \n",
       "8            0        0        1       1           7    8        2  \n",
       "9            0        0        1       8           6   14        2  \n",
       "10           0        0        1      12          24   36        3  \n",
       "11           0        0        1      26          30   56        3  \n",
       "12           0        0        1      29          55   84        3  \n",
       "13           0        0        2      47          47   94        3  \n",
       "14           0        0        2      35          71  106        4  \n",
       "15           0        0        2      40          70  110        4  \n",
       "16           0        0        2      41          52   93        4  \n",
       "17           0        0        2      15          52   67        4  \n",
       "18           0        0        3       9          26   35        5  \n",
       "19           0        0        3       6          31   37        5  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>periods</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"40\" valign=\"top\">2011</th>\n",
       "      <th rowspan=\"40\" valign=\"top\">1</th>\n",
       "      <th rowspan=\"6\" valign=\"top\">0</th>\n",
       "      <th>1</th>\n",
       "      <td>0.200000</td>\n",
       "      <td>2.600000</td>\n",
       "      <td>2.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.666667</td>\n",
       "      <td>66.000000</td>\n",
       "      <td>68.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.666667</td>\n",
       "      <td>48.833333</td>\n",
       "      <td>56.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8.166667</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>88.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2.722222</td>\n",
       "      <td>54.833333</td>\n",
       "      <td>57.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.833333</td>\n",
       "      <td>6.833333</td>\n",
       "      <td>7.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">1</th>\n",
       "      <th>1</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.200000</td>\n",
       "      <td>3.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.750000</td>\n",
       "      <td>108.500000</td>\n",
       "      <td>110.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5.700000</td>\n",
       "      <td>44.000000</td>\n",
       "      <td>49.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>76.833333</td>\n",
       "      <td>82.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.833333</td>\n",
       "      <td>60.111111</td>\n",
       "      <td>60.944444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.500000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>5.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">2</th>\n",
       "      <th>1</th>\n",
       "      <td>0.125000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.666667</td>\n",
       "      <td>93.166667</td>\n",
       "      <td>94.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.583333</td>\n",
       "      <td>51.916667</td>\n",
       "      <td>56.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.916667</td>\n",
       "      <td>91.833333</td>\n",
       "      <td>96.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2.722222</td>\n",
       "      <td>73.666667</td>\n",
       "      <td>76.388889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.166667</td>\n",
       "      <td>5.666667</td>\n",
       "      <td>5.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">3</th>\n",
       "      <th>1</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>112.125000</td>\n",
       "      <td>114.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.250000</td>\n",
       "      <td>53.875000</td>\n",
       "      <td>58.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.750000</td>\n",
       "      <td>84.750000</td>\n",
       "      <td>91.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.750000</td>\n",
       "      <td>69.833333</td>\n",
       "      <td>71.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.250000</td>\n",
       "      <td>5.750000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">4</th>\n",
       "      <th>1</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.833333</td>\n",
       "      <td>2.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.875000</td>\n",
       "      <td>97.125000</td>\n",
       "      <td>103.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8.625000</td>\n",
       "      <td>53.500000</td>\n",
       "      <td>62.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.375000</td>\n",
       "      <td>90.750000</td>\n",
       "      <td>97.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2.416667</td>\n",
       "      <td>62.000000</td>\n",
       "      <td>64.416667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1.500000</td>\n",
       "      <td>9.250000</td>\n",
       "      <td>10.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">5</th>\n",
       "      <th>1</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>8.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.500000</td>\n",
       "      <td>10.333333</td>\n",
       "      <td>11.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16.000000</td>\n",
       "      <td>60.166667</td>\n",
       "      <td>76.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22.500000</td>\n",
       "      <td>73.166667</td>\n",
       "      <td>95.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.166667</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>41.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>21.166667</td>\n",
       "      <td>24.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">6</th>\n",
       "      <th>1</th>\n",
       "      <td>1.636364</td>\n",
       "      <td>4.636364</td>\n",
       "      <td>6.272727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.416667</td>\n",
       "      <td>9.666667</td>\n",
       "      <td>10.083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12.416667</td>\n",
       "      <td>66.000000</td>\n",
       "      <td>78.416667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14.166667</td>\n",
       "      <td>68.333333</td>\n",
       "      <td>82.500000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               casual  registered         tot\n",
       "year month weekday periods                                   \n",
       "2011 1     0       1         0.200000    2.600000    2.800000\n",
       "                   2         2.666667   66.000000   68.666667\n",
       "                   3         7.666667   48.833333   56.500000\n",
       "                   4         8.166667   80.000000   88.166667\n",
       "                   5         2.722222   54.833333   57.555556\n",
       "                   6         0.833333    6.833333    7.666667\n",
       "           1       1         0.000000    3.200000    3.200000\n",
       "                   2         1.750000  108.500000  110.250000\n",
       "                   3         5.700000   44.000000   49.700000\n",
       "                   4         6.000000   76.833333   82.833333\n",
       "                   5         0.833333   60.111111   60.944444\n",
       "                   6         0.500000    5.000000    5.500000\n",
       "           2       1         0.125000    3.000000    3.125000\n",
       "                   2         1.666667   93.166667   94.833333\n",
       "                   3         4.583333   51.916667   56.500000\n",
       "                   4         4.916667   91.833333   96.750000\n",
       "                   5         2.722222   73.666667   76.388889\n",
       "                   6         0.166667    5.666667    5.833333\n",
       "           3       1         0.000000    2.714286    2.714286\n",
       "                   2         2.000000  112.125000  114.125000\n",
       "                   3         4.250000   53.875000   58.125000\n",
       "                   4         6.750000   84.750000   91.500000\n",
       "                   5         1.750000   69.833333   71.583333\n",
       "                   6         0.250000    5.750000    6.000000\n",
       "           4       1         0.000000    2.833333    2.833333\n",
       "                   2         5.875000   97.125000  103.000000\n",
       "                   3         8.625000   53.500000   62.125000\n",
       "                   4         6.375000   90.750000   97.125000\n",
       "                   5         2.416667   62.000000   64.416667\n",
       "                   6         1.500000    9.250000   10.750000\n",
       "           5       1         1.000000    7.500000    8.500000\n",
       "                   2         1.500000   10.333333   11.833333\n",
       "                   3        16.000000   60.166667   76.166667\n",
       "                   4        22.500000   73.166667   95.666667\n",
       "                   5         6.166667   35.000000   41.166667\n",
       "                   6         3.000000   21.166667   24.166667\n",
       "           6       1         1.636364    4.636364    6.272727\n",
       "                   2         0.416667    9.666667   10.083333\n",
       "                   3        12.416667   66.000000   78.416667\n",
       "                   4        14.166667   68.333333   82.500000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "hours = np.array(data_train.hour)\n",
    "\n",
    "data_train['periods']=np.where( (2 <= hours) &  (hours <= 5), 1,\n",
    "                        np.where( (6 <= hours) &  (hours <= 9), 2,\n",
    "                                 np.where( (10 <= hours) &  (hours <= 13), 3,\n",
    "                                          np.where( (14 <= hours) &  (hours <= 17), 4,\n",
    "                                                  np.where( (18 <= hours) &  (hours <= 23), 5, 6)\n",
    "                                                  )\n",
    "                                         )\n",
    "                                )\n",
    "                        )\n",
    "display(data_train.head(20))\n",
    "\n",
    "groupby_year_month_weekday_periods = data_train.groupby(['year','month','weekday','periods']).mean()[target_var]\n",
    "display(groupby_year_month_weekday_periods.head(40))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10886\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "      <th>periods</th>\n",
       "      <th>avg_casual</th>\n",
       "      <th>avg_registered</th>\n",
       "      <th>avg_tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0  9.84  14.395        81          0  2011       1      1        5     0   \n",
       "1  9.02  13.635        80          0  2011       1      1        5     1   \n",
       "2  9.02  13.635        80          0  2011       1      1        5     2   \n",
       "3  9.84  14.395        75          0  2011       1      1        5     3   \n",
       "4  9.84  14.395        75          0  2011       1      1        5     4   \n",
       "\n",
       "   workingday  holiday  weather  casual  registered  tot  periods  avg_casual  \\\n",
       "0           0        0        1       3          13   16        6           0   \n",
       "1           0        0        1       8          32   40        6           0   \n",
       "2           0        0        1       5          27   32        1           0   \n",
       "3           0        0        1       3          10   13        1           0   \n",
       "4           0        0        1       0           1    1        1           0   \n",
       "\n",
       "   avg_registered  avg_tot  \n",
       "0               0        0  \n",
       "1               0        0  \n",
       "2               0        0  \n",
       "3               0        0  \n",
       "4               0        0  "
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train['avg_casual'] = data_train['avg_registered'] = data_train['avg_tot'] = np.zeros(data_train.shape[0])\n",
    "print data_train.shape[0]\n",
    "data_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1000\n",
      "2000\n",
      "3000\n",
      "4000\n",
      "5000\n",
      "6000\n",
      "7000\n",
      "8000\n",
      "9000\n",
      "10000\n"
     ]
    }
   ],
   "source": [
    "for i in range(data_train.shape[0]):\n",
    "    if i % 1000 == 0: print i\n",
    "    data_train.loc[i,'avg_casual'] = groupby_year_month_weekday_periods.xs(int(data_train.loc[i].year), level ='year'\n",
    "                                     ).xs(int(data_train.loc[i].month), level ='month'\n",
    "                                         ).xs(int(data_train.loc[i].weekday), level ='weekday'\n",
    "                                             ).loc[int(data_train.loc[i].periods)].casual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1000\n",
      "2000\n",
      "3000\n",
      "4000\n",
      "5000\n",
      "6000\n",
      "7000\n",
      "8000\n",
      "9000\n",
      "10000\n"
     ]
    }
   ],
   "source": [
    "for i in range(data_train.shape[0]):\n",
    "    if i % 1000 == 0: print i\n",
    "    data_train.loc[i,'avg_registered'] = groupby_year_month_weekday_periods.xs(int(data_train.loc[i].year), \n",
    "                                                                               level ='year'\n",
    "                                     ).xs(int(data_train.loc[i].month), level ='month'\n",
    "                                         ).xs(int(data_train.loc[i].weekday), level ='weekday'\n",
    "                                             ).loc[int(data_train.loc[i].periods)].registered\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1000\n",
      "2000\n",
      "3000\n",
      "4000\n",
      "5000\n",
      "6000\n",
      "7000\n",
      "8000\n",
      "9000\n",
      "10000\n"
     ]
    }
   ],
   "source": [
    "for i in range(data_train.shape[0]):\n",
    "    if i % 1000 == 0: print i\n",
    "    data_train.loc[i,'avg_tot'] = groupby_year_month_weekday_periods.xs(int(data_train.loc[i].year), level ='year'\n",
    "                                     ).xs(int(data_train.loc[i].month), level ='month'\n",
    "                                         ).xs(int(data_train.loc[i].weekday), level ='weekday'\n",
    "                                             ).loc[int(data_train.loc[i].periods)].tot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "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>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "      <th>periods</th>\n",
       "      <th>avg_casual</th>\n",
       "      <th>avg_registered</th>\n",
       "      <th>avg_tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>38</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>74</td>\n",
       "      <td>86</td>\n",
       "      <td>4</td>\n",
       "      <td>6.75</td>\n",
       "      <td>84.750000</td>\n",
       "      <td>91.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>9.02</td>\n",
       "      <td>11.365</td>\n",
       "      <td>51</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>163</td>\n",
       "      <td>172</td>\n",
       "      <td>4</td>\n",
       "      <td>6.75</td>\n",
       "      <td>84.750000</td>\n",
       "      <td>91.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>9.02</td>\n",
       "      <td>11.365</td>\n",
       "      <td>51</td>\n",
       "      <td>8.9981</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>158</td>\n",
       "      <td>163</td>\n",
       "      <td>5</td>\n",
       "      <td>1.75</td>\n",
       "      <td>69.833333</td>\n",
       "      <td>71.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>9.02</td>\n",
       "      <td>12.880</td>\n",
       "      <td>55</td>\n",
       "      <td>6.0032</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>109</td>\n",
       "      <td>112</td>\n",
       "      <td>5</td>\n",
       "      <td>1.75</td>\n",
       "      <td>69.833333</td>\n",
       "      <td>71.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>8.20</td>\n",
       "      <td>10.605</td>\n",
       "      <td>51</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>66</td>\n",
       "      <td>69</td>\n",
       "      <td>5</td>\n",
       "      <td>1.75</td>\n",
       "      <td>69.833333</td>\n",
       "      <td>71.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>48</td>\n",
       "      <td>48</td>\n",
       "      <td>5</td>\n",
       "      <td>1.75</td>\n",
       "      <td>69.833333</td>\n",
       "      <td>71.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>51</td>\n",
       "      <td>19.0012</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>51</td>\n",
       "      <td>52</td>\n",
       "      <td>5</td>\n",
       "      <td>1.75</td>\n",
       "      <td>69.833333</td>\n",
       "      <td>71.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>8.20</td>\n",
       "      <td>9.850</td>\n",
       "      <td>59</td>\n",
       "      <td>12.9980</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>19</td>\n",
       "      <td>23</td>\n",
       "      <td>5</td>\n",
       "      <td>1.75</td>\n",
       "      <td>69.833333</td>\n",
       "      <td>71.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>8.20</td>\n",
       "      <td>9.850</td>\n",
       "      <td>64</td>\n",
       "      <td>12.9980</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>1.50</td>\n",
       "      <td>9.250000</td>\n",
       "      <td>10.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>8.20</td>\n",
       "      <td>9.850</td>\n",
       "      <td>69</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>1.50</td>\n",
       "      <td>9.250000</td>\n",
       "      <td>10.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>8.20</td>\n",
       "      <td>9.850</td>\n",
       "      <td>69</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.833333</td>\n",
       "      <td>2.833333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "130  10.66  12.880        38    11.0014  2011       1      1        3    16   \n",
       "131   9.02  11.365        51    11.0014  2011       1      1        3    17   \n",
       "132   9.02  11.365        51     8.9981  2011       1      1        3    18   \n",
       "133   9.02  12.880        55     6.0032  2011       1      1        3    19   \n",
       "134   8.20  10.605        51    11.0014  2011       1      1        3    20   \n",
       "135   9.02  10.605        55    15.0013  2011       1      1        3    21   \n",
       "136   9.02  10.605        51    19.0012  2011       1      1        3    22   \n",
       "137   8.20   9.850        59    12.9980  2011       1      1        3    23   \n",
       "138   8.20   9.850        64    12.9980  2011       1      1        4     0   \n",
       "139   8.20   9.850        69    15.0013  2011       1      1        4     1   \n",
       "140   8.20   9.850        69    15.0013  2011       1      1        4     2   \n",
       "\n",
       "     workingday  holiday  weather  casual  registered  tot  periods  \\\n",
       "130           1        0        1      12          74   86        4   \n",
       "131           1        0        1       9         163  172        4   \n",
       "132           1        0        1       5         158  163        5   \n",
       "133           1        0        1       3         109  112        5   \n",
       "134           1        0        1       3          66   69        5   \n",
       "135           1        0        2       0          48   48        5   \n",
       "136           1        0        2       1          51   52        5   \n",
       "137           1        0        2       4          19   23        5   \n",
       "138           1        0        2       4          13   17        6   \n",
       "139           1        0        2       2           5    7        6   \n",
       "140           1        0        2       0           1    1        1   \n",
       "\n",
       "     avg_casual  avg_registered    avg_tot  \n",
       "130        6.75       84.750000  91.500000  \n",
       "131        6.75       84.750000  91.500000  \n",
       "132        1.75       69.833333  71.583333  \n",
       "133        1.75       69.833333  71.583333  \n",
       "134        1.75       69.833333  71.583333  \n",
       "135        1.75       69.833333  71.583333  \n",
       "136        1.75       69.833333  71.583333  \n",
       "137        1.75       69.833333  71.583333  \n",
       "138        1.50        9.250000  10.750000  \n",
       "139        1.50        9.250000  10.750000  \n",
       "140        0.00        2.833333   2.833333  "
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train.loc[130:140]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data_train=data_train.astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "temp              float64\n",
       "atemp             float64\n",
       "humidity          float64\n",
       "windspeed         float64\n",
       "year              float64\n",
       "season            float64\n",
       "month             float64\n",
       "weekday           float64\n",
       "hour              float64\n",
       "workingday        float64\n",
       "holiday           float64\n",
       "weather           float64\n",
       "casual            float64\n",
       "registered        float64\n",
       "tot               float64\n",
       "periods           float64\n",
       "avg_casual        float64\n",
       "avg_registered    float64\n",
       "avg_tot           float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving `train_prep_orig_avg.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "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>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>avg_casual</th>\n",
       "      <th>avg_registered</th>\n",
       "      <th>avg_tot</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>21.166667</td>\n",
       "      <td>24.166667</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>21.166667</td>\n",
       "      <td>24.166667</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>8.500000</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>8.500000</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>8.500000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0  9.84  14.395        81          0  2011       1      1        5     0   \n",
       "1  9.02  13.635        80          0  2011       1      1        5     1   \n",
       "2  9.02  13.635        80          0  2011       1      1        5     2   \n",
       "3  9.84  14.395        75          0  2011       1      1        5     3   \n",
       "4  9.84  14.395        75          0  2011       1      1        5     4   \n",
       "\n",
       "   workingday  holiday  weather  avg_casual  avg_registered    avg_tot  \\\n",
       "0           0        0        1           3       21.166667  24.166667   \n",
       "1           0        0        1           3       21.166667  24.166667   \n",
       "2           0        0        1           1        7.500000   8.500000   \n",
       "3           0        0        1           1        7.500000   8.500000   \n",
       "4           0        0        1           1        7.500000   8.500000   \n",
       "\n",
       "   casual  registered  tot  \n",
       "0       3          13   16  \n",
       "1       8          32   40  \n",
       "2       5          27   32  \n",
       "3       3          10   13  \n",
       "4       0           1    1  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cat_var = ['year','season', 'month', 'weekday', 'hour', 'workingday', 'holiday', 'weather']\n",
    "num_var = ['temp', 'atemp', 'humidity', 'windspeed']\n",
    "avg_var = ['avg_casual','avg_registered','avg_tot']\n",
    "target_var = ['casual', 'registered', 'tot']\n",
    "\n",
    "\n",
    "display(data_train[num_var+cat_var+avg_var+target_var].head())\n",
    "\n",
    "data_train[num_var+cat_var+avg_var+target_var].to_csv('data/train_prep_orig_avg.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving `train_prep_dum_avg.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "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>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>is2011</th>\n",
       "      <th>d_season_1</th>\n",
       "      <th>d_season_2</th>\n",
       "      <th>d_season_3</th>\n",
       "      <th>d_season_4</th>\n",
       "      <th>d_month_1</th>\n",
       "      <th>...</th>\n",
       "      <th>d_workingday_1</th>\n",
       "      <th>d_holiday_0</th>\n",
       "      <th>d_holiday_1</th>\n",
       "      <th>d_weather_1</th>\n",
       "      <th>d_weather_2</th>\n",
       "      <th>d_weather_3</th>\n",
       "      <th>d_weather_4</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 63 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   temp   atemp  humidity  windspeed  is2011  d_season_1  d_season_2  \\\n",
       "0  9.84  14.395        81          0       1           1           0   \n",
       "1  9.02  13.635        80          0       1           1           0   \n",
       "2  9.02  13.635        80          0       1           1           0   \n",
       "3  9.84  14.395        75          0       1           1           0   \n",
       "4  9.84  14.395        75          0       1           1           0   \n",
       "\n",
       "   d_season_3  d_season_4  d_month_1 ...   d_workingday_1  d_holiday_0  \\\n",
       "0           0           0          1 ...                0            1   \n",
       "1           0           0          1 ...                0            1   \n",
       "2           0           0          1 ...                0            1   \n",
       "3           0           0          1 ...                0            1   \n",
       "4           0           0          1 ...                0            1   \n",
       "\n",
       "   d_holiday_1  d_weather_1  d_weather_2  d_weather_3  d_weather_4  casual  \\\n",
       "0            0            1            0            0            0       3   \n",
       "1            0            1            0            0            0       8   \n",
       "2            0            1            0            0            0       5   \n",
       "3            0            1            0            0            0       3   \n",
       "4            0            1            0            0            0       0   \n",
       "\n",
       "   registered  tot  \n",
       "0          13   16  \n",
       "1          32   40  \n",
       "2          27   32  \n",
       "3          10   13  \n",
       "4           1    1  \n",
       "\n",
       "[5 rows x 63 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>is2011</th>\n",
       "      <th>d_season_1</th>\n",
       "      <th>d_season_2</th>\n",
       "      <th>d_season_3</th>\n",
       "      <th>d_season_4</th>\n",
       "      <th>d_month_1</th>\n",
       "      <th>...</th>\n",
       "      <th>d_weather_1</th>\n",
       "      <th>d_weather_2</th>\n",
       "      <th>d_weather_3</th>\n",
       "      <th>d_weather_4</th>\n",
       "      <th>avg_casual</th>\n",
       "      <th>avg_registered</th>\n",
       "      <th>avg_tot</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>21.166667</td>\n",
       "      <td>24.166667</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>21.166667</td>\n",
       "      <td>24.166667</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>8.500000</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>8.500000</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>8.500000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 66 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   temp   atemp  humidity  windspeed  is2011  d_season_1  d_season_2  \\\n",
       "0  9.84  14.395        81          0       1           1           0   \n",
       "1  9.02  13.635        80          0       1           1           0   \n",
       "2  9.02  13.635        80          0       1           1           0   \n",
       "3  9.84  14.395        75          0       1           1           0   \n",
       "4  9.84  14.395        75          0       1           1           0   \n",
       "\n",
       "   d_season_3  d_season_4  d_month_1 ...   d_weather_1  d_weather_2  \\\n",
       "0           0           0          1 ...             1            0   \n",
       "1           0           0          1 ...             1            0   \n",
       "2           0           0          1 ...             1            0   \n",
       "3           0           0          1 ...             1            0   \n",
       "4           0           0          1 ...             1            0   \n",
       "\n",
       "   d_weather_3  d_weather_4  avg_casual  avg_registered    avg_tot  casual  \\\n",
       "0            0            0           3       21.166667  24.166667       3   \n",
       "1            0            0           3       21.166667  24.166667       8   \n",
       "2            0            0           1        7.500000   8.500000       5   \n",
       "3            0            0           1        7.500000   8.500000       3   \n",
       "4            0            0           1        7.500000   8.500000       0   \n",
       "\n",
       "   registered  tot  \n",
       "0          13   16  \n",
       "1          32   40  \n",
       "2          27   32  \n",
       "3          10   13  \n",
       "4           1    1  \n",
       "\n",
       "[5 rows x 66 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>avg_casual</th>\n",
       "      <th>avg_registered</th>\n",
       "      <th>avg_tot</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>21.166667</td>\n",
       "      <td>24.166667</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>21.166667</td>\n",
       "      <td>24.166667</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>8.500000</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>8.500000</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>8.500000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0  9.84  14.395        81          0  2011       1      1        5     0   \n",
       "1  9.02  13.635        80          0  2011       1      1        5     1   \n",
       "2  9.02  13.635        80          0  2011       1      1        5     2   \n",
       "3  9.84  14.395        75          0  2011       1      1        5     3   \n",
       "4  9.84  14.395        75          0  2011       1      1        5     4   \n",
       "\n",
       "   workingday  holiday  weather  avg_casual  avg_registered    avg_tot  \\\n",
       "0           0        0        1           3       21.166667  24.166667   \n",
       "1           0        0        1           3       21.166667  24.166667   \n",
       "2           0        0        1           1        7.500000   8.500000   \n",
       "3           0        0        1           1        7.500000   8.500000   \n",
       "4           0        0        1           1        7.500000   8.500000   \n",
       "\n",
       "   casual  registered  tot  \n",
       "0       3          13   16  \n",
       "1       8          32   40  \n",
       "2       5          27   32  \n",
       "3       3          10   13  \n",
       "4       0           1    1  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dummy_train = pd.read_csv('data/train_prep_dum.csv')\n",
    "\n",
    "display(dummy_train.head())\n",
    "dummy_train_avg = dummy_train.ix[:,'temp':'d_weather_4'].copy()\n",
    "\n",
    "dummy_train_avg['avg_casual'] = data_train['avg_casual']\n",
    "dummy_train_avg['avg_registered'] = data_train['avg_registered']\n",
    "dummy_train_avg['avg_tot'] = data_train['avg_tot']\n",
    "\n",
    "dummy_train_avg['casual'] = dummy_train['casual']\n",
    "dummy_train_avg['registered'] = dummy_train['registered']\n",
    "dummy_train_avg['tot'] = dummy_train['tot']\n",
    "\n",
    "display(dummy_train_avg.head())\n",
    "display(data_train[num_var+cat_var+avg_var+target_var].head())\n",
    "\n",
    "dummy_train_avg.to_csv('data/train_prep_dum_avg.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next step is to add the new features to the test data set. First we load the data set and group by ['`year`','`month`','`weekday`','`period`']."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "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>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.66</td>\n",
       "      <td>11.365</td>\n",
       "      <td>56</td>\n",
       "      <td>26.0027</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0  10.66  11.365        56    26.0027  2011       1      1        3     0   \n",
       "1  10.66  13.635        56     0.0000  2011       1      1        3     1   \n",
       "2  10.66  13.635        56     0.0000  2011       1      1        3     2   \n",
       "3  10.66  12.880        56    11.0014  2011       1      1        3     3   \n",
       "4  10.66  12.880        56    11.0014  2011       1      1        3     4   \n",
       "\n",
       "   workingday  holiday  weather  \n",
       "0           1        0        1  \n",
       "1           1        0        1  \n",
       "2           1        0        1  \n",
       "3           1        0        1  \n",
       "4           1        0        1  "
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_test_avg = pd.read_csv('data/test_prep_orig.csv')\n",
    "data_test_avg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "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>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>periods</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.66</td>\n",
       "      <td>11.365</td>\n",
       "      <td>56</td>\n",
       "      <td>26.0027</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9.84</td>\n",
       "      <td>11.365</td>\n",
       "      <td>60</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>60</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55</td>\n",
       "      <td>19.0012</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.84</td>\n",
       "      <td>11.365</td>\n",
       "      <td>52</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0  10.66  11.365        56    26.0027  2011       1      1        3     0   \n",
       "1  10.66  13.635        56     0.0000  2011       1      1        3     1   \n",
       "2  10.66  13.635        56     0.0000  2011       1      1        3     2   \n",
       "3  10.66  12.880        56    11.0014  2011       1      1        3     3   \n",
       "4  10.66  12.880        56    11.0014  2011       1      1        3     4   \n",
       "5   9.84  11.365        60    15.0013  2011       1      1        3     5   \n",
       "6   9.02  10.605        60    15.0013  2011       1      1        3     6   \n",
       "7   9.02  10.605        55    15.0013  2011       1      1        3     7   \n",
       "8   9.02  10.605        55    19.0012  2011       1      1        3     8   \n",
       "9   9.84  11.365        52    15.0013  2011       1      1        3     9   \n",
       "\n",
       "   workingday  holiday  weather  periods  \n",
       "0           1        0        1        6  \n",
       "1           1        0        1        6  \n",
       "2           1        0        1        1  \n",
       "3           1        0        1        1  \n",
       "4           1        0        1        1  \n",
       "5           1        0        1        1  \n",
       "6           1        0        1        2  \n",
       "7           1        0        1        2  \n",
       "8           1        0        1        2  \n",
       "9           1        0        2        2  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "hours = np.array(data_test_avg.hour)\n",
    "\n",
    "data_test_avg['periods']=np.where( (2 <= hours) &  (hours <= 5), 1,\n",
    "                           np.where( (6 <= hours) &  (hours <= 9), 2,\n",
    "                                 np.where( (10 <= hours) &  (hours <= 13), 3,\n",
    "                                          np.where( (14 <= hours) &  (hours <= 17), 4,\n",
    "                                                  np.where( (18 <= hours) &  (hours <= 23), 5, 6)\n",
    "                                                  )\n",
    "                                         )\n",
    "                                )\n",
    "                        )\n",
    "display(data_test_avg.head(10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6493\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>periods</th>\n",
       "      <th>avg_casual</th>\n",
       "      <th>avg_registered</th>\n",
       "      <th>avg_tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.66</td>\n",
       "      <td>11.365</td>\n",
       "      <td>56</td>\n",
       "      <td>26.0027</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9.84</td>\n",
       "      <td>11.365</td>\n",
       "      <td>60</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>60</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55</td>\n",
       "      <td>19.0012</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.84</td>\n",
       "      <td>11.365</td>\n",
       "      <td>52</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0  10.66  11.365        56    26.0027  2011       1      1        3     0   \n",
       "1  10.66  13.635        56     0.0000  2011       1      1        3     1   \n",
       "2  10.66  13.635        56     0.0000  2011       1      1        3     2   \n",
       "3  10.66  12.880        56    11.0014  2011       1      1        3     3   \n",
       "4  10.66  12.880        56    11.0014  2011       1      1        3     4   \n",
       "5   9.84  11.365        60    15.0013  2011       1      1        3     5   \n",
       "6   9.02  10.605        60    15.0013  2011       1      1        3     6   \n",
       "7   9.02  10.605        55    15.0013  2011       1      1        3     7   \n",
       "8   9.02  10.605        55    19.0012  2011       1      1        3     8   \n",
       "9   9.84  11.365        52    15.0013  2011       1      1        3     9   \n",
       "\n",
       "   workingday  holiday  weather  periods  avg_casual  avg_registered  avg_tot  \n",
       "0           1        0        1        6           0               0        0  \n",
       "1           1        0        1        6           0               0        0  \n",
       "2           1        0        1        1           0               0        0  \n",
       "3           1        0        1        1           0               0        0  \n",
       "4           1        0        1        1           0               0        0  \n",
       "5           1        0        1        1           0               0        0  \n",
       "6           1        0        1        2           0               0        0  \n",
       "7           1        0        1        2           0               0        0  \n",
       "8           1        0        1        2           0               0        0  \n",
       "9           1        0        2        2           0               0        0  "
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_test_avg['avg_casual'] = data_test_avg['avg_registered'] = data_test_avg['avg_tot'] = np.zeros(data_test_avg.shape[0])\n",
    "print data_test_avg.shape[0]\n",
    "data_test_avg.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1000\n",
      "2000\n",
      "3000\n",
      "4000\n",
      "5000\n",
      "6000\n"
     ]
    }
   ],
   "source": [
    "for i in range(data_test_avg.shape[0]):\n",
    "    if i % 1000 == 0: print i\n",
    "    data_test_avg.loc[i,'avg_casual'] = groupby_year_month_weekday_periods.xs(int(data_test_avg.loc[i].year), level ='year'\n",
    "                                     ).xs(int(data_test_avg.loc[i].month), level ='month'\n",
    "                                         ).xs(int(data_test_avg.loc[i].weekday), level ='weekday'\n",
    "                                             ).loc[int(data_test_avg.loc[i].periods)].casual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1000\n",
      "2000\n",
      "3000\n",
      "4000\n",
      "5000\n",
      "6000\n"
     ]
    }
   ],
   "source": [
    "for i in range(data_test_avg.shape[0]):\n",
    "    if i % 1000 == 0: print i\n",
    "    data_test_avg.loc[i,'avg_registered'] = groupby_year_month_weekday_periods.xs(int(data_test_avg.loc[i].year), \n",
    "                                                                               level ='year'\n",
    "                                     ).xs(int(data_test_avg.loc[i].month), level ='month'\n",
    "                                         ).xs(int(data_test_avg.loc[i].weekday), level ='weekday'\n",
    "                                             ).loc[int(data_test_avg.loc[i].periods)].registered"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1000\n",
      "2000\n",
      "3000\n",
      "4000\n",
      "5000\n",
      "6000\n"
     ]
    }
   ],
   "source": [
    "for i in range(data_test_avg.shape[0]):\n",
    "    if i % 1000 == 0: print i\n",
    "    data_test_avg.loc[i,'avg_tot'] = groupby_year_month_weekday_periods.xs(int(data_test_avg.loc[i].year), level ='year'\n",
    "                                     ).xs(int(data_test_avg.loc[i].month), level ='month'\n",
    "                                         ).xs(int(data_test_avg.loc[i].weekday), level ='weekday'\n",
    "                                             ).loc[int(data_test_avg.loc[i].periods)].tot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "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>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>periods</th>\n",
       "      <th>avg_casual</th>\n",
       "      <th>avg_registered</th>\n",
       "      <th>avg_tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.66</td>\n",
       "      <td>11.365</td>\n",
       "      <td>56</td>\n",
       "      <td>26.0027</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0.25</td>\n",
       "      <td>5.750000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0.25</td>\n",
       "      <td>5.750000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9.84</td>\n",
       "      <td>11.365</td>\n",
       "      <td>60</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>60</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2.00</td>\n",
       "      <td>112.125000</td>\n",
       "      <td>114.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2.00</td>\n",
       "      <td>112.125000</td>\n",
       "      <td>114.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55</td>\n",
       "      <td>19.0012</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2.00</td>\n",
       "      <td>112.125000</td>\n",
       "      <td>114.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.84</td>\n",
       "      <td>11.365</td>\n",
       "      <td>52</td>\n",
       "      <td>15.0013</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2.00</td>\n",
       "      <td>112.125000</td>\n",
       "      <td>114.125000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0  10.66  11.365        56    26.0027  2011       1      1        3     0   \n",
       "1  10.66  13.635        56     0.0000  2011       1      1        3     1   \n",
       "2  10.66  13.635        56     0.0000  2011       1      1        3     2   \n",
       "3  10.66  12.880        56    11.0014  2011       1      1        3     3   \n",
       "4  10.66  12.880        56    11.0014  2011       1      1        3     4   \n",
       "5   9.84  11.365        60    15.0013  2011       1      1        3     5   \n",
       "6   9.02  10.605        60    15.0013  2011       1      1        3     6   \n",
       "7   9.02  10.605        55    15.0013  2011       1      1        3     7   \n",
       "8   9.02  10.605        55    19.0012  2011       1      1        3     8   \n",
       "9   9.84  11.365        52    15.0013  2011       1      1        3     9   \n",
       "\n",
       "   workingday  holiday  weather  periods  avg_casual  avg_registered  \\\n",
       "0           1        0        1        6        0.25        5.750000   \n",
       "1           1        0        1        6        0.25        5.750000   \n",
       "2           1        0        1        1        0.00        2.714286   \n",
       "3           1        0        1        1        0.00        2.714286   \n",
       "4           1        0        1        1        0.00        2.714286   \n",
       "5           1        0        1        1        0.00        2.714286   \n",
       "6           1        0        1        2        2.00      112.125000   \n",
       "7           1        0        1        2        2.00      112.125000   \n",
       "8           1        0        1        2        2.00      112.125000   \n",
       "9           1        0        2        2        2.00      112.125000   \n",
       "\n",
       "      avg_tot  \n",
       "0    6.000000  \n",
       "1    6.000000  \n",
       "2    2.714286  \n",
       "3    2.714286  \n",
       "4    2.714286  \n",
       "5    2.714286  \n",
       "6  114.125000  \n",
       "7  114.125000  \n",
       "8  114.125000  \n",
       "9  114.125000  "
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_test_avg.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "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>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "      <th>periods</th>\n",
       "      <th>avg_casual</th>\n",
       "      <th>avg_registered</th>\n",
       "      <th>avg_tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>7.38</td>\n",
       "      <td>12.120</td>\n",
       "      <td>55</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "      <td>0.25</td>\n",
       "      <td>5.750000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>6.56</td>\n",
       "      <td>11.365</td>\n",
       "      <td>64</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>0.25</td>\n",
       "      <td>5.750000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>6.56</td>\n",
       "      <td>11.365</td>\n",
       "      <td>64</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>6.56</td>\n",
       "      <td>9.850</td>\n",
       "      <td>64</td>\n",
       "      <td>6.0032</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>5.74</td>\n",
       "      <td>9.090</td>\n",
       "      <td>69</td>\n",
       "      <td>6.0032</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>5.74</td>\n",
       "      <td>8.335</td>\n",
       "      <td>63</td>\n",
       "      <td>7.0015</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>2.00</td>\n",
       "      <td>112.125000</td>\n",
       "      <td>114.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>6.56</td>\n",
       "      <td>11.365</td>\n",
       "      <td>59</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>95</td>\n",
       "      <td>95</td>\n",
       "      <td>2</td>\n",
       "      <td>2.00</td>\n",
       "      <td>112.125000</td>\n",
       "      <td>114.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>6.56</td>\n",
       "      <td>11.365</td>\n",
       "      <td>59</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>216</td>\n",
       "      <td>219</td>\n",
       "      <td>2</td>\n",
       "      <td>2.00</td>\n",
       "      <td>112.125000</td>\n",
       "      <td>114.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>7.38</td>\n",
       "      <td>12.120</td>\n",
       "      <td>51</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>116</td>\n",
       "      <td>122</td>\n",
       "      <td>2</td>\n",
       "      <td>2.00</td>\n",
       "      <td>112.125000</td>\n",
       "      <td>114.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>8.20</td>\n",
       "      <td>12.880</td>\n",
       "      <td>47</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>3</td>\n",
       "      <td>4.25</td>\n",
       "      <td>53.875000</td>\n",
       "      <td>58.125000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "115  7.38  12.120        55     0.0000  2011       1      1        3     0   \n",
       "116  6.56  11.365        64     0.0000  2011       1      1        3     1   \n",
       "117  6.56  11.365        64     0.0000  2011       1      1        3     2   \n",
       "118  6.56   9.850        64     6.0032  2011       1      1        3     4   \n",
       "119  5.74   9.090        69     6.0032  2011       1      1        3     5   \n",
       "120  5.74   8.335        63     7.0015  2011       1      1        3     6   \n",
       "121  6.56  11.365        59     0.0000  2011       1      1        3     7   \n",
       "122  6.56  11.365        59     0.0000  2011       1      1        3     8   \n",
       "123  7.38  12.120        51     0.0000  2011       1      1        3     9   \n",
       "124  8.20  12.880        47     0.0000  2011       1      1        3    10   \n",
       "\n",
       "     workingday  holiday  weather  casual  registered  tot  periods  \\\n",
       "115           1        0        1       0          11   11        6   \n",
       "116           1        0        1       0           4    4        6   \n",
       "117           1        0        1       0           2    2        1   \n",
       "118           1        0        2       0           1    1        1   \n",
       "119           1        0        2       0           4    4        1   \n",
       "120           1        0        2       0          36   36        2   \n",
       "121           1        0        2       0          95   95        2   \n",
       "122           1        0        1       3         216  219        2   \n",
       "123           1        0        2       6         116  122        2   \n",
       "124           1        0        1       3          42   45        3   \n",
       "\n",
       "     avg_casual  avg_registered     avg_tot  \n",
       "115        0.25        5.750000    6.000000  \n",
       "116        0.25        5.750000    6.000000  \n",
       "117        0.00        2.714286    2.714286  \n",
       "118        0.00        2.714286    2.714286  \n",
       "119        0.00        2.714286    2.714286  \n",
       "120        2.00      112.125000  114.125000  \n",
       "121        2.00      112.125000  114.125000  \n",
       "122        2.00      112.125000  114.125000  \n",
       "123        2.00      112.125000  114.125000  \n",
       "124        4.25       53.875000   58.125000  "
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking the last three. Intetestingly, the below table has no value fo hour == 3  \n",
    "\n",
    "data_train.loc[115:124]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "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>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>year</th>\n",
       "      <th>season</th>\n",
       "      <th>month</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>workingday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weather</th>\n",
       "      <th>avg_casual</th>\n",
       "      <th>avg_registered</th>\n",
       "      <th>avg_tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.66</td>\n",
       "      <td>11.365</td>\n",
       "      <td>56</td>\n",
       "      <td>26.0027</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.25</td>\n",
       "      <td>5.750000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.25</td>\n",
       "      <td>5.750000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0  10.66  11.365        56    26.0027  2011       1      1        3     0   \n",
       "1  10.66  13.635        56     0.0000  2011       1      1        3     1   \n",
       "2  10.66  13.635        56     0.0000  2011       1      1        3     2   \n",
       "3  10.66  12.880        56    11.0014  2011       1      1        3     3   \n",
       "4  10.66  12.880        56    11.0014  2011       1      1        3     4   \n",
       "\n",
       "   workingday  holiday  weather  avg_casual  avg_registered   avg_tot  \n",
       "0           1        0        1        0.25        5.750000  6.000000  \n",
       "1           1        0        1        0.25        5.750000  6.000000  \n",
       "2           1        0        1        0.00        2.714286  2.714286  \n",
       "3           1        0        1        0.00        2.714286  2.714286  \n",
       "4           1        0        1        0.00        2.714286  2.714286  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(data_test_avg[num_var+cat_var+avg_var].head())\n",
    "\n",
    "data_test_avg[num_var+cat_var+avg_var].to_csv('data/test_prep_orig_avg.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving `test_prep_dum_avg.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "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>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>is2011</th>\n",
       "      <th>d_season_1</th>\n",
       "      <th>d_season_2</th>\n",
       "      <th>d_season_3</th>\n",
       "      <th>d_season_4</th>\n",
       "      <th>d_month_1</th>\n",
       "      <th>...</th>\n",
       "      <th>d_hour_22</th>\n",
       "      <th>d_hour_23</th>\n",
       "      <th>d_workingday_0</th>\n",
       "      <th>d_workingday_1</th>\n",
       "      <th>d_holiday_0</th>\n",
       "      <th>d_holiday_1</th>\n",
       "      <th>d_weather_1</th>\n",
       "      <th>d_weather_2</th>\n",
       "      <th>d_weather_3</th>\n",
       "      <th>d_weather_4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.66</td>\n",
       "      <td>11.365</td>\n",
       "      <td>56</td>\n",
       "      <td>26.0027</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 60 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    temp   atemp  humidity  windspeed  is2011  d_season_1  d_season_2  \\\n",
       "0  10.66  11.365        56    26.0027       1           1           0   \n",
       "1  10.66  13.635        56     0.0000       1           1           0   \n",
       "2  10.66  13.635        56     0.0000       1           1           0   \n",
       "3  10.66  12.880        56    11.0014       1           1           0   \n",
       "4  10.66  12.880        56    11.0014       1           1           0   \n",
       "\n",
       "   d_season_3  d_season_4  d_month_1     ...       d_hour_22  d_hour_23  \\\n",
       "0           0           0          1     ...               0          0   \n",
       "1           0           0          1     ...               0          0   \n",
       "2           0           0          1     ...               0          0   \n",
       "3           0           0          1     ...               0          0   \n",
       "4           0           0          1     ...               0          0   \n",
       "\n",
       "   d_workingday_0  d_workingday_1  d_holiday_0  d_holiday_1  d_weather_1  \\\n",
       "0               0               1            1            0            1   \n",
       "1               0               1            1            0            1   \n",
       "2               0               1            1            0            1   \n",
       "3               0               1            1            0            1   \n",
       "4               0               1            1            0            1   \n",
       "\n",
       "   d_weather_2  d_weather_3  d_weather_4  \n",
       "0            0            0            0  \n",
       "1            0            0            0  \n",
       "2            0            0            0  \n",
       "3            0            0            0  \n",
       "4            0            0            0  \n",
       "\n",
       "[5 rows x 60 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>is2011</th>\n",
       "      <th>d_season_1</th>\n",
       "      <th>d_season_2</th>\n",
       "      <th>d_season_3</th>\n",
       "      <th>d_season_4</th>\n",
       "      <th>d_month_1</th>\n",
       "      <th>...</th>\n",
       "      <th>d_workingday_1</th>\n",
       "      <th>d_holiday_0</th>\n",
       "      <th>d_holiday_1</th>\n",
       "      <th>d_weather_1</th>\n",
       "      <th>d_weather_2</th>\n",
       "      <th>d_weather_3</th>\n",
       "      <th>d_weather_4</th>\n",
       "      <th>avg_casual</th>\n",
       "      <th>avg_registered</th>\n",
       "      <th>avg_tot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.66</td>\n",
       "      <td>11.365</td>\n",
       "      <td>56</td>\n",
       "      <td>26.0027</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>5.750000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>5.750000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56</td>\n",
       "      <td>11.0014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.714286</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 63 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    temp   atemp  humidity  windspeed  is2011  d_season_1  d_season_2  \\\n",
       "0  10.66  11.365        56    26.0027       1           1           0   \n",
       "1  10.66  13.635        56     0.0000       1           1           0   \n",
       "2  10.66  13.635        56     0.0000       1           1           0   \n",
       "3  10.66  12.880        56    11.0014       1           1           0   \n",
       "4  10.66  12.880        56    11.0014       1           1           0   \n",
       "\n",
       "   d_season_3  d_season_4  d_month_1    ...     d_workingday_1  d_holiday_0  \\\n",
       "0           0           0          1    ...                  1            1   \n",
       "1           0           0          1    ...                  1            1   \n",
       "2           0           0          1    ...                  1            1   \n",
       "3           0           0          1    ...                  1            1   \n",
       "4           0           0          1    ...                  1            1   \n",
       "\n",
       "   d_holiday_1  d_weather_1  d_weather_2  d_weather_3  d_weather_4  \\\n",
       "0            0            1            0            0            0   \n",
       "1            0            1            0            0            0   \n",
       "2            0            1            0            0            0   \n",
       "3            0            1            0            0            0   \n",
       "4            0            1            0            0            0   \n",
       "\n",
       "   avg_casual  avg_registered   avg_tot  \n",
       "0        0.25        5.750000  6.000000  \n",
       "1        0.25        5.750000  6.000000  \n",
       "2        0.00        2.714286  2.714286  \n",
       "3        0.00        2.714286  2.714286  \n",
       "4        0.00        2.714286  2.714286  \n",
       "\n",
       "[5 rows x 63 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dummy_test = pd.read_csv('data/test_prep_dum.csv')\n",
    "\n",
    "display(dummy_test.head())\n",
    "dummy_test_avg = dummy_test.ix[:,'temp':'d_weather_4'].copy()\n",
    "\n",
    "dummy_test_avg['avg_casual'] = data_test_avg['avg_casual']\n",
    "dummy_test_avg['avg_registered'] = data_test_avg['avg_registered']\n",
    "dummy_test_avg['avg_tot'] = data_test_avg['avg_tot']\n",
    "\n",
    "display(dummy_test_avg.head())\n",
    "\n",
    "dummy_test_avg.to_csv('data/test_prep_dum_avg.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}