Mapping FY’ 2008 Earmarks for the United States with PowerShell and NetMap

I read Senate sends big spending bill to Bush to sign. At the end it said

Taxpayers for Common Sense, a watchdog group, discovered 2,322 pet projects totaling $6.6 billion. That included 2,025 in the defense portion alone that cost a total of $4.9 billion.

On http://www.taxpayer.net there is TCS Database of FY’ 08 Earmarks that can be downloaded. I did and proceeded to do some data mining. 

States Receiving Defense Earmarks

This is a network graph from data in the TCS Earmarks Excel document. Each line representing a state receiving an earmark from a bill.

• Ag-Rural Development-FDA
• Commerce, Justice & Science
• Defense
• Energy & Water
• Financial Services
• Homeland Security
• Interior
• Labor-HHS-Education
• Legislative Branch
• Military Construction
• State-Foreign Ops
• Transportation and Housing & Urban Development

The red lines are states receiving money from the Defense bill.

Approach

I edited the spreadsheet, removing the Title, summary information and modified some headings to remove spaces. Then I exported the spreadsheet to a csv format. Using PowerShell, I imported the data and filtered out data records where state and bill were empty. Earmarks allocated to more than one state in a single record were also filtered out. The data was transformed into records of the form Source and Target and then mapped as a directed graph with NetMap. I used the PowerShell script I posted about in Using PowerShell and World Bank Data  with Microsoft’s NetMap graphing libraries.

Note

It took longer to write this post than to mine and graph the data. There are other interesting columns in the spreadsheet like the House and Senate requestors and party. This data may need some work but I’ll graph it and look for patterns.