Mastering stops.txt and stop_times.txt Relationships

The stops.txt and stop_times.txt tables form the spatial-temporal backbone of every GTFS dataset. Every routing engine, schedule visualizer, and real-time prediction model depends on a clean, referentially intact join between these two files. When that join breaks — due to orphaned foreign keys, sequence violations, or silent type coercion — failures cascade silently: trips disappear from network graphs, vehicles are matched to phantom locations, and schedule APIs return stale or incorrect data.

The pages in this section provide the Python-specific patterns for loading, joining, validating, and remediating these files at production scale. For the underlying GTFS specification context, see Mastering stops.txt and stop_times.txt Relationships (Architecture) in the architecture fundamentals section.

Prerequisites

• Python 3.9+ with pandas >= 2.0, numpy, and pyarrow
• A raw GTFS feed (ZIP or directory) containing stops.txt and stop_times.txt
• pip install pandas numpy pyarrow
• Basic familiarity with pandas merge operations and groupby aggregations

Key Spec Constraints

Step 1 — Ingest with Explicit dtypes

The most common silent failure in GTFS pipelines is stop_id coercion. When pandas auto-infers stop_id as numeric, zero-padded identifiers like "0042" lose their leading zeros and every downstream join silently returns zero matches.

import pandas as pd
import numpy as np

stops = pd.read_csv(
    "stops.txt",
    dtype={"stop_id": str},
    usecols=["stop_id", "stop_lat", "stop_lon", "stop_name", "location_type"],
)

stop_times = pd.read_csv(
    "stop_times.txt",
    dtype={"stop_id": str, "trip_id": str},
    usecols=["trip_id", "stop_id", "stop_sequence", "arrival_time", "departure_time"],
)

print(f"Loaded {len(stops):,} stops and {len(stop_times):,} stop_times records.")

Step 2 — Orphan Detection via Left Join

A left join from stop_times onto stops isolates records referencing missing stop_id values. This happens when agencies delete or rename stops mid-cycle without regenerating the full feed export.

merged = stop_times.merge(
    stops[["stop_id", "stop_lat", "stop_lon"]],
    on="stop_id",
    how="left",
    indicator=True,
)

orphans = merged[merged["_merge"] == "left_only"].copy()
orphan_rate = len(orphans) / len(stop_times)
print(f"Orphaned stop_times: {len(orphans):,} ({orphan_rate:.2%})")

if orphan_rate > 0.005:
    raise RuntimeError(
        f"Orphan rate {orphan_rate:.2%} exceeds 0.5% threshold — halting ingestion."
    )

# Stops present in stops.txt but never scheduled
used_stop_ids = stop_times["stop_id"].unique()
unused_stops = stops[~stops["stop_id"].isin(used_stop_ids)]
print(f"Unused stops (no scheduled visits): {len(unused_stops):,}")

Step 3 — Sequence and Temporal Validation

GTFS permits times exceeding 24:00:00 for overnight service. Standard datetime.strptime() rejects these values — use pd.to_timedelta() instead, which models elapsed duration rather than wall-clock time.

def validate_trip(group: pd.DataFrame) -> pd.Series:
    seq = group["stop_sequence"].reset_index(drop=True)
    arr = pd.to_timedelta(group["arrival_time"].reset_index(drop=True))
    dep = pd.to_timedelta(group["departure_time"].reset_index(drop=True))

    seq_monotonic = seq.is_monotonic_increasing
    dwell_valid = (arr <= dep).all()
    continuity_valid = bool((arr.shift(-1).dropna() >= dep.iloc[:-1]).all())

    return pd.Series({
        "seq_monotonic": seq_monotonic,
        "dwell_valid": dwell_valid,
        "continuity_valid": continuity_valid,
    })

trip_validation = stop_times.groupby("trip_id").apply(validate_trip)
invalid_trips = trip_validation[~trip_validation.all(axis=1)]
print(f"Trips failing validation: {len(invalid_trips):,}")

Step 4 — Coordinate Bounds and Duplicate Detection

lat_ok = stops["stop_lat"].between(-90, 90)
lon_ok = stops["stop_lon"].between(-180, 180)
coord_violations = stops[~(lat_ok & lon_ok)]
print(f"Coordinate bound violations: {len(coord_violations):,}")

# Near-duplicate detection (stops within ~10 m of each other)
stops["lat_r"] = stops["stop_lat"].round(4)
stops["lon_r"] = stops["stop_lon"].round(4)
near_dupes = stops[stops.duplicated(subset=["lat_r", "lon_r"], keep=False)]
print(f"Near-duplicate stop coordinates: {len(near_dupes):,}")

Common Failure Modes

• Leading-zero truncation on stop_id. Always use dtype=str — never let pandas infer numeric types for ID columns.

• Frequency-governed trips in stop_times.txt. When a trip_id appears in frequencies.txt, its stop_times.txt rows define relative offsets, not absolute departure times. Validate these trips with a separate code path. See Handling Frequency-Based vs Timetable Schedules.

• Parent station stop_id reuse. Some feeds use the same stop_id for a parent station (location_type=1) and a child platform (location_type=0). This is a spec violation but appears in real feeds. Deduplicate by sorting location_type ascending and keeping the first occurrence.

• Missing stop_times.txt records for a trip. A trip_id in trips.txt with zero rows in stop_times.txt produces an invisible trip. For remediation patterns, see Fixing Missing stop_times.txt Records in Python.

In This Section

• Fixing Missing stop_times.txt Records in Python — orphaned trip detection, sequence gap analysis, foreign key validation, and spec-compliant patching

Related

• Mastering stops.txt and stop_times.txt Relationships (Architecture) — spec reference and in-depth join logic
• Handling Frequency-Based vs Timetable Schedules — distinguishing frequency trips from timetable trips before validating stop_times
• GTFS Validation Rules and Common Schema Errors — full referential integrity checks across all GTFS files
• Memory-Efficient Processing for Large Feeds — chunked reading and Parquet output for metro-scale feeds
• Timezone Handling and Schedule Normalization — converting stop_times departure values to UTC

Up: Python Parsing & Data Normalization | Home