Table Aggregate

Inputs

Outputs

Source

python

inp = __ivy_ctx__["nodes"][__ivy_node_id__]["input"]

rows = inp["rows"]
group_by = inp.get("group_by", [])
if isinstance(group_by, str):
    group_by = [group_by]
value = inp.get("value")
op = inp.get("op", "sum")
period = inp.get("period")
date_column = inp.get("date_column")

if op not in ("sum", "mean", "count", "min", "max"):
    raise ValueError(f"unknown op {op!r}; use sum, mean, count, min or max")
if op != "count" and not value:
    raise ValueError(f"op {op} needs a value column")
if period and period not in ("day", "month", "year"):
    raise ValueError("period must be day, month or year")
# "group by month" with a date column names the period, not a column, and
# so does group_by naming the period it also sets.
if date_column:
    for c in list(group_by):
        if c in ("day", "month", "year") and (not period or c == period) and not any(c in row for row in rows):
            period = c
            group_by.remove(c)
            break
if period and not date_column:
    # "sum per month of the date column" names the date as the group.
    if len(group_by) != 1:
        raise ValueError("period needs a date_column")
    date_column, group_by = group_by[0], []

if rows:
    seen = sorted({k for row in rows for k in row})
    for c in group_by + ([date_column] if period else []):
        if not any(c in row for row in rows):
            raise ValueError(f"no row has a column {c!r}; the columns are {', '.join(seen)}")

cut = {"day": 10, "month": 7, "year": 4}.get(period)
groups = {}
for row in rows:
    key = []
    if period:
        stamp = row.get(date_column)
        if not stamp:
            continue
        key.append(str(stamp)[:cut])
    key.extend(row.get(c) for c in group_by)
    numbers = groups.setdefault(tuple(key), [])
    if op == "count":
        numbers.append(1)
    elif isinstance(row.get(value), (int, float)) and not isinstance(row.get(value), bool):
        numbers.append(row[value])

result = []
# The key keeps the date column's name and the aggregate the value column's,
# which is what a later step asks for: a plan read "x: date, y: amount".
names = ([date_column] if period else []) + list(group_by)
label = "count" if op == "count" else value
for key in sorted(groups, key=lambda k: [str(x) for x in k]):
    numbers = groups[key]
    if op == "count":
        figure = len(numbers)
    elif not numbers:
        figure = None
    elif op == "sum":
        figure = sum(numbers)
    elif op == "mean":
        figure = sum(numbers) / len(numbers)
    elif op == "min":
        figure = min(numbers)
    else:
        figure = max(numbers)
    result.append({**dict(zip(names, key)), label: round(figure, 6) if isinstance(figure, float) else figure})

out = __ivy_ctx__["nodes"][__ivy_node_id__]["output"]
out["rows"] = result
out["groups"] = len(result)
out["value_column"] = label

Tests

Requires: python:3.9

• monthly-sum

  Sales sum by month, with the region as a second key.

• count

  count needs no value column.

• period-on-the-group

  A period with one group_by column and no date_column groups by that date's month.

• month-as-a-group

  group_by month with a date_column groups by that date's month.

• period-also-grouped

  group_by naming the period it sets groups by the date's month once.

• missing-column

  A group column no row has is refused with the columns there are.

• period-without-a-date

  A period with two group columns and no date_column is refused.

• unknown-op

  An aggregate the node does not have is named.