Making AP Election Data Easy with Fusion Tables

This post is for journalists who use (or would like to use) election data from the Associated Press -- which is a paid service the AP provides. If that describes you, read on!

When Google gave away free, live election data for the Iowa Caucuses, something struck me right away: It was easy.

Data provided by the Associated Press, which drives almost every election site you've ever seen, is notoriously tricky to manage -- a statement I'm confident making based on talks with many election-night veterans and on my own experience.

But Google's results were posted in a public Google Fusion Table, which is basically a spreadsheet on steroids. That meant I could get the data I wanted simply by constructing the correct URL. Votes by county, sorted by county? No problem. Candidate totals for the entire state? Sure. Votes mashed with other data I had? Yup. Formatted in JSON? Bring it.

Instantly. Easily.

(Here are the URLs I used above, and here's the documentation from Google on how to construct them. Hard-to-find tip: Append &jsonCallback=anything to get the json. And if you're using jQuery AJAX calls, make it &jsonCallback=?)

A week later, for the New Hampshire Primary, there were no free Google data. So I made an AP data-fetcher-and-wrangler based on code by Al Shaw. Through no fault of Al's code, my adaptation was slow, complicated and crashed every couple of hours. It worked, but just barely.

Next up was South Carolina, and I was determined to make AP's data friendlier by putting in a Google Fusion Table.

And it worked.

How I did it

In the interest of time and clarity -- and to spark discussion before the primaries are over (or irrelevant) -- I'm leaving out a bunch of the nitpicky details. If you're an AP Elections subscriber and want to try this, contact me at john (at) johnkeefe.net. I'll help you any way I can.

AP provides data in several formats, including a "flat file," which basically is a huge, semicolon-delimited spreadsheet. Each row represents a county, and each column the latest stats for that county, such as precincts reporting and each candidate's total votes.

The flat file doesn't have column headers, though. So I first uploaded AP's South Carolina test table to Google Spreadsheets and added the column names I needed.

I then imported the spreadsheet into a non-public Google Fusion Table.

For election night, I set up a script on my computer that does the following steps every two minutes:

1. Logs into AP's servers via FTP and downloads the flat file.

2. Deletes the data from the Google Fusion Table I made earlier and uploads the entire flat file anew. This is accomplished with a little Python program written by the brilliant (and patient) Kathryn Hurley, of the Google Fusion Tables team. I've posted it here with her permission. I don't know Python, but didn't need to. I just needed to make sure the list of columns in the data_import.py exactly matched the columns in my table. So I cut-and-pasted them from the Google spreadsheet. The script executes the command:

python data_import.py [google account username] [flat file filepath] [fusion table id]

3. Next, it hits the Fusion Table with a simple URL request formatted to return the data I want as JSON. This is the URL I used for getting the county totals.

4. Then it sends that JSON as a file, via FTP, to a subdirectory of my map application on WNYC's servers.

Once a minute, the election map running in the user's browser looks at that data file to get the latest info.

In this way, I completely avoided the need to build and maintain a database. I know there are great database folks out there, but I'm not one of them. The Fusion Table became my database.

Technically, I could skip steps 3 and 4 by simply pointing my map application at the Fusion Table to get the data it needs. That's what I did for Iowa, using the free Google data. But the table would be publicly visisble on Google's servers ... and my reading of the AP contract, understandably, doesn't allow that.

I strongly believe that the easier AP's data is to use, the more budding journocoders will make new election-night interactives. And if we can work together to do that, let's. For me, this method was a lot easier than anything else I had tried before.

A final note: If you're a Python-savvy programmer, be sure to check out what the LA Times has shared to make life easier, too. It's pretty slick.

Journo-Hacker Sharing in Action

If you need more proof that it's valuable for journalist-programmers to show their work, here's some: WNYC's Live New Jersey Election Map.

Exactly one week after Albert Sun of the Wall Street Journal New York Times shared some of his work, we made this:

