Grouping and Aggregation

The previous lesson changed the shape of the dataset but did not combine a single value: the 3,199 measurements still sat in 3,199 separate cells in the 1,260-row wide format. The number that goes into a report, however, is rarely a cell. The question “average usage per region” reduces 3,199 rows to five rows, and unlike the change of shape in the previous lesson, this reduction cannot be undone.

The Pivot Tables lesson in the Analysis with Spreadsheets course measured this decision on a worksheet and showed that five summary decisions made three different regions first on the same sheet. This lesson takes up the same work on the script side and with another measure: which region is first is not counted, but how many rows remain and what has disappeared.

• TF11. Aggregation is the operation that reduces the rows of a group to a single row. The output row count is the result not of the calculation but of the number of unique values of the grouping field.
• TF12. In a cross-break, the number of possible cells is the product of the unique values of the fields. A cell that carries no rows never appears in the output, and for the reader “zero” and “no data” cannot be told apart.
• TF13. When aggregated rows are aggregated again, every group receives equal weight. This gives a different number from the one calculated from the raw rows, and the difference comes from the inequality of the group sizes.
• TF14. A summary row carries the center of the group, not its spread. If the spread is not calculated separately and written next to it, it is lost and cannot be brought back.
• TF15. The measure of the loss is the questions the summary cannot answer: the answer read from the summary row and the answer read from the raw rows are placed side by side.

How Many Rows Remain

// group.mjs — MODEL: the long-format dataset of the previous lesson and four grouping
// decisions. The dataset is fictional; no query interface is imitated.
export const SEED = 20260218, RAW = 1400, PERIOD = ["2026-01", "2026-02", "2026-03"];
export const TARIFF = [10, 25, 40];
const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507);
  x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; };
const rng = (t) => { let s = mix(t);
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };
const pick = (u, w) => { let t = 0;
  for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; };
const REGION = [["north", 0.28, 21], ["south", 0.22, 17], ["east", 0.18, 26],
  ["west", 0.14, 14], ["central", 0.18, 23]];
const SUBSCRIBER = [], DROPPED = [];
for (let i = 0; i < RAW; i++) {
  const r = rng(SEED + i), b = REGION[pick(r(), REGION.map((x) => x[1]))];
  const k = { subscriber_id: 10001 + i, region: b[0], base: b[2],
    household_size: pick(r(), [0.06, 0.24, 0.30, 0.24, 0.11, 0.05]),
    satisfaction: pick(r(), [0.08, 0.14, 0.27, 0.34, 0.17]) + 1 };
  (r() < 0.046 ? DROPPED : SUBSCRIBER).push(k);
}
const keys = new Set(SUBSCRIBER.map((a) => a.subscriber_id)), rows = [];
for (const k of [...SUBSCRIBER, ...DROPPED]) {
  const r = rng(SEED + 7000 + k.subscriber_id), n = pick(r(), [0.05, 0.12, 0.21, 0.62]);
  for (let d = 0; d < n; d++) {
    const t = r() < 0.038 ? 0 : Math.round(k.base * Math.exp((r() + r() + r() - 1.5) * 0.62)
      * (1 - d * 0.05) * 100) / 100;
    if (!keys.has(k.subscriber_id)) { r(); continue; }
    rows.push({ subscriber_id: k.subscriber_id, region: k.region, period: PERIOD[d], usage_m3: t });
    if (r() < 0.028) rows.push({ subscriber_id: k.subscriber_id, region: k.region, period: PERIOD[d],
      usage_m3: Math.round(t * 1.02 * 100) / 100 });
  }
}
// Closing dataset of the previous lesson; the tariff tier is derived from the reading, and the
// comparison at every threshold is made with less-than.
const first = new Map();
for (const o of rows) { const a = `${o.subscriber_id}|${o.period}`; if (!first.has(a)) first.set(a, o); }
export const LONG = [...first.values()].map((o) => ({ ...o,
  tier: o.usage_m3 < TARIFF[0] ? 1 : o.usage_m3 < TARIFF[1] ? 2
    : o.usage_m3 < TARIFF[2] ? 3 : 4 }));

export const group = (a, f) => {
  const m = new Map();
  for (const o of a) { const k = f(o); if (!m.has(k)) m.set(k, []); m.get(k).push(o); }
  return m;
};
export const mean = (a) => a.reduce((x, y) => x + y, 0) / a.length;
export const gen = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j])
  : String(v).padEnd(w[0]))).join("");

