Saturday, July 28, 2012

With a little help from a friend

Staying in the Olympic weekend theme... with a little help from a friend you can get the Census data from their recently released beta data API in a format more easily used in Excel.

I found out about the Census Data API and couldn't resist. Is there a map? What do you get? Quickly got an API key and off I went.

Oh... do you know the ANSI code for your state? Mine is 06. That's Ca/CA/California for most.
Wanted to get population by state? that's parameter code P0010001 in the 2010 Census Summary File 1 (SF1) dataset.

Enhancement Request #1 - provide more easily accessible lookup for the various parameters.
Enhancement Request #2 - allow state abbreviations in addition to ANSI codes.

Alright, once you have this plus the key, off you go:,NAME&for=state:*

response: a JSON array like this:

that's ok if you're a web developer, but I had thought to use this in Excel!

Enhancement Request #3 - support additional common output formats like CSV.

Based on 'stackoverflow convert-json-format-to-csv-format-for-ms-excel' I created a simple 
wrapper for the requests I was sending. The resulting CSV opens nicely in Excel like so:

AHA! From here we're good with the new Esri Maps for Office add-in:
And then shared to ArcGIS Online: 

View Larger Map