You can use the following basic syntax to create a pivot table in pandas that displays the sum of values in certain columns:
pd.pivot_table(df, values='col1', index='col2', columns='col3', aggfunc='sum')
The following example shows how to use this syntax in practice.
Example: Create Pandas Pivot Table With Sum of Values
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'], 'position': ['G', 'G', 'F', 'F', 'G', 'F', 'F', 'F'], 'points': [4, 4, 6, 8, 9, 5, 5, 12]}) #view DataFrame print(df) team position points 0 A G 4 1 A G 4 2 A F 6 3 A F 8 4 B G 9 5 B F 5 6 B F 5 7 B F 12
The following code shows how to create a pivot table in pandas that shows the sum of ‘points’ values for each ‘team’ and ‘position’ in the DataFrame:
#create pivot table
df_pivot = pd.pivot_table(df, values='points', index='team', columns='position',
aggfunc='sum')
#view pivot table
print(df_pivot)
position F G
team
A 14 8
B 22 9
From the output we can see:
- Players on team A in position F scored a total of 14 points.
- Players on team A in position G scored a total of 8 points.
- Players on team B in position F scored a total of 22 points.
- Players on team B in position G scored a total of 9 points.
Note that we can also use the margins argument to display the margin sums in the pivot table:
#create pivot table with margins
df_pivot = pd.pivot_table(df, values='points', index='team', columns='position',
aggfunc='sum', margins=True, margins_name='Sum')
#view pivot table
print(df_pivot)
position F G Sum
team
A 14 8 22
B 22 9 31
Sum 36 17 53
The pivot table now displays the row sums and column sums.
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 operations in pandas:
Pandas: How to Reshape DataFrame from Long to Wide
Pandas: How to Reshape DataFrame from Wide to Long
Pandas: How to Group and Aggregate by Multiple Columns