Sorting a data set helps answer a basic question journalists like to ask: “Which ____ has the highest (or lowest) ______?”
Excel (and other spreadsheets such as the open source Calc) make sorting data easy. In fact, I often make sorting my first step when “interviewing” data because it quickly reveals high and low values and often highlights some that may seem questionable.
Let’s work through a simple sort in Excel. I’ll be using Excel 2007, but older versions have similar functions. Start by downloading the file “sorting.xls” and saving it to your computer. Open it and follow along:
1. We have a table of Census data from the 2006-2008 American Community Survey. It shows the median age of the population for each of 79 school districts in Virginia plus the state itself.
We want to know which district has the oldest and youngest populations. Let’s sort it!
2. Click once on one cell anywhere in the table. This will help Excel auto-discover your table in the next step.
(more…)
Filed under: Analysis, Excel by Anthony on May 12, 2010
3 Comments »
Good news for our USA TODAY team that researched, reported and wrote the “Trouble on the Tray” series on school lunch safety: The Education Writers Association yesterday named it a winner in the 2009 National Awards for Education Reporting. The series — reported by Blake Morrison, Peter Eisler and Elizabeth Weise with data analysis by yours truly — received first prize in the “Large Media — Investigative Reporting” category.
I’m giving a talk this week at the IRE Computer Assisted Reporting conference on how we acquired and analyzed the federal data that helped fuel the story.
Major stories in the series include:
– Schools in the dark about tainted lunches
– Why a recall of tainted beef didn’t include school lunches
– Fast-food standards for meat top those for school lunches
– 26,500 school cafeterias lack required inspections
Our series spurred congressional calls for reforms to USDA policies, and in February the agency announced tighter requirements on companies that supply food to the National School Lunch Program, including stricter testing of meat.
Filed under: Analysis by Anthony on Mar 11, 2010
No Comments »
A reporter called recently for tips on setting up “a CAR desk” in the newsroom of a decent-sized community newspaper. The editor had watched the reporter’s success at gathering and analyzing data and, as typically happens, now wanted the reporter to train the rest of the newsroom.
Here was my advice:
Focus on a few: Instead of holding building-wide Excel classes or database journalism seminars, start with just one or two reporters who show a combination of interest and decent technical smarts. That lets you go deep on a couple of beats rather than spread yourself thin. Also, success breeds success. Watching a few reporters land great stories will possibly spur interest from others.
Have the right goals: Goals like “publish one CAR story a week” miss the point. Better objectives are to have data-thinking ever present in the reporter’s mind, have the reporter well-versed in her beat’s data sources, and have the reporter develop basic data skills. From that, stories will flow.
Inventory data: Speaking of data sources, have each reporter you work with find out the sets of data local governments keep. File FOIA requests for table layouts and database schemas. Get the data, then study it. That will spur story ideas.
Crawl first, run later: All the hot talk in data journalism these days is on Web frameworks and visualizations, but there’s plenty of work for the beginner in the land of Excel and Access. Build those skills as a starting point.
Your thoughts? Add a comment below …
Filed under: Analysis, Journalism, Workflow by Anthony on Feb 6, 2010
2 Comments »
