The Grant XML file from Recovery.gov contains information about approximately 61,000 federal grants that comprise the Recovery Act of 2009.

If you want to do this yourself on your own instance of CouchDB, you'll need to clone this sample project, install CouchDB, and populate your database with grant documents from the parsed Grant XML. The sample project is available on GitHub here: http://github.com/to...n/sample-parse. To clone this project, execute: "git clone git://github.com/tobrien/sample-parse.git"

1. Download the Raw XML Data from Recovery.gov. If you've cloned the sample-parse from from GitHub, this data file will already be in ./data.

2. Install CouchDB On Your Local Machine - That particular answer is OSX-specific. If you are running Linux, you can just download the couched distribution and compile it from source. Be warned, Couch installation is often confusing due to the number of dependencies required. For more information on Couch installation, see Installation on the CouchDB Wiki.

3. Load the Recovery.gov Data into a CouchDB Database. This recipe assumes that there is a couch database named "grants", and that you are accessing the CouchDB administrative interface either through CouchDBX or http://localhost:5984/_utils/ on your own Couch installation.

4. Once you've loaded all of the Grant XML into CouchDB you should have approximately 60k documents, you are going to have a series of JSON documents which resemble the following JSON. (I've omitted a number of fields which are irrelevant to the current recipe.) Each document contains a series of names and values that correspond to the contents of feed/entry/content in the Grant XML document.

{
   "_id": "000191ef09c5af54f767c6c158fbd09b",
   "_rev": "1-3520487957",
   "recipient_name": "UNIVERSITY OF CALIFORNIA-LOS ANGELES, BOARD O",
   "recip_cat_type": "Higher Education",
   "recipient_type": "State controlled institution of",
   "agency_code": "7529: HHS - National Institutes of Health",
   "agency_name": "DEPARTMENT OF HEALTH AND HUMAN SERVICES",
   "federal_award_id": "F31DA025448",
   "federal_award_mod": "0",
   "state_app_id": "SAI UNAVAILABLE",
   "cfda_program_num": "93.701",
   "cfda_program_title": "Trans-NIH Recovery Act Research Support",
   "asst_cat": "Grants & Cooperative Agreements",
   "asst_type": "block grant",
   "proj_desc": "ARE DRUG-ASSOCIATED CUES SUSCEPTIBLE TO THE BLOCKING EFFECT?",
   "fed_funding_amount": "30990",
   "principal_place_code": "CALIFORNIA",
   "principal_place_state": "LOS ANGELES",
   "principal_place_state_code": "CA",
   "principal_place_cc": "90095",
   "principal_place_zip": "30",
   "principal_place_cd": "",
   "principal_place_country_code": "20725394\n"
}

5. Load the CouchDB interface, and click on the "grants" database. Then select "Temporary View…" from the database dropdown.

6. In this recipe, we're going to create a CouchDB view which will create a sum of all of the "fed_funding_amount" attributes of each document in the database.



Note: Because we've loaded the data into CouchDB as text, we need to use cast the fed_funding_amount to Number in the Javascript function.

Once you have typed in your new view functions, click on "Save As…". Same this view in a new document named "state" as "fundingState".



7. Save the view, and then select it from the dropdown, CouchDB will then create the View Index. This should take a few minutes, you can check on the status by click on "status" in the right-hand menu. The status screen is shown in the following figure:



8. Select the view from the database dropdown in the CouchDB interface, and you should see the following output:



9. If you would prefer to view the results over HTTP, go to: http://localhost:598...tate?group=true - you should see the following JSON document listing all grants in the Recovery Act by state. The key is the principal_place_state_code and the value is the sum of all fed_funding_amount values for each state code.

{"rows":[
{"key":"","value":44998041742},
{"key":"AK","value":1085432942},
{"key":"AL","value":1540376039},
{"key":"AR","value":893357329},
{"key":"AS","value":11963573},
{"key":"AZ","value":2191241087},
{"key":"CA","value":10798121095},
{"key":"CO","value":1433064889},
{"key":"CT","value":1046196155},
{"key":"DC","value":742934030},
{"key":"DE","value":312826782},
{"key":"FL","value":5369459233},
{"key":"FM","value":3619468},
{"key":"GA","value":3036078072},
{"key":"GU","value":35149426},
{"key":"HI","value":461076267},
{"key":"IA","value":989388403},
{"key":"ID","value":563956410},
{"key":"IL","value":3883500328},
{"key":"IN","value":2137071772},
{"key":"KS","value":844090776},
{"key":"KY","value":1412566899},
{"key":"LA","value":1410925257},
{"key":"MA","value":2210459419},
{"key":"MD","value":1587893126},
{"key":"ME","value":442020588},
{"key":"MH","value":1814451},
{"key":"MI","value":3303053848},
{"key":"MN","value":1562193567},
{"key":"MO","value":1857438977},
{"key":"MP","value":4491427},
{"key":"MS","value":980923851},
{"key":"MT","value":368434278},
{"key":"NC","value":2556203262},
{"key":"ND","value":293075456},
{"key":"NE","value":510686220},
{"key":"NH","value":399696986},
{"key":"NJ","value":2369471175},
{"key":"NM","value":701341228},
{"key":"NV","value":616159259},
{"key":"NY","value":7098607554},
{"key":"OH","value":3699215898},
{"key":"OK","value":1014076641},
{"key":"OR","value":1216584291},
{"key":"PA","value":2319695848},
{"key":"PR","value":1838861283},
{"key":"PW","value":1332652},
{"key":"RI","value":493276753},
{"key":"SC","value":1497554422},
{"key":"SD","value":305622340},
{"key":"TN","value":2171457075},
{"key":"TX","value":6908043998},
{"key":"UT","value":770879741},
{"key":"VA","value":2212825632},
{"key":"VI","value":21742368},
{"key":"VT","value":245560261},
{"key":"WA","value":2094865907},
{"key":"WI","value":1494927319},
{"key":"WV","value":650053382},
{"key":"WY","value":307786620}
]}

Note: There appears to be about $44B worth of records that do not have a principal_place_state_code.