Turn a Pivot Table Export Back into Tidy Data

You ask for the sales data and receive a pivot table, pasted as values into a new workbook: regions and products down the side, years and quarters across the top in two header rows, a subtotal under each region, a grand total at the bottom and right, and the region name shown only on the first row of its block. It is perfect for reading and useless for analysis — you cannot filter it, join it or re-pivot it a different way. The underlying data can usually be rebuilt: each value cell is one observation of region, product, year, quarter and amount. This page reverses the pivot layout with pandas step by step, and finishes by recomputing the subtotals and grand total from the rebuilt data and comparing them with the ones in the export — the proof that nothing was lost or double-counted. It belongs to Extracting Data from Messy Excel Sheets.

Prerequisites

pip install pandas openpyxl

pivot_export.xlsx has a title row, a blank row, two header rows — years on the first (each year written once above its quarters), quarters on the second — then three region blocks with products, a bold subtotal row after each region, a grand-total row, and a grand-total column on the right. The region label appears only on the first row of each block, as pivot tables display it.

Step 1 — Read the sheet raw

Do not let pandas guess the header; read everything as a grid and look at it:

import pandas as pd

raw = pd.read_excel("pivot_export.xlsx", header=None)
print(raw.head(8).to_string())

With header=None, every row is data and the columns are numbered. Row 2 holds the years — "2025" once, "2026" once, with blanks to their right — row 3 the quarter labels and the "Region" and "Product" captions, and the values start at row 4. Depending on how the pivot was pasted, the year cells may hold numbers or digit-only text, so the code below converts them explicitly with int() rather than relying on either. Reading raw first makes every later step explicit about which rows and columns it uses, which matters because layouts like this shift when someone adds a title or a filter row.

Step 2 — Rebuild the column headers

The value columns are identified by a year and a quarter spread over two header rows, with the year written only once per group. Forward-fill the year row across, then pair it with the quarter row:

years = raw.iloc[2].ffill()
quarters = raw.iloc[3]
columns = []
for y, q in zip(years, quarters):
    if pd.isna(q) or q in ("Region", "Product"):
        columns.append(q if pd.notna(q) else "Total")
    else:
        columns.append(f"{int(y)} {q}")
body = raw.iloc[4:].copy()
body.columns = columns
print(columns)

Forward-filling the year row turns 2025, NaN, 2026, NaN … into 2025, 2025, 2026, 2026 …, so each quarter column knows its year. The "Region" and "Product" captions become the names of the label columns, and the unlabelled grand-total column is called "Total" so it can be dropped deliberately rather than by position. The same approach works for any two-level header; for headers with merged cells, see Read Excel with Multi-Row Headers in pandas.

Step 3 — Fill the row labels and drop the total rows

Pivot layouts show a region once per block. Forward-fill it down, then remove the subtotal and grand-total rows — but keep them aside, because they are the check in Step 5:

body["Region"] = body["Region"].ffill()
is_total = body["Region"].astype(str).str.contains(r"Total$", regex=True)
totals = body[is_total].copy()
detail = body[~is_total & body["Product"].notna()].copy()
print(detail[["Region", "Product"]].to_string(index=False))
print("total rows set aside:", totals["Region"].tolist())

After the forward fill, every product row carries its region. Subtotal rows are recognised by their label ending in "Total" — "North Total", "Grand Total" — the default wording of Excel pivot tables; if your export uses another language or custom labels, adjust the pattern. The extra condition Product.notna() drops any row with no product, which also catches total rows whose label was missed. Six detail rows remain: the real data. Removing subtotal rows from ordinary reports is covered in Remove Subtotal and Total Rows from an Excel Export.

Step 4 — Unpivot into one row per observation

Now melt the quarter columns into rows, drop the grand-total column, and split the column names back into year and quarter:

value_cols = [c for c in detail.columns if c not in ("Region", "Product", "Total")]
tidy = detail.melt(id_vars=["Region", "Product"], value_vars=value_cols, var_name="Period", value_name="Units")
tidy[["Year", "Quarter"]] = tidy["Period"].str.split(" ", expand=True)
tidy["Year"] = tidy["Year"].astype(int)
tidy["Units"] = pd.to_numeric(tidy["Units"])
tidy = tidy.drop(columns="Period").sort_values(["Region", "Product", "Year", "Quarter"]).reset_index(drop=True)
print(tidy.head(6).to_string(index=False))
print(tidy.shape)

