Before this: Pivot without inventing zeros
The idea
A pivot puts one value in each (row, column) cell. If the long table has two readings for the same entity and month, they collide.
Tools hide collisions: some keep the first value, some sum, some raise an error. Decide the rule from the business meaning: sum for amounts that add up, latest for corrected readings, or reject when two values contradict.
Count collisions before pivoting and report them.
Worked example
Input: (A, Jan, 5), (A, Jan, 7)
- Visual
Add step → Group and total → by entity, month → count → filter count > 1
- SQL (DuckDB)
SELECT entity, month, COUNT(*) FROM readings GROUP BY ALL HAVING COUNT(*) > 1
- pandas
df.groupby(["entity", "month"]).size().loc[lambda s: s > 1]
Result: A/Jan collides; apply the declared rule before pivoting
Common mistakes
- Letting the tool pick silently.
- Summing values that are corrections.
- Not reporting collisions.
Check yourself
Why is 'keep the first' a risky default?
It depends on row order, so results can change between runs and data is dropped silently.
Practice it
Pivot monthly values, keeping zeros and gaps apart.