Building Pivot Tables and Summaries for Excel with pandas

Most recurring Excel reports are a summary of a longer table: revenue by region and month, tickets by team and priority, hours by project and person. Built by hand, the summary is a pivot table someone drags together each month, and it drifts — a filter left on, a region missing because it had no sales in March, a total that includes the previous month because the source range was not updated. Built naively in Python, it drifts differently: pivot_table output with two header rows that Excel users cannot sort or filter, columns that appear and disappear between months, a NotImplementedError when writing without the index, and grand totals that disagree with the detail sheet by a rounding cent.

A summary sheet is a contract with its readers: the same rows and columns every period, totals that reconcile to the detail, headers a person can filter on. pandas produces all of that reliably once the shape is decided up front instead of inherited from whatever the data happened to contain. This guide builds summaries with pivot_table and groupby, fixes the row and column sets, flattens headers for Excel, adds totals and subtotals that reconcile, and covers the option of shipping a native Excel pivot table that refreshes from a data sheet.

Prerequisites

python -m venv .venv && source .venv/bin/activate
pip install "pandas>=2.2" "openpyxl>=3.1" xlsxwriter
mkdir -p in out

pandas does the aggregation. openpyxl handles styling and templates; xlsxwriter is used where a workbook is generated from scratch. The examples use a transactions extract with date, region, product, channel, units and revenue columns — the shape most sales, finance and operations exports share. If your extract comes from several files, combine them first with the patterns in Merging Multiple Spreadsheets.

Diagnostic: Profile the Dimensions Before Pivoting

A pivot's row and column labels come from the data. Profile each dimension column — distinct values, blanks, stray whitespace and case variants — because each of those becomes a separate row or column in the output.

# pip install "pandas>=2.2"
from pathlib import Path
import pandas as pd

SOURCE = Path("in/transactions-2026-09.csv")
DIMENSIONS = ["region", "product", "channel"]

def profile_dimensions(path: Path) -> pd.DataFrame:
    try:
        frame = pd.read_csv(path, parse_dates=["date"])
    except (OSError, pd.errors.ParserError) as exc:
        raise SystemExit(f"cannot read {path}: {exc}")
    rows = []
    for col in DIMENSIONS:
        raw = frame[col]
        normalised = raw.astype("string").str.strip().str.casefold()
        rows.append({
            "column": col,
            "distinct_raw": raw.nunique(dropna=True),
            "distinct_normalised": normalised.nunique(dropna=True),
            "blanks": int(raw.isna().sum() + (raw.astype("string").str.strip() == "").sum()),
            "examples": sorted(raw.dropna().astype(str).unique())[:6],
        })
    return pd.DataFrame(rows)

if __name__ == "__main__":
    print(profile_dimensions(SOURCE).to_string(index=False))

  column  distinct_raw  distinct_normalised  blanks                                         examples
  region             7                    5       3  ['EMEA', 'Emea', 'North America', 'North America ', ...]
 product            42                   42       0  ['A-100', 'A-110', 'B-200', ...]
 channel             4                    3      11  ['Online', 'Partner', 'Retail', 'retail']

Seven raw regions collapse to five after trimming and case-folding, and there are blanks. Pivoting this as-is produces a summary with EMEA and Emea on separate rows and a NaN row or silently dropped transactions. Clean first; clean column names and whitespace in pandas covers the cleaning helpers in detail.

Core Implementation

Step 1: Normalise Dimensions and Fix the Category Sets

Map raw values to canonical labels, then declare each dimension as a categorical with the complete list of values the report must show. Months with no activity and regions with no sales then still appear.

# pip install "pandas>=2.2"
import pandas as pd

REGIONS = ["EMEA", "North America", "LATAM", "APAC", "Unassigned"]
CHANNELS = ["Online", "Retail", "Partner", "Unassigned"]
REGION_MAP = {"emea": "EMEA", "north america": "North America", "latam": "LATAM", "apac": "APAC"}

