08 - Kaggle - bike share system - Adding customer-value-logarithm average to the features.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 08 - Kaggle - bike share system - Adding customer-value-logarithm 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": 33,
   "metadata": {
    "collapsed": false
   },
   "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": 34,
   "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.0</td>\n",
       "      <td>0.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.0</td>\n",
       "      <td>0.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.0</td>\n",
       "      <td>0.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.0</td>\n",
       "      <td>0.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.0</td>\n",
       "      <td>0.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        0.0  2011       1      1        5     0   \n",
       "1  9.02  13.635      80.0        0.0  2011       1      1        5     1   \n",
       "2  9.02  13.635      80.0        0.0  2011       1      1        5     2   \n",
       "3  9.84  14.395      75.0        0.0  2011       1      1        5     3   \n",
       "4  9.84  14.395      75.0        0.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": [
    "## Adding the log1p values of target variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>l_casual</th>\n",
       "      <th>l_registered</th>\n",
       "      <th>l_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.0</td>\n",
       "      <td>0.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>1.386294</td>\n",
       "      <td>2.639057</td>\n",
       "      <td>2.833213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.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>2.197225</td>\n",
       "      <td>3.496508</td>\n",
       "      <td>3.713572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.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.791759</td>\n",
       "      <td>3.332205</td>\n",
       "      <td>3.496508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.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.386294</td>\n",
       "      <td>2.397895</td>\n",
       "      <td>2.639057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.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>0.000000</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>0.693147</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        0.0  2011       1      1        5     0   \n",
       "1  9.02  13.635      80.0        0.0  2011       1      1        5     1   \n",
       "2  9.02  13.635      80.0        0.0  2011       1      1        5     2   \n",
       "3  9.84  14.395      75.0        0.0  2011       1      1        5     3   \n",
       "4  9.84  14.395      75.0        0.0  2011       1      1        5     4   \n",
       "\n",
       "   workingday  holiday  weather  casual  registered  tot  l_casual  \\\n",
       "0           0        0        1       3          13   16  1.386294   \n",
       "1           0        0        1       8          32   40  2.197225   \n",
       "2           0        0        1       5          27   32  1.791759   \n",
       "3           0        0        1       3          10   13  1.386294   \n",
       "4           0        0        1       0           1    1  0.000000   \n",
       "\n",
       "   l_registered     l_tot  \n",
       "0      2.639057  2.833213  \n",
       "1      3.496508  3.713572  \n",
       "2      3.332205  3.496508  \n",
       "3      2.397895  2.639057  \n",
       "4      0.693147  0.693147  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data_train['l_casual']=np.log1p(data_train['casual'])\n",
    "data_train['l_registered']=np.log1p(data_train['registered'])\n",
    "data_train['l_tot']=np.log1p(data_train['tot'])\n",
    "\n",
    "display(data_train.head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## features and dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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",
    "target_l_var = ['l_casual', 'l_registered', 'l_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": 41,
   "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>l_casual</th>\n",
       "      <th>l_registered</th>\n",
       "      <th>l_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.0</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>1.386294</td>\n",
       "      <td>2.639057</td>\n",
       "      <td>2.833213</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.0</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>2.197225</td>\n",
       "      <td>3.496508</td>\n",
       "      <td>3.713572</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.0</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.791759</td>\n",
       "      <td>3.332205</td>\n",
       "      <td>3.496508</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.0</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.386294</td>\n",
       "      <td>2.397895</td>\n",
       "      <td>2.639057</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.0</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>0.000000</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>0.693147</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.0</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>0.000000</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>0.693147</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.0</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>1.098612</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.098612</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.0</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>0.693147</td>\n",
       "      <td>1.098612</td>\n",
       "      <td>1.386294</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.0</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>0.693147</td>\n",
       "      <td>2.079442</td>\n",
       "      <td>2.197225</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.0</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.197225</td>\n",
       "      <td>1.945910</td>\n",
       "      <td>2.708050</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.0</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>2.564949</td>\n",
       "      <td>3.218876</td>\n",
       "      <td>3.610918</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.0</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.295837</td>\n",
       "      <td>3.433987</td>\n",
       "      <td>4.043051</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.0</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.401197</td>\n",
       "      <td>4.025352</td>\n",
       "      <td>4.442651</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.0</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.871201</td>\n",
       "      <td>3.871201</td>\n",
       "      <td>4.553877</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.0</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>3.583519</td>\n",
       "      <td>4.276666</td>\n",
       "      <td>4.672829</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.0</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>3.713572</td>\n",
       "      <td>4.262680</td>\n",
       "      <td>4.709530</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.0</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>3.737670</td>\n",
       "      <td>3.970292</td>\n",
       "      <td>4.543295</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.0</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>2.772589</td>\n",
       "      <td>3.970292</td>\n",
       "      <td>4.219508</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.0</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>2.302585</td>\n",
       "      <td>3.295837</td>\n",
       "      <td>3.583519</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.0</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>1.945910</td>\n",
       "      <td>3.465736</td>\n",
       "      <td>3.637586</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     0.0000  2011       1      1        5     0   \n",
       "1    9.02  13.635      80.0     0.0000  2011       1      1        5     1   \n",
       "2    9.02  13.635      80.0     0.0000  2011       1      1        5     2   \n",
       "3    9.84  14.395      75.0     0.0000  2011       1      1        5     3   \n",
       "4    9.84  14.395      75.0     0.0000  2011       1      1        5     4   \n",
       "5    9.84  12.880      75.0     6.0032  2011       1      1        5     5   \n",
       "6    9.02  13.635      80.0     0.0000  2011       1      1        5     6   \n",
       "7    8.20  12.880      86.0     0.0000  2011       1      1        5     7   \n",
       "8    9.84  14.395      75.0     0.0000  2011       1      1        5     8   \n",
       "9   13.12  17.425      76.0     0.0000  2011       1      1        5     9   \n",
       "10  15.58  19.695      76.0    16.9979  2011       1      1        5    10   \n",
       "11  14.76  16.665      81.0    19.0012  2011       1      1        5    11   \n",
       "12  17.22  21.210      77.0    19.0012  2011       1      1        5    12   \n",
       "13  18.86  22.725      72.0    19.9995  2011       1      1        5    13   \n",
       "14  18.86  22.725      72.0    19.0012  2011       1      1        5    14   \n",
       "15  18.04  21.970      77.0    19.9995  2011       1      1        5    15   \n",
       "16  17.22  21.210      82.0    19.9995  2011       1      1        5    16   \n",
       "17  18.04  21.970      82.0    19.0012  2011       1      1        5    17   \n",
       "18  17.22  21.210      88.0    16.9979  2011       1      1        5    18   \n",
       "19  17.22  21.210      88.0    16.9979  2011       1      1        5    19   \n",
       "\n",
       "    workingday  holiday  weather  casual  registered  tot  l_casual  \\\n",
       "0            0        0        1       3          13   16  1.386294   \n",
       "1            0        0        1       8          32   40  2.197225   \n",
       "2            0        0        1       5          27   32  1.791759   \n",
       "3            0        0        1       3          10   13  1.386294   \n",
       "4            0        0        1       0           1    1  0.000000   \n",
       "5            0        0        2       0           1    1  0.000000   \n",
       "6            0        0        1       2           0    2  1.098612   \n",
       "7            0        0        1       1           2    3  0.693147   \n",
       "8            0        0        1       1           7    8  0.693147   \n",
       "9            0        0        1       8           6   14  2.197225   \n",
       "10           0        0        1      12          24   36  2.564949   \n",
       "11           0        0        1      26          30   56  3.295837   \n",
       "12           0        0        1      29          55   84  3.401197   \n",
       "13           0        0        2      47          47   94  3.871201   \n",
       "14           0        0        2      35          71  106  3.583519   \n",
       "15           0        0        2      40          70  110  3.713572   \n",
       "16           0        0        2      41          52   93  3.737670   \n",
       "17           0        0        2      15          52   67  2.772589   \n",
       "18           0        0        3       9          26   35  2.302585   \n",
       "19           0        0        3       6          31   37  1.945910   \n",
       "\n",
       "    l_registered     l_tot  periods  \n",
       "0       2.639057  2.833213        6  \n",
       "1       3.496508  3.713572        6  \n",
       "2       3.332205  3.496508        1  \n",
       "3       2.397895  2.639057        1  \n",
       "4       0.693147  0.693147        1  \n",
       "5       0.693147  0.693147        1  \n",
       "6       0.000000  1.098612        2  \n",
       "7       1.098612  1.386294        2  \n",
       "8       2.079442  2.197225        2  \n",
       "9       1.945910  2.708050        2  \n",
       "10      3.218876  3.610918        3  \n",
       "11      3.433987  4.043051        3  \n",
       "12      4.025352  4.442651        3  \n",
       "13      3.871201  4.553877        3  \n",
       "14      4.276666  4.672829        4  \n",
       "15      4.262680  4.709530        4  \n",
       "16      3.970292  4.543295        4  \n",
       "17      3.970292  4.219508        4  \n",
       "18      3.295837  3.583519        5  \n",
       "19      3.465736  3.637586        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>l_casual</th>\n",
       "      <th>l_registered</th>\n",
       "      <th>l_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.138629</td>\n",
       "      <td>1.173139</td>\n",
       "      <td>1.230675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.038780</td>\n",
       "      <td>3.816931</td>\n",
       "      <td>3.882240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.969823</td>\n",
       "      <td>3.866267</td>\n",
       "      <td>4.005175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.959106</td>\n",
       "      <td>4.312191</td>\n",
       "      <td>4.413583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.077602</td>\n",
       "      <td>3.653726</td>\n",
       "      <td>3.712849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.529676</td>\n",
       "      <td>1.647078</td>\n",
       "      <td>1.849811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">1</th>\n",
       "      <th>1</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.346680</td>\n",
       "      <td>1.346680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.895880</td>\n",
       "      <td>4.507533</td>\n",
       "      <td>4.521242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.542084</td>\n",
       "      <td>3.597040</td>\n",
       "      <td>3.691943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.631812</td>\n",
       "      <td>4.191619</td>\n",
       "      <td>4.279062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.507150</td>\n",
       "      <td>3.843064</td>\n",
       "      <td>3.866688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.274653</td>\n",
       "      <td>1.668640</td>\n",
       "      <td>1.710404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">2</th>\n",
       "      <th>1</th>\n",
       "      <td>0.086643</td>\n",
       "      <td>1.278414</td>\n",
       "      <td>1.329097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.773571</td>\n",
       "      <td>4.331233</td>\n",
       "      <td>4.345825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.453852</td>\n",
       "      <td>3.937474</td>\n",
       "      <td>4.021056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.528945</td>\n",
       "      <td>4.407484</td>\n",
       "      <td>4.468716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.169725</td>\n",
       "      <td>4.115982</td>\n",
       "      <td>4.156350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.115525</td>\n",
       "      <td>1.874896</td>\n",
       "      <td>1.897151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">3</th>\n",
       "      <th>1</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.777822</td>\n",
       "      <td>4.523827</td>\n",
       "      <td>4.536710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.523590</td>\n",
       "      <td>3.959058</td>\n",
       "      <td>4.035925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.929471</td>\n",
       "      <td>4.330747</td>\n",
       "      <td>4.420036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.803294</td>\n",
       "      <td>4.032818</td>\n",
       "      <td>4.066030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.173287</td>\n",
       "      <td>1.784717</td>\n",
       "      <td>1.818100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">4</th>\n",
       "      <th>1</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.126929</td>\n",
       "      <td>1.126929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.355021</td>\n",
       "      <td>4.338522</td>\n",
       "      <td>4.405972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.087189</td>\n",
       "      <td>3.981552</td>\n",
       "      <td>4.132036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.945806</td>\n",
       "      <td>4.408148</td>\n",
       "      <td>4.491324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.044158</td>\n",
       "      <td>3.963423</td>\n",
       "      <td>4.002502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.677013</td>\n",
       "      <td>2.232657</td>\n",
       "      <td>2.367406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">5</th>\n",
       "      <th>1</th>\n",
       "      <td>0.438125</td>\n",
       "      <td>1.787425</td>\n",
       "      <td>1.847208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.712778</td>\n",
       "      <td>2.019945</td>\n",
       "      <td>2.258091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.517242</td>\n",
       "      <td>4.046522</td>\n",
       "      <td>4.305017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.955623</td>\n",
       "      <td>4.280280</td>\n",
       "      <td>4.546382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.629383</td>\n",
       "      <td>3.519572</td>\n",
       "      <td>3.681076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1.242453</td>\n",
       "      <td>3.054927</td>\n",
       "      <td>3.174986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">6</th>\n",
       "      <th>1</th>\n",
       "      <td>0.661127</td>\n",
       "      <td>1.450155</td>\n",
       "      <td>1.682999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.231049</td>\n",
       "      <td>1.940694</td>\n",
       "      <td>1.965548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.254214</td>\n",
       "      <td>4.182009</td>\n",
       "      <td>4.339141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.502841</td>\n",
       "      <td>4.229059</td>\n",
       "      <td>4.404744</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            l_casual  l_registered     l_tot\n",
       "year month weekday periods                                  \n",
       "2011 1     0       1        0.138629      1.173139  1.230675\n",
       "                   2        1.038780      3.816931  3.882240\n",
       "                   3        1.969823      3.866267  4.005175\n",
       "                   4        1.959106      4.312191  4.413583\n",
       "                   5        1.077602      3.653726  3.712849\n",
       "                   6        0.529676      1.647078  1.849811\n",
       "           1       1        0.000000      1.346680  1.346680\n",
       "                   2        0.895880      4.507533  4.521242\n",
       "                   3        1.542084      3.597040  3.691943\n",
       "                   4        1.631812      4.191619  4.279062\n",
       "                   5        0.507150      3.843064  3.866688\n",
       "                   6        0.274653      1.668640  1.710404\n",
       "           2       1        0.086643      1.278414  1.329097\n",
       "                   2        0.773571      4.331233  4.345825\n",
       "                   3        1.453852      3.937474  4.021056\n",
       "                   4        1.528945      4.407484  4.468716\n",
       "                   5        1.169725      4.115982  4.156350\n",
       "                   6        0.115525      1.874896  1.897151\n",
       "           3       1        0.000000      1.268834  1.268834\n",
       "                   2        0.777822      4.523827  4.536710\n",
       "                   3        1.523590      3.959058  4.035925\n",
       "                   4        1.929471      4.330747  4.420036\n",
       "                   5        0.803294      4.032818  4.066030\n",
       "                   6        0.173287      1.784717  1.818100\n",
       "           4       1        0.000000      1.126929  1.126929\n",
       "                   2        1.355021      4.338522  4.405972\n",
       "                   3        2.087189      3.981552  4.132036\n",
       "                   4        1.945806      4.408148  4.491324\n",
       "                   5        1.044158      3.963423  4.002502\n",
       "                   6        0.677013      2.232657  2.367406\n",
       "           5       1        0.438125      1.787425  1.847208\n",
       "                   2        0.712778      2.019945  2.258091\n",
       "                   3        2.517242      4.046522  4.305017\n",
       "                   4        2.955623      4.280280  4.546382\n",
       "                   5        1.629383      3.519572  3.681076\n",
       "                   6        1.242453      3.054927  3.174986\n",
       "           6       1        0.661127      1.450155  1.682999\n",
       "                   2        0.231049      1.940694  1.965548\n",
       "                   3        2.254214      4.182009  4.339141\n",
       "                   4        2.502841      4.229059  4.404744"
      ]
     },
     "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_l_var]\n",
    "display(groupby_year_month_weekday_periods.head(40))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>...</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "      <th>l_casual</th>\n",
       "      <th>l_registered</th>\n",
       "      <th>l_tot</th>\n",
       "      <th>periods</th>\n",
       "      <th>avg_l_casual</th>\n",
       "      <th>avg_l_registered</th>\n",
       "      <th>avg_l_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.0</td>\n",
       "      <td>0.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>...</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "      <td>1.386294</td>\n",
       "      <td>2.639057</td>\n",
       "      <td>2.833213</td>\n",
       "      <td>6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.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>...</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "      <td>2.197225</td>\n",
       "      <td>3.496508</td>\n",
       "      <td>3.713572</td>\n",
       "      <td>6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.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>...</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "      <td>1.791759</td>\n",
       "      <td>3.332205</td>\n",
       "      <td>3.496508</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.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>...</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>1.386294</td>\n",
       "      <td>2.397895</td>\n",
       "      <td>2.639057</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.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>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "0  9.84  14.395      81.0        0.0  2011       1      1        5     0   \n",
       "1  9.02  13.635      80.0        0.0  2011       1      1        5     1   \n",
       "2  9.02  13.635      80.0        0.0  2011       1      1        5     2   \n",
       "3  9.84  14.395      75.0        0.0  2011       1      1        5     3   \n",
       "4  9.84  14.395      75.0        0.0  2011       1      1        5     4   \n",
       "\n",
       "   workingday    ...      casual  registered  tot  l_casual  l_registered  \\\n",
       "0           0    ...           3          13   16  1.386294      2.639057   \n",
       "1           0    ...           8          32   40  2.197225      3.496508   \n",
       "2           0    ...           5          27   32  1.791759      3.332205   \n",
       "3           0    ...           3          10   13  1.386294      2.397895   \n",
       "4           0    ...           0           1    1  0.000000      0.693147   \n",
       "\n",
       "      l_tot  periods  avg_l_casual  avg_l_registered  avg_l_tot  \n",
       "0  2.833213        6           0.0               0.0        0.0  \n",
       "1  3.713572        6           0.0               0.0        0.0  \n",
       "2  3.496508        1           0.0               0.0        0.0  \n",
       "3  2.639057        1           0.0               0.0        0.0  \n",
       "4  0.693147        1           0.0               0.0        0.0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train['avg_l_casual'] = data_train['avg_l_registered'] = data_train['avg_l_tot'] = np.zeros(data_train.shape[0])\n",
    "print data_train.shape[0]\n",
    "data_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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_l_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)]['l_casual']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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_l_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)].l_registered\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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_l_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)].l_tot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>...</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "      <th>l_casual</th>\n",
       "      <th>l_registered</th>\n",
       "      <th>l_tot</th>\n",
       "      <th>periods</th>\n",
       "      <th>avg_l_casual</th>\n",
       "      <th>avg_l_registered</th>\n",
       "      <th>avg_l_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.0</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>...</td>\n",
       "      <td>12</td>\n",
       "      <td>74</td>\n",
       "      <td>86</td>\n",
       "      <td>2.564949</td>\n",
       "      <td>4.317488</td>\n",
       "      <td>4.465908</td>\n",
       "      <td>4</td>\n",
       "      <td>1.929471</td>\n",
       "      <td>4.330747</td>\n",
       "      <td>4.420036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>9.02</td>\n",
       "      <td>11.365</td>\n",
       "      <td>51.0</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>...</td>\n",
       "      <td>9</td>\n",
       "      <td>163</td>\n",
       "      <td>172</td>\n",
       "      <td>2.302585</td>\n",
       "      <td>5.099866</td>\n",
       "      <td>5.153292</td>\n",
       "      <td>4</td>\n",
       "      <td>1.929471</td>\n",
       "      <td>4.330747</td>\n",
       "      <td>4.420036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>9.02</td>\n",
       "      <td>11.365</td>\n",
       "      <td>51.0</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>...</td>\n",
       "      <td>5</td>\n",
       "      <td>158</td>\n",
       "      <td>163</td>\n",
       "      <td>1.791759</td>\n",
       "      <td>5.068904</td>\n",
       "      <td>5.099866</td>\n",
       "      <td>5</td>\n",
       "      <td>0.803294</td>\n",
       "      <td>4.032818</td>\n",
       "      <td>4.066030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>9.02</td>\n",
       "      <td>12.880</td>\n",
       "      <td>55.0</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>...</td>\n",
       "      <td>3</td>\n",
       "      <td>109</td>\n",
       "      <td>112</td>\n",
       "      <td>1.386294</td>\n",
       "      <td>4.700480</td>\n",
       "      <td>4.727388</td>\n",
       "      <td>5</td>\n",
       "      <td>0.803294</td>\n",
       "      <td>4.032818</td>\n",
       "      <td>4.066030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>8.20</td>\n",
       "      <td>10.605</td>\n",
       "      <td>51.0</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>...</td>\n",
       "      <td>3</td>\n",
       "      <td>66</td>\n",
       "      <td>69</td>\n",
       "      <td>1.386294</td>\n",
       "      <td>4.204693</td>\n",
       "      <td>4.248495</td>\n",
       "      <td>5</td>\n",
       "      <td>0.803294</td>\n",
       "      <td>4.032818</td>\n",
       "      <td>4.066030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55.0</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>...</td>\n",
       "      <td>0</td>\n",
       "      <td>48</td>\n",
       "      <td>48</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.891820</td>\n",
       "      <td>3.891820</td>\n",
       "      <td>5</td>\n",
       "      <td>0.803294</td>\n",
       "      <td>4.032818</td>\n",
       "      <td>4.066030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>51.0</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>...</td>\n",
       "      <td>1</td>\n",
       "      <td>51</td>\n",
       "      <td>52</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>3.951244</td>\n",
       "      <td>3.970292</td>\n",
       "      <td>5</td>\n",
       "      <td>0.803294</td>\n",
       "      <td>4.032818</td>\n",
       "      <td>4.066030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>8.20</td>\n",
       "      <td>9.850</td>\n",
       "      <td>59.0</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>...</td>\n",
       "      <td>4</td>\n",
       "      <td>19</td>\n",
       "      <td>23</td>\n",
       "      <td>1.609438</td>\n",
       "      <td>2.995732</td>\n",
       "      <td>3.178054</td>\n",
       "      <td>5</td>\n",
       "      <td>0.803294</td>\n",
       "      <td>4.032818</td>\n",
       "      <td>4.066030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>8.20</td>\n",
       "      <td>9.850</td>\n",
       "      <td>64.0</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>...</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "      <td>1.609438</td>\n",
       "      <td>2.639057</td>\n",
       "      <td>2.890372</td>\n",
       "      <td>6</td>\n",
       "      <td>0.677013</td>\n",
       "      <td>2.232657</td>\n",
       "      <td>2.367406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>8.20</td>\n",
       "      <td>9.850</td>\n",
       "      <td>69.0</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>...</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>1.098612</td>\n",
       "      <td>1.791759</td>\n",
       "      <td>2.079442</td>\n",
       "      <td>6</td>\n",
       "      <td>0.677013</td>\n",
       "      <td>2.232657</td>\n",
       "      <td>2.367406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>8.20</td>\n",
       "      <td>9.850</td>\n",
       "      <td>69.0</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>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.126929</td>\n",
       "      <td>1.126929</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      temp   atemp  humidity  windspeed  year  season  month  weekday  hour  \\\n",
       "130  10.66  12.880      38.0    11.0014  2011       1      1        3    16   \n",
       "131   9.02  11.365      51.0    11.0014  2011       1      1        3    17   \n",
       "132   9.02  11.365      51.0     8.9981  2011       1      1        3    18   \n",
       "133   9.02  12.880      55.0     6.0032  2011       1      1        3    19   \n",
       "134   8.20  10.605      51.0    11.0014  2011       1      1        3    20   \n",
       "135   9.02  10.605      55.0    15.0013  2011       1      1        3    21   \n",
       "136   9.02  10.605      51.0    19.0012  2011       1      1        3    22   \n",
       "137   8.20   9.850      59.0    12.9980  2011       1      1        3    23   \n",
       "138   8.20   9.850      64.0    12.9980  2011       1      1        4     0   \n",
       "139   8.20   9.850      69.0    15.0013  2011       1      1        4     1   \n",
       "140   8.20   9.850      69.0    15.0013  2011       1      1        4     2   \n",
       "\n",
       "     workingday    ...      casual  registered  tot  l_casual  l_registered  \\\n",
       "130           1    ...          12          74   86  2.564949      4.317488   \n",
       "131           1    ...           9         163  172  2.302585      5.099866   \n",
       "132           1    ...           5         158  163  1.791759      5.068904   \n",
       "133           1    ...           3         109  112  1.386294      4.700480   \n",
       "134           1    ...           3          66   69  1.386294      4.204693   \n",
       "135           1    ...           0          48   48  0.000000      3.891820   \n",
       "136           1    ...           1          51   52  0.693147      3.951244   \n",
       "137           1    ...           4          19   23  1.609438      2.995732   \n",
       "138           1    ...           4          13   17  1.609438      2.639057   \n",
       "139           1    ...           2           5    7  1.098612      1.791759   \n",
       "140           1    ...           0           1    1  0.000000      0.693147   \n",
       "\n",
       "        l_tot  periods  avg_l_casual  avg_l_registered  avg_l_tot  \n",
       "130  4.465908        4      1.929471          4.330747   4.420036  \n",
       "131  5.153292        4      1.929471          4.330747   4.420036  \n",
       "132  5.099866        5      0.803294          4.032818   4.066030  \n",
       "133  4.727388        5      0.803294          4.032818   4.066030  \n",
       "134  4.248495        5      0.803294          4.032818   4.066030  \n",
       "135  3.891820        5      0.803294          4.032818   4.066030  \n",
       "136  3.970292        5      0.803294          4.032818   4.066030  \n",
       "137  3.178054        5      0.803294          4.032818   4.066030  \n",
       "138  2.890372        6      0.677013          2.232657   2.367406  \n",
       "139  2.079442        6      0.677013          2.232657   2.367406  \n",
       "140  0.693147        1      0.000000          1.126929   1.126929  \n",
       "\n",
       "[11 rows x 22 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train.loc[130:140]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data_train=data_train.astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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",
       "l_casual            float64\n",
       "l_registered        float64\n",
       "l_tot               float64\n",
       "periods             float64\n",
       "avg_l_casual        float64\n",
       "avg_l_registered    float64\n",
       "avg_l_tot           float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving `train_prep_orig_l_avg.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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_l_casual</th>\n",
       "      <th>avg_l_registered</th>\n",
       "      <th>avg_l_tot</th>\n",
       "      <th>l_casual</th>\n",
       "      <th>l_registered</th>\n",
       "      <th>l_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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.242453</td>\n",
       "      <td>3.054927</td>\n",
       "      <td>3.174986</td>\n",
       "      <td>1.386294</td>\n",
       "      <td>2.639057</td>\n",
       "      <td>2.833213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.242453</td>\n",
       "      <td>3.054927</td>\n",
       "      <td>3.174986</td>\n",
       "      <td>2.197225</td>\n",
       "      <td>3.496508</td>\n",
       "      <td>3.713572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.438125</td>\n",
       "      <td>1.787425</td>\n",
       "      <td>1.847208</td>\n",
       "      <td>1.791759</td>\n",
       "      <td>3.332205</td>\n",
       "      <td>3.496508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.438125</td>\n",
       "      <td>1.787425</td>\n",
       "      <td>1.847208</td>\n",
       "      <td>1.386294</td>\n",
       "      <td>2.397895</td>\n",
       "      <td>2.639057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.438125</td>\n",
       "      <td>1.787425</td>\n",
       "      <td>1.847208</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>0.693147</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        0.0  2011.0     1.0    1.0      5.0   0.0   \n",
       "1  9.02  13.635      80.0        0.0  2011.0     1.0    1.0      5.0   1.0   \n",
       "2  9.02  13.635      80.0        0.0  2011.0     1.0    1.0      5.0   2.0   \n",
       "3  9.84  14.395      75.0        0.0  2011.0     1.0    1.0      5.0   3.0   \n",
       "4  9.84  14.395      75.0        0.0  2011.0     1.0    1.0      5.0   4.0   \n",
       "\n",
       "   workingday  holiday  weather  avg_l_casual  avg_l_registered  avg_l_tot  \\\n",
       "0         0.0      0.0      1.0      1.242453          3.054927   3.174986   \n",
       "1         0.0      0.0      1.0      1.242453          3.054927   3.174986   \n",
       "2         0.0      0.0      1.0      0.438125          1.787425   1.847208   \n",
       "3         0.0      0.0      1.0      0.438125          1.787425   1.847208   \n",
       "4         0.0      0.0      1.0      0.438125          1.787425   1.847208   \n",
       "\n",
       "   l_casual  l_registered     l_tot  \n",
       "0  1.386294      2.639057  2.833213  \n",
       "1  2.197225      3.496508  3.713572  \n",
       "2  1.791759      3.332205  3.496508  \n",
       "3  1.386294      2.397895  2.639057  \n",
       "4  0.000000      0.693147  0.693147  "
      ]
     },
     "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",
    "avg_l_var = ['avg_l_casual','avg_l_registered','avg_l_tot']\n",
    "target_var = ['casual', 'registered', 'tot']\n",
    "target_l_var = ['l_casual', 'l_registered', 'l_tot']\n",
    "\n",
    "\n",
    "\n",
    "display(data_train[num_var+cat_var+avg_l_var+target_l_var].head())\n",
    "\n",
    "data_train[num_var+cat_var+avg_l_var+target_l_var].to_csv('data/train_prep_orig_l_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": 52,
   "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.0</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.0</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.0</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.0</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.0</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.0    26.0027  2011       1      1        3     0   \n",
       "1  10.66  13.635      56.0     0.0000  2011       1      1        3     1   \n",
       "2  10.66  13.635      56.0     0.0000  2011       1      1        3     2   \n",
       "3  10.66  12.880      56.0    11.0014  2011       1      1        3     3   \n",
       "4  10.66  12.880      56.0    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": 52,
     "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": 53,
   "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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0    26.0027  2011       1      1        3     0   \n",
       "1  10.66  13.635      56.0     0.0000  2011       1      1        3     1   \n",
       "2  10.66  13.635      56.0     0.0000  2011       1      1        3     2   \n",
       "3  10.66  12.880      56.0    11.0014  2011       1      1        3     3   \n",
       "4  10.66  12.880      56.0    11.0014  2011       1      1        3     4   \n",
       "5   9.84  11.365      60.0    15.0013  2011       1      1        3     5   \n",
       "6   9.02  10.605      60.0    15.0013  2011       1      1        3     6   \n",
       "7   9.02  10.605      55.0    15.0013  2011       1      1        3     7   \n",
       "8   9.02  10.605      55.0    19.0012  2011       1      1        3     8   \n",
       "9   9.84  11.365      52.0    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": 54,
   "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_l_casual</th>\n",
       "      <th>avg_l_registered</th>\n",
       "      <th>avg_l_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.0</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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56.0</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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56.0</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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56.0</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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56.0</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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9.84</td>\n",
       "      <td>11.365</td>\n",
       "      <td>60.0</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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>60.0</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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55.0</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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55.0</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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.84</td>\n",
       "      <td>11.365</td>\n",
       "      <td>52.0</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.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.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.0    26.0027  2011       1      1        3     0   \n",
       "1  10.66  13.635      56.0     0.0000  2011       1      1        3     1   \n",
       "2  10.66  13.635      56.0     0.0000  2011       1      1        3     2   \n",
       "3  10.66  12.880      56.0    11.0014  2011       1      1        3     3   \n",
       "4  10.66  12.880      56.0    11.0014  2011       1      1        3     4   \n",
       "5   9.84  11.365      60.0    15.0013  2011       1      1        3     5   \n",
       "6   9.02  10.605      60.0    15.0013  2011       1      1        3     6   \n",
       "7   9.02  10.605      55.0    15.0013  2011       1      1        3     7   \n",
       "8   9.02  10.605      55.0    19.0012  2011       1      1        3     8   \n",
       "9   9.84  11.365      52.0    15.0013  2011       1      1        3     9   \n",
       "\n",
       "   workingday  holiday  weather  periods  avg_l_casual  avg_l_registered  \\\n",
       "0           1        0        1        6           0.0               0.0   \n",
       "1           1        0        1        6           0.0               0.0   \n",
       "2           1        0        1        1           0.0               0.0   \n",
       "3           1        0        1        1           0.0               0.0   \n",
       "4           1        0        1        1           0.0               0.0   \n",
       "5           1        0        1        1           0.0               0.0   \n",
       "6           1        0        1        2           0.0               0.0   \n",
       "7           1        0        1        2           0.0               0.0   \n",
       "8           1        0        1        2           0.0               0.0   \n",
       "9           1        0        2        2           0.0               0.0   \n",
       "\n",
       "   avg_l_tot  \n",
       "0        0.0  \n",
       "1        0.0  \n",
       "2        0.0  \n",
       "3        0.0  \n",
       "4        0.0  \n",
       "5        0.0  \n",
       "6        0.0  \n",
       "7        0.0  \n",
       "8        0.0  \n",
       "9        0.0  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_test_avg['avg_l_casual'] = data_test_avg['avg_l_registered'] = data_test_avg['avg_l_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": 55,
   "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_l_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)].l_casual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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_l_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)].l_registered"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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_l_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)].l_tot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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_l_casual</th>\n",
       "      <th>avg_l_registered</th>\n",
       "      <th>avg_l_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.0</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.173287</td>\n",
       "      <td>1.784717</td>\n",
       "      <td>1.818100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56.0</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.173287</td>\n",
       "      <td>1.784717</td>\n",
       "      <td>1.818100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56.0</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.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56.0</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.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56.0</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.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9.84</td>\n",
       "      <td>11.365</td>\n",
       "      <td>60.0</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.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>60.0</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.777822</td>\n",
       "      <td>4.523827</td>\n",
       "      <td>4.536710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55.0</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.777822</td>\n",
       "      <td>4.523827</td>\n",
       "      <td>4.536710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9.02</td>\n",
       "      <td>10.605</td>\n",
       "      <td>55.0</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.777822</td>\n",
       "      <td>4.523827</td>\n",
       "      <td>4.536710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.84</td>\n",
       "      <td>11.365</td>\n",
       "      <td>52.0</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.777822</td>\n",
       "      <td>4.523827</td>\n",
       "      <td>4.536710</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.0    26.0027  2011       1      1        3     0   \n",
       "1  10.66  13.635      56.0     0.0000  2011       1      1        3     1   \n",
       "2  10.66  13.635      56.0     0.0000  2011       1      1        3     2   \n",
       "3  10.66  12.880      56.0    11.0014  2011       1      1        3     3   \n",
       "4  10.66  12.880      56.0    11.0014  2011       1      1        3     4   \n",
       "5   9.84  11.365      60.0    15.0013  2011       1      1        3     5   \n",
       "6   9.02  10.605      60.0    15.0013  2011       1      1        3     6   \n",
       "7   9.02  10.605      55.0    15.0013  2011       1      1        3     7   \n",
       "8   9.02  10.605      55.0    19.0012  2011       1      1        3     8   \n",
       "9   9.84  11.365      52.0    15.0013  2011       1      1        3     9   \n",
       "\n",
       "   workingday  holiday  weather  periods  avg_l_casual  avg_l_registered  \\\n",
       "0           1        0        1        6      0.173287          1.784717   \n",
       "1           1        0        1        6      0.173287          1.784717   \n",
       "2           1        0        1        1      0.000000          1.268834   \n",
       "3           1        0        1        1      0.000000          1.268834   \n",
       "4           1        0        1        1      0.000000          1.268834   \n",
       "5           1        0        1        1      0.000000          1.268834   \n",
       "6           1        0        1        2      0.777822          4.523827   \n",
       "7           1        0        1        2      0.777822          4.523827   \n",
       "8           1        0        1        2      0.777822          4.523827   \n",
       "9           1        0        2        2      0.777822          4.523827   \n",
       "\n",
       "   avg_l_tot  \n",
       "0   1.818100  \n",
       "1   1.818100  \n",
       "2   1.268834  \n",
       "3   1.268834  \n",
       "4   1.268834  \n",
       "5   1.268834  \n",
       "6   4.536710  \n",
       "7   4.536710  \n",
       "8   4.536710  \n",
       "9   4.536710  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_test_avg.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>...</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>tot</th>\n",
       "      <th>l_casual</th>\n",
       "      <th>l_registered</th>\n",
       "      <th>l_tot</th>\n",
       "      <th>periods</th>\n",
       "      <th>avg_l_casual</th>\n",
       "      <th>avg_l_registered</th>\n",
       "      <th>avg_l_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.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.484907</td>\n",
       "      <td>2.484907</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.173287</td>\n",
       "      <td>1.784717</td>\n",
       "      <td>1.818100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>6.56</td>\n",
       "      <td>11.365</td>\n",
       "      <td>64.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.609438</td>\n",
       "      <td>1.609438</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.173287</td>\n",
       "      <td>1.784717</td>\n",
       "      <td>1.818100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>6.56</td>\n",
       "      <td>11.365</td>\n",
       "      <td>64.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.098612</td>\n",
       "      <td>1.098612</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>6.56</td>\n",
       "      <td>9.850</td>\n",
       "      <td>64.0</td>\n",
       "      <td>6.0032</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>5.74</td>\n",
       "      <td>9.090</td>\n",
       "      <td>69.0</td>\n",
       "      <td>6.0032</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.609438</td>\n",
       "      <td>1.609438</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>5.74</td>\n",
       "      <td>8.335</td>\n",
       "      <td>63.0</td>\n",
       "      <td>7.0015</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.610918</td>\n",
       "      <td>3.610918</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.777822</td>\n",
       "      <td>4.523827</td>\n",
       "      <td>4.536710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>6.56</td>\n",
       "      <td>11.365</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.564348</td>\n",
       "      <td>4.564348</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.777822</td>\n",
       "      <td>4.523827</td>\n",
       "      <td>4.536710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>6.56</td>\n",
       "      <td>11.365</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>216.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>1.386294</td>\n",
       "      <td>5.379897</td>\n",
       "      <td>5.393628</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.777822</td>\n",
       "      <td>4.523827</td>\n",
       "      <td>4.536710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>7.38</td>\n",
       "      <td>12.120</td>\n",
       "      <td>51.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>1.945910</td>\n",
       "      <td>4.762174</td>\n",
       "      <td>4.812184</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.777822</td>\n",
       "      <td>4.523827</td>\n",
       "      <td>4.536710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>8.20</td>\n",
       "      <td>12.880</td>\n",
       "      <td>47.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>1.386294</td>\n",
       "      <td>3.761200</td>\n",
       "      <td>3.828641</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.523590</td>\n",
       "      <td>3.959058</td>\n",
       "      <td>4.035925</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     temp   atemp  humidity  windspeed    year  season  month  weekday  hour  \\\n",
       "115  7.38  12.120      55.0     0.0000  2011.0     1.0    1.0      3.0   0.0   \n",
       "116  6.56  11.365      64.0     0.0000  2011.0     1.0    1.0      3.0   1.0   \n",
       "117  6.56  11.365      64.0     0.0000  2011.0     1.0    1.0      3.0   2.0   \n",
       "118  6.56   9.850      64.0     6.0032  2011.0     1.0    1.0      3.0   4.0   \n",
       "119  5.74   9.090      69.0     6.0032  2011.0     1.0    1.0      3.0   5.0   \n",
       "120  5.74   8.335      63.0     7.0015  2011.0     1.0    1.0      3.0   6.0   \n",
       "121  6.56  11.365      59.0     0.0000  2011.0     1.0    1.0      3.0   7.0   \n",
       "122  6.56  11.365      59.0     0.0000  2011.0     1.0    1.0      3.0   8.0   \n",
       "123  7.38  12.120      51.0     0.0000  2011.0     1.0    1.0      3.0   9.0   \n",
       "124  8.20  12.880      47.0     0.0000  2011.0     1.0    1.0      3.0  10.0   \n",
       "\n",
       "     workingday    ...      casual  registered    tot  l_casual  l_registered  \\\n",
       "115         1.0    ...         0.0        11.0   11.0  0.000000      2.484907   \n",
       "116         1.0    ...         0.0         4.0    4.0  0.000000      1.609438   \n",
       "117         1.0    ...         0.0         2.0    2.0  0.000000      1.098612   \n",
       "118         1.0    ...         0.0         1.0    1.0  0.000000      0.693147   \n",
       "119         1.0    ...         0.0         4.0    4.0  0.000000      1.609438   \n",
       "120         1.0    ...         0.0        36.0   36.0  0.000000      3.610918   \n",
       "121         1.0    ...         0.0        95.0   95.0  0.000000      4.564348   \n",
       "122         1.0    ...         3.0       216.0  219.0  1.386294      5.379897   \n",
       "123         1.0    ...         6.0       116.0  122.0  1.945910      4.762174   \n",
       "124         1.0    ...         3.0        42.0   45.0  1.386294      3.761200   \n",
       "\n",
       "        l_tot  periods  avg_l_casual  avg_l_registered  avg_l_tot  \n",
       "115  2.484907      6.0      0.173287          1.784717   1.818100  \n",
       "116  1.609438      6.0      0.173287          1.784717   1.818100  \n",
       "117  1.098612      1.0      0.000000          1.268834   1.268834  \n",
       "118  0.693147      1.0      0.000000          1.268834   1.268834  \n",
       "119  1.609438      1.0      0.000000          1.268834   1.268834  \n",
       "120  3.610918      2.0      0.777822          4.523827   4.536710  \n",
       "121  4.564348      2.0      0.777822          4.523827   4.536710  \n",
       "122  5.393628      2.0      0.777822          4.523827   4.536710  \n",
       "123  4.812184      2.0      0.777822          4.523827   4.536710  \n",
       "124  3.828641      3.0      1.523590          3.959058   4.035925  \n",
       "\n",
       "[10 rows x 22 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking the last three. Intetestingly, the below table has no value for hour == 3  \n",
    "\n",
    "data_train.loc[115:124]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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_l_casual</th>\n",
       "      <th>avg_l_registered</th>\n",
       "      <th>avg_l_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.0</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.173287</td>\n",
       "      <td>1.784717</td>\n",
       "      <td>1.818100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56.0</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.173287</td>\n",
       "      <td>1.784717</td>\n",
       "      <td>1.818100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.66</td>\n",
       "      <td>13.635</td>\n",
       "      <td>56.0</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.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56.0</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.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.66</td>\n",
       "      <td>12.880</td>\n",
       "      <td>56.0</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.000000</td>\n",
       "      <td>1.268834</td>\n",
       "      <td>1.268834</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.0    26.0027  2011       1      1        3     0   \n",
       "1  10.66  13.635      56.0     0.0000  2011       1      1        3     1   \n",
       "2  10.66  13.635      56.0     0.0000  2011       1      1        3     2   \n",
       "3  10.66  12.880      56.0    11.0014  2011       1      1        3     3   \n",
       "4  10.66  12.880      56.0    11.0014  2011       1      1        3     4   \n",
       "\n",
       "   workingday  holiday  weather  avg_l_casual  avg_l_registered  avg_l_tot  \n",
       "0           1        0        1      0.173287          1.784717   1.818100  \n",
       "1           1        0        1      0.173287          1.784717   1.818100  \n",
       "2           1        0        1      0.000000          1.268834   1.268834  \n",
       "3           1        0        1      0.000000          1.268834   1.268834  \n",
       "4           1        0        1      0.000000          1.268834   1.268834  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(data_test_avg[num_var+cat_var+avg_l_var].head())\n",
    "\n",
    "data_test_avg[num_var+cat_var+avg_l_var].to_csv('data/test_prep_orig_l_avg.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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
}