Skip to content

Data Wrangling·Lesson 30 of 67·intermediate

Group-by, aggregate, pivot

Split-apply-combine — the operation that answers "how does this vary by model, by difficulty, by day?" — plus transform, pivot tables and crosstabs.


In this lesson · 20 min

  • →Group by one or more columns and aggregate each group
  • →Name several aggregations at once with agg
  • →Broadcast a group statistic back to every row with transform
  • →Keep or drop whole groups with filter
  • →Build a pivot table and a crosstab

Nearly every question about an evaluation is a group-by: accuracy by model, latency by difficulty, cost by day. pandas calls the pattern split-apply-combine — split the rows into groups, apply a function to each, combine the answers into a table.

PythonPython not started

One group, one number

PythonPython not started

The grouping column becomes the index. reset_index() turns it back into an ordinary column when you want a flat table:

PythonPython not started

Named aggregations

Asking for several statistics with names is what makes a summary table readable:

PythonPython not started

Grouping by several columns

PythonPython not started

transform

transform computes a per-group statistic and hands it back aligned to every original row — which is how you compare a row against its own group:

PythonPython not started

agg collapses each group to one row; transform keeps every row. That is the whole distinction, and choosing the wrong one is the most common group-by mistake.

filter

filter keeps or drops entire groups based on a condition about the group:

PythonPython not started

Pivot tables

A pivot table is a group-by laid out as a grid, which is far easier to read when you are comparing two dimensions:

PythonPython not started

crosstab is the count-only shorthand, useful for checking a dataset is balanced:

PythonPython not started

apply, and its price

apply runs an arbitrary Python function per group. It is the escape hatch — flexible, and far slower than a built-in aggregation, because it cannot stay in compiled code:

PythonPython not started

Try it yourself

Exercise 1

Build summary: one row per model, with columns examples (row count), accuracy (mean score) and total_tokens (sum), sorted by accuracy, best first.

Your turnPython not started
Exercise 2

Add a vs_model_mean column holding each row's score minus its own model's mean score, and keep every original row.

Your turnPython not started

Check yourself

You want each row labelled with its group's average, keeping every row. agg or transform?

Check yourself

A per-model accuracy table shows one model at 0.99 from 3 examples and another at 0.84 from 400. What should you do?

Recap

  • Split-apply-combine: groupby(cols) then an aggregation.
  • agg(name=(column, function), ...) names several statistics in one readable table.
  • size counts rows; count counts non-missing values, and the difference is informative.
  • transform returns a value per original row — use it to compare a row with its own group. agg collapses; transform keeps.
  • filter keeps or drops whole groups, which is how you remove groups too small to trust.
  • pivot_table lays a two-dimensional group-by out as a grid; crosstab does the count-only version.
  • apply is the flexible, slow escape hatch — prefer a built-in aggregation when one exists.