def normalise(frame: pd.DataFrame, period: str) -> pd.DataFrame:
    out = frame.copy()
    region = out["region"].astype("string").str.strip().str.casefold()
    out["region"] = pd.Categorical(region.map(REGION_MAP).fillna("Unassigned"), categories=REGIONS)
    channel = out["channel"].astype("string").str.strip().str.title()
    out["channel"] = pd.Categorical(channel.where(channel.isin(CHANNELS), "Unassigned"),
                                    categories=CHANNELS)
    months = pd.period_range(end=pd.Period(period, "M"), periods=12, freq="M")
    out["month"] = pd.Categorical(out["date"].dt.to_period("M").astype(str),
                                  categories=[str(m) for m in months])
    dropped = out["month"].isna().sum()
    if dropped:
        print(f"note: {dropped} row(s) outside the 12-month window excluded")
    return out.dropna(subset=["month"])

Mapping unknown values to Unassigned rather than dropping them keeps totals honest: every unit of revenue appears somewhere in the summary, and a growing Unassigned row is itself a data-quality signal someone will ask about.

Step 2: Pivot with Stable Rows and Columns

# pip install "pandas>=2.2"
import pandas as pd

def revenue_pivot(frame: pd.DataFrame) -> pd.DataFrame:
    table = pd.pivot_table(
        frame,
        index="region",
        columns="month",
        values="revenue",
        aggfunc="sum",
        fill_value=0,              # a month with no sales shows 0, not blank
        observed=False,            # keep categories with no rows
        margins=True,              # grand totals
        margins_name="Total",
        sort=False,                # keep the categorical order, not alphabetical
    )
    return table.round(2)

observed=False is the setting that keeps empty categories; pandas is moving the default to True, so set it explicitly and the report shape will not change on upgrade. When empty rows still disappear — because the dimension is not categorical, or because dropna removed an all-empty column — the fix is in fix pandas pivot_table missing categories.

Step 3: Flatten Headers for Excel

Pivoting with more than one values field or more than one columns level produces MultiIndex columns. Excel users need a single header row they can filter and sort, and pandas refuses to write MultiIndex columns without the index.

# pip install "pandas>=2.2"
import pandas as pd

def flatten_columns(table: pd.DataFrame, sep: str = " ") -> pd.DataFrame:
    out = table.copy()
    if isinstance(out.columns, pd.MultiIndex):
        out.columns = [sep.join(str(level) for level in col if str(level) != "").strip()
                       for col in out.columns.to_flat_index()]
    else:
        out.columns = [str(c) for c in out.columns]
    out.columns.name = None
    return out.reset_index()             # index labels become a normal first column

multi = pd.pivot_table(
    pd.DataFrame({"region": ["EMEA", "APAC"], "channel": ["Online", "Retail"],
                  "units": [5, 3], "revenue": [500.0, 240.0]}),
    index="region", columns="channel", values=["units", "revenue"], aggfunc="sum", fill_value=0)
print(flatten_columns(multi).columns.tolist())
# ['region', 'revenue Online', 'revenue Retail', 'units Online', 'units Retail']

The details — including the NotImplementedError pandas raises and the blank row it inserts under MultiIndex headers — are in fix pivot table MultiIndex columns in Excel.

Step 4: Reconcile Totals to the Detail

A summary that does not add up to the detail sheet destroys trust in the whole report. Check it in code before writing.

# pip install "pandas>=2.2"
import pandas as pd

def reconcile(summary: pd.DataFrame, detail: pd.DataFrame, value: str = "revenue",
              tolerance: float = 0.005) -> None:
    grand = float(summary.loc["Total", "Total"])
    detail_total = float(detail[value].sum())
    if abs(grand - round(detail_total, 2)) > tolerance:
        raise AssertionError(f"summary total {grand:,.2f} != detail {detail_total:,.2f}")
    body = summary.drop(index="Total", columns="Total")
    row_sums = body.sum(axis=1).round(2)
    mismatched = (row_sums - summary.drop(index="Total")["Total"]).abs() > tolerance
    if mismatched.any():
        raise AssertionError(f"row totals mismatch for {list(row_sums[mismatched].index)}")

