NYC yellow-taxi trips in H5Col#

This example loads a sample of the NYC Taxi & Limousine Commission (TLC) yellow-taxi trip records into two H5Col tables and exercises most of the convention at once:

  • categoricals — numeric (VendorID, RatecodeID) and string (payment_type, store_and_fwd_flag) dictionary-encoded columns;

  • missing values — the out-of-range sentinel style (passenger_count, fill -1) and the NaN style (nullable money columns);

  • datetime — HDF5 has no datetime type, so timestamps are stored as int64 seconds with a CF-style units string;

  • filters — Shuffle + Deflate on the numeric columns;

  • search indexes + the query layer — BITMAP, SORTED_ROWS, and CHUNK_MINMAX indexes drive Table.select(...).

The committed sample (taxi/data/yellow_sample.parquet, ~25k rows) lets this notebook run offline. For a real-scale run, download a full month first — see the last section.

Run this notebook from the examples/ directory.

import h5py
import numpy as np
import pandas as pd

# The local `taxi` package (examples/taxi) resolves when this notebook is run
# from the examples/ directory.
from taxi.build import DEFAULT_OUT, build
from taxi.schema import decode_datetime

from h5col import Table, field

Build the H5Col file#

build() reads the committed parquet sample, writes the trips and zones tables into one HDF5 file, and builds three search indexes.

path = build()  # writes taxi/nyc_taxi.h5
f = h5py.File(DEFAULT_OUT, "r")
trips = Table(f["trips"])
zones = Table(f["zones"])
trips

wrote /Users/ajelenak/Documents/H5Col/h5col/examples/taxi/nyc_taxi.h5 (1.40 MB), 25000 trips

<h5col.Table '/trips' nrows=25000>

zones

<h5col.Table '/zones' nrows=265>

list(trips.column_names)

['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',
 'congestion_surcharge',
 'airport_fee']

Categorical columns#

payment_type is stored as a string categorical: the file holds small integer codes plus a categories dataset, and Column.read() returns the labels.

pt = trips["payment_type"].read()
pd.Series(pt).value_counts()

Credit card      19190
Cash              3981
Not specified     1000
Dispute            637
No charge          192
Name: count, dtype: int64

# The distinct labels live in a categories dataset referenced by the column.
trips["payment_type"].categories

array(['Not specified', 'Credit card', 'Cash', 'No charge', 'Dispute',
       'Unknown', 'Voided trip'], dtype=object)

Missing values#

H5Col marks a missing row with the column’s HDF5 fill value. This sample shows both recommended styles:

  • passenger_count — an integer column with an out-of-range sentinel fill (-1, with valid_min = 0);

  • RatecodeID (categorical) and the nullable money columns — NaN / missing code style.

Column.is_missing() applies the canonical missing-value test.

nullable = [
    "passenger_count",
    "RatecodeID",
    "store_and_fwd_flag",
    "congestion_surcharge",
    "airport_fee",
]
pd.DataFrame(
    {
        "missing": {c: int(trips[c].is_missing().sum()) for c in nullable},
        "fill_value": {c: trips[c].fill_value for c in nullable},
    }
)
missing fill_value
passenger_count 1000 -1.0
RatecodeID 1000 -1.0
store_and_fwd_flag 1000 -1.0
congestion_surcharge 1000 NaN
airport_fee 1000 NaN

Datetime#

The pickup/dropoff columns are int64 seconds carrying a units attribute; the taxi.schema codec decodes them back to datetime64. The TLC timestamps are local NYC wall-clock (no timezone), preserved verbatim.

pickup = decode_datetime(trips["tpep_pickup_datetime"].read())
print("units:", trips["tpep_pickup_datetime"].units)
print("pickup range:", pickup.min(), "->", pickup.max())

units: seconds since 1970-01-01 00:00:00
pickup range: 2009-01-01T23:58:40 -> 2024-01-01T06:51:17

Querying with search indexes#

Table.select(...) takes a pyarrow-style expression. Selection.explain() reports which index answered each predicate. Three indexes are in play:

column

index

good for

payment_type

BITMAP

categorical equality / isin

total_amount

SORTED_ROWS

numeric range

tpep_pickup_datetime

CHUNK_MINMAX

range pruning by chunk

 
q = trips.select(field("payment_type") == "Credit card")
print("credit-card trips:", q.count)
q.explain()

credit-card trips: 19190

QueryPlan(nrows=25000, terms=[TermPlan(leaves=[LeafPlan(column='payment_type', op='==', negated=False, method='bitmap', note='')])], matched=19190)

q = trips.select(field("total_amount") > 50)
print("trips over $50:", q.count)
q.explain()

trips over $50: 1936

