Add subtotal rows to a pandas pivot_table with a MultiIndex using groupby, relabel, and concat

Learn a modern way to add subtotal rows to a pandas pivot_table with a MultiIndex: groupby the index level, relabel, concat, and sort. No deprecated pd.append.

Adding subtotals to a pandas pivot_table is a common reporting need, but many older snippets rely on the deprecated pd.append. Below is a concise, modern approach that works when your pivot has a MultiIndex on rows, such as index=["A", "B"], and you want per-group subtotals inside the same table.

Problem setup

We start from a simple dataset and build a pivot with hierarchical rows. The goal is to add subtotal rows for one of the index levels without breaking the structure.

import pandas as pd

raw_df = pd.DataFrame({
    "A": ["A1", "A1", "A1", "A2", "A2", "A2", "A3", "A3", "A3"],
    "B": ["B1", "B1", "B1", "B2", "B2", "B2", "B3", "B3", "B3"],
    "C": ["C1", "C1", "C2", "C2", "C2", "C3", "C3", "C3", "C3"],
    "D": [1, 2, 3, 4, 5, 6, 7, 8, 9],
})

cube = raw_df.pivot_table(
    index=["A", "B"],
    columns="C",
    values="D",
    aggfunc="sum",
    fill_value=0,
)

print(cube)

The pivot looks like this:

C       C1  C2  C3
A  B              
A1 B1   3   3   0
A2 B2   0   9   6
A3 B3   0   0  24

What’s the actual issue

With a single-level index you could lean on margins, but as soon as the rows are a MultiIndex, you need a way to compute and place subtotals that align with the index structure. The task is to keep everything in one table, insert group-level totals at the right positions, and avoid deprecated APIs.

Solution: groupby on the index level, reshape labels, concat, sort

The pattern is straightforward. First, aggregate over the desired index level. Then relabel the aggregated result so it fits back into the MultiIndex as a “Total” row under each group. Finally, concatenate it with the original pivot and sort by the index.

# Subtotals by the first index level ("A")
level_sums = cube.groupby(level=0).sum().rename(index=lambda k: (k, "Total"))
with_totals = pd.concat([cube, level_sums]).sort_index()

print(with_totals)

The result places a subtotal row under each group of A:

C         C1  C2  C3
A1 B1      3   3   0
   Total   3   3   0
A2 B2      0   9   6
   Total   0   9   6
A3 B3      0   0  24
   Total   0   0  24

If instead your subtotal should be by the second level ("B"), flip the level in groupby and adjust the new index labels accordingly, then sort by that level:

# Subtotals by the second index level ("B")
level_sums_b = cube.groupby(level=1).sum().rename(index=lambda v: ("Total", v))
with_totals_b = pd.concat([cube, level_sums_b]).sort_index(level=1)

print(with_totals_b)

That yields:

C         C1  C2  C3
A1    B1   3   3   0
Total B1   3   3   0
A2    B2   0   9   6
Total B2   0   9   6
A3    B3   0   0  24
Total B3   0   0  24

Why this matters

Reports often need subtotals for readability and quick validation. Doing it directly on the pivot keeps everything in one place, avoids manual stitching of tables, and plays nicely with MultiIndex layouts. Using concat avoids deprecated patterns and remains compatible with current pandas versions.

Takeaways

When you need subtotals in a pandas pivot_table with multiple index levels, compute a grouped sum over the index level of interest, reshape the resulting labels so they integrate into the existing MultiIndex, then concatenate and sort. For a single-level index, margins can be enough, but for multi-level scenarios this approach keeps the output clean and presentation-ready.