MTA Transportation Trends Dashboard

About this project Purpose

This dashboard analyzes transportation trends within the MTA from 2020 to 2024, focusing on the impact of the COVID-19 pandemic and subsequent recovery. It highlights ridership and traffic patterns across key categories like subways, buses, commuter rails, and Access-A-Ride, comparing current levels to pre-pandemic benchmarks from 2019.

The purpose is to provide insights into changes in commuting behavior, category-specific resilience, and the evolving demand for accessible transit. Through data visualization, this dashboard makes complex trends accessible, supporting strategic decisions for public transit planning in a post-pandemic world.

Dashboard Link: https://lnkd.in/gR8wgZC5 Process

First, I started by studying the raw data, conducting in-depth research to understand key terms, industry jargon, and data context. This helped me build familiarity with the data and form a plan for the dashboard layout and structure.

Original Data Second, I transformed and organized the data. I created distinct categories for transportation volume, separated ridership from traffic, and included data collection methodology details to clarify data sources and metrics. After cleaning the data, I used Excel Power Query and PowerPivot in Excel to simplify the process, ensuring efficient data handling and manipulation.

Transform Data ETL (Power Query)

Third, I established a calendar with seasonal, quarterly, yearly, and weekday categorizations to support trend analysis. I created relationships between these date elements and the main dataset, enabling a smooth flow of data through the analysis. I then set up an analysis tab with pivot tables to serve as the foundation for the dashboard visuals. Calendar (Power Pivot) Pivot Tables & Tables

Finally, I applied techniques I learned from Enrique Ruiz’s advanced Excel dashboard design course, combined with my experience, to shape the dashboard layout. This approach allowed me to highlight essential insights and deliver a user-friendly, visually appealing dashboard that meets the project’s objectives. Overview Dashboard Trend Analysis Dashboard Key Insights This summary provides insights into MTA transportation trends from 2020 to 2024, examining ridership and traffic volumes, the impact of the pandemic, and recovery patterns. Key areas include comparisons across weekdays, weekends, and seasons, highlighting changes in transportation demand and a gradual return to pre-pandemic norms. Transportation Insights Summary (2020 - 2024) Total transportation volume from 2020 to October 2024 reached 7.93 billion, with 6.47 billion in ridership and 1.46 billion in traffic. Data collection primarily relied on MetroCard & OMNY (75%), supplemented by Toll Collection (19%) and other methods (6%), underscoring the role of payment data in tracking trends. Category Analysis Subways dominated transportation, capturing 35.1% of usage, despite a 46% decline compared to 2019. Notably, Access-A-Ride saw a 20% increase in 2024 vs. 2019, driven by demand for accessible transit. Since the pandemic began, Access-A-Ride usage has surged 476%, standing 26% above pre-pandemic levels. 2024 Transport Volume and Changes from 2019 In 2024, subways recorded 987.8 million trips, down 30% from 2019. Buses fell 41% to 339.2 million. Bridges and Tunnels increased 1%, while LIRR and Metro North declined 19% and 24%. Staten Island Railway dropped 54%, whereas Access-A-Ride grew 20%, reflecting increased demand for accessible options. Pandemic Impact and Recovery Progress Subways dominated transportation, capturing 35.1% of usage, despite a 46% decline compared to 2019. Notably, Access-A-Ride saw a 20% increase in 2024 vs. 2019, driven by demand for accessible transit. Since the pandemic began, Access-A-Ride usage has surged 476%, standing 26% above pre-pandemic levels. Ridership and Traffic Trends Ridership and traffic have steadily recovered since 2020’s lows. Growth was consistent through 2021-2022, and by 2024, both metrics neared pre-pandemic levels, led slightly by traffic. Weekday vs. Weekend Patterns Weekday volumes rebounded more strongly than weekends from 2023-2024, signaling a return to work-related travel, while weekend travel recovery remained slower. Seasonal Comparisons Transportation volumes are still below 2019 levels across all seasons. Spring and Summer volumes fell to 2.1 billion (from 3.7B and 3.6B), Fall dropped to 2.2 billion (from 3.5B), and Winter saw the steepest decline at 1.6 billion (from 2.7B), reflecting reduced seasonal demand post-pandemic. Conclusion This dashboard provides a detailed view of MTA ridership and traffic from 2020 to 2024, highlighting pandemic impacts, recovery trends, and seasonal patterns to offer insights into evolving transportation demand. These insights reveal significant shifts in transportation needs post-pandemic and serve as a data-driven foundation for planning future transit services that align with changing commuter behaviors and demands. #MavenAnalyticsChallenge #MTA #RidershipRecovery #FirstTimeEntrant