(Map isn't embeddable for licensing reasons; the live version is here.)

Here's what happened.

Last month I went to a Hacks/Hackers NYC meetup about mapping. There, Albert showed his WSJ Census Map Maker project and a map I had admired that has dynamic mouse-overs without using Flash. At one point, he showed his project's code repository and welcomed us to use and build on it.

The next day, I downloaded the code and tried to make a rough version of Albert's map, but using the shapes of New Jersey legislative disricts (downladed from the US Census, stored in this Fusion Table, which generates this KML file). After a little tinkering -- which includes a fix I've described in the comments below -- I managed to build one that works. I sent that to stellar coder Jonathan Soma, of Balance Media, who works with me to build interactives for WNYC.

I also reached out to Al Shaw, of ProPublica, who I knew (from another Hacks/Hackers Meetup) had wrestled with live Associated Press election data for Talking Points Memo. He had some great tips, which I passed along to Soma, too.

Also on the case were Balance's Kate Reyes and Adda Birnir, who crafted the map's design and user experience -- a particularly tricky task because each district elects one person for state senate and two people for state assembly.

A week later, as the results rolled in, WNYC's map was live and rockin' -- listing real-time returns for each district, and changing colors when races were called.

In the process, Soma built on Albert's work, and those modifications are now a part of the code base (see Github commits here and here).

And if you need proof that such work is valuable, the map was WNYC's No. 6 traffic-getter for the month -- despite the fact it was truly useful for about 4 hours late on the evening of an off-year election.

Hacks/Hackers NYC: How We Made Our Maps

Tonight I had the honor of sharing some of WNYC's mapping work to a big audience at the Hacks/Hackers NYC Meetup, along with Albert Sun of the Wall Street Journal and Jeff Larson of ProPublica.

My part was a version of the demo I gave at the Online News Association conference on how we built our Hurricane Evacuation Zones map, the links and slides for which are here. There's also an eerily similar blog post about it.

At tonight's talk, I promised several folks the JavaScript I use for the address-locator box on our maps. I'll set it up as a proper Git soon, with better documentation, but you can copy it as-is, paste it into your JavaScript code and follow the comments. It should work for you -- but let me know if it doesn't!

Also, the link to the amazing census data site I mentioned is census.ire.org.

Thanks to everyone who came out, and to Chrys, Al, Beth and Jenny for inviting me.

Hands-on Redistricting

One of many redistricting experiments we're working on looks at the effort by some community groups to carve out districts particular to their community.

So we gave it a whirl.

There are many ways to wrestle with this data -- and there is much wrestling yet to come. But by ranking and repeatedly mapping the Asian populations by census tract, we were able to come up with some maps that actually satisfy some of the contraints (except for the one that says you can't draw a district based on race!) For more info, click the "Deeper Data Details" link.

WNYC's Colby Hamilton inspired this map, and has a great post to go with it.

Mostly I worked in Google Spreadsheets and Google Fusion tables, using data from the great census.ire.org site.

I had hoped to create a single shapefile of my new district, but joining all of those census tracts kept choking my computer (and QGIS). So you're actually seeing a bunch of tracts without borders. I did use QGIS to rework some of the shapes so they cross two parks -- allowing me to make a contiguous district.

UPDATE: Here is the list of census tracts I used. And if you're poking around in the code, don't rely on the data in the experiment's fusion table; I mucked with it a lot.The original, intact tract data is here. And the Congressional district map of New York is valid for the 111th Congress.

Random thing I learned: Did you know QGIS can export shapefiles into KML? I didn't. Not used here, but good to know.

Once Upon a Datum: Mapmaking on News Time

In September, I shared how WNYC makes news maps during a talk at the the Online News Association conference.

UPDATE: ONA posted a video of this presentation, which I've embedded here:

'Once Upon a Datum': Telling Visual Stories from Online News Association on Vimeo.

Here are my presentation slides (PDF), and here's a list of links to pages and sites I discussed in my talk:

Same-Sex Couples in NYC: http://www.wnyc.org/articles/wnyc-news/2011/jul/14/census-shows-rising-number-gay-couples-and-dominicans/

Hispanic Origins in NYC: http://www.wnyc.org/articles/wnyc-news/2011/jul/14/census-shows-rising-number-gay-couples-and-dominicans/

The New Littles: http://www.wnyc.org/shows/bl/clusters/2011/jun/02/june-guest-andrew-beveridge-and-new-littles/

Marijuana Arrests: http://www.wnyc.org/articles/wnyc-news/2011/apr/27/alleged-illegal-searches/

Contributions by Zip Code: http://empire.wnyc.org/2011/07/where-are-the-mayoral-candidates-raising-their-money/

Dollars in a District: http://empire.wnyc.org/2011/09/the-54th-assembly-campaign-contribution-breakdown-where-have-all-the-in-district-donors-gone/

NYC Hurricane Evacuation Map: http://wny.cc/EvacZones

NYC DataMine: http://www.nyc.gov/html/datamine/html/data/geographic.shtml

Shpescape: http://www.shpescape.com/

Hurricane Zones Fusion Table: http://www.google.com/fusiontables/DataSource?dsrcid=964884

Fusion Tables Layer Builder: http://j.mp/FusionBuilder or http://gmaps-samples.googlecode.com/svn/trunk/fusiontables/fusiontableslayer_builder.html

Layer-wizard map from presentation: http://dl.dropbox.com/u/466610/preso-map.html

 


Mapping Dollars in a District

I loved this challenge.

WNYC's Colby Hamilton wanted to know: How much money was being raised by candidates for a state legislative district from within the district itself?

Answer: Very little.

Making this Map

This wasn't my typical "just upload it to Fusion Tables" project. It got geeky quickly, intentionally.

My method involved a PostgreSQL / PostGIS database and QGIS mapping software. Everything is free, which is amazing, yet they take some advanced tinkering -- especially the database stuff.

First, I geocoded the donation addresses, getting each one's latitude and longitude, using this nifty batch geocoder. The donor's name and donation amount were also on each line.

Then I fed the data into my PostgreSQL database and pulled it into QGIS (they talk nicely together). I also layered in a shapefile of the district from the US Census Bureau.

I then asked QGIS where the donations and the district "intersect" -- and spit out the resulting shapefile for each candidate. 

Next I uploaded each candidates' "intersection" shapefile and their all-donations shapefile to Google Fusion Tables using shpescape. Once there, I used Fusion Tables' aggregation feature to total the donations in the district (the intersection).

Fusion Tables also allowed me me plot all of the donations, and also the shape of the district. (Little trick: I actually copied the "geography" cell from the 54th District table and added it as a new row to the donations table. That way the donations and the district shape appear at the same time.)

Finally, I put the layers together into a map template I've grown since building 2010 Census maps.

You'll notice I'm not diving deep into the details here, but if you're looking into a similar project, drop me a note at john at johnkeefe dot net look at this page, where I share every tidbit, command and SQL "select" statement I used.

Coulda Just Used Fusion Tables

The truth is, I could have used only Fusion Tables. The number of donations within the district turned out to be so small -- 69 in total -- I could have simply uploaded the donations into Fusion Tables, letting it do the geocoding and the drawing of points and the district shape.

Then it's just a matter of clicking on every dot within the pink lines, adding up the donations in each bubble along the way.

Instead, I've created a process to do more complicated inside-an-area calculations. And to help others do them, too.

Be Sure to Vote (and Consider Me)

Last Day to Vote! Thursday, October 13 is the last day you can your vote for the ONA Board. Check your inbox for an reminder email sent Wednesday at 11 a.m. ET titled "Deadline is tomorrow -- vote for the 2010 ONA Board of Directors." It has your personal ballot login details. Then go vote!

I'm up for election, and my opponents are awesome.

The race is to fill seats on the board of the Online News Association, an important organization at the intersection of journalism and technology

And while the slate of candidates is crazy strong, I still think I'd be a great addition to ONA's board. So here are some John Keefe Bullet Points to help ONA members consider a vote for me:

  • Planner and doer
  • Super collaborative
  • Public media news director
  • Data-news MacGyver
  • Journohacker
  • Share what I learn so others might, too

I'm especially committed to that last point, working hard to explain our data-news projects in ways other journalists can replicate and build upon. For evidence, just look anywhere else on this blog.

It's journalism in the public interest, infused with the spirit of open-source coding. This isn't unique to me; there are several fantastic teams committed to this. But it is exactly the commitment to propelling journalists, in open and accessible ways, that I would bring to the ONA board.

My full bio and ONA vision statement -- and those of my slatemates -- is right here.

Voting begins Friday, September 23.

Making the NYC Evacuation Map

A couple of years ago, I had our WNYC engineers use a plotter to print out this huge evacuation map PDF. Seemed like a good thing for the disaster-planning file. Just in case.

Then, back in June of this year, I was browsing the NYC DataMine (like you do), and realized New York City had posted a shapefile for the colored zones on that map.

UPDATE (Feb. 11, 2012): NYC has nicely revamped the DataMine since the summer Irene struck -- even mapping geographic files like this right in the browser. But it's actually tricker to find the shapefiles now. Here's the hurricane zones dataset. Click "About" and scroll down to "Attachments" for the .zip file containing the shapefiles. Or just use this shortcut.

I knew I could use the shapefile to make a zoomable Google map -- which would be a heckuvalot easier to use than the PDF. So I imported the shapefile into a Google fusion table. (It's super easy to do; check out this step-by-step guide.) Next, I added that table as a layer in a Google Map and tacked on an address finder I'd developed for WNYC's census maps.

Then I tucked the code away on my computer. Just in case.

Fast-forward to Thursday morning, as Irene approached. On the subway in to work, I polished the map and added a color key. It was up on WNYC.org by midmorning, long before the Mayor ordered an evacuation of Zone A.

When the order was announced, I used another fusion table to add evacuation center locations, updating that list with info from New York City's Chief Digital Officer Rachel Sterne. (The dots are gone now, since the sites are closed.)

I'm not at liberty to reveal traffic numbers, but the site where we host our maps received, um, a lot more views than it usually does. By orders of magnitude. Huge props to the WNYC.org digital team for keeping the servers alive.

Tracking a Hurricane

As Hurricane Irene was approaching Puerto Rico, I noticed that the National Hurricane Center posts mapping layers for each element in their storm-track forecast maps.

Since their zoomable maps aren't embeddable, I made one that is. Feel free to use it:

Right now, I'm manually updating the map with new layers as they are issued, every three hours. I'm pretty close to having a script ready to handle that for me, based on information in the storm's RSS feed.

In the process of building this map, I learned how to use "listeners" to dictate the order the layers are rendered. For anyone trying to work that out, here's the code for how I did it.

Mapping Campaign Contributions on the Fly

Our new Empire Blog reporter, Colby Hamilton, dropped by my desk the other day wondering whether we could map contributions to presumptive NYC mayoral candidates by zip code.

He was going to post about it after lunch. I said I'd be ready with a map.

Thanks to Fusion Tables and a little Ruby magic, I had one ready when his story was done shortly after lunch, and we updated it into the evening as the candidates' filings were made available by the NYC Campaign Finance Board.

How I Did It

For anyone looking to do something similar, here's what I did:

-- I downloaded each candidate's donation as an Excel spreadsheet from the homepage of the Campaign Finance Board.

-- I uploaded the spreadsheet to Google Fusion Tables (if it's an Excel file more than 1MB, you have to save it as comma-separated-values, or .CSV, before uploading).

-- I used Fusion Tables' fantastic aggregattion function -- View -> Aggregate -- to sum the contributions by zip code. Then I exported that file as a .CSV, which gave me a file for each candidate with the columns: ZIP, SUM and COUNT -- SUM being the total donations and COUNT the number of donations for the zip code.

-- I re-uploaded that aggregation export back to Fusion Tables. (If anyone knows how to save an aggregation in FT without exporting it and uploading it again, I'm all ears.)

-- Now that I had the contributions by zip code, I need the zip code shapes to go with them. The US Census has zip code shapefiles by state FIPS code, and for the entire United States. (Quick note: Census zip code data and US Postal Service zip codes aren't exactly the same, though we felt comfortable using the Census version for this project.)

-- I uploaded the New York state zip code shapefile to Fusion Tables, too, using Shpescape. (If you're working with New York State, you can save some work and just use mine.)

-- I opened the ZIP-SUM-COUNT file in Fusion Tables and merged it with the zip code shapefile, linking them with the ZIP field in the first file and ZTCA5CE10 in the second file.

-- Using Visualize -> Map, I could see all of the relevant zip codes for that candidate. By using the Configure Styles link, and then tinkering with Fill Color -> Buckets settings, I shaded the map according to total donations.

This map is ready to be embeded!

The Trouble with Tables

An admission, though: I didn't use the Fusion Tables embeddable map for this story. I did share the FT map with Colby, which let us know we had a couple of good stories. FT is great and fast for that. It also works in production with smaller data sets.

But the long time it takes Fusion Tables to populate a map with large data sets can make for a frustrating user experience. That's compounded by the fact there's no way (yet) to "listen" for a sign that the layer has fully loaded, which would let me display a "Please wait ..." sign until it did.

So in this case, I built my own KML, or Keyhole Markup Language, file (5 of them, actually; one for each candidate). I then compressed those files in to much smaller KMZ files, which are just "zipped" KML files, so they load quickly. I then used those files as layers with Google Maps' KmlLayer() constructor. I also used a "listener" to find out when the layer is fully loaded, and display an alert to the user until it is.

More to Come

As for how I built the KML file, I'm going to share that in another post once I clean up the Ruby code I used to automate the process. (If your project can't wait for that post, drop me a note and I'll try to help.)

But the basics are these:

1. The layout of a KML file, and the format for using different styles to color different shapes, is pretty straightforward and nicely documented. In my code, I changed the style name for a given shape based on the value of the "SUM" variable.

2. The hardest part of writing a KML file is defining each shape in the proper format. But the merged file I made linking the ZIP-SUM-COUNT data and the shapefile actually has that information! The "geometry" column of that table is straight KML! (Thank you, Shpescape.) Export that merged file as a .CSV, and you've got all of the building blocks for your map.

If you have ideas, improvements or questions about this post, please don't hesitate to drop a note in the comments or drop me a note via email.