1. Home
  2. Python Parsing Data Normalization
  3. Mastering Stopstxt And Stop Timestxt Relationships

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.

stops.txt and stop_times.txt join validation pipeline Diagram showing stops.txt and stop_times.txt being merged on stop_id into a validation layer checking orphans, sequence order, temporal continuity and coordinate bounds, then outputting clean Parquet or an error report. stops.txt stop_id (PK) stop_lat / stop_lon stop_name location_type stop_times.txt trip_id (FK) stop_id (FK → stops) stop_sequence arrival / departure_time LEFT JOIN on stop_id Validation Orphan detection Unused stops Sequence monotonic Time continuity WGS84 bounds Coord duplicates Clean Parquet Error Report

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

Constraint Rule Consequence of violation
stop_id FK in stop_times.txt Must exist in stops.txt Orphaned schedule record; routing graph edge missing
stop_sequence per trip_id Positive integers, monotonically increasing Routing order undefined
arrival_time / departure_time Non-decreasing within a trip; arrival ≤ departure Schedule timeline breaks
stop_lat / stop_lon WGS84: lat in [−90, 90], lon in [−180, 180] Distance calculations corrupt
Times > 24:00:00 Allowed — represents service past midnight on same service day Standard parsers reject these

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.

python 
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.

python 
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.

python 
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

python 
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):,}")
The join that finds orphaned stop times A left join from stop_times.txt to stops.txt on stop_id: every row whose right side is null is a call at a stop the feed never defines. stop_times.txt trip_id FK stop_id FK stop_sequence stops.txt stop_id PK stop_name stop_lat / stop_lon orphan rows stop_id unmatched → quarantine → report to agency left join no match

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.

Defects found in stop_times.txt across forty agency feeds How often each integrity defect appears: orphaned stop references are rare, but non-monotonic times and duplicated sequence numbers are not. Times that decrease within a trip 11 feeds usually a midnight rollover, not an error Duplicate stop_sequence in a trip 7 feeds genuine defect; the order is undefined stop_id absent from stops.txt 3 feeds the feed is internally inconsistent Trips with a single call 2 feeds a trip that stops once goes nowhere out of forty feeds surveyed; only the last two are always defects

Validation and Verification

A stop-and-stop-time pipeline is easy to write and easy to write wrongly, because a feed that fails every check below still loads without complaint. These five assertions are the ones worth running on every ingest, in roughly this order — each is cheap, and each catches a defect the ones above it cannot see.

python 
def verify_stop_relationships(stops, stop_times) -> None:
    # 1. Every call resolves to a defined place.
    orphans = set(stop_times["stop_id"]) - set(stops["stop_id"])
    assert not orphans, f"{len(orphans)} stop_id(s) called at but never defined"

    # 2. Sequence numbers strictly increase within each trip. Gaps are legal;
    #    repeats and reversals are not, because they leave the order undefined.
    ordered = stop_times.sort_values(["trip_id", "stop_sequence"])
    rising = ordered.groupby("trip_id")["stop_sequence"].apply(
        lambda s: s.is_monotonic_increasing and s.is_unique)
    assert rising.all(), f"{int((~rising).sum())} trip(s) have unusable stop_sequence"

    # 3. Times never run backwards within a trip.
    monotonic = ordered.groupby("trip_id")["departure_s"].apply(
        lambda s: s.dropna().is_monotonic_increasing)
    assert monotonic.all(), f"{int((~monotonic).sum())} trip(s) run backwards in time"

    # 4. Every trip calls at least twice — a trip with one call goes nowhere.
    calls = stop_times.groupby("trip_id").size()
    assert (calls >= 2).all(), f"{int((calls < 2).sum())} trip(s) have a single call"

    # 5. Coordinates are inside the WGS84 envelope and not at the null island.
    bad = stops[(stops["stop_lat"].abs() > 90) | (stops["stop_lon"].abs() > 180)
                | ((stops["stop_lat"] == 0) & (stops["stop_lon"] == 0))]
    assert bad.empty, f"{len(bad)} stop(s) with impossible coordinates"

The fifth check deserves a note. A coordinate of exactly zero, zero is syntactically valid and lands in the Gulf of Guinea, several thousand kilometres from any transit network. It is how a missing coordinate is written when a producer fills nulls with zeros, and it wrecks any bounding box, any nearest-stop query and any spatial join computed over the feed. Treating it as a defect rather than as data is almost always right.

The second check is worth separating from the third. A trip whose stop_sequence values repeat has no defined call order at all, so the time check that follows it is testing an arbitrary ordering. Running them in this order means the diagnostic names the real problem rather than a symptom of it.

Performance and Scale Notes

stop_times.txt holds roughly 87% of a feed’s rows — around 1.8 million for a metropolitan network against 31,000 trips and 8,900 stops — so every decision about it dominates the pipeline’s cost. Three of them account for nearly all the difference.

