1. Home
  2. GTFS Realtime Integration
  3. Matching Realtime To Static Schedules
  4. Joining Tripupdates To Stop Times

Joining TripUpdates to stop_times.txt

Left-join the scheduled stop_times.txt rows onto the realtime stop_time_update records on (trip_id, stop_sequence), falling back to (trip_id, stop_id) only for updates that omit the sequence, so every scheduled stop keeps its row and gains a live delay where one exists. Then forward-fill the delay within each trip — because a GTFS-Realtime delay propagates to all later stops until the next update overrides it — and add the delay to the scheduled time to produce a predicted departure for every row on the board.

Root Cause Analysis

A TripUpdate is sparse by design. A vehicle mid-route typically emits a StopTimeUpdate for only the next stop or two, not for all thirty stops on its trip. The realtime feed is telling you “at stop 12 the arrival is 95 seconds late” and trusting the consumer to propagate that delay to stops 13 through 30 using the static schedule as the backbone. The parent matching realtime to static schedules guide covers the trip-level reconciliation; this page is the narrow stop-level merge that turns a sparse update into a full board.

Two structural facts drive the join design. First, the scheduled feed is authoritative for which stops exist, so the join must be a left join from stop_times.txt, not from the realtime side — otherwise stops without a live update vanish from the board. Second, stop_sequence is the reliable key and stop_id is not: a trip on a loop or an out-and-back branch visits the same stop_id more than once, so joining on stop_id fans one update out onto multiple scheduled rows. The stops.txt and stop_times.txt relationship model treats (trip_id, stop_sequence) as the composite key precisely because of this, and the join here honours that.

The remaining subtlety is delay propagation. GTFS-Realtime specifies that a StopTimeUpdate delay applies to every following stop until the next StopTimeUpdate supersedes it. That is a forward-fill along stop_sequence, and skipping it leaves most of the board showing an on-time prediction for a trip that is running late.

Production-Ready Python Implementation

The script flattens the realtime stop_time_update records, splits them by whether they carry a stop_sequence, left-joins the schedule, forward-fills the delay per trip, and computes a predicted departure. It depends only on gtfs-realtime-bindings and pandas.

python 
import logging
from datetime import datetime, timedelta, timezone

import pandas as pd
from google.transit import gtfs_realtime_pb2

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

STOP_TIMES_DTYPES = {
    "trip_id": "string",
    "stop_id": "string",
    "stop_sequence": "Int64",
    "arrival_time": "string",
    "departure_time": "string",
}


def flatten_stop_time_updates(feed: gtfs_realtime_pb2.FeedMessage) -> pd.DataFrame:
    """One row per StopTimeUpdate, carrying its trip_id and delay in seconds."""
    rows: list[dict] = []
    for entity in feed.entity:
        if not entity.HasField("trip_update"):
            continue
        trip = entity.trip_update.trip
        for stu in entity.trip_update.stop_time_update:
            # arrival/departure are StopTimeEvent messages; prefer departure delay
            delay = None
            if stu.HasField("departure") and stu.departure.HasField("delay"):
                delay = stu.departure.delay
            elif stu.HasField("arrival") and stu.arrival.HasField("delay"):
                delay = stu.arrival.delay
            rows.append(
                {
                    "trip_id": trip.trip_id or None,
                    "stop_sequence": stu.stop_sequence if stu.HasField("stop_sequence") else pd.NA,
                    "stop_id": stu.stop_id or None,
                    "rt_delay_sec": delay,
                }
            )
    updates = pd.DataFrame(rows).astype(
        {"trip_id": "string", "stop_sequence": "Int64", "stop_id": "string", "rt_delay_sec": "Int64"}
    )
    logging.info("Flattened %d stop_time_update rows", len(updates))
    return updates


def _gtfs_time_to_timedelta(value: str) -> timedelta:
    """Parse an HH:MM:SS GTFS time (hours may exceed 24) into a timedelta."""
    hours, minutes, seconds = (int(part) for part in value.split(":"))
    return timedelta(hours=hours, minutes=minutes, seconds=seconds)


