GroupBy Stats¶

Source: src/memframe/wrappers/analytix/groupby_stats.py

GroupByStatsWrapper is the public group-by aggregation interface exposed through a ContextManager. It provides pandas-groupby().agg()-like analytics: pick grouping column(s), map value columns to lists of statistics, and get one row per group. Every operation compiles to backend-native SQL and runs in-engine across DuckDB, PostgreSQL, and ClickHouse; each call writes a new transient table (<table>__op_<n>) and the source upload table is never mutated in place.

Users normally call group-by methods directly on a dataset context returned by an upload operation. There are two equivalent entry points:

a direct call — dataset.agg(group_cols, agg_dict) — with the grouping columns passed as an argument;
a pandas-style fluent builder — dataset.groupby("region").sum("sales").

dataset = mf.upload_df(frame)

result = dataset.agg(group_cols=["region"], agg_dict={"sales": ["sum", "mean"]})
result = dataset.groupby("region").sum("sales")

dataset = await mf.aupload_df(frame)

result = await dataset.aagg(group_cols="region", agg_dict={"sales": ["sum"]})
result = await dataset.groupby("region").aagg({"sales": ["mean"]})

groupby is top-level (dataset.groupby, not dataset.dt.*) — dt is reserved for DateTimeWrapper.

The lower-level files are implementation details:

src/memframe/core/analytix/groupby_stats.py builds and executes backend-specific SQL in a single GroupByStatsOps class (isinstance branches for DuckDB / PostgreSQL / ClickHouse).
src/memframe/core/orchestrator/analytix/groupby_stats.py resolves the active dataset context and applies @record_call.
src/memframe/wrappers/analytix/groupby_stats.py exposes synchronous and asynchronous public methods plus the fluent builder.

Public API¶

Direct¶

Synchronous	Asynchronous	Purpose
`agg(group_cols, agg_dict, new_table=None, map_feature=False)`	`await aagg(...)`	Group-by aggregation over an explicit column mapping
`event_rate(group_cols, datetime_col, unit="day", new_table=None)`	`await aevent_rate(...)`	Grouped event rate over a time unit

group_cols accepts a single column name or a list of names. agg_dict maps a value column to a list of statistics, e.g. {"sales": ["sum", "mean"]}.

Builder¶

groupby(*columns) returns a GroupByWrapper holding the group columns. Each terminal method has a synchronous and asynchronous form:

Synchronous	Asynchronous	Purpose
`agg(agg_dict, new_table=None, map_feature=False)`	`await aagg(...)`	Generic — any column → stats mapping
`sum(column)`	`await asum(...)`	Group-wise sum
`mean(column)`	`await amean(...)`	Group-wise mean
`min(column)`	`await amin(...)`	Group-wise minimum
`max(column)`	`await amax(...)`	Group-wise maximum
`count(column)`	`await acount(...)`	Group-wise non-null count
`median(column)`	`await amedian(...)`	Group-wise median
`mode(column)`	`await amode(...)`	Group-wise mode
`std(column)`	`await astd(...)`	Group-wise population standard deviation
`var(column)`	`await avar(...)`	Group-wise population variance
`sem(column)`	`await asem(...)`	Group-wise standard error of the mean
`nunique(column)`	`await anunique(...)`	Group-wise distinct count
`range(column)`	`await arange(...)`	Group-wise range (`max - min`)
`product(column)`	`await aproduct(...)`	Group-wise product
`event_rate(datetime_col, unit="day", new_table=None)`	`await aevent_rate(...)`	Grouped event rate

Usage Overview¶

dataset = mf.upload_df(frame)

sample = dataset.agg(group_cols="region", agg_dict={"sales": ["sum", "mean"]})
sample = dataset.groupby("region").agg({"sales": ["sum"], "qty": ["max"]})
sample = dataset.groupby("region").mean("sales")

dataset = await mf.aupload_df(frame)

