Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Unpivot a Wide Excel Sheet with pandas melt

Turn a month-per-column spreadsheet into tidy rows — pd.melt, choosing id_vars, parsing the column names into real dates, and handling multi-level headers.

Spreadsheets grow sideways. A report starts with one column per month, and two years later it has twenty-four value columns, a new one added every reporting cycle, and every formula and chart has to be rewritten each time. Analysis wants the opposite shape: one row per observation, with the period as a value rather than a column name. pd.melt performs that reshape in one call — and the details that matter are which columns you name as identifiers, and turning the header text back into real dates. This guide covers both. It extends Creating Pivot Tables from Excel Data.

Wide to long: every value cell becomes a row On the left a wide sheet with a region column and three month columns, so two regions and three months occupy six value cells in a two by three grid. On the right the same data in long form: six rows, each carrying a region, a month and a revenue value. The month, which was a column name, is now a value in its own column, so adding a fourth month adds rows rather than changing the table's shape. wide — one column per month region 2026-06 2026-07 2026-08 North 5150 4820 5402 South 4268 3980 3140 a new month changes the table's shape melt long — one row per observation region month revenue North · 2026-06 · 5150 South · 2026-06 · 4268 North · 2026-07 · 4820 … six rows in total a new month adds rows, not columns

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter

A wide sheet of the kind that accumulates:

Python
import pandas as pd

wide = pd.DataFrame({
    "region": ["North", "South", "West"],
    "owner": ["A. Chen", "B. Ortiz", "C. Novak"],
    "2026-06": [5150.00, 4268.50, 3511.25],
    "2026-07": [4820.50, 3980.25, 2711.50],
    "2026-08": [5402.75, 3140.75, 1820.00],
})
wide.to_excel("wide_report.xlsx", index=False)

Step 1 — Melt, naming the identifiers

melt splits the columns into two groups: the identifiers that stay as columns, and everything else, which collapses into a name column and a value column.

Python
import pandas as pd

wide = pd.read_excel("wide_report.xlsx")

long = wide.melt(
    id_vars=["region", "owner"],     # stay as columns
    var_name="month",                # the old column names land here
    value_name="revenue",            # the old cell values land here
)
print(long.head())
#   region     owner    month  revenue
# 0  North   A. Chen  2026-06  5150.00
# 1  South  B. Ortiz  2026-06  4268.50

Name the identifiers, not the values. melt also accepts value_vars, and using it looks equivalent — but it is not. Next month a 2026-09 column appears, and a value_vars list silently omits it while an id_vars list picks it up automatically:

Python
# Fragile: needs editing every month.
long = wide.melt(id_vars="region",
                 value_vars=["2026-06", "2026-07", "2026-08"])

# Robust: any new period column is included without a change.
long = wide.melt(id_vars=["region", "owner"],
                 var_name="month", value_name="revenue")

That single choice is the difference between a script that keeps working and one that quietly under-reports from the month somebody adds a column.

Step 2 — Turn the header text into real dates

The month column is text, so it sorts alphabetically and cannot be grouped by quarter. Parse it once, after melting — which is one conversion over a column rather than one per header:

Python
import pandas as pd

long["month"] = pd.to_datetime(long["month"], format="%Y-%m", errors="coerce")

unparsed = long["month"].isna().sum()
if unparsed:
    print(f"warning: {unparsed} row(s) had an unparseable period label")

Headers are rarely as tidy as 2026-06. Real ones look like Jun-26, Q3 2026 or Aug Actual, so extract the part that is a date before parsing:

Python
import pandas as pd

def parse_period(labels, fmt="%b-%y"):
    """Pull a period out of a messy column heading and parse it."""
    text = labels.astype("string").str.strip()
    extracted = text.str.extract(
        r"([A-Za-z]{3}[- ]?\d{2,4}|\d{4}[-/]\d{2})", expand=False
    )
    cleaned = extracted.str.replace(" ", "-", regex=False)
    return pd.to_datetime(cleaned, format=fmt, errors="coerce")

