Data quality

What each rule removes, and why

The 2021 notebook cleaned 84,598,444 raw records down to 74,910,889. The method page shows how many rows survive each rule in order. This report asks the follow-up questions: what each rule actually catches, which rules matter on their own, and what got through. Every figure is a full count over the 2019 records, not a sample, so there is no sampling error to report. The uncertainty is whether a rule is right. Reproduce it with scripts/data_quality.py.

Missing values

The January gap is one column

The notebook's first step, dropna(), removes any row with a missing field. 5,300,601 rows go, and almost all of them are missing only the congestion surcharge, which TLC started recording on 21 January 2019.

Missing values per monthly TLC file
File	Rows	Any missing	Surcharge	Passengers, rate code, flag
2019-01	7,696,617	4,884,887 (63.5%)	4,884,887	28,672
2019-02	7,049,370	29,663 (0.4%)	29,663	29,663
2019-03	7,866,620	33,474 (0.4%)	33,474	33,474
2019-04	7,475,949	42,456 (0.6%)	42,456	42,456
2019-05	7,598,445	32,770 (0.4%)	32,770	32,770
2019-06	6,971,560	30,747 (0.4%)	30,747	30,747
2019-07	6,310,419	33,959 (0.5%)	33,959	33,959
2019-08	6,073,357	33,321 (0.5%)	33,321	33,321
2019-09	6,567,788	34,090 (0.5%)	34,090	34,089
2019-10	7,213,891	46,723 (0.6%)	46,723	46,723
2019-11	6,878,111	47,493 (0.7%)	47,493	47,491
2019-12	6,896,317	51,018 (0.7%)	51,018	51,018

Passenger count, rate code and store-and-forward flag are always missing together: about 0.4% of each month, the same rows each time.

January 2019: rows per pickup day, missing surcharge shaded

1 Jan21 Jan31 Jan

Red: rows missing the congestion surcharge. Every row up to 20 January lacks it, and from 21 January almost none do.

Rule by rule

Removed in sequence, failing alone, failing only this rule

Rules run in the notebook's order, so a rule only removes what earlier rules left. “Fails alone” counts rows breaking the rule whatever else is true of them (round 1 rules on the 79,297,843 complete rows, later rules on the 76,488,807 round-1 rows). “Only this rule” counts rows that no other rule in the same set would catch: the rows the rule is solely responsible for.

