Taxis and buses through the day

Buses and taxis share Manhattan’s streets. Are buses slow because the streets are slow, or for their own reasons, such as stops and boarding? Lining the two up by hour of day gives a first answer.

The two tables cover different months: the TLC publishes taxi trips about two months late, and our bus speeds start in October 2026. So this compares the shape of a typical weekday, not the same days. Once the TLC publishes October 2026, set both to the same dates.

The query

ATTACH 'https://data.transitlab.nyc/v1/transitlab.duckdb' AS transitlab (READ_ONLY);

WITH taxi AS (
  SELECT hour(pickup_datetime) AS hour,
         sum(trip_miles) / sum(trip_minutes / 60) AS taxi_mph,
         count(*) / count(DISTINCT pickup_datetime::DATE) AS taxi_trips_a_day
  FROM transitlab.taxi_yellow_trips
  WHERE pickup_datetime >= '2026-08-01' AND pickup_datetime < '2026-09-01'
    AND dayofweek(pickup_datetime) BETWEEN 1 AND 5
    AND pickup_borough = 'Manhattan' AND dropoff_borough = 'Manhattan'
    -- Trim bad records, as in our taxi study
    AND trip_miles BETWEEN 0.3 AND 30
    AND trip_minutes BETWEEN 2 AND 90
  GROUP BY hour
),
bus AS (
  SELECT hour,
         sum(meters) / sum(seconds) * 2.23694 AS bus_mph
  FROM transitlab.bus_route_speeds
  WHERE route_id LIKE 'M%'   -- Manhattan local and Select Bus routes
    AND dayofweek(service_date::DATE) BETWEEN 1 AND 5
  GROUP BY hour
)
SELECT hour,
       round(taxi_mph, 1) AS taxi_mph,
       round(bus_mph, 1) AS bus_mph,
       round(bus_mph / taxi_mph, 2) AS bus_share_of_taxi_speed,
       round(taxi_trips_a_day) AS taxi_trips_a_day
FROM taxi JOIN bus USING (hour)
WHERE hour BETWEEN 6 AND 22
ORDER BY hour;

How it works

• Taxi speed is total miles over total hours for trips that start and end in Manhattan, so long trips count for more, as in the bus figures. Taxi times are New York local time.
• Bus speed adds up the metres and seconds in bus route speeds for routes whose names start with M. Its hour is already New York time.
• The join is on hour of day. Both sides are weekday averages.

Reading it

A taxi trip is door to door with no stops; a bus stops every few blocks. So buses will be slower at every hour. The useful part is the ratio. If bus_share_of_taxi_speed holds steady through the day, buses slow down when traffic does. If it drops at rush hour while taxis hold up, something besides traffic is slowing buses then: crowded stops, slow boarding, or buses bunching.

Going further

• By street. Bus delay by street has bus speeds street by street. Taxi trips only have zones, so compare zone pairs along one avenue: pickup_zone and dropoff_zone hold names like Midtown Center.
• Uber and Lyft. Swap in fhv_trips, which has the same columns, for four times as many trips.