The Intrepid Data Nerd

The last CFFR (FY2010)

In the fall of 2011, the IDN analyzed the FY2010 Consolidated Federal Funds Report by Congressional District and Party.  This summary of federal spending by Congressional district lets you see with your very own eyeballs who's raiding the Treasury!
Since Congress recently terminated funding for the Census Bureau's Federal Financial Statistics program, there won't be any FY2011 CFFR to analyze!  Congress has decided to stop wasting our tax dollars reporting on how they're...uh, wasting our tax dollars. 

This district-level summary of the FY2010 CFFR ranks all 435 Congressional districts by total FY2010 federal spending per capita.  Some of the highlights (or lowlights) are presented in this nifty "Party On!" poster featuring the 50 biggest budget hogs in Congress:

  • The 50 top-spending districts accounted for 25% of all FY2010 federal spending. 
  • 39 of the 50 top-spending districts are represented by Republicans. 
  • 17 of the 50 top-spending districts are represented by "Tea Party" Republicans in the 62-member House Tea Party caucus. 
  • Florida's 20th, 22nd and 23rd Congressional districts each spent over $65,000 per capita in federal funds!

To provide a different perspective, the Nerd combined CFFR and IRS tax data in a state-level analysis of the 2010 federal deficit
These data are also presented in a "You Fix Your Deficit, We'll Fix Ours!" poster

  • The states with the highest federal expenditures per tax dollar are Mississippi ($9.09), South Carolina ($8.49) and Florida ($8.27). 
    The states with the lowest federal expenditures per tax dollar are Minnesota ($1.11), Delaware ($1.31) and Ohio ($1.44).
  • Florida alone accounts for 19.3% of the total federal deficit.  Texas accounts for 8.7%.  California accounts for 7.8%.
  • Individual insurance payments (Social Security, Medicare, SSI and Medicaid) account for 69% of federal spending in Florida, 64% of federal spending in Lousiana, and 48% of federal spending in South Carolina.
  • The states with the highest per-capita federal spending are Louisiana ($36,602), Florida ($36,461) and North Dakota ($25,911).
    The states with the lowest per-capita spending are Vermont ($10,924), Michigan ($11,045) and Illinois ($11,242).
  • The states with the lowest per-capita federal tax payments are Mississippi ($1,929), West Virginia ($2,212) and New Mexico ($2,538).
    The states with the highest per-capita tax payments are Delaware ($13,844), New Jersey ($11,563) and Minnesota ($10,948).
  • The states with the biggest per-capita federal deficits are Florida ($32,051), Louisiana ($30,283), Hawaii ($21,598) and North Dakota ($20,668).  The states with the smallest per-capita federal deficits are Minnesota ($1,255), Ohio ($3,496), Illinois ($4,221) and Delaware ($4,234).

In case you want to wallow in the numbers yourself, here is the entire FY2010 Consolidated Federal Funds Report (39MB) including my summaries by Congressional District and State, and gross federal tax collections and tax refunds by state obtained from Tables 5 and 8 of the 2010 IRS Data Book.

Federal Taxes and Expenditures by Congressional District, 2008

To get a Congressonal district-level analysis of the federal deficit, the IDN compared federal spending by district from the FY2008 CFFR versus 2008 tax payments by district as summarized from the IRS's individual income tax data by Adjusted Gross Income and zip code for tax year 2008--the most recent year for which IRS data by zip code are available. 

First, I extracted the totals by zip code from the IRS's 51 state files.  Next, to match zip codes to Congressional Districts, I created a composite GIS shapefile of Zip Code Tabulation Area polygons in the US from 51 shapefiles obtained from the Census Bureau

A derived point shapefile of zip code centroids was then matched to a polygon shapefile of 110th Congressional districts using ArcGIS's spatial join utility.  The names, centroids and Congressional districts of 42,961 US zip codes are distributed here as an Excel file. 

In order to account for other federal taxes at the zipcode level, I apportioned each state's corporate, estate, gift and excise taxes (from IRS 2008 Data Book Tables 5 [gross revenues] and 8 [refunds]) evenly between the Congressional districts in each state.  Net revenues from these other sources are mostly dwarfed by individual income tax revenues. 

