Grouping, Aggregating, and Stating the Denominator: Python Foundations

Outcome

By the end of this lesson you can collapse a batch of records into one row per group with counts, sums, and averages, and you can report each of those numbers in a way that says what it was computed from.

Why it matters

"Which agencies publish the work we want, and how much of it?" is the first question anyone asks of a shortlist, and it is a grouping question. So is "how many notices closed this month" and "what is a typical contract size in this category".

Aggregates are also where analysis quietly stops being true. A sum over a batch where a third of the amounts were never published is a subtotal computed from an incomplete subset, and it can look exactly like a complete-data total. The remedy is not cleverness; it is stating the denominator every single time.

Concept

Grouping means building a dictionary keyed by the grouping field, where each value collects the rows or values belonging to that key.

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

setdefault(key, []) returns the list already stored under the key, or inserts a new empty list and returns that. It replaces the four-line "if the key is not there yet, create it" dance and is worth learning as an idiom. The standard library's collections.defaultdict(list) does the same thing with slightly different ergonomics.

Then choose the aggregate deliberately:

Count answers "how many", but name the population: source rows, or rows with a usable amount. A missing amount still belongs to the source-row count but not the usable-amount count.
Sum answers "how much in total" for the values you have. When some amounts are missing, label it as a known-amount subtotal and report the missing-row coverage; do not present it as the complete agency total.
Average answers "what is typical", and is the one that misleads most, both because a single very large row drags it and because the denominator is easy to get wrong.
Median, from statistics.median, is often a useful companion to the average for skewed money values. One very large contract may move it less, though a small group can still move substantially.

The denominator rule: a row excluded from the numerator must be excluded from the denominator, and the exclusion must be reported. Concretely, if three of eight amounts were never published, the average is over five, the report says five, and it says three were excluded. Every other arrangement is a claim you cannot support.

Two traps to recognise. Dividing by zero raises ZeroDivisionError, so a group where every amount is missing needs a branch, and reporting "no usable amounts" for it is the honest output. And an unweighted average of group averages is not generally the overall average when groups differ in usable size. Equal group sizes guarantee equality; otherwise weight each group average by its usable count (the two figures can also coincide by accident).

In pandas the same work is df.groupby("agency")["amount"].agg(["count", "mean"]), and it has the same trap: mean and count skip missing amounts, so count is the mean denominator. Compare it with size, the source-row count, to see how many rows had no amount.

Read the code

from statistics import median

rows = [
    {"agency": "GSA", "amount": 300000},
    {"agency": "GSA", "amount": 100000},
    {"agency": "GSA", "amount": None},
    {"agency": "DLA", "amount": 900000},
    {"agency": "DLA", "amount": 100000},
]

amounts_by_agency = {}
excluded_by_agency = {}

for row in rows:
    agency = row["agency"]
    amounts_by_agency.setdefault(agency, [])
    if row["amount"] is None:
        excluded_by_agency[agency] = excluded_by_agency.get(agency, 0) + 1
        continue
    amounts_by_agency[agency].append(row["amount"])

