Learn

Pivot tables won’t help you get your furniture through a door (unfortunately). They will help you group and summarize your data. Let’s look at an example of how powerful this can be.

Suppose we’re trying to calculate the total dollars lost to tornados in each state by month. Let’s use the small example table below.

Year Month Losses (\$) Length (mi) State
1 2020 March 5,000 .4 TX
2 2020 February 10,000 1.5 FL
3 2020 January 50,000 .8 AZ
4 2020 February 25,000 .9 FL
5 2020 January 30,000 1 AZ
6 2020 January 1,000 1.2 TX

We could answer this question by hand in three steps:

1. Group together all rows with the same state and month.
2. Collect the `Losses` values for all the rows in each group.
3. Sum the `Losses` for each group to report the total losses by group.

A pivot table is a separate table collecting the results of this process. That might sound a bit abstract, so let’s go through this step-by-step.

We start by creating a table of all the possible `Month` and `State` groups.

First, we use the values of the `State` column as column headers for the pivot table. Note that we don’t repeat values. Even though `AZ` appears twice in the state column, it only appears once in the headers of our new table.

AZ FL TX

Second, we use the values of the `Month` column to label the rows of the new table. Once again, each month will only appear once.

AZ FL TX
January
February
March

The cells of this table correspond to the groups we need to form to answer the original question. Now, we need to gather and summarize the values in each group to fill in the corresponding cell.

For example, the upper-left cell of the new table is in the row `January` and the column `AZ`. When we look back at the original table, there are two rows in this group. To report the total losses, we collect the `Losses` values for those rows and add them up: `50,000+30,000 = 80,000`.

AZ FL TX
January 80,000
February
March

If we move one cell down, we’ll be in the row `February` and column `AZ`. There are no rows of the original table corresponding to this group. We leave this cell blank, to tell someone reading the table that there were no rows of data in this group.

Here’s the final pivot table — do you agree with the rest of our calculations?

AZ FL TX
January 80,000 1,000
February 35,000
March 5,000

This process is the same for any pivot table. Starting with a table of data, you pick

• column labels: a column or columns of the original table to serve as column labels (or headers) for the pivot table
• row labels: a column or columns of the original table to serve as row labels for the pivot table
• cell values: a column or columns of the original table to provide the values for the cells of the pivot table
• summary method: a method for taking multiple values corresponding to a single group and producing one value for the pivot table

In our initial example, we picked

• column labels: `State`
• row labels: `Month`
• cell values: `Losses`
• summary method: `sum` (or total)

### Instructions

You have a table of data from a pet adoption agency. The columns in the table are `Species`, `Age`, `Weight`, and `Date adopted`. The adoption agency has asked you to calculate the average weight of a pet by species and age.

How would you set up a pivot table to answer this question? That is, what would you pick for

• column labels:
• row labels:
• cell values:
• summary method
• column labels: `Age`
• row labels: `Species`
• cell values: `Weight`
• summary method: `average`