Use Polars Window Functions on Excel Data

Many spreadsheet calculations need a figure about a row's group on the row itself: each sale's share of its region's total, a product's rank within its category, a running total that restarts for each customer, the change from the same product's previous month. In Excel these are SUMIFS divided by a total, RANK.EQ with helper columns, or carefully anchored ranges copied down. A group_by in Polars collapses the rows, which is not what you want here. Window functions — expressions followed by .over(...) — compute per group and broadcast the result back to every row, keeping the table intact. This page applies them to monthly sales from Excel: shares, ranks, running totals, previous-period changes and top-N filters, with the options that control ordering. It belongs to Reading Excel with Polars and Arrow.

Prerequisites

pip install polars fastexcel

monthly_units.xlsx holds units sold per month, region and product for June to September 2026 — 24 rows in shuffled order, as exports often are. The examples were run with Polars 1.44.

Step 1 — Share of the group total

The classic window calculation: each row's units as a share of its region-month total. pl.col("Units").sum().over("Region", "Month") computes the total per region and month and repeats it on every row of that group:

import polars as pl

df = pl.read_excel("monthly_units.xlsx").sort("Month", "Region", "Product")
shares = df.with_columns(
    pl.col("Units").sum().over("Region", "Month").alias("Region total"),
).with_columns(
    (pl.col("Units") / pl.col("Region total") * 100).round(1).alias("Share %"),
)
print(shares.filter(pl.col("Month") == "2026-09"))

Every row keeps its own units and gains the region total and its share; the table still has 24 rows. The over partition can be several columns, here region and month, so each month's shares add up to 100 within each region. The Excel equivalent is a SUMIFS over the same keys in every row, which recalculates the whole column on every change — fine for hundreds of rows, slow for hundreds of thousands. A pandas groupby().transform("sum") does the same job; the comparison is at the end of this page.

Step 2 — Rank within a group

Ranks work the same way: rank evaluated over a partition ranks within each group:

ranked = df.with_columns(
    pl.col("Units").rank("ordinal", descending=True).over("Region", "Month").alias("Rank in region"),
    pl.col("Units").rank("dense", descending=True).over("Month").alias("Rank overall"),
)
print(ranked.filter(pl.col("Month") == "2026-09").sort("Rank overall"))

"ordinal" gives every row a distinct rank (ties broken by position), "dense" gives equal values the same rank with no gaps, and "min" matches Excel's RANK.EQ, where ties share the lowest rank and the next rank is skipped. Ranking over Month alone ranks across both regions; in September North's hoses and South's saws both sold 70, so the dense overall rank gives both a 5, while the ordinal rank within each region still separates every row. Ranks are integers starting at 1, as in Excel. The pandas and Excel ranking options are compared in RANK and PERCENTILE Formulas in pandas.

Step 3 — Running totals and change from the previous month

Running totals and period-on-period changes need the rows in order within each group. Sort first, then cum_sum and shift over the group:

trend = df.sort("Region", "Product", "Month").with_columns(
    pl.col("Units").cum_sum().over("Region", "Product").alias("Units YTD"),
    (pl.col("Units") - pl.col("Units").shift(1)).over("Region", "Product").alias("vs prev month"),
    (pl.col("Units") / pl.col("Units").shift(1) * 100 - 100).round(1).over("Region", "Product").alias("Growth %"),
)
print(trend.filter((pl.col("Region") == "North") & (pl.col("Product") == "Hose")))

shift(1) inside the window looks at the previous row of the same group, so the first month of each product has no previous value (null) rather than borrowing the last month of another product — the most common bug when this is done with a plain shift or a copied-down Excel formula. The cumulative sum restarts for each region and product. Because the result depends on row order, the explicit sort matters; a shuffled export without it produces running totals in a random order.

Rather than sorting the whole frame, you can give the window its own ordering with order_by:

same = df.with_columns(
    pl.col("Units").cum_sum().over("Region", "Product", order_by="Month").alias("Units YTD"),
).sort("Region", "Product", "Month")
print("same running totals:", same["Units YTD"].to_list() == trend["Units YTD"].to_list())

order_by sorts within each window for the calculation and puts the results back in the frame's original row order. The figures are identical to the sorted version. This is handy when the table must keep its existing order — the order of an export someone will compare against — while the calculation needs time order.

Step 4 — Top N per group with a window filter

Windows also work inside filter, which turns "the best-selling product per region each month" into one line:

top = df.filter(pl.col("Units") == pl.col("Units").max().over("Region", "Month"))
print(top.sort("Month", "Region"))

