Guide

Advanced Data Transformation And CleaningDeep dive

Creating Pivot Tables from Excel Data with Pandas

Q: What's the difference between pivot_table() and DataFrame.pivot()?

When multiple rows share the same index/columns combination, pivot_table aggregates them with aggfunc, while pivot() raises a ValueError on duplicate entries. Set aggfunc explicitly so the rollup is intentional.

Q: Can I aggregate different metrics with different functions?

Yes — pass a dict to aggfunc, like {"revenue": "sum", "units_sold": "mean"}. Each value column then uses its own function, and margins=True totals respect each one.

Q: How do I get a totals row and column?

Pass margins=True, and label the totals with margins_name (for example "Total"). The margins honour each metric's own aggfunc.

Q: Why are some category combinations missing from the result in pandas 3.0?

When index or columns is a categorical dtype, the default is now observed=True, so only combinations present in the data appear. Pass observed=False to show every category level even when it has no rows.

Build pivot tables from Excel data in Python: ingest and clean a workbook, aggregate with pandas pivot_table, add totals, filter, and export a formatted report.

A pivot table summarizes raw rows into a grid: one dimension down the rows, another across the columns, and an aggregated metric in the cells. pandas.pivot_table() does the same job as Excel's pivot engine, but in code you can version, schedule, and rerun. This page walks the full pipeline — ingest, clean, aggregate, filter, sort, and export — with runnable snippets. Each block builds on the previous one and shares the same namespace, so you can paste them in order.

This is the reporting-grade walkthrough. If you only need the single-function recipe, jump to Create Pivot Table from Excel with Pandas; if you already have a pivot and just need it styled for stakeholders, see Export a Pandas Pivot Table to Excel (Formatted). Both sit under this workflow.

These examples assume a clean source table, part of the broader Advanced Data Transformation and Cleaning workflow that this page belongs to.

Install the dependencies

pandas delegates Excel I/O to an engine. openpyxl reads and writes .xlsx; xlsxwriter is an alternative writer with rich cell formatting.

Bash

pip install pandas openpyxl xlsxwriter

Create a sample workbook

So every example below runs as-is, generate a small sales workbook. The headers deliberately include mixed casing and a stray space to mimic a real export:

Python

import pandas as pd

sales = pd.DataFrame({
    "Region ": ["North", "North", "South", "South", "West", "West", "North", "South"],
    "Quarter": ["Q1", "Q2", "Q1", "Q2", "Q1", "Q2", "Q1", "Q2"],
    "Product_Category": ["Hardware", "Software", "Hardware", "Software",
                          "Hardware", "Software", "Software", "Hardware"],
    "Revenue": [12000, 8500, 9800, 7200, 6400, 5100, 4300, 11200],
    "Units_Sold": [120, 40, 95, 36, 60, 22, 18, 110],
})
sales.to_excel("sales_data.xlsx", sheet_name="Raw_Data", index=False)

Step 1: Ingest and clean the source

Excel exports arrive with trailing whitespace in headers and the occasional fully blank row. Normalize the column names and drop empty rows and columns before aggregating, so a header like "Region " doesn't break later lookups:

Python

df = pd.read_excel("sales_data.xlsx", sheet_name="Raw_Data", engine="openpyxl")

# Standardize headers: strip, lowercase, underscore-separate
df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")

# Drop rows/columns that are entirely empty
df = df.dropna(how="all").dropna(axis=1, how="all")

# Make sure metrics are numeric (coerce stray text to NaN)
for col in ["revenue", "units_sold"]:
    df[col] = pd.to_numeric(df[col], errors="coerce")

print(df.columns.tolist())

For deeper cleaning — currency symbols, mixed types, duplicate handling — see Cleaning Excel Data with Pandas. If the metrics you want to summarize live in a separate lookup workbook, join them onto the transactions first with Merging and Joining Excel DataFrames, and if the source has gaps that would skew a sum or mean, resolve them with Handling Missing Data in Excel Reports before you pivot.

Step 2: Build the pivot table

pivot_table takes the clean DataFrame, the metrics to summarize (values), the row grouping (index), the column grouping (columns), and how to aggregate (aggfunc). Here revenue is summed and units are averaged:

Python

