Fixing Missing stop_times.txt Records in Python

Use vectorized anti-joins in pandas to cross-reference trips.txt and stops.txt, enforce nullable Int64 for stop_sequence to prevent silent float coercion, and output either a patched feed or a machine-readable anomaly report. Never silently interpolate missing records — downstream routing engines treat every stop_times.txt row as authoritative schedule data.

Root Cause Analysis

Missing stop_times.txt records rarely occur at random. Three structural failure patterns account for the vast majority of real-agency incidents.

Schedule truncation is the most frequent cause. An agency exports only peak-hour trips from their scheduling software but leaves off-peak or seasonal trip_id values intact in trips.txt. The mismatch is invisible until a routing query returns no itineraries for those services. Understanding the stops.txt and stop_times.txt relationship model is essential before any repair: the (trip_id, stop_sequence) pair is effectively a composite key, and patching that violates sequence continuity will break isochrone generation and transfer-window calculations downstream.

ETL misalignment causes rows to vanish during CSV export. Column-order shifts between agency software versions, delimiter changes (tab-to-comma), and UTF-8 BOM encoding errors all truncate or corrupt rows. A pipeline that reads column positions rather than names will silently discard entire blocks.

Referential drift occurs when stop_id or trip_id values in stop_times.txt point to entities that were renamed or retired in their parent tables. Agencies sometimes update stops.txt identifiers for a network redesign but forget to propagate changes into historical stop_times.txt exports. GTFS validation tools will flag these as foreign-key violations; catching them before patching prevents you from masking the underlying referential drift.

Production-Ready Python Implementation

The script below uses pandas for vectorized joins and explicit dtype enforcement. It detects all three failure classes, exports a structured diagnostic CSV, and optionally generates placeholder records for sequence gaps only — never for completely missing trips.

import pandas as pd
import numpy as np
from pathlib import Path
import logging

logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")

REQUIRED_ST_COLS = {"trip_id", "stop_id", "arrival_time", "departure_time", "stop_sequence"}


