Grouping and Aggregation

A filter selects rows, but a report does not ask for individual rows; it asks for a summary by breakdown. The question “average meter reading per region” is a job of three steps: the dataset is split by the key, a calculation is applied to each part, and the results are combined in a single table. The grouping lesson of the Data Collection and Preparation course ran these three steps with hand-written loops and measured that the grouping decision determines the number of rows that come out; that calculation is the input of this lesson and is not repeated.

What changes here is that the three steps fit into a single call. That call carries two separate decisions at once, and neither of them is written: which group the row with an empty group key enters, and what the aggregation calculation takes as its denominator. The measure of this lesson is how many rows and how many cubic meters these two decisions move.

• DF18. Split–apply–combine is the three steps of grouping. The split sets up one bucket per key, the apply step makes a calculation in each bucket, and the combine step produces one row per bucket. The number of rows that come out is the number of distinct values of the key, not the number of rows of the dataset.
• DF19. A row whose group key is missing does not belong to any bucket. The default behavior is to drop it; collecting the missing value in a bucket of its own is also a legitimate decision, and the difference between the two decisions is a row count and a total.
• DF20. The denominator of the aggregation is a separate decision. An average can be calculated by counting the filled values of the aggregated column or by counting all the rows of the group. Two denominators give two averages, and the n column in the report has to say which one it writes.
• DF21. If the split step is done once, the same buckets are shared by many measures. The measure of the gain is the number of key lookups, not the number of value reads.

A Row With No Key

# dataset.py — MODEL: the long-format reading table of the previous lesson is
# regenerated from the same seed. The dataset is fiction; the detail of the
# generator is in the first lesson of the topic.
import math

M32 = 0xFFFFFFFF
SEED, PERIODS = 20260218, ["2026-01", "2026-02", "2026-03"]
REGION = [("north", 0.28, 21), ("south", 0.22, 17), ("east", 0.18, 26),
          ("west", 0.14, 14), ("central", 0.18, 23)]

def rng(seed: int):
    s = ((seed ^ (seed >> 16)) * 2246822507) & M32
    s = ((s ^ (s >> 13)) * 3266489909) & M32
    s ^= s >> 16
    def next_value() -> float:
        nonlocal s
        s = (s * 1664525 + 1013904223) & M32
        return s / 4294967296
    return next_value

def pick(u: float, weights: list[float]) -> int:
    t = 0.0
    for i, p in enumerate(weights):
        t += p
        if u < t:
            return i
    return len(weights) - 1

subscribers, dropped = [], []
for i in range(1400):
    r = rng(SEED + i)
    name, _, base = REGION[pick(r(), [b[1] for b in REGION])]
    r(); r()  # the household and satisfaction draws are not used in this lesson
    (dropped if r() < 0.046 else subscribers).append(
        {"subscriber_id": 10001 + i, "region": name, "base": base})

valid = {k["subscriber_id"] for k in subscribers}
pairs: dict[tuple[int, str], float] = {}
for k in subscribers + dropped:
    r = rng(SEED + 7000 + k["subscriber_id"])
    for d in range(pick(r(), [0.05, 0.12, 0.21, 0.62])):
        t = 0.0 if r() < 0.038 else math.floor(k["base"] * math.exp(
            (r() + r() + r() - 1.5) * 0.62) * (1 - d * 0.05) * 100 + 0.5) / 100
        if k["subscriber_id"] in valid:
            pairs.setdefault((k["subscriber_id"], PERIODS[d]), t)
        r()
LONG = [{"subscriber_id": a, "period": d, "consumption_m3": t} for (a, d), t in pairs.items()]

# The group key is the subscriber's region; for some subscribers the address record
# did not match, so the region is empty. The meter_reading column carries missing
# values as in the previous lesson.
MISSING = None

def region_cell(no: int, name: str):
    return MISSING if rng(SEED + 61000 + no)() < 0.048 else name