Once the column is real dates, everything in grouping Excel rows by month and quarter becomes available — quarterly rollups, fiscal periods, gap filling.

Step 3 — Handle a two-row header

Wide sheets often stack a period row above a measure row: Q1 spanning Units, Revenue, Margin, then Q2 doing the same. Read both header rows and melt on the levels.

A two-level header becomes two columns A wide sheet whose upper header row holds Q1 and Q2 spanning groups, and whose lower row holds Units and Revenue within each. Melting with two var_names produces a long frame with a period column carrying Q1 or Q2, a measure column carrying Units or Revenue, and a single value column. Each of the four original value columns per row therefore becomes four rows. two header rows — Q1 Q2 region Units Rev Units Rev North · 412 · 5150 · 388 · 4820 four columns, four rows per region region period measure value North · Q1 · Units · 412 North · Q1 · Rev · 5150 North · Q2 · Units · 388 … and North · Q2 · Rev · 4820
Python
import pandas as pd

stacked = pd.read_excel("quarterly_wide.xlsx", header=[0, 1], index_col=0)
stacked.columns.names = ["period", "measure"]

long = (
    stacked.stack(["period", "measure"], future_stack=True)
           .rename("value")
           .reset_index()
)
print(long.head())

Using stack here rather than melt is the exception to the earlier rule — stack understands index levels natively, and a MultiIndex column is exactly that. future_stack=True opts into the newer behaviour, which keeps rows whose value is missing rather than silently dropping them. Reading multi-level headers is covered in skipping rows and setting the header.

Step 4 — Drop or keep the empty cells

A sparse grid melts into mostly empty rows A wide grid of six regions by twelve months holds seventy-two value cells, of which only twenty-four are populated. Melting produces one row per cell, so the long frame has seventy-two rows and forty-eight of them carry a null value. Dropping those immediately after the melt leaves twenty-four real observations, and every subsequent operation works on a third of the data. 6 regions × 12 months = 72 cells, 24 populated after melt 24 real 48 rows carrying nothing after dropna 24 real every later step works on a third of the rows drop only when a blank means "no data" — when it means zero, fill instead

A wide grid is usually sparse: not every region has a value in every month. Melting turns each empty cell into a row with a NaN value, which can multiply the row count considerably.

Python
import pandas as pd

long = wide.melt(id_vars=["region", "owner"],
                 var_name="month", value_name="revenue")

print(f"{len(long)} rows, {long['revenue'].isna().sum()} of them empty")

# Keep only real observations — usually right for analysis.
observed = long.dropna(subset=["revenue"])

# Or keep them, when an absent month genuinely means zero.
zeroed = long.fillna({"revenue": 0})

Choose deliberately. Dropping is right when a blank means "no data recorded"; filling with zero is right when it means "nothing happened". Getting it backwards either understates a total or invents activity — the distinction developed in finding and reporting missing values.

Step 5 — Write the long form back

The long shape is what every downstream tool wants — pivot tables, charts, database loads:

Python
import pandas as pd

def write_long(long, path, sheet_name="Data", table_name="Observations"):
    """Write the unpivoted frame as a named table, ready to pivot from."""
    with pd.ExcelWriter(path, engine="xlsxwriter",
                        date_format="yyyy-mm-dd") as writer:
        long.to_excel(writer, sheet_name=sheet_name, index=False)
        sheet = writer.sheets[sheet_name]

        sheet.add_table(0, 0, len(long), len(long.columns) - 1,
                        {"name": table_name,
                         "columns": [{"header": str(c)} for c in long.columns],
                         "style": "Table Style Medium 2"})
        money = writer.book.add_format({"num_format": "#,##0.00"})
        sheet.set_column("A:B", 16)
        sheet.set_column("C:C", 13)
        sheet.set_column("D:D", 14, money)
        sheet.freeze_panes(1, 0)

    return path

write_long(observed, "long_report.xlsx")

Writing it as a named table is deliberate: a reader can then insert a pivot over it and reproduce the original wide view interactively, which is strictly better than the fixed wide sheet you started with. That round trip is described in adding a native Excel pivot table with Python.