Step 1 · Round 1
Drop rows with any missing value
dropna()
The 2021 model needs every field. TLC left congestion_surcharge empty until 21 January 2019, so this one rule removes almost all of 1-20 January.
Removed in sequence
5,300,601
Fails alone
5,300,601
Only this rule
–
Most common offending values
- missing congestion_surcharge5,300,601
- missing passenger_count444,383
- missing RatecodeID444,383
- missing store_and_fwd_flag444,383
Step 2 · Round 1
Trip distance must be positive
trip_distance > 0
A metered trip cannot cover zero or negative distance: these are cancelled rides, meter tests or voids.
Removed in sequence
705,218
Fails alone
705,218
Only this rule
508,124
Most common offending values
- trip_distance = 0.0705,157
- trip_distance = -1.742
- trip_distance = -1.752
- trip_distance = -2.292
- trip_distance = -0.071
Step 3 · Round 1
Passenger count between 1 and 6
passenger_count BETWEEN 1 AND 6
Drivers enter the passenger count by hand; 0 means it was not entered, and a yellow cab seats at most 5 adults plus a child.
Removed in sequence
1,431,245
Fails alone
1,454,347
Only this rule
1,422,874
Most common offending values
- passenger_count = 0.01,453,468
- passenger_count = 7.0404
- passenger_count = 8.0255
- passenger_count = 9.0220
Step 4 · Round 1
Known rate code (drops 99)
RatecodeID BETWEEN 1 AND 6
TLC's data dictionary defines rate codes 1-6; 99 is an undocumented placeholder.
Removed in sequence
1,803
Fails alone
3,726
Only this rule
1,767
Most common offending values
- RatecodeID = 99.03,726
Step 5 · Round 1
Fare must be positive
fare_amount > 0
Negative fares are reversals of an earlier charge, logged as separate rows; zero fares are not paid trips.
Removed in sequence
156,428
Fails alone
197,991
Only this rule
359
Most common offending values
- fare_amount = 0.033,013
- fare_amount = -2.531,867
- fare_amount = -3.012,738
- fare_amount = -52.012,122
- fare_amount = -4.511,829
Step 6 · Round 1
Extra charge cannot be negative
extra >= 0
Negative extras are reversals, like negative fares.
Removed in sequence
600
Fails alone
80,325
Only this rule
135
Most common offending values
- extra = -0.553,961
- extra = -1.023,312
- extra = -4.52,160
- extra = -1.7266
- extra = -2.5169
Step 7 · Round 1
MTA tax of $0.50 to $1
mta_tax BETWEEN 0.5 AND 1
Every metered trip in New York City pays the $0.50 MTA state surcharge; other values are reversals or out-of-city trips.
Removed in sequence
306,307
Fails alone
644,160
Only this rule
304,423
Most common offending values
- mta_tax = 0.0482,698
- mta_tax = -0.5160,944
- mta_tax = 1.44250
- mta_tax = 0.2582
- mta_tax = 3.361
Step 8 · Round 1
Tolls cannot be negative
tolls_amount >= 0
Negative tolls are reversals.
Removed in sequence
0
Fails alone
3,579
Only this rule
0
Most common offending values
- tolls_amount = -6.122,685
- tolls_amount = -5.76293
- tolls_amount = -10.5119
- tolls_amount = -2.850
- tolls_amount = -12.548
Step 9 · Round 1
Improvement surcharge equals $0.30
improvement_surcharge = 0.30
The $0.30 improvement surcharge applied to every trip in 2019.
Removed in sequence
7,848
Fails alone
216,983
Only this rule
7,848
Most common offending values
- improvement_surcharge = -0.3164,903
- improvement_surcharge = 0.052,051
- improvement_surcharge = 1.019
- improvement_surcharge = 0.0310
Step 10 · Round 1
Total amount must be positive
total_amount > 0
A trip with a non-positive total is a reversal or a void.
Removed in sequence
0
Fails alone
184,455
Only this rule
0
Most common offending values
- total_amount = 0.019,499
- total_amount = -6.811,191
- total_amount = -7.810,411
- total_amount = -8.310,399
- total_amount = -6.310,294
Step 11 · Round 1
Congestion surcharge cannot be negative
congestion_surcharge >= 0
Negative congestion surcharges are reversals.
Removed in sequence
0
Fails alone
119,540
Only this rule
0
Most common offending values
- congestion_surcharge = -2.5119,536
- congestion_surcharge = -0.753
- congestion_surcharge = -1.51
Step 12 · Round 1
Pickup between 2018-01-01 and 2019-12-31
pickup_dt BETWEEN TIMESTAMP '2018-01-01 00:00:00' AND TIMESTAMP '2019-12-31 23:59:59'
Meter clocks occasionally report years like 2003 or 2088. The notebook's window starts in 2018, a year early; the weather join later drops 2018 pickups anyway.
Removed in sequence
965
Fails alone
1,010
Only this rule
0
Most common offending values
- year(pickup_dt) = 2020428
- year(pickup_dt) = 2009358
- year(pickup_dt) = 2008185
- year(pickup_dt) = 200213
- year(pickup_dt) = 20034
Step 13 · Round 1
Drop-off within 2019
dropoff_dt BETWEEN TIMESTAMP '2019-01-01 00:00:00' AND TIMESTAMP '2019-12-31 23:59:59'
Drop-offs must fall in the year being studied.
Removed in sequence
1,150
Fails alone
2,191
Only this rule
1,150
Most common offending values
- year(dropoff_dt) = 20201,607
- year(dropoff_dt) = 2009440
- year(dropoff_dt) = 2008103
- year(dropoff_dt) = 200211
- year(dropoff_dt) = 20036
Step 14 · Round 1
Drop unknown vendor 4
VendorID != 4
Vendor 4 does not appear in TLC's 2019 data dictionary (1 = Creative Mobile Technologies, 2 = VeriFone).
Removed in sequence
197,472
Fails alone
199,180
Only this rule
197,472
Most common offending values
- VendorID = 4199,180
Step 15 · Round 2
Fare z-score at most 3
fare_amount <= 296.14325468838814
Fares more than three standard deviations above the mean are treated as keying errors. The standard deviation is inflated by fares up to $671,123, so the cut-off ends up at about $296.
Removed in sequence
747
Fails alone
747
Only this rule
617
Most common offending values
- fare (nearest $100) = 300.0347
- fare (nearest $100) = 400.0267
- fare (nearest $100) = 500.079
- fare (nearest $100) = 600.020
- fare (nearest $100) = 700.05
Step 16 · Round 2
Drop exact duplicate rows
SELECT DISTINCT *
Identical rows in every field are double submissions from the meter.
Removed in sequence
3
Fails alone
–
Only this rule
–
Step 17 · Round 3
Fare at least $2.50
fare_amount >= 2.5
$2.50 was the flag-drop charge in 2019, so a metered fare cannot be lower.
Removed in sequence
351
Fails alone
351
Only this rule
311
Most common offending values
- fare = 2.094
- fare = 0.138
- fare = 0.0124
- fare = 0.513
- fare = 0.6513
Step 18 · Round 3
Drop-off after pick-up
travel_time > 0
A trip that ends before it starts is a clock error.
Removed in sequence
3,184
Fails alone
3,184
Only this rule
945
Most common offending values
- minutes = 0.02,238
- minutes = -40.39
- minutes = -46.99
- minutes = -51.99
- minutes = -45.78
Step 19 · Round 3
Distance / minutes at most 50
trip_distance / travel_time <= 50
Meant to remove trips faster than 50 mph. The code divides by minutes, so only trips faster than 50 miles a minute go (a quirk kept on purpose).
Removed in sequence
6,454
Fails alone
8,694
Only this rule
6,046
Most common offending values
- miles per minute (nearest 10) = undefined (zero or negative duration)2,238
- miles per minute (nearest 10) = 60.0853
- miles per minute (nearest 10) = 70.0690
- miles per minute (nearest 10) = 80.0626
- miles per minute (nearest 10) = 50.0548
Step 20 · Round 3
Trip at most 180 minutes
travel_time <= 180
Trips over three hours are almost always meters left running.
Removed in sequence
206,182
Fails alone
206,299
Only this rule
205,265
Most common offending values
- duration (hours) = 24.0 h120,875
- duration (hours) = 23.0 h64,629
- duration (hours) = 22.0 h2,396
- duration (hours) = 7.0 h1,465
- duration (hours) = 6.0 h1,398
Step 21 · Round 3
Tip at most half the fare
fare_amount >= 2 * tip_amount
The comment says 'tips more than twice the fare'; the code keeps tips up to half the fare, and the code is what ran.
Removed in sequence
596,042
Fails alone
597,433
Only this rule
596,042
Most common offending values
- tip / fare = 0.5240,716
- tip / fare = 0.6188,130
- tip / fare = 0.755,201
- tip / fare = 0.830,387
- tip / fare = 0.917,058