def build_departure_board(
    feed: gtfs_realtime_pb2.FeedMessage,
    stop_times_path: str,
    service_date: str,          # 'YYYYMMDD'
    agency_tz: timezone = timezone.utc,
) -> pd.DataFrame:
    """
    Attach live delays from a TripUpdate feed to scheduled stop_times.txt rows.

    Left-joins on (trip_id, stop_sequence), falls back to (trip_id, stop_id),
    forward-fills the delay per trip, and computes predicted_departure.
    """
    stop_times = pd.read_csv(stop_times_path, dtype=STOP_TIMES_DTYPES)
    updates = flatten_stop_time_updates(feed)

    # Only score trips that actually appear in the realtime feed
    live_trip_ids = set(updates["trip_id"].dropna())
    board = stop_times[stop_times["trip_id"].isin(live_trip_ids)].copy()
    board = board.sort_values(["trip_id", "stop_sequence"]).reset_index(drop=True)

    # --- Primary join: (trip_id, stop_sequence) ---
    seq_updates = updates[updates["stop_sequence"].notna()][["trip_id", "stop_sequence", "rt_delay_sec"]]
    board = board.merge(seq_updates, on=["trip_id", "stop_sequence"], how="left")

    # --- Fallback join: (trip_id, stop_id) for updates lacking a sequence ---
    id_updates = (
        updates[updates["stop_sequence"].isna() & updates["stop_id"].notna()]
        .drop_duplicates(subset=["trip_id", "stop_id"])[["trip_id", "stop_id", "rt_delay_sec"]]
        .rename(columns={"rt_delay_sec": "rt_delay_fallback"})
    )
    board = board.merge(id_updates, on=["trip_id", "stop_id"], how="left")
    board["rt_delay_sec"] = board["rt_delay_sec"].fillna(board["rt_delay_fallback"])
    board = board.drop(columns="rt_delay_fallback")

    # --- Propagate delay forward within each trip, default leading stops to 0 ---
    board["rt_delay_sec"] = (
        board.groupby("trip_id", sort=False)["rt_delay_sec"].ffill().fillna(0).astype("Int64")
    )

    # --- Compute predicted departure from scheduled time + delay ---
    day = datetime.strptime(service_date, "%Y%m%d").replace(tzinfo=agency_tz)
    board["scheduled_departure"] = board["departure_time"].map(
        lambda t: day + _gtfs_time_to_timedelta(t) if pd.notna(t) else pd.NaT
    )
    board["predicted_departure"] = board.apply(
        lambda r: r["scheduled_departure"] + timedelta(seconds=int(r["rt_delay_sec"]))
        if pd.notna(r["scheduled_departure"])
        else pd.NaT,
        axis=1,
    )

    logging.info(
        "Board built: %d rows across %d live trips (%d rows carry a direct RT delay)",
        len(board),
        board["trip_id"].nunique(),
        seq_updates.shape[0],
    )
    return board


if __name__ == "__main__":
    message = gtfs_realtime_pb2.FeedMessage()
    with open("feeds/trip_updates.pb", "rb") as handle:
        message.ParseFromString(handle.read())

    departures = build_departure_board(
        message, "gtfs/stop_times.txt", service_date="20260713"
    )
    print(
        departures[
            ["trip_id", "stop_sequence", "stop_id", "departure_time",
             "rt_delay_sec", "predicted_departure"]
        ].head(15).to_string(index=False)
    )

Step-by-Step Walkthrough

Departure delay preferred over arrival. In flatten_stop_time_updates, each StopTimeUpdate carries arrival and departure StopTimeEvent sub-messages, and either can hold a delay. A departure board cares about when a vehicle leaves, so the code reads departure.delay first and only falls back to arrival.delay. Both are guarded with HasField, because a 0 delay is a legitimate “on time” value distinct from “no delay reported”.

The left join runs from the schedule. board starts as the scheduled stop_times.txt rows for trips present in the realtime feed, and both merges use how="left". Every scheduled stop therefore survives even when no StopTimeUpdate targets it — the requirement that makes this a left join rather than the inner join a naive implementation would reach for.

