Parsing historical MBTA data

Link to this section Transit and Data

Transit systems and public data are a great match. In my daily life, I interact with so many devices and applications which pull from transit data, from the LED matrix in my living room, to the app on my phone, and to the countdown clocks within the station itself. It's also incredibly accessible, too, as the myriad of DIY projects pulling in transit data demonstrates.

There are, in general, two types of data about a transit system: what the system is in theory (schedules, routes, and stations determined months in advance), and what the system is in practice (vehicle locations, arrival predictions, and dropped/added trips updated in realtime).

Link to this section Transit Systems in Theory

Describing what a transit system does in theory is the easy part. The General Transit Feed Specification defines a common format made up (in true 2006 fashion) of a set of TXT files contained within a ZIP file, each describing a different aspect of the system. For instance, stops.txt describes the vehicle stops and stations in the system, stop_times.txt describes when each stop is serviced by a vehicle trip, and trips.txt can specify the train number, whether bikes are aloud, and more.

The benefit of this approach is clear: sharing a common standard means that code written for one city can work seamlessly with others. Smaller transit operators can create these files by hand, and larger ones can build up the necessary automation to handle hundreds of routes and thousands of stops. Since these ZIP files usually only change when new schedules are determined, distributing them is straightforward and storing historical ones is easy to do.

Link to this section Transit Systems in Practice

Realtime data is where things get messy. The requirements are more demanding. Data usually needs to be generated and distributed without a human in the loop, and clients need to pull updates every minute or faster.

GTFS does offer a solution to this in the form of GTFS-Realtime. However, being a Google initiative, they chose to build this using Protobuf as an interchange format, which requires specific language bindings to work with. My home system, the MBTA, chose to offer a JSON version of their feeds as well.

Even still, I tend to use their excellent, well-documented service API which makes it easier to ingest only the data relevant to the lines and stations I need. Interoperability with other systems is usually not a priority for my projects.

GTFS-Realtime, however, does not specify how historical data should be represented and stored, leaving transit systems to invent bespoke formats for this data -- that is, if they choose to make it available at all.

Link to this section Historical Data at the MBTA

Link to this section LAMP

The MBTA has a service called LAMP (Lightweight Application for Measuring Performance), which does three things:

That second point is what we'll focus on for parsing historical realtime data.

Link to this section Open Data Portal

The MBTA Open Data Portal contains lots of additional reports generated by the MBTA covering ridership, predictions accuracy, and more across the various transit modes. One such dataset is the Bus Arrival Departure Times 2025 dataset, which nicely complements the subway data published by the LAMP team.

Link to this section Let's get parsing

Link to this section Subway data

Link to this section Data format

The subway data we're interested in is distributed in the Parquet format, using URLs like this for each day of service:

https://performancedata.mbta.com/lamp/subway-on-time-performance-v1/YYYY-MM-DD-subway-on-time-performance-v1.parquet

The Parquet file format is an Apache project specification for storing tabular data efficiently. They pack the column data efficiently but don't require the receiver to know the schema definition like Protobuf does, which means we can easily throw these files into Pandas, the popular Python data analysis library.

import pandas as pd

path = "https://performancedata.mbta.com/lamp/subway-on-time-performance-v1/2025-10-31-subway-on-time-performance-v1.parquet"
df = pd.read_parquet(path)

with open("data.json", "w") as out:
    out.write(df.to_json(orient="records"))

We can see that the data has 27 columns. While these aren't documented anywhere, here's how I assume the data is structured:

Link to this section Simulating trips

Suppose I had been on the southbound platform at Sullivan Station at 5:15 PM. When would I have made it to North Station?

Let's start by finding all the trips which arrived at North Station coming southbound.

trips_to_target = df[(df["parent_station"] == "place-north") & (df["direction"] == "South")]

Now, let's find the earliest one of those trips which also stopped at Sullivan.

trip_ids = trips_to_target["trip_id"].unique()

trips_from_start = df[
    (df["parent_station"] == "place-sull")
    & (df["direction"] == "South")
    & (df["trip_id"].isin(trip_ids))
]

Right now, most of these are the same trip IDs, but now the timestamps match up with the train's stop at Sullivan. If we were dealing with different branches of the Green Line, for instance, this step would also filter out trips which don't run between our station pair.

