/nyc-taxi-data

Import public NYC taxi and for-hire vehicle (Uber, Lyft, etc.) trip data into PostgreSQL database

Primary LanguageRMIT LicenseMIT

New York City Taxi and For-Hire Vehicle Data

Code originally in support of this post: "Analyzing 1.1 Billion NYC Taxi and Uber Trips, with a Vengeance"

This repo provides scripts to download, process, and analyze data for billions of taxi and for-hire vehicle (Uber, Lyft, etc.) trips originating in New York City since 2009. Most of the raw data comes from the NYC Taxi & Limousine Commission.

The data is stored in a PostgreSQL database, and uses PostGIS for spatial calculations.

Statistics through December 31, 2019:

  • 2.63 billion total trips
    • 1.69 billion taxi
    • 935 million for-hire vehicle
  • 291 GB of raw data
  • Database takes up 391 GB on disk with minimal indexes

Instructions

1. Install PostgreSQL and PostGIS

Both are available via Homebrew on Mac

2. Download raw data

./download_raw_data.sh && ./remove_bad_rows.sh

The remove_bad_rows.sh script fixes two particular files that have a few rows with too many columns. See the "data issues" section below for more.

Note that the raw data is hundreds of GB, so it will take a while to download.

3. Initialize database and set up schema

./initialize_database.sh

4. Import taxi and FHV data

./import_trip_data.sh
./import_fhv_trip_data.sh

The full import process takes ~36 hours on a 2013 MacBook Pro with 16 GB of RAM.

5. Optional: download and import 2014 Uber data

The FiveThirtyEight Uber dataset contains Uber trip records from Apr–Sep 2014. Uber and other FHV (Lyft, Juno, Via, etc.) data is available since Jan 2015 in the TLC's data.

./download_raw_2014_uber_data.sh
./import_2014_uber_trip_data.sh

6. Analysis

Additional Postgres and R scripts for analysis are in the analysis/ folder, or you can do your own!

Schema

  • trips table contains all yellow and green taxi trips. Each trip has a cab_type_id, which references the cab_types table and refers to one of yellow or green
  • fhv_trips table contains all for-hire vehicle trip records, including ride-hailing apps Uber, Lyft, Via, and Juno
  • fhv_bases maps fhv_trips to base names and "doing business as" labels, which include ride-hailing app names
  • nyct2010 table contains NYC census tracts plus the Newark Airport. It also maps census tracts to NYC's official neighborhood tabulation areas
  • taxi_zones table contains the TLC's official taxi zone boundaries. Starting in July 2016, the TLC no longer provides pickup and dropoff coordinates. Instead, each trip comes with taxi zone pickup and dropoff location IDs
  • central_park_weather_observations has summary weather data by date

Other data sources

These are bundled with the repository, so no need to download separately, but:

Data issues encountered

  • Remove carriage returns and empty lines from TLC data before passing to Postgres COPY command
  • Some raw data files have extra columns with empty data, had to create dummy columns junk1 and junk2 to absorb them
  • Two of the yellow taxi raw data files had a small number of rows containing extra columns. I discarded these rows
  • The official NYC neighborhood tabulation areas (NTAs) included in the census tracts shapefile are not exactly what I would have expected. Some of them are bizarrely large and contain more than one neighborhood, e.g. "Hudson Yards-Chelsea-Flat Iron-Union Square", while others are confusingly named, e.g. "North Side-South Side" for what I'd call "Williamsburg", and "Williamsburg" for what I'd call "South Williamsburg". In a few instances I modified NTA names, but I kept the NTA geographic definitions
  • The shapefile includes only NYC census tracts. Trips to New Jersey, Long Island, Westchester, and Connecticut are not mapped to census tracts, with the exception of the Newark Airport

Why not use BigQuery or Redshift?

Google BigQuery and Amazon Redshift would probably provide significant performance improvements over PostgreSQL. A lot of the data is already available on BigQuery, but in scattered tables, and each trip has only latitude and longitude coordinates, not census tracts and neighborhoods. PostGIS seemed like the easiest way to map coordinates to census tracts. Once the mapping is complete, it might make sense to load the data back into BigQuery or Redshift to make the analysis faster. Note that BigQuery and Redshift cost some amount of money, while PostgreSQL and PostGIS are free.

Mark Litwintschik has used the taxi dataset to benchmark performance of many different technology stacks, his summary is here: http://tech.marksblogg.com/benchmarks.html

TLC summary statistics

There's a Ruby script in the tlc_statistics/ folder to import data from the TLC's summary statistics reports:

ruby import_statistics_data.rb

These summary statistics are used in the NYC Taxi & Ridehailing Stats dashboard

Taxi vs. Citi Bike comparison

Code in support of the post "When Are Citi Bikes Faster Than Taxis in New York City?" lives in the citibike_comparison/ folder

2017 update

Code in support of the 2017 update to the original post lives in the analysis/2017_update/ folder

Questions/issues/contact

todd@toddwschneider.com, or open a GitHub issue