Research Guides: Business Research: Government Finances from US Census Bureau Data

Government Finances from the US Census Bureau Data

The US Census Bureau provides data on state on local government finances on its web site. This data goes down to the level of the finances of individual cities. Unfortunately, the data at this level is presented in massive tables that are designed for database programs rather than humans. This page shows how to access and interpret this data, and should be useful whether you are planning to write a program to analyze the data or will be looking at the data manually.

Getting The Data

The starting point for gathering the data is here: https://www.census.gov/govs/local/
On the left of the page is a box where you can select which year's data you want (at the time this was written, available dates are from 1991 to 2008).

After selecting a year, the left column is "Viewable Data," easily readable tables summarizing the data. Unfortunately, the viewable data only has summaries, and does not break the data down for individual cities. For that, look at the right column, "Downloadable Data." In particular, look for two files: Individual Unit File - Public Use Format, and Directory Information File - Individual Units. Not every year has these files; the links here are for the most recent (2003-2004) versions. These are large, compressed files: for 2003-2004 the first is 3.3Mb (expanding to 19.6 Mb when uncompressed), while the second is .55Mb (expanding to 4.2Mb).

Download both files to your computer and uncompress them (recent versions of Windows should uncompress automatically by clicking on the .zip files). This gives you two files with names like 2004FinInddiv5_noimps051206.txt and fin04gid.txt; for convenience they will be called the data file and the directory file. You can open them with Notepad or any other word processor program; the data file is just two columns of numbers (with a few letters), while the directory is a mixture of names and numbers.

Reading The Directory File

Here is a small part of the directory file:

22000000000000MASSACHUSETTS     [...]            [...]  642035703                   1    04
22100100100000BARNSTABLE COUNTY [...] BARNSTABLE [...] 2500199001   22857702                   1063004
22100300300000BRISTOL COUNTY    [...] BRISTOL    [...] 2500599005   54343402                   1063004
22101101000000NORFOLK COUNTY    [...] NORFOLK    [...] 2502199021   65648602                   1063004
22101201100000PLYMOUTH COUNTY   [...] PLYMOUTH   [...] 2502399023   48574702                   1063004
22200120100000BARNSTABLE CITY   [...] BARNSTABLE [...] 2500103600    4885402                   1063004
22200200100000NORTH ADAMS CITY  [...] BERKSHIRE  [...] 2500346225    1443002                   2063004
22200200200000PITTSFIELD CITY   [...] BERKSHIRE  [...] 2500353960    4502302                   1063004
22200300100000ATTLEBORO CITY    [...] BRISTOL    [...] 2500502690    4316402                   1063004

(To make this sample more readable, a bunch of spaces have been removed in each line, as indicated by "[...]". When you view the file in a word processor it won't look so neat because each line is so long it will wrap one or two times.)

The directory file has one line for each "unit," where a unit can be a state, county, city, township, special district, or independent school district. While the directory file provides a bit of data on the unit (its population in the most recent census), the main use of the file is to find the codes for the units you are interested in so you can find their data in the data file. The code for a unit is all the digits at the beginning of the line preceding the name of the unit. For example, here's the line for the city of Barnstable, Massachusetts:

22200120100000BARNSTABLE CITY    BARNSTABLE  2500103600    4885402                   1063004

This tells us that Barnstable's code is "22200120100000". That's all you really need to know to find data for Barnstable, and you can skip down to Reading the Data File, or you can read on for more details.

The Gritty Details

The first two digits of the code tells the state ("22" for Massachusetts); the next digit tells the type of unit (0=State, 1=County, 2=City, 3=Township, 4=Special District, 5=Independent School District); in this case a "2", so Barnstable is a city. The next three digits are the county ("001" for Barnstable County, as can be checked by looking at the second line of the example), and the next three digits are the code for the unit (so Barnstable has code "201" inside Barnstable County in Massachusetts). The rest of the digits are for codes that don't apply here, and then there is the name of the unit ("BARNSTABLE CITY").

The next column ("BARNSTABLE") is the name of the county. Then comes FIPS (Federal Information Processing Standards) code for the unit ("2500103600"). The next column gives the population for the unit ("48854") and the year for the population ("02"). Finally there is the Fiscal Year End for the unit ("0630") and the year for the data ("04"). For even more details, you can read here.

Reading The Data File

Here is a small part of the data file:

22200120100000E01        377704
22200120100000E12       6187604
22200120100000E23        318504
22200120100000E25         45704
22200120100000E29        137304
22200120100000E31        276404

Each line of the data file gives the amount of a particular item (which may be a charge, revenue, expense, debt, etc. depending upon the item) for a particular unit. The first 14 digits of each line ("22200120100000" in this sample) is the unit code, while the next three digits ("E01" for this first line of the sample) gives the item code. Then comes the amount ("3777" for the first line) followed by the survey year ("04"). The amounts appear to be in thousands of dollars, so that would be $3,777,000.

The unit code is found in the directory file, as in the example above which showed that "22200120100000" is the unit code for the city of Barnstable in Massachusetts.

The item code is found in a table here; here is a small portion of the table:

Item Code	Description
E01	Current Operations - Air Transportation
E03	Current Operations - Miscellaneous Commercial Activities, NEC
E04	Current Operations - Correctional Institutions
E05	Current Operations - Corrections - Other
E12	Current Operations - Elementary and Secondary Education
E16	Current Operations - Higher Education Auxiliary Enterprises
E18	Current Operations - Other Higher Education
E19	Current Operations - Educational Scholarships, Assistance, and Subsidies
E21	Current Operations - Other Education

So looking at the first two lines of the sample data file, we can see that in 2003-2004, Barnstable spent $3,777,000 on Air Transportation, and $61,876,000 on Elementary and Secondary Education.

More details on what is included in each item can be found in the Government Finance and Employment Classification Manual.

Putting It All Together

Here are the steps to find data about several cities or towns:

Download the directory file and data file from the US Census website.
Open the directory file in Notepad or a word processor, and search for each city or town of interest, and record its unit code (e.g., "22200120100000" for Barnstable).
Look in the table of item codes, and record the item codes of interest (e.g., "E12" for Current Operations - Elementary and Secondary Education).
Open the data file and for each combination of unit and item code, do a search (e.g. "2220012010000E12" for education current operations in Barnstable) and record the result. This process is helped by the fact that all the items for a given unit are grouped together in the data file.

Of course, if you are doing a large number of cities and/or items, it might help to automate some of this process using a spreadsheet or database program.