Pandas Pivot Tables in Python – Easy Guide

Pivot Tables Pandas

In this article, we’ll talk about Pivot Tables in Python. We’ll implement the same using the pivot_table function in the Pandas module.

What is a Pivot Table?

Pivot Tables are a key feature of Microsoft Excel and one of the reasons that made excel so popular in the corporate world.

Pivot tables provide great flexibility to perform analysis of the data. 

It allows us to transform columns into rows and rows into columns. As it allows grouping based on any parameter, it can be easily confused with pandas Groupby function.

Think of pivot tables as a multidimensional version of GroupBy function.

Steps to Implement Pivot Tables in Python

Let’s get right into the implementation of Pivot tables in Python.

1. Load a dataset of your choice

We’ll be analyzing the Pokémon with stats dataset (Downloaded from Kaggle). You can get the dataset here.

Irrespective of whether you are fond of pokemon or not you can still get 100% from this article.

Gotta catch ‘em all… 🙂

import pandas as pd

#importing the dataset
data = pd.read_csv('Pokemon.csv')
data.head()
Dataset
Dataset

2. Syntax of pivot_table() method

DataFrame.pivot_table(data, values=None, index=None,columns=None, aggfunc='mean')

After calling pivot_table method on a dataframe, let’s breakdown the essential input arguments given to the method.

  • data – it is the numerical column on which we apply the aggregation function. for example, sales, speed, price, etc.
  • index – The columns which we want to convert as rows. we can pass multiple values as a list.
  • columns – Column variables which we now wish to keep as Column.
  • aggfunc – type of operation to perform on data. i.e. sum, mean, count, etc.

It will be a lot clearer with an Example.

3. Implementing pivot_tables in Python

Let’s say we need to find the average Speed of Pokémons belonging to Type-1.

It can be easily done using pandas Groupby, but the same output can be achieved easily using pivot_table with a much cleaner code.

With each example, we’ll slowly explore pivot_table in its full glory.

df.groupby(['Type 1'])[['Speed']].mean()

Similar result using pivot_table

df.pivot_table('Speed' , index = ['Type 1'], aggfunc = 'mean')
Result With Gropuby And Pivot Table
Result With gropuby And Pivot Table

4. Find the average speed using Pivot tables

But now if we were asked to find the average speed of each Type 1 Pokémon along with segregating them into Legendary and Non-Legendary.

df.pivot_table('Speed' , index = ['Type 1'] , columns= ['Legendary'] , aggfunc = 'mean')
Mean Speed Legendary Vs Non Legendary Pokemons
Mean Speed Legendary Vs Non Legendary Pokémon’s

Tada! With a single line of code we were able to achieve the desired results.

I couldn’t help but notice that on average The Legendary Pokémons were faster than the non-Legendary ones. Magic of pivot tables.

An important thing to understand here is that we needed the Type 1 column as our index so we passed it to the index argument in the pivot_table method.

But now, as each Type 1 Pokemon belonged to either Legendary category or Non-Legendary, we just transformed our data frame in a way so that it now shows an average speed of each type.

Now we have Legendary or Non-Legendary as features in our data.

As we needed ‘Legendary’ feature in our data as the column of new data, we simply needed to pass it in the columns parameter. Let’s modify our dataset a bit and do some new analysis.

We now create bins of Speed variable as a new added feature.

df['speed_range'] = pd.cut(df['Speed'] , [0,40,60,80,120] , include_lowest=True)

Now let’s output a dataframe which shows average speed as based on Legendary feature and the Speed range feature.

df.pivot_table('Speed' , index = ['Type 1'] , columns= ['speed_range','Legendary'] , aggfunc = 'mean')
Legendary As Well As Range Of Speed As Columns
Legendary As Well As Range Of Speed As Columns

I encourage you to try pandas pivot_table on the Titanic dataset as it would complement your practice of the topic.

Pivot tables in Python are not an easy topic to master but given the benefits it has to offer one must definitely include this knowledge in their analysis toolkit. Remember Practice is the Key here!

Conclusion

if this article we tried to get some idea on Pandas pivot_table by implementing it on a dataset. We realized it to be a multidimensional version of groupby operation (Conceptually speaking). Happy Learning!