Round once, at the end. Rounding individual cells before summing produces totals that differ from the rounded sum by a cent or two — which readers notice immediately when they add up a column in Excel.

Step 5: Write Summary and Detail with Formatting

# pip install "pandas>=2.2" xlsxwriter
from pathlib import Path
import pandas as pd

def write_report(summary: pd.DataFrame, detail: pd.DataFrame, dest: Path) -> None:
    flat = flatten_columns(summary)
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        flat.to_excel(writer, sheet_name="Summary", index=False)
        detail.to_excel(writer, sheet_name="Detail", index=False)
        book, ws = writer.book, writer.sheets["Summary"]
        money = book.add_format({"num_format": "#,##0.00"})
        bold_money = book.add_format({"num_format": "#,##0.00", "bold": True, "top": 1})
        ws.set_column(0, 0, 18)
        ws.set_column(1, len(flat.columns) - 1, 12, money)
        last = len(flat)                                   # Total row index (0-based, after header)
        ws.set_row(last, None, bold_money)
        ws.freeze_panes(1, 1)
        ws.autofilter(0, 0, last - 1, len(flat.columns) - 1)   # filter excludes the Total row
        writer.sheets["Detail"].freeze_panes(1, 0)

Excluding the total row from the autofilter range means sorting by a month column never moves Total into the middle of the regions — a small detail that saves a lot of confused emails. Number formats applied per column follow fix openpyxl number format not applied if you use openpyxl instead.

Edge Cases and Variants

Subtotals Within Groups

Reports grouped by region and product often need a subtotal after each region's products, which margins does not provide. Build them with groupby and interleave, then use Excel outline levels so readers can collapse regions — the complete pattern is in add subtotals to Excel reports with pandas.

Several Measures with Named Aggregations

When a summary needs different functions per measure — sum of revenue, mean of discount, count of orders — groupby().agg with named aggregations is clearer than a multi-valued pivot and produces flat column names directly:

# pip install "pandas>=2.2"
import pandas as pd

def region_kpis(frame: pd.DataFrame) -> pd.DataFrame:
    return (frame.groupby("region", observed=False)
                 .agg(orders=("order_id", "nunique"),
                      units=("units", "sum"),
                      revenue=("revenue", "sum"),
                      avg_discount=("discount", "mean"))
                 .assign(revenue_per_order=lambda d: (d["revenue"] / d["orders"]).round(2))
                 .fillna({"avg_discount": 0, "revenue_per_order": 0})
                 .reset_index())

A Native Excel Pivot Table That Refreshes

Some users want a real pivot table they can re-slice. Neither openpyxl nor xlsxwriter can build a pivot cache from scratch, but openpyxl preserves pivot tables that already exist in a template. Design the pivot once in Excel against a data sheet or table, then have Python replace the data and flag the pivot to refresh on open:

# pip install "pandas>=2.2" "openpyxl>=3.1"
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

def refresh_template(template: Path, dest: Path, detail: pd.DataFrame) -> None:
    wb = load_workbook(template)
    try:
        ws = wb["Data"]
        ws.delete_rows(2, ws.max_row)                          # keep the header row
        for row in detail.itertuples(index=False):
            ws.append(list(row))
        for sheet in wb.worksheets:
            for pivot in getattr(sheet, "_pivots", []):
                pivot.cache.refreshOnLoad = True               # Excel rebuilds the cache on open
        wb.save(dest)
    finally:
        wb.close()

The pivot's source range must cover the new rows — base it on an Excel table or a generous range in the template. _pivots is an internal attribute, so pin your openpyxl version and test after upgrades; LibreOffice and Google Sheets honour refresh-on-load inconsistently, so this pattern suits Excel-only audiences.

