Using Pivot Table for Dummies with Pokemon Data — Part 1

Anthony Thong Do
Dec 3, 2018 · 4 min read

Working as a Product Manager at Holistics, a Business Intelligence company, I eat and sleep with data, either figuratively or literally 🤣

Since Pivot Tables are one of the most popular visualizations to present insights, it plays a significant role in my daily analysis work. However, it took me a while to get familiar with the concept for the first time since mostly all the examples are something Finance or Sales related, using random dummy datasets. But it’s easiest to develop your data knowledge when working with data sets that you already understand.

That’s how I came up with the idea of learning about pivot tables through more commonly known data points such as those of Pokemon, Football, Marvel Heroes, etc.

What is a Pivot Table and Why Should You Care?

Basically, a Pivot Table helps you transform the data in Raw Tables into readable and meaningful insights quickly using just drag-and-drop, which we will see through more examples below.

Since it’s very powerful, the feature is available in most of the spreadsheet editors such as Excel, Google Spreadsheet, CloudPivot.co… In this tutorial, I will use CloudPivot because of its ease of sharing online.

We will use this data table below across all the examples, let’s take a look
👉 Pokemon Dataset

Some exploratory questions might pop up in your mind, for example, “How many Normal Pokemon and Legendary Pokemon are there?”. And you want to have an answer like the table below?

This, is what we call Pivot Table ✌️ How do we achieve that?

Pivot Table Concept

Pivot Table is the result of summarizing and grouping the raw table through these 4 properties:

Values

Aggregated fields, numerical values that can be used for different types of calculations. For the text fields, they need to be turned into

Possible values: Count of Pokemon, Average Strength, Maximum Weight, Sum of Attack, etc.

Example
👉 Drag Count of Name into the Values Area to count how many Pokemon are there?

Columns

Group same values and calculations into columns.

Example
👉 Keep Count of Name and drag Legendary into the Columns Area to count how many Legendary and Non-Legendary Pokemon are there?

Rows

Group same values and calculations into one or multiple rows.

Example
👉 Keep Count of Name and drag Legendary into the Rows Area to count how many Legendary and Non-Legendary Pokemon are there?

Filters

To apply filters to the entire table.

Example
👉 Keep Count of Name in Values Area and Legendary in the Rows Area, then we add a filter to get Legendary Pokemon only

Advanced Examples

The examples above are just the beginning, with Pivot Tables, you can achieve more meaningful insights by mixing the Rows, Columns, Filters and Values area.

How many Legendary/Non-Legendary Pokemon by Generation?

How many Legendary/Non-Legendary Pokemon by Type 1?

Average HP, Attack, Defense, Speed, Special Atk & Def of Legendary/Non-Legendary Pokemon

From Pivot Tables to Charts

Another nice thing about Pivot Tables is that they make your data prepared for charts. With the example of the Legendary/Non-Legendary Pokemon above you can easily turn your pivot table into a Pie Chart like the one below

Pivot Table is a very powerful concept in data analysis, this first part might be short but I hope it’s useful for you to start as an overview.

In the next part, we will go through more complicated questions and datasets.

If you like it, follow me to get the latest update of this series and don’t hesitate to ask me any questions!

Disclaimer: CloudPivot is a free web app for data community to visualize data built by Holistics.io. You can upload or connect your data to quickly visualize and share with others.

Sample Datasets for You to Play Around

Pokemon with Gif Images

Fifa 19

Sample E-commerce

HackerNoon.com

#BlackLivesMatter

HackerNoon.com

Elijah McClain, George Floyd, Eric Garner, Breonna Taylor, Ahmaud Arbery, Michael Brown, Oscar Grant, Atatiana Jefferson, Tamir Rice, Bettie Jones, Botham Jean

Anthony Thong Do

Written by

Entrepreneurship, Product, Data, Design | holistics.io, dbidagram.io | anthonytd.com | 200% on Products

HackerNoon.com

Elijah McClain, George Floyd, Eric Garner, Breonna Taylor, Ahmaud Arbery, Michael Brown, Oscar Grant, Atatiana Jefferson, Tamir Rice, Bettie Jones, Botham Jean

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store