John Nelson’s infographic, "Five Years of Traffic Fatalities," comprises charts and maps made with little more than Excel spreadsheets.

Alcohol fatalities occur heavily during the early morning hours, presumably after a night of drinking.

Here, the pronounced blue bow for the pedestrian-related accidents shows how later sunsets in the summertime push those types of incidents back a few hours. Meanwhile, pedestrians appear to be struck most often after work, rather than before it.

The charts prove that sometimes the simplest tools make for the best data visualizations.

Even the maps were made using pivot tables in Excel. Nelson outlines the technique here.

"When your data is telling its story well that is when it’s time to stop," Nelson says. "Who cares what software you are using at the time?"

Infographic: Showing When People Get Killed In Cars, Using Excel

John Nelson’s charts and maps on traffic fatalities are a reminder that a good data viz doesn’t always require graphic design chops--or software.

With the abundance of data visualization projects we’ve seen over the last few years, it’s hardly a surprise that some have ended up putting more emphasis on the "viz" than the data they’re supposed to be conveying. Which is why this infographic, showing a series of charts and maps covering various aspects of traffic fatalities over the last five years, is something of a breath of fresh air. The designer, John Nelson, managed to tease a few insights out of the massive data set--some more expected than others--using little more than an Excel spreadsheet.

Every year, the National Highway Traffic Safety Administration releases data for every automobile crash that resulted in a fatality. John Nelson, a designer at the Michigan-based data software company IDV Solutions, wanted to see what patterns he could find over a slightly longer period of time, so he downloaded five years’ worth of data and dumped it into Excel. After twisting and turning the data a bit with the help of some pivot tables, this infographic is what he came up with.

Click to enlarge

The first set of graphics show accidents by time of the day and day of the week, bearing out a few common-sense conclusions: There’s a higher percentage of alcohol-related accidents after midnight, when people are leaving bars; accidents involving pedestrians occur most frequently after dusk; and accidents where weather is a factor tend to happen from 5 to 8 o’clock in the morning, when conditions like fog and ice cause trouble when people are driving to work.

The charts that show the data by month-of-the-year bring out some less obvious insights. The one for accidents involving pedestrians shows a pronounced blue bow around summertime--a visual representation of the fact that the sun sets later here in the summer, pushing those dusk-related injuries back an hour or two. "I’m always on the lookout for structure within data sets," Nelson says, "so a pattern this stark caught me by surprise." But to Nelson, that simple, pixelated curve is representative of even bigger concepts--a clear visual signpost pointing us to a confluence of factors.

Click to enlarge

"This is the signature of a perpetual rhythm of Earth rotating around the sun," he pointed out, "producing growing and shrinking spans of daylight that manifests in the most abrupt and unplanned sort of human events. It’s one of the clearest illustrations of environmental conditions determining to a large degree what we think of as erratic and isolated human activity."

The next graphics are a set of maps that show accidents by location--these, too, created only with a spreadsheet. Nelson wrote a quick how-to on his company’s blog, showing how any data set with longitude and latitude can be turned into a simple map, right in Excel. He’s "pretty excited" about the technique, he told me. "I’m all for map democratization … This method has me up and running with a map in about 30 seconds. Plus it looks like my mom’s cross stitch patterns from when I was young and I get nostalgic about them."

Nelson’s visuals admittedly require a bit more work, on the part of the viewer, than some of the prevailing styles of data viz. But ultimately, the designer says, those that truly engage the viewer are often the most rewarding. "I try to make graphics that take folks some of the way but leave a little bit of the work for them," he says. "Visuals that generate their own questions tend to be more interesting to me than rote statements."

Add New Comment

7 Comments

  • CM

    There are several typos in the article; you really should proofread more thoroughly before publishing.

     "why this infogrpahic"
    " showin a series of "
    "This method has me up a running"

  • QRN

    Zing!  A small voice in my head says, "Infographic of the Obvious." I think the one great omission in this infographic is the inclusion of population density, which has me seriously wondering about all those drunk drivers in Montana, Idaho, the Dakotas, and along the Canadian border.  (Oh man, I'm beginning to sound like a jerk.)

  • Elizabeth

    I hate to go down this road as well, but "infogrpahic, showin" is misspelled in the first paragraph of the article.

  • John Nelson

    I should start saying that I insert one misspelled word per graphic intentionally...as a social experiment.  But I dno't thikn anybdoy would buy taht.