Scripts to download, process, and analyze data from 3+ billion taxi and for-hire vehicle (Uber, Lyft, etc.) trips originating in New York City since 2009. There are separate sets of scripts for storing data in either a PostgreSQL or ClickHouse database.
Most of the raw data comes from the NYC Taxi & Limousine Commission.
The repo was created originally in support of this post: Analyzing 1.1 Billion NYC Taxi and Uber Trips, with a Vengeance
The TLC changed the raw data format from CSV to Apache Parquet in May 2022, including a full replacement of all historical files. This repo is now updated to handle the Parquet files in one of two ways:
As part of the May 2022 update, the TLC added several new columns to the High Volume For-Hire Vehicle (Uber, Lyft) trip files, including information about passenger fares, driver pay, and time spent waiting for passengers. These new fields are available back to February 2019.
This repo no longer works with the old CSV files provided by the TLC. Those files are no longer available to download from the TLC's website, but if you happen to have them lying around and want to use this repo, you should look at this older verion of the code from before the Parquet file format change.
Both are available via Homebrew on Mac
Note that R used to be optional for this repo, but is required starting with the 2022 file format change. The scripts use R to convert Parquet files to CSV before loading into Postgres. There are other ways to convert from Parquet to CSV that wouldn't require R, but I found that R's
arrow package was faster than some of the other CLI tools I tried
Note that the full import process might take several hours or possibly even over a day depending on computing power
tripstable contains all yellow and green taxi trips. Each trip has a
cab_type_id, which references the
cab_typestable and refers to one of
fhv_tripstable contains all for-hire vehicle trip records, including ride-hailing apps Uber, Lyft, Via, and Juno
fhv_tripsto base names and "doing business as" labels, which include ride-hailing app names
nyct2010table contains NYC census tracts plus the Newark Airport. It also maps census tracts to NYC's official neighborhood tabulation areas
taxi_zonestable 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_observationshas summary weather data by date
These are bundled with the repository, so no need to download separately, but:
Mark Litwintschik has used the taxi dataset to benchmark performance of many different technology stacks, including PostgreSQL and ClickHouse. His summary is here: https://tech.marksblogg.com/benchmarks.html
There's a Ruby script in the
tlc_statistics/ folder to import data from the TLC's summary statistics reports:
These summary statistics are used in the NYC Taxi & Ridehailing Stats dashboard
Code in support of the post When Are Citi Bikes Faster Than Taxis in New York City? lives in the
Code in support of the 2017 update to the original post lives in the
[email protected], or open a GitHub issue