tag:johnkeefe.net,2013:/posts johnkeefe.net 2014-12-14T12:39:09Z John Keefe tag:johnkeefe.net,2013:Post/781957 2014-12-11T19:23:52Z 2014-12-11T19:31:51Z Drinking and Soldering Yes, those are the secrets to happiness. 

(Well, at least the soldering part.)

I was fortunate and mildly terrified to explain all of this before a group of ridiculously smart people in November at the Newsgeist conference run by the Knight Foundation and Google at the Cronkite School of Journalism in Phoenix.

(The "Institute of Higher Learning" mentioned here is the New School's Journalism + Design Program, where I'm lucky to be an instructor.)

]]>
John Keefe
tag:johnkeefe.net,2013:Post/735762 2014-09-03T04:16:29Z 2014-09-03T04:16:29Z Make, Map, Blink: A Cooking Class

Starting this week, I'm teaching Make, Map, Blink, a course at the New School university in Manhattan. It's an evening of cooking up data-driven projects -- both on the table and on the screen.

The course is a little quirky in a few ways, including that any New School student can attend: It's held in the cafeteria in the Eugene Lang building every Wednesday night at 7 p.m.

For those of you who can't attend (or aren't New School students), all of the course material, code and slides are posted in this Github repository. A link there also will sit on the left rail of this blog.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/717076 2014-07-30T23:22:22Z 2014-07-30T23:22:23Z "Live Blogging" Daddy Robot Camp

Welcome robots! I'm leading my daughters and a friend through some summer fun building simple robots.

This is live prototyping at its finest (by all of us). I'm tweaking the hardware and software by night, and running "camp" at the kitchen table by day.

The main learning concept I'm aiming for: "If A is detected then B happens," like IFTTT does so well. It seems to be a good, base robot function. Also: Making robots is fun.

My hope is that the kids get to express hands-on creativity, and that I can get Arduino to help me bring their creations alive. As Liza Stark advised me, make sure they have their hands on the project more than I do. Let's see if that happens.

I'll keep posting here as we work through the week. The fun begins today!

The Plans

Given a set of "if" sensors (light, temperature, movement, distance, buttons) and a set of "then" actions (LEDs light, servos rotate), the girls each came up with a plan for a robot:

Read more »

]]>
John Keefe
tag:johnkeefe.net,2013:Post/681910 2014-04-25T02:22:04Z 2014-04-25T02:23:09Z Extinguishable LED Candles

We love Blinkies. Especially simple led throwies.

Lately, we've been playing with "candle flicker" LEDs, which contain a tiny chip that makes their warm light flicker like a flame.

Tape 'em to a battery and watch glow. They're great. But we wanted a version we could, essentially, blow out.

Enter the tilt switch.

This little component is a small cylinder, about the size of a bean, with a metal ball inside. When the cylinder is vertical, the ball rests on two metal leads at the bottom, completing a circuit. Tip it, and the ball rolls away, breaking the circuit.

So we combined tilt switches, flicker LEDs, coin batteries and some plastic battery holders to make 15 little candles.

Read more »

]]>
John Keefe
tag:johnkeefe.net,2013:Post/673139 2014-04-04T21:13:51Z 2014-04-04T21:13:51Z A Child's Guide to Object-Oriented Programming Someone recently reminded me about this Ignite talk about how my daughter taught me to understand object-oriented programming. I never posted it myself ... until now!

]]>
John Keefe
tag:johnkeefe.net,2013:Post/660387 2014-03-04T05:19:08Z 2014-03-04T05:22:43Z Cooking with Hardware

The amazing Liza Stark and I had the honor of showing data journalists some of our hardware hackery at the NICAR conference in Baltimore last week.

For a rundown of the things we presented, and our ongoing creations, visit the Team Blinky Tumblr!

]]>
John Keefe
tag:johnkeefe.net,2013:Post/658611 2014-02-27T19:51:57Z 2014-03-01T16:56:42Z The Chartbeat LED Bar

We're always interested in how many folks are viewing our WNYC Data News projects at a given moment, and Chartbeat answers that question. But we don't always want to watch Chartbeat's dashboard for the latest info.

