NYC Yellow Taxi - 2014 Operations Status Report

Executive Summary

Yellow taxis carried just over 165 million passengers in 2014 and produced about 2.5 billion dollars in metered revenue. Demand was steady across the year, with a clear March peak and a February low, and it concentrated heavily into the evening hours. Solo riders dominated every month, and card payments made up a growing majority of trips.

The headline numbers for the year are summarized below. Sort any column to reorder the table.

Two findings deserve early attention: cash tips are not recorded in this dataset, so any blended tip rate understates reality, and demand is so evening-heavy that supply planning should focus on the 18:00 to 22:00 window.

Monthly Volume and Revenue

Monthly volume stayed within a fairly narrow band. March was the busiest month at 15.4 million trips, while February was the quietest at 13.1 million, partly a function of its shorter length and winter weather. Revenue tracked volume closely and stayed between 190 and 231 million dollars every month.

The query behind the table and chart aggregates every trip into calendar months and rounds the revenue and averages for reporting. Open it in Kusto to reproduce the numbers directly.

nyc_taxi
| summarize Trips = count(),
            Revenue = round(sum(total_amount), 0),
            AvgFare = round(avg(fare_amount), 2),
            AvgDist = round(avg(trip_distance), 2)
    by Month = startofmonth(pickup_datetime)
| order by Month asc

Fares and Trip Distance

The average fare drifted upward through the year, from $11.98 in January to a plateau near $12.90 in the second half. That drift is modest and consistent with a mid-year rate adjustment rather than a change in trip mix.

Average trip distance tells a more interesting story. Most months sit near 2.8 to 3.1 miles, but June stands out at 4.74 miles, well above every other month. This is a strong outlier worth investigating: it may reflect a batch of long airport or out-of-town trips, a GPS or meter data issue, or a handful of extreme trip_distance values dragging the mean up. March is also slightly elevated at 3.81 miles.

The focused query below returns just the monthly average fare and average distance, which is the pair used to spot the June anomaly.

nyc_taxi
| summarize AvgFare = round(avg(fare_amount), 2),
            AvgDist = round(avg(trip_distance), 2)
    by Month = startofmonth(pickup_datetime)
| order by Month asc

A useful next check is to recompute the June average with a distance cap (for example, dropping trips over 100 miles) to see how much of the spike is driven by a small number of extreme rows.

Payment Mix and Tipping

Card payments accounted for 95.6 million trips, comfortably ahead of the 68.2 million cash trips. The remaining codes (unknown, no charge, and disputed) are a small tail of under 1.4 million trips combined.

Important caveat: cash tips are not recorded in this dataset, so the cash tip rate reads as exactly 0.00%. That is a recording gap, not rider behavior. Any blended tip rate across all payment types therefore understates true tipping. The honest headline is the card-only figure of about 20.3%, and cash tips should be treated as unknown rather than zero.

The query below computes trip counts and the average tip as a percent of fare, guarding against divide-by-zero by returning a null tip percent when the fare is not positive.

nyc_taxi
| summarize Trips = count(),
            AvgTipPct = round(avg(iff(fare_amount > 0, 100.0 * tip_amount / fare_amount, real(null))), 2)
    by payment_type
| order by Trips desc

Passenger Occupancy

Solo riders dominated the year: 116.3 million of the 165 million trips carried a single passenger, about 70 percent of the total. Two-passenger trips were a distant second, and five-passenger trips slightly outnumbered three and four, a common pattern where groups round up into a single larger-capacity ride.

The query restricts to the valid 1 to 6 passenger range, which excludes a small number of zero-passenger and out-of-range rows that are almost certainly meter or entry errors.

nyc_taxi
| where passenger_count between (1 .. 6)
| summarize Trips = count() by passenger_count
| order by passenger_count asc

Demand by Hour of Day

Demand is strongly concentrated in the evening. The single busiest hour is 19:00 with 10.3 million pickups, and the four hours from 19:00 to 22:00 form the dominant demand window. There is a smaller morning commute ramp around 08:00 to 09:00, and the quietest hours are the pre-dawn 04:00 and 05:00 slots.

The query buckets every pickup by hour of day across the whole year. Running it returns all 24 hours; the highlights above pick out the peak and trough.