region_of = {k["subscriber_id"]: region_cell(k["subscriber_id"], k["region"]) for k in subscribers}
cumulative: dict[int, float] = {}
records = []
for o in LONG:
    no, t = o["subscriber_id"], o["consumption_m3"]
    cumulative[no] = cumulative.get(no, 0.0) + t
    u = rng(SEED + 9100 + no * 4 + PERIODS.index(o["period"]))()
    meter_reading = MISSING if u < 0.055 else 100000 + (no - 10000) * 37 + int(cumulative[no])
    records.append({"subscriber_id": no, "period": o["period"], "consumption_m3": t,
                    "meter_reading": meter_reading, "region": region_of[no]})

print(f"{len(records)} rows; the group key is empty in "
      f"{sum(1 for k in records if k['region'] is MISSING)} rows, the column to aggregate "
      f"is empty in {sum(1 for k in records if k['meter_reading'] is MISSING)} rows")
print(f"the true total of the dataset is {round(sum(k['consumption_m3'] for k in records), 2)} m3")

3199 rows; the group key is empty in 160 rows, the column to aggregate is empty in 191 rows
the true total of the dataset is 63060.63 m3

The two missing patterns sit in the same dataset and are independent of each other. In 160 rows the group key is empty: the address record of the subscriber did not match a region. In 191 rows the column to aggregate is empty: the meter could not be read and the meter reading was not recorded. Both of the missing patterns pass through the same grouping call, and the call treats each of them differently.

The true total of the dataset is 63,060.63 m³, and this number is the audit anchor of this lesson. As long as the total of a summary table can be compared with it, what the grouping lost can be read; when it is not compared, the summary looks consistent within itself and says nothing.

Split, Apply, Combine

# split_apply_combine.py — grouping is three steps: split, apply, combine.
# It fits into a single call and carries two decisions at once.
def split(rows, key: str, drop_missing=True):
    """Split step: rows are distributed into buckets by the key."""
    bucket, lost = {}, 0
    for k in rows:
        if k[key] is MISSING and drop_missing:
            lost += 1
            continue
        bucket.setdefault(k[key], []).append(k)
    return bucket, lost

def apply(bucket, column: str):
    """Apply step: in each bucket a count, a total and an average with two separate denominators."""
    out = []
    for name, members in bucket.items():
        filled = [k[column] for k in members if k[column] is not MISSING]
        out.append({
            "group": "(missing)" if name is MISSING else name,
            "rows": len(members), "filled": len(filled),
            "total_m3": round(sum(k["consumption_m3"] for k in members), 2),
            "avg_filled": round(sum(filled) / len(filled), 1) if filled else 0.0,
            "avg_rows": round(sum(filled) / len(members), 1)})
    return sorted(out, key=lambda c: c["group"])

WIDTH = [12, 8, 8, 12, 12, 12]
show = lambda h: "".join(str(v).rjust(WIDTH[k]) if k else str(v).ljust(WIDTH[0])
                         for k, v in enumerate(h))
bucket, lost = split(records, "region")
summary = apply(bucket, "meter_reading")
print(show(["group", "rows", "filled", "total m3", "avg/filled", "avg/rows"]))
for c in summary:
    print(show([c["group"], c["rows"], c["filled"], c["total_m3"], c["avg_filled"], c["avg_rows"]]))
print(show(["combined", sum(c["rows"] for c in summary), sum(c["filled"] for c in summary),
            round(sum(c["total_m3"] for c in summary), 2), "", ""]))
print(f"\nthe default grouping produced a summary of {len(summary)} rows and showed {lost} rows "
      f"in no group")

group           rows  filled    total m3  avg/filled    avg/rows
central          570     533     12796.0    125262.2    117131.2
east             533     508    13203.17    124854.4    118998.2
north            878     831     17749.3    126247.9    119489.8
south            614     574    10153.66    125303.5    117140.4
west             444     415     6123.43    126006.5    117776.3
combined        3039    2861    60025.56

the default grouping produced a summary of 5 rows and showed 160 rows in no group