top2 = df.filter(pl.col("Units").rank("ordinal", descending=True).over("Region", "Month") <= 2)
print("top two per region and month:", top2.height, "rows")

Comparing each row with its group's maximum keeps the winners (ties would keep all tied rows); filtering on a rank keeps the top N. There is no intermediate table and no join back — the window does the grouping inside the filter. The pandas version of this is in Get the Top N Rows per Group in pandas.

Windows that return more than one value per group

So far each window expression returned either one value per group (a sum, a maximum) or one value per row (a rank, a running total). Polars handles both automatically: a single group value is repeated on every row, a per-row result is put back in place. A third kind — an expression that returns a list or a different number of values per group — needs a decision, made with mapping_strategy. The default, "group_to_rows", requires one result per row; "join" stores the group's whole result as a list on every row; "explode" returns one row per result and changes the frame's length. The common use of "join" on spreadsheet data is attaching a group's full set of values, such as a product's monthly series, to each of its rows for later comparison:

series = df.sort("Month").with_columns(
    pl.col("Units").over("Region", "Product", mapping_strategy="join").alias("All months"),
)
print(series.filter((pl.col("Region") == "South") & (pl.col("Product") == "Saw")).select("Month", "Units", "All months"))

Each South saw row now carries the list of all four months' units. That is rarely what a report shows directly, but it makes calculations such as "this month versus the product's best month" a list operation instead of a self-join. For most Excel-style work, though, the default strategy is the right one, and Polars raises an error rather than guessing when the shapes do not fit.

Step 5 — The pandas equivalents

Every calculation above has a pandas counterpart with groupby().transform() or grouped cumsum/shift/rank. Putting them side by side confirms the results match:

import pandas as pd

p = pd.read_excel("monthly_units.xlsx").sort_values(["Region", "Product", "Month"])
p["Region total"] = p.groupby(["Region", "Month"])["Units"].transform("sum")
p["Units YTD"] = p.groupby(["Region", "Product"])["Units"].cumsum()
p["vs prev month"] = p.groupby(["Region", "Product"])["Units"].diff()

pl_check = trend.join(shares.select("Month", "Region", "Product", "Region total"), on=["Month", "Region", "Product"])
merged = p.merge(pl_check.to_pandas(), on=["Month", "Region", "Product"], suffixes=("_pd", "_pl"))
for col in ["Region total", "Units YTD", "vs prev month"]:
    same = (merged[f"{col}_pd"].fillna(-1) == merged[f"{col}_pl"].fillna(-1)).all()
    print(f"{col:14} pandas == polars: {same}")

transform broadcasts a group result like over; grouped cumsum and diff correspond to cum_sum().over() and the shifted difference. The results agree column for column. The Polars versions read as one expression per column inside a single with_columns, which keeps longer pipelines tidy, and run in parallel across columns. For a full comparison of the two libraries on Excel work, see pandas vs Polars for Excel Workflows.

Common pitfalls

• Unsorted data for running totals and shifts. Sort, or pass order_by to over.
• A plain shift across groups. Put it inside the window so groups do not leak into each other.
• Using group_by when rows must stay. group_by collapses; over broadcasts.
• Averaging shares. Recompute shares from totals rather than averaging percentages.
• Expecting Excel's rank ties. Choose the rank method (min matches RANK.EQ).

Conclusion

Polars window functions — any expression followed by .over(keys) — compute per group and write the result onto every row: shares of a group total with sum().over(), ranks with rank().over(), running totals with cum_sum().over(), changes from the previous period with shift(1).over(), and top-N filters by using a window inside filter. Sort the data or pass order_by whenever the result depends on order, and remember that pandas' groupby().transform() family gives the same results when you need them there.

Frequently asked questions

What does over() do in Polars? It evaluates the expression separately for each group defined by the given columns and maps the result back to every row of that group, so the DataFrame keeps all its rows.

How do I calculate percent of group total in Polars? Divide the column by its sum over the group: take the column, call sum, then over with the group columns, and divide the original column by that.

How do I compute a running total per group in Polars? Use cum_sum().over(group columns) on data sorted by date, or pass order_by="Month" to over so the window is ordered internally.

How do I keep the top row per group in Polars? Filter where the value equals its maximum over the group, or where its rank over the group is at most N.

Related

• Up one level: Reading Excel with Polars and Arrow — fast Excel reading with Polars.
• Calculate Percent of Total per Group in pandas — the pandas version of Step 1.
• Running Totals and Year-over-Year Growth in pandas — the pandas version of Step 3.
• Clean Wide Excel Sheets with Polars Column Selectors — more Polars expressions for spreadsheets.