4a. Random Forest (average days).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preamble"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Configure libraries\n",
    "\n",
    "%matplotlib inline\n",
    "import numpy as np\n",
    "import scipy as sp\n",
    "import matplotlib as mpl\n",
    "import matplotlib.cm as cm\n",
    "import matplotlib.pyplot as plt\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",
    "import seaborn as sns\n",
    "sns.set_style(\"whitegrid\")\n",
    "sns.set_context(\"poster\")\n",
    "from sklearn.grid_search import GridSearchCV\n",
    "from sklearn.cross_validation import train_test_split\n",
    "from sklearn.metrics import confusion_matrix\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "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 data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a Geohash precision of 6 (geohash length of 6 characters), we have roughly 300,000 records. For a precision of 7, we have about 4 million records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3819998, 11)\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 = [\"time_cat\", \"time_num\", \"time_cos\", \"time_sin\", \"day_cat\", \"day_num\", \"day_cos\", \"day_sin\", \"weekend\", \"geohash\", \"pickups\"]\n",
    "dftaxi=pd.read_csv(\"./data/taxi_data_prec_7.csv\", header=None, names = names)\n",
    "print dftaxi.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define training and test sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([False,  True,  True,  True,  True,  True, False,  True, False,  True], dtype=bool)"
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "itrain, itest = train_test_split(xrange(dftaxi.shape[0]), train_size=0.8)\n",
    "mask=np.ones(dftaxi.shape[0], dtype='int')\n",
    "mask[itrain]=1\n",
    "mask[itest]=0\n",
    "mask = (mask==1)\n",
    "mask[:10]"
   ]
  },
  {
   "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, but I quickly realized that this was not feasible from a memory perspective. 3 million records times 100,000 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": "code",
   "execution_count": 193,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Split off the features\n",
    "Xnames = [\"time_cat\", \"time_num\", \"time_cos\", \"time_sin\", \"day_cat\",\n",
    "          \"day_num\", \"day_cos\", \"day_sin\", \"weekend\", \"geohash\"]\n",
    "X = dftaxi[Xnames]\n",
    "\n",
    "# Split off the target (which will be the logarithm of the number of pickups (+1))\n",
    "y = np.log10(dftaxi['pickups']+1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "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>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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15:30</td>\n",
       "      <td>0.656250</td>\n",
       "      <td>-0.555570</td>\n",
       "      <td>-0.831470</td>\n",
       "      <td>Monday</td>\n",
       "      <td>0.093750</td>\n",
       "      <td>0.831470</td>\n",
       "      <td>0.555570</td>\n",
       "      <td>0</td>\n",
       "      <td>dr5rvnz</td>\n",
       "      <td>40.775070</td>\n",
       "      <td>-73.949661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15:00</td>\n",
       "      <td>0.635417</td>\n",
       "      <td>-0.659346</td>\n",
       "      <td>-0.751840</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0.233631</td>\n",
       "      <td>0.102669</td>\n",
       "      <td>0.994716</td>\n",
       "      <td>0</td>\n",
       "      <td>dr5rm12</td>\n",
       "      <td>40.656967</td>\n",
       "      <td>-73.959274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>08:00</td>\n",
       "      <td>0.343750</td>\n",
       "      <td>-0.555570</td>\n",
       "      <td>0.831470</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>0.334821</td>\n",
       "      <td>-0.508075</td>\n",
       "      <td>0.861313</td>\n",
       "      <td>0</td>\n",
       "      <td>dr5resv</td>\n",
       "      <td>40.720139</td>\n",
       "      <td>-74.018326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14:30</td>\n",
       "      <td>0.614583</td>\n",
       "      <td>-0.751840</td>\n",
       "      <td>-0.659346</td>\n",
       "      <td>Monday</td>\n",
       "      <td>0.087798</td>\n",
       "      <td>0.851662</td>\n",
       "      <td>0.524092</td>\n",
       "      <td>0</td>\n",
       "      <td>dr5russ</td>\n",
       "      <td>40.762711</td>\n",
       "      <td>-73.975754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11:30</td>\n",
       "      <td>0.489583</td>\n",
       "      <td>-0.997859</td>\n",
       "      <td>0.065403</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0.212798</td>\n",
       "      <td>0.231627</td>\n",
       "      <td>0.972805</td>\n",
       "      <td>0</td>\n",
       "      <td>dr72m1p</td>\n",
       "      <td>40.831375</td>\n",
       "      <td>-73.949661</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  time_cat  time_num  time_cos  time_sin    day_cat   day_num   day_cos   day_sin  weekend  geohash   latitude  longitude\n",
       "0    15:30  0.656250 -0.555570 -0.831470     Monday  0.093750  0.831470  0.555570        0  dr5rvnz  40.775070 -73.949661\n",
       "1    15:00  0.635417 -0.659346 -0.751840    Tuesday  0.233631  0.102669  0.994716        0  dr5rm12  40.656967 -73.959274\n",
       "2    08:00  0.343750 -0.555570  0.831470  Wednesday  0.334821 -0.508075  0.861313        0  dr5resv  40.720139 -74.018326\n",
       "3    14:30  0.614583 -0.751840 -0.659346     Monday  0.087798  0.851662  0.524092        0  dr5russ  40.762711 -73.975754\n",
       "4    11:30  0.489583 -0.997859  0.065403    Tuesday  0.212798  0.231627  0.972805        0  dr72m1p  40.831375 -73.949661"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 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",
    "X['latitude'] = X['geohash'].apply(lambda geo: decodegeo(geo, 0))\n",
    "X['longitude'] = X['geohash'].apply(lambda geo: decodegeo(geo, 1))\n",
    "X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Create indicator variables for the hours and days of the week and drop the categorical values\n",
    "# g = 5\n",
    "# X = X.join(pd.get_dummies(X['time_cat']))\\\n",
    "#      .join(pd.get_dummies(X['day_cat']))\\\n",
    "#      .drop(['time_cat','day_cat','geohash'], axis=1)\n",
    "X = X.drop(['time_cat','day_cat','geohash'], axis=1)\n",
    "#     .join(pd.get_dummies(X['geohash'].str[:g]))\\"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get the training and test sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3055998, 9)\n",
      "(1000000, 9)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1360274</th>\n",
       "      <td>0.572917</td>\n",
       "      <td>-0.896873</td>\n",
       "      <td>-0.442289</td>\n",
       "      <td>0.224702</td>\n",
       "      <td>0.158281</td>\n",
       "      <td>0.987394</td>\n",
       "      <td>0</td>\n",
       "      <td>40.736618</td>\n",
       "      <td>-73.878250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1363421</th>\n",
       "      <td>0.885417</td>\n",
       "      <td>0.751840</td>\n",
       "      <td>-0.659346</td>\n",
       "      <td>0.983631</td>\n",
       "      <td>0.994716</td>\n",
       "      <td>-0.102669</td>\n",
       "      <td>1</td>\n",
       "      <td>40.806656</td>\n",
       "      <td>-73.956528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3517805</th>\n",
       "      <td>0.843750</td>\n",
       "      <td>0.555570</td>\n",
       "      <td>-0.831470</td>\n",
       "      <td>0.549107</td>\n",
       "      <td>-0.952775</td>\n",
       "      <td>-0.303677</td>\n",
       "      <td>0</td>\n",
       "      <td>40.765457</td>\n",
       "      <td>-74.022446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1417738</th>\n",
       "      <td>0.239583</td>\n",
       "      <td>0.065403</td>\n",
       "      <td>0.997859</td>\n",
       "      <td>0.462798</td>\n",
       "      <td>-0.972805</td>\n",
       "      <td>0.231627</td>\n",
       "      <td>0</td>\n",
       "      <td>40.759964</td>\n",
       "      <td>-73.970261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1396909</th>\n",
       "      <td>0.593750</td>\n",
       "      <td>-0.831470</td>\n",
       "      <td>-0.555570</td>\n",
       "      <td>0.513393</td>\n",
       "      <td>-0.996461</td>\n",
       "      <td>-0.084051</td>\n",
       "      <td>0</td>\n",
       "      <td>40.717392</td>\n",
       "      <td>-73.940048</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         time_num  time_cos  time_sin   day_num   day_cos   day_sin  weekend   latitude  longitude\n",
       "1360274  0.572917 -0.896873 -0.442289  0.224702  0.158281  0.987394        0  40.736618 -73.878250\n",
       "1363421  0.885417  0.751840 -0.659346  0.983631  0.994716 -0.102669        1  40.806656 -73.956528\n",
       "3517805  0.843750  0.555570 -0.831470  0.549107 -0.952775 -0.303677        0  40.765457 -74.022446\n",
       "1417738  0.239583  0.065403  0.997859  0.462798 -0.972805  0.231627        0  40.759964 -73.970261\n",
       "1396909  0.593750 -0.831470 -0.555570  0.513393 -0.996461 -0.084051        0  40.717392 -73.940048"
      ]
     },
     "execution_count": 196,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Xtrain, Xtest, ytrain, ytest = X[mask], X[~mask], y[mask], y[~mask]\n",
    "n_samples = Xtrain.shape[0]\n",
    "n_features = Xtrain.shape[1]\n",
    "print Xtrain.shape\n",
    "max_samples = 1000000\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Create a Random Forest Regression estimator\n",
    "estimator = RandomForestRegressor(n_estimators=20, n_jobs=-1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The step below takes about 25 mins on my laptop for 300,000 records and up to 100 estimators."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fitting 5 folds for each of 1 candidates, totalling 5 fits\n",
      "[CV] max_features=auto, n_estimators=50, max_depth=50 ................\n",
      "[CV]  max_features=auto, n_estimators=50, max_depth=50, score=-0.037116 - 2.4min\n",
      "[CV] max_features=auto, n_estimators=50, max_depth=50 ................\n",
      "[CV]  max_features=auto, n_estimators=50, max_depth=50, score=-0.037396 - 2.5min\n",
      "[CV] max_features=auto, n_estimators=50, max_depth=50 ................\n",
      "[CV]  max_features=auto, n_estimators=50, max_depth=50, score=-0.037323 - 2.7min\n",
      "[CV] max_features=auto, n_estimators=50, max_depth=50 ................\n",
      "[CV]  max_features=auto, n_estimators=50, max_depth=50, score=-0.037099 - 2.8min\n",
      "[CV] max_features=auto, n_estimators=50, max_depth=50 ................\n",
      "[CV]  max_features=auto, n_estimators=50, max_depth=50, score=-0.037165 - 2.6min"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Parallel(n_jobs=1)]: Done   1 jobs       | elapsed:  2.4min\n",
      "[Parallel(n_jobs=1)]: Done   5 out of   5 | elapsed: 13.1min finished\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "BEST {'max_features': 'auto', 'n_estimators': 50, 'max_depth': 50} -0.0372196114629 [mean: -0.03722, std: 0.00012, params: {'max_features': 'auto', 'n_estimators': 50, 'max_depth': 50}] make_scorer(mean_squared_error, greater_is_better=False)\n",
      "Best score:  -0.0372196114629\n",
      "Wall time: 16min 41s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Define a grid of parameters over which to optimize the random forest\n",
    "# We will figure out which number of trees is optimal\n",
    "parameters = {\"n_estimators\": [50],\n",
    "              \"max_features\": [\"auto\"], # [\"auto\",\"sqrt\",\"log2\"]\n",
    "              \"max_depth\": [50]}\n",
    "best = cv_optimize(estimator, parameters, Xtrain, ytrain, n_folds=5, score_func='mean_squared_error', verbose=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Evaluate the results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "############# based on standard predict ################\n",
      "R^2 on training data: 0.9928\n",
      "R^2 on test data:     0.9505\n"
     ]
    }
   ],
   "source": [
    "# Fit the best Random Forest and calculate R^2 values for training and test sets\n",
    "reg=best.fit(Xtrain, ytrain)\n",
    "training_accuracy = reg.score(Xtrain, ytrain)\n",
    "test_accuracy = reg.score(Xtest, ytest)\n",
    "print \"############# based on standard predict ################\"\n",
    "print \"R^2 on training data: %0.4f\" % (training_accuracy)\n",
    "print \"R^2 on test data:     %0.4f\" % (test_accuracy)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sanity check on some records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[189, 178],\n",
       "       [  1,   1],\n",
       "       [ 47,  48],\n",
       "       ..., \n",
       "       [  2,   1],\n",
       "       [ 13,  42],\n",
       "       [  1,   1]])"
      ]
     },
     "execution_count": 210,
     "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",
    "np.round(np.power(10,np.column_stack((reg.predict(Xtest),ytest))) - 1,decimals=0).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "RMSE = 0.183 (this is in log-space!)\n",
      "So two thirds of the records would be a factor of less than 1.52 away from the real value.\n"
     ]
    }
   ],
   "source": [
    "# Calculate the Root Mean Squared Error\n",
    "rmse = np.sqrt(mean_squared_error(reg.predict(Xtest),ytest))\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": 213,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('longitude', 0.49286711184864612),\n",
       " ('latitude', 0.42160153852351745),\n",
       " ('time_num', 0.02934174261881017),\n",
       " ('time_cos', 0.013840678747627112),\n",
       " ('day_sin', 0.012299318524748769),\n",
       " ('day_num', 0.011275554374888259),\n",
       " ('time_sin', 0.010335042662988656),\n",
       " ('day_cos', 0.0077883669738441274),\n",
       " ('weekend', 0.00065064572492942946)]"
      ]
     },
     "execution_count": 213,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What are the most important features?\n",
    "import operator\n",
    "dict_feat_imp = dict(zip(list(X.columns.values),reg.feature_importances_))\n",
    "\n",
    "sorted_features = sorted(dict_feat_imp.items(), key=operator.itemgetter(1), reverse=True)\n",
    "sorted_features"
   ]
  },
  {
   "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": [
    "#### First we need a dataframe with all possible combinations of time and location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Construct dataframes with all possible times (time_data) and all possible locations (loc_data)\n",
    "\n",
    "# Columns about time\n",
    "time_cols = list(X.columns.values)\n",
    "time_cols.remove('latitude')\n",
    "time_cols.remove('longitude')\n",
    "\n",
    "# Columns about location\n",
    "loc_cols = ['latitude', 'longitude']\n",
    "\n",
    "# Unique times\n",
    "time_data = X.drop(loc_cols, axis=1).drop_duplicates()\n",
    "\n",
    "# In Tableau we are only going to look at Monday\n",
    "time_data = time_data[time_data['day_num'] <= 1/7.]\n",
    "\n",
    "# Unique locations\n",
    "loc_data = X.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)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Dummy column to be able to join them together\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]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Then we do the prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\m07e036.EUROPE.003\\AppData\\Local\\Continuum\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "C:\\Users\\m07e036.EUROPE.003\\AppData\\Local\\Continuum\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>pred_pickups</th>\n",
       "      <th>pickups</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.65625</td>\n",
       "      <td>-0.55557</td>\n",
       "      <td>-0.83147</td>\n",
       "      <td>0.09375</td>\n",
       "      <td>0.83147</td>\n",
       "      <td>0.55557</td>\n",
       "      <td>0</td>\n",
       "      <td>40.775070</td>\n",
       "      <td>-73.949661</td>\n",
       "      <td>189.259397</td>\n",
       "      <td>178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.65625</td>\n",
       "      <td>-0.55557</td>\n",
       "      <td>-0.83147</td>\n",
       "      <td>0.09375</td>\n",
       "      <td>0.83147</td>\n",
       "      <td>0.55557</td>\n",
       "      <td>0</td>\n",
       "      <td>40.656967</td>\n",
       "      <td>-73.959274</td>\n",
       "      <td>3.963223</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.65625</td>\n",
       "      <td>-0.55557</td>\n",
       "      <td>-0.83147</td>\n",
       "      <td>0.09375</td>\n",
       "      <td>0.83147</td>\n",
       "      <td>0.55557</td>\n",
       "      <td>0</td>\n",
       "      <td>40.720139</td>\n",
       "      <td>-74.018326</td>\n",
       "      <td>1.084932</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.65625</td>\n",
       "      <td>-0.55557</td>\n",
       "      <td>-0.83147</td>\n",
       "      <td>0.09375</td>\n",
       "      <td>0.83147</td>\n",
       "      <td>0.55557</td>\n",
       "      <td>0</td>\n",
       "      <td>40.762711</td>\n",
       "      <td>-73.975754</td>\n",
       "      <td>677.696195</td>\n",
       "      <td>646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.65625</td>\n",
       "      <td>-0.55557</td>\n",
       "      <td>-0.83147</td>\n",
       "      <td>0.09375</td>\n",
       "      <td>0.83147</td>\n",
       "      <td>0.55557</td>\n",
       "      <td>0</td>\n",
       "      <td>40.831375</td>\n",
       "      <td>-73.949661</td>\n",
       "      <td>2.245625</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.65625</td>\n",
       "      <td>-0.55557</td>\n",
       "      <td>-0.83147</td>\n",
       "      <td>0.09375</td>\n",
       "      <td>0.83147</td>\n",
       "      <td>0.55557</td>\n",
       "      <td>0</td>\n",
       "      <td>40.806656</td>\n",
       "      <td>-73.942795</td>\n",
       "      <td>182.849692</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.65625</td>\n",
       "      <td>-0.55557</td>\n",
       "      <td>-0.83147</td>\n",
       "      <td>0.09375</td>\n",
       "      <td>0.83147</td>\n",
       "      <td>0.55557</td>\n",
       "      <td>0</td>\n",
       "      <td>40.707779</td>\n",
       "      <td>-74.021072</td>\n",
       "      <td>1.049252</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.65625</td>\n",
       "      <td>-0.55557</td>\n",
       "      <td>-0.83147</td>\n",
       "      <td>0.09375</td>\n",
       "      <td>0.83147</td>\n",
       "      <td>0.55557</td>\n",
       "      <td>0</td>\n",
       "      <td>40.670700</td>\n",
       "      <td>-73.981247</td>\n",
       "      <td>3.682788</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.65625</td>\n",
       "      <td>-0.55557</td>\n",
       "      <td>-0.83147</td>\n",
       "      <td>0.09375</td>\n",
       "      <td>0.83147</td>\n",
       "      <td>0.55557</td>\n",
       "      <td>0</td>\n",
       "      <td>40.735245</td>\n",
       "      <td>-73.875504</td>\n",
       "      <td>42.386296</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.65625</td>\n",
       "      <td>-0.55557</td>\n",
       "      <td>-0.83147</td>\n",
       "      <td>0.09375</td>\n",
       "      <td>0.83147</td>\n",
       "      <td>0.55557</td>\n",
       "      <td>0</td>\n",
       "      <td>40.720139</td>\n",
       "      <td>-74.001846</td>\n",
       "      <td>486.831538</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   time_num  time_cos  time_sin  day_num  day_cos  day_sin  weekend   latitude  longitude  pred_pickups  pickups\n",
       "0   0.65625  -0.55557  -0.83147  0.09375  0.83147  0.55557        0  40.775070 -73.949661    189.259397      178\n",
       "1   0.65625  -0.55557  -0.83147  0.09375  0.83147  0.55557        0  40.656967 -73.959274      3.963223        7\n",
       "2   0.65625  -0.55557  -0.83147  0.09375  0.83147  0.55557        0  40.720139 -74.018326      1.084932      NaN\n",
       "3   0.65625  -0.55557  -0.83147  0.09375  0.83147  0.55557        0  40.762711 -73.975754    677.696195      646\n",
       "4   0.65625  -0.55557  -0.83147  0.09375  0.83147  0.55557        0  40.831375 -73.949661      2.245625        3\n",
       "5   0.65625  -0.55557  -0.83147  0.09375  0.83147  0.55557        0  40.806656 -73.942795    182.849692      168\n",
       "6   0.65625  -0.55557  -0.83147  0.09375  0.83147  0.55557        0  40.707779 -74.021072      1.049252      NaN\n",
       "7   0.65625  -0.55557  -0.83147  0.09375  0.83147  0.55557        0  40.670700 -73.981247      3.682788        1\n",
       "8   0.65625  -0.55557  -0.83147  0.09375  0.83147  0.55557        0  40.735245 -73.875504     42.386296       41\n",
       "9   0.65625  -0.55557  -0.83147  0.09375  0.83147  0.55557        0  40.720139 -74.001846    486.831538      555"
      ]
     },
     "execution_count": 225,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the real number of pickups and take care that we can merge it with the predictions,\n",
    "# by also taking the geohash and the timestamp\n",
    "yy = dftaxi[['geohash','day_num','pickups']]\n",
    "\n",
    "# Decode the geohash in the latitude and longitude\n",
    "yy['latitude'] = yy['geohash'].apply(lambda geo: decodegeo(geo, 0))\n",
    "yy['longitude'] = yy['geohash'].apply(lambda geo: decodegeo(geo, 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=['day_num', 'latitude', 'longitude']).drop(['geohash'], axis=1)\n",
    "result.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Drop unnecessary columns to reduce memory consumption in Tableau\n",
    "result = result.drop(['time_cos','day_num','time_sin','day_cos','day_sin','weekend'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Write to csv\n",
    "result.to_csv('./data/taxi-data-predictions_prec_7_monday.csv')"
   ]
  }
 ],
 "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
}