def audit_and_patch_stop_times(feed_dir: str, patch: bool = False) -> pd.DataFrame:
    """
    Audit a GTFS feed for missing or inconsistent stop_times.txt records.

    Parameters
    ----------
    feed_dir : str
        Path to the directory containing unpacked GTFS .txt files.
    patch : bool
        If True, write a stop_times_patched.txt with placeholder rows
        for intra-trip sequence gaps. Completely missing trips are never
        auto-filled — they require agency-supplied data.

    Returns
    -------
    pd.DataFrame
        Diagnostic summary with issue_type, count, and sample_ids columns.
    """
    feed_path = Path(feed_dir)

    # --- Load with explicit dtypes to prevent silent coercion ---
    trips = pd.read_csv(
        feed_path / "trips.txt",
        dtype={"trip_id": str, "route_id": str, "service_id": str},
        usecols=["trip_id", "route_id", "service_id"],
    )
    stops = pd.read_csv(
        feed_path / "stops.txt",
        dtype={"stop_id": str},
        usecols=["stop_id"],
    )
    stop_times = pd.read_csv(
        feed_path / "stop_times.txt",
        dtype={
            "trip_id": str,
            "stop_id": str,
            "arrival_time": str,
            "departure_time": str,
            "stop_sequence": "Int64",  # nullable — handles NaN from encoding errors
        },
    )

    # --- Schema guard ---
    missing_cols = REQUIRED_ST_COLS - set(stop_times.columns)
    if missing_cols:
        raise ValueError(f"stop_times.txt is missing required columns: {missing_cols}")

    # --- 1. Orphaned trips: in trips.txt but absent from stop_times.txt ---
    trip_coverage = stop_times[["trip_id"]].drop_duplicates()
    merged = trips.merge(trip_coverage, on="trip_id", how="left", indicator=True)
    missing_trip_ids = merged.loc[merged["_merge"] == "left_only", "trip_id"].tolist()
    logging.info("Orphaned trip_ids (no stop_times rows): %d", len(missing_trip_ids))

    # --- 2. Invalid stop_id foreign key references ---
    valid_stop_ids = set(stops["stop_id"])
    invalid_stop_refs = stop_times[~stop_times["stop_id"].isin(valid_stop_ids)].copy()
    logging.info("Rows with stop_id not in stops.txt: %d", len(invalid_stop_refs))

    # --- 3. Sequence gaps: stop_sequence jumps > 1 within a single trip ---
    st_sorted = stop_times.sort_values(["trip_id", "stop_sequence"])
    diffs = st_sorted.groupby("trip_id", sort=False)["stop_sequence"].diff()
    gap_mask = diffs.gt(1)
    gap_rows = st_sorted[gap_mask].copy()
    logging.info("Rows preceded by a sequence gap (diff > 1): %d", len(gap_rows))

    # --- Compile diagnostic summary ---
    diagnostics = pd.DataFrame(
        {
            "issue_type": ["missing_trip_ids", "invalid_stop_refs", "sequence_gaps"],
            "count": [len(missing_trip_ids), len(invalid_stop_refs), len(gap_rows)],
            "sample_ids": [
                str(missing_trip_ids[:5]),
                invalid_stop_refs[["trip_id", "stop_id"]].head(5).to_dict("records"),
                gap_rows[["trip_id", "stop_sequence"]].head(5).to_dict("records"),
            ],
        }
    )

    # --- Optional: patch intra-trip sequence gaps only ---
    if patch:
        patched_rows: list[dict] = []
        for trip_id, group in st_sorted.groupby("trip_id", sort=False):
            seqs = group["stop_sequence"].dropna().astype(int).tolist()
            if len(seqs) < 2:
                continue
            full_range = set(range(min(seqs), max(seqs) + 1))
            missing_seqs = sorted(full_range - set(seqs))
            for seq in missing_seqs:
                patched_rows.append(
                    {
                        "trip_id": trip_id,
                        "stop_id": "PLACEHOLDER",
                        "stop_sequence": seq,
                        "arrival_time": "00:00:00",
                        "departure_time": "00:00:00",
                    }
                )

        if patched_rows:
            patch_df = pd.DataFrame(patched_rows)
            out_path = feed_path / "stop_times_patched.txt"
            patched_full = pd.concat([stop_times, patch_df], ignore_index=True)
            patched_full.sort_values(["trip_id", "stop_sequence"]).to_csv(
                out_path, index=False
            )
            logging.info(
                "Wrote %d placeholder rows to %s — requires agency review before use",
                len(patch_df),
                out_path,
            )

    diag_path = feed_path / "stop_times_diagnostics.csv"
    diagnostics.to_csv(diag_path, index=False)
    logging.info("Diagnostics written to %s", diag_path)
    return diagnostics


# --- Entry point ---
# report = audit_and_patch_stop_times("/data/gtfs/wmata", patch=False)
# print(report.to_string(index=False))

Step-by-Step Walkthrough

Dtype enforcement on load. stop_sequence is declared as "Int64" (capital I) — pandas’ nullable integer type. Standard int64 cannot represent NaN, so a single encoding-damaged cell causes read_csv to either raise or silently widen the entire column to float64, corrupting downstream comparisons. trip_id and stop_id are kept as str to prevent numeric-looking IDs such as "0012" from being cast to 12.

Anti-join for orphaned trips. The merge with how="left" and indicator=True produces a _merge column on every row of trips. Rows flagged "left_only" represent trip_id values that exist in trips.txt but appear in zero stop_times.txt rows — the clearest sign of a schedule truncation event.

Foreign key scan. Building valid_stop_ids as a Python set and applying isin() is substantially faster than a merge for this check because set membership is O(1). For feeds with hundreds of thousands of rows this matters; see memory-efficient processing for large feeds for chunked alternatives when the full frame will not fit in RAM.

Sequence gap detection via diff(). After sorting by (trip_id, stop_sequence), groupby().diff() computes the difference between consecutive stop_sequence values within each trip. Any value greater than 1 indicates a missing intermediate stop. The result is the row after the gap — the first evidence that a gap exists.

