NYC Yellow Taxi Trips → Analytics Schema¶

What

Convert raw NYC TLC Yellow Taxi trip records into an analytics-ready CSV with ISO timestamps, human-readable payment types, and a per-row data quality flag.

Why interesting¶

NYC TLC publishes monthly Yellow Taxi CSVs containing ~10M rows per month, publicly cited in hundreds of analytics tutorials, and they ship with two silent data-quality landmines that wreck naive aggregations: trips with passenger_count = 0 (driver-only / no fare) and trips with fare_amount < 0 (refund/reversal posted as a negative row).

The data-quality sentinel classifies every row up front, so those landmines stand out instead of quietly skewing every average-fare and tip-rate aggregate:

flowchart TD
    R["raw trip row"] --> Q{"passenger_count = 0 ?"}
    Q -->|yes| NP["quality = no_passengers"]
    Q -->|no| F{"fare_amount &lt; 0 ?"}
    F -->|yes| RF["quality = refund"]
    F -->|no| OK["quality = ok"]

Edge cases sourced from.

https://www.nyc.gov/assets/tlc/downloads/pdf/data_dictionary_trip_records_yellow.pdf — official TLC data dictionary (datetime format, payment_type codes 1-6)
https://chriswhong.com/open-data/foil_nyc_taxi/ — original FOIL release, documents the negative-fare refund convention

Data source. NYC TLC Trip Record Data (this slice: 10 real trips hand-picked so that one short table carries all four payment types present in the data, both store_and_fwd_flag values, and one of each data-quality state). The TLC has since retired its public CSV downloads in favour of Parquet, so the full-scale fetch below pulls the identical records — same CSV layout, same MM/DD/YYYY hh:mm:ss AM/PM timestamps — from the NYC OpenData mirror.

The trick¶

US datetime with AM/PM — 02/09/2018 01:25:25 PM → ISO 8601 via DATE_CONVERT(..., 'MM/DD/YYYY hh:mm:ss A', 'YYYY-MM-DD[T]hh:mm:ss[Z]'). Run it: DATE_CONVERT([tpep_pickup_datetime], 'MM/DD/YYYY hh:mm:ss A', 'YYYY-MM-DD[T]hh:mm:ss[Z]')
store_and_fwd_flag — "N"/"Y" → readable false/true with IF.
payment_type code → label — REMAP() over a 1-6 → text named map built from the TLC dictionary.
Data-quality sentinel column — IF(NOT ISEMPTY([passenger_count]) AND [passenger_count] = 0, ...) classifies each row as ok / no_passengers / refund so the anomalies stand out instead of contaminating aggregates. Run it: IF(NOT ISEMPTY([passenger_count]) AND [passenger_count] = 0, 'no_passengers', IF([fare_amount] < 0, 'refund', 'ok'))

At full scale¶

The committed sample.csv is a 10-row teaching slice; the real 2019 dataset is ~84M trips. Pull it and run the same template against the whole thing:

bash fetch-full.sh          # downloads ./full/yellow_tripdata_2019.csv (~8 GB)
bxp-cli --config full.json  # processes all ~84M trips

Measured on the reference machine (ReleaseFast, 8 cores):

metric	value
input / output	84,399,019 rows (1:1) / 7.7 GB → 6.5 GB
wall time	~285 s (two `DATE_CONVERT` calls per row)
peak RSS	~23 MB (flat — does not grow with 84M rows)
`no_passengers`	1,772,399 rows (2.1%) — physically impossible, paid fare with `passenger_count = 0`
`refund`	169,241 rows — negative-fare reversals posted as trips

Constant ~23 MB while streaming 84 million rows and emitting 6.5 GB is the headline: the data-quality flag isolates ~1.94M anomalous rows that no spot check would ever surface — a tiny fraction of 84M, invisible in any spot check, yet they silently skew every average-fare and tip-rate aggregate computed over the raw column.

