Pandas: How to Add Subtotals to 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 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