A few days ago, the Federal Transit Administration posted the 2012 National Transit Database. Data are downloadable in three formats. First, you can download profiles for every transit agency (21.3 MB). These give basic data such as trips, passenger miles, fares, and costs broken down by type of transit. If you don’t want to download the whole book, you can also download profiles for individual agencies by entering the agency name, city, or ID number in a search box (scroll down and look for “Individual Profiles” on the righthand column).
Second, you can download data tables, which present the data for all transit agencies in fairly user-friendly spreadsheets. All of the tables at once can be downloaded in a self-extracting exe file (4.1 MB), but my Macintosh doesn’t want to extract the spreadsheets. One problem with the data tables is that there are separate tables for trips, fares, operating costs, capital costs, and other data, so comparing the numbers is difficult.
Third, you can download database spreadsheets. These are more difficult to read than the data tables, but are easier to manipulate on a computer because every row on each spreadsheet follows a consistent format. Like the data tables, these spreadsheets can also be downloaded as one self-extracting exe file (5.1 MB).
While it is easier to do calculations on the database than the data tables, individual spreadsheets for trips, fares, operating costs, and capital costs don’t have a consistent number of rows. So I’ve combined the data that I use most often into a single summary spreadsheet (1.8 MB). This spreadsheet is similar to the ones I’ve made in previous years. If you aren’t familiar with those, then you need to know that the spreadsheet is divided into three main parts.
Part one, the first 1774 rows, consists of agency-by-agency data broken down by modes such as “MB” (motor bus) and “LR” (light rail). Part two summarizes the data by mode in rows 1778 through 1796 (and provides a key for what all those two-letter mode codes mean). Rows 1799 through 1817 provides the same summery but only includes data from agencies that submitted energy data. The calculations of energy and CO2 per passenger mile on these rows will be more accurate than in the first set of totals. Finally, part three summarizes the data by urbanized area in rows 1819 through 2299.
The columns are pretty similar to those used in the past, but I’ve included four or five new ones. Column A is the FTA’s agency ID number; B is the agency name; C is the mode; and D is whether the service is directly operated by the agency (DO) or purchased from other operators (PT). Column E is the agency’s headquarters city, F is urbanized area number, and G is the state. Columns H, I, and J is the population, density, and land area of the urbanized area each agency serves; these don’t match Census Bureau numbers but seem to be close to 2011 Census Bureau estimates.
Columns K through N are trips, passenger miles, vehicle revenue miles, and vehicle revenue hours. Column O is the number of days a year the agency operated (which I thought might be useful but probably isn’t). Column P through S are fares and costs. Columns T, U, and V are the number of vehicles, seats, and standing room, while W is the directional route miles of rail lines (divide by 2 to get route miles). All of these columns are straight from the database with no calculations.
The database includes energy use in kilowatt hours, gallons of various kinds of fuel, and other measures. I used standard factors to convert all of these to BTUs, which are shown in column X, and to carbon dioxide emissions in pounds in column Y. In the case of electric powered transit, I estimated CO2 emissions based on the mix of electricity produced in each state as published by the Department of Energy. Of course, electricity often crosses state lines, so this is only a first approximation.
All remaining columns are calculated from the previous ones, and include things like BTUs and CO2 per passenger mile and passenger miles per vehicle revenue mile, which is the average number of people on board each bus or rail car over the course of the year. This year, column AA shows carbon dioxide emissions in pounds per passenger mile (to be consistent with previous years) and column AB is in grams per passenger mile (which is the more usual format).
If you find this summary file useful and haven’t done so already, you may also want to download similar summary files for 2011, 2010, 2009, 2008, 2007, 2006, and 2005. The formats are similar though some columns are included in later summaries that I didn’t include in earlier ones.