TopolearnReports want wide tables while plotting and grouping want long ones, so you'll convert constantly.
Long (tidy) data has one row per observation: (store, month, sales). Wide data spreads one variable across columns: store, jan, feb, mar.
df.pivot(on="month", index="store", values="sales", aggregate_function="sum") makes it wide: each distinct value of on becomes a column. If several rows share the same index and on pair you must say how to combine them with aggregate_function ("sum", "mean", "first", "len", or an expression). Combinations with no rows become null, except with "sum" or "len", where they become 0.
Pivot is eager-only, since the output columns depend on the data. For a lazy query, compute the distinct values first, or use group_by with filtered aggregations like pl.col("sales").filter(pl.col("month") == "jan").sum().alias("jan").
df.unpivot(index="store", on=["jan", "feb"], variable_name="month", value_name="sales") goes back to long form. Leaving out on unpivots every non-index column, and selectors work: on=cs.numeric(). This used to be called melt, which was removed in Polars 2.
Rule of thumb: keep data long while computing (group_by, joins and plots are all easier) and pivot only at the end for presentation.
Example
import polars as pl
long = pl.DataFrame({
"store": ["A", "A", "A", "B"],
"month": ["jan", "feb", "feb", "jan"],
"sales": [10, 4, 6, 7],
})
wide = long.pivot(on="month", index="store", values="sales",
aggregate_function="sum").sort("store")
print(wide.rows()) # [('A', 10, 10), ('B', 7, 0)] (sum of nothing is 0)
print(wide.columns) # ['store', 'jan', 'feb']
back = wide.unpivot(index="store", variable_name="month",
value_name="sales").sort("store", "month")
print(back.rows())
# [('A', 'feb', 10), ('A', 'jan', 10), ('B', 'feb', 0), ('B', 'jan', 7)]Remember
pivot= long to wide;unpivot= wide to long (formerlymelt)- Pass
aggregate_functionwhen index/on pairs repeat - Compute in long form, pivot at the end for display
Watch out for
- Pivoting a column with thousands of distinct values and getting a frame with thousands of columns.