Python

Install DuckDB and pandas:

pip install duckdb pandas

Query into a DataFrame

import duckdb

con = duckdb.connect()
con.sql("ATTACH 'https://data.transitlab.nyc/v1/transitlab.duckdb' AS transitlab (READ_ONLY)")

zones = con.sql("""
    SELECT segment, routes, start_date, end_date, slow_days, rider_hours_lost
    FROM transitlab.subway_slow_zones
    WHERE kind = 'slow zone'
    ORDER BY rider_hours_lost DESC
""").df()

print(zones.head(10))

.df() returns a pandas DataFrame. Use .pl() for Polars or .arrow() for an Arrow table.

Do the heavy work in SQL

Trip tables run to hundreds of millions of rows. Group and filter in DuckDB, then hand pandas the small result:

hourly = con.sql("""
    SELECT hour(pickup_datetime) AS hour, count(*) AS trips
    FROM transitlab.taxi_yellow_trips
    WHERE pickup_datetime >= '2026-08-01' AND pickup_datetime < '2026-09-01'
    GROUP BY hour
    ORDER BY hour
""").df()

Pass values safely

Use parameters rather than pasting values into the SQL string:

route = "M15+"
speeds = con.execute("""
    SELECT service_date, hour, sum(meters) / sum(seconds) * 2.23694 AS mph
    FROM transitlab.bus_route_speeds
    WHERE route_id = ?
    GROUP BY ALL
    ORDER BY ALL
""", [route]).df()

Without DuckDB

pandas can read a single-file table straight from its URL (it needs pyarrow and fsspec):

import pandas as pd

lanes = pd.read_parquet("https://data.transitlab.nyc/v1/bus_lanes.parquet")

This downloads the whole file, which is fine for small tables. For trip tables, use DuckDB.