nyc_taxi
| summarize Trips = count() by Hour = hourofday(pickup_datetime)
| order by Hour asc

Data Quality Notes

The figures in this report are trustworthy at the aggregate level, but a few data-quality issues shape how they should be read:

• Cash tips are not recorded. The tip_amount column is only populated for card trips, so cash rides show a zero tip. Blended tip rates are therefore biased low and should not be quoted without the card-only qualifier.
• Non-positive fares exist. A small number of rows carry zero or negative fare_amount and total_amount (voided rides, test rows, and refunds). These should be filtered before computing averages so they do not understate revenue or skew the mean fare.
• Minor payment codes are noisy. The UNK, NOC, and DIS codes together are under one percent of trips, but their tip and fare values are unreliable and should be reported as a combined tail rather than analyzed individually.
• The June distance mean is an outlier. At 4.74 miles it is far above neighboring months and should be validated (extreme trip_distance rows, airport batches, or a meter issue) before being used in any trend.

The cleaning step below is the one most worth adopting first. This unified diff adds a filter that drops non-positive fares before any aggregation runs.

--- a/clean_trips.py
+++ b/clean_trips.py
@@ -1,7 +1,10 @@
 def load_trips(rows):
     return [r for r in rows if r.vendor_id]
 
 def summarize(trips):
+    # Drop non-positive fares before aggregating: voided rides, test rows,
+    # and refunds understate revenue and skew the average fare.
+    trips = [t for t in trips if t.fare_amount > 0 and t.total_amount > 0]
     total = sum(t.total_amount for t in trips)
     avg_fare = total / len(trips)
     return total, avg_fare

The report itself is produced by a simple pipeline: query the help cluster, aggregate on the server, and render the returned tables into this document. The diagram makes that flow explicit.

flowchart LR
    subgraph Source[Source data]
      Raw[(nyc_taxi 165.1M rows)]
    end
    subgraph Query[ADX server-side aggregation]
      M[By month]
      P[By payment type]
      H[By pickup hour]
      D[By passenger count]
    end
    subgraph Output[This report]
      Tbl[Aggregated tables]
      Cht[Charts]
      Rpt[[2014 operations report]]
    end
    Raw --> M
    Raw --> P
    Raw --> H
    Raw --> D
    M --> Tbl
    P --> Tbl
    H --> Tbl
    D --> Tbl
    Tbl --> Cht
    Cht --> Rpt

The review loop itself is a short conversation between the analyst, the query engine, and this document. The sequence diagram traces one pass.

sequenceDiagram
    autonumber
    participant An as Analyst
    participant Adx as ADX help cluster
    participant Tbl as Samples.nyc_taxi
    participant Rep as This report
    An->>Adx: Submit KQL (summarize by month)
    Adx->>Tbl: Scan 165.1M rows
    Tbl-->>Adx: Aggregated rows
    Adx-->>An: Result set
    An->>Rep: Render tables and charts
    Note over An,Rep: Reviewer leaves inline comments
    Rep-->>An: Comments handed back for the next revision

Demand by Hour

Trip volume follows a strong daily rhythm. Demand bottoms out around 05:00 (about 1.7 million trips across the year) and peaks in the evening at 19:00 (about 10.3 million), with a secondary rise near the 08:00 morning commute. The 18:00 to 22:00 evening window is the clearest operational signal in the dataset.

This within-day view is what drives the evening-supply recommendation below. It complements the monthly totals by showing where demand concentrates inside a typical day.

Recommendations and Next Steps

Based on the year of data, three actions offer the most value:

1. Add evening supply. Demand peaks hard from 19:00 to 22:00. Shift-planning and incentives should target that window before the morning commute.
2. Investigate cash-tip capture. Zero recorded cash tips is a reporting gap, not reality. Closing it would make tipping analysis honest and unlock fair driver-earnings reporting.
3. Monitor the June distance anomaly. The 4.74-mile mean needs a root-cause check before it is used in any trend or forecast.

Supporting follow-ups include adopting the non-positive-fare filter as a standard cleaning step, reporting card-only tip rates by default, and treating the minor payment codes as a single tail. Reviewers should leave inline comments on any figure they want reworked, and this document can be handed to other people for the same review workflow.