Transit ridership forecasting

A metropolitan transit agency needed one trusted view of how service choices would land on ridership and operating dollars. Python extracts pull APC ridership from FY2018 to present and historical farebox ridership from 2014 through pre-FY2018 into the database. SQL cleans each stream, then merges them into a unified ridership table — with a small retroactive uplift on the farebox years so they sit on an APC-comparable basis (counters pick up trips farebox missed). Historical and future GTFS supply the service that produced actuals and the service that is planned. XGBoost runs at a daily grain; outputs load back to the same database; Power BI is what planners, schedulers, budget, and executives actually open. This page is a scaled monthly demo: 32 routes, filters, and levers — not a copy of the production network. The agency name is withheld.

Problem

Farebox history and APC counts lived in different systems; nobody could change a route’s service plan and see demand and operating cost in the same report.

Who

Service planning, scheduling, budget, and the executive team.

Solution

Python loads into SQL, a unified ridership table, XGBoost forecast functions, then forecast rows written back to the database and a Power BI report on that store.

Outcome

Those groups could plan to potential demand and calculate the projected dollar amount of running the planned network five fiscal years out.

Background

The agency had farebox ridership back to 2014 and APC counts from FY2018 forward. Those series did not share a table, a grain, or a definition of a boarding. Planning still answered “what if we change span or hours on this route?” with a custom pull. Budget and executives needed a dollar figure for running the planned network, not only a boarding chart. Fiscal years run October 1–September 30.

Challenges

• Farebox (2014–pre-FY2018) and APC (FY2018–present) on different instruments
• APC under-counts vs. a farebox series that needed an APC-comparable adjustment
• Service supply and ridership on different calendars
• Short-term momentum vs. long-range scenarios that cannot share the same features
• Stakeholders who needed cost and a live report, not a notebook

Stakeholders

• Service planning
• Scheduling
• Budget
• Executive team

Success criteria

• One unified ridership table that planning and budget could both quote
• Forecast written back to the database and consumed in Power BI
• Levers for span, hours, miles, and demographic scenarios
• A five-year operating-cost view of the planned service (Oct–Sep fiscal years)

Data sources

• APC ridership, FY2018 to present (Python extract → database)
• Historical farebox ridership, 2014 through pre-FY2018 (Python extract → database)
• Historical GTFS (baseline service)
• Future / planned GTFS (predicted service)
• Route category and calendar exceptions
• Demographic layers, gas prices, headway, and two-year OD patterns (tested)

Stack

Design decisions

Why this architecture? Extracts land in SQL so cleaning and the farebox-to-APC merge are governed in the warehouse. XGBoost is a pair of Python functions on that gold table. The last mile is writing predictions back and pointing Power BI at the same database.

Why two forecast modes? The next twelve months can lean on recent ridership. Years beyond that have to be driven by service (and explicit scenario assumptions), because lags cannot be known that far out and new or consolidated routes have no history.

What did we test and leave out? Gas prices, headway as a factor distinct from service hours, and origin–destination patterns from the prior two years were all tested against historical records. None cleared the bar for a stable, significant lift once service supply was in the model, so they stay in the diagnostics — not in the production feature set.

Business metrics

Ridership

Unified boardings: APC from FY2018; farebox 2014–pre-FY2018 with a small APC-comparable uplift.

Revenue hours / miles

GTFS-derived service supply for the route-day (rolled to month here).

Span of service

Hours from first to last trip — a scheduling lever, not the same as revenue hours.

Operating $

Revenue hours × a fully allocated hourly cost, so budget can price the plan.

Governance

• Bronze extracts (APC and farebox) stay raw; SQL clean tables are the only inputs to the merge
• Unified series applies a small retroactive % to farebox years so they approximate trips APC would have counted
• Horizon labels: actual, short-term forecast (one year from model run), scenario thereafter
• Fiscal year starts October 1; this site uses 32 named routes and monthly totals — not the production list or daily grain

What the model was telling us

Plain-language readout for analysts. Production is daily XGBoost; snippets below are the shape of the fit, not the agency notebook.

Near-term is momentum. For the rolling year, XGBoost leaned on last week and the last 28 days of ridership. That is why the next 12 months are labeled a forecast: the best predictor of Tuesday is last Tuesday, given the route still runs.

Long-range is service. Lags cannot be known five years out, and new or consolidated routes have no history. The structural model therefore used span, revenue hours, trip count, and miles — the same levers planners actually change in GTFS.

What it was not saying. Gas prices, headway as a separate knob from hours, and two-year OD patterns were tested and did not add a stable, significant signal. They are in the diagnostics so an analyst can see they were tried, not ignored.

How we scored it. Target was log1p(boardings) so small routes were not drowned by rail. Validation was expanding-window time-series CV (train on the past, test the next slice) — not a random shuffle, which would leak the future.

Ingestion

• Python: APC ridership FY2018 to present → database
• Python: historical farebox ridership 2014 through pre-FY2018 → database
• GTFS zip feeds mapped across signups (historical and future)

Transformations

• SQL: clean APC and farebox into consistent route-day tables
• SQL: merge into a unified ridership report; farebox years receive a small % uplift for APC under-count
• Python: assemble GTFS service features and run XGBoost forecast functions
• Python: load forecast output back to the database

Automation

• Repeatable extract → SQL clean/merge → train/forecast → write-back
• Power BI connected to the forecast tables (not to a local workbook)
• Horizon and provenance columns so the report can split forecast from scenario

load_sql("bronze.apc_ridership", extract_apc(fy_from=2018))
load_sql("bronze.farebox_ridership", extract_farebox(year_from=2014, before_fy=2018))
forecast = run_xgboost_forecast(gold_ridership)
load_sql("gold.ridership_forecast", forecast)

insert into gold.ridership_unified
select route_id, service_date, boardings
from silver.apc_clean
union all
select route_id, service_date,
       boardings * (1 + @apc_undercount_pct)
from silver.farebox_clean;

y = np.log1p(df["boardings"])
lag_model.fit(X[lags + service], y)      # next 12 months
struct_model.fit(X[service], y)          # FY+2 and beyond
# route-day prediction; Power BI rolls to month / FY

service = ["span_hours", "revenue_hours", "trip_count", "revenue_miles"]
# tested, not shipped: gas_price, headway_peak, od_share_2yr
pred = np.expm1(struct_model.predict(future[service]))

• Power BI report on the database — planners, schedulers, budget, executives
• Route-level forecast with type filters (local, limited, express, BRT, light rail)
• Levers for span, service hours, service miles, and demographic change
• Five-year operating-cost table for the planned network (FY Oct–Sep)

Production is daily XGBoost; this graph is a monthly rollup on a 32-route synthetic network. Near-term (12 months) is a forecast. Beyond that is a scenario. We tested gas prices, headway as a distinct factor, and two-year OD patterns; they were not statistically significant on historical records, so they are documented, not shipped. Demographic sliders here are scenario factors — stop-area demographics were a weak driver compared with service supply.

Operational outcomes

• Planning could test demand against a proposed GTFS, not a one-off spreadsheet
• Scheduling could see span and hours as first-class inputs
• Budget could translate the same plan into a five-year operating-dollar figure
• Executives opened the same Power BI model, not a side deck of numbers

Adoption

• Service planning
• Scheduling
• Budget
• Executive team

Lessons learned

• Write the forecast back to the database; the report is only as trusted as that table.
• Call the first year a forecast and the rest a scenario — mixing those words burns trust.
• Tested-and-rejected features (gas, standalone headway, recent OD) belong in the write-up so leadership knows they were not ignored.