A summary of five rows came out, and looked at on its own it seems flawless. The region names are complete, the counts are plausible, the averages are close to each other. The flaw shows only in the combined row: 3,039 rows have been counted, whereas the dataset is 3,199 rows; the total is 60,025.56 m³, whereas the dataset is 63,060.63 m³.

The 160 rows in between are reported nowhere. There is no error message, no warning, not even an empty row; because no bucket was opened for those rows, there was no place in the table where they could appear. This differs in structure from the silent loss measured in the previous lesson: there the filter made its decision by a condition, here what makes the decision is the non-existence of the bucket.

The last two columns of the table show the second decision. The north group has 878 rows but the meter reading column has 831 filled values. If the average counts the filled values it comes out as 126,247.9, if it counts all the rows of the group it comes out as 119,489.8. Both numbers are an average and there is a difference of 6,758.1 between them.

Two Decisions, Two Numbers

# decision.py — the two decisions are measured separately: the missing in the group key
# and the denominator of the aggregation.
bucket2, lost2 = split(records, "region", drop_missing=False)
summary2 = apply(bucket2, "meter_reading")
miss = next(c for c in summary2 if c["group"] == "(missing)")
total1 = round(sum(c["total_m3"] for c in summary), 2)
total2 = round(sum(c["total_m3"] for c in summary2), 2)
print(f"if the missing key is kept the summary has {len(summary2)} rows instead of {len(summary)}; "
      f"rows in the buckets {sum(c['rows'] for c in summary2)}, total {total2} m3")
print(f"the (missing) group carries {miss['rows']} rows and {miss['total_m3']} m3; the default "
      f"grouping drops it and the total stays {total1} m3")
print(f"share lost: {round(100 * (total2 - total1) / total2, 2)} percent, without producing any warning")

north = next(c for c in summary if c["group"] == "north")
print(f"\nin the north group there are two separate counts: {north['rows']} rows and {north['filled']} "
      f"filled meter readings; the average is {north['avg_filled']} or {north['avg_rows']}")
print(f"a report that writes 'n' on the same line has to say which one it wrote")

MEASURES = ["count", "sum", "mean", "max", "min"]
print(f"\npasses: a separate loop per measure {len(MEASURES) * len(records)} key lookups, "
      f"a single split {len(records)} key lookups and {len(MEASURES) * len(records)} value reads")
print(f"code  : a separate loop per measure {len(MEASURES)} blocks, a single split "
      f"{len(MEASURES)} applications 1 call")

if the missing key is kept the summary has 6 rows instead of 5; rows in the buckets 3199, total 63060.63 m3
the (missing) group carries 160 rows and 3035.07 m3; the default grouping drops it and the total stays 60025.56 m3
share lost: 4.81 percent, without producing any warning

in the north group there are two separate counts: 878 rows and 831 filled meter readings; the average is 126247.9 or 119489.8
a report that writes 'n' on the same line has to say which one it wrote

passes: a separate loop per measure 15995 key lookups, a single split 3199 key lookups and 15995 value reads
code  : a separate loop per measure 5 blocks, a single split 5 applications 1 call

The change of a single option takes the summary from five rows to six, and the sixth row carries the whole of the dropped part: 160 rows, 3,035.07 m³, 4.81% of the dataset. The total of the six-row summary is equal to the true total of the dataset, and this equality is the only check needed for a grouping to be verifiable.

Both decisions have a defensible side. Putting the 160 rows whose region is unknown into the region breakdown is meaningless, because it is not known which region they belong to. But not writing them into the table at all means that the region totals do not give the dataset total and that this is not visible. The difference in between is not a preference but a reporting debt: as long as the dropped rows are counted and written down, the report remains readable whichever decision is made.

The decision on the denominator has the same structure. The average meter reading of the north group is 126,247.9 as the average of 831 measured values; in the calculation that also adds the 47 unmeasured rows to the count it falls to 119,489.8. The second is the same as saying “the meter reading of the unreadable meters is zero”, and this is an assumption. When n is not written next to the average, the two numbers cannot be told apart.

