Pandas: How to Create Pivot Table with Sum of Values

---

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