Import public NYC taxi and for-hire vehicle (Uber, Lyft) trip data into a PostgreSQL or ClickHouse database
MIT License
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:
COPY
commandAs 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.
See the clickhouse
directory
Both are available via Homebrew on Mac
From CRAN
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
./download_raw_data.sh
./initialize_database.sh
./import_yellow_taxi_trip_data.sh
./import_green_taxi_trip_data.sh
./import_fhv_taxi_trip_data.sh
./import_fhvhv_trip_data.sh
Note that the full import process might take several hours or possibly even over a day depending on computing power
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 Junofhv_bases
maps fhv_trips
to base names and "doing business as" labels, which include ride-hailing app namesnyct2010
table contains NYC census tracts plus the Newark Airport. It also maps census tracts to NYC's official neighborhood tabulation areastaxi_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 IDscentral_park_weather_observations
has summary weather data by dateThese 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:
ruby import_statistics_data.rb
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 citibike_comparison/
folder
Code in support of the 2017 update to the original post lives in the analysis/2017_update/
folder
[email protected], or open a GitHub issue