Finally, let's find the first trip from the start which departed after we got to the station.

Times in this data are represented as Unix timestamps (i.e., seconds since 1970), seemingly in the local timezone (U.S. Eastern time). So, for instance, the first trip in our list arrived at the station at 1761902737 seconds, or at 5:25:37 AM on 2025-10-31.

import datetime

timestamp = datetime.datetime.fromisoformat("2025-10-31 17:15:00").timestamp()
trips_after_time = trips_from_start[trips_from_start["stop_timestamp"] > timestamp]

The first of those trips is trip ID 70526030, which arrived at Sullivan at 5:27:35 PM.

next_train = trips_after_time.loc[trips_after_time["stop_timestamp"].idxmin()]

train_arrival = df[
    (df["trip_id"] == next_train["trip_id"])
    & (df["parent_station"] == "place-north")
]

Looking up its arrival into North Station, we see:

It appears we would have arrived at 5:32:02 PM.

Link to this section Bus data

The bus data is a little different. The MBTA provides it as a ZIP file for each year, with a CSV file for each month. At time of writing, the latest one is MBTA-Bus-Arrival-Departure-Times_2025-09.csv.

The data is pretty straightforward, providing the following columns:

Notably, not every stop is covered in this data, only the time points (places along the route with a scheduled arrival time). However, the time points are typically placed at high-traffic stops like subway stations or the start and end of the line, so they are probably useful anyway.

Link to this section Basic parsing

Let's see if we can repeat a similar "simulated journey" to what we did with the subway, but with the bus data. Suppose I'm trying to get from Harvard to Hynes Convention Center station using the 1 bus on 2025-09-20 at 11:00 AM.

Ingesting the CSV file is quite easy:

import pandas as pd

path = "MBTA_Bus_Arrival_Departure_Times_2025/MBTA-Bus-Arrival-Departure-Times_2025-09.csv"
df = pd.read_csv(path)

The first part is quite similar to simulating a subway trip. Let's try selecting all of the trips that arrived at our destination stop ID, stop 79, then find the records for those trips departing from Harvard, stop 110.

trips_to_target = df[
    (df['stop_id'] == 79)
    & (df['direction_id'] == "Inbound")
]

trip_ids = trips_to_target["half_trip_id"].unique()

trips_from_start = df[
    (df["stop_id"] == 110)
    & (df["direction_id"] == "Inbound")
    & (df["half_trip_id"].isin(trip_ids))
]

And finally, we need to filter by trips happening after our chosen start time, so we need to handle the problem of dates.

Link to this section Dates and times nonsense

How do we assemble the service_date and scheduled/actual fields into an actual timestamp like we got with the subway data?

Let's start by parsing the service date.

timestamp = datetime.datetime.fromisoformat("2025-09-01")
>>> datetime.datetime(2025, 9, 1, 0, 0)

Now, let's parse the timestamp offset. Note that it is in UTC, not local time.

offset = datetime.datetime.fromisoformat("1900-01-01T11:31:00Z")
>>> datetime.datetime(1900, 1, 1, 11, 31, tzinfo=datetime.timezone.utc)

Right now, the offset is an aware datetime, meaning it contains timezone info. Combining aware datetimes with their counterparts, naive datetimes, is usually not allowed. Let's make the service date an aware datetime as well.

You might be tempted to use the astimezone method, but this will convert the naive datetime to an aware datetime assuming the naive datetime is in local time, which is not what we want -- we want to keep the year/month/day values the same, but just attach timezone information to this instance. We can use the replace method and replace the tzinfo field.

timestamp = timestamp.replace(tzinfo=datetime.UTC)
>>> datetime.datetime(2025, 9, 1, 0, 0, tzinfo=datetime.timezone.utc)

Cool. Now the only thing we need to do with the offset is subtract the placeholder date (1900-01-01). Python implements this nicely, where subtracting one datetime from another gives a timedelta object.

offset -= datetime.datetime(1900, 1, 1, tzinfo=datetime.UTC)
>>> datetime.timedelta(seconds=41460)

Now, we can add this to our service date:

timestamp += offset
>>> datetime.datetime(2025, 9, 1, 11, 31, tzinfo=datetime.timezone.utc)

