Pipeline

PRT Stop-Signal Classification ETL

Coverage: Coverage window unavailable for this page.

Built 2026-06-15 11:52 UTC · Commit e5cf673

Page Navigation

Data Provenance

 
flowchart LR
  15_stop_signals(["PRT Stop-Signal Classification ETL"])
  f1_15_stop_signals[/"data/prt-stop-signals/bus_stops_with_signals_2602.xlsx"/] --> 15_stop_signals
  t_stops[("stops")] --> 15_stop_signals
  01_data_ingestion[["Data Ingestion"]] --> t_stops
  u1_01_data_ingestion[/"data/routes_by_month.csv"/] --> 01_data_ingestion
  u2_01_data_ingestion[/"data/PRT_Current_Routes_Full_System_de0e48fcbed24ebc8b0d933e47b56682.csv"/] --> 01_data_ingestion
  u3_01_data_ingestion[/"data/Transit_stops_(current)_by_route_e040ee029227468ebf9d217402a82fa9.csv"/] --> 01_data_ingestion
  u4_01_data_ingestion[/"data/PRT_Stop_Reference_Lookup_Table.csv"/] --> 01_data_ingestion
  u5_01_data_ingestion[/"data/average-ridership/12bb84ed-397e-435c-8d1b-8ce543108698.csv"/] --> 01_data_ingestion
  15_stop_signals --> tp_stop_signals[("stop_signals")]
  classDef page fill:#dbeafe,stroke:#1d4ed8,color:#1e3a8a,stroke-width:2px;
  classDef table fill:#ecfeff,stroke:#0e7490,color:#164e63;
  classDef dep fill:#fff7ed,stroke:#c2410c,color:#7c2d12,stroke-dasharray: 4 2;
  classDef file fill:#eef2ff,stroke:#6366f1,color:#3730a3;
  classDef api fill:#f0fdf4,stroke:#16a34a,color:#14532d;
  classDef pipeline fill:#f5f3ff,stroke:#7c3aed,color:#4c1d95;
  class 15_stop_signals page;
  class t_stops,tp_stop_signals table;
  class f1_15_stop_signals,u1_01_data_ingestion,u2_01_data_ingestion,u3_01_data_ingestion,u4_01_data_ingestion,u5_01_data_ingestion file;
  class 01_data_ingestion pipeline;

Methods

Methods: Stop Signals ETL

Question

Which PRT bus stops sit at a signalized intersection, and are they near-side or far-side of the signal?

Approach

  1. Read the PRT-provided spreadsheet (bus_stops_with_signals_2602.xlsx), one row per bus stop with a mode tag.
  2. Map each mode to a canonical signal_class: none, nearside, farside, or busway (dedicated busway/BRT right-of-way, not a street signal).
  3. Derive has_signal (true only for near-side / far-side stops).
  4. Resolve each PRT stop_code to its GTFS stop_id via the stops table so the classification can join to route_stops, otp_monthly, and census geography.
  5. Drop and rebuild the stop_signals table in prt.db.

Data

  • data/prt-stop-signals/bus_stops_with_signals_2602.xlsx (PRT, Samuel Buckley, 2026)
  • stops table in prt.db (stop_code → GTFS stop_id crosswalk)

Output

  • stop_signals table in data/prt.db

Source Code

 
"""Pipeline 15: load PRT's authoritative bus-stop signal classification into prt.db."""

from prt_otp_analysis.stop_signals import main as stop_signals_main


def main() -> None:
    """Run the stop-signal ingestion ETL."""
    stop_signals_main()


if __name__ == "__main__":
    main()

Tables Produced
TableDescription
stop_signals Per-stop authoritative signal classification (signal_class, has_signal) keyed by stop_code with resolved GTFS stop_id.

Sources
NameTypeWhy It MattersOwnerFreshnessCaveat
data/prt-stop-signals/bus_stops_with_signals_2602.xlsx file PRT-provided spreadsheet (Samuel Buckley, 2026) tagging each of 6,306 bus stops as no-signal, near-side, far-side, or busway/BRT. Local project data owner not specified. Snapshot file; refresh by rerunning its pipeline step. May lag upstream source updates.
stops table Primary analytical table used in this page's computations. Produced by Data Ingestion. Updated when the producing pipeline step is rerun. Coverage depends on upstream source availability and ETL assumptions.
Upstream sources (5)
  • file data/routes_by_month.csv — Monthly route OTP source table in wide format.
  • file data/PRT_Current_Routes_Full_System_de0e48fcbed24ebc8b0d933e47b56682.csv — Current route metadata and mode classifications.
  • file data/Transit_stops_(current)_by_route_e040ee029227468ebf9d217402a82fa9.csv — Current stop-to-route coverage and trip counts.
  • file data/PRT_Stop_Reference_Lookup_Table.csv — Historical stop reference file with geography attributes.
  • file data/average-ridership/12bb84ed-397e-435c-8d1b-8ce543108698.csv — Average ridership by route and month.