Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 37 ยท Joins, Groups and Windows

Window Functions with over()

Learn Polars window functions with over(): group-relative values, shares, ranks, running totals, lag and lead, and rolling averages without losing any rows.

A group_by collapses each group into one row. Often you want the opposite: keep every row but attach a group-level value to it, such as "this order's share of its region's revenue" or "this sale's rank within its rep". SQL calls this a window function. In Polars it is the .over() method, and it is one of the most elegant parts of the library.

The idea: write any aggregation expression, then add .over("key"). Polars computes the aggregate per group and broadcasts it back onto the original rows, so the output has the same length as the input.

Group value on every row

Python Region total next to each order

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Compare with pandas groupby().transform(): the idea is the same, but Polars needs no separate method, just over.

flowchart LR
    A["7 rows"] --> B["split by region"]
    B --> C["sum per group"]
    C --> D["broadcast back"]
    D --> E["7 rows + new column"]

A window expression splits, computes, and puts the result back in the original row order.

Shares, differences and z-scores

Once the group value sits next to each row, derived columns are plain arithmetic. You can even skip the temporary column and write it in one expression:

Python Share of region and distance from the region mean

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A group with only one row has a null standard deviation, so its z-score is null. That is correct: one value has no spread.

Ranking inside groups

rank has several methods that decide how ties are handled: "average" (default), "min", "max", "dense", "ordinal", "random".

Python Rank within each region

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Ana and Ben tie at 30. With "min" both get rank 1 and Cy gets rank 3; with "dense" Cy gets rank 2; with "ordinal" the tie is broken by row order, so every rank is unique. To keep "the top 2 per region", filter on the rank: df.filter(pl.col("revenue").rank("ordinal", descending=True).over("region") <= 2).

Order inside the window: shift, diff, cumulative sums

Window expressions respect the current row order of the DataFrame, so sort first when order matters. Then shift (lag/lead), diff, cum_sum, cum_max and friends work per group.

Python Previous value, change, running total

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

shift(1) gives the previous row (a lag) and shift(-1) the next (a lead). The first row of each group has no previous value, so you get null, not a value from another store: that is the point of over.

over has an order_by argument. pl.col("sales").cum_sum().over("store", order_by="day") sorts inside each group for this one expression, so you do not have to sort the whole table first.

Rolling windows

For "the average of the last 3 rows", use the rolling_* family. Combine with over to restart for each group.

Python 3-day moving average per store

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first two rows of each store are null in ma3 because three values are not yet available. min_samples=1 relaxes that. Other rolling functions include rolling_sum, rolling_min, rolling_max, rolling_std and rolling_median.

For windows defined by time rather than a row count ("the last 7 days"), use rolling(index_column="day", period="7d") inside .over-style code or DataFrame.rolling(...):

Python Time-based rolling window

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The window for a given day covers that day and the previous two (period="3d"), so March 5 only sees itself, since March 1 and 2 are too old.

over versus group_by

You want Use
One row per group group_by().agg()
Original rows plus a group value expr.over(...)
Original rows, but only for some groups filter(expr.over(...))

A mistake to avoid: over is for group-aligned results. An expression that returns a different length per group (for example unique()) needs mapping_strategy="join" (which returns a list per row) or "explode"; for ordinary window work you will not need it.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the length of pl.col('x').sum().over('g') in a with_columns call on an n-row frame?

    over() broadcasts the per-group result back to the original rows, so the result has the same length as the input.
  2. 2. Which rank method gives tied values the same rank and leaves no gaps afterwards (1, 1, 2)?

    'dense' ranks tied values equally and continues with the next integer. 'min' would give 1, 1, 3.
  3. 3. In a per-store lag with pl.col('sales').shift(1).over('store'), what does the first row of each store contain?

    Windows are computed inside each group, so the first row has no previous value and gets null.
  4. 4. How do you make a rolling mean restart for every group?

    Writing pl.col('x').rolling_mean(3).over('group') computes the rolling window separately inside each group.

Summary

  • expr.over("key") computes an aggregation per group and broadcasts it to every row.
  • Use it for shares, differences from group means, z-scores and per-group ranks.
  • shift, diff and cum_* work in row order, so sort first or pass order_by.
  • rolling_* functions give moving statistics; combine with over for per-group windows.
  • Time-based windows use rolling(index_column=..., period=...).

Next: Pivot, Unpivot and Concatenation