Six product rows times six quarters give 36 observations, each with region, product, year, quarter and units — the shape the original data had before it was pivoted. pd.to_numeric makes sure the units are numbers; if the export had formatted values as text with thousands separators, convert them first as in Convert Excel Text Columns to Numbers with pandas. Blank cells in a pivot usually mean "no data" rather than zero; after melt they become NaN rows that you can drop with dropna(subset=["Units"]).

Step 5 — Prove it by recomputing the totals

The export contained its own checksums: the subtotals, the grand-total row and the grand-total column. Recompute them from the tidy data and compare:

tidy["Period"] = tidy["Year"].astype(str) + " " + tidy["Quarter"]
recomputed = tidy.pivot_table(index="Region", columns="Period", values="Units", aggfunc="sum")
recomputed = recomputed[value_cols]

expected = totals.set_index("Region")[value_cols].astype(int)
expected.index = expected.index.str.replace(" Total", "", regex=False)
grand_expected = expected.loc["Grand"]
regional_ok = (recomputed.loc[["North", "South", "West"]].astype(int).values == expected.drop("Grand").values).all()
grand_ok = (recomputed.sum().astype(int).values == grand_expected.values).all()
row_totals_ok = (detail.set_index(["Region", "Product"])["Total"].astype(int)
                 == tidy.groupby(["Region", "Product"])["Units"].sum()).all()
print("regional subtotals match:", regional_ok, "| grand total row matches:", grand_ok,
      "| row totals match:", row_totals_ok, "| grand total:", int(tidy["Units"].sum()))

Every regional subtotal, the grand-total row and each product's row total in the export equal the figures recomputed from the 36 tidy rows. That is strong evidence the rebuild is complete and correct: a missed row, a duplicated row or a misread header would break at least one of these sums. If a check fails, the export may have been filtered (pivot tables can hide items) or the subtotal labels may not be what you expected — both worth asking the sender about.

Variations you will meet

Pivot exports come in a few other shapes, and the same steps adapt. With several value fields — "Sum of Units" and "Sum of Revenue" side by side under each quarter — there is a third header level; forward-fill it like the year row, build names such as 2026 Q1|Units, and after melting split the name into period and measure, then pivot the measure back into columns so units and revenue sit side by side on each row. With the compact layout, where regions and products share one indented column, the region rows have no values and the product rows are indented; detect region rows by their empty value cells (or by the cell's indent, readable with openpyxl), forward-fill them into a new column, and drop them. And with row grand totals switched off, simply skip the row-total check. In every case, the export's remaining totals are the test of the rebuild.

Common pitfalls

• Letting pandas pick the header. Read with header=None and build headers explicitly.
• Forgetting the blank labels. Forward-fill region labels before filtering.
• Keeping subtotal rows. They double-count; identify them by their labels and set them aside.
• Year cells that are sometimes numbers, sometimes text. Convert them explicitly.
• Skipping the total check. Recompute the export's totals to prove nothing was lost.

Conclusion

A pasted pivot table can be turned back into data: read it without headers, forward-fill the year row and pair it with the quarter row to name each value column, forward-fill the row labels pivot layouts leave blank, set the subtotal and grand-total rows aside, and melt the value columns into one row per observation. Then recompute the subtotals and grand totals from the tidy data and compare them with the ones in the export — if they all match, the rebuild is complete. And next time, ask for the source data.

Frequently asked questions

How do I unpivot an Excel pivot table in Python? Read it with header=None, build column names from the header rows, forward-fill the row labels, drop subtotal and grand total rows, and use pandas melt to turn the value columns into rows.

How do I handle the blank row labels in a pivot table export? Forward-fill the label column with ffill so every row carries the label shown on the first row of its block.

How do I remove subtotal rows from a pivot export? Identify them by their labels, such as names ending in Total, keep them in a separate frame for checking, and filter them out of the data.

How can I check that the unpivoted data is correct? Recompute the subtotals and grand totals from the tidy data with a pivot table and compare them with the totals that were in the export.

Related

• Up one level: Extracting Data from Messy Excel Sheets — getting data out of awkward layouts.
• Unpivot a Wide Excel Sheet with pandas melt — the melt step on its own.
• Forward-Fill Blank Cells in Grouped Excel Reports — filling labels in other report layouts.
• Create a Pivot Table from Excel with pandas — going the other way.