Data-quality rules

Every 2021 cleaning rule with its reason, rows removed in sequence, rows failing it on its own and rows failing only it.

21 rows Download CSV

dq_rules, page 1 of 1
stepINTEGER	keyTEXT	stageTEXT	labelTEXT	ruleTEXT	reasonTEXT	applied_toTEXT	applied_rowsINTEGER	removed_in_sequenceINTEGER	fails_aloneINTEGER	fails_only_thisINTEGER
1	dropna	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.	raw	84598444	5300601	5300601	null
2	r1_1	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.	dropna	79297843	705218	705218	508124
3	r1_2	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.	dropna	79297843	1431245	1454347	1422874
4	r1_3	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.	dropna	79297843	1803	3726	1767
5	r1_4	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.	dropna	79297843	156428	197991	359
6	r1_5	Round 1	Extra charge cannot be negative	extra >= 0	Negative extras are reversals, like negative fares.	dropna	79297843	600	80325	135
7	r1_6	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.	dropna	79297843	306307	644160	304423
8	r1_7	Round 1	Tolls cannot be negative	tolls_amount >= 0	Negative tolls are reversals.	dropna	79297843	0	3579	0
9	r1_8	Round 1	Improvement surcharge equals $0.30	improvement_surcharge = 0.30	The $0.30 improvement surcharge applied to every trip in 2019.	dropna	79297843	7848	216983	7848
10	r1_9	Round 1	Total amount must be positive	total_amount > 0	A trip with a non-positive total is a reversal or a void.	dropna	79297843	0	184455	0
11	r1_10	Round 1	Congestion surcharge cannot be negative	congestion_surcharge >= 0	Negative congestion surcharges are reversals.	dropna	79297843	0	119540	0
12	r1_11	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.	dropna	79297843	965	1010	0
13	r1_12	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.	dropna	79297843	1150	2191	1150
14	r1_13	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).	dropna	79297843	197472	199180	197472
15	fare_zscore	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.	round1	76488807	747	747	617
16	dedup	Round 2	Drop exact duplicate rows	SELECT DISTINCT *	Identical rows in every field are double submissions from the meter.	zscore	76488060	3	null	null
17	min_fare	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.	round1	76488807	351	351	311
18	positive_duration	Round 3	Drop-off after pick-up	travel_time > 0	A trip that ends before it starts is a clock error.	round1	76488807	3184	3184	945
19	speed	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).	round1	76488807	6454	8694	6046
20	max_duration	Round 3	Trip at most 180 minutes	travel_time <= 180	Trips over three hours are almost always meters left running.	round1	76488807	206182	206299	205265
21	tip	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.	round1	76488807	596042	597433	596042