In this walk-through, we’ll use Olympic athlete data from London’s 2012 summer olympics. Use Guardian London Olympic data — look for the “download the data” link and be sure to save a copy so you can edit it. This blog post will use Google Spreadsheet’s Pivot Table function.
You can summarize your data with pivot tables in Excel, Google Spreadsheets or LibreOffice Calc. The details will between software, but the basic steps are the same. Follow these steps to look at the data in pivot tables on Google Spreadsheets:
1. Data > Pivot Table Report
2. A new tab gets created at the bottom with your Pivot table report. The table is empty, and on the right-hand side are fields for rows, columns, values, and filters.
3. Click Rows > Add Field, and choose Country name.
The country names appear on the left side of the matrix (as rows).
4. Click Columm > Add Field, and choose Sex.
The values for Sex, “F” and “M”, appear on the top of the matrix (as columns).
5. Now you have to populate the matrix with values. What to choose? Click Values > Add Field, and choose Name.
The Name category contains each athlete, so if you want to count the number of athletes from each country that are male or female, then this is the right selection. The matrix fills in. However, the values and totals don’t look right. The table is filled with “0”s.
6. For Values, choose Summarize by > CountUnique.
Pivot tables counts each unique entry in the Name category. Now your pivot table tabulates all the athletes according to Country and Sex. You can see how there are many different options for displaying values. (You should really be using COUNTA, which counts all entries, and not CountUnique, which only counts unique entries. As you recall from class, if any athletes have identical names, then you’ll have an undercount!).
7. You can sort the rows differently. Right now, they are sorted by country, alphabetically. Choose Rows > Sort by > CountUnique of Name in… >Sex > F.
Now your pivot table re-orders the rows by the number of Female athletes. Which country had the most female athletes? The United States.
8. Click Filter > Add Field and choose Sport name. Choose Show to get a list of all the values for Sport name.
9. Choose Clear, and then select only the sports that you want to see. For example, choose Badminton, and the OK. Your pivot table updates to show only the male and female athletes that play Badminton.
You should see that China sent the most female athletes in Badminton (8 athletes).
NOTE: If the “Report Editor” on the right-hand side (with the fields for Rows, Columns, Values, and Filters) disappears, don’t fret! You can get it back by simply clicking on any data inside of your the pivot table.