Reshaping Records

Group rows with defaultdict or itertools.groupby, pivot rows into columns, and aggregate without double counting.

Syntaxitertools.groupby(iterable, key=None) # the iterable must already be sorted by key

Most data work is reshaping: a flat list of rows arrives and you need it keyed, grouped, pivoted or totalled. Two tools cover nearly all of it.

defaultdict for grouping

collections.defaultdict(list) calls its default_factory on a missing key, inserts the result and returns it, so d[k].append(row) always works. It is the shorthand for d.setdefault(k, []).append(row) on a plain dict — identical behaviour, less noise. One caveat: __missing__ is only called by d[k], so d.get(k) still returns None rather than creating an entry, and merely reading a key adds it.

groupby, and the sort it needs

itertools.groupby(iterable, key=None) is not SQL's GROUP BY. The docs describe it as a uniq filter: it "generates a break or new group every time the value of the key function changes". Unsorted input therefore yields the same key several times. Sort first, with the same key function. The second trap is that each group iterator shares the underlying iterable, so advancing to the next group empties the previous one — aggregate inside the loop, or wrap it in list().

Pivoting and double counting

A pivot is one row per key plus a column per distinct value: defaultdict(lambda: defaultdict(int)) and += builds it in one pass. Fix the column list yourself so an absent combination becomes 0 rather than a missing key.

The last trap is arithmetic, not Python. A joined result repeats the parent row once per child, so summing the parent's total over it multiplies revenue by the number of lines. Collapse to one record per entity first — a dict keyed by id — then sum.

Example

Try it yourself
Loading editor…
Press Run to execute the code.

When to use it

  • Group a flat list of rows by region or customer to summarise each one.
  • Build a cross-tab report with one row per product and one column per month.
  • Count events by type from a log export and report the top few.
  • Total revenue from a joined query result without multiplying it by the line count.

More examples

Counter when the aggregate is a count

Reach for Counter instead of defaultdict(int) whenever the value is a tally: you get most_common(), addition and subtraction between counters, and total() for free. Note that counts[missing] returns 0 without inserting the key, unlike a defaultdict, which makes it safe to probe.

Example · python
from collections import Counter

EVENTS = [
    {"user": 1, "kind": "view"}, {"user": 1, "kind": "buy"},
    {"user": 2, "kind": "view"}, {"user": 1, "kind": "view"},
]

# Counter is a dict subclass whose __missing__ returns 0 instead of raising,
# so counts[k] += 1 needs no setup. Build it straight from an iterable.
counts = Counter(e["kind"] for e in EVENTS)
print(counts.most_common())        # [('view', 3), ('buy', 1)]

# update() ADDS counts rather than replacing them, so batches merge cleanly.
counts.update(["buy", "buy"])
print(counts["buy"])               # 3

# most_common(n) is sorted high to low; ties keep first-encountered order.
print(counts.most_common(1))

Pivot straight out to CSV columns

The shape of every cross-tab report. Two details make it correct: a fixed column list, so every output row has the same width and a spreadsheet can read it, and sorted(pivot), so two runs over the same data produce byte-identical files.

Example · python
import csv, io
from collections import defaultdict

ROWS = [("A", "eu", 3), ("A", "us", 1), ("B", "eu", 2)]
REGIONS = ["eu", "us"]            # fix the columns; do not derive them per row

pivot = defaultdict(lambda: defaultdict(int))
for sku, region, units in ROWS:
    pivot[sku][region] += units

out = io.StringIO(newline="")
writer = csv.DictWriter(out, fieldnames=["sku", *REGIONS])
writer.writeheader()
for sku in sorted(pivot):
    # A combination that never appeared must be 0, not a missing column.
    writer.writerow({"sku": sku, **{r: pivot[sku][r] for r in REGIONS}})
print(out.getvalue())
# sku,eu,us
# A,3,1
# B,2,0

The double-counting bug in a joined result

This is the reporting bug that gets noticed by finance rather than by tests, because the number is plausible. The fix is to name the grain of every aggregate: per line, or per order. A dict comprehension keyed by id is the cheapest deduplication available and keeps the last row seen per key.

Example · python
# A joined query repeats the order row once per order line. Summing the order
# total over those rows multiplies revenue by the number of lines.
JOINED = [
    {"order_id": 1, "order_total": 100, "line_sku": "A"},
    {"order_id": 1, "order_total": 100, "line_sku": "B"},
    {"order_id": 2, "order_total": 40, "line_sku": "A"},
]

print(sum(r["order_total"] for r in JOINED))     # 240 - wrong, order 1 counted twice

# Collapse to one record per entity first, keyed by its id.
orders = {r["order_id"]: r["order_total"] for r in JOINED}
print(sum(orders.values()))                      # 140 - right

# Line-level figures are summed over the joined rows; order-level figures are
# summed over the deduplicated dict. Decide which grain each total belongs to.

Related

py dictionariespy comprehensionspy data csvpy generatorspy stdlib

Discussion

  • Be the first to comment on this lesson.