Conservative patching. The patch loop only fills gaps within trips that already have partial stop_times data. Completely missing trips are deliberately left untouched and surfaced in the diagnostic report. Placeholder rows use "PLACEHOLDER" as stop_id and "00:00:00" for times, making them immediately visible to any GTFS validation tool rather than silently masquerading as real schedule data.

Verification and Output

After running the audit, verify correctness with these assertions before promoting any patched feed:

import pandas as pd
from pathlib import Path

feed_path = Path("/data/gtfs/wmata")

report = pd.read_csv(feed_path / "stop_times_diagnostics.csv")

# All three issue categories must be present in the report
assert set(report["issue_type"]) == {
    "missing_trip_ids", "invalid_stop_refs", "sequence_gaps"
}, "Diagnostic report is incomplete"

# If a patched file exists, no new stop_ids should appear beyond PLACEHOLDER
if (feed_path / "stop_times_patched.txt").exists():
    original = pd.read_csv(feed_path / "stop_times.txt", dtype={"stop_id": str})
    patched = pd.read_csv(feed_path / "stop_times_patched.txt", dtype={"stop_id": str})
    new_ids = set(patched["stop_id"]) - set(original["stop_id"]) - {"PLACEHOLDER"}
    assert not new_ids, f"Unexpected new stop_ids in patched file: {new_ids}"

    # Row count should only have grown
    assert len(patched) >= len(original), "Patched file has fewer rows than original"

print("Verification passed — safe to submit for agency review")

The diagnostic CSV contains three rows with issue_type, count, and sample_ids columns. A healthy feed returns counts of zero for all three. When count is nonzero, sample_ids lists up to five representative examples for each issue class, giving you enough information to trace each anomaly back to a specific agency export or pipeline step.

Gotchas and Edge Cases

• Times above 24:00:00. The GTFS spec permits overnight trips to use times like 25:30:00 to indicate 1:30 AM the following service day. Treating these as invalid during parsing will incorrectly flag rows as corrupt; always parse time columns as plain strings and only interpret the hour component numerically if you need to convert to UTC. This also interacts with timezone normalization — convert only after confirming the time string is spec-valid.

• Frequency-defined trips legitimately have sparse stop_times.txt. When a trip is driven by frequencies.txt, its stop_times.txt rows act as a template offset from start_time, not as literal departures. Such trips will look thin compared to a fully enumerated timetable, but they are not missing data — flagging them as orphaned would corrupt a valid feed. Detect frequency-based service before auditing; see converting frequencies.txt to exact departure times for how the template expansion works.

• Reused stop_id values across agency files in a combined feed. When you merge feeds from multiple agencies into a single dataset, stop_id collisions are nearly guaranteed. Apply a {agency_id}_ prefix to all identifiers before running the foreign key scan, otherwise valid references will appear as violations.

• Duplicate (trip_id, stop_sequence) pairs. Some agency ETL systems emit duplicate rows for the same stop event — typically from a join that fans out due to a many-to-one calendar relationship. The sequence gap check will not catch these; add stop_times.duplicated(subset=["trip_id", "stop_sequence"]).sum() as a separate pre-flight assertion.

• stop_sequence that starts at 0 vs 1. The GTFS spec requires stop_sequence to be a non-negative integer and only mandates that values increase along the trip — not that they are contiguous or start at any particular number. An agency that starts sequences at 0 and another that starts at 1 will both be valid. Do not assume gaps starting from 0 indicate missing records; check the minimum sequence value per trip before interpreting diffs.

Frequently Asked Questions

Related

• Converting GTFS frequencies.txt to Exact Departure Times — expand frequency-template trips before auditing so sparse stop_times.txt rows are not mistaken for missing data
• Step-by-Step Guide to Parsing GTFS with Partridge — strict foreign-key enforcement on load, which surfaces orphaned trip_id and stop_id references automatically
• Optimizing Pandas Memory Usage for Transit Feeds — keep the anti-join and gap scan in memory on large national feeds
• Up: Mastering stops.txt and stop_times.txt Relationships — the spec rules and relational model underpinning this repair workflow
• Section: Python Parsing & Data Normalization — the full GTFS parsing and normalization pipeline · Home