Aggregate and Group

Aggregate a whole column

❯make a new table that takes the events table and counts the non-null values in the id column

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — count() on the column, which counts the non-null values and ignores the nulls.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["events"],
  output_tables=["events_id_count"],
)
def count_ids(events: TableFrameSpec) -> TableFrameSpec:
  return events.select(col("id").count())

⏺

Execution committed, 0 failures.

⏺

subscriptions/events_id_count is live — one row, one column: the non-null ids:

┌────┐
│ id │
├────┤
│ 2  │
└────┘

Transformed tables

events

id
1
null
3

3 rows

events_id_count

id
2

1 row

Group rows and aggregate

❯make a new table that takes the sales table and totals the sales column and averages the quantity column per category

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — group_by("category").agg(...). Each expression in agg becomes one output column, and alias names it.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["sales"],
  output_tables=["sales_by_category"],
)
def sales_by_category(sales: TableFrameSpec) -> TableFrameSpec:
  return sales.group_by("category").agg(
      col("sales").sum().alias("total_sales"),
      col("quantity").mean().alias("avg_quantity")
  )

⏺

Execution committed, 0 failures.

⏺

subscriptions/sales_by_category is live — one row per category, with the two aggregates you asked for:

┌──────────┬─────────────┬──────────────┐
│ category │ total_sales │ avg_quantity │
├──────────┼─────────────┼──────────────┤
│ tools    │ 160         │ 5            │
├──────────┼─────────────┼──────────────┤
│ toys     │ 40          │ 2            │
└──────────┴─────────────┴──────────────┘

Transformed tables

sales

category	sales	quantity
tools	100	4
toys	40	2
tools	60	6

3 rows

sales_by_category

category	total_sales	avg_quantity
tools	160	5
toys	40	2

2 rows

Aggregate every column in a group

❯make a new table that takes the sales table and counts the rows in each category

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — the shorthand form. group_by("category").count() applies to the frame rather than to a named expression, so you get a count without writing an agg.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["sales"],
  output_tables=["sales_counts"],
)
def count_by_category(sales: TableFrameSpec) -> TableFrameSpec:
  return sales.group_by("category").count()

⏺

Execution committed, 0 failures.

⏺

subscriptions/sales_counts is live — one row per category with its row count:

┌──────────┬───────┐
│ category │ sales │
├──────────┼───────┤
│ tools    │ 2     │
├──────────┼───────┤
│ toys     │ 1     │
└──────────┴───────┘

Transformed tables

sales

category	sales
tools	100
toys	40
tools	60

3 rows

sales_counts

category	sales
tools	2
toys	1

2 rows

Rank values

❯make a new table that takes the results table and ranks the score column, with tied scores sharing the average rank

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — rank() returns a value per row rather than collapsing the table, so it goes through with_columns. method="average" is the default and gives tied rows the mean of the ranks they span.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["results"],
  output_tables=["results_ranked"],
)
def rank_average(results: TableFrameSpec) -> TableFrameSpec:
  return results.with_columns(col("score").rank(method="average").alias("avg_rank"))

⏺

Execution committed, 0 failures.

⏺

subscriptions/results_ranked is live — a rank column beside the scores. Ada and Cy tie on 7 and share 2.5:

┌────────┬───────┬──────────┐
│ player │ score │ avg_rank │
├────────┼───────┼──────────┤
│ Ada    │ 7     │ 2.5      │
├────────┼───────┼──────────┤
│ Bo     │ 5     │ 1        │
├────────┼───────┼──────────┤
│ Cy     │ 7     │ 2.5      │
└────────┴───────┴──────────┘

Transformed tables

results

player	score
Ada	7
Bo	5
Cy	7

3 rows

results_ranked

player	score	avg_rank
Ada	7	2.5
Bo	5	1
Cy	7	2.5

3 rows