Sorting Data in Excel: Simple Analysis

Sorting a data set helps answer a basic question data analysts 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. Start by downloading the file “sorting.xlsx” 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.


3. On the Excel ribbon, select the “Data” tab and click “Sort.”

Continue…

26,500 school cafeterias uninspected

Thousands of school cafeterias went uninspected in the 2007-08 school year, we report today in the fourth major installment of our “Trouble on the Tray” investigation into school lunch safety.

In today’s story, reporters Blake Morrison and Peter Eisler worked with me to examine data on the number of schools in each state that met a federal requirement to have two cafeteria inspections annually. We found that in eight states, more than half of schools reporting failed to meet that standard in 2006-07 and 2007-08 school years.

Meanwhile, the series continues to draw attention on Capitol Hill. This week, Sen. Kirsten Gillibrand, D-N.Y., called on the federal government to increase its standards for meat used in school lunches and to cut contracts with companies that repeatedly did not meet standards.

When chickens stop laying eggs …

In part three of USA TODAY’s investigation into the quality of government-bought food for school lunches, we examine how its standards for microbial testing of school lunch beef are less stringent than those employed by fast food chains such as McDonald’s, Jack In the Box and KFC.

We also write about “spent hens” — birds that are past their egg-laying prime. Tough and stringy, these old birds typically are turned into pet food or compost. But egg producers struggling to find a market for all of them have had help from the federal government:

From 2001 though the first half of 2009, USA TODAY found, the government spent more than $145 million on spent-hen meat for schools — a total of more than 77 million pounds served in chicken patties and salads. Since 2007, 13.6 million pounds were purchased.

Both stories were heavily informed by analysis of data sets obtained from the USDA under the Freedom of Information Act. They included hundreds of thousands of orders from a federal inventory system and about 150,000 results of microbial tests of beef destined for school lunches.

Prompted in part by our series but also by last week’s recall of beef by a company we identified in Part Two of our series, one lawmaker has called for the government to investigate a supplier to the school lunch program. From another story of ours today:

Rep. Rosa DeLauro, D-Conn., called on the U.S. Department of Agriculture to “undertake a comprehensive examination” of the facility, Beef Packers, to “identify and correct any major problems” before it produces more beef.

To see all the stories in our series thus far, click here.

Project: School lunch safety

Today, my colleagues Blake Morrison, Peter Eisler and I published the second part of our investigation into the safety of food used in the National School Lunch Program. Today’s installment focuses on a California firm that kept receiving government contracts even after  it had been suspended from the program several times — twice because of failure to produce ground beef that was free of salmonella.

When the firm, Beef Packers, recalled beef last summer because of a salmonella outbreak in 11 states, the government decided not to recall beef made for school lunches that the company made around the same time:

The recall, announced by the government Aug. 6, covered only ground beef sent to certain retailers. In the days after it was announced, government and company spokesmen said meat sent to schools was not included. Documents obtained by USA TODAY through the Freedom of Information Act reveal a more complicated story — one that raises questions about whether the government took adequate steps to ensure that meat it bought for schoolchildren during the same period was safe.

To get at the story, we filed FOIA requests for several government data sets. They included the results of hundreds of thousands of microbial tests conducted by the USDA as well as a dump from an inventory system the government uses to track orders for the school lunch program.

Update, 12/7/2009: Morrison and Eisler report that Beef Packers issued its second recall this year for beef tainted with salmonella.

Bookshelf: Numbers in the Newsroom

Never underestimate the value of a compact guide to math, especially if you’re one of those journalists who thought  you could avoid numbers by becoming a writer. You shouldn’t — understanding numbers will help you get stories  others miss because of innumeracy.

One of the handiest resources I’ve found — and recommended just this week to a roomful of colleagues — is Sarah Cohen’s “Numbers in the Newsroom.” It’s a 108-page guide that covers the basics on percent change, rates, graphics, probability and much more. Cohen is a Pulitzer-winning former Washington Post staffer and one-time training director for Investigative Reporters and Editors. She’s now at Duke University, where she is the Knight Professor of the Practice of Journalism and Public Policy.

The book is a few years old, but its lessons are timeless. You can pick it up through IRE’s online store.

Have your own math book recommendations? List them below …