Today our education reporter had a bunch of data from New York State she was trying to match to schools in New York City. But the school codes used by the two governments look radically different.For example, PS 15 on the Lower East Side is known to the state as 310100010015; the city calls it 01M015.I once made a nifty formula to make the conversion(!), but a more straightforward and official approach involves the Excel spreadsheet found here. It lists all of the city schools, along with their addresses, various codes, and more. For a data cruncher, that's a secret decoder ring.What made me smile was that the only reason I knew this document even existed was because of a little prototype I tried during the first swine flu outbreak. That experiment wasn't robust enough to make it beyond this blog, but it taught me a lot ... including where to find this ring!
(tip via Nate Westheimer @innonate)
Simple: Non-geeks can absorb it within a few secondsAnd I'll add one more:
Unexpected: It fills a gap in our knowledge
Concrete: It takes advantage of our senses and understandings
Credible: It is journalistically sound, from a trusted source, without bias
Emotional: It hits you in the chest, you feel the data
Story: It tells one
Relevant: It is timely, current and usefulGot examples that ring all seven bells? Maybe even four? Share them in the comments here or email me: john (at) designAgitator.com.-----The map detail above is from my favorite example at the moment, which is on Slate. Jump over there, take a look, and then run it through the checklist:
Simple? Once you know that blue is jobs gained and red is jobs lost, you're set. Just press play.Rings my bell.
Unexpected? Seriously so. The speed of change is amazing.
Concrete? The familiar map orients me at a glance; I respond quickly to the circle sizes, colors and densities.
Credible? Bureau of Labor Statistics, Slate.
Emotional? Oh yeah. I saw someone actually shiver while watching it.
NY terror-plot suspects indictedNone of this 140-character stuff. Better to use just five words; seven max. (I used a nonessential adjective clause once. Lost everyone by the second comma.)
Media banned from covering Iran protestsAnd I know where you are, no fancy GPS required.
Building collapse on Reade Street, up aheadEven if it's partly cloudy in the Bronx, I am absolutely certain you're in a downpour.
This rain ends by eveningUser customization? Easy. I can sense you're in line for the Holland Tunnel on your evening commute home. So how about a little news about your governor and his chief rival?
Corzine, Christie speak to biz group toniteIt's tempting to simply repurpose our tweets or web headlines, feeding them automatically to the sign. But it's also clear that wouldn't be as special. Or impactful. Or memorable. So I've been recrafting our material specifically for my particular version of a hyperlocal, mobile user.I've been doing this for a few weeks as a prototype, and soon WNYC's editors, producers and hosts will feed lines to the sign. What I've learned by writing -- and watching -- those little red words will help our staff craft the phrases that catch your eye as you zip by.
1) Queens and Brooklyn schools had much lower attendance rates than Manhattan and Staten Island schools.2) Teens skip school on nice May days.No. 2 is apparent because almost every red square is a high school, which have notoriously low rates this time of year. For a better indication of potentially flu-related absences, I'd chart the difference between these absentee rates and a typical May day at each school ... which is info I don't have. Yet.Initially I published this in Google Maps, which was interactive and allowed you to click on schools for specific info. But Google Maps only plotted about 100 or so schools, and there are more than 1,000 here. Instead, I did it in Google Earth on my own computer and took a snapshot. Here's another.Kinda cool. Was fun to do.Next!----------Anatomy of the process:Daily absentee data from the school system is here.
An Excel spreadsheet with general data on each school is here.
I crossed these two data sets in Access to match school numbers with addresses.
I got the latitude and longitude for each address, in 500-line batches, here.
I spent a lot of time learning about KML files, writing them, failing, trying again.
I made colored icons in Photoshop, and used Excel to assign each school the correct icon.
I put all of the relevant data into one spreadsheet and fed it to this little helper ...
Which gave me this KML file ...
Which I fed to Google Earth, running on my Mac.