And finally, convert it from UTC to our local time, then strip the timezone info to match the behavior of our other code. If this were production code, we would want to only use aware datetimes... but we're just messing around so let's do what's easy.

import zoneinfo
timestamp = timestamp.astimezone(zoneinfo.ZoneInfo("America/New_York"))
timestamp = timestamp.replace(tzinfo=None)
>>> datetime.datetime(2025, 9, 1, 7, 31)

Cool! So that trip was at 2025-09-01 at 7:31 AM.

Link to this section Pulling it together

Okay, back to the show. We were trying to filter by trips happening after a given time. Let's add a column to our dataframe with a proper timestamp to match our other data.

import zoneinfo

def convert_timestamp(row):
    if not isinstance(row["actual"], str):
        return pd.NA

    timestamp = datetime.datetime.fromisoformat(row["service_date"])
    offset = datetime.datetime.fromisoformat(row["actual"])

    timestamp = timestamp.replace(tzinfo=datetime.UTC)
    offset -= datetime.datetime(1900, 1, 1, tzinfo=datetime.UTC)

    timestamp += offset
    timestamp = timestamp.astimezone(zoneinfo.ZoneInfo("America/New_York"))
    timestamp = timestamp.replace(tzinfo=None)
    return timestamp

df['timestamp'] = df.apply(convert_timestamp, axis=1)

After rebuilding trips_from_start with this new column, we can select the trips after our chosen time, choose the one that departed earliest, and see when it got to Hynes in a similar way to our subway trip simulator.

timestamp = datetime.datetime.fromisoformat("2025-09-20 11:00:00")
trips_after_time = trips_from_start[trips_from_start['timestamp'] > timestamp]
next_bus = trips_after_time.loc[trips_after_time['timestamp'].idxmin()]
bus_arrival = df[(df['half_trip_id'] == next_bus['half_trip_id']) & (df['stop_id'] == 79)]

I would've made it to Hynes at 11:24 AM. Not bad!

Link to this section Simulating longer journeys

Why do we care about simulating historical journeys, you might ask? It's useful for answering a few different types of questions:

And, my favorite:

Link to this section Simulating an existing speedrun

Transit system speedrunning is a phenomenon in which competitors attempt to travel through all stations within a system as quickly as possible.

My friend Tris completed a speedrun about a year ago and documented her route in a Mastodon thread. Let's see if we can accurately simulate it!

I made a few improvements to the algorithm:

For parts of the run involving walking, I set very optimistic transfer times because I can vouch for the fact that Tris walks very fast.

The code for this is on GitHub if you want to play around with it.

Not too bad! Especially considering that the "actual time" column is based on Mastodon post timestamps so probably 1-2 minutes behind the actual timing.

Link to this section Generating the optimal route

My eventual plan with this is to use it to learn more about what makes an ideal MBTA speedrun, including the route, timing, etc. It's been a bucket list item of mine for a while to do a speedrun, and I want to see if I can find something new in terms of route, timing, etc. I think it's unlikely I'll find anything that substantial that could make a difference, but maybe it's possible...

Since running this type of simulation becomes extremely fast, there are a lot of places you could take this other than just tracing manually entered routes:

Link to this section Takeaways

I hadn't really done an in-depth data analysis project quite like this before. I definitely learned a ton!

Ingesting data into a data structure like this is something I usually consider "grunt work," but working on this showed me how wrong that assumption can be. I tried to throw a lot of this work to an LLM, only to watch it struggle against the date and timezone issues and completely miss the nuance between stop_timestamp and move_timestamp. While I got much further than it did (thankfully for the sake of my job security), it still required me to step away from the problem for a day before I could nail the accuracy.

I had also heard people constantly talk about how timezones are hard and date parsing is a mess, but had been spared the brunt of that struggle until now. I have discovered that there are a lot of bad ways to represent dates and times in software. I feel lucky that I do not need to deal with things like this more often.

The last thing I'll mention is that this project highlighted the difference between working with data in a domain where a dominant format exists (i.e., realtime GTFS), and working in a domain where implementors have no common format to use (i.e., historical transit data). I find it easy to get annoyed at format specifications not perfectly matching what I want to do... but when standardized formats work, they're pretty great!