Sort, Filter, Aggregate, Merge

How do you interview a spreadsheet like a human being? Today, we’ll work on turning a spreadsheet into a story, a source, a chart or something else journalistically valuable.

Housekeeping

• Make sure your info is right on the class roster
• Are you sure you’ve signed up for the Google group?
• More introductions

Critique

• Some lucky classmates are leading our discussion about a tricky fire hydrant.

Enough to make you dangerous

Sort	Data menu → Sort
Filter	Data menu → Filter
Aggregate	Data menu → Pivot table
Merge	=vlookup(…)

Basic skills

Here is some data on drunk driving deaths in 2013 from the National Highway Traffic Safety Administration.

1. Download the data to your computer, and open it in your spreadsheet program of choice.
2. What were the 3 states with the most deaths? The 3 with the fewest?
3. Find some state population estimates. Add them to your spreadsheet using a function called vlookup.
4. What were the states with the highest rates? The lowest?
5. How many deaths occured in the South? What region of the country had the highest rate of deaths? Here are some region codes that might help you. For practice, answer this question by using a pivot table. Pivot tables are one way to summarize data.
6. On the second tab of the drunk driving data, there is a breakdown of how intoxicated drivers were in fatal crashes. Use this sheet to ask and answer a more interesting question than the ones we have answered so far.

It’s exactly the same when your data is a little bigger.

Here is some data on where guns recovered in Chicago between 2001 and March 2012 came from.

1. What counties did the most guns come from?
2. What counties in Mississippi did the most guns come from? The fewest?
3. What states did the most guns come from?
4. Per capita?.

Choose your own adventure.

1. Using some of the skills we learned last time, make a map about the guns recovered in Chicago.
2. What happens when the data is even bigger? Find the fire hydrant in NYC that now has the most tickets.