NYC yellow taxi trips

lhbox duckdb --catalog commons/pubdata

SELECT pz.borough AS from_borough, pz.zone AS from_zone,
       dz.borough AS to_borough, dz.zone AS to_zone,
       count(*) AS trips,
       round(avg(t.trip_distance_mi), 2) AS avg_miles,
       round(avg(t.total_amount), 2) AS avg_total_usd
FROM commons_pubdata.transport.nyc_taxi_trips AS t
JOIN commons_pubdata.transport.nyc_taxi_zones AS pz ON pz.location_id = t.pickup_location_id
JOIN commons_pubdata.transport.nyc_taxi_zones AS dz ON dz.location_id = t.dropoff_location_id
GROUP BY 1, 2, 3, 4 ORDER BY trips DESC LIMIT 15;

INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
CREATE SECRET pubdata (TYPE S3, ENDPOINT 'lakehousebox-public-data.b-cdn.net', URL_STYLE 'path', REGION 'us-east-1');
ATTACH ':memory:' AS pubdata;
CREATE SCHEMA pubdata.transport;
CREATE VIEW pubdata.transport.nyc_taxi_trips AS SELECT * FROM iceberg_scan('s3://commons--pubdata/transport/nyc_taxi_trips');
CREATE VIEW pubdata.transport.nyc_taxi_zones AS SELECT * FROM iceberg_scan('s3://commons--pubdata/transport/nyc_taxi_zones');

SELECT pz.borough AS from_borough, pz.zone AS from_zone,
       dz.borough AS to_borough, dz.zone AS to_zone,
       count(*) AS trips,
       round(avg(t.trip_distance_mi), 2) AS avg_miles,
       round(avg(t.total_amount), 2) AS avg_total_usd
FROM pubdata.transport.nyc_taxi_trips AS t
JOIN pubdata.transport.nyc_taxi_zones AS pz ON pz.location_id = t.pickup_location_id
JOIN pubdata.transport.nyc_taxi_zones AS dz ON dz.location_id = t.dropoff_location_id
GROUP BY 1, 2, 3, 4 ORDER BY trips DESC LIMIT 15;

# NYC yellow taxi trips

*Every New York City yellow cab trip of the latest published month, with TLC taxi zones.*

Trip-level records from the New York City Taxi and Limousine Commission (TLC) for all yellow medallion taxicab rides in the newest published calendar month (~3.3 million trips per month), together with the companion lookup table `transport.nyc_taxi_zones` (all 265 TLC zones across Manhattan, Brooklyn, Queens, the Bronx, Staten Island and Newark Airport). Each row records pickup and drop-off timestamps, the pickup calendar `date`, passenger count, distance in miles, pickup and drop-off `location_id`, rate code, payment type, itemized fare, tip, tolls, congestion surcharge, airport fee and total amount.

## At a glance

| | |
|---|---|
| Table | `pubdata.transport.nyc_taxi_trips` |
| Category | Transport · Transport, Time series |
| Rows | n/a |
| Size | n/a data file(s) |
| Iceberg format | v? |
| Last refreshed | not loaded yet |
| Source version | n/a |
| Refresh cadence | monthly (replace) |
| Coverage | New York City (265 TLC taxi zones across the five boroughs and EWR); latest published calendar month (~3.3 million trips), replaced monthly |

## Origin

