🔧 Fix Public transit & GTFS Performance & scale Public Transit & GTFS · step 20 Notebook By · Updated

stop_times.txt is too big to load comfortably

On this page

Problem statement

Every other file in a GTFS feed loads instantly. stop_times.txt is different: one row per trip per stop, which in a mid-sized city is millions of rows and a quarter of a gigabyte of text. Reading it with default pandas settings works on a large machine and fails on a laptop or a CI runner, and the obvious escape — DuckDB — can fail too, with a CSV error hundreds of thousands of lines into a perfectly valid file.

TriMet's stop_times.txt is 227 MB with 3,403,393 rows. A default pd.read_csv peaks at 921 MB of process memory. Reading only the needed column in chunks peaks at 210 MB. DuckDB aggregates it in a fifth of a second — once it is told that the file uses quotes, which its sniffer missed because the first quoted field is on line 101,990.

Quick answer

Read only what the question needs, in the smallest types, and convert the file to Parquet once if you will query it repeatedly:

import duckdb

FEED = "trimet_gtfs"
src = f"""read_csv('{FEED}/stop_times.txt', quote='"',
          types={{'trip_id': 'VARCHAR', 'stop_id': 'VARCHAR',
                  'arrival_time': 'VARCHAR', 'departure_time': 'VARCHAR'}})"""
duckdb.sql(f"COPY (SELECT * FROM {src}) TO 'stop_times.parquet' (FORMAT parquet)")
per_stop = duckdb.sql("SELECT stop_id, count(*) AS calls FROM 'stop_times.parquet' GROUP BY 1").df()
print(len(per_stop), "stops;", int(per_stop.calls.sum()), "stop calls")
5959 stops; 3403393 stop calls

quote='"' is not optional: without it DuckDB decides from a sample that the file has no quoting and then fails on the first headsign with a comma in it. Identifiers and times are forced to text so no leading zeros are lost and 25:10:00 is not rejected as a time.

Peak process memory for four ways of reading TriMet stop_times.txt. peak memory reading a 227 MB stop_times.txtpandas defaults921 MBusecols + category663 MBDuckDB, quote set406 MB, 0.2 schunks of 500k207 MB0500 MB1000 MBVmHWM of a fresh process for each read; importing the libraries is part of every figure spatialworkflow.io
The same file, four reads, and an almost fivefold spread in peak memory.

Only the columns needed, small types, streaming or DuckDB

1. Decide which columns the question needs

Frequency needs trip_id, stop_id and departure_time. Travel times add arrival_time and stop_sequence. stop_headsign, shape_dist_traveled and timepoint are rarely needed and are among the widest columns.

2. Read with usecols and small types

category for IDs, int16 for stop_sequence, and text for times that you then convert to integer seconds. That keeps the frame small; the parse itself still needs working memory.

3. Stream when you only need an aggregate

Counts, sums and filters can be computed chunk by chunk with chunksize, and memory stays flat no matter how big the file is.

4. Or hand it to DuckDB

DuckDB reads CSV in parallel and aggregates without building a pandas frame. Give it quote='"' and explicit types for identifier and time columns.

5. Convert once to Parquet

A Parquet copy is a sixth of the size on disk, keeps the types, and makes every later query fast. Keep it next to the feed with the feed version in its name.

6. Filter by date before anything expensive

A single service day is typically a tenth of the file or less. Filtering to the trips active on your date first shrinks every later step.

Choosing how to read stop_times.txt by what the question needs. how to read stop_times.txtWhat do you need from the file?an aggregatechunks or DuckDBflat memorya tableusecols + small typesthen filter by datemany queriesconvert to Parquet oncea sixth the sizeWhichever branch, filter to one service day before anything expensive spatialworkflow.io
What you need from the file decides how to read it.

Read options measured in fresh processes

The data used on this page

TriMet's GTFS feed for Portland, Oregon, downloaded from developer.trimet.org and unzipped to trimet_gtfs/. TriMet republishes it every few weeks, so counts of trips and stop times move between versions; the setup prints the version the numbers on this page came from.

import io
import os
import zipfile

import pandas as pd
import requests

if not os.path.exists("trimet_gtfs"):
    feed = requests.get("https://developer.trimet.org/schedule/gtfs.zip", timeout=600)
    zipfile.ZipFile(io.BytesIO(feed.content)).extractall("trimet_gtfs")
info = pd.read_csv("trimet_gtfs/feed_info.txt", dtype=str).iloc[0]
print(f"TriMet feed {info.feed_version}, valid {info.feed_start_date} to {info.feed_end_date}")
TriMet feed 20260927-20261007-0900, valid 20260927 to 20270227

Example 1 — measure the options in fresh processes

import subprocess, sys

SETUP = f"import time, pandas as pd, duckdb\nF = '{FEED}/stop_times.txt'\nt = time.time()\n"
# VmHWM is this process's own peak; ru_maxrss would inherit the parent's across exec
REPORT = ("\nhwm = [l for l in open('/proc/self/status') if l.startswith('VmHWM')][0]"
          "\nprint(f'{time.time() - t:4.1f} s  {int(hwm.split()[1]) / 1024:5.0f} MB peak')")
