Dealing with Missing Data

The previous lesson reduced the sheet to 1,877 rows and assumed that all the remaining rows were filled in. The assumption does not hold. In the reading column some cells are blank, some say zero, and the spreadsheet does not count the two the same way in every operation: the sum function skips the blank cell and adds the zero; the mean function drops the blank cell from both the dividend and the divisor, and the zero only from the dividend.

This lesson calculates the mean of that column. The calculation is a single line, and at least three decisions stand in front of it: whether a blank cell is excluded, counted as zero, or imputed; whether the cells that say zero are real zero consumption; and which value the imputation uses. All three move the mean, and all three are unreadable from the sheet once the operation is done. These are the lesson’s two numbers: the mean the decision gives and how many decisions can be read back.

• SC5. The sheet is the deduplicated 1,877 rows of the previous lesson, but this time the reading column stands as it was on the field form. The spreadsheet is again modeled with node and the dataset is fictional.
• SC6. Missing data shows up in three forms: the visit produced no reading and the cell is blank; the meter is sealed and the consumption is really zero; the reading could not be taken but zero was written on the form.
• SC7. The sum and mean functions skip the blank cell; the exclusion decision therefore does not move the total, it moves the mean.
• SC8. The recoverability measure continues: if the decision is written in a cell formula it is read in one step, if in a separate note cell in two steps, and if it is in no cell it is unreadable.

The Same Column, Four Means

// missing.mjs — the spreadsheet is a MODEL: a cell lives in a map, a blank cell
// carries no value, a formula carries its own text. No application is imitated.
// The dataset is fictional: the deduplicated 1877-row sheet of the previous
// lesson, this time with the reading column as it was on the field form.
export const SEED = 20260218, SUBSCRIBERS = 1400;
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; };
// Last field: the rate at which a visit produces no reading. Regions differ in this rate.
export const REGION = [["north", 0.28, 21, 0.06], ["south", 0.22, 17, 0.03],
  ["east", 0.18, 26, 0.27], ["west", 0.14, 14, 0.03], ["central", 0.18, 23, 0.08]]
  .map(([name, share, base, missing]) => ({ name, share, base, missing }));

const LETTER = (j) => String.fromCharCode(65 + j);
export const formula = (text, f) => ({ kind: "formula", text, f });
export class Table {
  constructor(header, rows) {
    this.header = header; this.cells = new Map(); this.n = rows.length;
    header.forEach((b, j) => this.cells.set(`${LETTER(j)}1`, b));
    rows.forEach((s, i) => header.forEach((b, j) =>
      this.cells.set(`${LETTER(j)}${i + 2}`, s[b])));
  }
  read(name) { const v = this.cells.get(name); return v && v.kind === "formula" ? v.f(this) : v; }
  write(name, v) { this.cells.set(name, v); return this; }
  column(b) { const j = this.header.indexOf(b);
    return Array.from({ length: this.n }, (_, i) => this.read(`${LETTER(j)}${i + 2}`)); }
}
// The sum and mean functions skip the blank cell: the mean takes both the dividend
// and the divisor from the filled cells only. Zero is a filled cell.
export const numbers = (a) => a.filter((v) => typeof v === "number");
export const sum = (a) => numbers(a).reduce((x, y) => x + y, 0);
export const mean = (a) => { const d = numbers(a); return d.length ? sum(d) / d.length : 0; };

