I’m using the MTA Subway Hourly Ridership dataset (2020–2024) from data.ny.gov, chosen for its relevance to my work at NYCT and because it needs to be cleaned up before we can draw any conclusions. Ridership will serve as the target variable. Since this dataset is very large at over 500,000 rows, I have queried it so it is much more simple to work and with and publish by limiting it to the month of July 2024 consisting of riders with at least 1 transfer.
To tackle the problem, I will parse the timestamp into a proper datetime, drop redundant columns, split any categorical fields into separate variables, and subset to a clean, well-named set of columns.
The data challenges I anticipate include a string-formatted timestamp that needs to be parsed and separated to increase the ways of sorting and filtering through the data, redundant or overlapping columns, a compound categorical field that conflates two variables when they don’t relate, a large row count that may need filtering, and multiple transit modes that will require a scoping decision.
Code deliverables
Loading libraries and raw data
First, the necessary libraries must be loaded in order to load the dataset and access it.
library(tidyverse)
Warning: package 'tidyverse' was built under R version 4.6.1
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr 1.2.1 ✔ readr 2.2.0
✔ forcats 1.0.1 ✔ stringr 1.6.0
✔ ggplot2 4.0.3 ✔ tibble 3.3.1
✔ lubridate 1.9.5 ✔ tidyr 1.3.2
✔ purrr 1.2.2
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag() masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
The following changes are some of the many that need to be done:
Turn “transit_timestamp” to a real date-time object instead of a string.
A new column, fare_type, is created from fare_class_category. The “OMNY -” or “Metrocard -” prefix is taken off, so the fare type stands alone. Then the remaining is modified to be shorter: “Full Fare” and “Fair Fare” will remove the ‘fare’, but I will clarify that ‘Fair’ means reduced. All other fields in the column stays the same.
Columns are renamed to more expressive names: transit_timestamp to date_time, transit_mode to transit_type, and payment_method to fare_payment_method.
Also make sure ridership is the last column as it is the target.
Warning: There was 1 warning in `mutate()`.
ℹ In argument: `fare_type = `%>%`(...)`.
Caused by warning:
! `case_match()` was deprecated in dplyr 1.2.0.
ℹ Please use `recode_values()` instead.
Conclusion
The transformed data frame turns the raw MTA Subway Hourly Ridership file into a dataset primed to be analyzed. The combined fare-class string is split into separate payment method and fare type columns, and the timestamp is a proper datetime rather than a string. The target variable, riders, sits at the end of the frame, as it can be possibly predicted using all the other columns.
Analysis on this data can definitely be done on a much larger scale as I only took data for July 2024. Pulling in additional months from the same source would allow seasonal or year-over-year ridership comparisons, and adding back latitude and longitude would allow us to analyze the impact of location.
Also, the metrocard is being used less and less in favor of OMNY, which will make the category more populated with the latter. This will eventually make the column a bit redundant in the future and will be removed if necessary as its impact will be decreased.