Taxi Trip Records Explained: What Each Field in the TLC Data Means
On this page
Problem statement
A yellow-taxi trip record has twenty columns, and most of them are codes: VendorID 1, 2, 6 or 7, RatecodeID 1 to 6 or 99, payment_type 0 to 6, and nine money columns that are supposed to add up to total_amount. Read without the data dictionary, they produce confident wrong answers — tips that average near zero because cash tips are not recorded, airport fares that look like outliers but are a flat rate, fees that are negative. This page goes through the fields on the 4,322,960 trips of June 2025: what each code means, how often it occurs, and which columns can be trusted together.
Quick answer
Each row is one metered trip: who recorded it (VendorID), when and where it started and ended (times and zone IDs), how the fare was set (RatecodeID), how it was paid (payment_type) and the money in parts. The parts add up to the total in most rows but not all, and a fifth of rows have no rate code, passenger count or surcharge at all.
parts = ["fare_amount", "extra", "mta_tax", "tip_amount", "tolls_amount", "improvement_surcharge",
"congestion_surcharge", "Airport_fee", "cbd_congestion_fee"]
chk = con.sql(f"""select count(*) as n,
avg((abs(total_amount - ({' + '.join(f'coalesce({c}, 0)' for c in parts)})) < 0.01)::int) as adds_up
from june""").df()
print(f"June 2025: {chk.n[0]:,} trips; total_amount equals the sum of its nine parts in {chk.adds_up[0]:.1%}")
by_type = con.sql(f"""select payment_type = 0 as p0,
avg((abs(total_amount - ({' + '.join(f'coalesce({c}, 0)' for c in parts)})) < 0.01)::int) as adds_up from june group by 1 order by 1""").df()
print("adds up, by whether payment type is 0:", by_type.set_index("p0").adds_up.round(3).to_dict())June 2025: 4,322,960 trips; total_amount equals the sum of its nine parts in 62.4%
adds up, by whether payment type is 0: {False: 0.796, True: 0.185}
In June 2025 the nine parts add up to total_amount in 62.4% of trips. Split by payment type the picture is clearer: 79.6% of ordinary trips add up, against 18.5% of payment-type-0 trips, whose surcharge columns are empty. Neither the dictionary nor the files explain the remaining differences, so a study of fare components should check the sum rather than assume it.
The codes, one column at a time
1. Who recorded the trip
v = con.sql("select VendorID, count(*) as n from june group by 1 order by 1").df()
v["share"] = (v.n / v.n.sum()).round(4)
print(v.to_string(index=False)) VendorID n share
1 830851 0.1922
2 3423020 0.7918
6 1516 0.0004
7 67573 0.0156
The vendor is the company whose meter system recorded the trip, not the cab owner. In the TLC's 2025 dictionary, 1 is Creative Mobile Technologies, 2 is Curb Mobility, 6 is Myle Technologies and 7 is Helix. Curb records 79.18% of trips; 6 and 7 are new in recent files and show up as unfamiliar codes in older code.
2. How the fare was set
r = con.sql("""select RatecodeID, count(*) as n, median(fare_amount) as median_fare, median(trip_distance) as median_miles
from june group by 1 order by 1""").df()
print(r.to_string(index=False)) RatecodeID n median_fare median_miles
1 2881409 12.80 1.62
2 119754 70.00 17.71
3 12400 76.20 14.22
4 10610 98.90 18.92
5 38456 65.97 1.10
6 4 450.22 0.00
99 47381 31.50 7.20
<NA> 1212946 18.06 2.62
The rate code is the tariff: 1 standard metered fare (median $12.80), 2 the JFK–Manhattan flat fare (exactly $70.00), 3 Newark, 4 Nassau or Westchester, 5 negotiated, 6 group ride. Code 99 means unknown, and 1,212,946 rows have no rate code at all. A fare analysis that treats all trips as metered will find a cluster of $70 "outliers" that are the flat fare.
3. How it was paid
p = con.sql("""select payment_type, count(*) as n, avg(tip_amount) as mean_tip from june group by 1 order by 1""").df()
p["share"] = (p.n / p.n.sum()).round(4)
print(p.round(2).to_string(index=False)) payment_type n mean_tip share
0 1212946 0.29 0.28
1 2595374 4.45 0.60
2 386552 0.00 0.09
3 27923 0.01 0.01
4 100163 0.03 0.02
5 2 0.00 0.00
Card payments (type 1) are 60% of trips and the only ones whose tips are recorded, averaging $4.45. Cash (type 2) is 9%, with a recorded tip of $0.00 because cash tips are not entered. Type 0 is 28% of June's trips: the 2025 dictionary calls it a flex-fare trip. Types 3 and 4 are no charge and dispute.
4. The surcharges and what triggers them
s = con.sql("""select avg((congestion_surcharge > 0)::int) as congestion, avg((Airport_fee > 0)::int) as airport,
avg((cbd_congestion_fee > 0)::int) as cbd, avg((tolls_amount > 0)::int) as tolls,
avg((store_and_fwd_flag = 'Y')::int) as stored from june""").df()
print("share of June 2025 trips with each charge:", s.round(3).iloc[0].to_dict())share of June 2025 trips with each charge: {'congestion': 0.891, 'airport': 0.093, 'cbd': 0.723, 'tolls': 0.069, 'stored': 0.003}
Each surcharge has its own trigger. The state congestion surcharge, for trips in Manhattan south of 96th Street, appears on 89.1% of trips; the congestion relief zone fee, for trips touching Manhattan below 60th Street, on 72.3%; the airport fee on 9.3%; tolls on 6.9%. store_and_fwd_flag is Y on 0.3% of trips, recorded while the meter had no connection and sent later.
Where the money goes, and negative rows
The data used on this page
The NYC Taxi & Limousine Commission's yellow-taxi trip records for June 2025, and its taxi zone lookup; the field definitions are the TLC's data dictionary.
from pathlib import Path
import duckdb
import geopandas as gpd
import numpy as np
import pandas as pd
import requests
Path("data").mkdir(exist_ok=True)
TLC = "https://d37ci6vzurychx.cloudfront.net/trip-data/"
FILES = ['yellow_tripdata_2025-06.parquet']
for name in FILES:
if not Path(f"data/{name}").exists():
with requests.get(TLC + name, stream=True, timeout=600) as r: # 50-80 MB a month
r.raise_for_status()
with open(f"data/{name}", "wb") as f:
for chunk in r.iter_content(1 << 20):
f.write(chunk)
# zones and lookup as the TLC served them on 15 October 2026 (it reissues them; the live copies are
# https://d37ci6vzurychx.cloudfront.net/misc/taxi_zones.zip and .../misc/taxi_zone_lookup.csv)
for name in ["nyc-taxi-zones-2026-10-15.zip", "nyc-taxi-zone-lookup-2026-10-15.csv"]:
if not Path(f"data/{name}").exists():
Path(f"data/{name}").write_bytes(requests.get(f"https://data.spatialworkflow.io/{name}", timeout=300).content)
zones = gpd.read_file("zip://data/nyc-taxi-zones-2026-10-15.zip!taxi_zones/taxi_zones.shp") # EPSG:2263, feet
lookup = pd.read_csv("data/nyc-taxi-zone-lookup-2026-10-15.csv")
con = duckdb.connect()
con.sql("create view june as select * from read_parquet('data/yellow_tripdata_2025-06.parquet', union_by_name = true)")
print(f"{con.sql('select count(*) from june').fetchone()[0]:,} rows in june; {len(zones)} zone polygons, {len(lookup)} lookup rows")4,322,960 rows in june; 263 zone polygons, 265 lookup rows
Example 1: where the money goes
money = con.sql(f"""select {', '.join(f'sum(coalesce({c}, 0)) as {c}' for c in parts)}, sum(total_amount) as total from june
where payment_type = 1""").df().iloc[0]
print("card trips, share of total paid:", (money[parts] / money.total).round(3).to_dict())card trips, share of total paid: {'fare_amount': 0.654, 'extra': 0.054, 'mta_tax': 0.016, 'tip_amount': 0.144, 'tolls_amount': 0.02, 'improvement_surcharge': 0.032, 'congestion_surcharge': 0.075, 'Airport_fee': 0.005, 'cbd_congestion_fee': 0.019}
On card trips the metered fare is 65.4% of what passengers paid, tips 14.4%, the state congestion surcharge 7.5% and the new relief-zone fee 1.9%.
Example 2: negative rows
neg = con.sql("""select count(*) filter (where total_amount < 0) as negative, count(*) as n,
avg((payment_type in (3, 4))::int) filter (where total_amount < 0) as no_charge_or_dispute from june""").df().iloc[0]
print(f"negative totals: {neg.negative:,.0f} of {neg.n:,.0f}; paid as 'no charge' or 'dispute': {neg.no_charge_or_dispute:.1%}")negative totals: 74,257 of 4,322,960; paid as 'no charge' or 'dispute': 75.6%
74,257 trips have a negative total, and 75.6% of those are paid as no charge or dispute: they are reversals and adjustments of a charge, not trips. Summing revenue over every row nets them out; counting trips should leave them out.
What a trip record is
The TLC collects these records from the vendors' meter systems under its Taxicab Passenger Enhancement Program, and publishes them as received, with only light cleaning. That is why they are full of operational detail — a reversal is a row, a refunded fee is a negative fee — and why codes appear as the industry adds them: flex fares in 2024, new vendors, the congestion relief zone fee in 2025. The trip record describes a meter transaction, and a trip as a passenger thinks of it is usually, but not always, one row.
The TLC's data dictionary for each service is short and worth reading in full before any analysis; it changes from year to year, and the codes in this page are from the 2025 version.
Fields to read with care
passenger_countis entered by the driver and is often missing or 0; it is not a reliable count of riders.trip_distanceis the metered distance in miles, which includes detours; a few rows have impossible values in the thousands.extrabundles the overnight and rush-hour surcharges; its value depends on the time of day.Airport_feeis capitalised differently from the other columns and spelledairport_feein some years; useunion_by_nameand check spelling when reading several years.
Internal links
- How to analyse NYC taxi trip records — loading a year with DuckDB
- Taxi tips are zero for cash trips — the tip field's main trap
- Taxi trips with payment type 0 — rows with no passenger count or rate code
FAQ
What does RatecodeID 99 mean?
Unknown. In June 2025 it was on 47,381 trips, with a further 1,212,946 trips having no rate code at all.
Why do the fare components not add up to the total?
For payment-type-0 trips the surcharge columns are empty; for the rest, 79.6% add up to the cent in June 2025 and the dictionary does not explain the remainder.