Pandas pivot_table() – A Simple Guide with Video

Rate this post

In this tutorial, we will learn to use the Pandas function pivot_table(). This function is used to create a pivot table as a data frame. It allows for lots of customization possibilities to provide informative insights into our data.

Syntax Table

Here are the parameters from the official documentation:

ParameterTypeDescription
dataDataFrame
valuescolumn to aggregate,
optional
indexcolumn, Grouper, array,
or list of the previous
– If array, must be the same length as data. It’s used the same way as column values.
– The list can contain any of the other types but no list. – Keys to group by on the pivot table index.
columnscolumn, Grouper, array,
or list of the previous
– If array, must be the same length as the data. It’s used the same way as column values.
– The list can contain any of the other types but no list. – Keys to group by on the pivot table column.
aggfuncfunction, list of functions,
dict, default numpy.mean
– If list of functions, the resulting pivot table will have
hierarchical columns whose top level are the function names. Those are inferred from the function objects themselves.
– If dictionary, the key is the column to aggregate and the value is function or list of functions.
fill_valuescalar, default NoneValue to replace missing values with in the resulting pivot table, after aggregation.
marginsbool, default FalseAdd all row/columns. Example: subtotal/grand totals.
dropnabool, default TrueDo not include columns whose entries are all NaN.
margins_namestr, default 'All'Name of the row/column that will contain the totals when margins is True.
observedbool, default FalseThis only applies if any of the groupers are Categoricals.
– If True: only show observed values for categorical groupers.
– If False: show all values for categorical groupers.
sortbool, default TrueSort the result?
ReturnsTypeDescription
DataFrameAn Excel-style pivot table.

Basic Example

To get started, we will have a look at an introductory example. We create a data frame that we will be using throughout the rest of the article:

import pandas as pd
import numpy as np

df = pd.DataFrame(
           {'Team': ['Reds', 'Reds', 'Reds', 'Reds', 'Reds', 'Reds',
                     'Reds', 'Blues', 'Blues', 'Blues', 'Blues',
                     'Blues', 'Blues', 'Blues'], 
            'Position': ['Pitcher', 'Pitcher', 'Pitcher', 'Center Field',
                         'Center Field', 'Center Field', 'Center Field',
                         'Pitcher', 'Pitcher', 'Pitcher', 'Center Field',
                         'Center Field', 'Center Field', 'Center Field'],
            'Throw Arm': ['Left', 'Right', 'Right', 'Left', 'Left',
                          'Right', 'Left', 'Left', 'Right', 'Right',
                          'Right', 'Left', 'Left', 'Left',],
            'Throwing Speed': [80, 75, 69, 71, 68, 59, 80,
                               81, 74, 68, 70, 79, 76, 73],
            'Running Speed': [19, 20, 18, 17, 19, 20, 16,
                              16, 19, 20, 19, 20, 21, 17]})

print(df)

The resulting DataFrame:

TeamPositionThrow ArmThrowing SpeedRunning Speed
0RedsPitcherLeft8019
1RedsPitcherRight7520
2RedsPitcherRight6918
3RedsCenter FieldLeft7117
4RedsCenter FieldLeft6819
5RedsCenter FieldRight5920
6RedsCenter FieldLeft8016
7BluesPitcherLeft8116
8BluesPitcherRight7419
9BluesPitcherRight6820
10BluesCenter FieldRight7019
11BluesCenter FieldLeft7920
12BluesCenter FieldLeft7621
13BluesCenter FieldLeft7317

First, we import the necessary libraries, namely Pandas and Numpy. Then, we create a Pandas data frame and assign it to a variable called “df“. Finally, we output the data frame.

The data frame contains information about players of two Baseball teams. For each player, we get the player’s team, position, throw arm, throw speed, and running speed.

Now, we apply the pivot_table() function:

pd.pivot_table(df, values='Throwing Speed', 
               index='Team', aggfunc=np.mean)

Output:

Inside the function, we pass in the data frame as the first argument.

The second argument is the “values” parameter. This parameter expects the column or columns that we want to aggregate, and we assign it the column “Throwing Speed”.

The next parameter is the “index” parameter which we assign the column to be the index of the new data frame we create.

