Pandas Melt-Then-Pivot: The Shape I Always Need
I keep reaching for melt-then-pivot to reshape wide tables for charting. Here is the pandas-style transform written in pure stdlib Python so it runs anywhere, plus the multi-key pivot variant.
By @carlosherrera
May 10, 2026
·
Updated May 20, 2026
705 views
21
4.3 (14)
from __future__ import annotations
# A pandas-style 'melt' in 15 lines of stdlib Python.
# Wide input: each row is one entity, each non-id column is a measurement.
# Long output: one row per (entity, measurement_name, measurement_value).
def melt(rows, id_vars, value_vars=None):
if value_vars is None:
value_vars = [k for k in rows[0].keys() if k not in id_vars]
out = []
for row in rows:
base = {k: row[k] for k in id_vars}
for v in value_vars:
out.append({**base, 'variable': v, 'value': row[v]})
return out
wide = [
{'user': 'alice', 'jan': 12, 'feb': 18, 'mar': 25},
{'user': 'bob', 'jan': 3, 'feb': 9, 'mar': 14},
]
long = melt(wide, id_vars=['user'])
for r in long:
print(r)Melt is the 'unpivot' direction: every metric column becomes a row, with the original column name living in a variable field and its cell in value. I reach for it before charting, because most plotting libraries want one row per data point rather than a wide grid. The 15-line stdlib version is enough for any in-memory workload up to a few hundred thousand rows; past that I switch to pandas or polars. The contract matches pandas.melt(df, id_vars=...) exactly so when the dataset grows you can swap implementations without rewriting downstream code.
from __future__ import annotations
from collections import defaultdict
# The reverse: long rows (one (entity, variable, value) per row) → wide.
# Useful for building HTML tables or CSV exports from event-log data.
def pivot(rows, index, columns, value, fill=0):
grid = defaultdict(dict)
column_keys = set()
for row in rows:
key = tuple(row[k] for k in index)
col = row[columns]
grid[key][col] = row[value]
column_keys.add(col)
column_keys = sorted(column_keys)
out = []
for key, mapping in grid.items():
record = dict(zip(index, key))
for c in column_keys:
record[c] = mapping.get(c, fill)
out.append(record)
return out
events = [
{'user': 'alice', 'metric': 'jan', 'value': 12},
{'user': 'alice', 'metric': 'feb', 'value': 18},
{'user': 'bob', 'metric': 'jan', 'value': 3},
{'user': 'bob', 'metric': 'mar', 'value': 14},
]
wide = pivot(events, index=['user'], columns='metric', value='value', fill=0)
for r in wide:
print(r)Pivot fills the inverse role: turn (key, column-name, value) triples into a row-keyed dict where each column-name is its own field. The defaultdict(dict) keeps the per-key bag of measurements, and a final pass flattens it into uniform records with fill patching the holes. The most common bug I have hit is forgetting that fill matters: if alice has no 'mar' reading, the chart should show 0 (or None for 'no data', which is a different decision). Make the fill value explicit at the call site rather than letting it default.
from __future__ import annotations
from collections import defaultdict
# Real reporting tables key on more than one dimension.
# Here: pivot signups by (region, plan), with month as the column axis.
events = [
{'region': 'us', 'plan': 'free', 'month': '2025-01', 'signups': 42},
{'region': 'us', 'plan': 'free', 'month': '2025-02', 'signups': 51},
{'region': 'us', 'plan': 'pro', 'month': '2025-01', 'signups': 8},
{'region': 'eu', 'plan': 'free', 'month': '2025-01', 'signups': 19},
{'region': 'eu', 'plan': 'pro', 'month': '2025-02', 'signups': 4},
]
def pivot(rows, index, columns, value, fill=0, agg=sum):
bucket = defaultdict(lambda: defaultdict(list))
column_keys = set()
for row in rows:
key = tuple(row[k] for k in index)
col = row[columns]
bucket[key][col].append(row[value])
column_keys.add(col)
column_keys = sorted(column_keys)
out = []
for key, by_col in bucket.items():
record = dict(zip(index, key))
for c in column_keys:
record[c] = agg(by_col[c]) if c in by_col else fill
out.append(record)
return out
table = pivot(events, index=['region', 'plan'], columns='month', value='signups', fill=0)
for row in sorted(table, key=lambda r: (r['region'], r['plan'])):
print(row)The change from accordion 2 is small but powerful: the index is now a tuple (region + plan), and we collect a list of values per cell so the aggregator (sum, max, len, statistics.mean) decides how to combine duplicates. Real event logs always have duplicates, so the agg slot saves you from a silent overwrite bug. I have used this exact shape to build cohort signup grids and per-team latency tables; the only thing missing for production is column ordering control, which I usually add by passing a custom column-key sort. When the data outgrows memory I switch to a SQL GROUP BY or to polars; below that boundary, this function is dependency-free and obvious.