Stop-Level Feature Assembly (Service + Contextual Data)

[shweta487]

Which project does this issue pertain to?

• Affordable Housing and Sustainable Communities (AHSC) Ridership Calculator

Scope includes:

• Stop ID mapping and spatial alignment:
  • Observed ridership ↔ GTFS stops
• GTFS-derived service variables:
  • Existing trips/routes
  • Any additional service metrics (if retained)
• Contextual data aggregation:
  • ACS demographic & socioeconomic variables
  • LEHD job density variables

Deliverable:
Unified stop-level feature dataset ready for modeling.

[tiffanychu90]

I'll mention a couple of GTFS derived columns that are now available here in daily stops and monthly stops:

from stop_times

• arrivals by time-of-day (arrivals_am_peak, etc for time-of-day periods: owl, early_am, am_peak, midday, pm_peak, evening)
• n_hours_in_service: n distinct hours derived from arrival_sec that saw a scheduled arrival

from stop_times + trips

• route_type_array: list of the distinct route_types (ex: [0, 3] would refer to rail and bus)
• transit_mode_array: translates the route_types into transit modes (as 0/1/2 are rail, 3 is bus, 4 is ferry, and there's a couple other ones)
• route_id_array: list of route_ids present in fct_scheduled_trips
• n_route_types: count of distinct route_types. if it's 2, you might be interested to see if it's a combo of route_type = 3 (bus) and route_type = 0/1/2 (rail)
• route_type_* columns still there, which will help untangle the number of arrivals for each type