Missing Values

The previous topic pulled the data from its source and counted, at every step, how many rows went in and how many came out. The set sits in a file and the row count is known; the only unknown is what is inside the rows.

This lesson opens up the reading field. The field comes out empty in three different ways, and the three do not say the same thing. After that come two defensible decisions: dropping the rows with missing readings, or flagging them and filling them in. The Analysis with Spreadsheets course discussed this decision at the cell level and measured whether the decision could be read back from the sheet; here the measure is a single one, row accounting. How many rows went in, how many rows came out, where the difference went.

• CL1. The raw set is modeled with node. Every field is text exactly as it came from the field form; no type has been forced. The dataset is fiction: 1,400 subscribers of the regional measurement network and their field visit records.
• CL2. Missingness appears in three forms: the cell is empty, the cell holds only whitespace, or the cell has “none” written in it. All three describe the same thing, and all three behave differently.
• CL3. A row whose identifying field (the meter code) is empty cannot be tied to any subscriber; it cannot be filled in, only dropped. Missingness in the measurement field and missingness in the identifying field are two separate decisions.
• CL4. Whether missingness is random is measured by the ratio of the observed number of missing values in each group to the number a random distribution would predict.

Is the Missingness Random

// raw.mjs — the raw set the collection step left behind is a MODEL: every field is
// text exactly as it came from the field form, no type has been forced. The set is
// fiction: 1400 subscribers of the regional measurement network and their field
// visit records.
export const SEED = 20260305, SUBSCRIBERS = 1400;
const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507);
  x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; };
export const rng = (t) => { let s = mix(t);
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };
export 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 by this rate.
export const REGION = [["north", "NR", 0.28, 21, 0.06], ["south", "SU", 0.22, 17, 0.03],
  ["east", "ET", 0.18, 26, 0.27], ["west", "WK", 0.14, 14, 0.03],
  ["central", "CM", 0.18, 23, 0.08]]
  .map(([name, code, share, base, missing]) => ({ name, code, share, base, missing }));
const STREET = ["Çınar", "Söğüt", "Işık", "Ihlamur", "Zeytin", "Menekşe", "Palmiye", "Ceviz"];
const TARIFF = ["Mesken", "mesken", "MESKEN", " Mesken ", "Konut", "konut", "Ticari",
  "ticari", "TİCARİ", "Is yeri", "İş Yeri", "isyeri", "Sanayi", "sanayi", "Tarimsal"];

export const RAW = [];
let prev = null;
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));
  const meter = `${b.code}-${String(1000 + i).padStart(4, "0")}`;
  const shared = prev && prev.region === b.name && r() < 0.03;   // second meter in the same building
  const street = shared ? prev.street : STREET[Math.floor(r() * STREET.length)];
  const door = shared ? prev.door : 1 + Math.floor(r() * 60);
  const unit = shared ? prev.unit : r() < 0.26 ? `/${1 + Math.floor(r() * 9)}` : "";
  prev = { region: b.name, street, door, unit };
  const index0 = 8000 + Math.floor(r() * 11000);
  for (const p of r() < 0.34 ? ["2026-01", "2026-02"] : ["2026-01"]) {
    const q = rng(SEED + 7000 + RAW.length), u = q();
    const truth = Math.round(base * (p === "2026-02" ? 0.94 : 1) * 100) / 100;
    // Missingness shows up in three forms: empty cell, whitespace only, "none" written.
    const miss = u < b.missing ? pick(q(), [0.52, 0.19, 0.29]) : -1;
    const shifted = miss < 0 && q() < 0.008;   // decimal shift: reading written ten times too large
    const written = shifted ? Math.round(truth * 1000) / 100 : truth;
    const reading = miss === 0 ? "" : miss === 1 ? "  " : miss === 2 ? "none"
      : q() < 0.31 ? written.toFixed(2).replace(".", ",") : written.toFixed(2);
    const day = 1 + Math.floor(q() * 27), month = p.slice(5);
    const dateStyle = pick(q(), [0.55, 0.21, 0.24]);
    const date = dateStyle === 0 ? `${p}-${String(day).padStart(2, "0")}`
      : dateStyle === 1 ? `${String(day).padStart(2, "0")}.${month}.2026`
      : `${String(day).padStart(2, "0")}/${month}/2026`;
    const index = index0 + Math.round(truth * (p === "2026-02" ? 2 : 1));
    const dotted = q() < 0.22;
    const addrStyle = pick(q(), [0.30, 0.18, 0.18, 0.16, 0.18]);
    const doorFull = `${door}${unit}`;
    const Cap = `${b.name[0].toUpperCase()}${b.name.slice(1)}`, nameUpper = b.name.toLocaleUpperCase("tr");
    const lower = street.toLocaleLowerCase("tr"), upper = street.toLocaleUpperCase("tr");
    const address = addrStyle === 0 ? `${b.name} mahallesi ${lower} sokak no: ${doorFull}`
      : addrStyle === 1 ? `${Cap} Mahallesi ${street} Sokak No ${doorFull}`
      : addrStyle === 2 ? `${nameUpper} MAHALLESİ ${upper} SOKAK NO ${doorFull}`
      : addrStyle === 3 ? `  ${Cap}  Mahallesi  ${street}  Sokak  No ${doorFull} `
      : `${b.name} mah. ${lower} sk. no:${doorFull}`;
    RAW.push({ subscriber_id: 10001 + i, meter_id: q() < 0.011 ? "" : meter, region: b.name,
      period: p, reading_text: reading,
      index_text: dotted ? `${Math.floor(index / 1000)}.${String(index % 1000).padStart(3, "0")}`
        : `${index}`,
      date_text: date, tariff_text: TARIFF[pick(q(), Array(15).fill(1 / 15))],
      address_text: address, true_m3: truth, written_m3: miss < 0 ? written : null });
    if (q() < 0.09) {   // the same visit entered a second time
      const orig = RAW[RAW.length - 1], style = pick(q(), [0.34, 0.28, 0.38]);
      RAW.push({ ...orig,
        meter_id: style === 0 ? ` ${meter}` : style === 1 ? meter.toLowerCase() : meter,
        address_text: addrStyle === 2 ? `${b.name} mah. ${lower} sk. no:${doorFull}`
          : `${nameUpper} MAHALLESİ ${upper} SOKAK NO ${doorFull}`,
        tariff_text: TARIFF[pick(q(), Array(15).fill(1 / 15))] });
    }
  }
}