What got through

Implausible records in the final dataset

Checks the 2021 rules did not have, counted on the 74,910,889 trips of the final analysis dataset. They are reported, not removed: removing them would change the 2021 results this site reproduces.

Average speed above 60 mph
Trips
33,474
0.045%
Vendor 1
24,526
0.094%
Vendor 2
8,948
0.018%
New York City's top speed limit is 50 mph; an average above 60 over a whole trip points to a distance or clock error.
Shorter than one minute
Trips
191,992
0.256%
Vendor 1
54,195
0.208%
Vendor 2
137,797
0.282%
Rides under a minute are usually cancelled at the kerb or a meter started by mistake.
Under 0.1 mile but over 30 minutes
Trips
475
< 0.001%
Vendor 1
0
0.000%
Vendor 2
475
< 0.001%
A meter that recorded time but almost no distance: a stuck odometer or a waiting fare.
Longer than 50 miles
Trips
184
< 0.001%
Vendor 1
47
< 0.001%
Vendor 2
137
< 0.001%
Possible for out-of-town fares, but rare enough to check.
Total leaves out the congestion surcharge
Trips
23,397,653
31.2%
Vendor 1
23,397,647
89.8%
Vendor 2
6
< 0.001%
The total equals every other charge but not the $2.50 congestion surcharge recorded on the same row. A vendor reporting convention rather than a pricing error, but any analysis of total fares by vendor would be off by $2.50.
Total differs from the sum of its parts (other)
Trips
164,443
0.220%
Vendor 1
0
0.000%
Vendor 2
164,443
0.337%
The total should equal fare + extra + MTA tax + tip + tolls + improvement + congestion surcharges.
Rate code 2 (JFK) fare not $52
Trips
352
< 0.001%
Vendor 1
45
< 0.001%
Vendor 2
307
< 0.001%
The Manhattan-JFK flat fare was $52 throughout 2019.
Standard rate above $25 per mile (1 mile or more)
Trips
477
< 0.001%
Vendor 1
186
< 0.001%
Vendor 2
291
< 0.001%
At $2.50 a mile plus waiting time, a standard-rate fare far above $25 a mile is unlikely.