Sequence first, stop_id as fallback. The primary merge keys on (trip_id, stop_sequence). Updates that omit stop_sequence are merged separately on (trip_id, stop_id) into a rt_delay_fallback column, then coalesced with fillna. The fallback frame is de-duplicated on (trip_id, stop_id) first, so an ambiguous loop stop cannot fan one update onto several rows. This is the concrete application of the trip-level key hierarchy from matching realtime to static schedules.

Forward-fill is the propagation rule. groupby("trip_id")["rt_delay_sec"].ffill() pushes each observed delay down onto every later stop in stop_sequence order until a new update overrides it, and .fillna(0) sets the stops before the first update to on-time. That single line implements the GTFS-Realtime propagation semantics.

Predicted time respects overnight service. _gtfs_time_to_timedelta parses departure_time as a timedelta rather than a clock time, so a 25:10:00 overnight departure adds 25 hours to the service date correctly — the same overnight handling covered in timezone handling and schedule normalization and detailed under processing TripUpdates and delays.

Verification and Output

Assert the board’s invariants before serving it:

python 
import pandas as pd

departures = build_departure_board(message, "gtfs/stop_times.txt", service_date="20260713")

# Every scheduled row must survive the left join (no rows dropped)
assert departures["stop_sequence"].notna().all(), "Scheduled row lost its stop_sequence"

# Delay must be fully populated after ffill + fillna
assert departures["rt_delay_sec"].notna().all(), "Un-filled delay remains after propagation"

# Within a trip, predicted times must be non-decreasing by stop_sequence
def monotonic_ok(group: pd.DataFrame) -> bool:
    ordered = group.sort_values("stop_sequence")["predicted_departure"].dropna()
    return ordered.is_monotonic_increasing

violations = departures.groupby("trip_id").filter(lambda g: not monotonic_ok(g))
assert violations.empty, f"Non-monotonic predicted times on trips: {violations['trip_id'].unique()}"

print(f"Board verified: {len(departures)} rows, {departures['trip_id'].nunique()} trips")

For a mid-route trip with one live update at stop 12, the output shows stops 1–11 at rt_delay_sec = 0, stops 12 onward carrying the propagated delay, and predicted_departure equal to the scheduled time plus that delay — a complete board built from a single sparse update.

Gotchas and Edge Cases

  • SKIPPED stops break naive monotonicity. A StopTimeUpdate with schedule_relationship = SKIPPED means the vehicle will not serve that stop; its predicted time should be dropped, not computed, or the monotonic check above will misfire. Handle these before the assert, as detailed in handling skipped stops in TripUpdates.

  • Explicit time overrides delay. A StopTimeEvent may carry an absolute time (POSIX) instead of, or alongside, a delay. When time is present it is authoritative — use it directly rather than adding a delay to the schedule, because the producer has already computed the prediction.

  • Delay can be negative. A vehicle running ahead of schedule reports a negative delay. The Int64 dtype and the timedelta arithmetic both handle this correctly, but a display layer that clamps at zero will wrongly show an early trip as on-time.

  • Trips not in stop_times.txt. An ADDED trip has no scheduled rows, so it never appears in this board — by design, since the board is anchored to the schedule. Build such trips directly from their StopTimeUpdate list instead, per the realtime-to-static matching guide.

Frequently Asked Questions

Why left-join stop_times.txt instead of inner-joining the TripUpdates?

A departure board must show every scheduled stop, including ones with no live update yet. A left join from stop_times.txt keeps all scheduled rows and attaches a delay where a StopTimeUpdate exists, leaving the rest to inherit a propagated or zero delay.

What if a StopTimeUpdate has no stop_sequence?

The spec allows a producer to key a StopTimeUpdate by stop_id alone. Join those rows on (trip_id, stop_id) as a fallback, but be aware this is ambiguous on trips that visit a stop twice, so prefer stop_sequence whenever the producer supplies it.

How do I fill delay for stops with no StopTimeUpdate?

GTFS-Realtime says a delay applies to all subsequent stops until the next StopTimeUpdate overrides it. Sort by stop_sequence and forward-fill the delay within each trip, defaulting the leading stops to zero if no earlier update exists.