NYU — Spring 2014 / Week 2

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

Discussion: The data you brought

Please add a short description of the data you brought to class here.

Enough to make you dangerous

SortData menu → Sort
FilterData menu → Filter
AggregateData menu → Pivot table
Merge=vlookup(…)

Basic skills

Here is some data on drunk driving deaths in 2011.

  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. Here is some data on population. Add it 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.

Excel work doesn’t have to look like it

Your job is to identify as many people as you can in the picture above.

  1. The easiest people to identify in the picture should be the runners. From the online results, acquire a spreadsheet of the last runners to finish.

  2. Here’s a wrinkle: not everyone in the photo started at the same time. Here’s what the race organizers have to say about that: “Red bibs (numbers 101 - 8,999) are assigned to Wave 1 (10:00 a.m.). White bibs (numbers 9,000 to 17,999) are assigned to Wave 2 (10:20 a.m.). Blue bibs (numbers 18,000 +) are assigned to Wave 3 (10:40 a.m.).”

  3. Subtract 40 minutes from the finish gun off of everyone with a bib number less than 9000, and 20 minutes from everyone with a bib number from 9000 to 17999.

  4. Make a list of runners to call. Put their first and last names into separate columns with some combination of “Data -> Text to columns” and find and replace.

  5. Here’s the NYT take.

Basic skills when your data is bigger

A digression: sometimes data is published to the web, but, without context, it’s hard to know whether it’s interesting or not. Here’s an example about NYC school attendance from WNYC. It’s easy to pull down this data every day, but, pretty quickly, it’s going to get big enough that you will regret any cheating / manual labor you might have done in our earlier exercises today.

  1. See if you can find where the data that powers the Citibike stations map lives.

  2. Here is the same data for every 15 minutes on Wednesday, June 5.

  3. Filter the data to look at a single station.

  4. What time of day are the fewest bikes available system-wide?

  5. Add a column for which neighborhood each station belongs to, and answer the same question for Midtown South, the East Village, and another neighborhood of your choice.

  6. Why might you want to collect this data yourself instead of just asking CitiBike?

  7. In the Google doc we used earlier today, make a list of questions we could answer with this type of data, either by itself, or by joining it to other data.

Homework