// Converts the reading to a number. The decimal separator may be a comma; the traps of
// the conversion itself are measured in the third lesson of this topic, here they are tolerated.
export const toNumber = (t) => { const s = String(t).trim().replace(",", ".");
  return s === "" || !/^\d+(\.\d+)?$/.test(s) ? null : Number(s); };
export const median = (a) => { const l = [...a].sort((x, y) => x - y);
  return l.length % 2 ? l[(l.length - 1) / 2] : (l[l.length / 2 - 1] + l[l.length / 2]) / 2; };
export const fmt = (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 count = (f) => RAW.filter(f).length;
  console.log(`model (seed ${SEED}): ${SUBSCRIBERS} subscribers, raw set left by the ` +
    `collection step: ${RAW.length} rows`);
  const fmt1 = fmt([30, 8, 26]);
  console.log("\n" + fmt1(["missing form", "rows", "common conversion gives"]));
  console.log(fmt1(["empty cell", count((s) => s.reading_text === ""), 'Number("") -> 0']));
  console.log(fmt1(["whitespace only", count((s) => s.reading_text !== "" && s.reading_text.trim() === ""),
    'Number("  ") -> 0']));
  console.log(fmt1(['"none" written', count((s) => s.reading_text.trim() === "none"),
    'Number("none") -> NaN']));
  console.log(fmt1(["identifying field empty", count((s) => s.meter_id.trim() === ""),
    "row cannot be attributed"]));

  const identified = RAW.filter((s) => s.meter_id.trim() !== "");
  const missingRows = identified.filter((s) => toNumber(s.reading_text) === null);
  const fmt2 = fmt([9, 8, 8, 10, 15, 7]);
  console.log("\nis the missingness random");
  console.log(fmt2(["region", "rows", "missing", "rate %", "if random", "ratio"]));
  for (const b of REGION) {
    const n = count((s) => s.region === b.name && s.meter_id.trim() !== "");
    const e = missingRows.filter((s) => s.region === b.name).length;
    const exp = missingRows.length * n / identified.length;
    console.log(fmt2([b.name, n, e, (100 * e / n).toFixed(1), exp.toFixed(1), (e / exp).toFixed(2)]));
  }
  console.log(`of ${identified.length} rows, ${missingRows.length} have no reading; ` +
    `missingness ranges from 3.7% to 28.1% by region`);
}

model (seed 20260305): 1400 subscribers, raw set left by the collection step: 2086 rows