Enter the blinkies.

Using a string of colored LEDs, an Arduino and a little bit of code, we now have an ambient indicator that generally reflects our traffic and alerts us when thing get intense. A program running on my desktop computer checks Chartbeat for the latest number of simultaneous and passes that information across the room using Bluetooth, something I've wanted to play with for a while. 

Read more »

]]>
John Keefe
tag:johnkeefe.net,2013:Post/657483 2014-02-24T01:27:38Z 2014-02-24T01:37:10Z Making a Heartbeat Hoodie

I can wear my heartbeat on my sleeve.

Actually, I meant to put it on my sleeve, but turns out that sewing something inside of a sleeve is colossally frustrating. So it's along the front zipper.

The heartbeat hoodie is a blend of two things I wanted to play with: Soft circuits and consumer heartbeat monitors. I first made the basic LED hoodie, and later added on the heartbeat feature. And since the basic LED hoodie is easy and fun in its own right, I'll describe how I made that first, followed by the heartbeat addition.

Read more »

]]>
John Keefe
tag:johnkeefe.net,2013:Post/653498 2014-02-16T01:25:23Z 2014-03-01T18:17:54Z The Monthly Mood Cube

When Kristin is about to get her period, everyone in the house knows it.

A light on her nightstand tells us so.

What we now call the "Mood Cube" tracks my wife's cycles and has helped foster household harmony. It also makes Kristin smile every day.

It was easy to build. You can make one, too.

The backstory

The Mood Cube story really starts with Louise Ma, a great interaction designer and my colleague on the WNYC Data News Team. She knows her mood tracks closely with her menstrual cycle, so she put up a chart of different faces and hangs a rubber band on the face that matches her feelings. 

Stop by Louise's desk for a chat, and you immediately know where she's at. Talk about transparency!

Louise has made a hobby out of tracking her moods and cycles. Kristin had tried to track hers, too. She used several of the flowery iPhone apps designed to help, but didn't stick to them. She put the dates in Google calendar, but they never really lined up. 

And each month she was surprised by bouts of intense stress, frustration and agita -- always followed by her period just a day or two later. After a recent episode she texted me: "I want Louise's chart!"

I had another idea.

I wanted to make an ambient indicator -- something in Kristin's life that was subtle but clear. I wanted it to be peaceful, friendly and needinig no attention. And it shouldn't be harsh or shaming.

Read more »

]]>
John Keefe
tag:johnkeefe.net,2013:Post/634663 2013-12-26T04:32:21Z 2013-12-26T04:37:54Z LED Snowflake Ornament

Challenge to myself: Build an ornament for the Christmas tree in the few hours before Christmas Eve dinner.

I grabbed an Arduino the size of a coin I've been playing with called a "TinyDuino," from Tiny-Circuits, along with a little stash of LEDs I got from from Evil Mad Scientist.

Using the TinyDuino's prototyping board, I decided to solder the positive (long) ends of six LED's into the board so they radiated around it like this:

Read more »

]]>
John Keefe
tag:johnkeefe.net,2013:Post/607016 2013-11-05T15:17:54Z 2014-12-14T12:39:09Z Daddy-Daughters Project: Building a Minecraft Computer To play Minecraft for real, we needed a new computer. So we decided to build one.

It should take a week or two. And with any luck, it'll cost less than $300.

For several months now, my daughters -- ages 8 and 10 -- have wanted to play the computer version of Minecraft. The computer version is far superior to the pocket version they've been playing, which, among other things, has no wolves, horses or dragons.

Buying a whole computer just to play the game didn't seem, well, appropriate. But making one? That we could do. 

So we're on our way. And I'll update this post en route to document our progress.

Episode One: The parts

Poking around the internet, I found the hardware requirements for Minecraft. A little more fishing landed this Lifehacker article about making your own PC. It included links to an entire computer-building lesson series and PC Part Picker, a service that helps you buy your parts and ensure they're compatible. 