Final result¶

Timestamps become ISO, the payment code becomes a word, and every row carries its own verdict:

raw                                                 →  converted
02/09/2018 01:25:25 PM  pax=2  pay=1  fare=6        →  2018-02-09T13:25:25Z  credit_card  ok
02/09/2018 01:16:19 PM  pax=1  pay=4  fare=4.5      →  2018-02-09T13:16:19Z  dispute      ok
02/09/2018 01:45:16 PM  pax=0  pay=1  fare=6.5      →  2018-02-09T13:45:16Z  credit_card  no_passengers
02/09/2018 01:04:43 PM  pax=6  pay=3  fare=-4       →  2018-02-09T13:04:43Z  no_charge    refund

The last two rows are the point. A trip with a paid fare and zero passengers is physically impossible, and a negative fare is a reversal posted as if it were a trip. Both look like ordinary rows to any tool that only checks types — and both quietly drag down every average-fare and tip-rate aggregate. At full scale that is ~1.94M rows.

Trace it in the GUI

Click the quality cell of the refund row: the trace pane walks the nested IF that decided it, one comparison at a time.

Sample data¶

Run it with bxp-cli --config ./sample.json --template nyc_taxi_to_analytics:

sample.json (config)sample.csvsample.csvx (result)

{
  // payment_type 1-6 → human-readable. Defined by TLC data dictionary:
  // https://www.nyc.gov/assets/tlc/downloads/pdf/data_dictionary_trip_records_yellow.pdf
  maps: {
    payment_type_to_label: {
      "1": "credit_card",
      "2": "cash",
      "3": "no_charge",
      "4": "dispute",
      "5": "unknown",
      "6": "voided"
    }
  },
  conversion_templates: {
    nyc_taxi_to_analytics: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",

      input_schema: {
        // TRICK 1 — TLC dumps datetimes as "02/09/2018 01:25:25 PM" with AM/PM.
        // Most downstream tools want ISO-8601.
        $pickup_at:   "DATE_CONVERT([tpep_pickup_datetime], 'MM/DD/YYYY hh:mm:ss A', 'YYYY-MM-DD[T]hh:mm:ss[Z]')",
        $dropoff_at:  "DATE_CONVERT([tpep_dropoff_datetime], 'MM/DD/YYYY hh:mm:ss A', 'YYYY-MM-DD[T]hh:mm:ss[Z]')",

        $passengers:  "[passenger_count]",
        $distance_mi: "[trip_distance]",

        // TRICK 2 — store_and_fwd_flag is "N"/"Y". Normalize to readable bool.
        $stored:      "IF([store_and_fwd_flag] = 'Y', 'true', 'false')",

        // TRICK 3 — payment_type integer (1-6) → text via REMAP + named map.
        $payment:     "REMAP([payment_type], 'payment_type_to_label')",

        $fare_usd:    "[fare_amount]",
        $tip_usd:     "[tip_amount]",
        $tolls_usd:   "[tolls_amount]",
        $total_usd:   "[total_amount]",

        // TRICK 4 — data-quality sentinels. Real TLC dumps contain rows with
        // passenger_count=0 (driver pickup with no fare) and fare_amount<0
        // (refunds posted as negative trips). Both pass silently into
        // downstream analytics and skew aggregates. The ISEMPTY half of the
        // test is load-bearing: a blank passenger_count coerces to 0, so
        // without it a missing value would be reported as an observed zero.
        $quality:     "IF(NOT ISEMPTY([passenger_count]) AND [passenger_count] = 0, 'no_passengers', IF([fare_amount] < 0, 'refund', 'ok'))"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        pickup_at:    "$pickup_at",
        dropoff_at:   "$dropoff_at",
        passengers:   "$passengers",
        distance_mi:  "$distance_mi",
        stored:       "$stored",
        payment:      "$payment",
        fare_usd:     "$fare_usd",
        tip_usd:      "$tip_usd",
        tolls_usd:    "$tolls_usd",
        total_usd:    "$total_usd",
        quality:      "$quality"
      }
    }
  }
}