missing form                      rows   common conversion gives
empty cell                         104           Number("") -> 0
whitespace only                     31         Number("  ") -> 0
"none" written                      70     Number("none") -> NaN
identifying field empty             18  row cannot be attributed

is the missingness random
region       rows missing    rate %      if random  ratio
north         591      48       8.1           58.3   0.82
south         414      16       3.9           40.8   0.39
east          352      99      28.1           34.7   2.85
west          324      12       3.7           32.0   0.38
central       387      29       7.5           38.2   0.76
of 2068 rows, 204 have no reading; missingness ranges from 3.7% to 28.1% by region

The first table shows that missingness is not one thing. In 104 rows the cell is truly empty; in 31 rows it holds only whitespace and cannot be told apart from empty by eye; in 70 rows the visit result has been written as “none”. The third column explains why they are counted separately: a common number-conversion call turns the first two into zero, while the third returns a value that is not a number. The same missingness, depending on how it was written, either looks like a measurement or falls out of the calculation.

The second table answers the real question of the lesson. If missingness were random, each region would carry missing values in proportion to its share of the set: the east region would predict 34.7 rows, and in fact carries 99 rows, 2.85 times the prediction. south carries 39% of the prediction, west 38%. The missingness is not noise but a pattern, and that distinction determines the whole decision: with random missingness the dropped rows are a small copy of the set; with non-random missingness they are a particular group.

Whom Dropping Removes

// missing.mjs — same raw set, three questions: which group the drop decision removes
// disproportionately, what filling does not change, and how many rows a filter that
// does not count missingness as missing silently loses.
import { RAW, REGION, toNumber, median, fmt } from "./raw.mjs";

// A row with an empty identifying field cannot be filled in: if a reading cannot be tied to
// a meter, whose consumption it is cannot be written. These rows are dropped and their count is written.
const identified = RAW.filter((s) => s.meter_id.trim() !== "");
const dropped = RAW.length - identified.length;
const measured = identified.filter((s) => toNumber(s.reading_text) !== null);
const regionMedian = new Map(REGION.map((b) => [b.name,
  median(measured.filter((s) => s.region === b.name).map((s) => toNumber(s.reading_text)))]));
const filled = identified.map((s) => toNumber(s.reading_text) === null
  ? { ...s, reading_text: regionMedian.get(s.region).toFixed(2), wasMissing: true } : s);

const mean = (l) => l.reduce((a, s) => a + toNumber(s.reading_text), 0) / l.length;
const fmt1 = fmt([32, 8, 8, 12, 14]);
console.log(fmt1(["same set, two decisions", "in", "out", "mean m3", "east share %"]));
for (const [name, l] of [["D1 drop missing-reading rows", measured],
  ["D2 fill with region median", filled]]) {
  console.log(fmt1([name, identified.length, l.length, mean(l).toFixed(2),
    (100 * l.filter((s) => s.region === "east").length / l.length).toFixed(1)]));
}
console.log(`\ndropping leaves the network mean at ${mean(measured).toFixed(2)} m3, ` +
  `filling gives ${mean(filled).toFixed(2)} m3; the ` +
  `${Math.abs(mean(measured) - mean(filled)).toFixed(2)} m3 between the two numbers is larger than ` +
  `half of the two reported decimal places, but does not describe the size of the decision`);

// Whom dropping removes: each region's share within the set, before and after.
const fmt2 = fmt([9, 9, 8, 11, 11, 9]);
console.log("\n" + fmt2(["region", "dropped", "share %", "before %", "after %", "points"]));
for (const b of REGION) {
  const before = identified.filter((s) => s.region === b.name).length;
  const after = measured.filter((s) => s.region === b.name).length;
  const p0 = 100 * before / identified.length, p1 = 100 * after / measured.length;
  console.log(fmt2([b.name, before - after, (100 * (before - after) /
    (identified.length - measured.length)).toFixed(1), p0.toFixed(1), p1.toFixed(1),
    (p1 - p0).toFixed(1)]));
}

// A filter that counts only the empty cell as missing: the other two forms pass silently.
const blank = identified.filter((s) => s.reading_text !== "" && s.reading_text.trim() === "");
const none = identified.filter((s) => s.reading_text.trim() === "none");
const toZero = blank.filter((s) => Number(s.reading_text) === 0).length;
const nan = none.filter((s) => Number.isNaN(Number(s.reading_text))).length;
console.log(`\na filter that counts only the empty cell as missing fails to see ` +
  `${blank.length + none.length} of ${identified.length} rows: ${toZero} rows silently become 0 m3 ` +
  `consumption, ${nan} rows silently fall out of the numeric filter`);
