Category Archives: Hands-On

Pivot Tables

Spreadsheets are Handy, but pivot tables are incredibly useful.

Wells by County

The Department of Environmental Conservation publishes data on gas wells in New York State. Download it: How many wells are there per county?

  1. Start with Data > Pivot Table Report — look at the cells Excel proposes to use. Does that include all of your data?
  2. Add Row — Use “COUNTY” for the rows. You should see a list of county names.
  3. Add Value — Use “API_WELLNO” for now.
  4. Check the formula — should excel count values or sum them? Or find an average?

And there you have it. More things to play with:

  • Try adding “SLANT” as a Column — horizontal (as opposed to vertical) wells are particularly controversial. Are there any concentrations of horizontal wells?
  • How would you work out how much money each county is collecting in permit fees?
  • Can you see any trends in the average permit fee in each county?

Coalition Casualties

Last semester, Matt Surrusco found iCasualties.org — NYT has a nice profile of Michael White who trolls through news sites and official releases to build out a database of coalition forces deaths. Start with http://icasualties.org/OEF/OEF_US_Fatalities.xls and pivot by “Country of Death” and “Place of Death.”

This data needs some cleanup — we’ll work on that next week.

We used a function: =YEAR() to find the year of each death. We also had to do format > cells … and select general to correct wonky display issues. If you right-click in your pivot table, you’ll see a “Refresh Data” option — you might need that if your year column is not showing up.

Some questions you could answer:

  1. What’s the most common age of death
  2. How many deaths, and at what age?
  3. What’s the most common age of death for members of the CIA? the Army?
  4. What rank and branch had the greatest number of casualties?

Asking Good Questions

Asking Good Questions

If you challenge yourself (and you should challenge yourself), you’re bound to get stuck. If you aren’t hitting walls and getting stuck, you aren’t trying hard enough. Technology is changing constantly, so learning how to ask the right questions and get help with new tools is probably more important than actually learning how to use any one tool well today. Continue reading Asking Good Questions

Spreadsheet Walkthrough

Spreadsheet Skills

This is not quite what we did in class, but close.

Google tracks searches for flu-related terms. Start at http://www.google.org/flutrends/ — it is worth reading up on how they produce this data so you have a sense of the limitations of it, but we’re just going to play with it.

Using formulas

Pay attention to the screen. Look at what happens when you hover, etc.

Review of Spreadsheeting skills with Flu data
-sorting to find max and min
-data types (text, number, location, date, etc.)
-what is a formula and a function, what’s the difference? choosing cells

-use a function to find the mean, median and range: look at how mean and median differ.

-using functions, Max, Min, Average, Median, Unique, Countif, Match, If

Walk Through

  1. Download the world historical flu trends http://www.google.org/flutrends/data.txt
  2. What is this data? (comma separated)
  3. Paste into spreadsheet? Use Data > Text to Columns to separate data into columns according to a delimiter
  4. In which week did which country had the most flu searches?
    =Max()
    =Match(criterion, range, 0)
    =Indirect(“A”&cell) to get date or re-order columns
  5. How much more did that country search for flu in that week than average?
  6. Order the countries by most flu searches (SUM…choose arbitrary 2012-13 to capture searches from all countries, Transpose countries-values to make a quick bar chart)