if (import.meta.url === `file://${process.argv[1]}`) {
  console.log(`model (seed ${SEED}): in ${LONG.length} rows, ` +
    `${new Set(LONG.map((o) => o.subscriber_id)).size} subscribers, 5 regions, ` +
    `${PERIOD.length} periods, ${TARIFF.length + 1} tariff tiers`);
  const K = [
    ["G1 region", (o) => o.region, 5],
    ["G2 region x period", (o) => `${o.region}|${o.period}`, 15],
    ["G3 region x tier x period", (o) => `${o.region}|${o.tier}|${o.period}`, 60],
    ["G4 subscriber", (o) => o.subscriber_id, null],
  ];
  const y = gen([30, 9, 9, 15, 13, 12]);
  console.log("\n" + y(["grouping decision", "in", "out", "possible cells", "avg per cell",
    "empty cells"]));
  for (const [name, f, possible] of K) {
    const g = group(LONG, f);
    console.log(y([name, LONG.length, g.size, possible ?? "-",
      (LONG.length / g.size).toFixed(1), possible === null ? "-" : possible - g.size]));
  }
  const g3 = group(LONG, (o) => `${o.region}|${o.tier}|${o.period}`);
  console.log(`\n${60 - g3.size} cells of the cross-break carry no rows and never appear in the ` +
    `output table; the reader cannot tell "zero" from "no data"`);
  const single = [...group(LONG, (o) => `${o.region}|${o.tier}|${o.period}`).values()]
    .filter((l) => l.length === 1).length;
  console.log(`in the same break ${single} cell consists of a single reading: the ` +
    `"mean" of that cell is the reading itself`);
}

model (seed 20260218): in 3199 rows, 1260 subscribers, 5 regions, 3 periods, 4 tariff tiers

grouping decision                    in      out possible cells avg per cell empty cells
G1 region                          3199        5              5        639.8           0
G2 region x period                 3199       15             15        213.3           0
G3 region x tier x period          3199       53             60         60.4           7
G4 subscriber                      3199     1260              -          2.5           -

7 cells of the cross-break carry no rows and never appear in the output table; the reader cannot tell "zero" from "no data"
in the same break 1 cell consists of a single reading: the "mean" of that cell is the reading itself

All four decisions read the same 3,199 rows and produce four different row counts. The output row count does not come from the calculation: G1 gives five rows because there are five regions, and G4 gives 1,260 rows because there are 1,260 subscribers. Aggregation itself determines how a single row is calculated; the grouping field determines how many rows remain.

The third row carries the silent loss of this lesson. The cross of region, tariff tier, and period defines 60 cells, but the output is 53 rows. The missing 7 cells are not an error; they are combinations in which there is no reading at all: for example, in a certain region in a certain period, no reading falls in the top tariff tier. The problem is that these 7 cells do not appear at all in the output. Someone reading the output sees 53 rows and cannot tell whether the missing one is a cell with zero usage or a cell where nothing was measured. For an empty cell to be countable, the set of expected cells has to be built separately, just as the empty cells became countable in the wide format of the previous lesson.

The last row shows the other end of the scale: in the same break, one cell consists of a single reading. The “mean” of that cell is not a summary but the reading itself, and it is reported as if a group stood beside it. As the grouping gets finer, the number of rows per cell falls from 639.8 to 213.3, from there to 60.4, and at the far end to 1; the number stops being a summary and becomes the observation itself.

The Variability the Summary Erases

// variability.mjs — what the five-row summary carries and what it does not. Three overall
// means from the same dataset and three questions the summary cannot answer.
import { LONG, group, mean, gen } from "./group.mjs";

const stdDev = (a) => { const m = mean(a);
  return Math.sqrt(mean(a.map((x) => (x - m) ** 2))); };
const byRegion = group(LONG, (o) => o.region);
const y = gen([10, 9, 11, 11, 9, 9, 11]);
console.log(y(["region", "readings", "mean", "std dev", "min", "max",
  "range"]));
const summary = [...byRegion].map(([k, l]) => {
  const v = l.map((o) => o.usage_m3);
  return { k, n: l.length, m: mean(v), s: stdDev(v), a: Math.min(...v), b: Math.max(...v) };
}).sort((x, z) => z.m - x.m);
for (const s of summary) console.log(y([s.k, s.n, s.m.toFixed(2), s.s.toFixed(2),
  s.a.toFixed(2), s.b.toFixed(2), (s.b - s.a).toFixed(2)]));

const all = LONG.map((o) => o.usage_m3);
const byCell = group(LONG, (o) => `${o.region}|${o.tier}|${o.period}`);
console.log(`\nthree overall means from the same dataset:`);
console.log(`  ${LONG.length} raw rows   ${mean(all).toFixed(3)} m3`);
console.log(`  ${byRegion.size} region rows   ${mean(summary.map((s) => s.m)).toFixed(3)} m3`);
console.log(`  ${byCell.size} cell rows   ` +
  `${mean([...byCell.values()].map((l) => mean(l.map((o) => o.usage_m3)))).toFixed(3)} m3`);
console.log(`  difference ${(mean([...byCell.values()].map((l) => mean(l.map((o) => o.usage_m3))))
  - mean(all)).toFixed(3)} m3; when every group gets equal weight, small groups grow`);

// Questions the summary cannot answer: the answer is read from the raw rows and from
// the five-row summary separately and the two are placed side by side.
const LIMIT_M3 = 40, RATIO_LIMIT = 0.5;
const over = all.filter((v) => v > LIMIT_M3).length;
const summaryOver = summary.filter((s) => s.m > LIMIT_M3).length;
let far = 0;
for (const [, l] of byRegion) { const m = mean(l.map((o) => o.usage_m3));
  for (const o of l) if (Math.abs(o.usage_m3 - m) > RATIO_LIMIT * m) far++; }