The gain side is in the last two lines. Writing five separate loops for five measures costs 15,995 key lookups, because every measure scans the dataset from the start and rebuilds the buckets. Split–apply–combine does the split step once: 3,199 key lookups, and then the five measures are calculated on the same buckets with 15,995 value reads. The key lookups fall to one fifth, the code falls from five blocks to a single call, and it is exactly this shortening that makes the two decisions invisible.

The Summary Is Also a Frame

# share.py — the summary is also a frame and its label index is the group key.
# The share calculation gives two separate tables depending on which total it takes as the denominator.
W2 = [12, 12, 14, 14]
show2 = lambda h: "".join(str(v).rjust(W2[k]) if k else str(v).ljust(W2[0])
                          for k, v in enumerate(h))
print(show2(["group", "total m3", "share/summary", "share/dataset"]))
for c in summary:
    print(show2([c["group"], c["total_m3"], round(100 * c["total_m3"] / total1, 2),
                 round(100 * c["total_m3"] / total2, 2)]))
print(show2(["combined", total1, round(sum(100 * c["total_m3"] / total1 for c in summary), 2),
             round(sum(100 * c["total_m3"] / total2 for c in summary), 2)]))
print(f"\nthe table that takes the summary total as the denominator closes at 100 percent and hides the "
      f"{lost} dropped rows; the table that takes the dataset total as the denominator leaves "
      f"{round(100 - sum(100 * c['total_m3'] / total2 for c in summary), 2)} percent open")

group           total m3 share/summary share/dataset
central          12796.0         21.32         20.29
east            13203.17          22.0         20.94
north            17749.3         29.57         28.15
south           10153.66         16.92          16.1
west             6123.43          10.2          9.71
combined        60025.56         100.0         95.19

the table that takes the summary total as the denominator closes at 100 percent and hides the 160 dropped rows; the table that takes the dataset total as the denominator leaves 4.81 percent open

The output of the aggregation is a new frame and its label index is the group key. This applies the rule of the first lesson of the topic to the result of the grouping as well: the next operation is aligned over this index, and a group that is not in the index silently becomes missing. The share calculation is the most common form of this, because choosing the denominator is exactly choosing an index.

The two share columns come out of the same five numbers and they differ only in the denominator. The column that takes the summary total as the denominator closes at 100%; someone looking at the table sees no shortfall, because a percentage table closing at 100 is taken as a sign of correctness. The column that takes the dataset total as the denominator closes at 95.19% and the remaining 4.81% stands in the table itself as a question.

That the second table closes short is not a defect, it is a finding. The table that closes at 100% is more dangerous, because it can hide the existence of the 160 dropped rows without breaking the table: the closing check has passed, and what the check measured is not that there is no loss but that the shares are consistent within themselves.

Summary

• Split–apply–combine is three steps and the number of rows that come out is the number of distinct values of the key; doing the split step once brings the key lookups down from 15,995 to 3,199.
• The 160 rows whose group key is empty enter no bucket under the default grouping and there is no place in the table where they could appear; the summary counts 3,039 rows, the dataset is 3,199 rows.
• The dropped part is 3,035.07 m³, that is, 4.81% of the dataset; when the missing key is kept the summary has 6 rows and its total equals the true total of the dataset.
• The denominator of the aggregation is the second decision: the north group holds 878 rows and 831 filled meter readings, and the average comes out as 126,247.9 or 119,489.8 depending on the denominator.
• The only mandatory check of a summary table is that the group totals equal the dataset total; when the equality is not established, the summary looks consistent within itself and does not report the dropped rows.

Next Step

Grouping stays within a single table. Most questions of analysis, however, ask for more than one table: the subscriber information has to come next to the reading table, and the field visits next to the subscriber information. In the Data Collection and Preparation course this job was done with a hand-written matching loop and the row calculation was kept item by item. In a dataframe interface the same job is a single call, and that call chooses a join type. The next lesson applies the inner, outer and cross join to the same two tables and counts how many rows each of them produces and how rows multiply where the key is not unique.