Reproducible Preparation

The dataset was prepared in seventeen steps, and at each step the rows going in, coming out, and disappearing were counted — but those counts stayed inside their own lessons, and none of them sits in one place. Someone who prepares the same dataset a second time cannot find out which threshold was chosen, which ordering was used, or which seed the sample was drawn with.

The Analysis with Spreadsheets course closed with this question: only seven of thirty-two decisions could be read back, because the decisions were buried in cells. Writing a script looks like the answer to that, and partly it is. This lesson measures where the script falls short: how many steps give the same result from the same data, and how many decisions can be read after the run.

• TF25. A preparation pipeline is documented when the data of its steps is written down: each step’s name, its decisions, and its row accounting. The script itself does not take the place of this record.
• TF26. A step is reproducible if every value that determines its result is written in the script. If one of the values comes from the run environment, the same script produces a different result from the same data, and the difference is visible nowhere.

Writing the Pipeline as Data

// pipeline.mjs — MODELDIR: the course's seventeen preparation steps are written as data.
// The "silent" field is the silent loss that lesson measured — a record that drops or is
// corrupted without raising a warning — and it was read from that lesson. "params" holds the
// step's decisions; decisions whose value is "environment" are not written in the script,
// they come from outside at run time.
export const PIPELINE = [
  { n: 1, topic: "collection", name: "file formats", silent: 142,
    params: { delimiter: ";", quotes: "kept", encoding: "utf-8" } },
  { n: 2, topic: "collection", name: "extracting from a database", silent: 766,
    params: { unit: "subscriber-period", join: "unique key" } },
  { n: 3, topic: "collection", name: "collecting from interfaces", silent: 512,
    params: { paging: "cursor", scope: "all periods", rate: "queue within the limit" } },
  { n: 4, topic: "collection", name: "structured parsing", silent: 1877,
    params: { selector: "semantic", check: "source total" } },
  { n: 5, topic: "collection", name: "sampling", silent: 0,
    params: { method: "stratified", size: 200, seed: "environment" } },
  { n: 6, topic: "cleaning", name: "missing values", silent: 100,
    params: { missing: "empty + blank + none", action: "fill" } },
  { n: 7, topic: "cleaning", name: "duplicate records", silent: 0,
    params: { key: "trimmed meter code", criterion: "exact match" } },
  { n: 8, topic: "cleaning", name: "type conversions", silent: 465,
    params: { number: "format recognized first", date: "format recognized first" } },
  { n: 9, topic: "cleaning", name: "outliers", silent: 0,
    params: { method: "interquartile range", multiplier: "environment", action: "quarantine set" } },
  { n: 10, topic: "cleaning", name: "categorical correction", silent: 216,
    params: { key: "simplified", matching: "tariff table" } },
  { n: 11, topic: "cleaning", name: "text normalization", silent: 36,
    params: { digits: "four digits", lowercasing: "locale-aware" } },
  { n: 12, topic: "transformation", name: "merging", silent: 69,
    params: { type: "inner", key: "subscriber_no + period" } },
  { n: 13, topic: "transformation", name: "reshaping", silent: 103,
    params: { shape: "wide", empty: "skipped" } },
  { n: 14, topic: "transformation", name: "grouping", silent: 0,
    params: { breakdown: "region", summary: "mean + spread" } },
  { n: 15, topic: "transformation", name: "derived variables", silent: 328,
    params: { derivation: "per person + change", undefined: "counted with reason" } },
  { n: 16, topic: "transformation", name: "categorical encoding", silent: 0,
    params: { region: "unordered coding", satisfaction: "ordered", ordering: "environment" } },
  { n: 17, topic: "transformation", name: "preparation ledger", silent: 0,
    params: { ledger: "every step", measure: "in + out + silent" } },
];
export const DECISIONS = PIPELINE.reduce((a, x) => a + Object.keys(x.params).length, 0);
export const ENV = PIPELINE.flatMap((x) => Object.entries(x.params)
  .filter(([, v]) => v === "environment").map(([k]) => `${x.n}.${k}`));
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(`pipeline ledger: ${PIPELINE.length} steps, ${DECISIONS} decisions, ${ENV.length} decisions ` +
    `come from the run environment (${ENV.join(", ")})`);
  const y = gen([16, 8, 11, 13]);
  console.log("\n" + y(["topic", "steps", "decisions", "silent loss"]));
  for (const t of [...new Set(PIPELINE.map((x) => x.topic))]) {
    const l = PIPELINE.filter((x) => x.topic === t);
    console.log(y([t, l.length, l.reduce((a, x) => a + Object.keys(x.params).length, 0),
      l.reduce((a, x) => a + x.silent, 0)]));
  }
  const S = PIPELINE.reduce((a, x) => a + x.silent, 0);
  console.log(y(["total", PIPELINE.length, DECISIONS, S]));
  console.log(`\nbreakdown of the silent loss (step: records)`);
  console.log("  " + PIPELINE.filter((x) => x.silent > 0).map((x) => `${x.n}:${x.silent}`).join("  "));
  const actual = PIPELINE[11].silent;
  console.log(`total ${S} records, largest item step 4 (${PIPELINE[3].silent}); ${actual} of them ` +
    `actually dropped in the adopted pipeline, the remaining ${S - actual} records are the measured ` +
    `cost of the common decision that was not chosen`);
}