The final parameter here is the “aggfunc” parameter. This parameter expects the function or functions that we want to apply on the aggregated column. We set it equal to “np.mean” which is a Numpy function for calculating the mean value. We would not have to apply the “aggfunc” parameter to calculate the mean since the default value for this parameter is “np.mean“. But we do it anyway to make it easier to understand what is happening here.

The output shows a new data frame with the “Team” column as the index. For each team, we get the average throwing speed because the “Throwing Speed” column is the aggregated column and we assigned the “aggfunc” parameter the np.mean() function.

Apply Multiple Indexes

In the previous example, we saw how to calculate the average throwing speed of each team.

Now, we will go more into detail and calculate the average throwing speed for each position of each team. We achieve that by assigning the “index” parameter multiple columns:

pd.pivot_table(df, values='Throwing Speed', 
               index=['Team', 'Position'], aggfunc=np.mean)

Output:

We keep the rest of the function as it was before, but this time, we assign the “index” parameter a list of columns, namely the “Team” and the “Position” column.

This way, we created a multi-index data frame.

Add Columns to Group by

When we have a look at the initial data frame, we observe that we haven’t used the “Throw Arm” column in any way yet.

We could add it to the “index” parameter which creates this new data frame:

pd.pivot_table(df, values='Throwing Speed', 
               index=['Team', 'Position', 'Throw Arm'], aggfunc=np.mean)

Here’s the output table:

This way, we get the average throwing speed per throw arm per position per team. However, it looks a bit confusing here as there are too many index columns.  

An alternative way to apply the “Throw Arm” column within our pivot_table() function, is to assign it to the “columns” parameter:

pd.pivot_table(df, values='Throwing Speed', index=['Team', 'Position'], 
               columns = ['Throw Arm'], aggfunc=np.mean)

Here’s the output:

When we compare the two pivot tables here, we see that they contain the same information. The first column, as well as the second column, both provide information about the average throwing speed for each throw arm of each position of each team. Nevertheless, the second approach is way clearer as we have half as many rows here.

Thus, combining the “index” parameter with the “columns” parameter allows us to crave lots of information out of our data in a comprehensible way.

Other Aggregate Functions

By now, we only applied the np.mean() function to our pivot table. Of course, there are multiple other functions to make use of. One example is the np.max() function:

pd.pivot_table(df, values='Throwing Speed', index=['Team', 'Position'], 
               columns = ['Throw Arm'], aggfunc=np.max)

The output:

So, here we calculate the maximum value of the throwing speed of each throwing arm, position, and team. For example, the maximum value for throwing speed of the left-handed, Center Field positioned Blue’s Team players is 79.

This is just one example. There are all sorts of other functions to apply here.

Apply Multiple Functions

The “aggfunc” parameter allows us to perform multiple functions at once. So, the resulting data frame provides us with even more information about our data.

But to achieve that, we first have to assign the “values” parameter multiple columns to execute different functions on different columns:

pd.pivot_table(df, values=['Throwing Speed', 'Running Speed'], 
               index=['Team', 'Position'], columns = ['Throw Arm'], 
               aggfunc={'Throwing Speed': np.mean, 
                        'Running Speed': np.median})

Output:

We now assign the “values” parameter the “Throwing Speed” column, as well as the “Running Speed” column. The “aggfunc” parameter gets assigned a dictionary. The dictionary’s keys are the column names to perform the function on and the dictionary’s values are the functions. We calculate the mean for the “Throwing Speed” and the median for the “Running Speed”.

We can extend that even further by deploying multiple functions to a column. We do that by applying a list of functions within the dictionary:

pd.pivot_table(df, values=['Throwing Speed', 'Running Speed'],
               index=['Team', 'Position'], columns = ['Throw Arm'],
               aggfunc={'Throwing Speed': np.mean,
                        'Running Speed': [np.min, np.max, np.median]})

Output:

We assign the “Running Speed” column in our “aggfunc” parameter a list of functions, namely the np.min() function, the np.max() function, and the np.median() function. The outputted data frame shows the name of each function at the top.

Summary

All in all, the pivot_table() function provides us with lots of opportunities to create a pivot table that puts out a lot of information about our data. We learned how to use the function’s various parameters to analyze our data in several ways. We saw how to create a pivot table, how to do so with multiple indexes, how to apply key columns to group by, and how to add multiple functions for aggregation.

For more tutorials about Pandas, Python libraries, Python in general, or other computer science-related topics, check out the Finxter Blog page.

Happy Coding!