District-level deficits are calculated as net revenues minus CFFR expenditures as calculated from a district-level summary of the FY2008 Consolidated Federal Funds Report (28MB). The final results, with Congressional districts ranked by per-capita deficit spending are distributed here as an Excel spreadsheet.

Congressional districts are ranked by per-capita (deficit)/surplus.  The top 50 districts accounted for just over $1 trillion, or 35% of the total FY2008 federal deficit.   28 of these were represented by Republicans.

GIS data files:
US Zip Code centroids--Excel file (42,961 records) Includes ArcGIS spatial join of zip codes to Congressional districts.
GIS shapefile of US zipcode centroids (3MB)
GIS shapefile of US zipcode (Census ZCTA) polygons (65MB)

Prior CFFR Analyses

Summaries of the FY2009 Consolidated Federal Funds Report
by state, county and Congressional District, and by object code, agency and program (2.7MB).

Federal Taxation and Spending by State, 2006, 2007, 2008 and 2009
These indices are derived from the same data sources as the Tax Foundation's well-known series for 1981-2005.  This analysis also includes gross and per-capita contributions of each state to recent federal deficits.  Note that while the Tax Foundation's ratios of federal receipts to taxes were adjusted to reflect a balanced-budget scenario, the ratios that I have calculated for the years 2006-2009 are not.

2010 US Counties shapefile with Census data libraries

The IDN has created an updated shapefile of 3,143 US counties including population and housing profile data from the 2010 Census. While the Census's 2010 TIGER files include territorial waters and fine detail (making for flabby-looking coastal counties and very large file sizes), this shapefile represents land areas only, and its polygons have been generalized for file compactness.

I tried to create intelligible field names. Here are the field definitions.

I also compiled a series of spreadsheets from recent US Census Bureau data releases, including the Census's USA Counties Data File Downloads website. These tables are readily joined to the GIS counties shapefile via the "FIPS" field. Except where the field names are self-explanatory, each spreadsheet includes two worksheets: a data sheet and a listing of variable definitions.

NEW (3/26/2013): source data from the 2013 Robert Wood Johnson Foundation/University of Wisconsin County Health Rankings study

Converting Appalachian Trail GPS data to GIS layers

I downloaded discontinuous trail segments (lines), shelters (points) and parking areas (points) recorded by GPS from the AT Conservancy.  After deleting extraneous trail segments less than 10 meters in length, I snapped the end nodes of the remaining segments together and eliminated any remaining dangles.  I also snapped vertices within a 1-meter tolerance. I then dissolved the segments by trail maintenance club, and then split them by shelter.

In order to create a correct sequence of trail segments between shelters, I selected and flipped all the southbound segments, so that all segments were oriented northbound. I then calculated starting and ending X-Y coordinates for each segment, exported these coordinates to Excel, and used Excel's VLOOKUP utility to match the starting and ending nodes of adjacent segments.  I then numbered the segments sequentially.

I recombined the trail segments by club, and combined the club segments into a single composite trail feature covering 2,120 miles.  Using a 1-Km resolution DEM (see below), I created a quick-and-dirty 3D version of the composite trail layer in order to create an elevation profile graph of it, as shown here:

Click for a poster-size version.

The initial geodatabase (29GB) contains the AT Conservancy's shelters and parking areas, as well as trail segments between shelters, segments composited by club, and the complete AT as 2D and 3D feature layers.
The revised geodatabase (59GB) contains more recent AT data processed the same way, plus a 6KM-wide corridor of 30M-resolution DEM data spanning the entire AT, plus 3D layers based on this high-resolution DEM.  

The 3D length of the Appalachian Trail, derived from the 30M-resolution DEM, is calculated to be 2,141.6 miles.   I also calculated average slopes for each of the 288 trail segments between active shelters.

1-Km resolution Digital Elevation Data for the continental US

I downloaded 30-arc-second DEM data from the USGS National Map Viewer, added it to a projected Arc dataframe (US National Atlas Equal Area coordinate system), used a polygon of the Lower 48 states as a raster mask, and exported a DEM of the continental US with a cellsize (nominal resolution) of 1 Km.

This map package includes the DEM, a derived hillshade map, and state boundaries.

Click for a poster-size version.

Here's a MONSTER-size (20,000 pixels wide) version.

2010 Census: Delaware Population and Housing Units by Census Block.
Excel file including DE State Plane centroids (24,115 records; 3MB).