Pandas pivot: duplicates need an aggregation policy before reshaping

Operation contract

The receipt frame contains two amounts for one region and period. Direct pivot rejects that duplicate pair. The accepted contract groups each pair and sums it before reshaping, then reindexes to a declared period order. Missing combinations remain missing rather than being silently replaced with zero. The source frame stays unchanged.

Failure and ownership boundary

Choosing sum, last or mean changes the meaning of duplicate input; it is not merely a formatting fix. A wide result can allocate many mostly empty cells when key cardinalities grow. Predeclare output budgets before pivoting a received large dataset. Pandas groupby: retain missing keys and define all-null totals, Pandas nullable integers: missing amounts are not zero and Pandas joins: cardinality validation and missing lookup keys supply connected checks.

Tested environment

Dependency check: this program was executed on CPython 3.14.6 with pandas==3.0.6. Install these versions in a separate virtual environment. The download includes the recorded environment snapshot; no third-party package is part of the website runtime.

Working program

import pandas as pd

receipts = pd.DataFrame({"region": ["DEL", "DEL", "BOM"], "period": ["P1", "P1", "P2"], "amount": [125, 75, 250]})
try:
    receipts.pivot(index="region", columns="period", values="amount")
except ValueError:
    print("duplicate pair rejected")
grouped = receipts.groupby(["region", "period"], as_index=False)["amount"].sum()
wide = grouped.pivot(index="region", columns="period", values="amount").reindex(columns=["P1", "P2"])
print("accepted total:", int(wide.loc["DEL", "P1"]))
print("missing retained:", bool(pd.isna(wide.loc["DEL", "P2"])))
print("source rows:", len(receipts))

Output

duplicate pair rejected
accepted total: 200
missing retained: True
source rows: 3

Costs and limits

Grouping and reshaping cost depends on row count, key cardinalities and retained output cells. A two-dimensional result can approach the product of distinct key counts rather than merely the number of source rows.

Common Mistakes

• Duplicate pairs need an explicit domain aggregation rule.
• A missing combination is not automatically a measured zero.

Connected lessons

Pandas groupby: retain missing keys and define all-null totals, Pandas nullable integers: missing amounts are not zero, Pandas joins: cardinality validation and missing lookup keys.