Before this: Missing is not empty (and neither is zero)
The idea
Pivoting turns rows (entity, month, value) into one row per entity with a column per month. Cells with no source row are absent: nobody reported that month.
Many tools fill absent cells with 0 (SUM with ELSE 0, fillna(0)). That claims a measurement that never happened. Keep absent cells missing and keep reported zeros as 0.
Declare the output columns explicitly, and refuse to pivot if a cell would receive two values. Collisions need an aggregation rule first.
Worked example
Input: (A, Jan, 0) (A, Feb, missing) (B, Jan, 5) (B, Mar, 7)
- Visual
Convert value to int64 (keep missing) → Pivot month into Jan, Feb, Mar by entity
- SQL (DuckDB)
max(CASE WHEN month = 'Jan' THEN CAST(value AS BIGINT) END) AS "Jan"
- pandas
df.pivot(index="entity", columns="month", values="value").reindex(columns=["Jan", "Feb", "Mar"])
Result: A: 0, missing, missing · B: 5, missing, 7
Common mistakes
- ELSE 0 in conditional aggregation.
- fillna(0) after pivot.
- Letting a pivot silently keep the last of two values.
Check yourself
B has no Feb row. What goes in B's Feb cell?
Missing, not 0.
What if A has two Jan rows?
Stop: decide an aggregation rule before pivoting.
Practice it
Pivot monthly readings without filling gaps.