You can use the following syntax to create a pivot table in pandas and provide multiple values to the aggfunc argument:
df.pivot_table(index='col1', values='col2', aggfunc=('sum', 'mean'))
This particular example creates a pivot table that displays the sum and the mean of values in col2, grouped by col1.
The following example shows how to use this syntax in practice.
Example: Create Pandas Pivot Table with Multiple aggfunc
Suppose we have the following pandas DataFrame that contains information about various basketball players:
import pandas as pd #create DataFrame df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C'], 'points': [4, 4, 2, 8, 9, 5, 5, 7, 8, 8, 4, 3], 'assists': [2, 2, 5, 5, 4, 7, 5, 3, 9, 8, 4, 4]}) #view DataFrame print(df) team points assists 0 A 4 2 1 A 4 2 2 A 2 5 3 A 8 5 4 B 9 4 5 B 5 7 6 B 5 5 7 B 7 3 8 C 8 9 9 C 8 8 10 C 4 4 11 C 3 4
We can use the following code to create a pivot table that summarizes both the sum and the mean number of points scored by each team:
#create pivot table to summarize sum and mean of points by team
df.pivot_table(index='team', values='points', aggfunc=('sum', 'mean'))
mean sum
team
A 4.50 18
B 6.50 26
C 5.75 23
The resulting pivot table summarizes the mean and the sum of the points scored by each team.
For example, we can see:
- Players on team A had a mean points value of 4.50 and a sum points value of 18.
- Players on team B had a mean points value of 6.50 and a sum points value of 26.
- Players on team C had a mean points value of 5.75 and a sum points value of 23.
Note that we aggregated using the sum and the mean in this example, but we could also aggregate by other metrics such as:
- count
- min
- max
- median
- std (standard deviation)
The following example shows how to aggregate the values in the points column by these metrics for each team:
#create pivot table to summarize several metrics for points by team
df.pivot_table(index='team', values='points',
aggfunc=('count', 'min', 'max', 'median', 'std'))
count max median min std
team
A 4 8 4.0 2 2.516611
B 4 9 6.0 5 1.914854
C 4 8 6.0 3 2.629956
Note: You can find the complete documentation for the pandas pivot_table() function here.
Additional Resources
The following tutorials explain how to perform other common tasks in pandas:
Pandas: How to Sort Pivot Table by Values in Column
Pandas: How to Create Pivot Table with Sum of Values
Pandas: How to Add Subtotals to Pivot Table
Pandas: How to Modify Column Names in Pivot Table