4b. Random Forest (specific days).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preamble"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Loading necessary libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Configure libraries\n",
    "\n",
    "%matplotlib inline\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "pd.set_option('display.width', 500)\n",
    "pd.set_option('display.max_columns', 100)\n",
    "pd.set_option('display.notebook_repr_html', True)\n",
    "from sklearn.grid_search import GridSearchCV\n",
    "from sklearn.cross_validation import train_test_split\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from sklearn.pipeline import Pipeline\n",
    "from sklearn.cross_validation import cross_val_score\n",
    "import geohash\n",
    "from sklearn.metrics import mean_squared_error\n",
    "import random\n",
    "import math\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Funtion for cross-validation over a grid of parameters\n",
    "\n",
    "def cv_optimize(clf, parameters, X, y, n_jobs=1, n_folds=5, score_func=None, verbose=0):\n",
    "    if score_func:\n",
    "        gs = GridSearchCV(clf, param_grid=parameters, cv=n_folds, n_jobs=n_jobs, scoring=score_func, verbose=verbose)\n",
    "    else:\n",
    "        gs = GridSearchCV(clf, param_grid=parameters, n_jobs=n_jobs, cv=n_folds, verbose=verbose)\n",
    "    gs.fit(X, y)\n",
    "    print \"BEST\", gs.best_params_, gs.best_score_, gs.grid_scores_, gs.scorer_\n",
    "    print \"Best score: \", gs.best_score_\n",
    "    best = gs.best_estimator_\n",
    "    return best"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Reading the taxi data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in the training and validation data. Training data is from January 2013 till February 2015. Validation data is from March and April 2015. This data was prepared on an Amazon EC2 Spark cluster, transferred to S3 and then downloaded. It was converted from an RDD to a csv using the [ConvertRDDtoCSV](https://github.com/sdaulton/TaxiPrediction/blob/master/3. ConvertRDDtoCSV.ipynb) notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(7238532, 14)\n"
     ]
    }
   ],
   "source": [
    "# Each line is of the format:\n",
    "# ((time_cat, time_num, time_cos, time_sin, day_cat, day_num, day_cos, day_sin, weekend, geohash), number of pickups)\n",
    "names = [\"year\",\"month\",\"day\",\"time_cat\", \"time_num\", \"time_cos\", \"time_sin\", \"day_cat\", \"day_num\", \"day_cos\", \"day_sin\", \"weekend\", \"geohash\", \"pickups\"]\n",
    "dftrain=pd.read_csv(\"./data/taxi_data_training.csv\", header=None, names = names)\n",
    "print dftrain.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1569528, 14)\n"
     ]
    }
   ],
   "source": [
    "dfvalid=pd.read_csv(\"./data/taxi_data_validation.csv\", header=None, names = names)\n",
    "print dfvalid.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading and preparing the weather data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we are reading the weather data pulled from NOAA for New York City Central Park. It contains among other things, daily numbers of precipitation, snowfall, snow depth, wind speed and minimum and maximum temperatures."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>precipitation</th>\n",
       "      <th>snow_depth</th>\n",
       "      <th>snowfall</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>min_temp</th>\n",
       "      <th>avg_wind</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-3.3</td>\n",
       "      <td>-9.4</td>\n",
       "      <td>18.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.1</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>10.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>-1.7</td>\n",
       "      <td>16.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-3.9</td>\n",
       "      <td>12.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6.1</td>\n",
       "      <td>3.3</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day  precipitation  snow_depth  snowfall  max_temp  min_temp  avg_wind\n",
       "0  2009      1    1              0           0         0      -3.3      -9.4     18.00\n",
       "1  2009      1    2              0           0         0       1.1      -5.0     10.08\n",
       "2  2009      1    3              0           0         0       3.3      -1.7     16.20\n",
       "3  2009      1    4              0           0         0       5.6      -3.9     12.24\n",
       "4  2009      1    5              0           0         0       6.1       3.3     11.16"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the weather data\n",
    "\n",
    "weather=pd.read_csv(\"./data/nyc-weather-data.csv\")\n",
    "weather.ix[weather.SNWD <= -9999, 'SNWD'] = 0\n",
    "weather.ix[weather.SNOW <= -9999, 'SNOW'] = 0\n",
    "weather.ix[weather.AWND <= -9999, 'AWND'] = 0\n",
    "weather['year'] = (weather['DATE']/10000).apply(math.floor)\n",
    "weather['month'] = ((weather['DATE'].mod(10000))/100).apply(math.floor)\n",
    "weather['day'] = weather['DATE'].mod(100)\n",
    "weather = weather[['year','month','day','PRCP','SNWD','SNOW','TMAX','TMIN','AWND']]\n",
    "weather['PRCP'] = weather['PRCP'] / 10.\n",
    "weather['TMAX'] = weather['TMAX'] / 10.\n",
    "weather['TMIN'] = weather['TMIN'] / 10.\n",
    "weather['AWND'] = weather['AWND'] / 10. * 3.6\n",
    "weather.columns = ['year','month','day','precipitation','snow_depth','snowfall','max_temp','min_temp','avg_wind']\n",
    "weather.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Final preperation for machine learning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I tried initially doing one-hot-encoding on the geohashes to generate numerical features, but I quickly realized that this was not feasible from a memory perspective. Millions of records times tens of thousands of features would not fit in memory. So I decided to go for the numerical latitude and longitude route. Using a random forest, we can easily detect higher order structures in these two variables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we define some functions to further prepare the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Get the number of the day in the year. So January 1 is 1, December 31st is 365 or 366\n",
    "def get_yearday(df):\n",
    "    date = datetime.date(df['year'],df['month'],df['day'])\n",
    "    return (date.timetuple().tm_yday-1)/365.\n",
    "\n",
    "# Get the longitude and latitude from the geohash\n",
    "def decodegeo(geo, which):\n",
    "    if len(geo) >= 6:\n",
    "        geodecoded = geohash.decode(geo)\n",
    "        return geodecoded[which]\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "# Join the weather data to the taxi data, add some more time and date features and\n",
    "# get the latitude and longitude of the center of the geohash where the records were bucketed in.\n",
    "def further_data_prep(df):\n",
    "    df = pd.merge(df, weather, how='left', on=['year','month','day'])\n",
    "    df['year_num'] = df.apply(lambda x:get_yearday(x),axis=1)\n",
    "    df['month_num'] = (df['day']-1)/30.\n",
    "    df['year_sin'] = (df['year_num'] * 2 * math.pi).apply(math.sin)\n",
    "    df['year_cos'] = (df['year_num'] * 2 * math.pi).apply(math.cos)\n",
    "    df['month_sin'] = (df['month_num'] * 2 * math.pi).apply(math.sin)\n",
    "    df['month_cos'] = (df['month_num'] * 2 * math.pi).apply(math.cos)\n",
    "    df['latitude'] = df['geohash'].apply(lambda geo: decodegeo(geo, 0))\n",
    "    df['longitude'] = df['geohash'].apply(lambda geo: decodegeo(geo, 1))\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Prepare the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 9min 16s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "dftrain = further_data_prep(dftrain)\n",
    "dfvalid = further_data_prep(dfvalid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>time_cat</th>\n",
       "      <th>time_num</th>\n",
       "      <th>time_cos</th>\n",
       "      <th>time_sin</th>\n",
       "      <th>day_cat</th>\n",
       "      <th>day_num</th>\n",
       "      <th>day_cos</th>\n",
       "      <th>day_sin</th>\n",
       "      <th>weekend</th>\n",
       "      <th>geohash</th>\n",
       "      <th>pickups</th>\n",
       "      <th>precipitation</th>\n",
       "      <th>snow_depth</th>\n",
       "      <th>snowfall</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>min_temp</th>\n",
       "      <th>avg_wind</th>\n",
       "      <th>year_num</th>\n",
       "      <th>month_num</th>\n",
       "      <th>year_sin</th>\n",
       "      <th>year_cos</th>\n",
       "      <th>month_sin</th>\n",
       "      <th>month_cos</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>14:30</td>\n",
       "      <td>0.614583</td>\n",
       "      <td>-0.751840</td>\n",
       "      <td>-0.659346</td>\n",
       "      <td>Friday</td>\n",
       "      <td>0.659226</td>\n",
       "      <td>-0.539926</td>\n",
       "      <td>-0.841713</td>\n",
       "      <td>0</td>\n",
       "      <td>dr5ru68</td>\n",
       "      <td>12</td>\n",
       "      <td>4.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13.3</td>\n",
       "      <td>3.9</td>\n",
       "      <td>7.56</td>\n",
       "      <td>0.271233</td>\n",
       "      <td>0.300000</td>\n",
       "      <td>0.991114</td>\n",
       "      <td>-0.133015</td>\n",
       "      <td>0.951057</td>\n",
       "      <td>-0.309017</td>\n",
       "      <td>40.751724</td>\n",
       "      <td>-73.992233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>19:00</td>\n",
       "      <td>0.802083</td>\n",
       "      <td>0.321439</td>\n",
       "      <td>-0.946930</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>0.828869</td>\n",
       "      <td>0.475515</td>\n",
       "      <td>-0.879708</td>\n",
       "      <td>1</td>\n",
       "      <td>dr5rs0z</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>460</td>\n",
       "      <td>0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>-7.7</td>\n",
       "      <td>8.28</td>\n",
       "      <td>0.178082</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.899631</td>\n",
       "      <td>0.436651</td>\n",
       "      <td>0.951057</td>\n",
       "      <td>0.309017</td>\n",
       "      <td>40.698166</td>\n",
       "      <td>-73.993607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>27</td>\n",
       "      <td>16:00</td>\n",
       "      <td>0.677083</td>\n",
       "      <td>-0.442289</td>\n",
       "      <td>-0.896873</td>\n",
       "      <td>Friday</td>\n",
       "      <td>0.668155</td>\n",
       "      <td>-0.491881</td>\n",
       "      <td>-0.870662</td>\n",
       "      <td>0</td>\n",
       "      <td>dr5rum1</td>\n",
       "      <td>4</td>\n",
       "      <td>6.9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.8</td>\n",
       "      <td>3.9</td>\n",
       "      <td>6.84</td>\n",
       "      <td>0.232877</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>0.994218</td>\n",
       "      <td>0.107381</td>\n",
       "      <td>-0.743145</td>\n",
       "      <td>0.669131</td>\n",
       "      <td>40.765457</td>\n",
       "      <td>-73.990860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>12:30</td>\n",
       "      <td>0.531250</td>\n",
       "      <td>-0.980785</td>\n",
       "      <td>-0.195090</td>\n",
       "      <td>Monday</td>\n",
       "      <td>0.075893</td>\n",
       "      <td>0.888446</td>\n",
       "      <td>0.458982</td>\n",
       "      <td>0</td>\n",
       "      <td>dr72mdp</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>-4.9</td>\n",
       "      <td>10.80</td>\n",
       "      <td>0.221918</td>\n",
       "      <td>0.733333</td>\n",
       "      <td>0.984474</td>\n",
       "      <td>0.175531</td>\n",
       "      <td>-0.994522</td>\n",
       "      <td>-0.104528</td>\n",
       "      <td>40.836868</td>\n",
       "      <td>-73.927689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>18:00</td>\n",
       "      <td>0.760417</td>\n",
       "      <td>0.065403</td>\n",
       "      <td>-0.997859</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0.251488</td>\n",
       "      <td>-0.009350</td>\n",
       "      <td>0.999956</td>\n",
       "      <td>0</td>\n",
       "      <td>dr5ru0g</td>\n",
       "      <td>3</td>\n",
       "      <td>11.7</td>\n",
       "      <td>280</td>\n",
       "      <td>0</td>\n",
       "      <td>11.7</td>\n",
       "      <td>3.9</td>\n",
       "      <td>5.76</td>\n",
       "      <td>0.186301</td>\n",
       "      <td>0.300000</td>\n",
       "      <td>0.920971</td>\n",
       "      <td>0.389630</td>\n",
       "      <td>0.951057</td>\n",
       "      <td>-0.309017</td>\n",
       "      <td>40.742111</td>\n",
       "      <td>-73.999100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day time_cat  time_num  time_cos  time_sin   day_cat   day_num   day_cos   day_sin  weekend  geohash  pickups  precipitation  snow_depth  snowfall  max_temp  min_temp  avg_wind  year_num  month_num  year_sin  year_cos  month_sin  month_cos   latitude  longitude\n",
       "0  2015      4   10    14:30  0.614583 -0.751840 -0.659346    Friday  0.659226 -0.539926 -0.841713        0  dr5ru68       12            4.3           0         0      13.3       3.9      7.56  0.271233   0.300000  0.991114 -0.133015   0.951057  -0.309017  40.751724 -73.992233\n",
       "1  2015      3    7    19:00  0.802083  0.321439 -0.946930  Saturday  0.828869  0.475515 -0.879708        1  dr5rs0z        3            0.0         460         0       3.3      -7.7      8.28  0.178082   0.200000  0.899631  0.436651   0.951057   0.309017  40.698166 -73.993607\n",
       "2  2015      3   27    16:00  0.677083 -0.442289 -0.896873    Friday  0.668155 -0.491881 -0.870662        0  dr5rum1        4            6.9           0         0       7.8       3.9      6.84  0.232877   0.866667  0.994218  0.107381  -0.743145   0.669131  40.765457 -73.990860\n",
       "3  2015      3   23    12:30  0.531250 -0.980785 -0.195090    Monday  0.075893  0.888446  0.458982        0  dr72mdp        1            0.0           0         0       3.3      -4.9     10.80  0.221918   0.733333  0.984474  0.175531  -0.994522  -0.104528  40.836868 -73.927689\n",
       "4  2015      3   10    18:00  0.760417  0.065403 -0.997859   Tuesday  0.251488 -0.009350  0.999956        0  dr5ru0g        3           11.7         280         0      11.7       3.9      5.76  0.186301   0.300000  0.920971  0.389630   0.951057  -0.309017  40.742111 -73.999100"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check results\n",
    "dfvalid.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Extract the features that will actually be used in the machine learning algorithm and define the target. The target in this case will be the logarithm of the number of pickups. Why the logarithm? Because if we would use the number of pickups as is, mistakes in absolute numbers would be treated equally among high traffic locations and low traffic locations. An error of 100 pickups in a location with usually about 1000 pickups would be treated equally as an error of 100 pickups in a location with usually about 10 pickups. That is not what we want. We also want some accuracy in the low traffic locations. Taking the logarithm allows us to do that. With a logarithmic target, errors are optimized in an order of magnitude scale, rather than an absolute scale. So the result will be that predictions are correct to such and such order of magnitude."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Split off the features\n",
    "# Xnames = ['month','day','latitude','longitude','year_num','year_cos','year_sin',\n",
    "#           'month_num','month_cos','month_sin','day_num','day_cos','day_sin',\n",
    "#           'time_num','time_cos','time_sin','weekend','precipitation',\n",
    "#           'snow_depth','snowfall','max_temp','min_temp','avg_wind']\n",
    "Xnames = ['month','day','latitude','longitude','year_num','year_cos','year_sin',\n",
    "          'month_cos','month_sin','day_num','day_cos','day_sin',\n",
    "          'time_num','time_cos','time_sin','max_temp','min_temp','avg_wind']\n",
    "Xtrain = dftrain[Xnames]\n",
    "Xvalid = dfvalid[Xnames]\n",
    "\n",
    "# Split off the target (which will be the logarithm of the number of pickups (+1))\n",
    "ytrain = np.log10(dftrain['pickups']+1)\n",
    "yvalid = np.log10(dfvalid['pickups']+1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get the training and test sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(7238532, 18)\n",
      "(2000000, 18)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>year_num</th>\n",
       "      <th>year_cos</th>\n",
       "      <th>year_sin</th>\n",
       "      <th>month_cos</th>\n",
       "      <th>month_sin</th>\n",
       "      <th>day_num</th>\n",
       "      <th>day_cos</th>\n",
       "      <th>day_sin</th>\n",
       "      <th>time_num</th>\n",
       "      <th>time_cos</th>\n",
       "      <th>time_sin</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>min_temp</th>\n",
       "      <th>avg_wind</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1627377</th>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>40.751724</td>\n",
       "      <td>-73.942795</td>\n",
       "      <td>0.827397</td>\n",
       "      <td>0.467359</td>\n",
       "      <td>-0.884068</td>\n",
       "      <td>0.978148</td>\n",
       "      <td>-0.207912</td>\n",
       "      <td>0.498512</td>\n",
       "      <td>-0.999956</td>\n",
       "      <td>0.009350</td>\n",
       "      <td>0.489583</td>\n",
       "      <td>-0.997859</td>\n",
       "      <td>0.065403</td>\n",
       "      <td>15.0</td>\n",
       "      <td>8.3</td>\n",
       "      <td>5.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3147822</th>\n",
       "      <td>5</td>\n",
       "      <td>26</td>\n",
       "      <td>40.762711</td>\n",
       "      <td>-74.000473</td>\n",
       "      <td>0.397260</td>\n",
       "      <td>-0.798779</td>\n",
       "      <td>0.601624</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>-0.866025</td>\n",
       "      <td>0.936012</td>\n",
       "      <td>0.920261</td>\n",
       "      <td>-0.391305</td>\n",
       "      <td>0.552083</td>\n",
       "      <td>-0.946930</td>\n",
       "      <td>-0.321439</td>\n",
       "      <td>18.9</td>\n",
       "      <td>8.9</td>\n",
       "      <td>13.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5528461</th>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "      <td>40.736618</td>\n",
       "      <td>-73.983994</td>\n",
       "      <td>0.616438</td>\n",
       "      <td>-0.744104</td>\n",
       "      <td>-0.668064</td>\n",
       "      <td>-0.913545</td>\n",
       "      <td>0.406737</td>\n",
       "      <td>0.299107</td>\n",
       "      <td>-0.303677</td>\n",
       "      <td>0.952775</td>\n",
       "      <td>0.093750</td>\n",
       "      <td>0.831470</td>\n",
       "      <td>0.555570</td>\n",
       "      <td>23.3</td>\n",
       "      <td>16.1</td>\n",
       "      <td>10.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>951888</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>40.731125</td>\n",
       "      <td>-73.926315</td>\n",
       "      <td>0.336986</td>\n",
       "      <td>-0.519744</td>\n",
       "      <td>0.854322</td>\n",
       "      <td>0.809017</td>\n",
       "      <td>0.587785</td>\n",
       "      <td>0.715774</td>\n",
       "      <td>-0.213396</td>\n",
       "      <td>-0.976966</td>\n",
       "      <td>0.010417</td>\n",
       "      <td>0.997859</td>\n",
       "      <td>0.065403</td>\n",
       "      <td>20.6</td>\n",
       "      <td>8.9</td>\n",
       "      <td>9.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6931874</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>40.747604</td>\n",
       "      <td>-73.973007</td>\n",
       "      <td>0.413699</td>\n",
       "      <td>-0.856551</td>\n",
       "      <td>0.516062</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.953869</td>\n",
       "      <td>0.958287</td>\n",
       "      <td>-0.285808</td>\n",
       "      <td>0.677083</td>\n",
       "      <td>-0.442289</td>\n",
       "      <td>-0.896873</td>\n",
       "      <td>25.0</td>\n",
       "      <td>12.8</td>\n",
       "      <td>5.76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         month  day   latitude  longitude  year_num  year_cos  year_sin  month_cos  month_sin   day_num   day_cos   day_sin  time_num  time_cos  time_sin  max_temp  min_temp  avg_wind\n",
       "1627377     10   30  40.751724 -73.942795  0.827397  0.467359 -0.884068   0.978148  -0.207912  0.498512 -0.999956  0.009350  0.489583 -0.997859  0.065403      15.0       8.3      5.76\n",
       "3147822      5   26  40.762711 -74.000473  0.397260 -0.798779  0.601624   0.500000  -0.866025  0.936012  0.920261 -0.391305  0.552083 -0.946930 -0.321439      18.9       8.9     13.68\n",
       "5528461      8   14  40.736618 -73.983994  0.616438 -0.744104 -0.668064  -0.913545   0.406737  0.299107 -0.303677  0.952775  0.093750  0.831470  0.555570      23.3      16.1     10.44\n",
       "951888       5    4  40.731125 -73.926315  0.336986 -0.519744  0.854322   0.809017   0.587785  0.715774 -0.213396 -0.976966  0.010417  0.997859  0.065403      20.6       8.9      9.36\n",
       "6931874      6    1  40.747604 -73.973007  0.413699 -0.856551  0.516062   1.000000   0.000000  0.953869  0.958287 -0.285808  0.677083 -0.442289 -0.896873      25.0      12.8      5.76"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print Xtrain.shape\n",
    "max_samples = 2000000\n",
    "if Xtrain.shape[0] > max_samples:\n",
    "    rows = random.sample(Xtrain.index, max_samples)\n",
    "    Xtrain = Xtrain.ix[rows]\n",
    "    ytrain = ytrain.ix[rows]\n",
    "print Xtrain.shape\n",
    "Xtrain.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Random Forest Regression\n",
    "\n",
    "The best algorithm that we tried on this problem was a Random Forest regressor. Using deep trees, a random forest is able to really get at the detailed structure of the longitude and latitude features to really use specific regions of NYC to make its predictions. We're evaluating the random forest on root-mean-squared-error (RMSE) of the prediction (in log-space of number of pickups)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Create a Random Forest Regression estimator\n",
    "reg = RandomForestRegressor(n_estimators=20, n_jobs=-1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we did our first tests with this dataset, we used the `cv_optimize` function (see the cell below) to try out several configurations of parameters using cross validation to get a feeling for the influence on accuracy (model quality) and performance (runtime). We concluded a few things from this:\n",
    "\n",
    "1. It quickly became apparent that more than about 2 million records lead to very long runtimes.\n",
    "2. Trees deeper than 20 were not adding much quality.\n",
    "3. The more trees we created, the more accurate the model, but also the longer the runtime.\n",
    "4. Using all features (\"auto\") was better than using only sqrt(n) features or log2(n) features\n",
    "\n",
    "For this version of the notebook, we went with 50 trees of depth 20, using a sample of 2,000,000 data points."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "source": [
    "%%time\n",
    "# Define a grid of parameters over which to optimize the random forest\n",
    "\n",
    "# DO NOT RUN THIS !!!! YOU WILL BE WAITING A LOOOOOOOOOOOOONG TIME!\n",
    "# The code underneath is merely to show what the range of parameters was that we tested.\n",
    "parameters = {\"n_estimators\": [1,2,5,10,20,50,100,200],\n",
    "              \"max_features\": [\"auto\",\"sqrt\",\"log2\"]\n",
    "              \"max_depth\": [1,2,5,10,20,50,100]}\n",
    "best = cv_optimize(reg, parameters, Xtrain, ytrain, n_folds=5, score_func='mean_squared_error', verbose=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The code below can be used to train the model using 50 trees of depth 20. After training each tree, the RMSE on the training and validation sets is calculated to visualize the progress we're making in model quality."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "N = 1, R^2 (train) = 0.788, R^2 (valid) = 0.459, RMSE (train) = 0.158, RMSE (valid) = 0.243\n",
      "N = 2, R^2 (train) = 0.870, R^2 (valid) = 0.597, RMSE (train) = 0.124, RMSE (valid) = 0.210\n",
      "N = 3, R^2 (train) = 0.897, R^2 (valid) = 0.641, RMSE (train) = 0.110, RMSE (valid) = 0.198\n",
      "N = 4, R^2 (train) = 0.911, R^2 (valid) = 0.664, RMSE (train) = 0.102, RMSE (valid) = 0.192\n",
      "N = 5, R^2 (train) = 0.919, R^2 (valid) = 0.678, RMSE (train) = 0.097, RMSE (valid) = 0.188\n",
      "N = 6, R^2 (train) = 0.925, R^2 (valid) = 0.687, RMSE (train) = 0.094, RMSE (valid) = 0.185\n",
      "N = 7, R^2 (train) = 0.929, R^2 (valid) = 0.693, RMSE (train) = 0.092, RMSE (valid) = 0.184\n",
      "N = 8, R^2 (train) = 0.931, R^2 (valid) = 0.698, RMSE (train) = 0.090, RMSE (valid) = 0.182\n",
      "N = 9, R^2 (train) = 0.933, R^2 (valid) = 0.702, RMSE (train) = 0.089, RMSE (valid) = 0.181\n",
      "N = 10, R^2 (train) = 0.935, R^2 (valid) = 0.705, RMSE (train) = 0.087, RMSE (valid) = 0.180\n",
      "N = 11, R^2 (train) = 0.936, R^2 (valid) = 0.707, RMSE (train) = 0.087, RMSE (valid) = 0.179\n",
      "N = 12, R^2 (train) = 0.937, R^2 (valid) = 0.709, RMSE (train) = 0.086, RMSE (valid) = 0.179\n",
      "N = 13, R^2 (train) = 0.939, R^2 (valid) = 0.711, RMSE (train) = 0.085, RMSE (valid) = 0.178\n",
      "N = 14, R^2 (train) = 0.939, R^2 (valid) = 0.713, RMSE (train) = 0.084, RMSE (valid) = 0.177\n",
      "N = 15, R^2 (train) = 0.940, R^2 (valid) = 0.714, RMSE (train) = 0.084, RMSE (valid) = 0.177\n",
      "N = 16, R^2 (train) = 0.941, R^2 (valid) = 0.715, RMSE (train) = 0.083, RMSE (valid) = 0.177\n",
      "N = 17, R^2 (train) = 0.942, R^2 (valid) = 0.716, RMSE (train) = 0.083, RMSE (valid) = 0.176\n",
      "N = 18, R^2 (train) = 0.942, R^2 (valid) = 0.717, RMSE (train) = 0.083, RMSE (valid) = 0.176\n",
      "N = 19, R^2 (train) = 0.942, R^2 (valid) = 0.717, RMSE (train) = 0.082, RMSE (valid) = 0.176\n",
      "N = 20, R^2 (train) = 0.943, R^2 (valid) = 0.718, RMSE (train) = 0.082, RMSE (valid) = 0.176\n",
      "N = 21, R^2 (train) = 0.943, R^2 (valid) = 0.719, RMSE (train) = 0.082, RMSE (valid) = 0.176\n",
      "N = 22, R^2 (train) = 0.944, R^2 (valid) = 0.720, RMSE (train) = 0.081, RMSE (valid) = 0.175\n",
      "N = 23, R^2 (train) = 0.944, R^2 (valid) = 0.720, RMSE (train) = 0.081, RMSE (valid) = 0.175\n",
      "N = 24, R^2 (train) = 0.944, R^2 (valid) = 0.721, RMSE (train) = 0.081, RMSE (valid) = 0.175\n",
      "N = 25, R^2 (train) = 0.945, R^2 (valid) = 0.721, RMSE (train) = 0.081, RMSE (valid) = 0.175\n",
      "N = 26, R^2 (train) = 0.945, R^2 (valid) = 0.721, RMSE (train) = 0.081, RMSE (valid) = 0.175\n",
      "N = 27, R^2 (train) = 0.945, R^2 (valid) = 0.722, RMSE (train) = 0.080, RMSE (valid) = 0.175\n",
      "N = 28, R^2 (train) = 0.945, R^2 (valid) = 0.722, RMSE (train) = 0.080, RMSE (valid) = 0.174\n",
      "N = 29, R^2 (train) = 0.946, R^2 (valid) = 0.722, RMSE (train) = 0.080, RMSE (valid) = 0.174\n",
      "N = 30, R^2 (train) = 0.946, R^2 (valid) = 0.723, RMSE (train) = 0.080, RMSE (valid) = 0.174\n",
      "N = 31, R^2 (train) = 0.946, R^2 (valid) = 0.723, RMSE (train) = 0.080, RMSE (valid) = 0.174\n",
      "N = 32, R^2 (train) = 0.946, R^2 (valid) = 0.723, RMSE (train) = 0.080, RMSE (valid) = 0.174\n",
      "N = 33, R^2 (train) = 0.946, R^2 (valid) = 0.723, RMSE (train) = 0.080, RMSE (valid) = 0.174\n",
      "N = 34, R^2 (train) = 0.946, R^2 (valid) = 0.724, RMSE (train) = 0.079, RMSE (valid) = 0.174\n",
      "N = 35, R^2 (train) = 0.946, R^2 (valid) = 0.724, RMSE (train) = 0.079, RMSE (valid) = 0.174\n",
      "N = 36, R^2 (train) = 0.947, R^2 (valid) = 0.724, RMSE (train) = 0.079, RMSE (valid) = 0.174\n",
      "N = 37, R^2 (train) = 0.947, R^2 (valid) = 0.724, RMSE (train) = 0.079, RMSE (valid) = 0.174\n",
      "N = 38, R^2 (train) = 0.947, R^2 (valid) = 0.724, RMSE (train) = 0.079, RMSE (valid) = 0.174\n",
      "N = 39, R^2 (train) = 0.947, R^2 (valid) = 0.725, RMSE (train) = 0.079, RMSE (valid) = 0.174\n",
      "N = 40, R^2 (train) = 0.947, R^2 (valid) = 0.725, RMSE (train) = 0.079, RMSE (valid) = 0.174\n",
      "N = 41, R^2 (train) = 0.947, R^2 (valid) = 0.725, RMSE (train) = 0.079, RMSE (valid) = 0.174\n",
      "N = 42, R^2 (train) = 0.947, R^2 (valid) = 0.725, RMSE (train) = 0.079, RMSE (valid) = 0.174\n",
      "N = 43, R^2 (train) = 0.947, R^2 (valid) = 0.725, RMSE (train) = 0.079, RMSE (valid) = 0.173\n",
      "N = 44, R^2 (train) = 0.947, R^2 (valid) = 0.725, RMSE (train) = 0.079, RMSE (valid) = 0.173\n",
      "N = 45, R^2 (train) = 0.948, R^2 (valid) = 0.725, RMSE (train) = 0.079, RMSE (valid) = 0.173\n",
      "N = 46, R^2 (train) = 0.948, R^2 (valid) = 0.726, RMSE (train) = 0.079, RMSE (valid) = 0.173\n",
      "N = 47, R^2 (train) = 0.948, R^2 (valid) = 0.726, RMSE (train) = 0.079, RMSE (valid) = 0.173\n",
      "N = 48, R^2 (train) = 0.948, R^2 (valid) = 0.726, RMSE (train) = 0.078, RMSE (valid) = 0.173\n",
      "N = 49, R^2 (train) = 0.948, R^2 (valid) = 0.726, RMSE (train) = 0.078, RMSE (valid) = 0.173\n",
      "N = 50, R^2 (train) = 0.948, R^2 (valid) = 0.726, RMSE (train) = 0.078, RMSE (valid) = 0.173\n"
     ]
    }
   ],
   "source": [
    "reg = RandomForestRegressor(n_estimators=1, max_depth=30, n_jobs=-1, warm_start=True)\n",
    "for n in range(1,51):\n",
    "    reg.set_params(n_estimators=n)\n",
    "    reg.fit(Xtrain,ytrain)\n",
    "    training_accuracy = reg.score(Xtrain, ytrain)\n",
    "    valid_accuracy = reg.score(Xvalid, yvalid)\n",
    "    rmsetrain = np.sqrt(mean_squared_error(reg.predict(Xtrain),ytrain))\n",
    "    rmsevalid = np.sqrt(mean_squared_error(reg.predict(Xvalid),yvalid))\n",
    "    print \"N = %d, R^2 (train) = %0.3f, R^2 (valid) = %0.3f, RMSE (train) = %0.3f, RMSE (valid) = %0.3f\" % (n,training_accuracy, valid_accuracy, rmsetrain, rmsevalid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Evaluate the results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sanity check on some records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>13</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0   1\n",
       "0    7  12\n",
       "1    2   3\n",
       "2    5   4\n",
       "3    1   1\n",
       "4    2   3\n",
       "5    1   1\n",
       "6    1   1\n",
       "7    1   1\n",
       "8    4   3\n",
       "9    9  15\n",
       "10   2   1\n",
       "11   1   1\n",
       "12   6   5\n",
       "13   3   1\n",
       "14   1   1\n",
       "15   2   3\n",
       "16   2   4\n",
       "17  14  11\n",
       "18  13  18\n",
       "19   2   2"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show some of the predictions vs. the real number of pickups\n",
    "# predictions vs. real number of pickups\n",
    "pd.DataFrame(np.round(np.power(10,np.column_stack((reg.predict(Xvalid),yvalid))) - 1,decimals=0).astype(int)).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "RMSE = 0.173 (this is in log-space!)\n",
      "So two thirds of the records would be a factor of less than 1.49 away from the real value.\n"
     ]
    }
   ],
   "source": [
    "# Calculate the Root Mean Squared Error\n",
    "rmse = np.sqrt(mean_squared_error(reg.predict(Xvalid),yvalid))\n",
    "print \"RMSE = %0.3f (this is in log-space!)\" % rmse\n",
    "print \"So two thirds of the records would be a factor of less than %0.2f away from the real value.\" % np.power(10,rmse)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('longitude', 0.34072788876115723),\n",
       " ('latitude', 0.31287612120249852),\n",
       " ('time_num', 0.064314704030115605),\n",
       " ('time_cos', 0.026450957375376904),\n",
       " ('day_sin', 0.026326791200598051),\n",
       " ('day_num', 0.02421424852325817),\n",
       " ('avg_wind', 0.02320447792407548),\n",
       " ('year_sin', 0.022884790117684389),\n",
       " ('year_cos', 0.020631876770959508),\n",
       " ('time_sin', 0.020272077215941903),\n",
       " ('min_temp', 0.018447569373706708),\n",
       " ('max_temp', 0.017951183437801083),\n",
       " ('day_cos', 0.017214112320699134),\n",
       " ('year_num', 0.01711362151617072),\n",
       " ('day', 0.015338314285112615),\n",
       " ('month_cos', 0.014479578898743883),\n",
       " ('month_sin', 0.014306560224670604),\n",
       " ('month', 0.003245126821429412)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What are the most important features?\n",
    "import operator\n",
    "dict_feat_imp = dict(zip(list(Xtrain.columns.values),reg.feature_importances_))\n",
    "sorted_features = sorted(dict_feat_imp.items(), key=operator.itemgetter(1), reverse=True)\n",
    "sorted_features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Latitude and longitude are obviously the most important factors influencing the number of pickups. After that come time of the day and day of the week (in several incarnations). The average wind speed is the most important weather factor, corresponding nicely to a [2014 Taxicab Fact book](http://www.nyc.gov/html/tlc/downloads/pdf/2014_taxicab_fact_book.pdf) from TLC stating that a blizzard, a hurricane and a superstorm were the most important weather events influencing taxi rides. After wind speed comes the first seasonal variable."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create predictions for visualization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we are going to generate predictions that we can visualize in Tableau. We do this by generating all possible combinations of time and location so that we have a well filled space of predictions. Then we generate predictions for all these combinations and then export to .csv."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load the test set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(6306640, 14)\n"
     ]
    }
   ],
   "source": [
    "dftest=pd.read_csv(\"./data/taxi_data_test.csv\", header=None, names = names)\n",
    "print dftest.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Prepare the data in the same way as the training and validation sets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 6min 33s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "dftest = further_data_prep(dftest)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>time_cat</th>\n",
       "      <th>time_num</th>\n",
       "      <th>time_cos</th>\n",
       "      <th>time_sin</th>\n",
       "      <th>day_cat</th>\n",
       "      <th>day_num</th>\n",
       "      <th>day_cos</th>\n",
       "      <th>day_sin</th>\n",
       "      <th>weekend</th>\n",
       "      <th>geohash</th>\n",
       "      <th>pickups</th>\n",
       "      <th>precipitation</th>\n",
       "      <th>snow_depth</th>\n",
       "      <th>snowfall</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>min_temp</th>\n",
       "      <th>avg_wind</th>\n",
       "      <th>year_num</th>\n",
       "      <th>month_num</th>\n",
       "      <th>year_sin</th>\n",
       "      <th>year_cos</th>\n",
       "      <th>month_sin</th>\n",
       "      <th>month_cos</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>07:30</td>\n",
       "      <td>0.322917</td>\n",
       "      <td>-0.442289</td>\n",
       "      <td>0.896873</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>0.331845</td>\n",
       "      <td>-0.491881</td>\n",
       "      <td>0.870662</td>\n",
       "      <td>0</td>\n",
       "      <td>dr5rtjx</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27.8</td>\n",
       "      <td>18.3</td>\n",
       "      <td>5.04</td>\n",
       "      <td>0.438356</td>\n",
       "      <td>0.300000</td>\n",
       "      <td>0.377708</td>\n",
       "      <td>-0.925925</td>\n",
       "      <td>0.951057</td>\n",
       "      <td>-0.309017</td>\n",
       "      <td>40.724258</td>\n",
       "      <td>-73.949661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>20:00</td>\n",
       "      <td>0.843750</td>\n",
       "      <td>0.555570</td>\n",
       "      <td>-0.831470</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>0.977679</td>\n",
       "      <td>0.990181</td>\n",
       "      <td>-0.139790</td>\n",
       "      <td>1</td>\n",
       "      <td>dr5ru3e</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>26.7</td>\n",
       "      <td>10.6</td>\n",
       "      <td>3.96</td>\n",
       "      <td>0.334247</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>0.863142</td>\n",
       "      <td>-0.504961</td>\n",
       "      <td>0.406737</td>\n",
       "      <td>0.913545</td>\n",
       "      <td>40.746231</td>\n",
       "      <td>-73.988113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>04:30</td>\n",
       "      <td>0.197917</td>\n",
       "      <td>0.321439</td>\n",
       "      <td>0.946930</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>0.313988</td>\n",
       "      <td>-0.391305</td>\n",
       "      <td>0.920261</td>\n",
       "      <td>0</td>\n",
       "      <td>dr5ru96</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27.8</td>\n",
       "      <td>18.3</td>\n",
       "      <td>5.04</td>\n",
       "      <td>0.438356</td>\n",
       "      <td>0.300000</td>\n",
       "      <td>0.377708</td>\n",
       "      <td>-0.925925</td>\n",
       "      <td>0.951057</td>\n",
       "      <td>-0.309017</td>\n",
       "      <td>40.744858</td>\n",
       "      <td>-73.978500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>00:00</td>\n",
       "      <td>0.010417</td>\n",
       "      <td>0.997859</td>\n",
       "      <td>0.065403</td>\n",
       "      <td>Friday</td>\n",
       "      <td>0.572917</td>\n",
       "      <td>-0.896873</td>\n",
       "      <td>-0.442289</td>\n",
       "      <td>0</td>\n",
       "      <td>dr5rsmc</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>31.1</td>\n",
       "      <td>22.8</td>\n",
       "      <td>7.20</td>\n",
       "      <td>0.443836</td>\n",
       "      <td>0.366667</td>\n",
       "      <td>0.345612</td>\n",
       "      <td>-0.938377</td>\n",
       "      <td>0.743145</td>\n",
       "      <td>-0.669131</td>\n",
       "      <td>40.725632</td>\n",
       "      <td>-73.990860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>14:30</td>\n",
       "      <td>0.614583</td>\n",
       "      <td>-0.751840</td>\n",
       "      <td>-0.659346</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>0.944940</td>\n",
       "      <td>0.940754</td>\n",
       "      <td>-0.339090</td>\n",
       "      <td>1</td>\n",
       "      <td>dr5rsr8</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28.3</td>\n",
       "      <td>16.1</td>\n",
       "      <td>5.76</td>\n",
       "      <td>0.353425</td>\n",
       "      <td>0.300000</td>\n",
       "      <td>0.796183</td>\n",
       "      <td>-0.605056</td>\n",
       "      <td>0.951057</td>\n",
       "      <td>-0.309017</td>\n",
       "      <td>40.735245</td>\n",
       "      <td>-73.992233</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day time_cat  time_num  time_cos  time_sin    day_cat   day_num   day_cos   day_sin  weekend  geohash  pickups  precipitation  snow_depth  snowfall  max_temp  min_temp  avg_wind  year_num  month_num  year_sin  year_cos  month_sin  month_cos   latitude  longitude\n",
       "0  2015      6   10    07:30  0.322917 -0.442289  0.896873  Wednesday  0.331845 -0.491881  0.870662        0  dr5rtjx        1              0           0         0      27.8      18.3      5.04  0.438356   0.300000  0.377708 -0.925925   0.951057  -0.309017  40.724258 -73.949661\n",
       "1  2015      5    3    20:00  0.843750  0.555570 -0.831470     Sunday  0.977679  0.990181 -0.139790        1  dr5ru3e        5              0           0         0      26.7      10.6      3.96  0.334247   0.066667  0.863142 -0.504961   0.406737   0.913545  40.746231 -73.988113\n",
       "2  2015      6   10    04:30  0.197917  0.321439  0.946930  Wednesday  0.313988 -0.391305  0.920261        0  dr5ru96        3              0           0         0      27.8      18.3      5.04  0.438356   0.300000  0.377708 -0.925925   0.951057  -0.309017  40.744858 -73.978500\n",
       "3  2015      6   12    00:00  0.010417  0.997859  0.065403     Friday  0.572917 -0.896873 -0.442289        0  dr5rsmc        8              0           0         0      31.1      22.8      7.20  0.443836   0.366667  0.345612 -0.938377   0.743145  -0.669131  40.725632 -73.990860\n",
       "4  2015      5   10    14:30  0.614583 -0.751840 -0.659346     Sunday  0.944940  0.940754 -0.339090        1  dr5rsr8        7              0           0         0      28.3      16.1      5.76  0.353425   0.300000  0.796183 -0.605056   0.951057  -0.309017  40.735245 -73.992233"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dftest.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create an artificial dataset\n",
    "This dataset will contain all possible combinations of time and location to predict for on May 1st, 2015, the day we want to visualize."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(48, 21)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>time_num</th>\n",
       "      <th>time_cos</th>\n",
       "      <th>time_sin</th>\n",
       "      <th>day_num</th>\n",
       "      <th>day_cos</th>\n",
       "      <th>day_sin</th>\n",
       "      <th>weekend</th>\n",
       "      <th>precipitation</th>\n",
       "      <th>snow_depth</th>\n",
       "      <th>snowfall</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>min_temp</th>\n",
       "      <th>avg_wind</th>\n",
       "      <th>year_num</th>\n",
       "      <th>month_num</th>\n",
       "      <th>year_sin</th>\n",
       "      <th>year_cos</th>\n",
       "      <th>month_sin</th>\n",
       "      <th>month_cos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.968750</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.195090</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>0</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.406250</td>\n",
       "      <td>-0.831470</td>\n",
       "      <td>0.555570</td>\n",
       "      <td>0.629464</td>\n",
       "      <td>-0.686997</td>\n",
       "      <td>-0.726660</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>0</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.281250</td>\n",
       "      <td>-0.195090</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>0.611607</td>\n",
       "      <td>-0.764037</td>\n",
       "      <td>-0.645172</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>0</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.427083</td>\n",
       "      <td>-0.896873</td>\n",
       "      <td>0.442289</td>\n",
       "      <td>0.632440</td>\n",
       "      <td>-0.673289</td>\n",
       "      <td>-0.739379</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>0</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.510417</td>\n",
       "      <td>-0.997859</td>\n",
       "      <td>-0.065403</td>\n",
       "      <td>0.644345</td>\n",
       "      <td>-0.616153</td>\n",
       "      <td>-0.787627</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>0</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     month  day  time_num  time_cos  time_sin   day_num   day_cos   day_sin  weekend  precipitation  snow_depth  snowfall  max_temp  min_temp  avg_wind  year_num  month_num  year_sin  year_cos  month_sin  month_cos\n",
       "65       5    1  0.968750  0.980785 -0.195090  0.709821 -0.249776 -0.968304        0              0           0         0      17.2       9.4      8.28  0.328767          0  0.880012 -0.474951          0          1\n",
       "86       5    1  0.406250 -0.831470  0.555570  0.629464 -0.686997 -0.726660        0              0           0         0      17.2       9.4      8.28  0.328767          0  0.880012 -0.474951          0          1\n",
       "206      5    1  0.281250 -0.195090  0.980785  0.611607 -0.764037 -0.645172        0              0           0         0      17.2       9.4      8.28  0.328767          0  0.880012 -0.474951          0          1\n",
       "252      5    1  0.427083 -0.896873  0.442289  0.632440 -0.673289 -0.739379        0              0           0         0      17.2       9.4      8.28  0.328767          0  0.880012 -0.474951          0          1\n",
       "324      5    1  0.510417 -0.997859 -0.065403  0.644345 -0.616153 -0.787627        0              0           0         0      17.2       9.4      8.28  0.328767          0  0.880012 -0.474951          0          1"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get all the different times with its corresponding weather data on May 1st, 2015\n",
    "\n",
    "# Get the May 1, 2015 data\n",
    "time_data = dftest[(dftest['month'] == 5) & (dftest['day'] == 1)]\n",
    "\n",
    "# Drop the features we don't need and drop duplicates to get unique values.\n",
    "time_data = time_data.drop(['geohash','pickups','year','time_cat','day_cat','latitude','longitude'], axis=1).drop_duplicates()\n",
    "print time_data.shape\n",
    "time_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(22542, 2)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1627377</th>\n",
       "      <td>40.751724</td>\n",
       "      <td>-73.942795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3147822</th>\n",
       "      <td>40.762711</td>\n",
       "      <td>-74.000473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5528461</th>\n",
       "      <td>40.736618</td>\n",
       "      <td>-73.983994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>951888</th>\n",
       "      <td>40.731125</td>\n",
       "      <td>-73.926315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6931874</th>\n",
       "      <td>40.747604</td>\n",
       "      <td>-73.973007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          latitude  longitude\n",
       "1627377  40.751724 -73.942795\n",
       "3147822  40.762711 -74.000473\n",
       "5528461  40.736618 -73.983994\n",
       "951888   40.731125 -73.926315\n",
       "6931874  40.747604 -73.973007"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get all the different locations\n",
    "\n",
    "# First figure out which columns to drop\n",
    "time_cols = list(Xtrain.columns.values)\n",
    "time_cols.remove('latitude')\n",
    "time_cols.remove('longitude')\n",
    "\n",
    "# Then get the unique locations\n",
    "loc_data = Xtrain.drop(time_cols, axis=1).drop_duplicates()\n",
    "\n",
    "# To reduce memory consumption in Tableau, we are only predicting for\n",
    "# the region closely around Manhattan and the La Guardia and JFK airports\n",
    "loc_data = loc_data[(loc_data['latitude'] > 40.5) & (loc_data['latitude'] < 41.1) &\n",
    "                    (loc_data['longitude'] > -74.1) & (loc_data['longitude'] < -73.6)]\n",
    "\n",
    "print loc_data.shape\n",
    "loc_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1082016\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>year_num</th>\n",
       "      <th>year_cos</th>\n",
       "      <th>year_sin</th>\n",
       "      <th>month_cos</th>\n",
       "      <th>month_sin</th>\n",
       "      <th>day_num</th>\n",
       "      <th>day_cos</th>\n",
       "      <th>day_sin</th>\n",
       "      <th>time_num</th>\n",
       "      <th>time_cos</th>\n",
       "      <th>time_sin</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>min_temp</th>\n",
       "      <th>avg_wind</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.751724</td>\n",
       "      <td>-73.942795</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.762711</td>\n",
       "      <td>-74.000473</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.736618</td>\n",
       "      <td>-73.983994</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.731125</td>\n",
       "      <td>-73.926315</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.747604</td>\n",
       "      <td>-73.973007</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   month  day   latitude  longitude  year_num  year_cos  year_sin  month_cos  month_sin   day_num   day_cos   day_sin  time_num  time_cos  time_sin  max_temp  min_temp  avg_wind\n",
       "0      5    1  40.751724 -73.942795  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28\n",
       "1      5    1  40.762711 -74.000473  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28\n",
       "2      5    1  40.736618 -73.983994  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28\n",
       "3      5    1  40.731125 -73.926315  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28\n",
       "4      5    1  40.747604 -73.973007  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dummy column to be able to join the times with the locations\n",
    "time_data['key'] = 1\n",
    "loc_data['key'] = 1\n",
    "\n",
    "# Merge the time_data and location_data\n",
    "result = pd.merge(time_data, loc_data, on='key').drop(['key'], axis=1)\n",
    "#result = result[Xtrain.columns.values]\n",
    "print result.shape[0]\n",
    "result = result[Xnames]\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Then we do the prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>month</th>\n",
       "      <th>day</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>year_num</th>\n",
       "      <th>year_cos</th>\n",
       "      <th>year_sin</th>\n",
       "      <th>month_cos</th>\n",
       "      <th>month_sin</th>\n",
       "      <th>day_num</th>\n",
       "      <th>day_cos</th>\n",
       "      <th>day_sin</th>\n",
       "      <th>time_num</th>\n",
       "      <th>time_cos</th>\n",
       "      <th>time_sin</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>min_temp</th>\n",
       "      <th>avg_wind</th>\n",
       "      <th>pred_pickups</th>\n",
       "      <th>pickups</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.751724</td>\n",
       "      <td>-73.942795</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>1.396741</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.762711</td>\n",
       "      <td>-74.000473</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>1.642030</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.736618</td>\n",
       "      <td>-73.983994</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>7.021172</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.731125</td>\n",
       "      <td>-73.926315</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>1.368385</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.747604</td>\n",
       "      <td>-73.973007</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>11.825699</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.727005</td>\n",
       "      <td>-73.990860</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>13.534925</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.725632</td>\n",
       "      <td>-73.996353</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>14.008216</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.677567</td>\n",
       "      <td>-73.973007</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>5.666272</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.720139</td>\n",
       "      <td>-74.008713</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>15.291712</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.718765</td>\n",
       "      <td>-73.953781</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>1.369543</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   month  day   latitude  longitude  year_num  year_cos  year_sin  month_cos  month_sin   day_num   day_cos   day_sin  time_num  time_cos  time_sin  max_temp  min_temp  avg_wind  pred_pickups  pickups\n",
       "0      5    1  40.751724 -73.942795  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28      1.396741      NaN\n",
       "1      5    1  40.762711 -74.000473  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28      1.642030        8\n",
       "2      5    1  40.736618 -73.983994  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28      7.021172        8\n",
       "3      5    1  40.731125 -73.926315  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28      1.368385      NaN\n",
       "4      5    1  40.747604 -73.973007  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28     11.825699       16\n",
       "5      5    1  40.727005 -73.990860  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28     13.534925       10\n",
       "6      5    1  40.725632 -73.996353  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28     14.008216       12\n",
       "7      5    1  40.677567 -73.973007  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28      5.666272       14\n",
       "8      5    1  40.720139 -74.008713  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28     15.291712       15\n",
       "9      5    1  40.718765 -73.953781  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28      1.369543        1"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the real number of pickups and take care that we can merge it with the predictions,\n",
    "yy = dftest[['month','day','day_num','latitude','longitude','pickups']]\n",
    "yy = yy[(yy['month'] == 5) & (yy['day'] == 1)]\n",
    "\n",
    "# Do predictions and convert the logarithm to the normal numbers\n",
    "result['pred_pickups'] = np.power(10,reg.predict(result)) - 1\n",
    "\n",
    "# Merge the predictions and the real pickups\n",
    "result = pd.merge(result, yy, how='left', on=['month','day','day_num','latitude','longitude'])\n",
    "result.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1082016\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>year_num</th>\n",
       "      <th>year_cos</th>\n",
       "      <th>year_sin</th>\n",
       "      <th>month_cos</th>\n",
       "      <th>month_sin</th>\n",
       "      <th>day_num</th>\n",
       "      <th>day_cos</th>\n",
       "      <th>day_sin</th>\n",
       "      <th>time_num</th>\n",
       "      <th>time_cos</th>\n",
       "      <th>time_sin</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>min_temp</th>\n",
       "      <th>avg_wind</th>\n",
       "      <th>pred_pickups</th>\n",
       "      <th>pickups</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.751724</td>\n",
       "      <td>-73.942795</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>1.396741</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.762711</td>\n",
       "      <td>-74.000473</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>1.642030</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.736618</td>\n",
       "      <td>-73.983994</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>7.021172</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.731125</td>\n",
       "      <td>-73.926315</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>1.368385</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>40.747604</td>\n",
       "      <td>-73.973007</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-0.474951</td>\n",
       "      <td>0.880012</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.709821</td>\n",
       "      <td>-0.249776</td>\n",
       "      <td>-0.968304</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>0.980785</td>\n",
       "      <td>-0.19509</td>\n",
       "      <td>17.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>8.28</td>\n",
       "      <td>11.825699</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   month  day   latitude  longitude  year_num  year_cos  year_sin  month_cos  month_sin   day_num   day_cos   day_sin  time_num  time_cos  time_sin  max_temp  min_temp  avg_wind  pred_pickups  pickups\n",
       "0      5    1  40.751724 -73.942795  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28      1.396741      NaN\n",
       "1      5    1  40.762711 -74.000473  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28      1.642030        8\n",
       "2      5    1  40.736618 -73.983994  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28      7.021172        8\n",
       "3      5    1  40.731125 -73.926315  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28      1.368385      NaN\n",
       "4      5    1  40.747604 -73.973007  0.328767 -0.474951  0.880012          1          0  0.709821 -0.249776 -0.968304   0.96875  0.980785  -0.19509      17.2       9.4      8.28     11.825699       16"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print result.shape[0]\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>time_num</th>\n",
       "      <th>pred_pickups</th>\n",
       "      <th>pickups</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>40.751724</td>\n",
       "      <td>-73.942795</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>1.396741</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>40.762711</td>\n",
       "      <td>-74.000473</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>1.642030</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>40.736618</td>\n",
       "      <td>-73.983994</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>7.021172</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40.731125</td>\n",
       "      <td>-73.926315</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>1.368385</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>40.747604</td>\n",
       "      <td>-73.973007</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>11.825699</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    latitude  longitude  time_num  pred_pickups  pickups\n",
       "0  40.751724 -73.942795   0.96875      1.396741      NaN\n",
       "1  40.762711 -74.000473   0.96875      1.642030        8\n",
       "2  40.736618 -73.983994   0.96875      7.021172        8\n",
       "3  40.731125 -73.926315   0.96875      1.368385      NaN\n",
       "4  40.747604 -73.973007   0.96875     11.825699       16"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop unnecessary columns to reduce memory consumption in Tableau\n",
    "result = result.drop(['month','day','time_cos','day_num','time_sin','day_cos','day_sin',\n",
    "                      'max_temp','min_temp','avg_wind',\n",
    "                      'year_num','year_sin','year_cos','month_sin','month_cos'], axis=1)\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(135269, 5)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>time_num</th>\n",
       "      <th>pred_pickups</th>\n",
       "      <th>pickups</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>40.762711</td>\n",
       "      <td>-74.000473</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>1.642030</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>40.736618</td>\n",
       "      <td>-73.983994</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>7.021172</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>40.747604</td>\n",
       "      <td>-73.973007</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>11.825699</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>40.727005</td>\n",
       "      <td>-73.990860</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>13.534925</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>40.725632</td>\n",
       "      <td>-73.996353</td>\n",
       "      <td>0.96875</td>\n",
       "      <td>14.008216</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    latitude  longitude  time_num  pred_pickups  pickups\n",
       "1  40.762711 -74.000473   0.96875      1.642030        8\n",
       "2  40.736618 -73.983994   0.96875      7.021172        8\n",
       "4  40.747604 -73.973007   0.96875     11.825699       16\n",
       "5  40.727005 -73.990860   0.96875     13.534925       10\n",
       "6  40.725632 -73.996353   0.96875     14.008216       12"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Exclude all records that have less than 2 pickups or less than 1.5 predicted pickups.\n",
    "# This is to reduce the size of the resulting dataset in Tableau\n",
    "result = result[(result['pred_pickups'] >= 1.5) | (result['pickups'] >= 1.5)]\n",
    "print result.shape\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Write to csv\n",
    "result.to_csv('./data/predictions-may-1-weather.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>time_num</th>\n",
       "      <th>pred_pickups</th>\n",
       "      <th>pickups</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>135269.000000</td>\n",
       "      <td>135269.000000</td>\n",
       "      <td>135269.000000</td>\n",
       "      <td>135269.000000</td>\n",
       "      <td>79610.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>40.751806</td>\n",
       "      <td>-73.969821</td>\n",
       "      <td>0.506728</td>\n",
       "      <td>4.191233</td>\n",
       "      <td>6.162844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.045336</td>\n",
       "      <td>0.046021</td>\n",
       "      <td>0.304190</td>\n",
       "      <td>4.872338</td>\n",
       "      <td>7.399975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>40.500412</td>\n",
       "      <td>-74.099350</td>\n",
       "      <td>0.010417</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>40.725632</td>\n",
       "      <td>-73.994980</td>\n",
       "      <td>0.239583</td>\n",
       "      <td>1.650444</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>40.753098</td>\n",
       "      <td>-73.977127</td>\n",
       "      <td>0.510417</td>\n",
       "      <td>2.177100</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>40.776443</td>\n",
       "      <td>-73.955154</td>\n",
       "      <td>0.781250</td>\n",
       "      <td>4.682568</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>41.099167</td>\n",
       "      <td>-73.620071</td>\n",
       "      <td>0.989583</td>\n",
       "      <td>122.513052</td>\n",
       "      <td>175.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            latitude      longitude       time_num   pred_pickups       pickups\n",
       "count  135269.000000  135269.000000  135269.000000  135269.000000  79610.000000\n",
       "mean       40.751806     -73.969821       0.506728       4.191233      6.162844\n",
       "std         0.045336       0.046021       0.304190       4.872338      7.399975\n",
       "min        40.500412     -74.099350       0.010417       1.000000      1.000000\n",
       "25%        40.725632     -73.994980       0.239583       1.650444      2.000000\n",
       "50%        40.753098     -73.977127       0.510417       2.177100      4.000000\n",
       "75%        40.776443     -73.955154       0.781250       4.682568      8.000000\n",
       "max        41.099167     -73.620071       0.989583     122.513052    175.000000"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sanity check\n",
    "result.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script type='text/javascript' src='https://public.tableau.com/javascripts/api/viz_v1.js'></script><div class='tableauPlaceholder' style='width: 804px; height: 569px;'><noscript><a href='#'><img alt='Pickups in NYC on May 1, 2015Play around with the slider below to see pickup densities at different times of the day ' src='https:&#47;&#47;public.tableau.com&#47;static&#47;images&#47;KD&#47;KD45Y8HBN&#47;1_rss.png' style='border: none' /></a></noscript><object class='tableauViz' width='804' height='569' style='display:none;'><param name='host_url' value='https%3A%2F%2Fpublic.tableau.com%2F' /> <param name='path' value='shared&#47;KD45Y8HBN' /> <param name='toolbar' value='yes' /><param name='static_image' value='https:&#47;&#47;public.tableau.com&#47;static&#47;images&#47;KD&#47;KD45Y8HBN&#47;1.png' /> <param name='animate_transition' value='yes' /><param name='display_static_image' value='yes' /><param name='display_spinner' value='yes' /><param name='display_overlay' value='yes' /><param name='display_count' value='yes' /><param name='showVizHome' value='no' /><param name='showTabs' value='y' /><param name='bootstrapWhenNotified' value='true' /></object></div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "<script type='text/javascript' src='https://public.tableau.com/javascripts/api/viz_v1.js'></script><div class='tableauPlaceholder' style='width: 804px; height: 569px;'><noscript><a href='#'><img alt='Pickups in NYC on May 1, 2015Play around with the slider below to see pickup densities at different times of the day ' src='https:&#47;&#47;public.tableau.com&#47;static&#47;images&#47;KD&#47;KD45Y8HBN&#47;1_rss.png' style='border: none' /></a></noscript><object class='tableauViz' width='804' height='569' style='display:none;'><param name='host_url' value='https%3A%2F%2Fpublic.tableau.com%2F' /> <param name='path' value='shared&#47;KD45Y8HBN' /> <param name='toolbar' value='yes' /><param name='static_image' value='https:&#47;&#47;public.tableau.com&#47;static&#47;images&#47;KD&#47;KD45Y8HBN&#47;1.png' /> <param name='animate_transition' value='yes' /><param name='display_static_image' value='yes' /><param name='display_spinner' value='yes' /><param name='display_overlay' value='yes' /><param name='display_count' value='yes' /><param name='showVizHome' value='no' /><param name='showTabs' value='y' /><param name='bootstrapWhenNotified' value='true' /></object></div>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}