pivot = pd.pivot_table(
    df,
    values=["revenue", "units_sold"],
    index=["region", "quarter"],
    columns="product_category",
    aggfunc={"revenue": "sum", "units_sold": "mean"},
    fill_value=0,
)
print(pivot)

The result has a hierarchical row index (region then quarter) and a column layout split by product_category. fill_value=0 replaces the NaN cells that appear where a region/quarter has no rows in a category.

Step 3: Add grand totals

margins=True appends a totals row and column; margins_name labels them. The totals respect each metric's own aggfunc:

Python

pivot_totals = pd.pivot_table(
    df,
    values="revenue",
    index="region",
    columns="product_category",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="Total",
)
print(pivot_totals)

Step 4: Filter the result

A pivot is just a DataFrame, so you can slice it. To keep only regions whose total revenue clears a threshold, sum across the category columns and mask the rows:

Python

revenue_by_region = pd.pivot_table(
    df, values="revenue", index="region",
    columns="product_category", aggfunc="sum", fill_value=0,
)
row_totals = revenue_by_region.sum(axis=1)
top_regions = revenue_by_region.loc[row_totals > 15000]
print(top_regions)

Step 5: Export a formatted report

Flatten the multi-level columns to plain strings so Excel shows readable headers, then write with xlsxwriter and style the header row:

Python

export = pivot.copy()
export.columns = ["_".join(map(str, col)).strip() for col in export.columns]
export = export.reset_index()

with pd.ExcelWriter("pivot_report.xlsx", engine="xlsxwriter") as writer:
    export.to_excel(writer, sheet_name="Pivot_Report", index=False)

    workbook = writer.book
    worksheet = writer.sheets["Pivot_Report"]
    header_fmt = workbook.add_format({
        "bold": True, "bg_color": "#4472C4", "font_color": "white", "border": 1,
    })
    for col_idx, col_name in enumerate(export.columns):
        worksheet.write(0, col_idx, col_name, header_fmt)
    worksheet.autofit()

print("Wrote pivot_report.xlsx")

Sort and rank the report

A pivot arrives sorted by its index labels (alphabetical regions, then quarters), which is rarely the order a reader wants. For a report, sort by the number that matters — usually a total — so the biggest contributors sit at the top. Compute a row total, attach it, sort descending, then drop the helper column:

Python

ranked = revenue_by_region.copy()
ranked["_total"] = ranked.sum(axis=1)
ranked = ranked.sort_values("_total", ascending=False).drop(columns="_total")
print(ranked)

To rank within a hierarchical index instead — say, quarters within each region — sort by the level you want to keep grouped and a metric together with sort_values on a specific column, for example pivot.sort_values(("revenue", "Hardware"), ascending=False). Because the column axis is a MultiIndex, the key is the full tuple.

Shortcut: `pd.crosstab` for frequency tables

When the metric you want is simply "how many rows fall into each combination", pd.crosstab is a thinner wrapper than pivot_table — you hand it the row and column series directly and it counts:

Python

counts = pd.crosstab(df["region"], df["product_category"])
print(counts)

Pass values= and aggfunc= to make it aggregate a metric instead of counting, and normalize="index" to turn each row into percentages that sum to 1 — handy for share-of-total reports. Under the hood crosstab calls pivot_table, so everything you learned about margins, fill_value, and observed applies here too.

Common errors and fixes

Error	Cause	Fix
`KeyError: 'region'`	Header casing/whitespace mismatch	Normalize headers with `.str.strip().str.lower()`; check `df.columns.tolist()`.
`DataError: No numeric types to aggregate`	Metric column is stored as text	Coerce first: `df[col] = pd.to_numeric(df[col], errors="coerce")`.
`ValueError: Grouper ... not 1-dimensional`	Same column passed to both `index` and `columns`	Use distinct columns for each axis.
Columns export as tuples	`to_excel()` renders the MultiIndex literally	Flatten with `["_".join(map(str, c)) for c in pivot.columns]` before writing.

When rows share the same index/columns combination, pivot_table aggregates them with aggfunc rather than erroring — that is the difference from DataFrame.pivot(), which raises ValueError on duplicate entries. Always set aggfunc explicitly so the rollup is intentional.

A note on `observed`

When index or columns is a categorical dtype, the observed parameter controls whether unused category combinations appear as empty cells. In pandas 3.0 the default is observed=True, so only combinations present in the data are shown. Pass observed=False if you want every category level represented even when it has no rows.