QueryPlan(nrows=25000, terms=[TermPlan(leaves=[LeafPlan(column='total_amount', op='>', negated=False, method='sorted_rows', note='')])], matched=1936)

lo = np.datetime64("2024-01-01T06:00:00").astype("datetime64[s]").astype("int64")
hi = np.datetime64("2024-01-01T07:00:00").astype("datetime64[s]").astype("int64")
pickup_at = field("tpep_pickup_datetime")
q = trips.select((pickup_at >= int(lo)) & (pickup_at < int(hi)))
print("pickups 6-7am on Jan 1:", q.count)
q.explain()  # chunk_minmax pruning: only a few candidate chunks are read

pickups 6-7am on Jan 1: 21

QueryPlan(nrows=25000, terms=[TermPlan(leaves=[LeafPlan(column='tpep_pickup_datetime', op='>=', negated=False, method='chunk_minmax+verify', note='1 candidate chunks'), LeafPlan(column='tpep_pickup_datetime', op='<', negated=False, method='scan', note='')])], matched=21)

Combining predicates and reading columns#

Boolean operators (&, |, ~), .isin(...), and .is_valid() / .is_null() compose. Selection.read(columns=...) materializes only the requested columns for the matching rows.

sel = trips.select(
    (field("total_amount") > 50)
    & (field("payment_type") == "Credit card")
    & field("airport_fee").is_valid()
)
print("matching rows:", sel.count)
result = sel.read(columns=["total_amount", "tip_amount", "payment_type"])
pd.DataFrame(result).head()

matching rows: 1566
total_amount tip_amount payment_type
0 64.95 10.00 Credit card
1 85.09 14.15 Credit card
2 127.94 0.00 Credit card
3 60.72 7.92 Credit card
4 67.49 11.25 Credit card

Storage and compression#

Because the numeric columns carry a Shuffle + Deflate filter pipeline, the stored size is a fraction of the logical (uncompressed) size.

rows = []
logical_total = stored_total = 0
for name in trips.column_names:
    ds = trips[name].dataset  # for categoricals this is the integer code dataset
    logical = int(np.prod(ds.shape)) * ds.dtype.itemsize
    stored = ds.id.get_storage_size()
    logical_total += logical
    stored_total += stored
    ratio = round(logical / max(stored, 1), 1)
    rows.append((name, str(ds.dtype), logical, stored, ratio))

df = pd.DataFrame(rows, columns=["column", "dtype", "logical_B", "stored_B", "ratio"])
overall = logical_total / max(stored_total, 1)
print(
    f"trips: {logical_total / 1e6:.2f} MB logical -> "
    f"{stored_total / 1e6:.2f} MB stored ({overall:.1f}x)"
)
df.sort_values("stored_B", ascending=False).head(10)

trips: 2.90 MB logical -> 0.60 MB stored (4.9x)
column dtype logical_B stored_B ratio
4 trip_distance float64 200000 126008 1.6
16 total_amount float64 200000 120247 1.7
13 tip_amount float64 200000 104610 1.9
10 fare_amount float64 200000 61060 3.3
2 tpep_dropoff_datetime int64 200000 45724 4.4
1 tpep_pickup_datetime int64 200000 44702 4.5
8 DOLocationID int32 100000 24077 4.2
7 PULocationID int32 100000 21602 4.6
14 tolls_amount float64 200000 10860 18.4
11 extra float64 200000 9172 21.8

The zones dimension#

PULocationID / DOLocationID reference the zones table (fixed-length string columns). h5col does not do joins, but the lookup is a plain pandas merge.

zdf = pd.DataFrame({c: zones[c].read() for c in zones.column_names})
zdf = zdf.set_index("LocationID")
zdf.head()
Borough Zone service_zone
LocationID
1 EWR Newark Airport EWR
2 Queens Jamaica Bay Boro Zone
3 Bronx Allerton/Pelham Gardens Boro Zone
4 Manhattan Alphabet City Yellow Zone
5 Staten Island Arden Heights Boro Zone 
# Where did the first few trips start?
pu = trips["PULocationID"].read()[:5]
zdf.loc[pu, ["Borough", "Zone"]]
Borough Zone
LocationID
186 Manhattan Penn Station/Madison Sq West
140 Manhattan Lenox Hill East
236 Manhattan Upper East Side North
79 Manhattan East Village
211 Manhattan SoHo

Real scale#

The full monthly files (~3M rows) are not committed. Fetch one on demand and rebuild against it:

from taxi import fetch
month = fetch.yellow_tripdata("2024-01")   # downloads ~50 MB into taxi/cache/
build(out="nyc_taxi_full.h5", sample_parquet=month)

Or from a shell:

pixi run -e examples python -m examples.taxi.fetch 2024-01

f.close()