Here's our parts list. It's based on the original Lifehacker article, minus the optical drive (we won't need it) and plus a wifi card (we will need that). We also upgraded the processor just a tad.

The first component to arrive, symbolically, was the shell into which we'll put the rest of the parts -- once they show up!

All the parts have finally arrived! Let the building begin.

Read more »

]]> John Keefe tag:johnkeefe.net,2013:Post/481369 2013-03-11T17:10:00Z 2013-10-08T17:04:31Z SXSW Talk on Sensors & Journalism At SXSW 2013 in Austin, I'm moderating and speaking on a panel looking at how sensors can play a role in journalism called "Sensoring the News: Detector-Driven Journalsm."

Joining me on the panel are:

Sarah Williams, Assistant Professor and Director the Civic Data Design Project at MIT. Sarah's slides are here.

Nadav Aharony, co-founder and CEO at Behavio.

Matt Waite, Professor and head of the Drone Journalism Lab at the University of Nebraska-Lincoln. Matt's video presentation is here.

And my slides are here.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481373 2013-03-01T17:43:00Z 2013-10-08T17:04:31Z Talk: Data News on the Fly Today I'm talking about how the WNYC Data News team has done some quick-turnaround projects, especially around Sandy, at the IRE Computer Assisted Reporting conference

Here are the slides for my presentation, including links I reference. 

I also wrote a quick outline about our thinking on how we could best serve our audience during the storm, called "Predicting Questions, Building Answers."

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481374 2012-12-18T04:03:00Z 2013-10-08T17:04:31Z My Bus-to-Speech Experiment One of my "show don't tell" projects was mentioned this month in a Harvard Business Review article by Tom Kelley and David Kelley of IDEO. For more about the experiment -- and to give it a try -- jump over to this post.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481377 2012-11-26T23:38:00Z 2013-10-08T17:04:31Z Designing a Course in Data Journalism More data journalism classes are on the way, at little schools and big ones. I'm hearing this through the grapevine. Several grapevines today, in fact. And I think it's fantastic.

When asked what such a course might look like, I point folks to Brian Boyer's theoretical and amazing Hacker Journalism 101. Journalism needs more journalists who can code, and this is a great way to get there.

That's programming, though, you say. We can't teach programming in a journalism class!

You can, and you should. Basic programming will help journalists understand and deal with data used by cities, cops, politicians, agencies, campaigns, companies, banks, stores, non-profits, advocacy groups and just about any other source you can think of.

Knowing how to code, even a little, is like having a solar calculator for that database you just scored.

In addition to programming, here are some of my favorite topics for classes, readings or workshops:

  • Finding data for your stories
  • Finding stories in your data
  • How to tell one story well
  • All data is dirty ... and what to do about that
  • Basic stats
  • Percentage points for journalists
  • Mapmaking made easy
  • Lying and truthing with easily-made maps
  • When maps shouldn't be maps
  • Basic chartbuilding
  • Lying and truthing with charts and graphs
  • Did I mention programming?

And I'd have 'em code something. Every week.

What have I left out? Add comments below and I'll update this post -- and my advice to others.

Photo: My daughters in a UW-Madison lecture hall where I studied geography. Both of them have dabbled in coding.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481381 2012-11-17T18:51:00Z 2014-02-24T01:29:09Z Real-time Data Journalism While preparing for the real-time challenge of Election night, the WNYC Data News Team -- and the entire city -- turned its attention to an oncoming storm.

For our Hurricane Sandy coverage, we quickly built and maintained several data projects to help convey information people needed. All used open, public data and several were updated regularly -- either automatically or by hand.

Our projects included:

  • The evacuation map above, built using public shapefiles from New York City's Department of Emergency Management.
  • A storm-surge map for the entire New Jersey and New York coastlines, stitched together from a variety of U.S. Army Corps of Engineers shapefiles.
  • Hurricane Tracker to watch the storm's forecast track and its radar echo, fed by four real-time feeds from the National Weather Service. 
  • Transit Tracker with the latest information about several public transportaiton systems, driven by a Google spreadsheet updated by a half-dozen producers and reporters from transportation agency tweets, websites and public announcements.
  • A live flood-gauge map showing where the water was rising, driven by a real-time feed from the National Weather Service.
  • traffic map for the back-to-work crush sans subways, fed live by the Google Maps traffic layer.
  • A subway-restoration map, updated several times a day with new maps issued by the city transit agency.