console.log(`the network mean reported by the same filter comes out as ` +
  `${mean(identified.filter((s) => s.reading_text !== "" && !Number.isNaN(Number(s.reading_text
    .trim().replace(",", ".")))).map((s) => (Number(s.reading_text.trim().replace(",", ".")) === 0
    ? { ...s, reading_text: "0" } : s))).toFixed(2)} m3`);

console.log(`\nrow accounting: in ${RAW.length}, out ${filled.length}; ` +
  `${dropped} rows dropped (identifying field empty), ` +
  `${filled.filter((s) => s.wasMissing).length} rows flagged and filled, ` +
  `0 rows silently lost`);

same set, two decisions               in     out     mean m3  east share %
D1 drop missing-reading rows        2068    1864       22.45          13.6
D2 fill with region median          2068    2068       22.41          17.0

dropping leaves the network mean at 22.45 m3, filling gives 22.41 m3; the 0.04 m3 between the two numbers is larger than half of the two reported decimal places, but does not describe the size of the decision

region     dropped share %   before %    after %   points
north           48    23.5       28.6       29.1      0.6
south           16     7.8       20.0       21.4      1.3
east            99    48.5       17.0       13.6     -3.4
west            12     5.9       15.7       16.7      1.1
central         29    14.2       18.7       19.2      0.5

a filter that counts only the empty cell as missing fails to see 100 of 2068 rows: 31 rows silently become 0 m3 consumption, 69 rows silently fall out of the numeric filter
the network mean reported by the same filter comes out as 22.09 m3

row accounting: in 2086, out 2068; 18 rows dropped (identifying field empty), 204 rows flagged and filled, 0 rows silently lost

The most dangerous column of the first table is the network mean. Dropping discards 204 rows, filling discards none, and the means of the two decisions are 22.45 and 22.41 m³. The difference is 0.04 m³; anyone who looks only at this number concludes that the decision is harmless. The comparison was made not with an equality operator but with a threshold of 0.005 m³, the reporting precision — equality between two floating-point values cannot be asked in the first place.

The last column shows the real cost: the share of the east region falls from 17.0% to 13.6%. 99 of the 204 dropped rows, or 48.5%, come from a single region. This is not an error and cannot be seen from the look of the set: dropping leaves 1,864 perfectly well-formed rows. While the mean is preserved, the composition has changed, and every number produced from here on with a region breakdown carries this shift.

The third block measures silent loss. A filter that counts only the empty cell as missing fails to see 100 of the 2,068 rows: 31 of them silently become zero during conversion to a number and sit there like a real zero consumption, and 69 turn into a value that is not a number and silently fall out at the first numeric filter. The network mean reported with the same filter is 22.09 m³ — no warning, only a number 0.36 m³ lower.

The row accounting of the step is this: 2,086 rows in, 2,068 rows out. Of the 18 rows in between, the identifying field was empty and they were dropped; 204 rows were flagged, filled in, and stayed in the set; the rows silently lost are zero. Being able to write the loss item by item does not show that the decision is right, only that it is visible.

Summary

• In the 2,086 rows the collection step left behind, missingness appears in three forms: 104 empty cells, 31 whitespace-only cells, and 70 cells with “none” written in them; the first two turn into zero under a common conversion.
• The missingness is not random: the rate varies by region from 3.7% to 28.1%, and one region carries 2.85 times the expected number.
• Dropping reduces the set from 2,068 to 1,864 rows and looks harmless by leaving the network mean at 22.45 m³; 99 of the 204 dropped rows come from a single region, and that region’s share falls by 3.4 points.
• A filter that counts only the empty cell as missing fails to see 100 rows — 31 silently become zero, 69 silently fall out — and reports the network mean as 22.09 m³.
• Row accounting of the step: 2,086 in, 2,068 out; 18 dropped, 204 flagged and filled, 0 silently lost.

Next Step

The remaining 2,068 rows were all assumed to represent a separate visit. The assumption does not hold: the reading of the same subscriber in the same period has been entered a second time in a number of rows, and in the second entry the meter code sometimes carries a leading space and sometimes lowercase letters. The next lesson applies three deduplication criteria and counts how many rows merge as the criterion changes, then links illegibly written support requests to the subscriber register: how many of the links built by exact matching and by a similarity threshold go to the wrong subscriber.