sample = await dataset.aagg(group_cols=["region"], agg_dict={"sales": ["sum"]})
sample = await dataset.groupby("region").amean("sales")

Multi-column grouping works in both entry points:

result = dataset.agg(
    group_cols=["region", "month"],
    agg_dict={"sales": ["sum"]},
)
result = dataset.groupby("region", "month").sum("sales")

Common Parameters¶

Parameter	Type	Description
`group_cols`	`str` or `list[str]`	Column(s) to group by. At least one is required.
`agg_dict` / `agg_spec`	`dict[str, list[str]]`	Value column → list of statistics (e.g. `{"sales": ["sum", "mean"]}`).
`datetime_col`	`str`	Datetime column for `event_rate`.
`unit`	`str`	Rate unit for `event_rate`: `second`, `minute`, `hour`, `day` (default), or `week`.
`new_table`	`str` or `None`	Explicit name for the output table. Auto-generated as `<table>__op_<n>` (chained) when omitted.
`map_feature`	`bool`	When `True` (default `False`), LEFT JOIN the new feature columns back onto the original table — one value per row, matched on the group keys. No extra result table is created; the envelope gains `mapped_table` / `mapped_columns`. Agg only (not `event_rate`).

Aggregation Statistics¶

Result columns are named <column>_<stat> (e.g. sales_sum):

count → COUNT(col); sum → SUM(col); min / max → MIN(col) / MAX(col); avg and mean → AVG(col); nunique → COUNT(DISTINCT col); range → MAX(col) - MIN(col).

result = dataset.groupby("region").agg({"sales": ["sum", "mean", "nunique"]})

Spread: `std`, `var`, `sem`¶

Population (not sample) statistics: STDDEV_POP(col) / VAR_POP(col) on PostgreSQL and DuckDB, stddevPop(col) / varPop(col) on ClickHouse. sem is stddev_pop / SQRT(COUNT(col)).

result = dataset.groupby("region").std("score")
result = dataset.groupby("region").var("score")
result = dataset.groupby("region").sem("score")

`product`¶

Group-wise product via EXP(SUM(LN(NULLIF(col, 0)))) (ClickHouse: exp(sum(ln(nullIf(col, 0))))). Zeros are skipped rather than zeroing the group.

result = dataset.groupby("region").product("growth_factor")

`median`, `mode`¶

Backend-specific implementations: median uses PERCENTILE_CONT(0.5) WITHIN GROUP on PostgreSQL, MEDIAN(col) on DuckDB, and median(col) on ClickHouse. mode uses MODE() WITHIN GROUP on PostgreSQL, MODE(col) on DuckDB, and topK(1)(col)[1] (approximate) on ClickHouse.

result = dataset.groupby("region").mean("sales")

Map Feature Back (`map_feature`)¶

With map_feature=True, the new <column>_<stat> columns are LEFT JOINed back onto the original table — every original row gains its group's value (pandas: df.merge(grouped)). Only the feature columns are added; group keys are already on the original table and no extra result table is created. Only the group table is recorded by @record_call.

result = dataset.groupby("region").agg({"sales": ["sum"]}, map_feature=True)
# original table now has: region, sales, sales_sum

result = await dataset.aagg(
    group_cols=["region", "month"],
    agg_dict={"sales": ["sum"], "qty": ["max"]},
    map_feature=True,
)

Rules:

A repeated identical call re-runs cleanly (its own columns are dropped and re-added). This also holds in non-deep mode, which saves nothing.
If the column name already exists from anything else, the call fails with an error — drop or rename it first.
Backend swaps keep the original table name: DuckDB CREATE OR REPLACE, PostgreSQL create-swap-rename, ClickHouse atomic EXCHANGE TABLES.

Event Rate¶

Grouped event rate per time unit. Returns one row per group with cnt (group size) and event_rate. Groups whose timestamps are all identical (or a single row) yield 0.0 instead of dividing by zero.

result = dataset.groupby("region").event_rate("ts", unit="day")
result = dataset.aevent_rate(
    group_cols=["region"], datetime_col="ts", unit="hour"
)