const top = summary[0], bottom = summary[summary.length - 1];
const below = byRegion.get(top.k).filter((o) => o.usage_m3 < bottom.m).length;
const above = byRegion.get(bottom.k).filter((o) => o.usage_m3 > top.m).length;

const y2 = gen([52, 12, 12]);
console.log("\n" + y2(["question", "raw rows", "five rows"]));
console.log(y2([`how many readings are above ${LIMIT_M3} m3`, over, summaryOver]));
console.log(y2(["how many readings are far from own group mean", far, "unreadable"]));
console.log(y2([`${top.k} and ${bottom.k} swap places in how many readings`,
  below + above, "unreadable"]));
console.log(`\nsummary says ${top.k} ${top.m.toFixed(2)} > ${bottom.k} ` +
  `${bottom.m.toFixed(2)}; ${below} readings of region ${top.k} are below the ${bottom.k} ` +
  `mean, ${above} readings of region ${bottom.k} are above the ${top.k} mean`);

region     readings       mean    std dev      min      max      range
east            557      24.67       9.19     0.00    51.90      51.90
central         600      22.40       9.02     0.00    52.47      52.47
north           921      20.21       7.46     0.00    45.47      45.47
south           645      16.60       6.08     0.00    37.56      37.56
west            476      13.78       5.00     0.00    31.95      31.95

three overall means from the same dataset:
  3199 raw rows   19.713 m3
  5 region rows   19.532 m3
  53 cell rows   20.721 m3
  difference 1.008 m3; when every group gets equal weight, small groups grow

question                                                raw rows   five rows
how many readings are above 40 m3                             58           0
how many readings are far from own group mean                431  unreadable
east and west swap places in how many readings                50  unreadable

summary says east 24.67 > west 13.78; 39 readings of region east are below the west mean, 11 readings of region west are above the east mean

The first table shows more than five rows would carry, because the standard deviation and the extreme values have been added beside them. If a summary row carried only the mean, the 10.89 m³ gap between the 24.67 m³ of the east region and the 13.78 m³ of the west region would look small next to the within-group ranges of 51.90 and 31.95 m³, and this comparison could never have been made.

The middle block produces three overall means from the same dataset. From the 3,199 raw rows 19.713 m³; from the five region rows 19.532 m³; from the 53 cell rows 20.721 m³. All three are calculated correctly, and the difference between them is a weighting decision: when aggregated rows are aggregated again, the west region with 476 readings and the north region with 921 readings count as equal, and in the same way a cell with 244 readings and a cell with one reading count as equal. The deviation of the cell-level re-aggregation from the raw rows is 1.008 m³, that is, 5% of the overall mean. This is the script-side counterpart of the same trap that was measured on a worksheet in the previous course, and here it is read together with the row counts: the source of the deviation is the difference between 53 and 3,199.

The last table counts the loss directly. 58 readings are above 40 m³, and this threshold was chosen because it is the top of the tariff tiers; when the same question is put to the five-row summary, the answer is zero, because no region mean exceeds 40 m³. The answer is not wrong; the question that was asked is now a different question, and the party reading it cannot see that.

431 readings deviate from their own group mean by more than 50%; a ratio threshold was preferred to an absolute cubic-meter threshold because the region means vary between 13.78 and 24.67 m³. The five-row summary carries this number nowhere. In the same way, 50 readings swap places between the two extreme regions: 39 readings of the east region are below the west mean, and 11 readings of the west region are above the east mean. The summary says “east is higher than west”, and this is true; what it does not carry is that, when individual readings are looked at, this ordering is reversed 50 times.

The row accounting of the lesson thus closes: 3,199 rows went in, 5 rows came out, and 3,194 rows melted into the summary. Melting is not a loss, since that is its purpose, but if what melted is not written down, the summary takes the place of the dataset behind it.

Summary

• The grouping field, not the calculation, determines the output row count: the same 3,199 rows shrink to 5, 15, 53, and 1,260 rows respectively.
• Of the 60 cells of the cross-break, 7 carry no rows and never appear in the output; the reader cannot tell “zero” from “no data”. One cell consists of a single reading and stops being a summary.
• Re-aggregating aggregated rows gives every group equal weight: three overall means come out of the same dataset, 19.713, 19.532, and 20.721 m³.
• A summary row carries the center of the group, not its spread: 431 of the 3,199 readings deviate from their own group mean by more than 50%, and this number cannot be read from five rows.
• The five-row summary says zero to the question “how many readings are above 40 m³”, while the answer in the raw rows is 58; the east and west regions swap places in 50 readings.

Next Step

Aggregation reduced the row count but did not touch the set of columns: what we hold is still measured fields, with no calculated ones. The questions analysis asks mostly want not the measured value but a quantity derived from it: usage per person, a daily average, the rate of change between periods. Derivation does not produce new rows, so it looks harmless. Its harm appears elsewhere: when the divisor is zero or one of the inputs is missing, the derived value stays undefined, and that row is silently dropped in the next step. The next lesson counts in how many rows a derivation stays undefined and measures how far the dropping of those rows moves the result.