- **Source:** [New York City Taxi and Limousine Commission (TLC) Trip Record Data](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page)
- **Licence:** Public domain / NYC Open Data Terms of Use
- **Attribution:** Source: NYC Taxi and Limousine Commission (TLC) Trip Record Data, City of New York.
- **Recipe:** `pubdata/recipes/nyc_taxi.py` in the LakehouseBox repository; registry entry `pubdata/registry/nyc_taxi.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `pickup_time` | timestamp | meter engagement timestamp (local NYC time) |
| `dropoff_time` | timestamp | meter disengagement timestamp (local NYC time) |
| `date` | date | pickup calendar date (joins calendars.public_holidays, weather.forecast and reference.fx_rates) |
| `vendor_id` | int | TPEP provider code (1 = Creative Mobile Technologies, 2 = Curb Mobility, 6 = Myle, 7 = Helix) |
| `passenger_count` | int | driver-entered or meter passenger count |
| `trip_distance_mi` | double | elapsed trip distance in miles reported by the taximeter |
| `rate_code_id` | int | final rate code (1 = Standard, 2 = JFK, 3 = Newark, 4 = Nassau/Westchester, 5 = Negotiated, 6 = Group) |
| `pickup_location_id` | int | TLC taxi zone where the meter was engaged (joins transport.nyc_taxi_zones) |
| `dropoff_location_id` | int | TLC taxi zone where the meter was disengaged (joins transport.nyc_taxi_zones) |
| `payment_type` | int | 1 = Credit card, 2 = Cash, 3 = No charge, 4 = Dispute, 5 = Unknown |
| `fare_amount` | double | time-and-distance fare calculated by the meter, USD |
| `tip_amount` | double | tip amount, USD (automatically populated for credit card tips; cash tips are not recorded) |
| `tolls_amount` | double | total amount of all tolls paid in trip, USD |
| `congestion_surcharge` | double | New York State congestion surcharge collected in trip, USD |
| `airport_fee` | double | pickup fee at LaGuardia and John F. Kennedy airports, USD |
| `total_amount` | double | total amount charged to passengers (does not include cash tips), USD |
| `year_month` | string | published TLC file month, YYYY-MM |

**Join keys:** `date`, `pickup_location_id`, `dropoff_location_id`.

## Companion tables

- `pubdata.transport.nyc_taxi_zones`: the 265 NYC TLC taxi zones: location_id, borough, zone name and service_zone (Yellow Zone, Boro Zone, Airports) (n/a rows)

## For agents

Outlier timestamps outside the published calendar month (clock errors in the raw TLC file) are filtered out on load so
`date` always lies within `year_month`. `tip_amount` is recorded only for credit card rides (`payment_type = 1`);
filter on `payment_type = 1` when computing tip percentages. Join `pickup_location_id` or `dropoff_location_id` to
`transport.nyc_taxi_zones.location_id` for borough and neighborhood names.

## Example SQL (DuckDB)

```sql
-- top 15 pickup-to-dropoff routes by trip volume
SELECT pz.borough AS from_borough, pz.zone AS from_zone,
       dz.borough AS to_borough, dz.zone AS to_zone,
       count(*) AS trips,
       round(avg(t.trip_distance_mi), 2) AS avg_miles,
       round(avg(t.total_amount), 2) AS avg_total_usd
FROM pubdata.transport.nyc_taxi_trips AS t
JOIN pubdata.transport.nyc_taxi_zones AS pz ON pz.location_id = t.pickup_location_id
JOIN pubdata.transport.nyc_taxi_zones AS dz ON dz.location_id = t.dropoff_location_id
GROUP BY 1, 2, 3, 4 ORDER BY trips DESC LIMIT 15;
-- daily NYC taxi trips and average credit-card tip % by day of week
SELECT date, dayname(date) AS weekday, count(*) AS trips,
       round(avg(tip_amount / NULLIF(fare_amount, 0)) FILTER (WHERE payment_type = 1 AND fare_amount > 0) * 100, 1) AS card_tip_pct
FROM pubdata.transport.nyc_taxi_trips
GROUP BY 1, 2 ORDER BY date;
```

## How to read it

With a LakehouseBox account:

```bash
# with a LakehouseBox account (any organisation): the public catalog by name, read-only
lhbox duckdb --catalog commons/pubdata     # opens DuckDB attached as `commons_pubdata`
SELECT * FROM commons_pubdata.transport.nyc_taxi_trips LIMIT 10;
```

Public URL of the table's storage: `https://s3.lakehousebox.com/commons--pubdata/transport/nyc_taxi_trips/`. This card: `https://lakehousebox.com/pubdata/cards/nyc_taxi.md`; the page: https://lakehousebox.com/public-data/nyc_taxi. Every dataset as a table: `commons_pubdata.meta.datasets`; as Markdown: https://lakehousebox.com/public-data.md.