Prepare-taxi-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Taxi data preparation\n",
    "\n",
    "In this notebook we are showing how the taxi data was pre-processed prior to being used in the Dash web-application."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:19:17.524606Z",
     "start_time": "2020-06-02T09:19:16.795430Z"
    }
   },
   "outputs": [],
   "source": [
    "import vaex\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get the data\n",
    "\n",
    "The raw data of the New York Taxi dataset is curated by [Taxi and Limousine Comission (TLC)](https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page), and comes it CSV format. The data is somewhat dirty, and requires quite a lot of manual adjustments before it can be converted to the memory mappable HDF5 file format. As this is quite tedious, we have already done the conversion for the YellowCab taxi data between 2009 and 2015, and the data is available on S3.\n",
    "\n",
    "We will select a single year of taxi data, for example 2012."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:19:18.962295Z",
     "start_time": "2020-06-02T09:19:18.938569Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                                      </th><th>vendor_id  </th><th>pickup_datetime              </th><th>dropoff_datetime             </th><th>passenger_count  </th><th>payment_type  </th><th>trip_distance      </th><th>pickup_longitude  </th><th>pickup_latitude   </th><th>rate_code  </th><th>store_and_fwd_flag  </th><th>dropoff_longitude  </th><th>dropoff_latitude  </th><th>fare_amount      </th><th>surcharge  </th><th>mta_tax  </th><th>tip_amount      </th><th>tolls_amount     </th><th>total_amount     </th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i>          </td><td>CMT        </td><td>2012-01-10 23:55:50.000000000</td><td>2012-01-11 00:03:39.000000000</td><td>1                </td><td>CRD           </td><td>1.7000000476837158 </td><td>-73.99468994140625</td><td>40.725032806396484</td><td>1.0        </td><td>0.0                 </td><td>-73.9759521484375  </td><td>40.73078155517578 </td><td>6.900000095367432</td><td>0.5        </td><td>0.5      </td><td>1.0             </td><td>0.0              </td><td>8.899999618530273</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i>          </td><td>CMT        </td><td>2012-01-11 19:18:25.000000000</td><td>2012-01-11 19:26:10.000000000</td><td>1                </td><td>CSH           </td><td>1.100000023841858  </td><td>-73.98795318603516</td><td>40.75294876098633 </td><td>1.0        </td><td>0.0                 </td><td>-73.9945297241211  </td><td>40.76103973388672 </td><td>6.099999904632568</td><td>1.0        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>7.599999904632568</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i>          </td><td>CMT        </td><td>2012-01-11 19:19:19.000000000</td><td>2012-01-11 19:48:15.000000000</td><td>2                </td><td>CRD           </td><td>18.0               </td><td>-73.78309631347656</td><td>40.6485481262207  </td><td>2.0        </td><td>0.0                 </td><td>-73.99613189697266 </td><td>40.747623443603516</td><td>45.0             </td><td>0.0        </td><td>0.5      </td><td>10.0600004196167</td><td>4.800000190734863</td><td>60.36000061035156</td></tr>\n",
       "<tr><td>...                                    </td><td>...        </td><td>...                          </td><td>...                          </td><td>...              </td><td>...           </td><td>...                </td><td>...               </td><td>...               </td><td>...        </td><td>...                 </td><td>...                </td><td>...               </td><td>...              </td><td>...        </td><td>...      </td><td>...             </td><td>...              </td><td>...              </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,321</i></td><td>CMT        </td><td>2012-12-24 21:09:56.000000000</td><td>2012-12-24 21:13:22.000000000</td><td>1                </td><td>CSH           </td><td>0.30000001192092896</td><td>-73.96232604980469</td><td>40.77641296386719 </td><td>1.0        </td><td>0.0                 </td><td>-73.95834350585938 </td><td>40.774906158447266</td><td>4.0              </td><td>0.5        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>5.0              </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,322</i></td><td>CMT        </td><td>2012-12-24 20:07:23.000000000</td><td>2012-12-24 20:20:18.000000000</td><td>1                </td><td>CSH           </td><td>1.100000023841858  </td><td>-73.97209167480469</td><td>40.75520706176758 </td><td>4.0        </td><td>0.0                 </td><td>-73.98250579833984 </td><td>40.76253890991211 </td><td>9.5              </td><td>0.5        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>10.5             </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,323</i></td><td>CMT        </td><td>2012-12-24 09:57:17.000000000</td><td>2012-12-24 09:59:51.000000000</td><td>2                </td><td>CSH           </td><td>0.5                </td><td>-73.97518920898438</td><td>40.76108169555664 </td><td>1.0        </td><td>0.0                 </td><td>-73.98072814941406 </td><td>40.753299713134766</td><td>4.0              </td><td>0.0        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>4.5              </td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Read in the data\n",
    "df = vaex.open('/data/yellow_taxi_2009_2015_f32.hdf5')\n",
    "# df = vaex.open('s3://vaex/taxi/yellow_taxi_2009_2015_f32.hdf5?anon=true')\n",
    "\n",
    "# The data is sorted, so we can simply slite it\n",
    "df = df[516794416:695338739+1]  # take 2012\n",
    "df.head_and_tail_print(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Get the raw data straght from TLC (alternative method)\n",
    "\n",
    "If you would rather download the raw CSV files straight from the TLC website, and convert them to HDF5 locally, you can do so by uncommenting and running the cell below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T08:48:17.562666Z",
     "start_time": "2020-06-02T08:12:56.779946Z"
    }
   },
   "outputs": [],
   "source": [
    "# # List of files to download\n",
    "# dlinks = [f'https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2012-{m:02}.csv' for m in range(1, 13)]\n",
    "\n",
    "# # A consistent header names for all files\n",
    "# header = ['vendor_id',\n",
    "#           'pickup_datetime',\n",
    "#           'dropoff_datetime',\n",
    "#           'passenger_count',\n",
    "#           'trip_distance',\n",
    "#           'pickup_longitude',\n",
    "#           'pickup_latitude',\n",
    "#           'rate_code',\n",
    "#           'store_and_fwd_flag',\n",
    "#           'dropoff_longitude',\n",
    "#           'dropoff_latitude',\n",
    "#           'payment_type',\n",
    "#           'fare_amount',\n",
    "#           'surcharge',\n",
    "#           'mta_tax',\n",
    "#           'tip_amount',\n",
    "#           'tolls_amount',\n",
    "#           'total_amount',\n",
    "#          ]\n",
    "\n",
    "# # Specify the data types of each column\n",
    "# types = {'vendor_id': str,\n",
    "#          'pickup_datetime':  np.float32,\n",
    "#          'dropoff_datetime': np.float32,\n",
    "#          'passenger_count': np.int64,\n",
    "#          'trip_distance': np.float32,\n",
    "#          'pickup_longitude': np.float32,\n",
    "#          'pickup_latitude': np.float32,\n",
    "#          'rate_code': np.float32,\n",
    "#          'store_and_fwd_flag': str,\n",
    "#          'dropoff_longitude': np.float32,\n",
    "#          'dropoff_latitude': np.float32,\n",
    "#          'payment_type': str,\n",
    "#          'fare_amount': np.float32,\n",
    "#          'surcharge': np.float32,\n",
    "#          'mta_tax': np.float32,\n",
    "#          'tip_amount': np.float32,\n",
    "#          'tolls_amount': np.float32,\n",
    "#          'total_amount': np.float32,\n",
    "#          }\n",
    "\n",
    "# for i, month in enumerate(tqdm(dlinks, desc='Downloading and converting raw data...')):\n",
    "#     # Read the CSV data with pandas directly for the TLC website\n",
    "#     df_tmp = pd.read_csv(month, \n",
    "#                          parse_dates=['pickup_datetime', 'dropoff_datetime'],\n",
    "#                          header=None,\n",
    "#                          names=header,\n",
    "#                          dtype=types,\n",
    "#                          error_bad_lines=False,\n",
    "#                          skiprows=1)\n",
    "#     # Convert to a vaex dataframe\n",
    "#     df = vaex.from_pandas(df_tmp)\n",
    "#     # Export to HDF5\n",
    "#     df.export_hdf5(path=f'yellow_tripdata_2012-{i:02}.hdf5', progress=True)\n",
    "#     # Clean up\n",
    "#     del df, df_tmp\n",
    "\n",
    "# # Open all of the data as a single DataFrame\n",
    "# df = df = vaex.open('*.hdf5')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add borough/zone info\n",
    "\n",
    "In what follows we are adding pick-up and drop-off zone and borough information to the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:19:24.260492Z",
     "start_time": "2020-06-02T09:19:24.242582Z"
    }
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import collections\n",
    "\n",
    "geo_filename = './aux_data/taxi_zones-tiny.json'\n",
    "with open(geo_filename) as f:\n",
    "    geo_json = json.load(f)\n",
    "\n",
    "features = geo_json['features']\n",
    "borough_polygons = collections.defaultdict(list)\n",
    "zone_polygons = collections.defaultdict(list)\n",
    "zbmapper = {}\n",
    "list_of_polygons = []\n",
    "for i, feature in enumerate(features[:]):\n",
    "    properties = feature['properties']\n",
    "    geo = feature['geometry']\n",
    "\n",
    "    polygons = []\n",
    "    for polygon in geo['coordinates']:\n",
    "        polygon = np.array(polygon)\n",
    "        if polygon.ndim == 3:\n",
    "            polygon = polygon[0]\n",
    "        polygon = polygon.T\n",
    "        assert polygon.shape[0] == 2\n",
    "        assert polygon.ndim == 2\n",
    "        polygons.append(polygon)\n",
    "\n",
    "    borough_polygons[properties['borough']].extend(polygons)\n",
    "    zone_polygons[properties['zone']].extend(polygons)\n",
    "    zbmapper[properties['zone']] = properties['borough']\n",
    "    \n",
    "keys = list(borough_polygons.keys())\n",
    "bmapper = {i:keys[i] for i in range(len(keys))}\n",
    "keys = list(zone_polygons.keys())\n",
    "zmapper = {i:keys[i] for i in range(len(keys))}\n",
    "\n",
    "with open('./aux_data/zone.json', 'w') as f:\n",
    "    json.dump(zmapper, f)\n",
    "with open('./aux_data/borough.json', 'w') as f:\n",
    "    json.dump(bmapper, f)\n",
    "with open('./aux_data/zone_to_borough.json', 'w') as f:\n",
    "    json.dump(zbmapper, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:19:43.910612Z",
     "start_time": "2020-06-02T09:19:24.528975Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                                      </th><th>vendor_id  </th><th>pickup_datetime              </th><th>dropoff_datetime             </th><th>passenger_count  </th><th>payment_type  </th><th>trip_distance      </th><th>pickup_longitude  </th><th>pickup_latitude   </th><th>rate_code  </th><th>store_and_fwd_flag  </th><th>dropoff_longitude  </th><th>dropoff_latitude  </th><th>fare_amount      </th><th>surcharge  </th><th>mta_tax  </th><th>tip_amount      </th><th>tolls_amount     </th><th>total_amount     </th><th>dropoff_borough  </th><th>dropoff_zone  </th><th>pickup_borough  </th><th>pickup_zone  </th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i>          </td><td>CMT        </td><td>2012-01-10 23:55:50.000000000</td><td>2012-01-11 00:03:39.000000000</td><td>1                </td><td>CRD           </td><td>1.7000000476837158 </td><td>-73.99468994140625</td><td>40.725032806396484</td><td>1.0        </td><td>0.0                 </td><td>-73.9759521484375  </td><td>40.73078155517578 </td><td>6.900000095367432</td><td>0.5        </td><td>0.5      </td><td>1.0             </td><td>0.0              </td><td>8.899999618530273</td><td>4                </td><td>242           </td><td>4               </td><td>178          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i>          </td><td>CMT        </td><td>2012-01-11 19:18:25.000000000</td><td>2012-01-11 19:26:10.000000000</td><td>1                </td><td>CSH           </td><td>1.100000023841858  </td><td>-73.98795318603516</td><td>40.75294876098633 </td><td>1.0        </td><td>0.0                 </td><td>-73.9945297241211  </td><td>40.76103973388672 </td><td>6.099999904632568</td><td>1.0        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>7.599999904632568</td><td>4                </td><td>140           </td><td>4               </td><td>48           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i>          </td><td>CMT        </td><td>2012-01-11 19:19:19.000000000</td><td>2012-01-11 19:48:15.000000000</td><td>2                </td><td>CRD           </td><td>18.0               </td><td>-73.78309631347656</td><td>40.6485481262207  </td><td>2.0        </td><td>0.0                 </td><td>-73.99613189697266 </td><td>40.747623443603516</td><td>45.0             </td><td>0.0        </td><td>0.5      </td><td>10.0600004196167</td><td>4.800000190734863</td><td>60.36000061035156</td><td>4                </td><td>89            </td><td>0               </td><td>61           </td></tr>\n",
       "<tr><td>...                                    </td><td>...        </td><td>...                          </td><td>...                          </td><td>...              </td><td>...           </td><td>...                </td><td>...               </td><td>...               </td><td>...        </td><td>...                 </td><td>...                </td><td>...               </td><td>...              </td><td>...        </td><td>...      </td><td>...             </td><td>...              </td><td>...              </td><td>...              </td><td>...           </td><td>...             </td><td>...          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,321</i></td><td>CMT        </td><td>2012-12-24 21:09:56.000000000</td><td>2012-12-24 21:13:22.000000000</td><td>1                </td><td>CSH           </td><td>0.30000001192092896</td><td>-73.96232604980469</td><td>40.77641296386719 </td><td>1.0        </td><td>0.0                 </td><td>-73.95834350585938 </td><td>40.774906158447266</td><td>4.0              </td><td>0.5        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>5.0              </td><td>4                </td><td>74            </td><td>4               </td><td>74           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,322</i></td><td>CMT        </td><td>2012-12-24 20:07:23.000000000</td><td>2012-12-24 20:20:18.000000000</td><td>1                </td><td>CSH           </td><td>1.100000023841858  </td><td>-73.97209167480469</td><td>40.75520706176758 </td><td>4.0        </td><td>0.0                 </td><td>-73.98250579833984 </td><td>40.76253890991211 </td><td>9.5              </td><td>0.5        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>10.5             </td><td>4                </td><td>234           </td><td>4               </td><td>201          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,323</i></td><td>CMT        </td><td>2012-12-24 09:57:17.000000000</td><td>2012-12-24 09:59:51.000000000</td><td>2                </td><td>CSH           </td><td>0.5                </td><td>-73.97518920898438</td><td>40.76108169555664 </td><td>1.0        </td><td>0.0                 </td><td>-73.98072814941406 </td><td>40.753299713134766</td><td>4.0              </td><td>0.0        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>4.5              </td><td>4                </td><td>187           </td><td>4               </td><td>64           </td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Add the borough and zone ids to the pickup and dropoff locations as virtual columns\n",
    "df['dropoff_borough'] = df.geo.inside_which_polygons(df.dropoff_longitude, df.dropoff_latitude, borough_polygons.values()).astype('uint8')\n",
    "df['dropoff_zone'] = df.geo.inside_which_polygons(df.dropoff_longitude, df.dropoff_latitude, zone_polygons.values()).astype('uint16')\n",
    "df['pickup_borough'] = df.geo.inside_which_polygons(df.pickup_longitude, df.pickup_latitude, borough_polygons.values()).astype('uint8')\n",
    "df['pickup_zone'] = df.geo.inside_which_polygons(df.pickup_longitude, df.pickup_latitude, zone_polygons.values()).astype('uint16')\n",
    "df.head_and_tail_print(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(Optional) We can materialize the virtual columns to gain in speed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:21:30.115303Z",
     "start_time": "2020-06-02T09:19:43.911435Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                                      </th><th>vendor_id  </th><th>pickup_datetime              </th><th>dropoff_datetime             </th><th>passenger_count  </th><th>payment_type  </th><th>trip_distance      </th><th>pickup_longitude  </th><th>pickup_latitude   </th><th>rate_code  </th><th>store_and_fwd_flag  </th><th>dropoff_longitude  </th><th>dropoff_latitude  </th><th>fare_amount      </th><th>surcharge  </th><th>mta_tax  </th><th>tip_amount      </th><th>tolls_amount     </th><th>total_amount     </th><th>dropoff_borough  </th><th>dropoff_zone  </th><th>pickup_borough  </th><th>pickup_zone  </th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i>          </td><td>CMT        </td><td>2012-01-10 23:55:50.000000000</td><td>2012-01-11 00:03:39.000000000</td><td>1                </td><td>CRD           </td><td>1.7000000476837158 </td><td>-73.99468994140625</td><td>40.725032806396484</td><td>1.0        </td><td>0.0                 </td><td>-73.9759521484375  </td><td>40.73078155517578 </td><td>6.900000095367432</td><td>0.5        </td><td>0.5      </td><td>1.0             </td><td>0.0              </td><td>8.899999618530273</td><td>4                </td><td>242           </td><td>4               </td><td>178          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i>          </td><td>CMT        </td><td>2012-01-11 19:18:25.000000000</td><td>2012-01-11 19:26:10.000000000</td><td>1                </td><td>CSH           </td><td>1.100000023841858  </td><td>-73.98795318603516</td><td>40.75294876098633 </td><td>1.0        </td><td>0.0                 </td><td>-73.9945297241211  </td><td>40.76103973388672 </td><td>6.099999904632568</td><td>1.0        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>7.599999904632568</td><td>4                </td><td>140           </td><td>4               </td><td>48           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i>          </td><td>CMT        </td><td>2012-01-11 19:19:19.000000000</td><td>2012-01-11 19:48:15.000000000</td><td>2                </td><td>CRD           </td><td>18.0               </td><td>-73.78309631347656</td><td>40.6485481262207  </td><td>2.0        </td><td>0.0                 </td><td>-73.99613189697266 </td><td>40.747623443603516</td><td>45.0             </td><td>0.0        </td><td>0.5      </td><td>10.0600004196167</td><td>4.800000190734863</td><td>60.36000061035156</td><td>4                </td><td>89            </td><td>0               </td><td>61           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>3</i>          </td><td>CMT        </td><td>2012-01-11 19:19:21.000000000</td><td>2012-01-11 19:27:00.000000000</td><td>1                </td><td>CRD           </td><td>1.7000000476837158 </td><td>-73.96751403808594</td><td>40.758453369140625</td><td>1.0        </td><td>0.0                 </td><td>-73.95658111572266 </td><td>40.779903411865234</td><td>6.900000095367432</td><td>1.0        </td><td>0.5      </td><td>1.0             </td><td>0.0              </td><td>9.399999618530273</td><td>4                </td><td>74            </td><td>4               </td><td>68           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>4</i>          </td><td>CMT        </td><td>2012-01-11 14:38:15.000000000</td><td>2012-01-11 14:43:51.000000000</td><td>1                </td><td>CSH           </td><td>1.2000000476837158 </td><td>-74.01131439208984</td><td>40.711448669433594</td><td>1.0        </td><td>0.0                 </td><td>-74.00286865234375 </td><td>40.72813034057617 </td><td>5.699999809265137</td><td>0.0        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>6.199999809265137</td><td>4                </td><td>29            </td><td>4               </td><td>260          </td></tr>\n",
       "<tr><td>...                                    </td><td>...        </td><td>...                          </td><td>...                          </td><td>...              </td><td>...           </td><td>...                </td><td>...               </td><td>...               </td><td>...        </td><td>...                 </td><td>...                </td><td>...               </td><td>...              </td><td>...        </td><td>...      </td><td>...             </td><td>...              </td><td>...              </td><td>...              </td><td>...           </td><td>...             </td><td>...          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,319</i></td><td>CMT        </td><td>2012-12-26 20:55:55.000000000</td><td>2012-12-26 21:27:18.000000000</td><td>1                </td><td>CSH           </td><td>21.100000381469727 </td><td>-73.77667999267578</td><td>40.64518737792969 </td><td>1.0        </td><td>0.0                 </td><td>-74.02727508544922 </td><td>40.61878204345703 </td><td>56.0             </td><td>0.5        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>57.0             </td><td>2                </td><td>114           </td><td>0               </td><td>61           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,320</i></td><td>CMT        </td><td>2012-12-24 07:10:44.000000000</td><td>2012-12-24 07:16:48.000000000</td><td>1                </td><td>CSH           </td><td>2.0999999046325684 </td><td>-73.99629211425781</td><td>40.73793411254883 </td><td>1.0        </td><td>0.0                 </td><td>-73.97488403320312 </td><td>40.75730514526367 </td><td>8.0              </td><td>0.0        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>8.5              </td><td>4                </td><td>64            </td><td>4               </td><td>217          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,321</i></td><td>CMT        </td><td>2012-12-24 21:09:56.000000000</td><td>2012-12-24 21:13:22.000000000</td><td>1                </td><td>CSH           </td><td>0.30000001192092896</td><td>-73.96232604980469</td><td>40.77641296386719 </td><td>1.0        </td><td>0.0                 </td><td>-73.95834350585938 </td><td>40.774906158447266</td><td>4.0              </td><td>0.5        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>5.0              </td><td>4                </td><td>74            </td><td>4               </td><td>74           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,322</i></td><td>CMT        </td><td>2012-12-24 20:07:23.000000000</td><td>2012-12-24 20:20:18.000000000</td><td>1                </td><td>CSH           </td><td>1.100000023841858  </td><td>-73.97209167480469</td><td>40.75520706176758 </td><td>4.0        </td><td>0.0                 </td><td>-73.98250579833984 </td><td>40.76253890991211 </td><td>9.5              </td><td>0.5        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>10.5             </td><td>4                </td><td>234           </td><td>4               </td><td>201          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>178,544,323</i></td><td>CMT        </td><td>2012-12-24 09:57:17.000000000</td><td>2012-12-24 09:59:51.000000000</td><td>2                </td><td>CSH           </td><td>0.5                </td><td>-73.97518920898438</td><td>40.76108169555664 </td><td>1.0        </td><td>0.0                 </td><td>-73.98072814941406 </td><td>40.753299713134766</td><td>4.0              </td><td>0.0        </td><td>0.5      </td><td>0.0             </td><td>0.0              </td><td>4.5              </td><td>4                </td><td>187           </td><td>4               </td><td>64           </td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "#            vendor_id    pickup_datetime                dropoff_datetime               passenger_count    payment_type    trip_distance        pickup_longitude    pickup_latitude     rate_code    store_and_fwd_flag    dropoff_longitude    dropoff_latitude    fare_amount        surcharge    mta_tax    tip_amount        tolls_amount       total_amount       dropoff_borough    dropoff_zone    pickup_borough    pickup_zone\n",
       "0            CMT          2012-01-10 23:55:50.000000000  2012-01-11 00:03:39.000000000  1                  CRD             1.7000000476837158   -73.99468994140625  40.725032806396484  1.0          0.0                   -73.9759521484375    40.73078155517578   6.900000095367432  0.5          0.5        1.0               0.0                8.899999618530273  4                  242             4                 178\n",
       "1            CMT          2012-01-11 19:18:25.000000000  2012-01-11 19:26:10.000000000  1                  CSH             1.100000023841858    -73.98795318603516  40.75294876098633   1.0          0.0                   -73.9945297241211    40.76103973388672   6.099999904632568  1.0          0.5        0.0               0.0                7.599999904632568  4                  140             4                 48\n",
       "2            CMT          2012-01-11 19:19:19.000000000  2012-01-11 19:48:15.000000000  2                  CRD             18.0                 -73.78309631347656  40.6485481262207    2.0          0.0                   -73.99613189697266   40.747623443603516  45.0               0.0          0.5        10.0600004196167  4.800000190734863  60.36000061035156  4                  89              0                 61\n",
       "3            CMT          2012-01-11 19:19:21.000000000  2012-01-11 19:27:00.000000000  1                  CRD             1.7000000476837158   -73.96751403808594  40.758453369140625  1.0          0.0                   -73.95658111572266   40.779903411865234  6.900000095367432  1.0          0.5        1.0               0.0                9.399999618530273  4                  74              4                 68\n",
       "4            CMT          2012-01-11 14:38:15.000000000  2012-01-11 14:43:51.000000000  1                  CSH             1.2000000476837158   -74.01131439208984  40.711448669433594  1.0          0.0                   -74.00286865234375   40.72813034057617   5.699999809265137  0.0          0.5        0.0               0.0                6.199999809265137  4                  29              4                 260\n",
       "...          ...          ...                            ...                            ...                ...             ...                  ...                 ...                 ...          ...                   ...                  ...                 ...                ...          ...        ...               ...                ...                ...                ...             ...               ...\n",
       "178,544,319  CMT          2012-12-26 20:55:55.000000000  2012-12-26 21:27:18.000000000  1                  CSH             21.100000381469727   -73.77667999267578  40.64518737792969   1.0          0.0                   -74.02727508544922   40.61878204345703   56.0               0.5          0.5        0.0               0.0                57.0               2                  114             0                 61\n",
       "178,544,320  CMT          2012-12-24 07:10:44.000000000  2012-12-24 07:16:48.000000000  1                  CSH             2.0999999046325684   -73.99629211425781  40.73793411254883   1.0          0.0                   -73.97488403320312   40.75730514526367   8.0                0.0          0.5        0.0               0.0                8.5                4                  64              4                 217\n",
       "178,544,321  CMT          2012-12-24 21:09:56.000000000  2012-12-24 21:13:22.000000000  1                  CSH             0.30000001192092896  -73.96232604980469  40.77641296386719   1.0          0.0                   -73.95834350585938   40.774906158447266  4.0                0.5          0.5        0.0               0.0                5.0                4                  74              4                 74\n",
       "178,544,322  CMT          2012-12-24 20:07:23.000000000  2012-12-24 20:20:18.000000000  1                  CSH             1.100000023841858    -73.97209167480469  40.75520706176758   4.0          0.0                   -73.98250579833984   40.76253890991211   9.5                0.5          0.5        0.0               0.0                10.5               4                  234             4                 201\n",
       "178,544,323  CMT          2012-12-24 09:57:17.000000000  2012-12-24 09:59:51.000000000  2                  CSH             0.5                  -73.97518920898438  40.76108169555664   1.0          0.0                   -73.98072814941406   40.753299713134766  4.0                0.0          0.5        0.0               0.0                4.5                4                  187             4                 64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.materialize(virtual_column='dropoff_borough', inplace=True)\n",
    "df.materialize(virtual_column='dropoff_zone', inplace=True)\n",
    "df.materialize(virtual_column='pickup_borough', inplace=True)\n",
    "df.materialize(virtual_column='pickup_zone', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create features of interest\n",
    "\n",
    "Let us create some features of interest that will be important for the web application."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:21:30.122429Z",
     "start_time": "2020-06-02T09:21:30.116220Z"
    }
   },
   "outputs": [],
   "source": [
    "# Time in transit (minutes) - This is the target variable\n",
    "df['trip_duration_min'] = (df.dropoff_datetime - df.pickup_datetime) / np.timedelta64(1, 'm')\n",
    "df['trip_duration_min'] = df.trip_duration_min.astype('float32')\n",
    "\n",
    "# Speed (miles per hour) - To be used for cleaning of the training data\n",
    "df['trip_speed_mph'] = df.trip_distance / ((df.dropoff_datetime - df.pickup_datetime) / np.timedelta64(1, 'h'))\n",
    "df['trip_speed_mph'] = df.trip_speed_mph.astype('float32')\n",
    "\n",
    "# Extract pickup hour, day and month\n",
    "df['pickup_hour'] = df.pickup_datetime.dt.hour.astype('int8')\n",
    "df['pickup_day'] = df.pickup_datetime.dt.dayofweek.astype('int8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning the data\n",
    "\n",
    "There are numerous outliers in the data, which we decide to filter out. If you are interested in the rationale behind the following filters, please read through [this article.](https://towardsdatascience.com/how-to-analyse-100s-of-gbs-of-data-on-your-laptop-with-python-f83363dda94)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:21:30.380603Z",
     "start_time": "2020-06-02T09:21:30.123279Z"
    }
   },
   "outputs": [],
   "source": [
    "# Drop missing values\n",
    "df = df.dropna(column_names=['dropoff_longitude', 'dropoff_latitude'])\n",
    "\n",
    "# Filter abnormal number of passengers\n",
    "df = df[(df.passenger_count>0) & (df.passenger_count<7)]\n",
    "\n",
    "# Select taxi trips have travelled maximum 7 miles (but also with non-zero distance).\n",
    "df = df[(df.trip_distance > 0) & (df.trip_distance < 10)]\n",
    "\n",
    "# Filter taxi trips that have durations longer than 25 minutes or that lasted less than 3 minutes\n",
    "df = df[(df.trip_duration_min > 1) & (df.trip_duration_min < 60)]\n",
    "\n",
    "# Filter out errouneous average trip speeds.\n",
    "df = df[(df.trip_speed_mph > 1) & (df.trip_speed_mph < 60)]\n",
    "\n",
    "# Select only valid rate code\n",
    "df = df[(df.trip_distance >= 1) & (df.trip_distance <= 6 )]\n",
    "\n",
    "# Select only trips that were paid for by cash or card\n",
    "df = df[df.payment_type.isin(['CSH', 'CRD'])]\n",
    "\n",
    "# Drop missing pickup and dropoff locations\n",
    "df = df.dropmissing(['dropoff_borough', 'dropoff_zone', 'pickup_borough', 'pickup_zone'])\n",
    "\n",
    "\n",
    "# # Make a selection based on the boundaries\n",
    "\n",
    "# # Define the NYC boundaries\n",
    "# long_min = -74.05\n",
    "# long_max = -73.75\n",
    "# lat_min = 40.58\n",
    "# lat_max = 40.90\n",
    "\n",
    "# df = df[(df.pickup_longitude > long_min)  & (df.pickup_longitude < long_max) & \\\n",
    "#                     (df.pickup_latitude > lat_min)    & (df.pickup_latitude < lat_max) & \\\n",
    "#                     (df.dropoff_longitude > long_min) & (df.dropoff_longitude < long_max) & \\\n",
    "#                     (df.dropoff_latitude > lat_min)   & (df.dropoff_latitude < lat_max)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See the number of taxi trips in the filtered DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:21:31.995708Z",
     "start_time": "2020-06-02T09:21:30.381553Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<string>:1: RuntimeWarning: divide by zero encountered in true_divide\n",
      "<string>:1: RuntimeWarning: invalid value encountered in true_divide\n",
      "<string>:1: RuntimeWarning: invalid value encountered in greater\n",
      "<string>:1: RuntimeWarning: invalid value encountered in less\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The filtered DataFrame contains 117,444,049 records.\n"
     ]
    }
   ],
   "source": [
    "print(f'The filtered DataFrame contains {len(df):,} records.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export the data\n",
    "\n",
    "Sort the data by pickup location, to achieve more organized data locallity, thus marginally improving performance of the dashboard."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:22:26.790168Z",
     "start_time": "2020-06-02T09:21:31.996718Z"
    }
   },
   "outputs": [],
   "source": [
    "df = df.sort(by=['pickup_zone', 'pickup_longitude', 'pickup_latitude'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Export the data to disk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:24:51.534520Z",
     "start_time": "2020-06-02T09:22:26.791205Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[########################################] 99.99% estimated time:     0.01s =  0.0m =  0.0h   "
     ]
    }
   ],
   "source": [
    "df.export_hdf5('/data/taxi/nyc_taxi_2012.hdf5', progress=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Verify that the data has been exported correctly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:24:51.570345Z",
     "start_time": "2020-06-02T09:24:51.536066Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                                      </th><th>vendor_id  </th><th>pickup_datetime              </th><th>dropoff_datetime             </th><th>passenger_count  </th><th>payment_type  </th><th>trip_distance     </th><th>pickup_longitude  </th><th>pickup_latitude   </th><th>rate_code  </th><th>store_and_fwd_flag  </th><th>dropoff_longitude  </th><th>dropoff_latitude  </th><th>fare_amount       </th><th>surcharge  </th><th>mta_tax  </th><th>tip_amount        </th><th>tolls_amount  </th><th>total_amount      </th><th>dropoff_borough  </th><th>dropoff_zone  </th><th>pickup_borough  </th><th>pickup_zone  </th><th>trip_duration_min  </th><th>trip_speed_mph    </th><th>pickup_hour  </th><th>pickup_day  </th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i>          </td><td>VTS        </td><td>2012-03-31 18:13:00.000000000</td><td>2012-03-31 18:24:00.000000000</td><td>1                </td><td>CRD           </td><td>2.2300000190734863</td><td>-73.87706756591797</td><td>40.61756134033203 </td><td>1.0        </td><td>nan                 </td><td>-73.86943817138672 </td><td>40.62184524536133 </td><td>8.100000381469727 </td><td>0.0        </td><td>0.5      </td><td>1.5               </td><td>0.0           </td><td>10.100000381469727</td><td>0                </td><td>0             </td><td>0               </td><td>0            </td><td>11.0               </td><td>12.163636207580566</td><td>18           </td><td>5           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i>          </td><td>VTS        </td><td>2012-01-31 02:17:00.000000000</td><td>2012-01-31 02:26:00.000000000</td><td>1                </td><td>CSH           </td><td>3.559999942779541 </td><td>-73.87632751464844</td><td>40.61985397338867 </td><td>1.0        </td><td>nan                 </td><td>-73.86920166015625 </td><td>40.62523651123047 </td><td>10.5              </td><td>0.5        </td><td>0.5      </td><td>0.0               </td><td>0.0           </td><td>11.5              </td><td>0                </td><td>0             </td><td>0               </td><td>0            </td><td>9.0                </td><td>23.733333587646484</td><td>2            </td><td>1           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i>          </td><td>VTS        </td><td>2012-03-24 20:19:00.000000000</td><td>2012-03-24 20:24:00.000000000</td><td>1                </td><td>CRD           </td><td>1.149999976158142 </td><td>-73.87577056884766</td><td>40.621028900146484</td><td>1.0        </td><td>nan                 </td><td>-73.85384368896484 </td><td>40.62257385253906 </td><td>5.300000190734863 </td><td>0.5        </td><td>0.5      </td><td>1.159999966621399 </td><td>0.0           </td><td>7.460000038146973 </td><td>0                </td><td>0             </td><td>0               </td><td>0            </td><td>5.0                </td><td>13.799999237060547</td><td>20           </td><td>5           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>3</i>          </td><td>VTS        </td><td>2012-09-09 00:57:00.000000000</td><td>2012-09-09 01:13:00.000000000</td><td>1                </td><td>CSH           </td><td>4.190000057220459 </td><td>-73.87551879882812</td><td>40.61985778808594 </td><td>1.0        </td><td>nan                 </td><td>-73.89090728759766 </td><td>40.63335037231445 </td><td>15.5              </td><td>0.5        </td><td>0.5      </td><td>0.0               </td><td>0.0           </td><td>16.5              </td><td>2                </td><td>5             </td><td>0               </td><td>0            </td><td>16.0               </td><td>15.71250057220459 </td><td>0            </td><td>6           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>4</i>          </td><td>VTS        </td><td>2012-04-24 18:59:00.000000000</td><td>2012-04-24 19:04:00.000000000</td><td>1                </td><td>CRD           </td><td>1.1799999475479126</td><td>-73.87530517578125</td><td>40.62031555175781 </td><td>1.0        </td><td>nan                 </td><td>-73.87605285644531 </td><td>40.61798858642578 </td><td>5.300000190734863 </td><td>1.0        </td><td>0.5      </td><td>1.2599999904632568</td><td>0.0           </td><td>8.0600004196167   </td><td>0                </td><td>0             </td><td>0               </td><td>0            </td><td>5.0                </td><td>14.15999984741211 </td><td>18           </td><td>1           </td></tr>\n",
       "<tr><td>...                                    </td><td>...        </td><td>...                          </td><td>...                          </td><td>...              </td><td>...           </td><td>...               </td><td>...               </td><td>...               </td><td>...        </td><td>...                 </td><td>...                </td><td>...               </td><td>...               </td><td>...        </td><td>...      </td><td>...               </td><td>...           </td><td>...               </td><td>...              </td><td>...           </td><td>...             </td><td>...          </td><td>...                </td><td>...               </td><td>...          </td><td>...         </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>117,444,044</i></td><td>VTS        </td><td>2012-10-20 05:38:00.000000000</td><td>2012-10-20 05:49:00.000000000</td><td>1                </td><td>CRD           </td><td>4.349999904632568 </td><td>-74.00861358642578</td><td>40.711395263671875</td><td>1.0        </td><td>nan                 </td><td>-73.98361206054688 </td><td>40.76372146606445 </td><td>13.5              </td><td>0.5        </td><td>0.5      </td><td>3.5               </td><td>0.0           </td><td>18.0              </td><td>4                </td><td>234           </td><td>4               </td><td>260          </td><td>11.0               </td><td>23.727272033691406</td><td>5            </td><td>5           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>117,444,045</i></td><td>VTS        </td><td>2012-02-24 15:12:00.000000000</td><td>2012-02-24 15:28:00.000000000</td><td>4                </td><td>CSH           </td><td>3.609999895095825 </td><td>-74.00860595703125</td><td>40.71139144897461 </td><td>1.0        </td><td>nan                 </td><td>-73.99629211425781 </td><td>40.75292205810547 </td><td>12.5              </td><td>0.0        </td><td>0.5      </td><td>0.0               </td><td>0.0           </td><td>13.0              </td><td>4                </td><td>150           </td><td>4               </td><td>260          </td><td>16.0               </td><td>13.53749942779541 </td><td>15           </td><td>4           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>117,444,046</i></td><td>CMT        </td><td>2012-03-04 17:28:40.000000000</td><td>2012-03-04 17:45:05.000000000</td><td>1                </td><td>CSH           </td><td>4.0               </td><td>-74.00860595703125</td><td>40.71139144897461 </td><td>1.0        </td><td>0.0                 </td><td>-73.97952270507812 </td><td>40.76235580444336 </td><td>12.100000381469727</td><td>0.0        </td><td>0.5      </td><td>0.0               </td><td>0.0           </td><td>12.600000381469727</td><td>4                </td><td>234           </td><td>4               </td><td>260          </td><td>16.41666603088379  </td><td>14.61928939819336 </td><td>17           </td><td>6           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>117,444,047</i></td><td>VTS        </td><td>2012-08-18 19:26:00.000000000</td><td>2012-08-18 19:43:00.000000000</td><td>1                </td><td>CRD           </td><td>4.78000020980835  </td><td>-74.00860595703125</td><td>40.71139144897461 </td><td>1.0        </td><td>nan                 </td><td>-73.98592376708984 </td><td>40.76795196533203 </td><td>14.100000381469727</td><td>0.0        </td><td>0.5      </td><td>1.0               </td><td>0.0           </td><td>15.600000381469727</td><td>4                </td><td>139           </td><td>4               </td><td>260          </td><td>17.0               </td><td>16.870588302612305</td><td>19           </td><td>5           </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>117,444,048</i></td><td>CMT        </td><td>2012-09-09 18:47:12.000000000</td><td>2012-09-09 18:58:14.000000000</td><td>1                </td><td>CRD           </td><td>2.4000000953674316</td><td>-74.00860595703125</td><td>40.71139144897461 </td><td>1.0        </td><td>0.0                 </td><td>-73.99994659423828 </td><td>40.741336822509766</td><td>10.0              </td><td>0.0        </td><td>0.5      </td><td>2.0999999046325684</td><td>0.0           </td><td>12.600000381469727</td><td>4                </td><td>9             </td><td>4               </td><td>260          </td><td>11.033333778381348 </td><td>13.051360130310059</td><td>18           </td><td>6           </td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "#            vendor_id    pickup_datetime                dropoff_datetime               passenger_count    payment_type    trip_distance       pickup_longitude    pickup_latitude     rate_code    store_and_fwd_flag    dropoff_longitude    dropoff_latitude    fare_amount         surcharge    mta_tax    tip_amount          tolls_amount    total_amount        dropoff_borough    dropoff_zone    pickup_borough    pickup_zone    trip_duration_min    trip_speed_mph      pickup_hour    pickup_day\n",
       "0            VTS          2012-03-31 18:13:00.000000000  2012-03-31 18:24:00.000000000  1                  CRD             2.2300000190734863  -73.87706756591797  40.61756134033203   1.0          nan                   -73.86943817138672   40.62184524536133   8.100000381469727   0.0          0.5        1.5                 0.0             10.100000381469727  0                  0               0                 0              11.0                 12.163636207580566  18             5\n",
       "1            VTS          2012-01-31 02:17:00.000000000  2012-01-31 02:26:00.000000000  1                  CSH             3.559999942779541   -73.87632751464844  40.61985397338867   1.0          nan                   -73.86920166015625   40.62523651123047   10.5                0.5          0.5        0.0                 0.0             11.5                0                  0               0                 0              9.0                  23.733333587646484  2              1\n",
       "2            VTS          2012-03-24 20:19:00.000000000  2012-03-24 20:24:00.000000000  1                  CRD             1.149999976158142   -73.87577056884766  40.621028900146484  1.0          nan                   -73.85384368896484   40.62257385253906   5.300000190734863   0.5          0.5        1.159999966621399   0.0             7.460000038146973   0                  0               0                 0              5.0                  13.799999237060547  20             5\n",
       "3            VTS          2012-09-09 00:57:00.000000000  2012-09-09 01:13:00.000000000  1                  CSH             4.190000057220459   -73.87551879882812  40.61985778808594   1.0          nan                   -73.89090728759766   40.63335037231445   15.5                0.5          0.5        0.0                 0.0             16.5                2                  5               0                 0              16.0                 15.71250057220459   0              6\n",
       "4            VTS          2012-04-24 18:59:00.000000000  2012-04-24 19:04:00.000000000  1                  CRD             1.1799999475479126  -73.87530517578125  40.62031555175781   1.0          nan                   -73.87605285644531   40.61798858642578   5.300000190734863   1.0          0.5        1.2599999904632568  0.0             8.0600004196167     0                  0               0                 0              5.0                  14.15999984741211   18             1\n",
       "...          ...          ...                            ...                            ...                ...             ...                 ...                 ...                 ...          ...                   ...                  ...                 ...                 ...          ...        ...                 ...             ...                 ...                ...             ...               ...            ...                  ...                 ...            ...\n",
       "117,444,044  VTS          2012-10-20 05:38:00.000000000  2012-10-20 05:49:00.000000000  1                  CRD             4.349999904632568   -74.00861358642578  40.711395263671875  1.0          nan                   -73.98361206054688   40.76372146606445   13.5                0.5          0.5        3.5                 0.0             18.0                4                  234             4                 260            11.0                 23.727272033691406  5              5\n",
       "117,444,045  VTS          2012-02-24 15:12:00.000000000  2012-02-24 15:28:00.000000000  4                  CSH             3.609999895095825   -74.00860595703125  40.71139144897461   1.0          nan                   -73.99629211425781   40.75292205810547   12.5                0.0          0.5        0.0                 0.0             13.0                4                  150             4                 260            16.0                 13.53749942779541   15             4\n",
       "117,444,046  CMT          2012-03-04 17:28:40.000000000  2012-03-04 17:45:05.000000000  1                  CSH             4.0                 -74.00860595703125  40.71139144897461   1.0          0.0                   -73.97952270507812   40.76235580444336   12.100000381469727  0.0          0.5        0.0                 0.0             12.600000381469727  4                  234             4                 260            16.41666603088379    14.61928939819336   17             6\n",
       "117,444,047  VTS          2012-08-18 19:26:00.000000000  2012-08-18 19:43:00.000000000  1                  CRD             4.78000020980835    -74.00860595703125  40.71139144897461   1.0          nan                   -73.98592376708984   40.76795196533203   14.100000381469727  0.0          0.5        1.0                 0.0             15.600000381469727  4                  139             4                 260            17.0                 16.870588302612305  19             5\n",
       "117,444,048  CMT          2012-09-09 18:47:12.000000000  2012-09-09 18:58:14.000000000  1                  CRD             2.4000000953674316  -74.00860595703125  40.71139144897461   1.0          0.0                   -73.99994659423828   40.741336822509766  10.0                0.0          0.5        2.0999999046325684  0.0             12.600000381469727  4                  9               4                 260            11.033333778381348   13.051360130310059  18             6"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in the exported dataset\n",
    "df = vaex.open('/data/taxi/nyc_taxi_2012.hdf5')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:24:51.575180Z",
     "start_time": "2020-06-02T09:24:51.571378Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "vendor_id              <class 'str'>\n",
       "pickup_datetime       datetime64[ns]\n",
       "dropoff_datetime      datetime64[ns]\n",
       "passenger_count                int64\n",
       "payment_type           <class 'str'>\n",
       "trip_distance                float32\n",
       "pickup_longitude             float32\n",
       "pickup_latitude              float32\n",
       "rate_code                    float32\n",
       "store_and_fwd_flag           float32\n",
       "dropoff_longitude            float32\n",
       "dropoff_latitude             float32\n",
       "fare_amount                  float32\n",
       "surcharge                    float32\n",
       "mta_tax                      float32\n",
       "tip_amount                   float32\n",
       "tolls_amount                 float32\n",
       "total_amount                 float32\n",
       "dropoff_borough                uint8\n",
       "dropoff_zone                  uint16\n",
       "pickup_borough                 uint8\n",
       "pickup_zone                   uint16\n",
       "trip_duration_min            float32\n",
       "trip_speed_mph               float32\n",
       "pickup_hour                     int8\n",
       "pickup_day                      int8\n",
       "dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check dtypes\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Before you go"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For convenience we have also placed this exported dataset on S3, and you can download it locally simply by:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-02T09:24:51.579404Z",
     "start_time": "2020-06-02T09:24:51.576272Z"
    }
   },
   "outputs": [],
   "source": [
    "df = vaex.open('s3://vaex/taxi/yellow_taxi_2012_zones.hdf5?anon=true')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}