pipeline ledger: 17 steps, 39 decisions, 3 decisions come from the run environment (5.seed, 9.multiplier, 16.ordering)

topic              steps  decisions  silent loss
collection             5         13         3297
cleaning               6         13          817
transformation         6         13          500
total                 17         39         4614

breakdown of the silent loss (step: records)
  1:142  2:766  3:512  4:1877  6:100  8:465  10:216  11:36  12:69  13:103  15:328
total 4614 records, largest item step 4 (1877); 69 of them actually dropped in the adopted pipeline, the remaining 4545 records are the measured cost of the common decision that was not chosen

The pipeline itself is a table here. Each of the seventeen steps carries a name, a silent loss item, and a set of decisions; the decisions total 39 and are spread 13 apiece across the three topics. The format has a single purpose: so that the code that runs a step and the decision behind the step can be read separately.

The breakdown line gives the course total. The silent loss measured across the seventeen steps is 4,614 records; the largest item is the fourth step, because when a column is added to the page no selector breaks, and all 1,877 records are read from the wrong field. The number carries an important distinction: of the 4,614, only 69 actually dropped in the adopted pipeline — the subscribers the inner join never showed. The remaining 4,545 records are the measured cost of the common decision at that step, and they did not occur because that decision was not chosen. One is the real loss of the pipeline; the other is the size of the risk that was avoided because the loss was written down.

The Same Script, Two Runs

// runs.mjs — the three environment-fed steps of the pipeline are run twice. Same data,
// same code, two runs. Then the same three steps are run with values written into the script.
import { PIPELINE, DECISIONS, ENV, gen } from "./pipeline.mjs";

const rng = (t) => { let s = (Math.imul(t ^ 0x9e3779b9, 2246822507) >>> 0);
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };
// A small model dataset; it is fictional and exists only to run the three steps.
// Every 97th reading has a shifted decimal point, every 41st reading has a high value.
const r0 = rng(20260218), DATASET = [];
for (let i = 0; i < 600; i++) {
  const t = Math.round((6 + r0() * 46) * 100) / 100;
  DATASET.push({ reading: i % 97 === 0 ? t * 10 : i % 41 === 0 ? t * 2.2 : t,
    region: ["north", "south", "east"][Math.floor(r0() * 3)] });
}

const mean = (a) => a.reduce((x, y) => x + y, 0) / a.length;
const STEPS = [
  ["5 sampling", (l, o) => { const r = rng(o.seed);
    return l.map((x) => ({ x, u: r() })).sort((a, b) => a.u - b.u)
      .slice(0, 200).map((a) => a.x); }],
  ["9 outlier removal", (l, o) => { const v = l.map((x) => x.reading).sort((a, b) => a - b);
    const q = (t) => v[Math.floor(t * (v.length - 1))], q1 = q(0.25), q3 = q(0.75);
    const iqr = q3 - q1;
    return l.filter((x) => x.reading >= q1 - o.multiplier * iqr && x.reading <= q3 + o.multiplier * iqr); }],
  ["16 encoding threshold", (l, o) => { const a = [...new Set(l.map((x) => x.region))];
    const d = o.ordering === "alphabetical" ? [...a].sort() : a;
    const k = Object.fromEntries(d.map((x, j) => [x, j + 1]));
    return l.filter((x) => k[x.region] >= 2); }],
];
const run = (o) => STEPS.map(([name, f]) => { const c = f(DATASET, o);
  return { name, rows: c.length, mean: mean(c.map((x) => x.reading)) }; });

// The environment differs between the two runs: the seed comes from the clock, the multiplier from
// a configuration, the ordering from the source the category names arrive from. None of the three
// is written in the script.
const R1 = run({ seed: 4711, multiplier: 1.5, ordering: "alphabetical" });
const R2 = run({ seed: 8123, multiplier: 3.0, ordering: "first seen" });
const THRESHOLD = 0.0005; // m3; two means closer than this threshold count as the same
const same = (a, b) => a.rows === b.rows && Math.abs(a.mean - b.mean) < THRESHOLD;

const y = gen([23, 10, 12, 10, 12, 8]);
console.log(`same data (${DATASET.length} rows), same code, two runs:`);
console.log("\n" + y(["step", "R1 rows", "R1 mean", "R2 rows", "R2 mean", "same?"]));
R1.forEach((a, j) => console.log(y([a.name, a.rows, a.mean.toFixed(3), R2[j].rows,
  R2[j].mean.toFixed(3), same(a, R2[j]) ? "yes" : "no"])));

