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.

This page covers the join logic, validation patterns, temporal arithmetic, and remediation strategies required to keep these two files consistent at production scale. For the broader context of how stops and stop_times fit into the full static feed, see GTFS Feed Architecture & Fundamentals.

Prerequisites

Before implementing the validation pipeline, ensure your environment meets these requirements:

• Python 3.9+ with pandas >= 2.0 and numpy installed
• pip install: pip install pandas numpy pyarrow
• A raw GTFS static feed (ZIP or extracted CSV directory) containing both stops.txt and stop_times.txt
• Familiarity with Understanding GTFS Static Feed Structure, particularly how primary and foreign keys relate across GTFS files
• A working directory with write permissions for validation logs and cleaned outputs

Concept and Spec Background

The one-to-many spatial-temporal join

In the GTFS Schedule specification, stops.txt is the spatial reference table. It defines physical locations with stop_id as the primary key, plus stop_lat and stop_lon for geographic positioning. stop_times.txt is the temporal schedule table — it records when a vehicle visits each stop along a specific trip. The relationship is strictly one-to-many: a single stop_id can appear across thousands of stop_times.txt rows, but every stop_times.txt row must reference a valid stop_id from stops.txt.

The following diagram shows that relationship and how the data flows from raw files through validation into a clean, persistent output.

Spec-reference table

Step-by-Step Implementation

Step 1 — Schema ingestion with explicit dtype enforcement

Load both CSVs with dtype={'stop_id': str} to prevent leading-zero truncation. This is the most common silent failure in automated GTFS pipelines: when pandas auto-infers stop_id as numeric, any agency using zero-padded identifiers (e.g. "0042") loses the leading zeros, making every downstream join silently return 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'],
)

required_stops_cols = {'stop_id', 'stop_lat', 'stop_lon'}
required_times_cols = {'trip_id', 'stop_id', 'stop_sequence', 'arrival_time', 'departure_time'}

missing_stops = required_stops_cols - set(stops.columns)
missing_times = required_times_cols - set(stop_times.columns)

if missing_stops:
    raise ValueError(f"stops.txt missing required columns: {missing_stops}")
if missing_times:
    raise ValueError(f"stop_times.txt missing required columns: {missing_times}")

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

Step 2 — Relational join and orphan detection

Perform a left join from stop_times onto stops on stop_id. Rows where the indicator is left_only are orphaned schedule records: they reference a stop_id that does not exist in stops.txt. 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%} of total)")

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

# Unused 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)].copy()
print(f"Unused stops (no scheduled visits): {len(unused_stops):,}")

Unused stops are technically valid per the spec (future expansions, mothballed platforms) but inflate feed payload and slow parsing. Flag them for agency review rather than silently deleting them.

Step 3 — Sequence and temporal consistency validation

A valid trip requires monotonically increasing stop_sequence values and non-decreasing arrival_time/departure_time pairs. GTFS permits times exceeding 24:00:00 for overnight service — standard datetime.strptime() rejects these, but pd.to_timedelta() handles them correctly because it models elapsed duration rather than wall-clock time. For the broader context of how timezone handling and schedule normalization interacts with these values across agency timezone boundaries, see the dedicated page on that topic.

def validate_trip(group: pd.DataFrame) -> pd.Series:
    seq = group['stop_sequence'].reset_index(drop=True)
    # to_timedelta handles "25:30:00" correctly; strptime would raise ValueError
    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
    # arrival must not exceed departure at the same stop
    dwell_valid = (arr <= dep).all()
    # next stop's arrival must be >= this stop's departure (no time travel)
    # compare arr[i+1] >= dep[i]; use shift and drop the final NaT
    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_checks = (
    stop_times
    .sort_values(['trip_id', 'stop_sequence'])
    .groupby('trip_id', sort=False)
    .apply(validate_trip)
)

invalid_trips = trip_checks[~(
    trip_checks['seq_monotonic']
    & trip_checks['dwell_valid']
    & trip_checks['continuity_valid']
)]
print(f"Trips failing sequence/time checks: {len(invalid_trips):,}")
if not invalid_trips.empty:
    print(invalid_trips.head(10).to_string())

Step 4 — Coordinate bounds and spatial integrity

