Manipulation and Visualisations

Adding Columns

Let's look at two more features of pivot tables that will allow you to do more complex investigations of your data.

We learned that a Row in a pivot table specifies an aggregation or grouping of items for which you want to compute a value. A Column in a pivot table is just another aggregation, but it displays the values across the top of the table. It's easier to understand when you see it...

Do This: Add columns that group your data by gender, as in the animation below.

Adding columns grouped by gender. The resulting table shows the average rating and count for each movie, but also broken down by gender. The pivot table also preserves the "Grand Totals" which is what the data would look like if no columns were specified.

Filtering Pivot Tables

Applying a filter to a pivot table does the same thing as it does in the normal spreadsheet - it allows you to filter out values from the raw data.

The animation below shows first filtering out 14-year-olds from the calculations, and then filtering out some of the movies. You don't have to do this, but in some instances it can be a very useful tool.

The Next Step - Manipulating the Pivot Table

If you want to maninpulate the data further, to sort or filter, you shouldn't do it in the live, active pivot table. Instead you should copy the table, and paste the values into a new spreadsheet.

Note: "Paste Values" is not the same as a normal "Paste".

Do This: Copy the pivot table, create a new tab in the spreadsheet and do Edit -> Paste special -> Paste values only. Watch the animation to see how.

Copying a pivot table, making a new tab in the spreadsheet, and pasting values.

"Why Paste values only instead of just Paste?"

If you copy a pivot table and do a normal paste it will paste another copy of the active, live, responsive pivot table into a new tab. We don't want the active table; we just want the values it produced.
You probably want to add/change column headings to display the table, especially to use it for charting. See the image below for how you might do this.

Changing column names to make them easier to read for a chart

From cleaning that up, plus some filtering we can make a chart of movies where the differences between male and female ratings are significant.

A chart showing movies with large differences in male vs. female ratings

Moving on: Visualizing Summary Tables

A summary table can be a good first step toward a great visualization. You often want to summarize data first, then chart it, so you can see larger connections or patterns.

Summary tables also don't have to be small! You might make a summary table that is still too big or full of numbers to see any trends in the data.

For example, from the original movie rating data (which had roughly 65,000 records) if you make a pivot table that shows the average movie rating for every possible age group the table will be about 75 rows long with a whole bunch of decimal numbers.

You can't see any trend or pattern in the data just by looking at the table. But if you plot the results on a graph you can!

Summary TableChart

NOTE: A deeper investigation of the data shows that the number of movies rated by people at this web site declined steadily after age 28. The upward trend may be affected by the fewer number of ratings.

Page updated

Report abuse