// The fix: all three values are written into the script; the environment is no longer read.
const FIXED = { seed: 4711, multiplier: 1.5, ordering: "alphabetical" };
const D1 = run(FIXED), D2 = run(FIXED);
const held = D1.filter((a, j) => same(a, D2[j])).length;
const before = R1.filter((a, j) => same(a, R2[j])).length;
console.log(`\nof the ${STEPS.length} environment-fed steps, ${before} give the same result in both runs; ` +
  `once the values are written into the script, ${held} do. Of the pipeline's ${PIPELINE.length} steps, ` +
  `${PIPELINE.length - STEPS.length + before} are reproducible before the fix and ${PIPELINE.length} ` +
  `after the fix`);

// Readability: can the value of a decision be read from somewhere after the run.
const ratio = (a, b) => `${a}/${b} (${(100 * a / b).toFixed(0)}%)`;
console.log(`\ndecisions that can be read back — only in cells ${ratio(7, 32)}, in the script with no ` +
  `ledger ${ratio(DECISIONS - ENV.length, DECISIONS)}, with a run ledger ${ratio(DECISIONS, DECISIONS)}`);
console.log(`the three unreadable decisions are the ones that come from the environment; if no ledger is ` +
  `kept, they cannot be read from anywhere after the run`);

same data (600 rows), same code, two runs:

step                      R1 rows     R1 mean   R2 rows     R2 mean   same?
5 sampling                    200      31.886       200      32.361      no
9 outlier removal             590      29.468       593      29.806      no
16 encoding threshold         404      31.965       391      32.968      no

of the 3 environment-fed steps, 0 give the same result in both runs; once the values are written into the script, 3 do. Of the pipeline's 17 steps, 14 are reproducible before the fix and 17 after the fix

decisions that can be read back — only in cells 7/32 (22%), in the script with no ledger 36/39 (92%), with a run ledger 39/39 (100%)
the three unreadable decisions are the ones that come from the environment; if no ledger is kept, they cannot be read from anywhere after the run

The table above shows why the script alone is not enough. The parameters of three steps are not written in the script: the sampling seed comes from the clock, the outlier removal multiplier from a configuration, and the encoding ordering from the source the category names arrive from. The code is the same in both runs, and so is the data, the same 600 rows; the result differs in all three.

The shape of the differences varies. Sampling draws 200 rows in both runs and moves the mean between 31.886 and 32.361 m³: the row count never changes, so a check that looks at row counts sees nothing. Outlier removal leaves 590 rows in one run and 593 in the other. The encoding threshold selects 404 rows in one and 391 in the other — this is the pipeline counterpart of the previous lesson’s finding, and because the ordering decision comes from the environment, it is no longer even visible that it was chosen.

14 of the pipeline’s seventeen steps are reproducible before the fix, and 17 once the three values are written into the script. The fix is not a rewrite; it is making three numbers readable from the code.

The last line closes the question at the course’s opening. While the decisions were inside cells, 7 of 32 decisions could be read back, 22%. Once they are written into the script, 36 of 39 decisions are readable, 92%. When a run ledger is kept — that is, when each step writes its own decisions and row accounting to the output — the ratio becomes 39 of 39. The gap between them is where reproducibility stands: the script shows what could be done, the ledger shows what was done.

Summary

• A preparation pipeline is documented when the data of its steps is written down: 17 steps, 39 decisions, one row accounting per step.
• The silent loss measured across the seventeen steps is 4,614 records; 69 occurred in the adopted pipeline, and 4,545 are the measured cost of the common decision that was not chosen.
• Three steps whose parameters come from the run environment give different results in two runs over the same data; because the row count does not change in sampling, the difference escapes the row check.
• Once the values are written into the script, the number of reproducible steps rises from 14 to 17.
• The readability of decisions is 7 of 32 (22%) in cells, 36 of 39 (92%) in a script with no ledger, and 39 of 39 with a run ledger.

Course Wrap-Up

The accounting for the seventeen lessons sits in a single table, and each number was read from its own lesson.

One topic’s rows out do not equal the next topic’s rows in, because the three topics work on three separate pulls of the same measurement network: collection measures 1,877 readings, cleaning a raw pull of 2,086 rows, and transformation 3,471 readings over three periods. What is carried end to end is not a single number but the accounting itself.

The course’s rule is the way this table is read: the sum of the preparation steps is an accounting; if the difference between what went in and what came out cannot be written item by item, the data has not been prepared, only altered. Cleaning whose loss you cannot write down is not cleaning. The total silent loss measured across the seventeen lessons is 4,614 records, and the existence of this number is not a flaw but the output of the course: a silent loss that is not written down looks like zero.

The dataset was prepared in seventeen lessons, and the accounting of each step was written down. But all of these steps were done one by one, with small pieces of hand-written code; no toolset that does the same work over thousands of rows, repeatably and readably, was used at all. The next course, Data Analysis with Python, takes up the counterpart of every hand-written step in a data frame interface.