Derived Variables

Aggregation lowered the row count but did not touch the set of columns; what we hold is still measured fields only. The questions of analysis, however, mostly ask not for the measured value but for a quantity derived from it: the answer to “which region uses more” is raw cubic meters, while the answer to “which region uses more per person” is not found in the dataset.

Unlike the previous three steps, a derivation does not change the row count at all, and for that reason it looks harmless. The measure of this lesson lies there: how many rows leave the derived value undefined while the row count stays fixed. An undefined value does not drop the row; it only makes the row ready to be dropped in the next step, and the loss happens not in the derivation but in the first filter written after it.

• TF16. Derived variable is a new field calculated from existing fields: it does not change the row count, it adds a column. The dataset is carried over from the previous lesson and is fictional.
• TF17. A derivation stays undefined in a row when one of its inputs is missing or zero. Three causes are counted separately: division by zero, missing input, and undefinedness by definition.
• TF18. A missing input silently turns into zero in arithmetic; a missing input and a real zero produce the same result, and the cause can be read only from the input.
• TF19. A row that carries an undefined value does not drop; it is filtered in the next step, and the filter is a decision separate from the derivation and does not appear in its report.
• TF20. A filter does not remove at random: if the cause of undefinedness piles up in one group, that group is removed disproportionately and comparison between groups is distorted.

In How Many Rows the Derivation Stays Undefined

// derive.mjs — MODEL: the 3,199-row long-format dataset of the previous lesson and two
// derived variables. The dataset is fictional; no interface is imitated.
export const SEED = 20260218, RAW = 1400, PERIOD = ["2026-01", "2026-02", "2026-03"];
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]];
// Household size varies by region; zero persons means "no household" (a commercial
// subscription) and its share is high in the center. Because the number of draws does not
// change, the row counts and usage values of the previous lessons are preserved exactly.
const HOUSEHOLD = { north: [0.05, 0.22, 0.30, 0.25, 0.13, 0.05],
  south: [0.04, 0.18, 0.26, 0.28, 0.16, 0.08], east: [0.02, 0.05, 0.12, 0.26, 0.33, 0.22],
  west: [0.05, 0.42, 0.32, 0.14, 0.05, 0.02], central: [0.19, 0.26, 0.28, 0.17, 0.07, 0.03] };
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(), HOUSEHOLD[b[0]]),
    satisfaction: pick(r(), [0.08, 0.14, 0.27, 0.34, 0.17]) + 1 };
  (r() < 0.046 ? DROPPED : SUBSCRIBER).push(k);
  if (r() < 0.031) k.household_size = null; // a record whose household information was never entered
}
// The dataset produced by the previous lessons: unmatched readings are left out, and the
// same subscriber+period pair is one row. The draws of dropped subscribers and of the
// deduplicated second record are preserved so that the usage values stay the same as in
// the previous lessons.
const keys = new Set(SUBSCRIBER.map((a) => a.subscriber_id));
const HOUSEHOLD_SIZE = new Map(SUBSCRIBER.map((a) => [a.subscriber_id, a.household_size]));
export const LONG = [];
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; }
    LONG.push({ subscriber_id: k.subscriber_id, region: k.region, period: PERIOD[d],
      usage_m3: t, household_size: HOUSEHOLD_SIZE.get(k.subscriber_id) });
    r();
  }
}

const value = new Map(LONG.map((o) => [`${o.subscriber_id}|${o.period}`, o.usage_m3]));
const previous = (o) => { const j = PERIOD.indexOf(o.period);
  return j === 0 ? undefined : value.get(`${o.subscriber_id}|${PERIOD[j - 1]}`); };
