Add Subtotals and Grand Totals to a pandas Pivot Table

Excel's pivot tables show a subtotal under every region and a grand total at the bottom, and managers expect the same from a report generated in Python. pandas gets you halfway: pivot_table(..., margins=True) adds a grand total, but a two-level pivot — region and city — gets no region subtotals. This guide, part of Creating Pivot Tables from Excel Data, adds them properly: computed from the raw data so averages are right, placed after each group, and written to Excel either as formatted values or as live formulas.

Prerequisites

pip install pandas openpyxl

Sample sales data, as it would come from an export sheet:

import pandas as pd

sales = pd.DataFrame({
    "Region": ["North"] * 5 + ["South"] * 4,
    "City": ["Leeds", "Leeds", "York", "York", "York", "Bath", "Bath", "Exeter", "Exeter"],
    "Quarter": ["Q1", "Q2", "Q1", "Q2", "Q2", "Q1", "Q2", "Q1", "Q2"],
    "Revenue": [120, 150, 80, 60, 40, 200, 210, 90, 100],
})
sales.to_excel("sales.xlsx", index=False)
sales = pd.read_excel("sales.xlsx")

Step 1 — The grand total with margins

pt = sales.pivot_table(index=["Region", "City"], columns="Quarter", values="Revenue",
                       aggfunc="sum", margins=True, margins_name="Total")
print(pt)

The result has a Total column and a final Total row — 1,050 — computed from all the raw rows. There is nothing between Leeds, York and Bath to show where North ends. Grand totals and their formatting are covered in Export a pandas Pivot Table to Excel, Formatted; the rest of this guide is about the rows in between.

Step 2 — Build subtotal rows from the raw data

A subtotal is just the same pivot computed one level up. Build it with a second pivot_table on the outer level only, then give it the same two-level index as the detail rows, with "Subtotal" in the inner position:

def with_subtotals(data, rows, columns, values, aggfunc="sum", label="Subtotal", total="Total"):
    detail = data.pivot_table(index=rows, columns=columns, values=values,
                              aggfunc=aggfunc, margins=True, margins_name=total)
    grand = detail.loc[[total]]
    detail = detail.drop(index=total, level=0)
    sub = data.pivot_table(index=rows[0], columns=columns, values=values,
                           aggfunc=aggfunc, margins=True, margins_name=total)
    sub = sub.drop(index=total)
    sub.index = pd.MultiIndex.from_arrays([sub.index, [label] * len(sub)], names=rows)
    grand.index = pd.MultiIndex.from_tuples([(total, "")], names=rows)
    parts = []
    for group in detail.index.get_level_values(0).unique():
        parts += [detail.loc[[group]], sub.loc[[group]]]
    return pd.concat(parts + [grand])

report = with_subtotals(sales, ["Region", "City"], "Quarter", "Revenue")
print(report)

Building the output group by group keeps the regions in their original order and puts each subtotal directly after its cities. The subtotal pivot also uses margins=True, so each subtotal row gets its own Total column value. And because both the subtotals and the grand total come from pivot_table over the raw rows, they are correct for any aggregation — which matters as soon as the numbers are not sums.

Step 3 — Averages: why raw data matters

Suppose the report shows average order value instead of total revenue. The tempting shortcut is to average the city rows of each group. That weights York — three orders — the same as Leeds with two:

city_avg = sales.pivot_table(index=["Region", "City"], values="Revenue", aggfunc="mean")
print(city_avg.groupby(level="Region").mean()["Revenue"].to_dict())   # {'North': 97.5, 'South': 150.0}
print(sales.groupby("Region")["Revenue"].mean().to_dict())            # {'North': 90.0, 'South': 150.0}

avg_report = with_subtotals(sales, ["Region", "City"], "Quarter", "Revenue", aggfunc="mean")
print(avg_report.loc[("North", "Subtotal")].round(1).to_dict())

North's true average is 90, not 97.5. with_subtotals gets it right because each subtotal is a fresh aggregation of the region's raw rows. The same applies to counts of distinct customers, medians and percentages: never derive a subtotal from the rows above it unless the measure is a plain sum.

Step 4 — Write the report with bold subtotal rows

Flatten the index into columns and write the table, then style every subtotal and total row so readers can find them:

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

flat = report.reset_index()
flat.columns.name = None
flat.to_excel("pivot_subtotals.xlsx", index=False)

wb = load_workbook("pivot_subtotals.xlsx")
ws = wb.active
bold = Font(bold=True)
shade = PatternFill("solid", fgColor="E7EBEF")
for row in ws.iter_rows(min_row=2):
    if row[1].value == "Subtotal" or row[0].value == "Total":
        for cell in row:
            cell.font = bold
            cell.fill = shade
wb.save("pivot_subtotals.xlsx")
print(flat.values.tolist()[2])     # ['North', 'Subtotal', 200, 250, 450]

Repeating the region on every row keeps the sheet filterable. If readers prefer the grouped look with the region shown once, blank the repeats just before writing, as described in Forward-Fill Blank Cells in Grouped Excel Reports with pandas.

Step 5 — Or write subtotals as live formulas

