What cities have the most and least profitable public transportation services in the US? Perhaps you can find out in this graphical visualization of the data!
The US data I'm analyzing here covers things like buses, vans, and rail ... but it might be even more interesting to analyze data from other countries, where they have a bit more variety in their modes of transportation. Can anyone guess what country my friends Joy and Erik are in?
And now, back to the US data!...
I found the data on the National Transit Data page, maintained by the Federal Transit Administration. The tables are in Excel spreadsheet format, so I had to import them into SAS, and then perform some SQL table joins to wrangle them into just the right form to plot.
Several variables are tracked in the data, but I decided to focus on the total operating expenses, and the fare revenues earned. I used a scatter plot with a marker for each individual type of transportation in each city or county (for example NC's "Rowan Transit Systems (RTS), Demand Response - Purchased Transportation"). And I annotated a diagonal line - markers above the line are 'profitable' (in the sense that the fares were higher than the expenses) and those below it lost money.
My first plot was a bit disappointing. Since a few of the really large cities (such as NYC) had such higher values than all the others, most of the markers were squished into the bottom left corner of the graph:
So I changed to a log-based scale for my axes, which helped a lot:
This graph lets me see the overall trend of the data, but I assumed that most people would want to see something a little more specific - like "How did my state do?" So I set up a graph for each state, where that state's markers are red, and all the other markers are light gray. And the red markers have hover-text so you can see the name and exact values. Click the North Carolina graph image below to see the interactive graphs for all the states:
It appears North Carolina has a big variety. We had one of the few profitable markers above the line (a Vanpool in Wilmington), and one of the markers farthest below the diagonal line (some Demand Response transportation in Rowan county).
So, how did your state do? Or, if you're from another country, do you think your country's transit systems are more, or less, profitable?