// The unique sheet of the previous lesson: one or two periods per subscriber,
// already deduplicated by the subscriber+period key. The visit result comes from
// a separate generator stream; the readings do not change. unreachable -> cell is
// blank. sealed -> real zero consumption. form_zero -> reading could not be taken but
// zero was written on the form.
export const ROWS = [];
for (let i = 0; i < SUBSCRIBERS; i++) {
  const r = rng(SEED + i);
  const b = REGION[pick(r(), REGION.map((x) => x.share))];
  const base = Math.max(1.2, b.base * Math.exp((r() + r() + r() - 1.5) * 0.62));
  for (const d of r() < 0.34 ? ["2026-01", "2026-02"] : ["2026-01"]) {
    const q = rng(SEED + 7000 + ROWS.length), u = q();
    const status = u < b.missing ? (q() < 0.72 ? "unreachable" : "form_zero")
      : u < b.missing + 0.035 ? "sealed" : "read";
    const actual = Math.round(base * (d === "2026-02" ? 0.94 : 1) * 100) / 100;
    ROWS.push({ subscriber_no: 10001 + i, region: b.name, period: d, visit_result: status,
      reading_m3: status === "unreachable" ? null : status === "read" ? actual : 0,
      actual_m3: actual });
  }
}
// The same column, four exclusion and imputation methods. Each method changes the
// set of rows entering the calculation and the divisor.
export const columnMean = mean(ROWS.map((s) => s.reading_m3));
export const METHODS = [
  ["M1 blank excluded, zero counted", (l) => numbers(l.map((s) => s.reading_m3))],
  ["M2 blank counted as zero", (l) => l.map((s) => s.reading_m3 ?? 0)],
  ["M3 blank and zero excluded", (l) => numbers(l.map((s) => s.reading_m3)).filter((v) => v > 0)],
  ["M4 blank imputed with column mean", (l) => l.map((s) => s.reading_m3 ?? columnMean)],
];
export const THRESHOLD = 0.005; // half of the two reported decimals
export const y = (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]}`) {
  const withResult = (d) => ROWS.filter((s) => s.visit_result === d);
  console.log(`model (seed ${SEED}): ${ROWS.length} rows, reported total ` +
    `${sum(ROWS.map((s) => s.actual_m3)).toFixed(1)} m3; ${withResult("unreachable").length} blank ` +
    `cells, ${withResult("sealed").length + withResult("form_zero").length} cells hold a zero`);
  console.log(`of the zeros ${withResult("sealed").length} are real zero consumption, ` +
    `${withResult("form_zero").length} are unread visits; the ` +
    `${sum(withResult("unreachable").map((s) => s.actual_m3)).toFixed(1)} m3 carried by the blank cells is ` +
    `inside the total but rests on no reading`);
  const y1 = y([42, 9, 11, 12]);
  console.log("\n" + y1(["same column, same question, four methods", "rows", "mean", "total m3"]));
  const o = {};
  for (const [name, f] of METHODS) { const v = f(ROWS); o[name.slice(0, 2)] = mean(v);
    console.log(y1([name, v.length, mean(v).toFixed(2), sum(v).toFixed(1)])); }
  const [lo, hi] = [Math.min(...Object.values(o)), Math.max(...Object.values(o))];
  console.log(`\nlowest ${lo.toFixed(2)}, highest ${hi.toFixed(2)}, difference ` +
    `${(hi - lo).toFixed(2)} m3; M1 and M4 give ` +
    `${Math.abs(o.M1 - o.M4) < THRESHOLD ? "the same" : "a different"} mean ` +
    `(threshold ${THRESHOLD} m3, half of the two reported decimals)`);
}

model (seed 20260218): 1877 rows, reported total 39147.7 m3; 116 blank cells, 112 cells hold a zero
of the zeros 57 are real zero consumption, 55 are unread visits; the 2719.2 m3 carried by the blank cells is inside the total but rests on no reading

same column, same question, four methods       rows       mean    total m3
M1 blank excluded, zero counted                1761      19.23     33866.8
M2 blank counted as zero                       1877      18.04     33866.8
M3 blank and zero excluded                     1649      20.54     33866.8
M4 blank imputed with column mean              1877      19.23     36097.6

lowest 18.04, highest 20.54, difference 2.49 m3; M1 and M4 give the same mean (threshold 0.005 m3, half of the two reported decimals)

The sheet has 1,877 rows and a reported total of 39,147.7 m³; both come from the previous lesson. What is new is this: in 116 cells there is no reading at all, and the 2,719.2 m³ that corresponds to them is inside that total. In the column 112 cells say zero, but only 57 of them are real zero consumption; the remaining 55 are a visit where no reading could be taken. In the sheet there is no difference between the two: a blank cell gives itself away, while a missing value written as zero presents itself as a measurement.

The numbers of the four decisions confirm this. The mean runs between 18.04 and 20.54; the 2.49 m³ in between is about 14% of the lower value. M1 counts the zeros as measurements and excludes the blanks; M2 counts the blanks as zero too, the divisor rises from 1,761 to 1,877, and the mean falls; M3 trusts no zero and gives the highest value with 1,649 rows. The total is fixed at 33,866.8 m³ in the first three decisions: exclusion does not move the total at all, because the sum function was skipping the blank cell anyway.

The fourth row does not give what the previous lesson would lead one to expect: imputing with the column mean does not produce a third mean, it gives the same as M1 — adding a value equal to the mean does not change the mean. The comparison is made with a threshold of 0.005 m³ — the number is reported with two decimals, and floating-point values are not compared with an equality operator. The real warning is here: while it preserves the mean, imputation raises the total to 36,097.6 m³ and adds 116 invented measurements to the sheet. That the mean does not change proves not that imputation is harmless but that the mean does not show the imputation.

What Remains After the Imputation

// imputation.mjs — the same sheet, three questions: how much the imputation decision
// moves the region breakdown, whether imputation leaves a trace in the sheet, and
// whether the decision can be read back from the sheet.
import { ROWS, REGION, Table, formula, numbers, mean, columnMean, THRESHOLD, y } from "./missing.mjs";

// Because missingness is not spread evenly across regions, the same decision does
// not move every region by the same amount: the region with the most blank cells
// moves the most.
const regions = REGION.map((b) => {
  const l = ROWS.filter((s) => s.region === b.name);
  return { name: b.name, m1: mean(l.map((s) => s.reading_m3)),
    m2: mean(l.map((s) => s.reading_m3 ?? 0)),
    rate: 100 * l.filter((s) => s.reading_m3 === null).length / l.length };
}).sort((a, b) => b.rate - a.rate);
const [top, bottom] = [regions[0], regions[regions.length - 1]];
console.log(`blank-cell rate is ${bottom.rate.toFixed(1)}% in the ${bottom.name} region and ` +
  `${top.rate.toFixed(1)}% in the ${top.name} region; if blank cells are counted as zero the mean ` +
  `of the ${top.name} region falls from ${top.m1.toFixed(2)} m3 to ${top.m2.toFixed(2)} m3, ` +
  `and that of the ${bottom.name} region from ${bottom.m1.toFixed(2)} m3 to ${bottom.m2.toFixed(2)} m3`);

// Does the imputation leave a trace in the sheet: the cells carrying the imputed value
// are counted. Floating-point comparison is done with a threshold; the threshold is
// half of the two decimals.
const blank = ROWS.filter((s) => s.reading_m3 === null).length;
const traceMean = ROWS.filter((s) => Math.abs((s.reading_m3 ?? columnMean) - columnMean) < THRESHOLD).length;
const traceZero = ROWS.filter((s) => (s.reading_m3 ?? 0) === 0).length;
console.log(`the ${blank} cells imputed with the column mean show up in ${traceMean} cells of ` +
  `the sheet, detection ${(100 * blank / traceMean).toFixed(0)}%; had the same cells been imputed ` +
  `with zero, detection among ${traceZero} zero cells would have been ` +
  `${(100 * blank / traceZero).toFixed(0)}%`);

// Recoverability of a decision: one step if it is written in a formula, two steps
// if it sits in a separate note cell, unreadable if it is in no cell.
const CODE = ["D1 which cell was blank", "D2 imputation method", "D3 meaning of zero",
  "D4 rows used in the calculation"];
const readBack = (t) => CODE.map((k) => {
  const v = [...t.cells.values()], n = k.slice(0, 2);
  const f = v.some((x) => x && x.kind === "formula" && x.text.includes(n));
  const note = v.some((x) => typeof x === "string" && x.startsWith("decision ") && x.includes(n));
  return f ? 1 : note ? 2 : null;
});
const imputedValue = Math.round(columnMean * 100) / 100;
const HEADER = ["subscriber_no", "region", "period", "reading_m3"];
const cleaned = ROWS.map(({ subscriber_no, region, period, reading_m3 }) =>
  ({ subscriber_no, region, period, reading_m3: reading_m3 ?? imputedValue }));
const imputedSheet = new Table(HEADER, cleaned);
const annotated = new Table(HEADER, cleaned)
  .write("F1", "decision D2: blank cells were imputed with the column mean");
const traced = new Table([...HEADER, "visit_result", "calc_m3"], ROWS.map((s) => ({
  subscriber_no: s.subscriber_no, region: s.region, period: s.period, reading_m3: s.reading_m3,
  visit_result: s.visit_result,
  calc_m3: formula("D1 if visit_result is unreachable the reading cell is blank; D2 a blank " +
    "cell is imputed with the column mean; D3 zero is real consumption on a sealed meter and an " +
    "unread visit under form_zero", () => s.reading_m3 ?? imputedValue) })));
traced.write("H2", formula("D4 the rows entering the calculation are counted from the filled " +
  "reading cells", (t) => numbers(t.column("reading_m3")).length));

const y3 = y([33, 14, 17, 14]);
console.log("\ncan the decision be read back from the sheet");
console.log(y3(["decision", "imputed sheet", "annotated sheet", "traced sheet"]));
const result = [imputedSheet, annotated, traced].map(readBack);
CODE.forEach((k, j) => console.log(y3([k,
  ...result.map((s) => (s[j] ? `${s[j]} ${s[j] === 1 ? "step" : "steps"}` : "unreadable"))])));
console.log(["imputed", "annotated", "traced"].map((name, j) =>
  `${name} ${result[j].filter(Boolean).length}/${CODE.length}`).join(", ") +
  " decisions can be read back");

blank-cell rate is 2.0% in the west region and 17.3% in the east region; if blank cells are counted as zero the mean of the east region falls from 22.80 m3 to 18.86 m3, and that of the west region from 13.62 m3 to 13.35 m3
the 116 cells imputed with the column mean show up in 116 cells of the sheet, detection 100%; had the same cells been imputed with zero, detection among 228 zero cells would have been 51%

can the decision be read back from the sheet
decision                          imputed sheet  annotated sheet  traced sheet
D1 which cell was blank              unreadable       unreadable        1 step
D2 imputation method                 unreadable          2 steps        1 step
D3 meaning of zero                   unreadable       unreadable        1 step
D4 rows used in the calculation      unreadable       unreadable        1 step
imputed 0/4, annotated 1/4, traced 4/4 decisions can be read back

The first line says where the missingness piles up: the blank-cell rate is 2.0% in the west region and 17.3% in the east region; the measurement network is failing systematically in one region. This is why the same decision does not move every region by the same amount: when the blanks are counted as zero, the mean of the east region falls from 22.80 to 18.86 — 3.94 m³, without a single measurement changing — and that of the west region falls by 0.27 m³. A decision that looks small at the column level grows in the breakdown.

The second line counts whether the imputation leaves a trace. The 116 cells imputed with the column mean can be told apart exactly, because none of the real readings lands on 19.23; had the same cells been imputed with zero, detection among 228 zeros would have dropped to 51%. The difference comes from how rarely the imputed value occurs in the data, not from whether the decision is written down; a guess, however accurate, is not a record.

Where the Decision Lives

The last table is the lesson’s second number. The imputed sheet shows all 1,877 rows as filled and carries zero of the four decisions. It is the same result as the deleted sheet in the previous lesson, but worse: deleting lowers the row count and one can see that something is missing, while imputation does not move the row count at all.

The annotated sheet gives back only one of the four decisions, the imputation method, in two steps; knowing the method does not say which cells it was applied to. In the previous lesson the note gave two decisions, here one: a note kept by hand is written looking at the sheet, and what is not visible in the sheet once the imputation is done is exactly which cell was imputed.

The traced sheet leaves the raw reading column with its 116 blank cells, carries the visit result in a separate column and produces the calculation in a formula column; all four decisions are read in one step. The sheet holds 1,761 filled readings and the 1,877-row calculation at the same time; the 116 difference between them is no longer a gap, it is a written decision. The cost is two extra columns: a spreadsheet is a calculation tool, not a record-keeping tool, and a record is bought with columns.

Summary

• In the reading column 116 cells are blank, and the 2,719.2 m³ that corresponds to them is inside the reported 39,147.7 m³.
• Of the 112 cells that say zero, 57 are real zero consumption and 55 are unread visits, and in the sheet the two cannot be told apart.
• The mean of the same column comes out as 19.23, 18.04, 20.54 and 19.23 m³ again under four decisions; the total stays fixed at 33,866.8 m³ in the first three decisions, because the sum function skips the blank cell anyway.
• Imputation does not change the mean beyond the 0.005 m³ threshold but raises the total to 36,097.6 m³; the region with a blank rate of 17.3% falls by 3.94 m³ when blanks are counted as zero.
• Once the operation is done, of the four decisions the imputed sheet gives back zero, the annotated sheet one in two steps, and the traced sheet four in one step.

Next Step

These two lessons corrected the sheet itself; in both, a single column was examined. A report does not want a single column, it wants a breakdown. The pivot table does this work and carries its own decisions: which field becomes the breakdown, whether a sum or a mean is taken, whether the filter is applied before or after the summary, at which level the calculated field is produced. The next lesson builds three breakdowns from the same data, shows that the three give three different “first” places, and counts in how many steps the decisions of the summary can be read.