READS = {
    "pandas defaults": "pd.read_csv(F).groupby('stop_id').size()",
    "usecols + category": "pd.read_csv(F, usecols=['trip_id', 'stop_id', 'departure_time'], "
                          "dtype='category').groupby('stop_id', observed=True).size()",
    "chunks of 500k": "pd.concat([c.stop_id.value_counts() for c in pd.read_csv("
                      "F, usecols=['stop_id'], dtype=str, chunksize=500_000)]).groupby(level=0).sum()",
    "duckdb": r"""duckdb.sql(f"SELECT stop_id, count(*) FROM read_csv('{F}', quote='\"', """
              r"""all_varchar=true) GROUP BY 1").df()""",
}
for name, expr in READS.items():
    out = subprocess.run([sys.executable, "-c", SETUP + expr + REPORT],
                         capture_output=True, text=True)
    print(f"{name:20} {out.stdout.strip() or out.stderr.strip().splitlines()[-1]}")
pandas defaults      2.4 s    921 MB peak
usecols + category   2.9 s    667 MB peak
chunks of 500k       1.0 s    210 MB peak
duckdb               0.2 s    409 MB peak

Peak memory includes the libraries themselves: importing pandas and DuckDB takes this process to 150 MB before any data is read.

Example 2 — the DuckDB failure, reproduced

try:
    duckdb.sql(f"SELECT count(*) FROM read_csv('{FEED}/stop_times.txt')").fetchall()
except duckdb.Error as e:
    print(str(e).splitlines()[0])
    print([l for l in str(e).splitlines() if l.startswith("Original Line")][0])
Invalid Input Error: CSV Error on Line: 101990
Original Line: 18124337,04:59:00,04:59:00,2253,1,"Parkrose TC, continues to Portland",0,0,0.0,1

The sniffer samples the first 20,480 rows by default. None of them contains a quoted field, so it concludes the file has no quote character, and the first headsign with a comma inside quotes — "Parkrose TC, continues to Portland" — then splits into two columns.

Example 3 — Parquet on disk

import os
print(f"CSV {os.path.getsize(f'{FEED}/stop_times.txt') / 1e6:.0f} MB -> "
      f"Parquet {os.path.getsize('stop_times.parquet') / 1e6:.1f} MB")
CSV 227 MB -> Parquet 39.4 MB

Why the default read is so expensive

Why the default read is so expensive

pandas infers a type per column by reading the text, holds the raw data and the parsed columns at once while it does, and stores every text column — trip IDs, times, headsigns — as full strings. The frame itself is 384 MB with pandas 3's string type; the parse needs more than twice that at peak.

Why chunking is so cheap

An aggregate like "calls per stop" needs only a running count per stop, which is a few thousand numbers. Reading 500,000 rows at a time keeps the working set to one chunk, so peak memory is set by the chunk size, not the file size.

Why the sniffer fails on a valid file

Automatic CSV dialect detection is a guess from a sample. A feed where only a few late trips carry a quoted headsign looks unquoted for the first hundred thousand rows. Stating the dialect — the quote character, and the types of the columns that matter — removes the guess.

The DuckDB CSV sniffer sample and the first quoted field in TriMet stop_times.txt. reading down stop_times.txtheaderline 1sniffer sampleendsrow 20,480: no quotes seenfirst quotedheadsignline 101,990"Parkrose TC,continues…"11 columns, not 10end of filerow 3,402,538Stating the quote character replaces the guess with a fact spatialworkflow.io
The sample said "no quotes"; line 101,990 disagreed.

Blank times, Polars and zipped files

  • Blank times on non-timepoint stops read as missing; interpolate before converting to seconds.
  • all_varchar=true in DuckDB is the bluntest safe option when you only need to count or filter.
  • Polars reads the file quickly too, and has the same need for explicit string types on identifiers.
  • Zip files: DuckDB can read CSV inside a zip through the zipfs extension, or unzip first.
  • CI runners often have 2–4 GB of memory; a default read of a large regional feed will not fit.
  • Keep the Parquet copy versioned with the feed it came from.

FAQ

How do I load a large stop_times.txt in pandas?

Read only the columns you need with usecols, store IDs as category, and stream in chunks if you only need an aggregate.

Why does DuckDB fail on stop_times.txt?

Its CSV sniffer can miss the quote character when the first quoted field is far down the file. Pass quote='"'.

Should I convert GTFS to Parquet?

Yes, if you query it more than once. It is much smaller and every later read is faster.

How much memory does stop_times.txt need?

For TriMet's 227 MB file, 921 MB peak with pandas defaults and 210 MB streamed in chunks.

Should I read times as datetimes?

No. Read them as text and convert to seconds; GTFS times can exceed 24:00.

What is the fastest way to count calls per stop?

DuckDB or a chunked pandas read; both avoid building the whole frame.