Joins, grouping, and aggregation
Join and aggregate Polars DataFrames with group_by, windows, and as-of joins. Reshape tables and work with lists, structs, and nested JSON.
Group and aggregate sales#
Grouping collects related rows; aggregation computes one result per group.
group_by("customer") creates one group for each customer. agg summarizes each group. pl.len() counts rows including missing values; pl.col(...).count() counts non-null values. n_unique() counts distinct values and includes null as a distinct value; drop null first when “known unique customers” is the intended metric. Grouping without aggregation is not a finished report.
import polars as pl
df = pl.DataFrame(
{
"region": ["Dhaka", "Dhaka", "Dhaka", "Sylhet"],
"order": [1, 1, 2, 3],
"customer": ["A", "A", None, "B"],
"cents": [2000, 500, 1000, 3000],
"paid": [True, True, False, True],
}
)
report = (
df.group_by("region")
.agg(
pl.len().alias("line_rows"),
pl.col("order").n_unique().alias("orders"),
pl.col("customer").drop_nulls().n_unique().alias("known_customers"),
pl.col("cents").sum().alias("gross_cents"),
pl.col("cents").filter(pl.col("paid")).sum().alias("paid_cents"),
pl.col("cents").mean().alias("mean_line_cents"),
)
.sort("region")
)
print(report.to_dicts())[{'region': 'Dhaka', 'line_rows': 3, 'orders': 2, 'known_customers': 1, 'gross_cents': 3500, 'paid_cents': 2500, 'mean_line_cents': 1166.6666666666667}, {'region': 'Sylhet', 'line_rows': 1, 'orders': 1, 'known_customers': 1, 'gross_cents': 3000, 'paid_cents': 3000, 'mean_line_cents': 3000.0}]Aggregation changes the unit from order lines to regions. Count distinct order IDs for order counts; calculate each order’s total before averaging order values. Empty or all-null sums can return zero, so retain observation counts when missing data must differ from a measured zero.
Official reference: Group and aggregate sales.
Joins and duplicate-key cardinality#
A join adds columns by matching keys; repeated keys can multiply rows.
A key is one or more columns identifying a match, such as customer ID. A join compares keys on the left and right tables. Cardinality describes how many rows can match: one-to-one (1:1), many-to-one (m:1), one-to-many (1:m), or many-to-many (m:m). If a key appears twice on each side, that key produces four joined rows. This can inflate money totals without any parsing error.
| Join | Which rows survive |
|---|---|
inner | Only keys with matches on both sides. |
left | Every left row; unmatched right values are null. |
right | Every right row; unmatched left values are null. |
full | All rows from either side, with nulls where unmatched. |
semi | Left rows that have a right match; no right columns and no match multiplicity. |
anti | Left rows with no right match. |
cross | Every left/right combination; N × M rows. |
import polars as pl
orders = pl.DataFrame(
{"order": [1, 2, 3], "customer": ["A", "A", "B"], "cents": [100, 200, 300]}
)
customers = pl.DataFrame({"customer": ["A", "B"], "region": ["Dhaka", "Sylhet"]})
print(
"lookup:",
orders.join(customers, on="customer", how="left", validate="m:1")
.sort("order")
.to_dicts(),
)
bad = pl.concat([customers, customers.filter(pl.col("customer") == "A")])
print("unprotected row count:", orders.join(bad, on="customer").height)
try:
orders.join(bad, on="customer", validate="m:1")
except pl.exceptions.ComputeError:
print("validated join: duplicate customer key rejected")lookup: [{'order': 1, 'customer': 'A', 'cents': 100, 'region': 'Dhaka'}, {'order': 2, 'customer': 'A', 'cents': 200, 'region': 'Dhaka'}, {'order': 3, 'customer': 'B', 'cents': 300, 'region': 'Sylhet'}]
unprotected row count: 5
validated join: duplicate customer key rejectedimport polars as pl
left = pl.DataFrame({"key": [None, "A"]}, schema={"key": pl.String})
right = pl.DataFrame(
{"key": [None, "A"], "label": ["unknown", "known"]},
schema={"key": pl.String, "label": pl.String},
)
print("default matches:", left.join(right, on="key").height)
print("nulls_equal matches:", left.join(right, on="key", nulls_equal=True).height)default matches: 1
nulls_equal matches: 2Match key types before joining. Use left_on/right_on for differently named keys, a list for composite keys, and suffix for conflicting non-key column names. coalesce=True combines join-key columns, especially useful for full joins. Do not assume default join order; sort explicitly. Validate cardinality before trusting totals. For a streaming pipeline, check key uniqueness in an explicit quality query before joining; do not assume every validate mode is supported by every execution path.
Official reference: Joins and duplicate-key cardinality.
Reconcile transactions from two systems#
Compare one row per transaction ID and report missing or differing records.
Reconciliation checks whether two systems describe the same transactions. First ensure each ID is unique on each side. A full join makes differences from both systems visible. A null amount means missing information, not automatically zero; filling it with zero too early can hide an unmatched transaction.
import polars as pl
ledger = pl.DataFrame({"tx": ["T1", "T2", "T3"], "ledger_cents": [1000, 2000, 3000]})
bank = pl.DataFrame({"tx": ["T1", "T2", "T4"], "bank_cents": [1000, 1900, 4000]})
comparison = ledger.join(bank, on="tx", how="full", coalesce=True, validate="1:1")
comparison = comparison.with_columns(
pl.when(pl.col("ledger_cents").is_null())
.then(pl.lit("bank_only"))
.when(pl.col("bank_cents").is_null())
.then(pl.lit("ledger_only"))
.when(pl.col("ledger_cents") != pl.col("bank_cents"))
.then(pl.lit("amount_diff"))
.otherwise(pl.lit("matched"))
.alias("status"),
(pl.col("ledger_cents") - pl.col("bank_cents")).alias("difference_cents"),
)
print(comparison.sort("tx").to_dicts())
print("ledger-only IDs:", ledger.join(bank, on="tx", how="anti")["tx"].to_list())
print(
"IDs seen by bank:",
ledger.join(bank, on="tx", how="semi").sort("tx")["tx"].to_list(),
)[{'tx': 'T1', 'ledger_cents': 1000, 'bank_cents': 1000, 'status': 'matched', 'difference_cents': 0}, {'tx': 'T2', 'ledger_cents': 2000, 'bank_cents': 1900, 'status': 'amount_diff', 'difference_cents': 100}, {'tx': 'T3', 'ledger_cents': 3000, 'bank_cents': None, 'status': 'ledger_only', 'difference_cents': None}, {'tx': 'T4', 'ledger_cents': None, 'bank_cents': 4000, 'status': 'bank_only', 'difference_cents': None}]
ledger-only IDs: ['T3']
IDs seen by bank: ['T1', 'T2']These inputs have non-null amounts, so a null after the join identifies an absent record. If source amounts can be null, add presence flags before joining. Match by transaction ID; aggregate instalments or add a line key when IDs repeat.
Official reference: Reconcile transactions from two systems.
Window functions and row-level context#
A window computes within a group while retaining individual rows.
Unlike group_by, a window function usually returns a value for each original row. over("customer") defines the group. A cumulative total adds observations in sequence; a rank orders values within a group; a shift reads a neighboring row. Order matters for running totals and previous values, so define it using order_by or a prior stable sort. A ties policy determines whether equal values share a rank.
import polars as pl
df = pl.DataFrame(
{
"customer": ["A", "B", "A", "A"],
"order": [1, 2, 3, 4],
"cents": [100, 500, 200, 200],
}
)
result = df.with_columns(
pl.col("cents").sum().over("customer").alias("customer_total"),
pl.col("cents").cum_sum().over("customer", order_by="order").alias("running_total"),
pl.col("cents").shift(1).over("customer", order_by="order").alias("previous"),
pl.col("cents").rank("dense", descending=True).over("customer").alias("rank"),
).with_columns((pl.col("cents") / pl.col("customer_total")).alias("share"))
print(result.sort("order").to_dicts())[{'customer': 'A', 'order': 1, 'cents': 100, 'customer_total': 500, 'running_total': 100, 'previous': None, 'rank': 2, 'share': 0.2}, {'customer': 'B', 'order': 2, 'cents': 500, 'customer_total': 500, 'running_total': 500, 'previous': None, 'rank': 1, 'share': 1.0}, {'customer': 'A', 'order': 3, 'cents': 200, 'customer_total': 500, 'running_total': 300, 'previous': 100, 'rank': 1, 'share': 0.4}, {'customer': 'A', 'order': 4, 'cents': 200, 'customer_total': 500, 'running_total': 500, 'previous': 200, 'rank': 1, 'share': 0.4}]The window total is repeated on each customer’s rows; summing it again would double-count spend. Dense ranking gives equal values the same rank. The first order in each customer has no previous value.
Official reference: Window functions and row-level context.
Calendar summaries, rolling windows, and missing days#
Time grouping can use fixed calendar boundaries or a moving interval per observation.
group_by_dynamic assigns timestamps to intervals such as calendar months. every is the distance between window starts; period is the window width and defaults to every. closed chooses which boundary is included; label chooses the timestamp representing the group. Windows can overlap when period is larger than every. The time column must be sorted, within groups when applicable.
A rolling window moves with each observation. A three-row rolling mean and a three-day rolling mean answer different questions when days are missing. Dynamic grouping emits windows with observations, not a complete calendar with empty groups. Use an explicit date grid or upsample when you need missing dates.
import polars as pl
df = pl.DataFrame(
{"day": ["2026-08-31", "2026-09-01", "2026-09-03"], "cents": [100, 200, 300]}
)
df = df.with_columns(pl.col("day").str.to_date()).sort("day")
monthly = df.group_by_dynamic("day", every="1mo", closed="left", label="left").agg(
pl.col("cents").sum()
)
print("monthly:", monthly.to_dicts())
rolling = df.rolling(index_column="day", period="2d", closed="right").agg(
pl.col("cents").sum().alias("last_two_days")
)
print("rolling:", rolling.to_dicts())
grid = df.upsample(time_column="day", every="1d").with_columns(
pl.col("cents").fill_null(0)
)
print("filled daily grid:", grid.to_dicts())monthly: [{'day': datetime.date(2026, 8, 1), 'cents': 100}, {'day': datetime.date(2026, 9, 1), 'cents': 500}]
rolling: [{'day': datetime.date(2026, 8, 31), 'last_two_days': 100}, {'day': datetime.date(2026, 9, 1), 'last_two_days': 300}, {'day': datetime.date(2026, 9, 3), 'last_two_days': 300}]
filled daily grid: [{'day': datetime.date(2026, 8, 31), 'cents': 100}, {'day': datetime.date(2026, 9, 1), 'cents': 200}, {'day': datetime.date(2026, 9, 2), 'cents': 0}, {'day': datetime.date(2026, 9, 3), 'cents': 300}]The September window is [September 1, October 1), so both September records contribute. For September 3 the rolling interval is (September 1, September 3], so the September 1 value is excluded. Filling September 2 with zero is correct only if missing means no sales, not a missing export. For per-region calendars, construct the grid per region and avoid filling absent measurements without evidence.
Official reference: Calendar summaries, rolling windows, and missing days.
As-of joins and effective prices#
Match each event to the most recent available observation, rather than an exact timestamp.
An as-of join is a left-style match by key proximity. strategy="backward" chooses the latest right key at or before the left key; forward chooses the next; nearest chooses the closest. by restricts matching to a product or account. tolerance limits how far away a match may be. Both time keys must be sorted, and their types and time zones must agree.
import polars as pl
sales = pl.DataFrame(
{
"sku": ["BOOK", "BOOK", "BOOK"],
"ts": ["2026-09-01 09:30", "2026-09-01 11:00", "2026-09-03 11:00"],
"qty": [2, 1, 1],
}
)
prices = pl.DataFrame(
{
"sku": ["BOOK", "BOOK"],
"effective": ["2026-09-01 09:00", "2026-09-01 10:00"],
"price_cents": [1000, 1200],
}
)
sales = sales.with_columns(pl.col("ts").str.to_datetime()).sort(["sku", "ts"])
prices = prices.with_columns(pl.col("effective").str.to_datetime()).sort(
["sku", "effective"]
)
assert prices.unique(subset=["sku", "effective"]).height == prices.height
result = sales.join_asof(
prices,
left_on="ts",
right_on="effective",
by="sku",
strategy="backward",
tolerance="1d",
check_sortedness=False,
)
result = result.with_columns(
(pl.col("qty") * pl.col("price_cents")).alias("revenue_cents")
)
print(result.select("qty", "price_cents", "revenue_cents").to_dicts())[{'qty': 2, 'price_cents': 1000, 'revenue_cents': 2000}, {'qty': 1, 'price_cents': 1200, 'revenue_cents': 1200}, {'qty': 1, 'price_cents': None, 'revenue_cents': None}]Sort both sides by sku and their time keys. check_sortedness=False suppresses a check; it does not sort the data. Resolve duplicate right-hand timestamps first. The last sale has no price within the one-day tolerance. Forward and nearest matches may use prices set after a sale.
Official reference: As-of joins and effective prices.
Reshape with unpivot and pivot#
Long tables use one observation per row; wide tables put categories into separate columns.
Unpivot turns several value columns into a name column and a value column. Pivot turns distinct category values into output columns. Specify an aggregation if more than one row would fill the same pivot cell. Wide reports are convenient for readers; long tables are usually easier to filter, join, and summarize. Output column names must be known for a lazy plan.
import polars as pl
wide = pl.DataFrame(
{"region": ["Dhaka", "Sylhet"], "aug": [100, 200], "sep": [150, 250]}
)
long = wide.unpivot(
on=["aug", "sep"], index="region", variable_name="month", value_name="cents"
)
print("long:", long.sort(["region", "month"]).to_dicts())
report = long.pivot(
on="month",
index="region",
values="cents",
aggregate_function="sum",
sort_columns=True,
).sort("region")
print("wide:", report.to_dicts())
lazy_report = (
long.lazy()
.pivot(
on="month",
on_columns=["aug", "sep"],
index="region",
values="cents",
aggregate_function="sum",
)
.sort("region")
.collect()
)
assert report.equals(lazy_report)
print("lazy with known categories:", lazy_report.to_dicts())long: [{'region': 'Dhaka', 'month': 'aug', 'cents': 100}, {'region': 'Dhaka', 'month': 'sep', 'cents': 150}, {'region': 'Sylhet', 'month': 'aug', 'cents': 200}, {'region': 'Sylhet', 'month': 'sep', 'cents': 250}]
wide: [{'region': 'Dhaka', 'aug': 100, 'sep': 150}, {'region': 'Sylhet', 'aug': 200, 'sep': 250}]
lazy with known categories: [{'region': 'Dhaka', 'aug': 100, 'sep': 150}, {'region': 'Sylhet', 'aug': 200, 'sep': 250}]In Polars 1.44.2, LazyFrame.pivot requires known categories in on_columns. Eager pivot can discover them from data. For unknown categories, reduce and collect the data before pivoting, or use conditional aggregations for a known set.
Official reference: Reshape with unpivot and pivot.
Lists, arrays, and exploding line items#
A nested sequence keeps several same-type values inside one cell.
List stores a variable number of items per row; Array stores a fixed shape. For example, an order has any number of quantities, while a two-coordinate point always has two values. list.eval transforms items with pl.element(). Explode turns items into rows and repeats the other columns. Exploding two list columns together requires matching per-row lengths.
import polars as pl
df = pl.DataFrame(
{"order": [1, 2, 3], "quantities": [[2, 1], [], None]},
schema={"order": pl.Int64, "quantities": pl.List(pl.Int64)},
)
summary = df.with_columns(
pl.col("quantities").list.len().alias("items"),
pl.col("quantities").list.sum().alias("total_qty"),
pl.col("quantities").list.eval(pl.element() * 2).alias("doubled"),
)
print("summary:", summary.to_dicts())
print(
"actual items:",
df.filter(pl.col("quantities").list.len() > 0)
.explode("quantities", empty_as_null=False)
.to_dicts(),
)
points = pl.DataFrame({"xy": [[1, 2], [3, 4]]}, schema={"xy": pl.Array(pl.Int64, 2)})
print("fixed arrays:", points.select(pl.col("xy").arr.sum().alias("sum")).to_dicts())summary: [{'order': 1, 'quantities': [2, 1], 'items': 2, 'total_qty': 3, 'doubled': [4, 2]}, {'order': 2, 'quantities': [], 'items': 0, 'total_qty': 0, 'doubled': []}, {'order': 3, 'quantities': None, 'items': None, 'total_qty': None, 'doubled': None}]
actual items: [{'order': 1, 'quantities': 2}, {'order': 1, 'quantities': 1}]
fixed arrays: [{'sum': 3}, {'sum': 7}]An empty list has zero items; a null list is missing. Use empty_as_null=False to omit empty lists and keep_nulls=False to omit null lists during explode. Set these options explicitly when row counts matter; defaults differ across major releases.
Official reference: Lists, arrays, and exploding line items.
Structs and nested JSON#
A struct keeps named fields together in one typed cell.
A struct is a fixed set of named fields such as customer.id and customer.city. Each field has its own type. A list of structs models several order items in one row. struct.field extracts a field; unnest expands fields into columns. JSON is a text format for objects and arrays; decode it into an explicit nested schema before querying. Missing fields become null under the declared schema; extra fields are not a substitute for an explicit contract.
import polars as pl
payload = pl.DataFrame(
{
"json": [
'{"id":"O1","customer":{"id":"C1","city":"Dhaka"},"items":[{"sku":"BK","qty":2,"cents":500},{"sku":"PN","qty":1,"cents":100}]}',
'{"id":"O2","customer":{"id":"C2"},"items":[]}',
]
}
)
dtype = pl.Struct(
{
"id": pl.String,
"customer": pl.Struct({"id": pl.String, "city": pl.String}),
"items": pl.List(
pl.Struct({"sku": pl.String, "qty": pl.Int64, "cents": pl.Int64})
),
}
)
orders = payload.select(pl.col("json").str.json_decode(dtype).alias("order")).unnest(
"order"
)
orders = orders.with_columns(pl.col("customer").struct.field("city").alias("city"))
print(
"orders:",
orders.select(
"id", "city", pl.col("items").list.len().alias("item_count")
).to_dicts(),
)
lines = (
orders.filter(pl.col("items").list.len() > 0)
.explode("items", empty_as_null=False)
.unnest("items")
)
totals = lines.group_by("id").agg(
(pl.col("qty") * pl.col("cents")).sum().alias("total_cents")
)
report = (
orders.select("id").join(totals, on="id", how="left", validate="1:1").fill_null(0)
)
print("totals:", report.sort("id").to_dicts())orders: [{'id': 'O1', 'city': 'Dhaka', 'item_count': 2}, {'id': 'O2', 'city': None, 'item_count': 0}]
totals: [{'id': 'O1', 'total_cents': 1100}, {'id': 'O2', 'total_cents': 0}]Missing city fields become null. Exploding the items creates one row per actual item; the final left join retains orders with none. Zero is appropriate here because an empty list means no items. Malformed JSON raises an error; quarantine it before decoding if the batch should continue.
Official reference: Structs and nested JSON.
Categorical data and ordered enums#
Repeated text can be encoded as categories; ordered business stages need a defined order.
Categorical encodes repeated strings with category identifiers and infers the categories. Enum declares a fixed, ordered set, such as new, packed, shipped. Enum values outside that set fail with strict casting. Use enums for a controlled workflow; use categorical for repeated labels whose possible values are not known ahead of time. A high-cardinality field, where nearly every value is unique, may gain little from category encoding.
import polars as pl
stages = pl.Enum(["new", "packed", "shipped"])
df = pl.DataFrame(
{
"order": [1, 2, 3],
"stage": ["shipped", "new", "packed"],
"region": ["Dhaka", "Dhaka", "Sylhet"],
}
)
df = df.with_columns(
pl.col("stage").cast(stages), pl.col("region").cast(pl.Categorical)
)
print("stage order:", df.sort("stage")["stage"].to_list())
print("region type:", df.schema["region"])
try:
pl.Series(["unknown"]).cast(stages)
except pl.exceptions.InvalidOperationError:
print("unknown stage rejected")stage order: ['new', 'packed', 'shipped']
region type: Categorical
unknown stage rejectedRecent Polars releases share categorical mappings and sort categoricals lexically. Check compatibility when combining encoded frames or exchanging Arrow dictionaries. Keep physical category codes out of business IDs. Use Enum for a fixed order.
Official reference: Categorical data and ordered enums.
Help improve CodeRef
Allow Google Analytics to measure page visits and use of the guides? It uses cookies after you accept. Your choice won’t affect access to the site. Privacy notice