Pipeline

NTD Annual Service ETL

Coverage: Coverage window unavailable for this page.

Built 2026-04-03 20:09 UTC · Commit 7c56b9a

Page Navigation

Data Provenance

flowchart LR
  06_ntd_service(["NTD Annual Service ETL"])
  f1_06_ntd_service[/"data/ntd-annual-service/2023_TS2.2_Service_Data.xlsx"/] --> 06_ntd_service
  06_ntd_service --> tp_ntd_annual_service[("ntd_annual_service")]
  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 06_ntd_service page;
  class tp_ntd_annual_service table;
  class f1_06_ntd_service file;

Findings

Findings: NTD Annual Service ETL

Loaded NTD TS2.2 annual service data into ntd_annual_service table from two source files: the 2023 edition (1991–2023) and the 2024 edition (2015–2024), with the newer file taking precedence for overlapping years.

93,188 rows written (2,794 unique agencies × up to 34 years each)
2,794 unique agencies with identifier data
Year range: 1991–2024

PRT verification (NTD ID 30022)

Year	VRH	VRM	UPT	VOMS
2019	2,382,972	31,955,492	64,007,925	942
2023	2,025,498	26,447,131	37,908,532	780
2024	2,070,196	26,908,180	37,876,514	766

Methods

Methods: NTD Annual Service ETL

Question

How do we make national transit service-level data (Vehicle Revenue Hours, Vehicle Revenue Miles) available for comparative analysis?

Approach

Read four sheets (VRH, VRM, UPT, VOMS) from the NTD TS2.2 "Service Data by System" Excel workbook.
For each sheet, identify agency identifier columns and year columns (1991–2023).
Unpivot each sheet from wide format (one column per year) to long format (one row per agency-year).
Join the four metric DataFrames on (ntd_id, year).
Attach agency identifiers (name, city, state, UZA) from the VRH sheet.
Write to a single ntd_annual_service table in prt.db.

Data

Name	Description	Source
`2023_TS2.2_Service_Data.xlsx`	NTD TS2.2 workbook with annual service metrics by system	FTA National Transit Database (`data/ntd-annual-service/`)

Output

Name	Description
`ntd_annual_service`	SQLite table in `prt.db`: annual VRH, VRM, UPT, VOMS per agency (1991–2023)

  Source Code
    
      """Pipeline 06: load NTD annual service tables (VRH, VRM) into prt.db."""

from prt_otp_analysis.ntd_service import main as ntd_service_main

def main() -> None:
    """Run NTD annual service ETL."""
    ntd_service_main()

if __name__ == "__main__":
    main()

Tables Produced

Table	Description
`ntd_annual_service`	Annual VRH, VRM, UPT, VOMS by NTD system (1991-2023).

Sources

Name	Type	Why It Matters	Owner	Freshness	Caveat
data/ntd-annual-service/2023_TS2.2_Service_Data.xlsx	file	NTD TS2.2 workbook with annual service data by system.	Local project data owner not specified.	Snapshot file; refresh by rerunning its pipeline step.	May lag upstream source updates.