Visualization for the Masses: Information Graphics and the New York Times

He explained how a 30-person team creates the impressive infographics and visualizations we see on the newspaper every week.

information r/evolution movie

This video explores the changes in the way we find, store, create, critique & share information, a nice video illustration of some of Shirkey's essays.

demographics by ZIP Code - ZIPskinny

Colorful visualization comparing demographic attributes of zip codes.

Have you run into this problem: you have a list of phone numbers and associated values which would be best shown geographically to see patterns, but there isn't a clear way to put the data on a map. Maybe you'd like to see a map of customer service calls by call duration or inbound sales by average order size.

I wanted to share how to MacGyver a solution with a piece of twine, bubble gum, Excel, and a free online map tool. To me, this is a nice testament to the simple but powerful data visualizations that can be accomplished without programming skills or expensive applications.

1. Pull out area codes

First I pulled the area codes from my list of phone numbers using the formula below. This simply checks if the phone number starts with 1, then grabs the appropriate three digits for the area code.

=VALUE(IF(LEFT(E7,1)="1",MID(E7,2,3),MID(E7,1,3)))

2. Convert area codes into states

For my purposes, mapping the phone numbers by state was sufficient. Ideally, we would map the phone numbers to precise latitude and longitude coordinates by doing a reverse lookup of addresses then using the Excel geocoding tool.

First I needed a lookup table that could link my list of area codes to states. I wasn't able to track down a good data table, so I grabbed the data from All Area Codes and cleaned it up. Here is a lookup table of area codes by state.

An aside: I have a pet peeve with people who sell data that feels like it should be publicly available. You'll run across these businesses when looking for basic information about ZIP codes, MSAs, or area codes. Here is an example of one of these parasitic businesses.

3. Create your summary data set

I used a pivot table to summarize metrics by state.

4. Create colorized map of the US

Our friend Ducky Sherwood has generously put together a online tool called Mapeteria that will generate a colorized overlay of US states. In Ducky's words: "Want to make a choropleth thematic map (i.e. coloured based on your data) for Canadian provinces, U.S. states, or French départements?" This overlay can be viewed in either Google Maps or Google Earth.

Here's where it gets a little tricky. You will need to provide Mapeteria with a URL to a properly structured CSV file. Posting a CSV file to a web server isn't trivial if you aren't running your own web site. I found one free service called FileDEN that did the job (other suggestions?). Beware all the advertising—and in all likelihood they immediately sold my e-mail address at registration. Nevertheless, you can upload a file here and it will give you a URL which can be used to create your map.

Here's an example of the results:

Modest Maps

Modest Maps is a BSD-licensed display and interaction library for tile-based maps in Adobe Flash 7+, written in ActionScript 2.0.

glTail.rb - realtime logfile visualization

We were recently asked to answer some questions about our usage of Google Apps. We’re writing up some business-prosey answers, but poetry is another way of capturing the experience. Here are the questions and our answers in loose haiku.

1) Where are you located?

In Herndon, VA
Beside the flowing traffic
Grove Street, 555

2) What does your business do?

What are these numbers?
Sea of corporate data
Juice is your life raft

3) How many people do you employ?

The cat leaps, clawing
The coiled bird escapes
Seven feathers fall

4) Who are your main competitors?

Thundering feet pound
Yet by the rippling puddle
The mammal sips uncaring

5) Why did you decide to use Google Apps, and why did you choose Google over other commercial or opensource alternatives?

Spring air warms the tree
Talking Heads song can't fight
O2 for free

6) What products did it replace and why?

Old friends whither
In spring, new shoots grow
Excellence in change

7) Are you using the Standard (free) version or Premier (paid) version and why?

The raven's keen eye
Gathers all he needs
He has no wallet

8) Which applications do you use (Gmail, Talk, Calendar, Docs and Spreadsheets, Page Creator….All?) . Which ones give you the most benefit?

Star, thread, search
Dinner for seven at seven
Featureful sunrise

9) How many people are using Google Apps and how?

Does the happy frog count
Beside the spring bullrushes
How many croaks he hears?

10) What benefits have you derived from using the Google Apps? (quantifiable benefits if available)

Deep frozen roots
Towering tree, branches drooping 
A nut in the snow

11) What features of the product do you appreciate most and why?

Was it you or me?
Making rash changes
Revision history

12) What’s been the overall impact of using the Google Apps?

Hive mind emerges
Cicadia-like, a boon?
or a pest?

13) Any advice you’d give others in implementing and using Google Apps?

Internet down?
Keep a chair warm
At local Starbucks

14) Are you using any other Google applications such as Maps? AdWords? AdSense? Please elaborate.

Reroute my route? Cool!
Every trip now includes
A stop at IKEA

15) What improvements would you like to see in Google Apps that would benefit your business?

A shopping list
is useful, but PivotTables
sparkle in sunshine

Care to share your experience with Google Apps? We'll highlight the best haiku in a later post.