Often you may want to add subtotals to a pandas pivot table.
Fortunately this is easy to do using built-in functions in pandas.
The following example shows how to do so.
Example: Add Subtotals to Pandas Pivot Table
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'], 'all_star': ['Y', 'N', 'Y', 'Y', 'N', 'N', 'N', 'Y'], 'points': [4, 4, 6, 8, 9, 5, 5, 12]}) #view DataFrame print(df) team position all_star points 0 A G Y 4 1 A G N 4 2 A F Y 6 3 A F Y 8 4 B G N 9 5 B F N 5 6 B F N 5 7 B F Y 12
We can use the following code to create a pivot table in pandas that shows the sum of points for each combination of team, all_star, and position in the DataFrame:
#create pivot table
my_table = pd.pivot_table(df, values='points',
index=['team', 'all_star'],
columns='position',
aggfunc='sum')
#view pivot table
print(my_table)
position F G
team all_star
A N NaN 4.0
Y 14.0 4.0
B N 10.0 9.0
Y 12.0 NaN
Now suppose we would like to add a subtotals row that shows the subtotal of points for each team and position.
We can use the following syntax to do so:
#add subtotals row to pivot table
pd.concat([
y.append(y.sum().rename((x, 'Total')))
for x, y in my_table.groupby(level=0)
]).append(my_table.sum().rename(('Grand', 'Total')))
position F G
team all_star
A N NaN 4.0
Y 7.0 4.0
Total 7.0 8.0
B N 5.0 9.0
Y 12.0 NaN
Total 17.0 9.0
Grand Total 24.0 17.0
We now have two subtotal rows that show the subtotal of points for each team and position, along with a grand total row that shows the grand total of each column.
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 Create Pivot Table with Count of Values
Pandas: How to Replace NaN Values in Pivot Table with Zeros
Pandas: How to Convert Pivot Table to DataFrame