When people will adjust the city figures in Excel, static subtotals go stale. Write the detail values and let Excel compute the rest with SUBTOTAL(9, …), which sums a range while ignoring any other SUBTOTAL results inside it:

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

detail = sales.pivot_table(index=["Region", "City"], columns="Quarter", values="Revenue", aggfunc="sum")
quarters = list(detail.columns)
wb = Workbook()
ws = wb.active
ws.append(["Region", "City", *quarters])
first_data_row = 2
for region, block in detail.groupby(level="Region", sort=False):
    start = ws.max_row + 1
    for (_, city), values in block.iterrows():
        ws.append([region, city, *values.tolist()])
    end = ws.max_row
    ws.append([region, "Subtotal", *[f"=SUBTOTAL(9,{get_column_letter(3 + i)}{start}:{get_column_letter(3 + i)}{end})"
                                     for i in range(len(quarters))]])
last = ws.max_row
ws.append(["Total", "", *[f"=SUBTOTAL(9,{get_column_letter(3 + i)}{first_data_row}:{get_column_letter(3 + i)}{last})"
                          for i in range(len(quarters))]])
wb.save("pivot_formulas.xlsx")
print(ws["C4"].value, ws["C8"].value)     # =SUBTOTAL(9,C2:C3) =SUBTOTAL(9,C2:C7)

The grand-total formula spans every row, subtotals included, and still returns 490 for Q1 — recalculating the file in LibreOffice confirms 200 and 290 for the Q1 subtotals and 490 for the total. SUBTOTAL skips the nested subtotal cells, so nothing is counted twice. Use function number 1 for averages, 2 for counts and 4 for maximums, but remember Step 3: an average of a range of detail cells is an average of cities, not of orders. Formulas are best for sums. Calculating them outside Excel to verify the file is covered in Recalculate Excel Formulas Without Excel in Python.

Step 6 — Check that the pieces add up

A report with subtotals makes three claims that must agree: the cities add up to each subtotal, the subtotals add up to the grand total, and the grand total matches the source data. For sums, test all three before the file goes out:

is_sub = report.index.get_level_values("City") == "Subtotal"
is_total = report.index.get_level_values("Region") == "Total"
cities = report[~is_sub & ~is_total]
subs = report[is_sub]

by_region = cities.groupby(level="Region", sort=False).sum()
assert (by_region.values == subs.values).all(), "cities do not add up to subtotals"
assert (subs.sum().values == report[is_total].values).all(), "subtotals do not add up to the total"
assert report.loc[is_total, "Total"].item() == sales["Revenue"].sum(), "total differs from source"
print("checks passed:", report.loc[is_total, "Total"].item())      # checks passed: 1050

The boolean masks built from the index levels are also how you separate detail, subtotal and total rows for any further processing — charting only the cities, for example, or exporting the subtotals as a summary sheet. Skip the first two checks for averages and other non-additive measures, where the parts are not supposed to add up; the third still applies with the matching aggregation of the source.

Common pitfalls

• Summing the subtotal rows again. Once subtotals are in the frame, report.sum() counts every value twice. Filter them out, or compute totals before adding them.
• Sorting after adding subtotals. An alphabetical sort moves "Subtotal" between cities. Sort before, or sort by region and a subtotal flag.
• Using SUM in formula totals. =SUM(C2:C7) over a range containing subtotals double-counts; use SUBTOTAL(9, …) or reference only the subtotal cells.
• Empty groups after filtering. If a filter removes every row for one city, it disappears from the detail pivot but the region subtotal still covers the remaining cities correctly, because both are built from the same filtered rows. Filter the raw data, never the finished report.
• Mismatched labels. A subtotal labelled "Subtotal" in one report and "Total" in another breaks every downstream filter. Pick names once.

Conclusion

margins=True provides the grand total; subtotals need a second pivot on the outer level, relabelled and interleaved with the detail rows. Aggregating each subtotal from the raw data keeps averages and counts correct. Write the result with styled subtotal rows for a finished report, or with SUBTOTAL(9, …) formulas when readers will change the figures in Excel.

Frequently asked questions

Does pandas pivot_table support subtotals? Not directly. margins=True adds one grand-total row and column, but no totals for each outer group of a multi-level index. Build subtotal rows with a second pivot on the outer level and combine them with the detail rows.

Why are my subtotal averages wrong? Averaging the group averages weights every city equally, however many rows it has. Compute each subtotal from the raw rows of that group — a second pivot_table on the outer level does exactly that.

How do I keep subtotals in the right place when sorting? Sort by the outer level first and then by a flag that is true for subtotal rows, so each subtotal follows its group. Or build the table group by group in the order you want.

Can the subtotals update when someone edits the numbers in Excel? Yes, if you write them as formulas. Use SUBTOTAL(9, range) for each group and for the grand total; SUBTOTAL ignores other SUBTOTAL results inside its range, so the grand total is not double counted.

Related

• Up one level: Creating Pivot Tables from Excel Data — pivots from Excel sheets.
• Create a Pivot Table from Excel with pandas — the basics of pivot_table.
• Add a Native Excel Pivot Table with Python — when readers need Excel's own pivot tools.
• SUMIF and SUMIFS Equivalent in pandas — conditional totals without a pivot.