Read fewer columns. The orphan, sequence and time checks need five columns: trip_id, stop_id, stop_sequence, arrival_time and departure_time. Passing usecols at read time means the rest are never parsed or allocated, which on a wide feed roughly halves both time and memory before anything else is tried.

Type the identifiers deliberately. Read as text — which is mandatory for correctness, not merely tidy — trip_id and stop_id are the two most expensive columns in the table. Cast them to category after validation and the frame typically shrinks by half again, because each distinct value is stored once rather than per row.

Do the joins with indexes, not with scans. The orphan check is a set membership test against a few thousand identifiers, which pandas performs in well under a second across 1.8 million rows. Written as a per-row lookup inside apply, the same check takes minutes — and that difference is the usual reason integrity checking gets quietly dropped from a pipeline.

Beyond about a gigabyte the right move stops being optimisation and starts being a change of approach: chunked reading where the computation reduces, and a columnar store where it does not. Converting once to partitioned Parquet turns every subsequent question about the table into a scan of the few columns it needs, which is why any pipeline that queries the same feed more than a handful of times should do it.

Frequently Asked Questions

Do gaps in stop_sequence mean the feed is broken?

No. Only the relative order matters, so a trip whose calls are numbered 1, 2, 5, 6 is perfectly valid — agencies commonly leave gaps so a stop can be inserted later without renumbering. What is not valid is a repeated or decreasing value, because that leaves the call order undefined.

Why does my join to stops.txt return no rows at all?

Almost always a dtype mismatch. If stop_id is read as an integer in one frame and as text in the other, no value ever compares equal and the join returns nothing. Read every identifier as text in both frames, and the problem disappears permanently.

Can two rows in stop_times.txt share a trip_id and stop_id?

Yes, and it is normal on a loop route that calls at the same stop twice. The composite key is trip_id plus stop_sequence, never trip_id plus stop_id, which is exactly why a realtime join on the two-column key doubles every prediction on a circular service.

What should I do with a trip that has no stop_times rows at all?

Remove the trip and report it. A trip with no calls cannot be presented to a rider, cannot be matched by a realtime feed, and inflates every count taken from trips.txt. Inventing a schedule for it is worse than dropping it.

Why the composite key is not negotiable

Almost every defect on this page traces back to one modelling fact: a row in stop_times.txt is identified by trip_id plus stop_sequence, and never by trip_id plus stop_id. A circular route calls at the same stop twice on the same trip, so the second key is not unique — and a join built on it silently produces two rows where one belongs, doubling every count taken downstream and every prediction served from it.

The consequence runs further than joins. Deduplication, interpolation between timepoints, delay propagation and block reconstruction all operate on a trip’s ordered calls, and the order comes from stop_sequence alone. A pipeline that sorts by stop_id, by arrival time, or by file order will be correct on the majority of trips and wrong on exactly the ones — loops, short turns, out-and-back services — where the ordering actually carries information.

Reading a trip as a sequence rather than a set

The practical habit that follows is to load a trip’s calls the same way every time: filter to the trip, sort by stop_sequence, and only then compute. Written once as a helper and used everywhere, it removes the whole class of ordering bugs, and it makes the cost visible — the sort is over a few dozen rows per trip rather than over the whole table, provided the filter comes first.

The same discipline applies in reverse when writing. Any process that emits stop_times rows — an expansion of frequency-based service, an interpolation that fills untimed calls, a merge across agencies — must assign stop_sequence values that preserve the intended order and stay unique within the trip. Renumbering from one on output is tempting and usually harmless, but it breaks any external reference to the original numbering, including a realtime feed that identifies calls by sequence position. Preserving the published numbers, gaps and all, is the safer default.

Failure Modes and Edge Cases

  • stop_id values that differ only in case. STOP_1 and stop_1 are distinct identifiers to a join and identical to a human reading the file. Feeds that mix them usually intend one stop, and the result is half the calls attached to a stop that has no coordinates.
  • Platforms published as separate stops with near-identical names. Common on rail feeds without a parent-station hierarchy. Nothing is invalid, and every interchange between them has to be inferred from proximity rather than known, which is the case station modelling exists to fix.
  • Calls with a blank arrival_time and departure_time. Legal at a non-timepoint stop, where the time is meant to be interpolated between the surrounding timepoints. Treating the blank as zero places the call at the start of the service day; dropping the row loses a stop the vehicle genuinely serves.
  • A stop_sequence that starts at zero on some trips and one on others. Both are valid — only the order matters — but code that assumes a particular first value will silently mis- identify the origin stop on half the feed.
  • Stops shared between agencies in a merged feed. Two operators calling at the same physical platform will have published two different stop_id values for it. Merging without reconciling them produces two stops at the same coordinates, and every interchange between the two networks disappears.
  • Very long trips. A long-distance service can have several hundred calls, and any per-trip computation that builds an intermediate structure per call will use far more memory on those trips than the average suggests. Size buffers by the longest trip, not the median.

In This Section

Up: Python Parsing & Data Normalization | Home