Pipeline

NTD Ridership ETL

Coverage: 2002-01 to 2025-12 (from ntd_ridership).

Built 2026-03-03 02:23 UTC · Commit defd5c8

Page Navigation

Data Provenance

flowchart LR
  05_ntd_ridership(["NTD Ridership ETL"])
  f1_05_ntd_ridership[/"data/ntd-monthly-ridership/December 2025 Complete Monthly Ridership (with adjustments and estimates)_260202.xlsx"/] --> 05_ntd_ridership
  05_ntd_ridership --> tp_ntd_agency[("ntd_agency")]
  05_ntd_ridership --> tp_ntd_ridership[("ntd_ridership")]
  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 05_ntd_ridership page;
  class tp_ntd_agency,tp_ntd_ridership table;
  class f1_05_ntd_ridership file;

Findings

Findings: NTD Ridership ETL

Summary

National agency and monthly ridership tables are loaded into prt.db for peer and trend benchmarking.

Notes

Uses the local NTD workbook checked into data/ntd-monthly-ridership/.
Emits verification summaries for PRT and top agencies.

Methods

Methods: NTD Ridership ETL

Question

How do we load national monthly ridership benchmarks into the project database?

Approach

Read agency dimension rows from NTD workbook Master sheet.
Unpivot monthly UPT values from wide to long format.
Normalize month keys to YYYY-MM.
Rebuild ntd_agency and ntd_ridership tables in prt.db.

Data

NTD monthly ridership workbook in data/ntd-monthly-ridership/

Output

ntd_agency table in data/prt.db
ntd_ridership table in data/prt.db

Tables Produced

Table	Description
`ntd_agency`	Agency dimension table keyed by NTD ID, mode, and TOS.
`ntd_ridership`	Monthly UPT facts by NTD ID, mode, and TOS.

Sources

Name	Type	Why It Matters	Owner	Freshness	Caveat
data/ntd-monthly-ridership/December 2025 Complete Monthly Ridership (with adjustments and estimates)_260202.xlsx	file	NTD monthly ridership workbook containing agency metadata and UPT series.	Local project data owner not specified.	Snapshot file; refresh by rerunning its pipeline step.	May lag upstream source updates.