Window functions¶
A window function computes over a group of rows and gives an answer back on every
row, so the frame keeps its height. GroupBy reduces each group to one row; a window
keeps the rows and puts each group's answer beside them.
The examples read the quick start's orders, and a series of two tickers' closing prices over six trading days, a weekend before the sixth:
func prices() *ursus.LazyFrame {
return ursus.ScanCSV("testdata/prices.csv",
ursus.WithSchemaOverrides(map[string]ursus.DataType{"date": ursus.Date}))
}
An aggregate beside every row¶
Over(keys...) computes an expression within each partition of rows with the same
keys. An aggregate under Over is its partition's value on each of its rows, which an
expression can then use, here for each order's share of its region:
qty, region := ursus.Col("qty"), ursus.Col("region")
df, err := sales().
Select(
ursus.Col("order_id"),
region,
qty,
qty.Sum().Over(region).Alias("region_qty"),
qty.Div(qty.Sum().Over(region)).Round(2).Alias("share"),
qty.Sum().Over().Alias("all_qty"),
).
Collect(context.Background())
if err != nil {
log.Fatal(err)
}
fmt.Println(df)
shape: (8, 6)
┌──────────┬─────────┬───────┬────────────┬─────────┬─────────┐
│ order_id │ region │ qty │ region_qty │ share │ all_qty │
│ Int64 │ String │ Int64 │ Int128 │ Float64 │ Int128 │
├──────────┼─────────┼───────┼────────────┼─────────┼─────────┤
│ 1 │ "north" │ 10 │ 20 │ 0.5 │ 53 │
│ 2 │ "south" │ 4 │ 19 │ 0.21 │ 53 │
│ 3 │ "north" │ 7 │ 20 │ 0.35 │ 53 │
│ 4 │ "east" │ 2 │ 14 │ 0.14 │ 53 │
│ 5 │ "south" │ 9 │ 19 │ 0.47 │ 53 │
│ 6 │ "east" │ 12 │ 14 │ 0.86 │ 53 │
│ 7 │ "north" │ 3 │ 20 │ 0.15 │ 53 │
│ 8 │ "south" │ 6 │ 19 │ 0.32 │ 53 │
└──────────┴─────────┴───────┴────────────┴─────────┴─────────┘
Over() with no keys is the whole frame as one partition: all_qty is the grand
total on every row. Rows keep their order, whatever the partitions.
Ordered windows¶
Cumulative functions, shifts and ranks depend on the rows' order. Without one of its
own a window takes the rows as they come. OverWith gives a partition its order
with a WindowSpec, whatever order the frame is in:
byTicker := ursus.WindowSpec{
PartitionBy: []ursus.Expr{ursus.Col("ticker")},
OrderBy: []ursus.SortKey{ursus.Asc(ursus.Col("date"))},
}
df, err := prices().
WithColumns(
ursus.Col("close").CumMax(false).OverWith(byTicker).Alias("high"),
ursus.Col("close").CumCount(false).OverWith(byTicker).Alias("day"),
).
Filter(ursus.Col("ticker").Eq("ACME")).
Collect(context.Background())
if err != nil {
log.Fatal(err)
}
fmt.Println(df)
shape: (6, 5)
┌────────────┬────────┬─────────┬─────────┬────────┐
│ date │ ticker │ close │ high │ day │
│ Date │ String │ Float64 │ Float64 │ Uint32 │
├────────────┼────────┼─────────┼─────────┼────────┤
│ 2026-01-05 │ "ACME" │ 10 │ 10 │ 1 │
│ 2026-01-06 │ "ACME" │ 11 │ 11 │ 2 │
│ 2026-01-07 │ "ACME" │ 10.5 │ 11 │ 3 │
│ 2026-01-08 │ "ACME" │ 12 │ 12 │ 4 │
│ 2026-01-09 │ "ACME" │ 12 │ 12 │ 5 │
│ 2026-01-12 │ "ACME" │ 13.5 │ 13.5 │ 6 │
└────────────┴────────┴─────────┴─────────┴────────┘
CumSum, CumProd, CumMin, CumMax and CumCount each take a reverse flag, to
accumulate from the end.
Shift, diff and percent change¶
Shift(n) moves values n rows later within each partition, nulls where they were;
Diff(n) is the difference from n rows before, and PctChange(n) the change as a
fraction:
t := ursus.Col("ticker")
df, err := prices().
WithColumns(
ursus.Col("close").Shift(1).Over(t).Alias("previous"),
ursus.Col("close").Diff(1).Over(t).Alias("change"),
ursus.Col("close").PctChange(1).Over(t).Round(3).Alias("pct"),
).
Filter(t.Eq("BOLT")).
Drop("ticker").
Collect(context.Background())
if err != nil {
log.Fatal(err)
}
fmt.Println(df)
shape: (6, 5)
┌────────────┬─────────┬──────────┬─────────┬─────────┐
│ date │ close │ previous │ change │ pct │
│ Date │ Float64 │ Float64 │ Float64 │ Float64 │
├────────────┼─────────┼──────────┼─────────┼─────────┤
│ 2026-01-05 │ 20 │ null │ null │ null │
│ 2026-01-06 │ 19 │ 20 │ -1 │ -0.05 │
│ 2026-01-07 │ 21 │ 19 │ 2 │ 0.105 │
│ 2026-01-08 │ 21 │ 21 │ 0 │ 0 │
│ 2026-01-09 │ 22 │ 21 │ 1 │ 0.048 │
│ 2026-01-12 │ 20 │ 22 │ -2 │ -0.091 │
└────────────┴─────────┴──────────┴─────────┴─────────┘
ShiftFill(n, value) shifts with a value instead of nulls.
Rank¶
Rank(method, descending) numbers the rows by value. The methods differ only on ties,
here two 9s and two 7s:
x := ursus.Col("points")
df, err := ursus.Frame(
ursus.Values("team", []string{"a", "b", "c", "d", "e"}),
ursus.Values("points", []int64{7, 9, 7, 3, 9}),
).WithColumns(
x.Rank(ursus.RankOrdinal, true).Alias("ordinal"),
x.Rank(ursus.RankMin, true).Alias("min"),
x.Rank(ursus.RankMax, true).Alias("max"),
x.Rank(ursus.RankDense, true).Alias("dense"),
x.Rank(ursus.RankAverage, true).Alias("average"),
).Collect(context.Background())
if err != nil {
log.Fatal(err)
}
fmt.Println(df)
shape: (5, 7)
┌────────┬────────┬─────────┬────────┬────────┬────────┬─────────┐
│ team │ points │ ordinal │ min │ max │ dense │ average │
│ String │ Int64 │ Uint32 │ Uint32 │ Uint32 │ Uint32 │ Float64 │
├────────┼────────┼─────────┼────────┼────────┼────────┼─────────┤
│ "a" │ 7 │ 3 │ 3 │ 4 │ 2 │ 3.5 │
│ "b" │ 9 │ 1 │ 1 │ 2 │ 1 │ 1.5 │
│ "c" │ 7 │ 4 │ 3 │ 4 │ 2 │ 3.5 │
│ "d" │ 3 │ 5 │ 5 │ 5 │ 3 │ 5 │
│ "e" │ 9 │ 2 │ 1 │ 2 │ 1 │ 1.5 │
└────────┴────────┴─────────┴────────┴────────┴────────┴─────────┘
| Method | Ties get |
|---|---|
RankOrdinal |
distinct ranks, in the rows' order |
RankMin |
the lowest rank of the tie |
RankMax |
the highest rank of the tie |
RankDense |
the same rank, with no gap after them |
RankAverage |
the mean of the tie's ranks, as a Float64 |
Under Over, a rank is within its partition: the expressions
page ranks each order in its region. A null's rank is null, and the values are ranked
among themselves, as Polars ranks them. Changed in 0.6.7: a null ranked as the least
value before, and took a position.
Rolling windows by rows¶
RollingMean(n) is the mean of each row and the n − 1 before it, and so on for
RollingSum, RollingMin, RollingMax, RollingStd, RollingVar, RollingMedian,
RollingQuantile, RollingSkew, RollingKurtosis and RollingRank:
t, c := ursus.Col("ticker"), ursus.Col("close")
df, err := prices().
WithColumns(
c.RollingMean(3).Over(t).Alias("mean3"),
c.RollingMean(3, ursus.MinSamples(1)).Over(t).Alias("mean3_min1"),
c.RollingMax(3, ursus.RollingCenter()).Over(t).Alias("max3_centred"),
).
Filter(t.Eq("ACME")).
Drop("ticker").
Collect(context.Background())
if err != nil {
log.Fatal(err)
}
fmt.Println(df)
shape: (6, 5)
┌────────────┬─────────┬────────────────────┬────────────────────┬──────────────┐
│ date │ close │ mean3 │ mean3_min1 │ max3_centred │
│ Date │ Float64 │ Float64 │ Float64 │ Float64 │
├────────────┼─────────┼────────────────────┼────────────────────┼──────────────┤
│ 2026-01-05 │ 10 │ null │ 10 │ null │
│ 2026-01-06 │ 11 │ null │ 10.5 │ 11 │
│ 2026-01-07 │ 10.5 │ 10.5 │ 10.5 │ 12 │
│ 2026-01-08 │ 12 │ 11.166666666666666 │ 11.166666666666666 │ 12 │
│ 2026-01-09 │ 12 │ 11.5 │ 11.5 │ 13.5 │
│ 2026-01-12 │ 13.5 │ 12.5 │ 12.5 │ null │
└────────────┴─────────┴────────────────────┴────────────────────┴──────────────┘
- a window needs all n values by default, so the first n − 1 rows are null;
MinSamples(k)answers from k; RollingCenter()centres the window on its row.
New in 0.6.7: RollingMap(name, type, n, fn) calls a Go function on each row's
window, as Polars' rolling_map; see Go functions.
Rolling windows by time¶
A window by rows counts rows; a window by time counts time. RollingSumBy(by,
period) sums the rows whose by lies in the period up to each row's, (t − period,
t] by default. Across the weekend the two differ: Monday's three rows reach back to
Wednesday, and its three days only to Saturday.
t, c := ursus.Col("ticker"), ursus.Col("close")
df, err := prices().
WithColumns(
c.RollingSum(3, ursus.MinSamples(1)).Over(t).Alias("last_3_rows"),
c.RollingSumBy(ursus.Col("date"), ursus.Every("3d")).Over(t).
Alias("last_3_days"),
).
Filter(t.Eq("ACME")).
Drop("ticker").
Collect(context.Background())
if err != nil {
log.Fatal(err)
}
fmt.Println(df)
shape: (6, 4)
┌────────────┬─────────┬─────────────┬─────────────┐
│ date │ close │ last_3_rows │ last_3_days │
│ Date │ Float64 │ Float64 │ Float64 │
├────────────┼─────────┼─────────────┼─────────────┤
│ 2026-01-05 │ 10 │ 10 │ 10 │
│ 2026-01-06 │ 11 │ 21 │ 21 │
│ 2026-01-07 │ 10.5 │ 31.5 │ 31.5 │
│ 2026-01-08 │ 12 │ 33.5 │ 33.5 │
│ 2026-01-09 │ 12 │ 34.5 │ 34.5 │
│ 2026-01-12 │ 13.5 │ 37.5 │ 13.5 │
└────────────┴─────────┴─────────────┴─────────────┘
A period is an Every interval: "3d", "2h", "1mo", or a combination such as
"1d12h"; m is minutes and mo months. WindowClosed says which ends of the window
belong to it.
The By forms take a Date, Datetime or Time column, which need not be sorted:
a window is found by value, and rows tied with a row's instant are in its window.
Unlike the windows by rows they answer from one value by default. New in 0.6.7:
RollingRankBy(by, period, method) ranks each row's value among its window's, as
Polars' rolling_rank_by; over one partition past a memory limit it is refused, as
its window's values are not yet walked. New in 0.6.7: by
can be an integer of 32 or 64 bits, a step rather than an instant, whose period counts
it in Polars' integer unit: Every("3i") is three steps, as in
Dates and times.
New in 0.6.7: e.Rolling(index, o) is any aggregate expression over each row's
window, as Polars' Expr.rolling: Col("x").Max().Sub(Col("x").Min()).Rolling(Col("t"),
ursus.RollingOptions{Period: ursus.Every("2d")}) is each row's range over the two days
ending at it, the window placed as the frame's Rolling places it, and Over(keys)
rolls within each partition. Where every aggregate in it is a sum, a mean, a minimum,
a maximum, a variance, a standard deviation, a median or a quantile, and no offset is
given, each is rolled by its own window above, found by value, so the index need not
be sorted, and in an aggregation it is a list a group. Anything else, a row's value,
which is the list of its window's, or a first, is rolled by the frame's rolling group,
whose index is checked sorted, within each partition under Over, and is refused in
an aggregation; under Over it holds its windows until the input ends.
Exponentially weighted¶
EwmMean, EwmStd, EwmVar and EwmSum weight recent rows more. The decay is
EwmAlpha, EwmSpan, EwmCom or EwmHalfLife, as Polars' parameters are, and
EwmAdjust(false) uses the recursive form:
t, c := ursus.Col("ticker"), ursus.Col("close")
df, err := prices().
WithColumns(
c.EwmMean(ursus.EwmAlpha(0.5)).Over(t).Round(4).Alias("ewm"),
c.EwmMean(ursus.EwmAlpha(0.5), ursus.EwmAdjust(false)).Over(t).Round(4).
Alias("ewm_unadjusted"),
).
Filter(t.Eq("ACME")).
Drop("ticker").
Collect(context.Background())
if err != nil {
log.Fatal(err)
}
fmt.Println(df)
shape: (6, 4)
┌────────────┬─────────┬─────────┬────────────────┐
│ date │ close │ ewm │ ewm_unadjusted │
│ Date │ Float64 │ Float64 │ Float64 │
├────────────┼─────────┼─────────┼────────────────┤
│ 2026-01-05 │ 10 │ 10 │ 10 │
│ 2026-01-06 │ 11 │ 10.6667 │ 10.5 │
│ 2026-01-07 │ 10.5 │ 10.5714 │ 10.5 │
│ 2026-01-08 │ 12 │ 11.3333 │ 11.25 │
│ 2026-01-09 │ 12 │ 11.6774 │ 11.625 │
│ 2026-01-12 │ 13.5 │ 12.6032 │ 12.5625 │
└────────────┴─────────┴─────────┴────────────────┘
EwmMeanBy(by, halfLife) decays by time between rows rather than by rows, or by the
steps of an integer by, its half-life such as Every("2i").
Without Over¶
A window function with no Over runs over the whole frame in the rows' order:
Col("close").CumSum(false) is a running total from the first row to the last.
A window inside a window¶
New in 0.6.7: a window function's input may hold another, and an aggregate, each
computed first in the outer window's partitions and order, as Polars computes them:
Col("x").Shift(1).CumSum(false).Over(Col("g")) shifts within each g and then sums
within each g, and Col("x").Sub(Col("x").Mean()).CumSum(false).Over(Col("g")) sums
each row's distance from its group's mean. A window with an Over of its own inside
another is computed within the outer one's partitions, so
Col("x").Sum().Over(Col("g")).CumSum(false).Over(Col("h")) sums g's values within
each h. An aggregate whose answer depends on the order, First, under an OrderBy is
refused, as an ordering over an aggregate is.
Under a memory limit¶
Windows spill to disk under a memory budget, over many partitions or one huge one, and give the answer they give in memory. A rolling window by time whose period holds more than the budget fails rather than exceed it. See Memory and spilling.