For details on the data, follow the source links on each project.

With more time, we would have worked more on the aethetics. But time wasn't something we had much of, so we did our best to be accurate and clear given the resources available.

Read more about the WNYC Data News team's thinking behind our Sandy coverage

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481386 2012-10-02T15:23:00Z 2013-10-08T17:04:32Z Counting the Jay-Z subway crowd Saturday morning we did something fun: We counted the number of people who took the subway to the opening-night Jay-Z concert at Brooklyns new Barclays Center the night before.

Or at least got pretty close.

Traffic and transit were closely watched for the new arena, as it the 19,000 or so concertgoers would have just 541 parking spaces. So we decided to grab data from subway turnstiles to measure the crowds leaving the Atlantic Ave-Barclays Center station for the show.

How we did it

Turning around the data overnight took a little planning. Here's how we pulled it off:

Every Saturday morning, the MTA posts turnstile data for the previous week. Fortunately for us, the last reading is 8 p.m. Friday, the scheduled start time for the concert.

The data files contain the entry and exit counter readings for each turnstile in the system as a sort of "odometer" reading. The data is a little tricky to use, though it does have a regular structure.

So Steve Melendez, our Data News Team programmer, wrote some Python code that grabs the data files and puts the individual readings into a SQLite database. He then sorted the readings by station (using this chart), and calculated how many exit clicks were logged for the Atlantic Avenue station from 4 p.m. to 8 p.m.

We suspected there would be a jump in the time period before the concert. So earlier in the week, we ran the numbers for each Friday for much of the year and calculated those averages (I ended up using just September, because they were higher, post-summer vacation readings). Then, Saturday morning, Steven got up really early Saturday and ran the program again, including the newly posted numbers.

He sent me the latest values, and I added them to the chart in a taxi on the way to the station. At 8:35 a.m., I was on the air talking about how it appears about a third of the concert-goers took the subway.

It could be more: Some people could have left the system at another station. And if anyone left through an emergency exit, or if they showed up after 8 p.m., they wouldn't be in our turnstile data.

But it's a place to start, and we'll be watching how these numbers change for future concerts and for Brooklyn Nets games.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481389 2012-07-26T20:52:00Z 2013-10-08T17:04:32Z Charting Local Olympic Data The WNYC Data News team isn't just about maps. We dig into all kinds of structured data -- and the 2012 Olympics will generate a bunch of it.

There are some great efforts afoot to follow the Games, with the New York Times doing amazing work as always.

Our slice of that effort is the Team NYC Olympian Tracker, which we made to help our audience follow who's competing today, who's in contention for a medal and who's won medals. 

WNYC's Team NYC Olympian Tracker

The entire application fed by a Google Spreadsheet, which is linked to the chart by a cool data tookit called Miso, from The Guardian and Bocoup.  Since we're inthe process of interviewing for designers, we went with a clean and quick design using Bootstrap.

It was a fun project and, just like a map, built to make interesting data easy to use.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481390 2012-07-18T04:36:00Z 2013-10-08T17:04:32Z NYPD Stop & Frisk Data for You This week, we published a map showing total NYPD stop and frisks by block together with locations where guns were discovered during such stops.

In the tradition of showing our work, here's some information about how we built it -- and data you can download and explore yourself.

The Data

The major bumps I hit working with the NYPD's Stop, Question and Frisk data sets were 1) they're in a format I don't know, and 2) the geographic locations aren't in latitudes and longitudes.

For bump #1, I used the free statistical program "R" to convert the NYPD's ".por" files into something I could use. R is also great at handling big data sets, and easily managed the 685,724 stops in the 2011 file.

For bump #2, I noticed that each stop had data fields called "XCOORD" and "YCOORD." A couple of tests confirmed that those values described the stop's position on the New York-Long Island State Plane Coordinate System -- something I've seen in a lot of city data. So I used the free geographic software QGIS to load in the data and convert (technically, reproject) those coordinates into latitudes and longitudes.

