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.
One group, one number
The grouping column becomes the index. reset_index() turns it back into an ordinary column when
you want a flat table:
Named aggregations
Asking for several statistics with names is what makes a summary table readable:
Grouping by several columns
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:
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:
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:
crosstab is the count-only shorthand, useful for checking a dataset is balanced:
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:
Try it yourself
Build summary: one row per model, with columns examples (row count), accuracy (mean score) and total_tokens (sum), sorted by accuracy, best first.
Add a vs_model_mean column holding each row's score minus its own model's mean score, and keep every original row.
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.sizecounts rows;countcounts non-missing values, and the difference is informative.transformreturns a value per original row — use it to compare a row with its own group.aggcollapses;transformkeeps.filterkeeps or drops whole groups, which is how you remove groups too small to trust.pivot_tablelays a two-dimensional group-by out as a grid;crosstabdoes the count-only version.applyis the flexible, slow escape hatch — prefer a built-in aggregation when one exists.