Going back to wide, when a printed report needs it, is a pivot:

Python
back_to_wide = observed.pivot(index=["region", "owner"],
                              columns="month", values="revenue").reset_index()

Common pitfalls and fixes

SymptomCauseFix
New month column ignoredvalue_vars hard-codedName id_vars instead.
Identifier columns became valuesNot listed in id_varsAdd every identifier to the list.
Month column sorts alphabeticallyStill textParse it with to_datetime after melting.
Row count explodedEmpty grid cells became rowsdropna(subset=[value]), or fill deliberately.
KeyError on id_varsColumn name has whitespaceNormalise the headers first.
Two header rows produce tuplesMultiIndex columnsstack the levels, or flatten first.
Values became object dtypeMixed types across the wide columnsCoerce after melting, in one column.
Rows silently disappearedstack dropped missing valuesPass future_stack=True.

Performance and scale notes

melt allocates one long frame roughly the size of the wide one, and copies the identifier columns once per value column. A frame with two identifiers and twenty-four months therefore repeats each identifier twenty-four times — which is the memory cost of the long shape, not of the operation.

Three habits keep that manageable. Melt before cleaning the values, since cleaning one long column is far cheaper than cleaning twenty-four wide ones:

Python
import pandas as pd

long = wide.melt(id_vars=["region", "owner"],
                 var_name="month", value_name="revenue")
long["revenue"] = pd.to_numeric(long["revenue"], errors="coerce")   # one pass

Make the repeated identifiers categorical after melting. A region name repeated twenty-four times is stored once with an integer code, which on a large frame is a substantial saving:

Python
for name in ("region", "owner"):
    long[name] = long[name].astype("category")

Drop the empty cells early. A sparse grid can more than double the row count with rows carrying no information, and every subsequent operation pays for them.

For a genuinely large wide sheet, melt column-group by column-group and concatenate, so peak memory holds one slice rather than the whole long frame at once:

Python
import pandas as pd

identifiers = ["region", "owner"]
periods = [c for c in wide.columns if c not in identifiers]

pieces = []
for batch_start in range(0, len(periods), 6):
    batch = periods[batch_start:batch_start + 6]
    piece = wide[identifiers + batch].melt(
        id_vars=identifiers, var_name="month", value_name="revenue"
    ).dropna(subset=["revenue"])
    pieces.append(piece)

long = pd.concat(pieces, ignore_index=True)

Dropping inside each batch is what makes this worthwhile — the empty cells never accumulate. For files too large to read at all, the chunked approach in reading large Excel files in chunks composes with this cleanly, because melting is row-independent.

Conclusion

pd.melt turns a sideways-growing spreadsheet into the shape every analysis tool wants, and the single most important choice is to name the identifier columns rather than the value columns — that is what makes the script survive a new month being added. Parse the resulting period column into real dates so it sorts and groups properly, decide deliberately whether an empty grid cell means "no data" or "zero", and write the long form back as a named table so readers can pivot it into whatever view they need. The wide sheet was one view; the long form is the data.

Frequently asked questions

What is the difference between melt and stack?melt works on columns and returns a flat DataFrame with the former column names in a variable column. stack works on the index and returns a Series with a MultiIndex. melt is almost always the clearer choice when unpivoting a spreadsheet.

How do I keep more than one identifier column? Pass them all as a list to id_vars. Everything not listed there is treated as a value column, so listing the identifiers is safer than listing the values when new period columns get added each month.

The column names are months — how do I turn them into dates? Melt first, then parse the resulting variable column with pd.to_datetime and a format string matching the header text. Parsing after melting means one conversion over a column instead of one per header.

What if the sheet has two header rows? Read it with header set to a list so the columns become a MultiIndex, then stack the levels. pandas will produce one column per header level, which is usually exactly what you want.

Should I unpivot before or after cleaning? Unpivot first when the cleaning applies to values, because one long value column is far easier to clean than twelve wide ones. Clean the identifier columns before, since they are unaffected by the reshape.