// Two derivations, each giving a value and a cause of undefinedness. The cause is read
// from the input, not from the output; the same undefined value can come from different causes.
export const DERIVATION = [
  ["T1 per-person usage", (o) => o.usage_m3 / o.household_size,
    (o) => o.household_size === null ? "missing" : o.household_size === 0 ? "zero" : "-"],
  ["T2 period change rate", (o) => (o.usage_m3 - previous(o)) / previous(o),
    (o) => PERIOD.indexOf(o.period) === 0 ? "definition" : previous(o) === undefined ? "missing"
      : previous(o) === 0 ? "zero" : "-"],
];
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]}`) {
  const count = (f) => LONG.filter(f).length, T = LONG.reduce((a, o) => a + o.usage_m3, 0);
  console.log(`model (seed ${SEED}): in ${LONG.length} rows, ${T.toFixed(1)} m3, ` +
    `mean ${(T / LONG.length).toFixed(3)} m3 — same as the previous lesson`);
  const y = gen([25, 8, 8, 11, 14, 15, 14]);
  console.log("\n" + y(["derived variable", "in", "out", "undefined", "div by zero",
    "missing input", "by definition"]));
  for (const [name, f, cause] of DERIVATION) {
    const n = LONG.map(cause);
    console.log(y([name, LONG.length, LONG.length, count((o) => !Number.isFinite(f(o))),
      n.filter((x) => x === "zero").length, n.filter((x) => x === "missing").length,
      n.filter((x) => x === "definition").length]));
  }
  const t1 = LONG.map(DERIVATION[0][1]);
  console.log(`\nneither derivation changed the output rows or raised an error; the missing input ` +
    `and the zero household of T1 merged in the same result: ${t1.filter((x) => x === Infinity).length} infinite, ` +
    `${t1.filter((x) => Number.isNaN(x)).length} not-a-number values`);
  // The "missing input" column of T2 came out zero; it changes once the expected set of cells is built.
  const cells = new Set(LONG.map((o) => o.subscriber_id)).size * (PERIOD.length - 1);
  const defined = count((o) => Number.isFinite(DERIVATION[1][1](o)));
  const zeroDiv = count((o) => DERIVATION[1][2](o) === "zero");
  console.log(`\nT2 in the expected set of cells: ${cells} cells, ${defined} defined, ${zeroDiv} division ` +
    `by zero, ${cells - defined - zeroDiv} cells because of a period never read — in the long format ` +
    `those cells were not even counted as undefined because there is no row`);
}

model (seed 20260218): in 3199 rows, 63060.6 m3, mean 19.713 m3 — same as the previous lesson

derived variable               in     out  undefined   div by zero  missing input by definition
T1 per-person usage          3199    3199        328           216            112             0
T2 period change rate        3199    3199       1334            74              0          1260

neither derivation changed the output rows or raised an error; the missing input and the zero household of T1 merged in the same result: 315 infinite, 13 not-a-number values

T2 in the expected set of cells: 2520 cells, 1865 defined, 74 division by zero, 581 cells because of a period never read — in the long format those cells were not even counted as undefined because there is no row

The in and out columns are the same on both rows: 3,199. Merging had dropped and multiplied rows, reshaping had turned rows into cells, aggregation had reduced 3,199 rows to five; a derivation does none of these. Even so, there are two separate numbers in the undefined column.

On the T1 row, per-person usage is undefined in 328 of the 3,199 rows, and the cause has two items. In 216 rows the number of persons in the household is zero; this is not a data error, because for a subscription with no household there is no such quantity as per-person usage. In 112 rows the household information was never entered; this is a gap and can be filled. The two items call for different decisions but look the same in the output: a missing input is silently turned into zero, division by zero gives infinity, and both melt into the same 315 infinite values; the remaining 13 undefined values come from rows whose usage is also zero. The type of an undefined value does not give its cause, which is why the count of undefined values is reported together with its cause.

The T2 row shows another item. The undefined count is larger at 1,334, but 1,260 of it is by definition: the first period has no period before it, and therefore no change rate. This is not a loss but the consequence of the definition. The remaining 74 rows are a division by zero.

The real warning is the zero in the missing input column of the T2 row. That zero does not say that there is no gap in the data; it says that the question asked in the long format cannot see the gap. In the expected set of cells, 1,260 subscribers have two transitions each, 2,520 cells in total, and only 1,865 of them are defined. 581 cells are undefined because of a period that was never read; these are the 581 empty cells of the previous lesson, and because there is no row in the long format they cannot even be counted as undefined. The derived value of a row that does not exist does not exist either.

The Next Step Pays for the Undefined

// impact.mjs — the effect of undefined rows on the result: the same question is answered
// four ways, and how many rows the filter silently drops from which region is counted
// region by region.
import { LONG, mean, gen } from "./derive.mjs";

const perPerson = (o) => o.usage_m3 / o.household_size;
const finite = LONG.filter((o) => Number.isFinite(perPerson(o)));
const T = (a, f) => a.reduce((x, o) => x + f(o), 0);
const ratioOf = (a, d) => T(a, (o) => o.usage_m3) / T(a, d);
const PATH = [
  ["Y1 row ratios are averaged", LONG, (a) => mean(a.map(perPerson))],
  ["Y2 finite rows are filtered", finite, (a) => mean(a.map(perPerson))],
  ["Y3 total m3 / total persons", LONG, (a) => ratioOf(a, (o) => o.household_size ?? 0)],
  ["Y4 filtered total / total", finite, (a) => ratioOf(a, (o) => o.household_size)],
];
const y = gen([37, 9, 10, 11, 11]);
console.log(`question: how many m3 does one person use on average in one period`);
console.log("\n" + y(["answer path", "in", "counted", "undefined", "result"]));
for (const [name, a, f] of PATH) console.log(y([name, LONG.length, a.length,
  a.filter((o) => !Number.isFinite(perPerson(o))).length, f(a).toFixed(3)]));
console.log(`\nY3 added the usage of ${LONG.length - finite.length} undefined rows to the numerator, ` +
  `added zero persons to the denominator; produced no warning`);

// The filter does not remove at random: the dropped rows are not spread evenly across regions.
const stats = [...new Set(LONG.map((o) => o.region))].map((b) => {
  const l = LONG.filter((o) => o.region === b), s = l.filter((o) => Number.isFinite(perPerson(o)));
  return { region: b, n: l.length, dropped: l.length - s.length,
    raw: mean(l.map((o) => o.usage_m3)), pp: mean(s.map(perPerson)) };
}).sort((a, b) => b.raw - a.raw);
const rankBy = (f) => Object.fromEntries([...stats].sort((a, b) => f(b) - f(a))
  .map((s, j) => [s.region, j + 1]));
const rankRaw = rankBy((x) => x.raw), rankPp = rankBy((x) => x.pp);
const y2 = gen([9, 8, 9, 9, 10, 12, 9, 9]);
console.log("\n" + y2(["region", "readings", "dropped", "share %", "raw mean", "per person",
  "rank raw", "rank pp"]));
for (const s of stats) console.log(y2([s.region, s.n, s.dropped, (100 * s.dropped / s.n).toFixed(1),
  s.raw.toFixed(2), s.pp.toFixed(2), rankRaw[s.region], rankPp[s.region]]));

const m3 = T(LONG.filter((o) => !Number.isFinite(perPerson(o))), (o) => o.usage_m3);
const shares = stats.map((s) => 100 * s.dropped / s.n);
console.log(`\nin ${LONG.length} rows -> out ${LONG.length} rows; the filter dropped ` +
  `${LONG.length - finite.length} rows and ${m3.toFixed(1)} m3, that is ` +
  `${(100 * m3 / T(LONG, (o) => o.usage_m3)).toFixed(1)}% of the total; the share by region ` +
  `varies between ${Math.min(...shares).toFixed(1)} and ${Math.max(...shares).toFixed(1)}`);
console.log(`${stats.filter((s) => rankRaw[s.region] !== rankPp[s.region]).length} regions ` +
  `changed rank; the first in per-person usage is ${stats.find((s) => rankPp[s.region] === 1).region}`);

question: how many m3 does one person use on average in one period

answer path                                 in   counted  undefined     result
Y1 row ratios are averaged                3199      3199        328        NaN
Y2 finite rows are filtered               3199      2871          0      9.972
Y3 total m3 / total persons               3199      3199        328      8.604
Y4 filtered total / total                 3199      2871          0      7.735

Y3 added the usage of 328 undefined rows to the numerator, added zero persons to the denominator; produced no warning

region   readings  dropped  share %  raw mean  per person rank raw  rank pp
east          557       30      5.4     24.67        8.43        1        4
central       600      127     21.2     22.40       14.00        2        1
north         921       52      5.6     20.21       10.67        3        2
south         645       61      9.5     16.60        7.50        4        5
west          476       58     12.2     13.78        9.37        5        3

in 3199 rows -> out 3199 rows; the filter dropped 328 rows and 6372.0 m3, that is 10.1% of the total; the share by region varies between 5.4 and 21.2
5 regions changed rank; the first in per-person usage is central

All four paths ask the same question, and all four are defensible. Y1 filters nothing and produces the only visible sign: the result is not a number. This is the only way in which undefinedness reaches the report, but it is not an answer, which is why the shortest fix is added.

Y2 is that fix and gives 9.972 m³. It is written as if the filter dropped not a single row, yet the counted rows are not 3,199 but 2,871, and the 328 dropped rows carry 6,372.0 m³, that is 10.1% of total usage. The report says “9.972 m³ per person”; it does not say which 328 rows were not counted.

Y3 is the silent loss of this lesson. Dividing total usage by the total number of household persons is a reasonable path, it produces no undefined value, and the result comes out clean at 8.604 m³. The 328 undefined rows have not been filtered here: their usage entered the numerator, and their household persons entered the denominator as zero. The only thing that shows the difference is Y4: the same calculation on the finite rows gives 7.735 m³, and the 0.869 m³ gap between them comes entirely from those zero denominators.

The difference between Y2 and Y4, on the other hand, is a weighting decision set up in the previous lesson: 9.972 m³ is the mean of the ratios, and 7.735 m³ is the ratio of the means.

The lower table shows why the filter is an item of the calculation. The 328 dropped rows are not spread evenly across regions: 5.4% of the readings of the east region drop, while 21.2% of those of the central region do. The cause is the data itself: subscriptions with no household are concentrated in the center. Someone comparing regions does not know that each region is represented with a different share. The last line gives the power and the risk of a derivation together: all five regions changed rank, and east, first in raw usage, becomes fourth in per-person usage. Both rankings are correct; what is risky is that both carry the name “usage”.

The row accounting of the lesson closes this way: 3,199 rows went in, 3,199 rows came out, and no row dropped in the derivation. In return, 328 rows carry an undefined value in per-person usage and 1,334 rows in the period change rate, and they will silently drop in the filter of the next step. When the count of undefined values is not written down, this drop is invisible, because in the report of the derivation the in and out counts are equal.

Summary

• A derivation does not change the row count: 3,199 rows went in, 3,199 rows came out, and no error was raised. What changes is the number of undefined values the rows carry.
• Undefinedness is separated by cause: in per-person usage, 216 rows are division by zero and 112 rows are missing input; of the 1,334 rows in the period change rate, 1,260 are by definition.
• A missing input silently turns into zero in arithmetic, and the 112 missing and 216 zero melt into the same 315 infinite values. The long format cannot see 581 cells at all.
• The same question gives four answers by four defensible paths: a not-a-number value, 9.972, 8.604, and 7.735 m³. The path that filters leaves 6,372.0 m³, that is 10.1% of the total, outside the calculation.
• The filter does not remove at random: the share by region varies between 5.4% and 21.2%, and the derived variable changes the rank of all five regions.

Next Step

The derivation produced numeric fields from numeric fields. The dataset still has fields that are waiting to be made numeric, and there is no number in them at all: the region is a name, the visit outcome is a label, the satisfaction level is a grade. Most tools of analysis want numbers, so categories are encoded, and encoding looks mechanical: each category is given an integer and the job is done. Its cost is the meaning the given number brings with it: an ordered code claims order and distance between categories, and when the region is given a code from 1 to 5, this claim enters the data without ever asking it. The next lesson separates ordered and unordered encoding and shows with numbers the wrong result that ordered encoding produces for an unordered category.