Table of Contents
Adding subtotals to a Pandas pivot table is done by using the aggfunc argument to the pivot_table() function. This argument allows you to specify a function that will be used to calculate the subtotals. Commonly used functions include sum, mean, and count. The subtotal values are placed in the last row of the pivot table.
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 .