Grouping and aggregation

Intermediate

Introduction

"Which region sold most?", "what is the average grade per class?", "how many orders came each day?" Questions like these do not ask about single rows; they ask about groups of rows. Grouping and aggregation is probably the most used operation in data analysis, and it follows one pattern, called split-apply-combine: split the rows into groups by a key, apply a summary to each group, and combine the summaries into a result table.

How it works

In plain Python, a dictionary does the splitting. Either collect each group's values in a list:

groups = {}
for region, amount in rows:
    groups.setdefault(region, []).append(amount)

and then aggregate each list (len, sum, sum / len, min, max, statistics.median), or keep running totals directly with totals[key] = totals.get(key, 0) + amount, which uses less memory but only works for sums and counts. collections.Counter counts occurrences in one line.

To group by several columns, use a tuple as a composite key: (region, product). Laying out a two-key aggregate as a grid, with one key down the side and the other across the top, gives a cross tabulation, or pivot table: total sales for every region and product, with totals for each row and column. Cells with no data need a value, usually 0 for sums and a blank for means.

Results are usually sorted, by the key or by the aggregate. When sorting by the aggregate, decide what happens to ties, for example by sorting on (-total, name); otherwise the order of tied groups depends on the input and changes from run to run of different data.

Beware of averaging averages. The overall average of all rows is not the average of the group averages when the groups have different sizes; compute it from the rows, or weight each group by its count.

In pandas the same work is df.groupby("region")["amount"].agg(["count", "sum", "mean"]), df.groupby(["region", "product"]).sum() and pd.crosstab or pivot_table.

Example

# Grouping: split rows by a key, then summarise each group.
sales = [
    ("North", "tea", 120),
    ("South", "tea", 90),
    ("North", "coffee", 200),
    ("South", "coffee", 150),
    ("North", "tea", 80),
    ("East", "tea", 60),
]

groups = {}
for region, product, amount in sales:
    groups.setdefault(region, []).append(amount)
for region in sorted(groups):
    amounts = groups[region]
    print(f"{region}: {len(amounts)} sales, total {sum(amounts)}, average {sum(amounts) / len(amounts):.1f}")

# Group by two keys at once with a tuple.
by_pair = {}
for region, product, amount in sales:
    by_pair[(region, product)] = by_pair.get((region, product), 0) + amount
best = max(by_pair, key=by_pair.get)
print(by_pair)
print("best region and product:", best, by_pair[best])
Open in compiler

Line by line

  1. The first loop splits the amounts into a list per region.
  2. For each region, in alphabetical order, it prints the count, total and average.
  3. The second loop groups by a composite key, the tuple of region and product, keeping running totals.
  4. max with key=by_pair.get finds the pair with the largest total.

Expected result

East: 1 sales, total 60, average 60.0
North: 3 sales, total 400, average 133.3
South: 2 sales, total 240, average 120.0
{('North', 'tea'): 200, ('South', 'tea'): 90, ('North', 'coffee'): 200, ('South', 'coffee'): 150, ('East', 'tea'): 60}
best region and product: ('North', 'tea') 200

In the real world

Business dashboards are mostly group-bys: revenue per month, users per country, errors per service. SQL's GROUP BY, spreadsheet pivot tables and pandas groupby all implement the same idea. Large-scale systems such as MapReduce were designed around it: the map step produces keys, and the reduce step aggregates each key's values.

Common mistakes

  • Averaging group averages when the groups have different sizes.
  • Forgetting groups with no rows, which should often appear with a count of 0.
  • Sorting by the aggregate without a tie rule, so equal groups come out in arbitrary order.
  • Grouping on text that has not been cleaned, so "Dhaka" and "dhaka " become two groups.

Best practices

  • Clean and standardise the key column before grouping.
  • Print the count of each group beside its aggregate.
  • Sort with an explicit key, such as (-total, name).

Summary

Grouping splits rows by a key, aggregation summarises each group, and combining the summaries answers questions about categories. A dictionary of lists or running totals does this in plain Python, tuples group by several columns, cross tabulations lay two keys out as a grid, and every sort needs a tie rule.

Quick check

1. What are the three steps of the grouping pattern?
2. How do you group by region and product together in a dictionary?
3. Group A has 10 rows averaging 50 and group B has 2 rows averaging 80. What is the overall average?
4. Why sort grouped results by (-total, name)?

Key takeaways

  • Most questions about data are a group by: totals per region, averages per class, counts per day.
  • A dictionary from key to list (or to a running total) is all you need in plain Python.
  • Use a tuple as the key to group by several columns; sort results with an explicit tie rule.

Practice problems for this lesson

My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.