And now you can have the data I used to make the map. Just click to download:

stopfrisk2011_databundle_sans_allstops.zip
(4.3MB download, unzips to 12MB)
Contains a shapefile of all NYC blocks with the total stop-and-frisks calculated for each block, a shapefile with the points for all stops where guns were found, raw data on each of the 768 stops where guns were found and notes about each data set. Here's more detail on the contents..

stopfrisk2011_databundle_with_allstops.zip
(51MB download, unzips to 500MB)
This file has of the above and a .csv with the raw data for all 685,724 stops in 2011. While it's in a more common format than what the NYPD provides, it's too big to open in Excel and maxes out the limits for Google Fusion Tables. So you'll need a stats program like R or some database know-how to handle it.

The Map

I built the map using TileMill from Mapbox, which I've been playing with for some months now.

While it's tricker than generating quick maps from Fusion Tables, if you're patient and spend some time with it, you can make some pretty gorgeous maps.

Besides providing wonderful control over styles and colors, TileMill solves an important problem: New York City has roughly 38,500 census blocks -- and loading the data to draw them all onto a Google map will anger any browser. With TileMill, you bake the data into individual image tiles, which get served up to the user as they zoom and pan.

To cover the area of NYC and provide 8 levels of zoom, I pre-cooked 59,095 tiles. But once they're uploaded to the MapBox server, which took about 15 minutes, they load almost instantly.

As always, I welcome comments and questions below or at john (at) johnkeefe.net.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481395 2012-05-29T16:00:00Z 2013-10-08T17:04:32Z Love Design? Join the WNYC Data News Team

Do you want to ...

  Inform the citizens of New York?

  Help people understand their world?

  Root out corruption?

  Make a mark on society?

  Craft beautiful online projects and visualizations?

  ( Like this diversity map, this stop & frisk project and this election tracker? )

WNYC is growing our Data News Team to make high-impact visualizations and projects, and to help WNYC reporters and producers present the facts, expose corruption and explain our world. We've been pioneers in the field of crowdsourcing, data journalism and mapping -- even winning some prestigious awards for our work.

Now we're kicking it up a notch. Like to join us? 

What we have:

  • An award-wining staff of reporters and producers
  • A committed, innovative digital staff
  • A mission to conduct journalism in the public interest
  • Millions of engaged, passionate and active listeners and readers

What you have:

  • A passion for news
  • An attention to detail, a respect for fairness and a hatred of inaccuracy
  • A user-centered approach to exploring information
  • An appreciation for clean lines, clear stories and use of white space
  • A genuine and friendly disposition, and an honest spirit of collaboration
  • A bias toward sharing what you know, and helping others build on it

What you'll do:

  • Huddle with reporters to figure out how we might help their stories with data, design and web technology
  • Work as a team to turn ideas into realities in days or weeks, tops
  • Learn from and build on successes and mistakes along the way
  • Have your work consumed online and talked about on air to millions of New Yorkers

Head over to our official aplication for Interaction Designer and tell us all about you.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481396 2012-05-15T18:30:00Z 2013-10-08T17:04:32Z The Thinking Behind WNYC's Vertical Timeline Making a music stand, my father said, was a great challenge: Even though people had made them for centuries, it was still possible to blend beauty and function in a new way.

In journalism, the same is true for the timeline.

Presenting a chronological story online, beautifully and functionally, has been tricky. There are some great examples, such as the New York Times' chronology of the Iraq war, and the three-dimensional Middle East timeline from The Guardian.

ProPublica built the excellent TimelineSetter to put Times-like timelines in the hands of non-Times journalists, and we used it for a while. But TimelineSetter's horizontal layout got cramped in WNYC's article columns, and we longed for something that fit better.

Working with Balance Media and the WNYC web design team, we kicked around several ideas and settled on a vertical version. As it happened, Facebook's new vertical timeline had come out, inspiring a crop of JavaScript libraries we could work with.

We also decided to dispense with a journalistic convention that represented temporal gaps visually -- making months wider than weeks, for example -- and focus, instead, on seeing the sequence of events at once.

