The Consolidated Federal Funds Report by the Census Bureau provided data on which funds were distributed from the federal government to states, counties and places by program and agency.
I'm using the past tense because in 2012 the US Federal Budget eliminated funding for the branch of the Census Bureau which produced the CFFR. The status of the CFFR going forward is unclear.
The Census Bureau used to have an web-based query system for the CFFR, which while not perfect, was useful for a moderate range of users including some of those who wished to create datasets for analysis. The query system has been removed and now users have two options: *.pdf reports or FTP filesets.
For very casual use, say looking up a single figure for a single year, the *.pdf versions of printed CFFR reports will probably be acceptable. The *.pdf reports mimic the print reports produced before the query system and are just as inconvenient for data analysis.
All other users will be working with the FTP site, aka a somewhat confusing list of compressed filesets for download.
There is one small piece of good news: you can now easily locate data from 1983-2010, which is 10 more years than before.
The price is in usability. There are three compressed filesets:
- consolidated federal funds report 1983-1992.zip
- consolidated federal funds report 1993-2010.zip
- consolidated federal funds report states 1993-2010.zip
You might think that, based on names, the last fileset is the only one with data at a state level and that the other two are national summaries. This is incorrect. As far as I can see, given that both 1993-2010 filesets have the exact same documentation, the difference is that the 3rd fileset is useful for users who only want to work with data already formatted for immediate use in spreadsheets (*.csv). Given that it's fairly simple to import and format the *.txt and *.DAT files into spreadsheets though, this isn't much of an improvement.
The real effort will be in rearranging the fileset you decide to use. Likely tasks will include:
- converting from fixed-width format to delimited format
- aggregating from places to counties or states
- filtering by type of aid, program or agency
- linking program names to codes
Google Refine will be helpful for at least some of these tasks.
For example, suppose you want to see trends in funding over time. All of the file sets are sorted by year. You can consider opening each, adding a year column and then creating one large file for all years. You could then use Google Refine to filter to rows for only one program for all years. What's nice about Google Refine is that if you have selections made, when you export, you only export those rows.
Refine may also help you to aggregate the data to state level, but I haven't tried that myself yet.
You can do all of these things in MS Excel of course, it can be more complicated and the bigger the files get, the slower Excel runs.