Cleaning funnel
Rows left after every cleaning rule, revived pipeline next to the counts printed by the 2021 notebook.
28 rows Download CSV
| stepINTEGER | stageTEXT | labelTEXT | ruleTEXT | revived_rowsINTEGER | notebook_rowsINTEGER | difference_pctREAL |
|---|---|---|---|---|---|---|
| 1 | Raw | Raw 2019 yellow-taxi records | 84598444 | 84399019 | 0.236 | |
| 2 | Round 1 | Drop rows with any missing value | 79297843 | 79296437 | 0.002 | |
| 3 | Round 1 | Trip distance must be positive | trip_distance > 0 | 78592625 | null | null |
| 4 | Round 1 | Passenger count between 1 and 6 | passenger_count BETWEEN 1 AND 6 | 77161380 | null | null |
| 5 | Round 1 | Known rate code (drops 99) | RatecodeID BETWEEN 1 AND 6 | 77159577 | null | null |
| 6 | Round 1 | Fare must be positive | fare_amount > 0 | 77003149 | null | null |
| 7 | Round 1 | Extra charge cannot be negative | extra >= 0 | 77002549 | null | null |
| 8 | Round 1 | MTA tax of $0.50 to $1 | mta_tax BETWEEN 0.5 AND 1 | 76696242 | null | null |
| 9 | Round 1 | Tolls cannot be negative | tolls_amount >= 0 | 76696242 | null | null |
| 10 | Round 1 | Improvement surcharge equals $0.30 | improvement_surcharge = 0.30 | 76688394 | null | null |
| 11 | Round 1 | Total amount must be positive | total_amount > 0 | 76688394 | null | null |
| 12 | Round 1 | Congestion surcharge cannot be negative | congestion_surcharge >= 0 | 76688394 | null | null |
| 13 | 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' | 76687429 | null | null |
| 14 | Round 1 | Drop-off within 2019 | dropoff_dt BETWEEN TIMESTAMP '2019-01-01 00:00:00' AND TIMESTAMP '2019-12-31 23:59:59' | 76686279 | null | null |
| 15 | Round 1 | Drop unknown vendor 4 (end of round 1) | VendorID != 4 | 76488807 | 76487438 | 0.002 |
| 16 | Round 2 | Fare z-score at most 3 | fare <= mean + 3 sd = 296.1433 (notebook 296.1457) | 76488060 | 76486691 | 0.002 |
| 17 | Round 2 | Drop exact duplicate rows | 76488057 | 76486688 | 0.002 | |
| 18 | Round 3 | Fare at least the $2.50 flag-fall | fare_amount >= 2.5 | 76487706 | 76486337 | 0.002 |
| 19 | Round 3 | Drop-off after pick-up | travel_time > 0 | 76484522 | null | null |
| 20 | Round 3 | At most 6 passengers | passenger_count <= 6 | 76484522 | null | null |
| 21 | Round 3 | Distance / minutes at most 50 (the 'mph' rule) | trip_distance / travel_time <= 50 | 76478068 | 76475571 | 0.003 |
| 22 | Round 3 | Trip at most 180 minutes | travel_time <= 180 | 76271886 | 76269392 | 0.003 |
| 23 | Round 3 | Tip at most half the fare | fare_amount >= 2 * tip_amount | 75675844 | 75673363 | 0.003 |
| 24 | Merge | Join taxi zones; drop trips from zone 264 to zone 265 | 75675163 | null | null | |
| 25 | Merge | Drop zones NA/NV; join weather (2019 pickups) and events | 75185703 | 75183226 | 0.003 |