for agency, amounts in amounts_by_agency.items():
    missing = excluded_by_agency.get(agency, 0)
    if not amounts:
        print(agency, "no usable amounts", missing, "excluded")
        continue
    print(agency, len(amounts), sum(amounts) // len(amounts), median(amounts), missing, "excluded")

print("excluded:", sum(excluded_by_agency.values()), "of", len(rows))

One pass over the rows keeps usable and missing amounts under the same agency key. Registering the agency before skipping a missing amount also keeps an all-missing agency in the report. Check the two counts against the source rows; sharing a loop alone does not prevent a counting bug.

For agencies with usable amounts, the report line prints the name, usable count, floor-divided average, median, then excluded count. An all-missing agency gets an explicit no-usable-amounts line. The usable count comes before the average on purpose. A reader who sees the count first interprets the average correctly; a reader who sees the average first has already formed an impression by the time the count arrives.

sum(amounts) // len(amounts) discards any fractional part. The two averages shown here happen to be whole numbers, so flooring does not change them; with other amounts, even a small group can lose meaningful precision (for example, [0, 1] displays 0 instead of 0.5). Choose an explicit rounding and money policy for a report you intend to reconcile.

Predict the output

Predict all three printed lines.

Check your prediction

GSA 2 200000 200000.0 1 excluded
DLA 2 500000 500000.0 0 excluded
excluded: 1 of 5

Both agencies have two usable amounts. The averages and medians coincide because each group has exactly two values, which is a coincidence of this data rather than a general rule. The median prints with a decimal point because the median of an even-sized group is the midpoint of the middle two, which statistics.median returns as a float even when the result is whole.

The agency lines show which group has missing amounts; the last line reconciles one excluded row across all five source rows. Without that coverage, "GSA 200000" reads as a fact about the whole agency, when it is computed from two of its three notices.

Five source rows, two agency denominators

GSA: three source rows

Usable amounts: 300,000 and 100,000
Average denominator: 2 usable rows
Missing amount: 1 excluded row

The printed 200,000 average describes two of three GSA source rows.

DLA: two source rows

Usable amounts: 900,000 and 100,000
Average denominator: 2 usable rows
Missing amount: 0 excluded rows

The printed 500,000 average covers both DLA source rows.

Whole batch: reconcile

Source rows: 5
Usable amounts: 4
Missing amounts: 1

Two agency denominators total four usable rows; the fifth source row is reported as excluded.

For each agency, compare source-row coverage with the usable-amount denominator before interpreting its average. The final panel reconciles the batch: five source rows, four usable amounts, one excluded.

Modify the code

Change DLA's two amounts to 9000000 and 100000, and predict the second printed line.

What changes, and why

DLA 2 4550000 4550000.0 0 excluded

With only two values the median sits between them, so it moves with the average and tells you nothing extra. Add a third DLA row at 120000 and the picture separates: the average becomes 3073333 while the median becomes 120000.

That gap is the signal. When a median and an average disagree by an order of magnitude, the distribution has a long tail, and reporting only the average describes a "typical" opportunity that does not exist in the data. For a group skewed like this three-value example, the median may better describe its center. Show it beside the average and usable count so readers can judge the spread; another distribution may call for a different headline.

Debug the bug

An assistant was asked for the average opportunity value per agency. It produced this and said it handles missing amounts.

rows = [
    {"agency": "GSA", "amount": 300000},
    {"agency": "GSA", "amount": None},
    {"agency": "GSA", "amount": None},
    {"agency": "DLA", "amount": 400000},
]

totals = {}
counts = {}

for row in rows:
    agency = row["agency"]
    totals[agency] = totals.get(agency, 0) + (row["amount"] or 0)
    counts[agency] = counts.get(agency, 0) + 1

for agency in totals:
    print(agency, totals[agency] // counts[agency])

What's actually wrong

It prints GSA 100000 and DLA 400000.

The one General Services Administration notice with a published amount is worth 300,000. The reported average is 100,000, a third of the only real figure, and the report offers no hint that anything was missing.

(row["amount"] or 0) converts absence into a zero contribution to the numerator, while counts still increments for every row, so two records that carry no information are counted as two opportunities worth nothing each. Numerator and denominator disagree about which rows exist.

The additional harm is that incomplete reporting can bias a comparison or ranking toward agencies with better-covered amounts. This four-row fixture says nothing about which agencies are smaller or newer.

The repair is to count only what you summed, and to say what you dropped:

totals, counts, excluded = {}, {}, {}

for row in rows:
    agency = row["agency"]
    totals.setdefault(agency, 0)
    counts.setdefault(agency, 0)
    if row["amount"] is None:
        excluded[agency] = excluded.get(agency, 0) + 1
        continue
    totals[agency] += row["amount"]
    counts[agency] += 1

for agency in totals:
    missing = excluded.get(agency, 0)
    if counts[agency] == 0:
        print(agency, "no usable amounts", missing, "excluded")
    else:
        print(agency, counts[agency], totals[agency] // counts[agency], f"({missing} excluded)")

Then print the count and the exclusions next to every average. GSA 1 300000 (2 excluded) is a sentence a reviewer can act on; GSA 100000 is one they will act on wrongly.

Try it yourself

Six rows across two agencies, two of which publish no amount. Report each agency's usable count, average and excluded count, then reconcile the overall number excluded.

Loading this exercise…

Practical challenge (optional)

Optional: add the median to your per-agency report, and add one very large row to a single agency. Compare how far the average and median move. With only two usable values, both may move; try three or more to see whether one large value affects the average more. Then write one sentence recommending which figure your report should lead with, and why. Being able to defend that choice is the difference between producing numbers and producing analysis.

AI collaboration

Checkpoint

What is the difference between a source-row count and a usable-amount count?
State the denominator rule in one sentence.
When is the median a better headline figure than the average?
Why is an average of per-group averages usually not the overall average?

Answers

The source-row count includes rows with missing amounts; the usable-amount count excludes them and is the average denominator. Label both so missingness stays visible.
A row excluded from the numerator must be excluded from the denominator, and the exclusion must be reported.
When values are skewed, a median can show the center without being pulled as far by an extreme value. A very small group is still sensitive; report the usable count and compare both figures.
It weights groups equally instead of weighting each usable amount equally. Equal usable group sizes guarantee the same result; with unequal sizes, calculate a weighted mean unless equality is incidental.

Summary and next step

Group with setdefault, choose the aggregate deliberately, count what you summed and nothing else, report exclusions beside every figure, and put the median next to the average when money is involved. Next: the analyses that pass every one of those checks arithmetically and still tell a reader something untrue.