Geographic coordinates must fall within valid WGS84 bounds. Out-of-bounds values often indicate unit errors (degrees vs. radians), accidental sign flips for southern-hemisphere agencies, or CSV export truncation. Additionally, stops clustered within a few meters of each other frequently indicate platform-level duplicates or GPS drift during field surveys — these break distance calculations in routing engines even when referential integrity is intact. For a deeper treatment of coordinate systems and projection transformations, see Coordinate Reference Systems for Transit Data.

lat_in_bounds = stops['stop_lat'].between(-90, 90)
lon_in_bounds = stops['stop_lon'].between(-180, 180)
coord_errors = stops[~(lat_in_bounds & lon_in_bounds)].copy()
print(f"Stops with out-of-bounds coordinates: {len(coord_errors):,}")

# Flag missing coordinate values (NaN lat/lon is a separate class of error)
coord_missing = stops[stops['stop_lat'].isna() | stops['stop_lon'].isna()]
print(f"Stops with null coordinates: {len(coord_missing):,}")

Validation and Verification

After running all checks, consolidate results into a structured summary before deciding whether to proceed with ingestion:

validation_summary = {
    'total_stops': len(stops),
    'total_stop_times': len(stop_times),
    'orphaned_stop_times': len(orphans),
    'orphan_rate_pct': round(orphan_rate * 100, 3),
    'unused_stops': len(unused_stops),
    'invalid_trips': len(invalid_trips),
    'coord_errors': len(coord_errors),
    'coord_missing': len(coord_missing),
}

for key, val in validation_summary.items():
    print(f"  {key}: {val}")

# Hard gate: no orphans or coordinate errors allowed in production ingestion
assert validation_summary['orphaned_stop_times'] == 0, "Orphaned stop_times detected"
assert validation_summary['coord_errors'] == 0, "Out-of-bounds coordinates detected"
assert validation_summary['invalid_trips'] == 0, "Trips with sequence/time violations detected"

print("All referential integrity checks passed.")

For feeds that fail the hard gate, route the output to a remediation pipeline (see the section below) rather than discarding the entire feed — partial failure should not prevent processing the valid subset.

Failure Modes and Edge Cases

• Leading-zero stop_id truncation. Agencies in some regions use numeric stop codes with leading zeros ("0042"). If pandas infers the column as int64, the merge silently returns zero matches. Always declare dtype={'stop_id': str}.
• Times exceeding 24:00:00. Any overnight trip will have stop times like 25:30:00 or 26:45:00. Python’s datetime.strptime raises ValueError for these. Use pd.to_timedelta() throughout — it correctly models elapsed seconds from service-day start.
• Duplicate stop_id in stops.txt. The GTFS spec forbids duplicate primary keys, but real-agency feeds often contain them when platform-level child stops share a code with the parent station. Deduplicate with stops.drop_duplicates(subset='stop_id', keep='first') after sorting by location_type ascending so parent records are retained.
• Non-integer stop_sequence. Some agencies export floats (1.0, 2.0) or alphanumeric codes ("A1", "B3"). Cast with pd.to_numeric(stop_times['stop_sequence'], errors='coerce').astype('Int64') and flag any resulting NaN values.
• Mixed encoding in CSV exports. Agencies occasionally export UTF-8 BOM-encoded files, causing the first column name to appear as stop_id. Load with encoding='utf-8-sig' to strip the BOM automatically.
• Stops present in multiple parent stations. The parent_station field in stops.txt can create ambiguous hierarchy: a child platform may reference a parent that is itself a child. Validate with a graph traversal rather than a flat join to detect circular references.
• Midnight-spanning service day boundary. An agency may define service day as ending at 28:00:00 (4 AM the next calendar day). When comparing arrival times across trips from different service calendars, always anchor relative to the service date, not the calendar date.

Remediation Patterns for Common Failures

When the validation gate fails, apply deterministic fixes before re-running ingestion — never silently drop records without logging the decision.

Orphaned stop_times records: Cross-reference with the previous feed version’s stops.txt. If the missing stop_id exists there with valid coordinates, backfill from the historical snapshot. If no historical match exists, isolate the affected trips and route them to a quarantine table rather than including them in the clean output.

# Quarantine orphaned trips rather than deleting them
orphan_trip_ids = orphans['trip_id'].unique()
clean_stop_times = stop_times[~stop_times['trip_id'].isin(orphan_trip_ids)].copy()
quarantine = stop_times[stop_times['trip_id'].isin(orphan_trip_ids)].copy()
quarantine['quarantine_reason'] = 'orphaned_stop_id'