VendorID,tpep_pickup_datetime,tpep_dropoff_datetime,passenger_count,trip_distance,RatecodeID,store_and_fwd_flag,PULocationID,DOLocationID,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,improvement_surcharge,total_amount
2,02/09/2018 01:25:25 PM,02/09/2018 01:31:11 PM,2,1.08,1,N,233,229,1,6,0,0.5,1,0,0.3,7.8
2,02/09/2018 01:25:00 PM,02/09/2018 02:05:03 PM,1,13.85,2,N,215,230,2,52,0,0.5,0,0,0.3,52.8
1,02/09/2018 01:24:18 PM,02/09/2018 02:14:34 PM,1,18,2,N,132,48,1,52,0,0.5,15.8,0,0.3,68.6
1,02/09/2018 01:44:11 PM,02/09/2018 01:44:14 PM,1,0,1,N,90,90,3,2.5,0,0.5,0,0,0.3,3.3
1,02/09/2018 01:55:36 PM,02/09/2018 02:01:32 PM,1,0.5,1,Y,263,236,1,5.5,0,0.5,1.25,0,0.3,7.55
1,02/09/2018 01:16:19 PM,02/09/2018 01:20:10 PM,1,0.4,1,N,142,50,4,4.5,0,0.5,0,0,0.3,5.3
1,02/09/2018 01:16:18 PM,02/09/2018 02:03:06 PM,2,21.2,2,Y,88,132,1,52,0,0.5,5,5.76,0.3,63.56
1,02/09/2018 01:45:16 PM,02/09/2018 01:53:13 PM,0,0.6,1,N,164,186,1,6.5,0,0.5,1.45,0,0.3,8.75
2,02/09/2018 01:04:43 PM,02/09/2018 01:07:53 PM,6,0.15,1,N,163,163,3,-4,0,-0.5,0,0,-0.3,-4.8
1,02/09/2018 01:15:14 PM,02/09/2018 01:26:52 PM,0,1.6,1,N,161,236,2,9,0,0.5,0,0,0.3,9.8

pickup_at,dropoff_at,passengers,distance_mi,stored,payment,fare_usd,tip_usd,tolls_usd,total_usd,quality
2018-02-09T13:25:25Z,2018-02-09T13:31:11Z,2,1.08,false,credit_card,6,1,0,7.8,ok
2018-02-09T13:25:00Z,2018-02-09T14:05:03Z,1,13.85,false,cash,52,0,0,52.8,ok
2018-02-09T13:24:18Z,2018-02-09T14:14:34Z,1,18,false,credit_card,52,15.8,0,68.6,ok
2018-02-09T13:44:11Z,2018-02-09T13:44:14Z,1,0,false,no_charge,2.5,0,0,3.3,ok
2018-02-09T13:55:36Z,2018-02-09T14:01:32Z,1,0.5,true,credit_card,5.5,1.25,0,7.55,ok
2018-02-09T13:16:19Z,2018-02-09T13:20:10Z,1,0.4,false,dispute,4.5,0,0,5.3,ok
2018-02-09T13:16:18Z,2018-02-09T14:03:06Z,2,21.2,true,credit_card,52,5,5.76,63.56,ok
2018-02-09T13:45:16Z,2018-02-09T13:53:13Z,0,0.6,false,credit_card,6.5,1.45,0,8.75,no_passengers
2018-02-09T13:04:43Z,2018-02-09T13:07:53Z,6,0.15,false,no_charge,-4,0,0,-4.8,refund
2018-02-09T13:15:14Z,2018-02-09T13:26:52Z,0,1.6,false,cash,9,0,0,9.8,no_passengers

Full-scale & binary files (run it on the complete dataset): fetch-full.sh · full.json.