Period-over-Period Comparison Columns

The next question every reader asks of a summary is "compared with what?". Add comparison columns computed in pandas from the same categorical frame, so the prior period uses exactly the same region list and cleaning rules as the current one:

# pip install "pandas>=2.2"
import numpy as np
import pandas as pd

def compare_periods(frame: pd.DataFrame, current: str, previous: str) -> pd.DataFrame:
    """Revenue by region for two months, with absolute and percentage change."""
    both = frame[frame["month"].isin([current, previous])]
    table = pd.pivot_table(both, index="region", columns="month", values="revenue",
                           aggfunc="sum", fill_value=0, observed=False, sort=False)
    table = table.reindex(columns=[previous, current], fill_value=0)
    table["change"] = (table[current] - table[previous]).round(2)
    table["change_pct"] = np.where(table[previous] != 0,
                                   (table["change"] / table[previous]).round(4),
                                   np.nan)                     # undefined when prior was zero
    table.columns = [str(c) for c in table.columns]
    table.columns.name = None
    return table.reset_index()

Leave the percentage blank rather than infinite when the prior period was zero; a new region going from nothing to 40,000 is better described by the absolute change than by a meaningless percentage. Format change_pct as 0.0% in the writer and apply a conditional colour rule for negative changes, as described in Conditional Formatting and Data Validation in Excel, so the number stays a real number that readers can sort. Comparing two whole workbooks rather than two columns — last month's file against this month's — is a different task, covered in find differences between two Excel files.

Validation

Beyond reconciliation, assert the report shape is identical to the contract: exact column list, exact row labels in order, and no negative or missing values where they are impossible.

# pip install "pandas>=2.2"
from pathlib import Path
import pandas as pd

def verify_summary(path: Path, regions: list[str], months: list[str]) -> None:
    sheet = pd.read_excel(path, sheet_name="Summary")
    expected_cols = ["region", *months, "Total"]
    assert list(sheet.columns) == expected_cols, f"columns differ: {list(sheet.columns)}"
    assert list(sheet["region"]) == [*regions, "Total"], f"rows differ: {list(sheet['region'])}"
    body = sheet.drop(columns="region")
    assert not body.isna().any().any(), "blank cells in summary"
    detail = pd.read_excel(path, sheet_name="Detail")
    grand = sheet.loc[sheet["region"] == "Total", "Total"].iloc[0]
    assert abs(grand - round(detail["revenue"].sum(), 2)) < 0.005, "grand total does not reconcile"
    print(f"{path.name}: shape and totals verified")

Reading the written file back — rather than checking the DataFrame in memory — catches problems introduced by the writer, such as header rows shifted by MultiIndex output or totals that were formatted as text.

Performance and Scale Notes

pivot_table on a few million transactions takes seconds; the costly parts are usually reading the source and writing the detail sheet. Excel caps sheets at 1,048,576 rows, so for large extracts write the summary plus a filtered or aggregated detail rather than every transaction, and link to the full data elsewhere. Convert dimension columns to categoricals before grouping — beyond fixing the category set, it cuts memory and speeds up grouping several times on high-row-count data. For extracts that do not fit in memory, aggregate chunk by chunk and combine the partial sums; fix wrong totals when aggregating CSV chunks covers which aggregations combine correctly and which do not.

Troubleshooting

Complete Working Script

#!/usr/bin/env python3
# pip install "pandas>=2.2" xlsxwriter
"""Build a 12-month revenue summary by region, reconciled to detail, as an Excel report."""
import argparse
import sys
from pathlib import Path

import pandas as pd

REGIONS = ["EMEA", "North America", "LATAM", "APAC", "Unassigned"]
REGION_MAP = {"emea": "EMEA", "north america": "North America", "latam": "LATAM", "apac": "APAC"}