The live version at WNYC is here.

We also went with a center-spine orientation, which give it balance and allows the user to see more items at the same time. And the very cool Isotope code reshuffles the items to fit as they are closed, opened, resorted or even added.

Open to use

Finally we wanted it easy for us -- and you -- to use. So we wired it to Google Spreadsheets, allowing reporters and editors to easily enter and update the information. The wiring there is based on a previous project of ours called Tabletop.js.

And we made the source code openly available and scary-easy to use, and you can start by copying this Google spreadsheet template.

We usually build an HTML page just like the one in the code example, and then use a simple line of HTML to iframe it onto an article page. The only trick is to make sure the iframe is tall enough.

The code is free for non-commercial use; commercial use requires a $25 license fee for Isotope.

We hope folks will use the timeline, and come up with improvements. Let us know about either in the comments below or by writing me at john (at) johnkeefe (dot) net.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481402 2012-02-24T16:39:00Z 2013-10-08T17:04:32Z NICAR 2012 - Links from My Presos I had the honor of presenting at four sessions at the Computer Assisted Reporting conference this week. For those who attended, here are the links I referenced in each session.

If you weren't in St. Louis for the conference, you can still get a sense of what's here. If you see something you want to know more about, let me know. For everyone else's presentations, check out this great list.

Election Night Results & Maps

Ins and Outs of APIs

Election Data Without a Database

Apps Without a Backend CMS (using Google Spreadsheets instead)

Hacking the Census - How we made a Fusion Tables census map

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481406 2012-02-23T04:48:00Z 2013-10-08T17:04:32Z States & Municipalities with Real-Time Election Results Here's my unofficial list of state and local governments that have -- or have provided in the past -- real-time election results. Great opportunities to build your own election-night maps and analysis!

Know of others? Drop me a note at john [at] johnkeefe.net. (Updated as of Feb. 2012.)

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481408 2012-02-04T20:25:00Z 2013-10-08T17:04:32Z The Nevada Vote: In 3-D The Guardian pushed the limits of election-night data display this week with a relief map of the Florida primary vote. 

They didn't push far enough.

As promised: Live election results in True 3-D.

Nevada 3d Still

