Skip to content

Aggregation and group-by

GroupBy(keys...) splits the rows by their keys, and Agg(exprs...) computes each expression once per group: one row per group comes out, its keys and its aggregates.

The aggregates

df, err := sales().
    GroupBy(ursus.Col("region")).
    Agg(
        ursus.Col("qty").Sum().Alias("sum"),
        ursus.Col("qty").Mean().Alias("mean"),
        ursus.Col("qty").Median().Alias("median"),
        ursus.Col("qty").Min().Alias("min"),
        ursus.Col("qty").Max().Alias("max"),
        ursus.Col("qty").Std(1).Round(2).Alias("std"),
    ).
    Sort(ursus.Asc(ursus.Col("region"))).
    Collect(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (3, 7)
┌─────────┬────────┬───────────────────┬─────────┬───────┬───────┬─────────┐
│ region  │ sum    │ mean              │ median  │ min   │ max   │ std     │
│ String  │ Int128 │ Float64           │ Float64 │ Int64 │ Int64 │ Float64 │
├─────────┼────────┼───────────────────┼─────────┼───────┼───────┼─────────┤
│ "east"  │ 14     │ 7                 │ 7       │ 2     │ 12    │ 7.07    │
│ "north" │ 20     │ 6.666666666666667 │ 7       │ 3     │ 10    │ 3.51    │
│ "south" │ 19     │ 6.333333333333333 │ 6       │ 4     │ 9     │ 2.52    │
└─────────┴────────┴───────────────────┴─────────┴───────┴───────┴─────────┘

The usual statistics are all there:

  • Sum, Mean, Median, Min and Max;
  • Std(ddof) and Var(ddof);
  • Quantile(q, interpolation);
  • Product;
  • Skew, Kurtosis and Entropy;
  • Corr, Cov and CorrSpearman of two columns.

Every aggregate skips nulls. A mean is over the values that are there, and a group with no values sums to null, not 0. An integer sum is an Int128, so it cannot overflow: cast it to Int64 if you want Polars' type.

Counting has several forms, each one value per group:

df, err := sales().
    GroupBy(ursus.Col("product")).
    Agg(
        ursus.Len().Alias("orders"),
        ursus.Col("region").NUnique().Alias("regions"),
        ursus.Col("region").First().Alias("first"),
        ursus.Col("region").Last().Alias("last"),
    ).
    Sort(ursus.Asc(ursus.Col("product"))).
    Collect(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (3, 5)
┌─────────┬────────┬─────────┬─────────┬─────────┐
│ product │ orders │ regions │ first   │ last    │
│ String  │ Uint64 │ Uint64  │ String  │ String  │
├─────────┼────────┼─────────┼─────────┼─────────┤
│ "apple" │ 3      │ 3       │ "north" │ "east"  │
│ "fig"   │ 2      │ 2       │ "south" │ "north" │
│ "pear"  │ 3      │ 3       │ "north" │ "south" │
└─────────┴────────┴─────────┴─────────┴─────────┘
  • Len() counts a group's rows;
  • Count() counts a column's values that are not null;
  • NUnique() counts its distinct values, a null not among them;
  • First() and Last() are its first and last value in the rows' order.

New in 0.6.7: Item(allowEmpty) is a group's one value, as Polars' item, and refuses a group of several, or of none unless allowEmpty, which makes it null. LowerBound() and UpperBound() are the least and greatest value of the column's type. IsSorted(descending, nullsLast) says whether a group's values come in order, NaN the greatest and the nulls all first, or all last; it reads the rows in their order, so a group-by holding it runs on one worker.

New in 0.6.7. First and Last take a value that may be null, so that First(a) and First(b) come from one row. FirstNonNull() and LastNonNull() skip the nulls, as Polars' first(ignore_nulls=True), so two of them may come from different rows. A group-by has each as a shorthand over every column, and All() gathers each column's values a group into a list, as Polars' group_by().all():

temps, read := []float64{0, 21.5, 22.0, 0}, []bool{false, true, true, false}
readings := ursus.Frame(
    ursus.Values("sensor", []string{"a", "a", "a", "b"}),
    ursus.ValuesNullable("temp", temps, read),
)
g := readings.GroupBy(ursus.Col("sensor")).MaintainOrder()
show(g.Agg(
    ursus.Col("temp").First().Alias("first"),
    ursus.Col("temp").FirstNonNull().Alias("first_seen"),
    ursus.Col("temp").LastNonNull().Alias("last_seen"),
))
show(g.All())
Output
shape: (2, 4)
┌────────┬─────────┬────────────┬───────────┐
│ sensor │ first   │ first_seen │ last_seen │
│ String │ Float64 │ Float64    │ Float64   │
├────────┼─────────┼────────────┼───────────┤
│ "a"    │ null    │ 21.5       │ 22        │
│ "b"    │ null    │ null       │ null      │
└────────┴─────────┴────────────┴───────────┘

shape: (2, 2)
┌────────┬──────────────────┐
│ sensor │ temp             │
│ String │ List(Float64)    │
├────────┼──────────────────┤
│ "a"    │ [null, 21.5, 22] │
│ "b"    │ [null]           │
└────────┴──────────────────┘

Keys

A key is any expression, not only a column. Alias a computed key to name it:

df, err := sales().
    GroupBy(ursus.Col("qty").Ge(8).Alias("bulk")).
    Agg(ursus.Len().Alias("orders"), ursus.Col("qty").Sum().Alias("qty")).
    Sort(ursus.Asc(ursus.Col("bulk"))).
    Collect(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (2, 3)
┌───────┬────────┬────────┐
│ bulk  │ orders │ qty    │
│ Bool  │ Uint64 │ Int128 │
├───────┼────────┼────────┤
│ false │ 5      │ 22     │
│ true  │ 3      │ 31     │
└───────┴────────┴────────┘

Several keys group by their combinations:

df, err := sales().
    GroupBy(ursus.Col("region"), ursus.Col("product")).
    Agg(ursus.Col("qty").Sum().Alias("qty")).
    Sort(ursus.Asc(ursus.Col("region")), ursus.Asc(ursus.Col("product"))).
    Collect(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (8, 3)
┌─────────┬─────────┬────────┐
│ region  │ product │ qty    │
│ String  │ String  │ Int128 │
├─────────┼─────────┼────────┤
│ "east"  │ "apple" │ 12     │
│ "east"  │ "pear"  │ 2      │
│ "north" │ "apple" │ 10     │
│ "north" │ "fig"   │ 3      │
│ "north" │ "pear"  │ 7      │
│ "south" │ "apple" │ 4      │
│ "south" │ "fig"   │ 9      │
│ "south" │ "pear"  │ 6      │
└─────────┴─────────┴────────┘

Groups come out in no promised order: they are built on every core at once. Sort the answer, as these examples do, or ask for the order the keys first appear in with MaintainOrder():

df, err := sales().
    GroupBy(ursus.Col("product")).
    MaintainOrder().
    Agg(ursus.Col("qty").Sum().Alias("qty")).
    Collect(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (3, 2)
┌─────────┬────────┐
│ product │ qty    │
│ String  │ Int128 │
├─────────┼────────┤
│ "apple" │ 26     │
│ "pear"  │ 15     │
│ "fig"   │ 12     │
└─────────┴────────┘

A null key is a group of its own, as in SQL's GROUP BY and Polars.

Conditional aggregates

An aggregate's input is an expression, so it can be narrowed or computed first:

df, err := sales().
    GroupBy(ursus.Col("region")).
    Agg(
        ursus.Col("qty").Filter(ursus.Col("product").Eq("apple")).Sum().
            Alias("apple_qty"),
        ursus.Col("qty").Ge(8).Sum().Alias("bulk_orders"),
        ursus.Col("product").MaxBy(ursus.Col("qty")).Alias("biggest"),
    ).
    Sort(ursus.Asc(ursus.Col("region"))).
    Collect(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (3, 4)
┌─────────┬───────────┬─────────────┬─────────┐
│ region  │ apple_qty │ bulk_orders │ biggest │
│ String  │ Int128    │ Int128      │ String  │
├─────────┼───────────┼─────────────┼─────────┤
│ "east"  │ 12        │ 1           │ "apple" │
│ "north" │ 10        │ 1           │ "apple" │
│ "south" │ 4         │ 1           │ "fig"   │
└─────────┴───────────┴─────────────┴─────────┘
  • Filter(pred) before an aggregate keeps a group's rows where pred is true: apple_qty sums only the apples;
  • a Bool sums as 0 and 1, so the sum of a condition counts the rows that meet it;
  • MaxBy(other) and MinBy(other) take a column's value where another column is largest or smallest: each region's biggest order's product.
  • New in 0.6.7: TopKBy(other, k) and BottomKBy(other, k) take k of them, a list a group, as Polars' top_k_by and bottom_k_by: each region's three biggest orders' products. A null in other comes last, and rows that tie on it are ordered by their values, the greater first for TopKBy, where Polars' sort orders them as it finds them.

Lists per group

Implode gathers a group's values into a list. Unique, Sort, Head and the other expressions that give several values give a list per group too:

df, err := sales().
    GroupBy(ursus.Col("region")).
    Agg(
        ursus.Col("order_id").Implode().Alias("orders"),
        ursus.Col("product").Unique().Sort().Alias("products"),
    ).
    Sort(ursus.Asc(ursus.Col("region"))).
    Collect(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (3, 3)
┌─────────┬─────────────┬──────────────────────────┐
│ region  │ orders      │ products                 │
│ String  │ List(Int64) │ List(String)             │
├─────────┼─────────────┼──────────────────────────┤
│ "east"  │ [4, 6]      │ ["apple", "pear"]        │
│ "north" │ [1, 3, 7]   │ ["apple", "fig", "pear"] │
│ "south" │ [2, 5, 8]   │ ["apple", "fig", "pear"] │
└─────────┴─────────────┴──────────────────────────┘

New in 0.6.7: any expression that is not an aggregate is each group's values as a list, as Polars answers it: Agg(Col("qty")) is each group's quantities, Agg(Col("qty").Mul(2)) doubled, and a window runs a group at a time, Agg(Col("qty").CumSum(false)) each group's running total, and Agg(Col("qty").CumSum(false).Max()) its greatest. An expression that mixes an aggregate with a row's value, Col("qty").Sub(Col("qty").Mean()), is each row's value less its group's mean, a list a group.

New in 0.6.7: an aggregate's input may hold another aggregate, computed over the group first, as Polars computes it: Col("qty").Sub(Col("qty").Mean()).Abs().Sum() is each group's sum of distances from its mean, and in a Select the frame's. Col("t").SortBy(Col("qty").Sub(Col("qty").Mean())).First() sorts by the same distances.

A list column can be exploded back into rows, or asked questions with List().

Filtering groups: having

New in 0.6.7. Having(predicates...) keeps the groups whose predicates all hold, each an aggregate of its group, as Polars' having and SQL's HAVING: the regions with more than two orders, here. It filters what Agg gives, the shorthands, and Head and Tail; a group whose predicate is null is left out.

show(sales().
    GroupBy(ursus.Col("region")).
    MaintainOrder().
    Having(ursus.Len().Gt(2)).
    Agg(ursus.Col("qty").Sum().Alias("qty")))
Output
shape: (2, 2)
┌─────────┬────────┐
│ region  │ qty    │
│ String  │ Int128 │
├─────────┼────────┤
│ "north" │ 20     │
│ "south" │ 19     │
└─────────┴────────┘

A predicate must be an aggregate and Boolean. Polars casts a number to a Boolean, so that a sum keeps every group not 0; here it is refused.

Each group's first and last rows

New in 0.6.7. GroupBy(keys).Head(n) keeps each group's first n rows as rows rather than lists, as Polars' group_by().head(n), and Tail(n) its last n. The key columns come first, the groups in the order each first appears, and each group's rows in the frame's order:

show(sales().
    Select(ursus.Col("region"), ursus.Col("product"), ursus.Col("qty")).
    GroupBy(ursus.Col("region")).
    Tail(1))
Output
shape: (3, 3)
┌─────────┬─────────┬───────┐
│ region  │ product │ qty   │
│ String  │ String  │ Int64 │
├─────────┼─────────┼───────┤
│ "north" │ "fig"   │ 3     │
│ "south" │ "pear"  │ 6     │
│ "east"  │ "apple" │ 12    │
└─────────┴─────────┴───────┘

They are a window over the keys and a sort, so they spill past a memory limit and keep their order there. Head(0) is no rows, where Polars gives a row of nulls for each group.

Every column at once

GroupBy(...) has shortcuts that aggregate every column but the keys:

  • Sum(), Mean(), Median() and Quantile(q, interpolation) take the numeric columns;
  • Min(), Max(), First(), Last() and NUnique() take every column;
  • Count() counts each group's rows into a column named count, and Len(name) under a name of your own.
df, err := sales().
    Select(ursus.Col("region"), ursus.Col("qty"), ursus.Col("price")).
    GroupBy(ursus.Col("region")).
    Max().
    Sort(ursus.Asc(ursus.Col("region"))).
    Collect(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (3, 3)
┌─────────┬───────┬─────────┐
│ region  │ qty   │ price   │
│ String  │ Int64 │ Float64 │
├─────────┼───────┼─────────┤
│ "east"  │ 12    │ 2.25    │
│ "north" │ 10    │ 3       │
│ "south" │ 9     │ 3       │
└─────────┴───────┴─────────┘

The whole frame

An aggregate in Select is one value for the whole frame, so a Select of aggregates is one row. GroupBy() with no keys is the same, written as a group-by:

df, err := sales().
    Select(
        ursus.Len().Alias("orders"),
        ursus.Col("qty").Sum().Alias("qty"),
        ursus.Col("qty").Mul(ursus.Col("price")).Sum().Alias("revenue"),
        ursus.Col("date").Max().Alias("last_day"),
    ).
    Collect(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (1, 4)
┌────────┬────────┬─────────┬────────────┐
│ orders │ qty    │ revenue │ last_day   │
│ Uint64 │ Int128 │ Float64 │ Date       │
├────────┼────────┼─────────┼────────────┤
│ 8      │ 53     │ 108.75  │ 2026-01-07 │
└────────┴────────┴─────────┴────────────┘

New in 0.6.7. A frame's own Sum, Mean, Median, Min, Max, Std(ddof), Var(ddof), Quantile(q, interpolation), Product and NullCount aggregate every column at once, one row, as Polars' frame methods do. A column the aggregate does not take is kept, as a null: the mean of text is null, and a Boolean's mean is the share of its trues.

lines := sales().Select(ursus.Col("region"), ursus.Col("qty"), ursus.Col("price"))
show(lines.Mean())
show(lines.Max())
Output
shape: (1, 3)
┌────────┬─────────┬─────────┐
│ region │ qty     │ price   │
│ String │ Float64 │ Float64 │
├────────┼─────────┼─────────┤
│ null   │ 6.625   │ 2.15625 │
└────────┴─────────┴─────────┘

shape: (1, 3)
┌─────────┬───────┬─────────┐
│ region  │ qty   │ price   │
│ String  │ Int64 │ Float64 │
├─────────┼───────┼─────────┤
│ "south" │ 12    │ 3       │
└─────────┴───────┴─────────┘

New in 0.6.7. Corr(ctx) is each pair of columns' Pearson correlation, a Float64 column a column, as Polars' frame corr, which NumPy computes: a column holding a null is NaN throughout, where Corr of two expressions skips the rows holding one. SumHorizontal(ctx, ignoreNulls), MeanHorizontal(ctx, ignoreNulls), MinHorizontal and MaxHorizontal are each row's reduction across every column, named sum, mean, min and max, and Fold(ctx, fn) folds a function across the columns from the first, as Polars' frame methods do.

ValueCounts(exprs...) counts the rows of each distinct value, most frequent first, ties in the order their values first appear:

df, err := sales().ValueCounts(ursus.Col("product")).Collect(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (3, 2)
┌─────────┬────────┐
│ product │ count  │
│ String  │ Uint64 │
├─────────┼────────┤
│ "apple" │ 3      │
│ "pear"  │ 3      │
│ "fig"   │ 2      │
└─────────┴────────┘

and Describe summarizes each column, as Polars' describe does:

df, err := sales().
    Select(ursus.Col("qty"), ursus.Col("price")).
    Describe(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println(df)
Output
shape: (9, 3)
┌──────────────┬───────────────────┬────────────────────┐
│ statistic    │ qty               │ price              │
│ String       │ Float64           │ Float64            │
├──────────────┼───────────────────┼────────────────────┤
│ "count"      │ 8                 │ 8                  │
│ "null_count" │ 0                 │ 0                  │
│ "mean"       │ 6.625             │ 2.15625            │
│ "std"        │ 3.543101950067402 │ 0.6258922202972101 │
│ "min"        │ 2                 │ 1.5                │
│ "25%"        │ 4                 │ 1.5                │
│ "50%"        │ 7                 │ 2.25               │
│ "75%"        │ 9                 │ 2.25               │
│ "max"        │ 12                │ 3                  │
└──────────────┴───────────────────┴────────────────────┘

A group's rows as a frame

New in 0.6.7. Groups(ctx) gives each group's rows as a frame beside its key, a Go iterator, as Polars' for key, df in df.group_by(...): the groups in the order each first appears, each frame the rows as the frame holds them, and the key a one-row frame named as the key expressions are, so a key computed from a column is the key's and never changes the rows. MapGroups(ctx, fn) runs a Go function over each group's frame and stacks the answers, which must agree in their columns and types, as Polars' map_groups, for what an aggregation cannot say. Both run the query and hold its answer, a frame a group, as PartitionBy does.

Under a memory limit

A group-by spills to disk when its groups outgrow the query's memory budget, a median's, a quantile's and an NUnique's values included, and gives the answer it gives in memory. Implode cannot divide its answer, and fails at the limit with an error that says so. See Memory and spilling.