def load(path: Path, period: str) -> pd.DataFrame:
    frame = pd.read_csv(path, parse_dates=["date"])
    missing = {"date", "region", "revenue"} - set(frame.columns)
    if missing:
        raise ValueError(f"missing columns: {sorted(missing)}")
    region = frame["region"].astype("string").str.strip().str.casefold()
    frame["region"] = pd.Categorical(region.map(REGION_MAP).fillna("Unassigned"), categories=REGIONS)
    months = [str(m) for m in pd.period_range(end=pd.Period(period, "M"), periods=12, freq="M")]
    frame["month"] = pd.Categorical(frame["date"].dt.to_period("M").astype(str), categories=months)
    return frame.dropna(subset=["month"])


def summarise(frame: pd.DataFrame) -> pd.DataFrame:
    table = pd.pivot_table(frame, index="region", columns="month", values="revenue",
                           aggfunc="sum", fill_value=0, observed=False,
                           margins=True, margins_name="Total", sort=False)
    table = table.round(2)
    grand = round(float(frame["revenue"].sum()), 2)
    if abs(float(table.loc["Total", "Total"]) - grand) > 0.005:
        raise AssertionError("summary does not reconcile to detail")
    table.columns = [str(c) for c in table.columns]
    table.columns.name = None
    return table.reset_index()


def write(summary: pd.DataFrame, detail: pd.DataFrame, dest: Path) -> None:
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="xlsxwriter", datetime_format="yyyy-mm-dd") as writer:
        summary.to_excel(writer, sheet_name="Summary", index=False)
        detail.drop(columns="month").to_excel(writer, sheet_name="Detail", index=False)
        book, ws = writer.book, writer.sheets["Summary"]
        money = book.add_format({"num_format": "#,##0.00"})
        total = book.add_format({"num_format": "#,##0.00", "bold": True, "top": 1})
        ws.set_column(0, 0, 18)
        ws.set_column(1, len(summary.columns) - 1, 12, money)
        ws.set_row(len(summary), None, total)
        ws.freeze_panes(1, 1)
        ws.autofilter(0, 0, len(summary) - 1, len(summary.columns) - 1)


def main() -> int:
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("csv", type=Path)
    ap.add_argument("dest", type=Path)
    ap.add_argument("--period", required=True, help="last month of the report, e.g. 2026-09")
    args = ap.parse_args()
    try:
        detail = load(args.csv, args.period)
        summary = summarise(detail)
        write(summary, detail, args.dest)
    except (OSError, ValueError, AssertionError, pd.errors.ParserError) as exc:
        print(f"error: {exc}", file=sys.stderr)
        return 1
    print(f"wrote {args.dest}: {len(summary) - 1} regions x {len(summary.columns) - 2} months")
    return 0


if __name__ == "__main__":
    sys.exit(main())

Frequently Asked Questions

Should I send a pandas summary or a native Excel pivot table? A pandas summary for fixed reports that must look identical every period and reconcile exactly. A native pivot, refreshed from a template, when readers genuinely need to re-slice the data themselves.

How do I show percentages of the row or column total? Divide after pivoting: table.div(table["Total"], axis=0) for row shares, table.div(table.loc["Total"], axis=1) for column shares. Format as 0.0% in Excel rather than multiplying by 100 in pandas.

Why use sort=False in pivot_table? Without it, pandas sorts labels, which puts APAC before EMEA and months in string order. With categoricals and sort=False, the order is the one you declared.

Can I write several pivots to one sheet? Yes, with startrow in to_excel for each table, leaving blank rows between them. Separate sheets are easier for readers to filter and for code to verify.

Related

• Fix Pivot Table MultiIndex Columns in Excel — the header problems multi-level pivots cause
• Add Subtotals to Excel Reports with pandas — group subtotals and collapsible outlines
• Fix pandas pivot_table Missing Categories — keeping empty rows and columns
• Automating Monthly Sales Reports in Excel — scheduling and delivering the finished report
• Comparing and Reconciling Spreadsheets — checking one period's summary against another

Part of Python for Excel & CSV Data Processing.