(To avoid blog lag, I've put the live version here.)

You need a current browser to see it. Recent versions of Chrome and Firefox work. Safari does, too, if you nudge it.

With any luck, the counties shall grow as the vote rolls in tonight.

For those interested, I built it in Processing and use Processing.js to put it on the web. You're welcome to embed it if you wish. Just drop me a note or comment that you did.

UPDATE: My data-fetching code is a little wonky. Refresh the page to ensure the latest results!

UPDATE 2: I actually don't believe this is the best way to present numeric data. Representing numeric scale with a 3D drawing on a 2D surface is exceptionally tricky and should probably be avoided. Also, there are no rollovers or other clarifying information -- like county names and vote counts.

That said, I like the idea that some data sets might be worth spinning, touching and flying through. So maybe this is my first step in that direction.

Plus, it was fun.

UPDATE 3: By request, here is the Processing sketch upon which this was built. 

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481414 2012-02-02T20:12:00Z 2013-10-08T17:04:32Z Steal This Map (or Make One Yourself) For the second time -- the first being Iowa -- WNYC's It's A Free Country has a map that uses free, live election data. 

And that means you can have it, too.

Just use the embed link at the bottom of the map.

And if you want to make your own, check out some tips in my next post.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481418 2012-01-30T04:26:00Z 2013-10-08T17:04:32Z Free, Live Election data: Now's your chance to play

UPDATED in two key spots below.

Election geeks, you are in luck. For the second time, Google plans to offer free, real-time election results, allowing anyone to tinker and play with hard-to-get voting numbers.

It's for the Nevada Republican caucuses this Saturday, February 4, and even if you have no connection to Nevada, it's a chance to experiment with live results like the Big Guys. Make a map. Mash up some data. Have fun.

The first time Google did this, we made this Iowa caucuses results map at WNYC, mashing up Patchwork Nation community types with the live vote tally. And since we've been through it once, I've got some tips and tricks for making your own project.

My only request: Send me a link to whatever you make. I'd love to see it.

Setting the Fusion Table

Updated: The Google folks are providing live tallies from the Nevada GOP in two Fusion Tables -- one by county and one by precinct -- which will be updated with new data throughout the evening. 

This means means you get all of the functionality of those tables, including simple charts and cool maps. Check out these posts to get started with Fusion Tables, if you're not already familiar.

Urge to Merge

My favorite part of Fusion Tables is that you can easily merge (or join, in SQL-speak) two separate tables of data. In this case, you'll be able to merge any data organized by Nevada's 17 counties (one's actually an independent city). Unemployment figures, Social Security recipients and any U.S. Census designation you can think of are just a few of the possibilities.

Updated 11:39 a.m. 2/2/2012: This section originally talked about merging on the county's unique FIPS code -- which turns out to be tricky, since the results table doesn't have those codes. But if your data has the Nevada county names, you can merge using the name as the key (provided they are identical lists in both tables). Or you can add the county names to your data by adding a column and entering them by hand.

For reference, or to map the shapes of the Nevada counties, you can use this table I built merging data from the U.S. Census (which calls the FIPS codes "GEOID10") and the live election data.

No matter how you do it, once merged, you'll end up with a larger table containing all of your mashup data -- unemployment, number of children, etc. -- lined up next to the live vote data. Even though it's a new table, it'll update in real time with the underlying vote table.

Welcome, Json

If you're a JavaScripter, it is super easy to get the data you want from Google's results table, or a merged table you built with it.

First, construct a query url according to the Google Fusion Tables documentation. This can be a little tricky, but with some tinkering you can make it work. Be sure to encode commas, greater-than signs and other symbols. Here's a nifty URL encoder if you need to convert all or part of the URL. Also, surround with single-quotes any column name containing dashes, such as 'VoteCount-Paul'.

For a simple example, take a peek at this "shoes" table. Then try this URL:

https://www.google.com/fusiontables/api/query?
sql=SELECT+Product%2C+Inventory+FROM+274409&jsonCallback=foo

A little decryption here: The + signs are spaces, and the %2C codes are commas. The table number we're looking at is 274409. So the syntax is "SELECT Product, Inventory FROM 274409." Append &jsonCallback=foo and you get back JSON. If you're using a jQuery AJAX call, as you'll see below, make it &jsonCallback=?

You should get a text file that looks like this:

foo({"table":{"cols":["Product","Inventory"],"rows":[["Amber Bead",1.251500558E9],["Black Shoes",356],["White Shoes",100]]}})

Voila! JSON.

To get the statewide total for Iowa, I made a crazylong URL that requests sums of the columns I wanted.

Pro-tip: If you try sorting the data within Fusion Tables using Options->Filter or Options->Aggregate the "query" you're using appears above the results. Use that to help form the URL after the query?sql= part.

Inside the JavaScript map application, I used jQuery's $.getJSON() function to hit that URL and load in the data, and setTimeout() to do it every two minutes. You can see and use the code here.

Try and Learn

If you've ever dreamed of making your own election-night results map, or just like the thrill of a new challenge, don't let this opportunity pass you by. It's lucky that we get a chance to play with free, live and well-structured voting information. And no matter what you learn in the process, I bet it'll be valuable down the road.

Maybe even in November.

As always, don't hesitate to contact me -- or post a comment -- with questions, clarifications and ideas. And if you're inspired to make something for Nevada's primary, definitely drop me a note and a link!

[ Map detail: Patchwork Nation - Votes for Barak Obama in 2008, by county ]

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481424 2012-01-29T06:07:00Z 2013-10-08T17:04:32Z 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.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481426 2011-12-05T02:25:00Z 2013-10-08T17:04:32Z 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.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481431 2011-11-02T05:14:00Z 2013-10-08T17:04:32Z 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.

]]>
John Keefe
tag:johnkeefe.net,2013:Post/481439 2011-10-13T15:57:00Z 2013-10-08T17:04:32Z 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.

]]>
John Keefe