Multiple aggregations in one pass

A report rarely wants one number per group. agg takes a dictionary of column-to-function pairs, and named aggregations keep the output columns readable rather than producing a MultiIndex nobody wants to flatten:

Python

import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders")
df["Revenue"] = df["Quantity"] * df["Unit_Price"]

summary = (
    df.groupby(["Region"], observed=True)
      .agg(
          Orders=("Order_ID", "nunique"),
          Units=("Quantity", "sum"),
          Revenue=("Revenue", "sum"),
          Average_Order=("Revenue", "mean"),
          Largest=("Revenue", "max"),
      )
      .round(2)
      .reset_index()
      .sort_values("Revenue", ascending=False)
)
print(summary)

nunique rather than count on the order id is the detail that makes the figure honest: a count of rows counts line items, and a business asking "how many orders?" means distinct order numbers. Mixing the two in one report is a common source of two figures that should agree and do not.

observed=True matters whenever a grouping column is categorical — without it pandas produces a row for every category combination that could exist, including the ones with no data, and a regional summary suddenly has forty empty rows.

The two are the same computation in different shapes. Work in the long form — it filters, charts and concatenates cleanly — and pivot once at the end for the sheet a person reads.

Totals that agree with the detail

A pivot with margins adds a grand total row and column, and the arithmetic is worth verifying rather than assuming:

Python

import pandas as pd

wide = pd.pivot_table(
    df, index="Region", columns="Month", values="Revenue",
    aggfunc="sum", fill_value=0, margins=True, margins_name="Total",
)

corner = wide.loc["Total", "Total"]
assert abs(corner - df["Revenue"].sum()) < 0.01, "margins disagree with the source"
print(wide.round(2))

fill_value=0 decides how an empty combination is displayed, and the choice is editorial: zero is right when the combination genuinely means "no sales", and misleading when it means "no data collected". Where the distinction matters, leave the blanks and let the reader see them.

The assertion is the part worth keeping permanently. A margin that disagrees with the source total means rows were dropped somewhere — usually by a filter applied to one branch of the code and not the other, which is invisible in the output and obvious in a two-line check.

Percentages, ranks and running totals

A pivot answers "how much"; the columns readers ask for next are usually relative. All three are one line on the aggregated frame:

Python

import pandas as pd

summary = (
    df.groupby("Region", observed=True)["Revenue"].sum().round(2).reset_index()
)

summary["Share"] = (summary["Revenue"] / summary["Revenue"].sum()).round(4)
summary["Rank"] = summary["Revenue"].rank(ascending=False, method="min").astype(int)
summary = summary.sort_values("Revenue", ascending=False)
summary["Cumulative_Share"] = summary["Share"].cumsum().round(4)
print(summary)

Computing Share before sorting and Cumulative_Share after is deliberate: a running total only means anything in a defined order, and computing it on unsorted data produces a column that looks plausible and says nothing. method="min" on the rank gives tied groups the same position, which is what a reader expects from a leaderboard.

Storing Share as a fraction and applying a 0.0% number format on export keeps the value usable in later arithmetic — multiplying an already-scaled 48.4 by anything is how percentage columns end up wrong by two orders of magnitude.

Pivoting on more than one level

Two grouping keys produce a hierarchy, and the shape you export depends on who reads it:

Python

by_month_region = (
    df.groupby([df["Order_Date"].dt.to_period("M").astype(str), "Region"], observed=True)
      .agg(Revenue=("Revenue", "sum"))
      .reset_index()
      .rename(columns={"Order_Date": "Month"})
)

wide = by_month_region.pivot(index="Month", columns="Region", values="Revenue").fillna(0)
print(wide.round(0))

The long form is what charts and filters want; the wide form is what a reader wants to see. Keeping both in the same workbook — detail long, summary wide — costs one extra sheet and removes the usual argument about which shape the report should be.

Flattening the column index before writing is the step people forget. After a pivot_table with multiple value columns, wide.columns is a MultiIndex, and to_excel writes it as two header rows that break every downstream read. wide.columns = [" ".join(c).strip() for c in wide.columns] turns it back into something a spreadsheet can use.

Writing a pivot Excel can read

A pivot's shape is convenient in pandas and awkward in a spreadsheet: the index becomes a column with no header, and a MultiIndex becomes two header rows that break every later read. Flatten both before exporting:

Python

import pandas as pd

wide = pd.pivot_table(df, index="Region", columns="Month", values="Revenue",
                      aggfunc="sum", fill_value=0)

flat = wide.reset_index()                                   # index becomes a real column
flat.columns = [str(c) if not isinstance(c, tuple) else " ".join(str(p) for p in c).strip()
                for c in flat.columns]

with pd.ExcelWriter("pivot.xlsx", engine="openpyxl") as writer:
    flat.to_excel(writer, sheet_name="By month", index=False)

reset_index() is the line that makes the output usable: without it the region names live in the frame's index, to_excel writes them into an unnamed first column, and every downstream read of the file produces a column called Unnamed: 0. Flattening the column names removes the second header row that would otherwise confuse both readers and scripts.

The finishing touches are the same as for any generated sheet — a number format on the value columns, a frozen header, and column widths derived from the content. A pivot is usually the sheet people look at first, so it repays the extra ten lines.

Reconcile the pivot against its source

A pivot is a summary, and the one property worth asserting is that it still adds up to the data it summarises. Comparing the pivot's grand total with the source column's sum, within a small tolerance, catches the two failures that structural checks miss: a filter applied to one branch of the code and not the other, and a join that multiplied rows before the aggregation ran. The check is one line and it belongs in the job, not in a notebook.

Aggregate once, present twice

The same aggregated frame can produce both the long table a chart reads and the wide table a person reads, so compute it once and reshape for each audience. Recomputing for the second view is where the two quietly diverge — a filter applied in one branch and not the other — and a summary that disagrees with its own detail is the failure readers remember.

Log what the run actually did

Row counts at each boundary, what was filled, what was quarantined, how long it took: five or six lines per run turn a question about a number into a lookup. The value is not in reading them on a good day but in having them on a bad one, when a total has moved and nobody can say whether the source changed, the cleaning changed, or a filter was added. A job that records its own behaviour is one that can be debugged after the fact rather than re-run and watched.

Key takeaways

pd.pivot_table() replaces the manual click-through of Excel's PivotTable wizard with one repeatable call: normalize headers once, then supply index, columns, values, and aggfunc.
Clean before you aggregate — strip and lowercase headers, coerce metrics to numeric, and drop blank rows, or a stray "Region " header will surface as a KeyError.
Pass a dict to aggfunc ({"revenue": "sum", "units_sold": "mean"}) to summarize each metric its own way, and margins=True with margins_name to append totals that honour those functions.
A pivot is just a DataFrame: slice it to filter, sort_values it to rank, and reach for pd.crosstab when you only need counts or share-of-total percentages.
Flatten the MultiIndex columns to plain strings before to_excel() so headers export as readable text instead of tuples, and remember that pandas 3.0 defaults categorical axes to observed=True.
The real payoff is repeatability — the same script runs against next month's export without anyone opening a spreadsheet.

Frequently asked questions

What's the difference between pivot_table() and DataFrame.pivot()? When multiple rows share the same index/columns combination, pivot_table aggregates them with aggfunc, while pivot() raises a ValueError on duplicate entries. Set aggfunc explicitly so the rollup is intentional.

Can I aggregate different metrics with different functions? Yes — pass a dict to aggfunc, like {"revenue": "sum", "units_sold": "mean"}. Each value column then uses its own function, and margins=True totals respect each one.

How do I get a totals row and column? Pass margins=True, and label the totals with margins_name (for example "Total"). The margins honour each metric's own aggfunc.

Why do my exported column headers come out as tuples? A pivot with multiple value columns produces a MultiIndex, and to_excel() renders it literally. Flatten it before writing with something like ["_".join(map(str, col)).strip() for col in export.columns].

Why are some category combinations missing from the result in pandas 3.0? When index or columns is a categorical dtype, the default is now observed=True, so only combinations present in the data appear. Pass observed=False to show every category level even when it has no rows.

Up to the parent workflow: Advanced Data Transformation and Cleaning.
For the focused, single-function recipe, see Create Pivot Table from Excel with Pandas.
To ship the pivot as a clean, styled sheet, see Export a Pandas Pivot Table to Excel (Formatted).
To enrich transactions with lookup tables before pivoting, see Merging and Joining Excel DataFrames.
To handle gaps before aggregating, see Handling Missing Data in Excel Reports.