Aggregating data for charts

Intermediate

Introduction

Real data arrives as rows: one order, one visit, one sensor reading at a time. A chart of a hundred thousand orders is noise; a chart of revenue per category is an answer. Almost every chart is drawn from aggregated data, and the decisions made while aggregating (what to group by, what to sum, what to leave out) shape what the chart says.

How it works

Group by a category and aggregate each group: sum the amounts, count the rows, or average a value. In plain Python a dictionary does it in one loop:

totals = {}
for category, amount in orders:
    totals[category] = totals.get(category, 0) + amount

pandas does the same with df.groupby("category")["amount"].sum().

Many categories have a long tail: a few big groups and dozens of small ones. A chart of all of them buries the big picture, so show the top N sorted by size, and combine the rest into one Other bar, placed last whatever its size. State what Other contains.

Percentages are rounded for display, and rounding each one separately often gives totals of 99 or 101, which readers notice. The largest remainder method fixes this: round every share down, see how many whole points are missing to reach 100, and give one each to the shares with the largest fractional parts. With three equal shares of 33.33%, the floors give 99, and one share becomes 34.

A pivot table aggregates by two categories at once: regions down the side, quarters across the top, a sum in each cell, and totals for each row and column. It is both a table to read and the input to grouped or stacked bar charts and heat maps. Missing combinations get zero, not a blank that could be mistaken for missing data.

Choose the aggregate deliberately. A sum favours big groups, a mean hides group size, and a count ignores amounts. When groups differ greatly in size, show the count alongside the mean.

Example

# Aggregate before plotting: totals per category, the top three, the rest as Other, shares that add up to 100.
orders = [("Books", 12.5), ("Toys", 30), ("Books", 7.5), ("Garden", 45), ("Toys", 10),
          ("Music", 4), ("Books", 20), ("Games", 3)]

totals = {}
for category, amount in orders:
    totals[category] = totals.get(category, 0) + amount
ranked = sorted(totals.items(), key=lambda kv: (-kv[1], kv[0]))
shown = ranked[:3] + [("Other", sum(v for _, v in ranked[3:]))]

grand = sum(totals.values())
exact = [v / grand * 100 for _, v in shown]
whole = [int(p) for p in exact]
# Largest remainder: give the missing points to the shares with the biggest fractional parts.
for i in sorted(range(len(exact)), key=lambda i: -(exact[i] - whole[i]))[: 100 - sum(whole)]:
    whole[i] += 1
for (name, value), pct in zip(shown, whole):
    print(f"{name:7} {value:6g} {pct:3d}% {'#' * (pct // 2)}")
print("total", grand, "and the shares add up to", sum(whole))
Open in compiler

Line by line

  1. The loop sums the amounts of each category into totals.
  2. Sorting by (-total, name) ranks the categories; the first three are shown and the rest summed as Other.
  3. The exact percentages are rounded down, and the missing points go to the largest fractional parts.
  4. Each line shows the category, its total, its share and a bar of one # per two percent.

Expected result

Garden      45  34% #################
Books       40  31% ###############
Toys        40  30% ###############
Other        7   5% ##
total 132.0 and the shares add up to 100

In the real world

Business dashboards are built on aggregations: revenue per region, signups per week, tickets per team. Spreadsheet pivot tables and SQL GROUP BY queries are the everyday tools, and election results use the largest remainder method itself to share out seats.

Common mistakes

  • Plotting raw records instead of aggregated values.
  • Percentages that add to 99 or 101 because each was rounded on its own.
  • Hiding a large Other, which may be bigger than any shown category.
  • Averages without counts, which make a group of two look as reliable as a group of two thousand.

Best practices

  • Decide the aggregate (sum, count, mean) from the question, and say which one the chart shows.
  • Show the top categories and an explicit Other, placed last.
  • Fill missing combinations in pivot tables with 0.

Summary

Charts are drawn from aggregated data: records grouped by category and summed, counted or averaged. Showing the top N with an Other bar keeps charts readable, the largest remainder method keeps rounded percentages at exactly 100, and pivot tables aggregate by two categories at once.

Quick check

1. Three equal shares round to 33% each. What does the largest remainder method do?
2. Where should the Other bar go?
3. What does a pivot table aggregate by?
4. Why show counts next to group means?

Key takeaways

  • Aggregate first: a chart of totals per category, not of individual records.
  • Show the top N and fold the long tail into Other to keep charts readable.
  • Use the largest remainder method so rounded percentages add up to 100.

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.