Residual plausibility checks by vendor
Check	Trips	Share	Vendor 1 (CMT)	Vendor 2 (VeriFone)
Average speed above 60 mphNew York City's top speed limit is 50 mph; an average above 60 over a whole trip points to a distance or clock error.	33,474	0.045%	24,5260.094%	8,9480.018%
Shorter than one minuteRides under a minute are usually cancelled at the kerb or a meter started by mistake.	191,992	0.256%	54,1950.208%	137,7970.282%
Under 0.1 mile but over 30 minutesA meter that recorded time but almost no distance: a stuck odometer or a waiting fare.	475	< 0.001%	00.000%	475< 0.001%
Longer than 50 milesPossible for out-of-town fares, but rare enough to check.	184	< 0.001%	47< 0.001%	137< 0.001%
Total leaves out the congestion surchargeThe total equals every other charge but not the $2.50 congestion surcharge recorded on the same row. A vendor reporting convention rather than a pricing error, but any analysis of total fares by vendor would be off by $2.50.	23,397,653	31.2%	23,397,64789.8%	6< 0.001%
Total differs from the sum of its parts (other)The total should equal fare + extra + MTA tax + tip + tolls + improvement + congestion surcharges.	164,443	0.220%	00.000%	164,4430.337%
Rate code 2 (JFK) fare not $52The Manhattan-JFK flat fare was $52 throughout 2019.	352	< 0.001%	45< 0.001%	307< 0.001%
Standard rate above $25 per mile (1 mile or more)At $2.50 a mile plus waiting time, a standard-rate fare far above $25 a mile is unlikely.	477	< 0.001%	186< 0.001%	291< 0.001%

Vendor 1 trips whose total leaves out the congestion surcharge, by month

Jan

89%

Feb

94%

Mar

94%

Apr

93%

May

93%

Jun

93%

Jul

93%

Aug

94%

Sep

94%

Oct

94%

Nov

94%

Dec

All of these tables are in the database: dq_rules, dq_rule_values, dq_missing, dq_january, dq_residual_checks, dq_surcharge_by_month.

The January gap is one column

January 2019: rows per pickup day, missing surcharge shaded

Removed in sequence, failing alone, failing only this rule

Drop rows with any missing value

Trip distance must be positive

Passenger count between 1 and 6

Known rate code (drops 99)

Fare must be positive

Extra charge cannot be negative

MTA tax of $0.50 to $1

Tolls cannot be negative

Improvement surcharge equals $0.30

Total amount must be positive

Congestion surcharge cannot be negative

Pickup between 2018-01-01 and 2019-12-31

Drop-off within 2019

Drop unknown vendor 4

Fare z-score at most 3

Drop exact duplicate rows

Fare at least $2.50

Drop-off after pick-up

Distance / minutes at most 50

Trip at most 180 minutes

Tip at most half the fare

Implausible records in the final dataset

Vendor 1 trips whose total leaves out the congestion surcharge, by month