Note

A flat dataset.event_rate(...) resolves to the ungrouped StatsWrapper.event_rate(column, unit) (it is registered first). The grouped form above is only reachable through the groupby(...) builder or GroupByStatsWrapper directly.

Return Values and Errors¶

On a ContextManager, public methods return the resulting DataFrame directly. Calling the wrapper class directly (or using a builder's terminal method) returns the raw operation envelope:

{
    "is_error": False,
    "message": "Group-by aggregation on ['sales']",
    "result": <pandas.DataFrame>,
    "new_table": "ab12CD__op_1",
    "new_columns": ["sales_sum", "sales_mean"],
    "group_cols": ["region"],
    "agg_spec": {"sales": ["sum", "mean"]},
}

Invalid operations surface as OperationError when the envelope is unwrapped (e.g. via a ContextManager): a missing group-by column, an empty agg_dict, an unknown statistic, or a missing value column. When inspecting the raw envelope, check is_error / error_message instead.

Generated Tables¶

Every group-by operation is non-destructive to the source upload table. It creates a new transient table holding one row per group, then returns a preview of it:

PostgreSQL / DuckDB: CREATE TABLE … AS SELECT <groups>, <aggregates> … GROUP BY ….
ClickHouse: CREATE TABLE … ENGINE = MergeTree() ORDER BY tuple() AS SELECT … GROUP BY ….

Output names chain via the transient registry (<table>__op_<n>); an explicit new_table is sanitized and deduplicated with a numeric suffix on collision. Operations apply @record_call, so repeat calls are recorded in the transient registry.

Backend Behavior¶

Group-by stats support DuckDB, PostgreSQL, and ClickHouse:

Identifiers are sanitized and quoted before SQL is generated.
group_cols may be a single column or a list; at least one is required.
Backend dialect differences (std/var/sem, product, median, mode, event-rate epoch extraction) are handled per backend.
The per-backend SQL is locked by tests/unit/test_groupby_stats_sql_fingerprint.py (6 scenarios × 3 backends).

Errors¶

Group-by methods raise OperationError (once unwrapped) for validation or backend failures:

No group-by column, or an empty agg_dict / no valid aggregate expressions.
Unknown statistic name.
Missing group, value, or datetime column.
Missing backend / data_id at the ops layer, or an unsupported backend.

API Reference¶

`memframe.wrappers.analytix.groupby_stats.GroupByStatsWrapper` ¶

Bases: GroupByStatsOrchestrator

Wrapper around GroupByStatsOrchestrator direct and fluent APIs.

`aagg(group_cols, agg_dict, new_table=None, map_feature=False)` `async` ¶

Asynchronously aggregate using explicit group columns.

`agg(group_cols, agg_dict, new_table=None, map_feature=False)` `async` ¶

Synchronously aggregate using explicit group columns.

`aevent_rate(group_cols, datetime_col, unit='day', new_table=None)` `async` ¶

Asynchronously compute event rates using explicit group columns.

`event_rate(group_cols, datetime_col, unit='day', new_table=None)` `async` ¶

Synchronously compute event rates using explicit group columns.

`groupby(*columns)` ¶

Create a fluent group-by stats builder for the given columns.

`memframe.wrappers.analytix.groupby_stats.GroupByWrapper` ¶

Bases: GroupBy

Fluent group-by stats builder with async/sync methods.

`aagg(agg_dict, new_table=None, map_feature=False)` `async` ¶

Asynchronously aggregate grouped data with an aggregation mapping.

`agg(agg_dict, new_table=None, map_feature=False)` `async` ¶

Synchronously aggregate grouped data with an aggregation mapping.

`asum(column)` `async` ¶

Asynchronously compute group-wise sum for a column.

`sum(column)` `async` ¶

Synchronously compute group-wise sum for a column.

`amean(column)` `async` ¶

Asynchronously compute group-wise mean for a column.

`mean(column)` `async` ¶

Synchronously compute group-wise mean for a column.

`amin(column)` `async` ¶

Asynchronously compute group-wise minimum for a column.

`min(column)` `async` ¶

Synchronously compute group-wise minimum for a column.

`amax(column)` `async` ¶

Asynchronously compute group-wise maximum for a column.

`max(column)` `async` ¶

Synchronously compute group-wise maximum for a column.

`acount(column)` `async` ¶

Asynchronously compute group-wise count for a column.

`count(column)` `async` ¶

Synchronously compute group-wise count for a column.

`amedian(column)` `async` ¶

Asynchronously compute group-wise median for a column.

`median(column)` `async` ¶

Synchronously compute group-wise median for a column.

`amode(column)` `async` ¶

Asynchronously compute group-wise mode for a column.

`mode(column)` `async` ¶

Synchronously compute group-wise mode for a column.

`astd(column)` `async` ¶

Asynchronously compute group-wise standard deviation.

`std(column)` `async` ¶

Synchronously compute group-wise standard deviation.

`avar(column)` `async` ¶

Asynchronously compute group-wise variance.

`var(column)` `async` ¶

Synchronously compute group-wise variance.

`asem(column)` `async` ¶

Asynchronously compute group-wise standard error of mean.

`sem(column)` `async` ¶

Synchronously compute group-wise standard error of mean.

`anunique(column)` `async` ¶

Asynchronously compute group-wise count of unique values.

`nunique(column)` `async` ¶

Synchronously compute group-wise count of unique values.

`arange(column)` `async` ¶

Asynchronously compute group-wise range (max-min).

`range(column)` `async` ¶

Synchronously compute group-wise range (max-min).

`aproduct(column)` `async` ¶

Asynchronously compute group-wise product for a column.

`product(column)` `async` ¶

Synchronously compute group-wise product for a column.

`aevent_rate(datetime_col, unit='day', new_table=None)` `async` ¶

Asynchronously compute grouped event rates over a time unit.

`event_rate(datetime_col, unit='day', new_table=None)` `async` ¶

Synchronously compute grouped event rates over a time unit.

GroupBy Stats¶

Public API¶

Direct¶

Builder¶

Usage Overview¶

Common Parameters¶

Aggregation Statistics¶

Spread: std, var, sem¶

product¶

median, mode¶

Map Feature Back (map_feature)¶

Event Rate¶

Return Values and Errors¶

Generated Tables¶

Backend Behavior¶

Errors¶

API Reference¶

memframe.wrappers.analytix.groupby_stats.GroupByStatsWrapper ¶

aagg(group_cols, agg_dict, new_table=None, map_feature=False) async ¶

agg(group_cols, agg_dict, new_table=None, map_feature=False) async ¶

aevent_rate(group_cols, datetime_col, unit='day', new_table=None) async ¶

event_rate(group_cols, datetime_col, unit='day', new_table=None) async ¶

groupby(*columns) ¶

memframe.wrappers.analytix.groupby_stats.GroupByWrapper ¶

aagg(agg_dict, new_table=None, map_feature=False) async ¶

agg(agg_dict, new_table=None, map_feature=False) async ¶

asum(column) async ¶

sum(column) async ¶

amean(column) async ¶

mean(column) async ¶

amin(column) async ¶

min(column) async ¶

amax(column) async ¶

max(column) async ¶

acount(column) async ¶

count(column) async ¶

amedian(column) async ¶

median(column) async ¶

amode(column) async ¶

mode(column) async ¶

astd(column) async ¶

std(column) async ¶

avar(column) async ¶

var(column) async ¶

asem(column) async ¶

sem(column) async ¶

anunique(column) async ¶

nunique(column) async ¶

arange(column) async ¶

range(column) async ¶

aproduct(column) async ¶

product(column) async ¶

aevent_rate(datetime_col, unit='day', new_table=None) async ¶

event_rate(datetime_col, unit='day', new_table=None) async ¶