print(f"Clean stop_times retained: {len(clean_stop_times):,}")
print(f"Quarantined records: {len(quarantine):,}")

Duplicate stop_id in stops.txt: Merge platform-level coordinates via spatial averaging and consolidate child records before re-exporting. Always maintain an audit trail mapping old stop_id values to the canonical identifier.

Out-of-sequence stop_sequence: Re-sort within each trip_id by arrival_time (after converting to timedelta) and reassign integer sequence values starting from 1. This is the safest remediation when the time data is trustworthy but the sequence column was incorrectly generated during export.

def reassign_sequence(group: pd.DataFrame) -> pd.DataFrame:
    group = group.copy()
    group['_arr_td'] = pd.to_timedelta(group['arrival_time'])
    group = group.sort_values('_arr_td').reset_index(drop=True)
    group['stop_sequence'] = group.index + 1
    return group.drop(columns=['_arr_td'])

fixed_stop_times = (
    clean_stop_times
    .groupby('trip_id', sort=False)
    .apply(reassign_sequence)
    .reset_index(drop=True)
)

For a complete implementation of missing record recovery, see Fixing Missing stop_times.txt Records in Python.

Performance and Scale Notes

Large metropolitan agencies routinely distribute stop_times.txt files exceeding 500 MB uncompressed. Loading these entirely into memory can trigger MemoryError exceptions or degrade pipeline throughput. The memory-efficient processing for large feeds page covers chunked reading and Parquet pipelines in depth; the strategies most directly applicable to this join are:

Use pyarrow engine for columnar loading. Pass engine='pyarrow' to pd.read_csv() for both files. This enables zero-copy reads and avoids materializing intermediate Python objects for each cell.

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

Chunk stop_times.txt for validation. When the file exceeds available RAM, validate in chunks partitioned by trip_id. Read the full stops.txt into memory first (it is typically < 5 MB even for large agencies), then stream stop_times.txt in chunks, joining each chunk against the in-memory stops DataFrame.

stops_lookup = stops.set_index('stop_id')[['stop_lat', 'stop_lon']]

chunk_results = []
for chunk in pd.read_csv(
    'stop_times.txt',
    dtype={'stop_id': str, 'trip_id': str},
    chunksize=500_000,
):
    merged_chunk = chunk.merge(
        stops_lookup, left_on='stop_id', right_index=True, how='left', indicator=True
    )
    orphan_chunk = merged_chunk[merged_chunk['_merge'] == 'left_only']
    chunk_results.append({
        'total': len(chunk),
        'orphans': len(orphan_chunk),
    })

total_records = sum(r['total'] for r in chunk_results)
total_orphans = sum(r['orphans'] for r in chunk_results)
print(f"Processed {total_records:,} records; {total_orphans:,} orphans found.")

Persist cleaned output as Parquet. After validation and remediation, write the clean stops and stop_times DataFrames to Parquet with pyarrow compression. Subsequent pipeline steps (routing graph construction, GTFS validation rules enforcement, schedule normalization) read from Parquet at a fraction of the CSV loading cost.

import pyarrow as pa
import pyarrow.parquet as pq

pq.write_table(
    pa.Table.from_pandas(stops, preserve_index=False),
    'stops.parquet',
    compression='snappy',
)
pq.write_table(
    pa.Table.from_pandas(clean_stop_times, preserve_index=False),
    'stop_times.parquet',
    compression='snappy',
)
print("Clean feed written to Parquet.")

Multi-agency batching. When processing multiple agency feeds, parameterise all paths and thresholds through a YAML/JSON configuration file. Log per-agency validation metrics to a centralized summary table indexed by feed_url and ingestion_timestamp, and enforce fail-fast thresholds via configuration rather than hardcoded constants. This prevents threshold drift as your agency portfolio grows.

Frequently Asked Questions

Related

• Fixing Missing stop_times.txt Records in Python — production implementation for backfilling orphaned schedule records
• GTFS Validation Rules and Common Schema Errors — full spec-level validation pipeline covering all GTFS files
• Timezone Handling and Schedule Normalization — converting GTFS elapsed-time values to UTC-anchored timestamps
• Coordinate Reference Systems for Transit Data — WGS84 validation and CRS projection transformations for stop coordinates
• Memory-Efficient Processing for Large Feeds — chunked reading, Parquet output, and multi-agency batching

Up: GTFS Feed Architecture & Fundamentals | Home