--- title: 'Data Collection and Preparation' source: 'https://academia.sh/en/courses/data-collection-and-preparation' language: en updated: '2026-10-07T04:31:11+00:00' license: 'CC BY-SA 4.0' lessons: 17 outcomes: - 'Collects data programmatically from different sources and counts the rows that enter, leave and silently drop at each step' - 'Cleans dirty data with documented steps and writes down the row accounting of each step' - 'Transforms data into a form suitable for analysis and makes the preparation reproducible' --- # Data Collection and Preparation Writing down item by item how each step on the path from source to analysis changes the row count: the row a parsing decision silently drops, the rows a query returns not matching the unit of analysis, the record skipped by consuming pagination incorrectly and the collection time set by an order that respects the rate limit, the selector that breaks when the structure changes, the effect of a sampling decision on representation, the group that missingness eliminates disproportionately, the false match in identity matching, the silent coercion that looks error-free, the non-overlapping sets of outlier methods, the match that category harmonization and text normalization recover, the rows a join multiplies, the losslessness of reshaping, the within-group variability lost in aggregation, the derivation that stays undefined, categorical encoding that violates the scale, and the reproducibility of the steps through a script. ## Data Collection Counting how many rows the data loses on its way into the analysis environment: reading the same file with two parsing decisions and the rows that silently drop because of quoted fields, line breaks, and encoding; a query's returned row not matching the unit of analysis and a join multiplying rows; the record skipped by consuming pagination wrongly and the collection time set by a queue that obeys the rate limit; the number of selectors that break when the page structure changes and the limit of access permission; the three answers that three sampling decisions give to the same question. ### File Formats Source: https://academia.sh/en/courses/data-collection-and-preparation/file-formats The export file that produces the previous course's 2,037-row page is read with two parsing decisions, and two different sets come out: the quote-preserving read extracts all 2,037 records, while the naive split extracts 1,934 records from 2,076 physical lines and drops 142 lines without any warning — 78 half-lines split at a line break inside the address, 64 lines whose fields shifted. Of the remaining 1,934 records, 33 survive but carry a house number shifted out of the address into the reading field; the totals diverge by 984.2 m³, and the region name of 1,157 records does not match the list because the file was decoded with the wrong encoding. With the same 2,037 records held in a columnar structure, learning the total reading means reading 4,074 bytes instead of 108,230. The previous course made thirty-two decisions in eleven lessons, and only seven of them could be read back from the page. But the page itself came from somewhere: 2,037 rows, 1,400 subscribers, 42,645.6 m³. This lesson looks at what came before that page and asks a single question: how many rows did the file lose on its way into the analysis environment? The answer depends on decisions, and decisions do not raise errors. When a parser meets a corrupt row, it does not stop and warn; it silently skips that row and keeps reading. That is this lesson's unit of measure, and it runs through the whole course: at every step, **rows in**, **rows out**, and **where they went**. The most important item of the third is **silent loss**, that is, a row that drops out without producing any warning. - **DC1.** The file is fiction. Instead of a real export, a generator that produces the same structure was written; the seed is visible and the output is the same from run to run. **It is a model.** - **DC2.** The generated set yields the previous course's page: 2,037 records, 1,400 distinct subscribers, 1,877 distinct subscriber-period pairs, 42,645.6 m³ in total. - **DC3.** The delimiter is a semicolon and the quote is a double quote; the address field is written inside quotes in every record, and the file is written in UTF-8. - **DC4.** The threshold for counting two totals as the same is 0.05 m³, because readings are reported with one decimal and a difference below that can only come from rounding. - **DC5.** The largest reading the generator can produce is 37.0 m³; a larger value in the reading field shows that the field does not carry a reading. #### What Is Inside the File ```js // source.mjs — a fictional export from the regional measurement network. THIS IS A // MODEL: a generator that produces the same structure was written instead of a // real file; the seed is visible and the output is the same from run to run. The // records produce the page from M26/K02: 1400 subscribers, 1877 subscriber-period // pairs, 160 repeated rows, 2037 records in total. export const SEED = 1246, DELIM = ";", QUOTE = '"'; export const REGION = ["kuzey", "doğu", "merkez", "güney", "batı"]; export const HEADER = ["subscriber_no", "meter_no", "period", "address", "reading_m3", "region"]; const STREET = ["Pine Road", "New Dist Garden Rd", "Station Blvd", "Creek St", "Olive Road"]; export const rng = (t) => { let s = t >>> 0; return () => { s = (Math.imul(s, 1103515245) + 12345) >>> 0; return s / 4294967296; }; }; // Records: first the first period of 1400 subscribers, then the second period of 477 // subscribers, then the repetition of the first 160 rows (32 exact copies, 128 // corrected readings). export function records() { const r = rng(SEED), read = () => 5 + Math.floor(r() * 321) / 10, k = []; const make = (i, period, reading) => ({ subscriber: 10000 + i, meter: `S-${4000 + i}`, period, address: `${STREET[i % 5]} No ${12 + (i % 88)}`, reading, region: REGION[i % 5] }); for (let i = 0; i < 1400; i++) k.push(make(i, "2024-01", read())); for (let i = 0; i < 477; i++) k.push(make(i, "2024-02", read())); for (let i = 0; i < 160; i++) k.push(make(i, "2024-01", i < 32 ? k[i].reading : read())); return k; } // Dirtiness rule: the address of every 21st record contains the delimiter (one in // three ends with a bare house number after the delimiter), and one in 50 of the // rest contains a line break. The address field is written inside quotes in every // record. export const HAS_DELIM = (i) => i % 21 === 0; export const HAS_NEWLINE = (i) => !HAS_DELIM(i) && i % 50 === 13; export const ENDS_NUMBER = (i) => HAS_DELIM(i) && (i / 21) % 3 === 0; export function exportFile() { const k = records(), s = [HEADER.join(DELIM)]; k.forEach((x, i) => { let address = x.address; if (HAS_DELIM(i)) address += ENDS_NUMBER(i) ? `${DELIM} ${45 + (i % 30)}` : `${DELIM} Suite ${1 + i % 8}`; else if (HAS_NEWLINE(i)) address += `\nFlr ${1 + (i % 6)}`; s.push([x.subscriber, x.meter, x.period, QUOTE + address + QUOTE, x.reading.toFixed(1), x.region] .join(DELIM)); }); return Buffer.from(s.join("\n") + "\n", "utf8"); // the file is written in UTF-8 } if (import.meta.url === `file://${process.argv[1]}`) { const k = records(), b = exportFile(), lines = b.toString("utf8").trimEnd().split("\n"); const count = (f) => k.reduce((s, _, i) => s + (f(i) ? 1 : 0), 0); console.log(`seed ${SEED}; logical records ${k.length}, distinct subscribers ` + `${new Set(k.map((x) => x.subscriber)).size}, distinct subscriber-periods ` + `${new Set(k.map((x) => x.subscriber + x.period)).size}`); console.log(`file ${b.length} bytes, ${lines.length} physical lines (1 header + ` + `${lines.length - 1} data lines)`); console.log(`records with a delimiter in the address ${count(HAS_DELIM)} (${count(ENDS_NUMBER)} of them ` + `end in a number after the delimiter), records with a line break in the address ${count(HAS_NEWLINE)}`); console.log(`correctly read total ${k.reduce((s, x) => s + x.reading, 0).toFixed(1)} m3`); const hex = (name) => Buffer.from(name, "utf8").toString("hex").match(/../g).join(" "); for (const name of ["kuzey", "güney"]) console.log(`region "${name}" UTF-8 bytes: ${hex(name)} (${Buffer.byteLength(name)} bytes, ` + `${name.length} characters)`); console.log(lines[0]); console.log(lines[1]); console.log(lines[14]); } ``` ``` seed 1246; logical records 2037, distinct subscribers 1400, distinct subscriber-periods 1877 file 108230 bytes, 2077 physical lines (1 header + 2076 data lines) records with a delimiter in the address 97 (33 of them end in a number after the delimiter), records with a line break in the address 39 correctly read total 42645.6 m3 region "kuzey" UTF-8 bytes: 6b 75 7a 65 79 (5 bytes, 5 characters) region "güney" UTF-8 bytes: 67 c3 bc 6e 65 79 (6 bytes, 5 characters) subscriber_no;meter_no;period;address;reading_m3;region 10000;S-4000;2024-01;"Pine Road No 12; 45";9.4;kuzey 10013;S-4013;2024-01;"Creek St No 25 ``` The first contradiction is in the last three lines. The file has **2,076 data lines** but **2,037 logical records**; the difference of 39 comes from the 39 records that carry a line break in their address. How many lines a record occupies is not fixed; measuring the file by counting lines gives a wrong number. The second is on the first data line. The address field reads `"Pine Road No 12; 45"`: the delimiter itself appears inside the field. The delimiter is a character chosen to separate fields, and it can occur in the data too; that is exactly why quotes exist. A parser that does not read quotes sees seven fields here instead of six and reads the tail of the address as the reading field. The third is in the byte lines. `kuzey` is five characters and five bytes, `güney` is five characters but six bytes. The encoding a file was written in is not written inside the file; the reading side makes a decision, and if the decision is wrong no error occurs, only the letters change. #### Rows In, Rows Out, Silently Dropped ```js // read.mjs — the same file is read with two parsing decisions. K1 splits the text // at line breaks and the lines at the delimiter, strips the quotes, and decodes the // file with a single-byte encoding. K2 preserves what is inside quotes and decodes // the file with the encoding it was written in. Neither raises an error; the // difference shows up only in the counts. import { exportFile, HEADER, REGION, DELIM, QUOTE, records } from "./source.mjs"; const b = exportFile(), RD = HEADER.indexOf("reading_m3"), RG = HEADER.indexOf("region"); const extract = (rows) => { const out = [], dropped = []; for (const fields of rows) { const reading = Number(fields[RD]); if (fields[RD] !== "" && fields[RD] !== undefined && Number.isFinite(reading)) out.push({ reading, region: fields[RG] }); else dropped.push(fields.length); } return { rowsIn: rows.length, out, dropped }; }; // K1: naive reader. It knows nothing about quotes; it strips the quotes field by field. const k1 = (bytes) => extract(bytes.toString("latin1").trimEnd().split("\n").slice(1) .map((s) => s.split(DELIM).map((a) => a.replaceAll(QUOTE, "")))); // K2: a state machine that preserves what is inside quotes. Inside quotes both the // delimiter and the line break are part of the field; two quotes side by side mean // a single quote. function k2(bytes) { const m = bytes.toString("utf8"), record = []; let field = [""], inside = false; for (let i = 0; i < m.length; i++) { const c = m[i]; if (inside) { if (c === QUOTE && m[i + 1] === QUOTE) { field[field.length - 1] += c; i++; } else if (c === QUOTE) inside = false; else field[field.length - 1] += c; } else if (c === QUOTE) inside = true; else if (c === DELIM) field.push(""); else if (c === "\n") { record.push(field); field = [""]; } else field[field.length - 1] += c; } if (field.length > 1 || field[0] !== "") record.push(field); return extract(record.slice(1)); } // The threshold for counting two totals as the same is 0.05 m3: readings are // reported with one decimal, and a difference below that can only come from rounding. const THRESHOLD = 0.05, MAX_READING = 37.0; // the largest reading the generator can produce const target = records().reduce((s, x) => s + x.reading, 0); const fmt = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j]) : String(v).padEnd(w[0]))).join(""); const fmt1 = fmt([22, 8, 9, 8, 12, 11, 10]); console.log(fmt1(["parsing decision", "rows in", "rows out", "dropped", "total m3", "mismatched", "house no"])); const result = {}; for (const [name, f] of [["K1 naive split", k1], ["K2 quote-preserving", k2]]) { const r = result[name] = f(b), t = r.out.reduce((s, x) => s + x.reading, 0); console.log(fmt1([name, r.rowsIn, r.out.length, r.dropped.length, t.toFixed(1), r.out.filter((x) => !REGION.includes(x.region)).length, r.out.filter((x) => x.reading > MAX_READING).length])); } const r1 = result["K1 naive split"], t1 = r1.out.reduce((s, x) => s + x.reading, 0); const half = r1.dropped.filter((n) => n < 6).length, shifted = r1.dropped.filter((n) => n > 6).length; const houseNo = r1.out.filter((x) => x.reading > MAX_READING); console.log(`\nthe file has ${r1.rowsIn} data lines but ${records().length} logical records: ` + `the 39 records that carry a line break in the address produced ${r1.rowsIn - 2037} extra lines`); console.log(`K1 dropped ${r1.dropped.length} lines without any warning: ${half} split ` + `half-lines, ${shifted} lines with a shifted field`); console.log(`of the ${r1.out.length} records K1 extracted, ${houseNo.length} survived ` + `but carry a house number shifted out of the address in the reading field (like ${houseNo[0].reading} m3)`); console.log(`the two totals diverge by ${Math.abs(target - t1).toFixed(1)} m3, ` + `${Math.round(Math.abs(target - t1) / THRESHOLD)} times the ${THRESHOLD} m3 threshold; ratio ` + `${(100 * Math.abs(target - t1) / target).toFixed(1)}%`); console.log(`the region name does not match the list in ` + `${r1.out.filter((x) => !REGION.includes(x.region)).length} of the records from K1; ` + `the region breakdown silently leaves these records out`); ``` ``` parsing decision rows in rows out dropped total m3 mismatched house no K1 naive split 2076 1934 142 41661.4 1157 33 K2 quote-preserving 2037 2037 0 42645.6 0 0 the file has 2076 data lines but 2037 logical records: the 39 records that carry a line break in the address produced 39 extra lines K1 dropped 142 lines without any warning: 78 split half-lines, 64 lines with a shifted field of the 1934 records K1 extracted, 33 survived but carry a house number shifted out of the address in the reading field (like 45 m3) the two totals diverge by 984.2 m3, 19684 times the 0.05 m3 threshold; ratio 2.3% the region name does not match the list in 1157 of the records from K1; the region breakdown silently leaves these records out ``` The two rows of the table read the same file and give two different sets. The quote-preserving read extracts all 2,037 rows, drops none, and finds a total of 42,645.6 m³. The naive split extracts 1,934 records from 2,076 lines: **142 lines disappeared and none of them raised an error**. Both finished with an exit code of zero. The breakdown of the loss has two items. **78 lines are split halves**: the 39 records that carry a line break in their address were spread over two lines in the file, the naive reader took both for separate lines, the reading field was missing in both, and it threw both away. One record spent two lines to disappear. **64 lines are shifted-field lines**: in 64 of the 97 records that carry a delimiter in their address, the tail of the address landed in the reading field, did not convert to a number, and the line dropped. In terms of logical records the account closes: 103 of the 2,037 records left the set entirely. The remaining 33 records are the lesson's real finding. In one third of the records that carry a delimiter in their address, a bare house number followed the delimiter; the shifted field converted to a number and the record **survived**. The reading field now holds a value like 45 m³, and according to DC5 this value cannot be a reading. These 33 records appear missing from no count; they only inflate the total. The 103 dropped records reduce the total, the 33 inflated records raise it; the net difference comes to 984.2 m³, 19,684 times the threshold and 2.3% of the total. The last row measures a fourth channel. The file was written in UTF-8, the naive reader decoded it with a single-byte encoding, and region names that carry multi-byte letters turned into other strings. In **1,157** of the 1,934 records the region name does not match the list; a summary that breaks down by region silently leaves these records out, and the sum of the breakdown does not match the grand total. The encoding decision dropped not a single row; it only made more than half of the records unclassifiable. #### Delimiter, Document, Column ```js // format.mjs — the same 2037 records are stored in three structures. THIS IS A // MODEL: the names of the formats do not matter, their structures do. Columnar // model: reading as a 2-byte integer (deci-m3), subscriber as a 4-byte integer, // period and region as one-byte codes, text length-prefixed. import { exportFile, records, REGION } from "./source.mjs"; const k = records(), doc = Buffer.from(JSON.stringify(k), "utf8"); const text = (a) => Buffer.concat(a.map((s) => Buffer.from(s, "utf8")) .flatMap((x) => [Buffer.from([x.length]), x])); const COLUMN = { subscriber: Buffer.from(new Int32Array(k.map((x) => x.subscriber)).buffer), reading: Buffer.from(new Uint16Array(k.map((x) => Math.round(x.reading * 10))).buffer), period: Buffer.from(k.map((x) => (x.period === "2024-01" ? 0 : 1))), region: Buffer.from(k.map((x) => REGION.indexOf(x.region))), meter: text(k.map((x) => x.meter)), address: text(k.map((x) => x.address)), }; const total = () => { let t = 0; for (let i = 0; i < k.length; i++) t += COLUMN.reading.readUInt16LE(i * 2) / 10; return t.toFixed(1); }; const fmt1 = (h) => h.map((v, j) => (j ? String(v).padStart([0, 13, 15, 12][j]) : String(v).padEnd(22))).join(""); console.log(fmt1(["structure", "total bytes", "bytes needed", "total m3"])); for (const [name, whole, question] of [["delimited text", exportFile().length, exportFile().length], ["nested document", doc.length, doc.length], ["columnar", Object.values(COLUMN).reduce((s, x) => s + x.length, 0), COLUMN.reading.length]]) console.log(fmt1([name, whole, question, total()])); console.log(`learning the total reading takes ${COLUMN.reading.length} bytes in the columnar structure ` + `and reading ${exportFile().length} bytes in delimited text: ${(exportFile().length / COLUMN.reading.length) .toFixed(0)} times`); ``` ``` structure total bytes bytes needed total m3 delimited text 108230 108230 42645.6 nested document 243328 243328 42645.6 columnar 68040 4074 42645.6 learning the total reading takes 4074 bytes in the columnar structure and reading 108230 bytes in delimited text: 27 times ``` The three structures carry the same 2,037 records and give the same total. The difference is where the boundary of a record is written. In delimited text the boundary is a character, and that character can occur in the data too; the loss of 142 lines is the price of that. In the nested document structure the file grows to more than twice the size because the field name is repeated in every record, but where a record ends can be read from the structure of the text. In the columnar structure every column is a separate byte array and its type is declared; a text shift into the reading field is structurally impossible. The last line measures the second face of the choice. The total-reading question asks for 4,074 bytes in the columnar structure and 108,230 bytes in delimited text — **27 times**. The columnar structure does this cheaply because it never opens the address column; on the other hand, seeing all the fields of a single record means going to six separate arrays. Choosing a format is not a matter of convenience but a decision about which job gets done cheaply. #### Summary - The file has 2,076 data lines and the set has 2,037 logical records; counting lines is not counting records. - The quote-preserving read extracts all 2,037 records and finds a total of 42,645.6 m³; the naive split extracts 1,934 records and drops 142 lines without any warning. - The breakdown of what dropped is 78 split half-lines and 64 shifted-field lines; of the 2,037 records, 103 left the set, and 33 of the shifted fields converted to numbers and survived in the reading field as house numbers. The net difference is 984.2 m³, 19,684 times the threshold. - The wrong encoding drops not a single line, but it makes the region name of 1,157 records fail to match the list. - The same set takes 68,040 bytes in the columnar structure and the total reading question is answered by reading 4,074 bytes; in delimited text the same question asks for 108,230 bytes. #### Next Step This lesson took the data from a file and counted its rows. But most data sits in a database, and on its way out it brings a question the file did not carry: a query returns rows, but what **a record of what** that row is depends on how the query is written. The next lesson measures whether the row a query returns matches the unit the analysis asks about, and counts how joining two tables multiplies the row count. ### Pulling Data from Databases Source: https://academia.sh/en/courses/data-collection-and-preparation/pulling-data-from-databases Three query forms against the same store return three different row sets, and none of them raises an error: joining reading with subscriber gives 1,877 rows; when the visit table is added with an inner join, 766 rows silently drop and 669 rows multiply — about 76% of the input is corrupted, yet the net row loss is only 97 and the total deviates by just 7.0%, so someone looking at the numbers cannot see the damage. The left join drops no rows but raises the set to 2,546 rows and inflates the total 1.34-fold. The same question gives three numbers under three decisions: 20.61 m³ per reading row, 27.64 m³ per subscriber, 20.21 m³ in the set spread over visit rows. The previous lesson took data from a file and showed that a parsing decision silently dropped 142 rows. The file had one convenience: a row was a record, at least when the quotes were read correctly. A database does not give that convenience. A query returns rows, and what **a record of what** that row is comes not from the structure of the tables but from how the query is written. This is the lesson's measure: whether the unit the analysis asks about and the unit of the row the query returns are the same. The relational model, keys, and writing queries are prerequisites and are not repeated here; the only things measured are **the unit of the row** and where the row count goes when that unit shifts. - **DC6.** The store is fiction and is modeled with `node` arrays. The join is done with a small helper; query syntax and optimization are not the subject of this course. **It is a model.** - **DC7.** There are three tables: the 1,400-row subscriber table, the 1,877-row reading table that holds one row per subscriber-period, and the 1,328-row field visit table that holds between zero and three rows per subscriber. - **DC8.** The unit of analysis is written down explicitly: the question asked is mean consumption per subscriber, that is, a row of the report is a subscriber. - **DC9.** The threshold for counting two means as the same is 0.05 m³, because readings are kept with one decimal and a difference below that can only come from rounding. #### The Store's Three Tables ```js // store.mjs — a fictional relational store of the regional measurement network. THIS // IS A MODEL: the tables are node arrays, the join is done with a small helper. Query // syntax is not the subject of this course; what is measured is which unit's record // the returned row is. export const SEED = 4711; export const REGION = ["north", "east", "central", "south", "west"]; const rng = (t) => { let s = t >>> 0; return () => { s = (Math.imul(s, 1103515245) + 12345) >>> 0; return s / 4294967296; }; }; const r = rng(SEED); // subscriber: the unit of analysis itself. Each subscriber appears once. export const SUBSCRIBER = Array.from({ length: 1400 }, (_, i) => ({ subscriber_no: 10000 + i, region: REGION[i % 5], type: i % 7 === 0 ? "commercial" : "residential" })); // reading: one row per subscriber-period pair. The first period of 1400 subscribers, the second period of 477 subscribers. export const READING = []; for (let i = 0; i < 1400; i++) READING.push({ subscriber_no: 10000 + i, period: "2024-01", reading_m3: 5 + Math.floor(r() * 321) / 10 }); for (let i = 0; i < 477; i++) READING.push({ subscriber_no: 10000 + i, period: "2024-02", reading_m3: 5 + Math.floor(r() * 321) / 10 }); // visit: field visits. A subscriber may have zero, one, two, or three visits. export const VISIT = []; for (const a of SUBSCRIBER) { const p = r(), n = p < 0.41 ? 0 : p < 0.71 ? 1 : p < 0.91 ? 2 : 3; for (let j = 0; j < n; j++) VISIT.push({ subscriber_no: a.subscriber_no, seq: j + 1, type: j === 0 ? "reading" : "inspection" }); } // Inner join: each row of the left table appears once for every row of the right table // whose key matches. If there is no match, the left row does not appear at all. export const innerJoin = (left, right, key) => { const index = new Map(); for (const s of right) { const k = s[key]; if (!index.has(k)) index.set(k, []); index.get(k).push(s); } return left.flatMap((x) => (index.get(x[key]) ?? []).map((y) => ({ ...x, ...y }))); }; // Left join: if there is no match, the left row appears once, with its right fields empty. export const leftJoin = (left, right, key) => { const index = new Map(); for (const s of right) { const k = s[key]; if (!index.has(k)) index.set(k, []); index.get(k).push(s); } return left.flatMap((x) => { const e = index.get(x[key]); return e ? e.map((y) => ({ ...x, ...y })) : [{ ...x }]; }); }; if (import.meta.url === `file://${process.argv[1]}`) { const distribution = new Map(); for (const a of SUBSCRIBER) { const n = VISIT.filter((z) => z.subscriber_no === a.subscriber_no).length; distribution.set(n, (distribution.get(n) ?? 0) + 1); } console.log(`seed ${SEED}; subscriber ${SUBSCRIBER.length} rows, reading ${READING.length} rows, ` + `visit ${VISIT.length} rows`); console.log(`distinct subscribers in the reading table ${new Set(READING.map((x) => x.subscriber_no)).size}, ` + `distinct subscriber-periods ${new Set(READING.map((x) => x.subscriber_no + x.period)).size}`); console.log("distribution of visit counts per subscriber:"); for (const n of [...distribution.keys()].sort()) console.log(` visits = ${n}: ${distribution.get(n)} subscribers`); console.log(`total of readings ${READING.reduce((s, x) => s + x.reading_m3, 0).toFixed(1)} m3`); } ``` ``` seed 4711; subscriber 1400 rows, reading 1877 rows, visit 1328 rows distinct subscribers in the reading table 1400, distinct subscriber-periods 1877 distribution of visit counts per subscriber: visits = 0: 570 subscribers visits = 1: 453 subscribers visits = 2: 256 subscribers visits = 3: 121 subscribers total of readings 38690.7 m3 ``` The three tables are kept in three different units. A row of the subscriber table is one subscriber, and the subscriber number is unique there. A row of the reading table is one subscriber-period pair; because the second-period readings of 477 of the 1,400 subscribers were also entered, the table has 1,877 rows and the subscriber number is **not unique** there. A row of the visit table is one field visit, and a subscriber may not appear in this table at all or may appear three times. The distribution table shows the real risk. **570 of the 1,400 subscribers have no visit at all**, and 121 have three visits. A join erases the first of these two extremes and triples the second. Both effects show up in the number of rows the query returns, and neither produces an error message. #### When the Row's Unit Shifts ```js // query.mjs — three query forms return three different row sets from the same store. // What is measured is not whether the row is correct but WHICH UNIT'S record the row // is: if the analysis asks for subscriber-period, every subscriber-period pair in // the result set must appear exactly once. import { SUBSCRIBER, READING, VISIT, innerJoin, leftJoin } from "./store.mjs"; const S1 = innerJoin(READING, SUBSCRIBER, "subscriber_no"); // reading + subscriber const S2 = innerJoin(S1, VISIT, "subscriber_no"); // + visit, inner join const S3 = leftJoin(S1, VISIT, "subscriber_no"); // + visit, left join // How many times the unit of analysis (subscriber-period) appears in a result set. const unit = (set) => { const count = new Map(); for (const s of set) { const k = s.subscriber_no + s.period; count.set(k, (count.get(k) ?? 0) + 1); } return { max: Math.max(...count.values()), dropped: READING.length - count.size, multiplied: set.length - count.size }; }; const total = (set) => set.reduce((t, s) => t + s.reading_m3, 0); const fmt = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j]) : String(v).padEnd(w[0]))).join(""); const fmt1 = fmt([24, 8, 9, 8, 11, 8, 12, 8]); console.log(fmt1(["query", "rows in", "rows out", "dropped", "multiplied", "max", "total m3", "ratio"])); for (const [name, k] of [["S1 reading + subscriber", S1], ["S2 + visit (inner)", S2], ["S3 + visit (left)", S3]]) { const b = unit(k); console.log(fmt1([name, READING.length, k.length, b.dropped, b.multiplied, b.max, total(k).toFixed(1), (total(k) / total(S1)).toFixed(2)])); } const visited = new Set(VISIT.map((z) => z.subscriber_no)); const zero = SUBSCRIBER.filter((a) => !visited.has(a.subscriber_no)); console.log(`\n${zero.length} subscribers with no visits: ` + `${READING.filter((o) => !visited.has(o.subscriber_no)).length} of their reading rows never appear in S2; ` + `the query raised no error, the row count is the only sign`); const b2 = unit(S2), affected = b2.dropped + b2.multiplied; console.log(`in S2 ${b2.dropped} rows dropped and ${b2.multiplied} rows multiplied: ${affected} rows, ` + `so ${(100 * affected / READING.length).toFixed(0)}% of the input was affected. But because the two ` + `errors ` + `worked in opposite directions, the row count went from 1877 to 1780, only ${READING.length - S2.length} ` + `rows dropped, and the total deviated by ${(100 * (1 - total(S2) / total(S1))).toFixed(1)}%`); // The same question, three decisions: "mean periodic consumption per subscriber". const subscriberTotal = new Map(); for (const s of S1) subscriberTotal.set(s.subscriber_no, (subscriberTotal.get(s.subscriber_no) ?? 0) + s.reading_m3); const meanA = total(S1) / S1.length; const meanB = [...subscriberTotal.values()].reduce((a, b) => a + b, 0) / subscriberTotal.size; const meanC = total(S2) / S2.length; const fmt2 = fmt([34, 18, 10]); console.log(`\nthree decisions for the question "mean consumption per subscriber"`); console.log(fmt2(["decision", "row unit", "m3"])); console.log(fmt2(["A mean per reading row", "subscriber-period", meanA.toFixed(2)])); console.log(fmt2(["B sum per subscriber first", "subscriber", meanB.toFixed(2)])); console.log(fmt2(["C mean per S2 row", "subscriber-visit", meanC.toFixed(2)])); // The threshold for counting two means as the same is 0.05 m3: readings are kept // with one decimal, and a difference below that can only come from rounding. const THRESHOLD = 0.05; console.log(`the difference between A and B is ${(meanB - meanA).toFixed(2)} m3, ` + `${Math.round((meanB - meanA) / THRESHOLD)} times the threshold of ${THRESHOLD}; both are correct ` + `calculations but they do not answer the same question`); console.log(`C is a mean that weights the readings of subscribers with many visits, and since it ` + `differs from A by only ${Math.abs(meanC - meanA).toFixed(2)} m3, the difference goes unnoticed`); ``` ``` query rows in rows out dropped multiplied max total m3 ratio S1 reading + subscriber 1877 1877 0 0 1 38690.7 1.00 S2 + visit (inner) 1877 1780 766 669 3 35967.4 0.93 S3 + visit (left) 1877 2546 0 669 3 51971.8 1.34 570 subscribers with no visits: 766 of their reading rows never appear in S2; the query raised no error, the row count is the only sign in S2 766 rows dropped and 669 rows multiplied: 1435 rows, so 76% of the input was affected. But because the two errors worked in opposite directions, the row count went from 1877 to 1780, only 97 rows dropped, and the total deviated by 7.0% three decisions for the question "mean consumption per subscriber" decision row unit m3 A mean per reading row subscriber-period 20.61 B sum per subscriber first subscriber 27.64 C mean per S2 row subscriber-visit 20.21 the difference between A and B is 7.02 m3, 140 times the threshold of 0.05; both are correct calculations but they do not answer the same question C is a mean that weights the readings of subscribers with many visits, and since it differs from A by only 0.41 m3, the difference goes unnoticed ``` S1 is the correct set. When the reading table is joined with the subscriber table, each reading row appears exactly once because the subscriber number is unique on the right side: rows in 1,877, rows out 1,877, dropped zero, multiplied zero. This is the only condition under which a join leaves the row count unchanged — the key being unique on the other side. S2 adds the visit table to the same set, and two things happen at once. The **766 reading rows** of the 570 subscribers with no visit do not appear in the result at all, because an inner join drops unmatched left rows. At the same time, the readings of subscribers with more than one visit multiply: **669 extra rows**, and a subscriber-period pair appears at most three times in the result set. The unit is no longer subscriber-period; it is the subscriber-visit pair. The lesson's real finding is that two numbers hide each other. 1,435 of the 1,877 rows, that is, **about 76% of the input**, either dropped or multiplied. But because the two errors worked in opposite directions, the result set came out at 1,780 rows: a net loss of **97 rows**, a deviation in the total of **7.0%**. Someone looking at the row count or the total cannot see that three quarters of the set is corrupted. The account opens up only when dropped and multiplied are counted separately. S3 uses a left join instead of an inner join and solves the dropped-row problem: subscribers with no visit stay in the set, dropped is zero. The multiplication remains — the set rises to 2,546 rows and the total rises to 1.34 times. A left join prevents loss, it does not prevent multiplication; they are separate decisions and are counted separately. The `unit` function in the code is the test that produces these three rows, and it can be run after every query. The test asks a single question: how many times does the key of the unit of analysis appear in the result set. If the count per key is one on every row, the unit is preserved. If the count is greater than one, there is multiplication, and that key's values carry weight in the total more than once. If a key that was in the input never appears in the result, there is silent loss. These three come out in a single scan and differ from reading the row count: the number 1,780 says nothing about S2 on its own, but when `dropped` and `multiplied` are written side by side, exactly what the set is becomes known. The course's rule applies here too — if the difference between rows in and rows out cannot be written out item by item, the data has not been prepared, only changed. #### Same Question, Three Units The lower table measures the unit-of-analysis question on its own. The question is fixed: mean consumption per subscriber. According to DC8, a row of the report is a subscriber, so the correct answer is B — **27.64 m³**. Decision A takes the mean over reading rows and gives **20.61 m³**; this number is not miscalculated, it is only the answer to another question (mean reading per period). Subscribers with two readings are counted twice in A and once in B, and the difference is 7.02 m³, that is, 140 times the threshold. Decision C is the most dangerous. The mean taken over the rows of S2 gives **20.21 m³** and differs from A by only 0.41 m³. The number looks plausible because the values themselves are not corrupted; the only thing corrupted is how many times each reading is counted. The readings of subscribers with many visits carry weight three times, those of subscribers with no visits carry zero. This mean is the answer to no question, and its closeness to A makes it hard to find. The shared lesson of these three numbers is this: a query that does not state which unit's record it is looking at after the join has not said what it returned, even if it says how many rows it returned. #### Summary - The unit of the row a query returns comes not from the structure of the tables but from how the query is written; joining reading with subscriber gives 1,877 rows and preserves the unit as subscriber-period. - Adding the visit table with an inner join silently drops 766 rows and multiplies 669 rows; the unit shifts to the subscriber-visit pair. - Because the two errors work in opposite directions, the net row loss is only 97 and the deviation in the total is 7.0%; even though about 76% of the input is corrupted, someone looking at the numbers cannot see the damage. - The left join brings dropped rows to zero but does not stop multiplication: the set rises to 2,546 rows and the total rises to 1.34 times. - The same question gives three numbers under three units — 27.64 m³ per subscriber, 20.61 m³ per reading row, 20.21 m³ in the set spread over visits; the last goes unnoticed because it is close to the right one. #### Next Step Both the file and the database gave the data all at once: when the file was read, how many rows it had was known, and when the query ran, the set was in hand. When collecting data from an interface, this assurance is missing. Records arrive piece by piece, the calling side keeps track of how each piece connects to the next, and the source puts a limit on call frequency. The next lesson counts how many records consuming pagination wrongly skips and measures how a queue that obeys the rate limit determines the collection time. ### Collecting Data from Interfaces Source: https://academia.sh/en/courses/data-collection-and-preparation/collecting-data-from-interfaces Three collection decisions against the same interface produce three different sets, and all three responses come back ok: a narrow-scope token collects 1,400 records and leaves out the 477 rows of the past period without ever requesting them, offset-based pagination collects exactly 1,877 rows but never receives 35 records and receives 35 records twice, and cursor-based pagination collects all 1,877 records once. The overall total of the offset-based decision matches exactly at 38,690.7 m³ because the skipped and duplicated records carry the same reading value; the damage shows up only in the region breakdown, as a 70.5 m³ deviation. Under a rate limit, the queue that counts the limit response as an empty page stops at 600 records and loses 1,277 records; the queue that obeys the limit collects the same set completely with 38 requests and 3 wait units, and when the page size is raised to 200 the wait drops to zero. Neither the file nor the database gave the data in a single piece. When the file was read, how many rows it had was known; when the query ran, the set was in hand. When collecting data from an interface, this assurance is missing: records arrive piece by piece, the calling side keeps track of how each piece connects to the next, the source puts a limit on call frequency, and the token determines which records can be seen. The lesson's measure is this: how many of the rows in the source entered the collected set. Interface design, pagination, and rate limiting are prerequisites from the point of view of the side that **provides** them and are not repeated here; the only things measured are how many records the consuming side received, how many it received twice, and how many it never asked for. Before the technical limit there is one more limit: collecting data from a source without the source's terms of use and an explicit access permission is a legal and professional problem even if it is technically possible; here a permitted collection is assumed. - **DC10.** The interface is fiction and is modeled with `node`. No network call is made: a request is a function call, a response is an object. **It is a model.** - **DC11.** The set in the source is the same as the reading table in the previous lesson's store — the same seed, 1,877 rows, 38,690.7 m³. - **DC12.** The interface offers two orderings. The `reading` ordering is descending by reading value, and the internal order of equal values is rebuilt on every request; the `id` ordering is ascending by subscriber-period key and unique. The page size is at most 200, and anything beyond that is silently truncated. - **DC13.** The rate limit quota is 12 requests. When the quota is exceeded, the response carries no records and its status is `limit`. Time is not measured with a real clock; the source's window count is counted as a **wait unit**. - **DC14.** The threshold for counting two totals as the same is 0.05 m³, because readings are reported with one decimal and a difference below that can only come from the order of summation. #### The Page the Interface Gives ```js // api.mjs — the fictional reading interface of the regional measurement network. IT IS A // MODEL: no network call is made, a "request" is a function call and a "response" is an // object. The records are the same as the 1877 reading rows in the previous lesson's // store: the same seed, the same numbers. export const SEED = 4711, PAGE_SIZE = 50, MAX_SIZE = 200, QUOTA = 12; const rng = (t) => { let s = t >>> 0; return () => { s = (Math.imul(s, 1103515245) + 12345) >>> 0; return s / 4294967296; }; }; // The reading is kept as an integer in deci-m3; since the sort key is an integer, // floating-point values never enter an equality comparison. const r = rng(SEED); export const RECORDS = []; for (let i = 0; i < 1400; i++) RECORDS.push({ subscriber_no: 10000 + i, period: "2024-01", deci: 50 + Math.floor(r() * 321) }); for (let i = 0; i < 477; i++) RECORDS.push({ subscriber_no: 10000 + i, period: "2024-02", deci: 50 + Math.floor(r() * 321) }); export const REGION = ["north", "east", "central", "south", "west"]; export const recordId = (k) => `${k.subscriber_no}-${k.period}`; export const m3 = (k) => k.deci / 10; export const regionOf = (k) => REGION[(k.subscriber_no - 10000) % 5]; // "reading": descending by reading, the internal order of equal values is rebuilt on // every request. "id": ascending by subscriber-period key and unique. function sorted(order, requestNo) { if (order === "id") return [...RECORDS].sort((a, b) => (recordId(a) < recordId(b) ? -1 : 1)); const g = rng(SEED + requestNo * 7919); for (let i = 0; i < 5; i++) g(); return RECORDS.map((k) => ({ k, p: g() })).sort((a, b) => b.k.deci - a.k.deci || a.p - b.p) .map((x) => x.k); } // The quota is the number of requests accepted in one window; when it is exceeded, the // response carries no records. If the scope does not include "history", the source // returns only the open period. export function server({ quota = QUOTA } = {}) { let requests = 0, inWindow = 0, waits = 0; return { counts: () => ({ requests, waits }), wait() { inWindow = 0; waits++; }, request({ scope = ["reading"], order = "id", offset = 0, cursor = null, size = PAGE_SIZE }) { requests++; inWindow++; if (inWindow > quota) return { status: "limit", records: [], next: cursor }; const b = Math.min(size, MAX_SIZE); let set = sorted(order, requests); if (!scope.includes("history")) set = set.filter((k) => k.period === "2024-01"); const start = cursor === null ? offset : set.findIndex((k) => recordId(k) > cursor); const slice = start < 0 ? [] : set.slice(start, start + b); return { status: "ok", total: set.length, records: slice.map((k) => ({ subscriber_no: k.subscriber_no, period: k.period, reading_m3: m3(k), region: regionOf(k) })), next: slice.length < b ? null : recordId(slice[slice.length - 1]) }; }, }; } if (import.meta.url === `file://${process.argv[1]}`) { const s = server({ quota: Infinity }), FULL = ["reading", "history"]; const sum = (y) => `status ${y.status}, total ${y.total}, ${y.records.length} ` + `records on the page, next cursor ${y.next}`; console.log(`seed ${SEED}; ${RECORDS.length} reading rows, ` + `${RECORDS.reduce((t, k) => t + m3(k), 0).toFixed(1)} m3; size ${PAGE_SIZE}, upper limit ` + `${MAX_SIZE}, quota ${QUOTA}`); console.log(`full scope: ${sum(s.request({ scope: FULL }))}`); console.log(`narrow scope: ${sum(s.request({ scope: ["reading"] }))}`); const u = () => s.request({ scope: FULL, order: "reading", offset: 49, size: 3 }) .records.map((x) => `${x.subscriber_no}/${x.reading_m3}`).join(" "); console.log(`three records from the 50th record in the "reading" order, in two separate requests:\n ${u()}\n ${u()}`); } ``` ``` seed 4711; 1877 reading rows, 38690.7 m3; size 50, upper limit 200, quota 12 full scope: status ok, total 1877, 50 records on the page, next cursor 10024-2024-02 narrow scope: status ok, total 1400, 50 records on the page, next cursor 10049-2024-01 three records from the 50th record in the "reading" order, in two separate requests: 11319/36 10452/36 10828/36 11319/36 10018/36 10230/36 ``` The difference between the two tokens is on the third line. The narrow-scope token returns the `ok` status, the page comes back full, the cursor advances, and there is no authorization warning anywhere. The only sign is that the `total` field says 1,400 instead of 1,877, and whether this number is correct cannot be understood from inside the response. Scope is not a constraint that produces an error; it shrinks the set the source gives and presents the rest as if it never existed. The last two lines show the second problem: three records were requested twice from the same offset, and the records that came back are different. The sort key is the reading value, and that value is not unique — the 1,877 records are spread over at most 321 distinct reading values, that is, each value is shared by about six records. The order of equal values among themselves is left to the source's decision. The only thing that looks fixed is the position in the list, but which record stands at that position is not fixed. #### Same Interface, Three Collection Decisions ```js // collect.mjs — three collection decisions against the same interface produce three // different sets. No decision gets an error; all responses return "ok". What is // measured is not the number of records returned but how many of the 1877 rows in the // source entered the collected set. import { RECORDS, recordId, m3, regionOf, REGION, server, PAGE_SIZE } from "./api.mjs"; const FULL = ["reading", "history"]; // Cursor-based pagination: each page asks for what comes after the last key, so the // boundary does not shift. T1 does this with a narrow token, T3 with a full-scope token. function withCursor(scope) { const s = server({ quota: Infinity }), collected = []; let cursor = null; do { const y = s.request({ scope, order: "id", cursor, size: PAGE_SIZE }); collected.push(...y.records); cursor = y.next; } while (cursor !== null); return { collected, requests: s.counts().requests }; } // T2. Offset-based pagination, in the "reading" order: since the key is not unique, // the page boundary falls on a different record on every request. function withOffset() { const s = server({ quota: Infinity }), collected = []; let offset = 0, total = Infinity; while (offset < total) { const y = s.request({ scope: FULL, order: "reading", offset, size: PAGE_SIZE }); total = y.total; collected.push(...y.records); offset += PAGE_SIZE; } return { collected, requests: s.counts().requests }; } // Threshold 0.05 m3: readings are kept with one decimal, and a difference below that // can only come from the order of summation. const THRESHOLD = 0.05, TARGET = RECORDS.reduce((t, k) => t + m3(k), 0); const REGION_TARGET = new Map(REGION.map((b) => [b, RECORDS.filter((k) => regionOf(k) === b).reduce((t, k) => t + m3(k), 0)])); const dev = (set) => { // the largest deviation of the region breakdown const t = new Map(REGION.map((b) => [b, 0])); for (const x of set) t.set(x.region, t.get(x.region) + x.reading_m3); return Math.max(...REGION.map((b) => Math.abs(t.get(b) - REGION_TARGET.get(b)))); }; const y1 = (h) => h.map((v, j) => (j ? String(v).padStart([0, 9, 10, 9, 9, 9, 12, 11, 12][j]) : String(v).padEnd(20))).join(""); console.log(y1(["collection decision", "rows in", "requests", "rows out", "distinct", "skipped", "duplicated", "total m3", "max dev m3"])); const result = {}; for (const [name, f] of [["T1 narrow scope", () => withCursor(["reading"])], ["T2 offset-based", withOffset], ["T3 cursor-based", () => withCursor(FULL)]]) { const r = result[name] = f(), distinct = new Set(r.collected.map(recordId)).size; r.distinct = distinct; r.total = r.collected.reduce((t, x) => t + x.reading_m3, 0); console.log(y1([name, RECORDS.length, r.requests, r.collected.length, distinct, RECORDS.length - distinct, r.collected.length - distinct, r.total.toFixed(1), dev(r.collected).toFixed(1)])); } const t2 = result["T2 offset-based"]; console.log(`\nT2 matched the ${t2.collected.length} rows the source reported, but ` + `${RECORDS.length - t2.distinct} records never arrived and ${t2.collected.length - t2.distinct} records ` + `arrived twice; because the skipped and duplicated records carried the same reading value, the overall total ` + `differs from the target by ${Math.abs(t2.total - TARGET).toFixed(3)} m3 (threshold ${THRESHOLD}), while in the region ` + `breakdown it deviates by ${Math.round(dev(t2.collected) / THRESHOLD)} times the threshold`); ``` ``` collection decision rows in requests rows out distinct skipped duplicated total m3 max dev m3 T1 narrow scope 1877 29 1400 1400 477 0 28790.8 2016.2 T2 offset-based 1877 38 1877 1842 35 35 38690.7 70.5 T3 cursor-based 1877 38 1877 1877 0 0 38690.7 0.0 T2 matched the 1877 rows the source reported, but 35 records never arrived and 35 records arrived twice; because the skipped and duplicated records carried the same reading value, the overall total differs from the target by 0.000 m3 (threshold 0.05), while in the region breakdown it deviates by 1410 times the threshold ``` The last column is the largest deviation in the region breakdown. T1 consumes the pagination correctly and still misses a quarter of the set. All twenty-nine requests came back successful, the cursor advanced properly, and the loop stopped in the right place. The loss comes entirely from the scope: the **477 rows** of the past period were never requested, and the total came out at 28,790.8 m³, that is, 0.74 times the truth. A collector finishing successfully does not say which set it was authorized to see; the scope question is asked **before** collecting. T2 is the lesson's main finding. The number of rows collected is **1,877**, exactly the same as the number the source reported; a check that looks at "did as many records arrive as expected" passes here. Yet when the record ids are counted, **35 records never arrived** and **35 records arrived twice**. Because the internal order of equal readings is rebuilt on every request, the page boundary fell on a different record each time: one moved to the end of the previous page and was skipped, another fell at the start of the next page and arrived again. Why the error is not visible in the measure is the cleanest example of silent loss. Because the skipped and duplicated records came from the same cluster of equal values, they **carry the same reading value**: the total stays at 38,690.7 m³ and the difference is below the 0.05 m³ threshold. The damage appears only in a breakdown that depends on the id — in the region breakdown the largest deviation is **70.5 m³**, 1,410 times the threshold. T3 collects the same 1,877 records once in 38 requests; the only thing that makes the difference is that the cursor advances over a **unique and stable** key. Offset-based pagination assumes the list stays fixed between requests, and this assumption is wrong both with a non-unique sort key and with a source that gains records while collection is going on. #### The Queue That Obeys the Rate Limit ```js // queue.mjs — the collection queue under a rate limit. There is no throttling technique: // what is measured is how many wait units the queue that obeys the limit spends and how // many records the queue that does not obey it leaves out. Time is not measured with a // real clock; the source's window count is counted as a wait unit. import { RECORDS, recordId, server, PAGE_SIZE, MAX_SIZE, QUOTA } from "./api.mjs"; const FULL = ["reading", "history"]; // H1: the queue that counts the limit response as an empty page. When no records // arrive, it says "the set is finished". function h1() { const s = server(), collected = []; let cursor = null, y; do { y = s.request({ scope: FULL, cursor, size: PAGE_SIZE }); collected.push(...y.records); cursor = y.next; } while (y.records.length > 0 && cursor !== null); return { collected, ...s.counts() }; } // H2: the queue that obeys the limit. It counts the rate limit quota itself and waits when it is full. function h2(size) { const s = server(), collected = []; let cursor = null, inWindow = 0; do { if (inWindow === QUOTA) { s.wait(); inWindow = 0; } const y = s.request({ scope: FULL, cursor, size }); inWindow++; collected.push(...y.records); cursor = y.next; } while (cursor !== null); return { collected, ...s.counts() }; } const y1 = (h) => h.map((v, j) => (j ? String(v).padStart([0, 9, 10, 12, 10, 9][j]) : String(v).padEnd(26))).join(""); console.log(y1(["queue", "rows in", "requests", "wait units", "rows out", "skipped"])); for (const [name, f] of [["H1 treats limit as empty", h1], [`H2 compliant, size ${PAGE_SIZE}`, () => h2(PAGE_SIZE)], ["H2 compliant, size 20", () => h2(20)], ["H2 compliant, size 100", () => h2(100)], [`H2 compliant, size ${MAX_SIZE}`, () => h2(MAX_SIZE)]]) { const r = f(), distinct = new Set(r.collected.map(recordId)).size; console.log(y1([name, RECORDS.length, r.requests, r.waits, r.collected.length, RECORDS.length - distinct])); } ``` ``` queue rows in requests wait units rows out skipped H1 treats limit as empty 1877 13 0 600 1277 H2 compliant, size 50 1877 38 3 1877 0 H2 compliant, size 20 1877 94 7 1877 0 H2 compliant, size 100 1877 19 1 1877 0 H2 compliant, size 200 1877 10 0 1877 0 ``` H1 is the costliest misunderstanding of a rate limit. Because the thirteenth request exceeded the quota, it came back carrying no records, the collector read this as "the set is finished", and the process ended with a zero exit code: **600 records** were collected, **1,277 records** were left out. An empty page and a rejected request look alike; if they are not told apart, the difference shows up only in the row count. H2 collects the same set completely, and its cost is **3 wait units**. The last three rows show where this cost comes from: the wait unit comes from dividing the number of requests by the rate limit quota. With a page size of 20, 94 requests and 7 wait units are needed; with a size of 200, 10 requests are enough and the collection ends in a single window. The collected records are 1,877 in all four rows. **Page size is not a detail, it is directly a collection time decision**, and the source's upper limit determines the lower limit of this time. According to DC12, when an attempt is made to exceed this upper limit, no error comes either: a queue that asks for a 400-record page and advances its position by 400 silently skips 200 records on every turn, because the source gives 200. The number of records on the page that arrives is counted; the requested size is not assumed. #### Summary - In a set collected from an interface, the number to verify is not the number of records returned but how many of the rows in the source entered the set once. - A narrow-scope token leaves out 477 rows without producing any warning, and the total drops to 0.74 times the truth; the scope question is asked before collecting. - Offset-based pagination on a non-unique key skips 35 records and collects 35 records twice; the row count and the overall total look correct, and the damage shows up in the region breakdown as a 70.5 m³ deviation. - A cursor advancing over a unique and stable key collects the same set as 1,877 records, zero skipped, and zero duplicated. - The queue that counts the rate limit response as an empty page stops at 600 records and loses 1,277 records; the queue that obeys the limit spends 38 requests and 3 wait units, and when the page size is raised to 200 the wait drops to zero. #### Next Step The source of this lesson's data was an interface designed for the party that receives its data: the field names were known, the page limit was declared, and the number of records was written inside the response. Part of the data, however, does not stand behind such an interface; it stands inside the structure of a page arranged for human eyes. There are no field names there; there are only position and appearance markers, and these are not a contract. The next lesson parses a fictional publication page and measures a single thing: when the structure changes, how many selectors break, how many rows drop, and how many rows survive having been read from the wrong field. ### Structural Parsing Source: https://academia.sh/en/courses/data-collection-and-preparation/structural-parsing A page that publishes the same 1,877 rows is read in four structure versions with two selector sets, and the number of broken selectors turns out not to measure the damage: when a single wrapper layer is inserted, the path selector of the positional set breaks and all 1,877 rows drop, but the failure is loud. When a column is inserted, no selector breaks, 1,877 records come out, none drops, and every one is read from the wrong field; the total deviates by only 60.1 m³, 0.16%, and the region field of 1,877 records is no longer even a region name. When the presentation layer is renewed, this time two selectors of the semantic set break and the records out drop to zero. Comparing the total the page itself reports with the computed total is the only check that catches the silent shift. The source of the previous lesson's data was an interface designed for the party that receives its data: the field names were inside the response, and the page limit was declared. Part of the data, however, does not stand behind such an interface; it stands inside the structure of a page arranged for human eyes. There are no field names there; only position and appearance markers say which part of the record is where. The lesson's measure is this: when the structure of the page changes, how many selectors break, how many rows drop, and how many rows survive having been read from the wrong field. The real question of the measure is not the number itself but whether the break **is heard**. The technical question is not enough on its own here either: collecting data from a page by scraping, without the source's terms of use and an explicit access permission, is a legal and professional problem even if it is technically possible; here a permitted collection is assumed, and fields carrying personal data were never put on the page. - **DC15.** The page is fiction and is modeled with `node`: the document tree is built in memory, and no network call is made. The running code is not a collection tool; it only parses, verifies, and counts. **It is a model.** - **DC16.** The page publishes the set of the previous two lessons — 1,877 reading rows, 38,690.7 m³. - **DC17.** The four structure versions carry the same data, and their layout changes: `Y0` is the base, `Y1` puts the table into a wrapper layer, `Y2` inserts a column, `Y3` renews the presentation layer. - **DC18.** A selector is counted as **broken** in two cases: if it matches no node, or if the column position that has to be resolved from the header text cannot be found. - **DC19.** The threshold for counting two readings as the same is 0.05 m³, because the page publishes values with one decimal and a difference below that can only come from rounding. #### The Structure of the Page ```js // page.mjs — the document tree of a fictional public publication page and a small // selector engine. IT IS A MODEL: there is no network call, the page is built in memory // and publishes the same 1877 reading rows. Four structure versions carry the same // data, and their layout changes. export const SEED = 4711; const rng = (t) => { let s = t >>> 0; return () => { s = (Math.imul(s, 1103515245) + 12345) >>> 0; return s / 4294967296; }; }; const r = rng(SEED); export const REGION = ["north", "east", "central", "south", "west"]; export const RECORDS = []; for (let i = 0; i < 1877; i++) { // first the first period of 1400 subscribers, then 477 second const n = i < 1400 ? i : i - 1400, deci = 50 + Math.floor(r() * 321); RECORDS.push({ subscriber_no: 10000 + n, period: i < 1400 ? "2024-01" : "2024-02", deci, previous: 50 + ((deci * 37 + 11) % 321), region: REGION[n % 5] }); } export const recordId = (k) => `${k.subscriber_no}-${k.period}`; export const m3 = (d) => d / 10; // Node: { tag, props, children }; children is either an array of nodes or a text. const d = (tag, props, children) => ({ tag, props, children }); export const text = (n) => (typeof n.children === "string" ? n.children : n.children.map(text).join("")); const childrenOf = (n) => (Array.isArray(n.children) ? n.children : []); const descendants = (n) => childrenOf(n).flatMap((c) => [c, ...descendants(c)]); // Selector language: space = any descendant level, ">" = direct child. // Step form: tag, .class, [name=value] or a combination of these. const part = (p) => { const m = /^([\w-]*)(?:\.([\w-]+))?(?:\[([\w-]+)=([\w-]+)\])?$/.exec(p); return { tag: m[1], cls: m[2], attr: m[3], value: m[4] }; }; const matches = (n, t) => (!t.tag || n.tag === t.tag) && (!t.cls || (n.props.class ?? "") === t.cls) && (!t.attr || n.props[t.attr] === t.value); export function find(root, selector) { let nodes = [root], direct = false; for (const p of selector.trim().split(/\s+/)) { if (p === ">") { direct = true; continue; } const t = part(p); nodes = nodes.flatMap((n) => (direct ? childrenOf(n) : descendants(n)).filter((x) => matches(x, t))); direct = false; } return nodes; } // Y0 is the base; Y1 puts the table into a wrapper layer; Y2 inserts a "previous_m3" // column; Y3 renews the presentation: class names change to generated names and // the header of the reading column is renamed. export function page(version) { const extra = version === "Y2", renewed = version === "Y3"; const cn = (a) => (renewed ? { summary: "b-7f2", total: "b-91c", row: "b-4ad" }[a] : a); const header = extra ? ["subscriber_no", "period", "previous_m3", "reading_m3", "region"] : ["subscriber_no", "period", renewed ? "usage_m3" : "reading_m3", "region"]; const cells = (k) => (extra ? [k.subscriber_no, k.period, m3(k.previous), m3(k.deci), k.region] : [k.subscriber_no, k.period, m3(k.deci), k.region]) .map((v) => d("cell", {}, String(v))); const table = d("table", {}, [ d("header", {}, [d("row", {}, header.map((b) => d("cell", {}, b)))]), d("body", {}, RECORDS.map((k) => d("row", { class: cn("row"), "data-kind": "reading" }, cells(k)))), ]); const total = RECORDS.reduce((t, k) => t + m3(k.deci), 0).toFixed(1); return d("page", {}, [ d("summary", { class: cn("summary") }, [d("p", { class: cn("total") }, `Total: ${total} m3`)]), d("section", {}, [version === "Y1" ? d("wrapper", { class: "box" }, [table]) : table]), ]); } if (import.meta.url === `file://${process.argv[1]}`) { console.log(`seed ${SEED}; page publishes ${RECORDS.length} reading rows, total ` + `${RECORDS.reduce((t, k) => t + m3(k.deci), 0).toFixed(1)} m3`); const y = (h) => h.map((v, j) => (j ? String(v).padStart([0, 8, 8, 52, 32][j]) : String(v).padEnd(8))).join(""); console.log(y(["version", "nodes", "rows", "column headers", "first data row"])); for (const s of ["Y0", "Y1", "Y2", "Y3"]) { const k = page(s), rows = find(k, "body > row"); console.log(y([s, descendants(k).length, rows.length, find(k, "header row cell").map(text).join(","), find(rows[0], "cell").map(text).join(",")])); } } ``` ``` seed 4711; page publishes 1877 reading rows, total 38690.7 m3 version nodes rows column headers first data row Y0 9396 1877 subscriber_no,period,reading_m3,region 10000,2024-01,18,north Y1 9397 1877 subscriber_no,period,reading_m3,region 10000,2024-01,18,north Y2 11274 1877 subscriber_no,period,previous_m3,reading_m3,region 10000,2024-01,30.1,18,north Y3 9396 1877 subscriber_no,period,usage_m3,region 10000,2024-01,18,north ``` What the four versions have in common does not show in the second column from the right: **the number of data rows is 1,877 in all four**. The published record set did not change at all. What changed is the node count and the column layout. Y1 adds a single node to the page (from 9,396 to 9,397), Y2 adds a cell to every row and raises the node count to 11,274, Y3 adds no node and changes only the texts. None of these three changes corrupts the data. Y1 may have been made to put the view in a box, Y2 to publish one more column, Y3 to renew the presentation; all three are ordinary maintenance work from the point of view of the side that publishes the page. The structure of the page is not a contract, and the publishing side has no obligation to tell anyone. What the parser attaches itself to is exactly this contractless surface. The last column shows why Y2 is special. The cells of the first row are now `10000,2024-01,30.1,18,north`: the third cell holds not 18 but 30.1, and 30.1 also looks like a reading value. Because the new column is **numeric and in the same range**, a parser that takes the third cell to be the reading finds a valid number. #### What Breaks When the Structure Changes ```js // parse.mjs — two selector sets read the same page. A1 relies on position: a path and // a fixed column order. A2 relies on meaning: the row is found by a data marker, the // column by the header text. What is measured is how many selectors broke when the // structure changed and how many rows dropped or survived having been read from the wrong field. import { page, find, text, RECORDS, recordId, m3, REGION } from "./page.mjs"; const A1 = { name: "A1 positional", row: "section > table > body > row", total: "summary > p", columns: () => ({ subscriber: 0, period: 1, reading: 2, region: 3 }) }; const A2 = { name: "A2 semantic", row: "row[data-kind=reading]", total: ".summary .total", columns: (root) => { const b = find(root, "header row cell").map(text); return { subscriber: b.indexOf("subscriber_no"), period: b.indexOf("period"), reading: b.indexOf("reading_m3"), region: b.indexOf("region") }; } }; // Threshold 0.05 m3: readings are published with one decimal, and a difference // below that can only come from rounding. const THRESHOLD = 0.05; const TRUTH = new Map(RECORDS.map((k) => [recordId(k), m3(k.deci)])); const TARGET = RECORDS.reduce((t, k) => t + m3(k.deci), 0); function extract(root, a) { const broken = [], rows = find(root, a.row), totals = find(root, a.total); if (rows.length === 0) broken.push(a.row); if (totals.length === 0) broken.push(a.total); const k = a.columns(root); for (const [name, i] of Object.entries(k)) if (i < 0) broken.push(`column:${name}`); const out = []; for (const s of rows) { const h = find(s, "cell").map(text), reading = Number(h[k.reading]); if (!Number.isFinite(reading)) continue; // silently dropped row out.push({ id: `${h[k.subscriber]}-${h[k.period]}`, reading, region: h[k.region] }); } const reported = totals.length ? Number(/[\d.]+/.exec(text(totals[0]))[0]) : null; return { broken, out, reported }; } const y = (h) => h.map((v, j) => (j > 1 ? String(v).padStart([0, 0, 8, 9, 10, 9, 7, 11, 10][j]) : String(v).padEnd(j ? 14 : 8))).join(""); console.log(y(["version", "parser", "broken", "rows in", "rows out", "dropped", "wrong", "total m3", "reported"])); const results = {}; for (const version of ["Y0", "Y1", "Y2", "Y3"]) { const root = page(version); for (const a of [A1, A2]) { const r = results[version + a.name] = extract(root, a); const t = r.out.reduce((s, x) => s + x.reading, 0); const wrong = r.out.filter((x) => !(Math.abs((TRUTH.get(x.id) ?? -1) - x.reading) < THRESHOLD)); console.log(y([version, a.name, r.broken.length, RECORDS.length, r.out.length, RECORDS.length - r.out.length, wrong.length, t.toFixed(1), r.reported === null ? "-" : r.reported.toFixed(1)])); } } const y1a = results["Y1A1 positional"], y2a = results["Y2A1 positional"], y3b = results["Y3A2 semantic"]; console.log(`\nIn Y1 a single layer was added and the path selector of A1, ${y1a.broken[0]}, found no ` + `rows: all ${RECORDS.length} rows dropped, the failure is loud`); const t2 = y2a.out.reduce((s, x) => s + x.reading, 0); console.log(`In Y2 A1 has no broken selector, ${y2a.out.length} records came out and none ` + `dropped; but because the columns shifted by one the total deviated by ${(t2 - TARGET).toFixed(1)} m3 and ` + `the region field of ${y2a.out.filter((x) => !REGION.includes(x.region)).length} records is not a region ` + `name`); console.log(`In Y2 the difference between the ${y2a.reported.toFixed(1)} m3 the page itself reports and the ` + `${t2.toFixed(1)} m3 A1 computes is ${Math.abs(t2 - y2a.reported).toFixed(1)} ` + `m3, ${Math.round(Math.abs(t2 - y2a.reported) / THRESHOLD)} times the ${THRESHOLD} m3 threshold`); console.log(`In Y3 ${y3b.broken.length} selectors of A2 broke (${y3b.broken.join(", ")}) and the ` + `records out are ${y3b.out.length}; in the same version A1 lost nothing`); ``` ``` version parser broken rows in rows out dropped wrong total m3 reported Y0 A1 positional 0 1877 1877 0 0 38690.7 38690.7 Y0 A2 semantic 0 1877 1877 0 0 38690.7 38690.7 Y1 A1 positional 1 1877 0 1877 0 0.0 38690.7 Y1 A2 semantic 0 1877 1877 0 0 38690.7 38690.7 Y2 A1 positional 0 1877 1877 0 1877 38630.6 38690.7 Y2 A2 semantic 0 1877 1877 0 0 38690.7 38690.7 Y3 A1 positional 0 1877 1877 0 0 38690.7 38690.7 Y3 A2 semantic 2 1877 0 1877 0 0.0 - In Y1 a single layer was added and the path selector of A1, section > table > body > row, found no rows: all 1877 rows dropped, the failure is loud In Y2 A1 has no broken selector, 1877 records came out and none dropped; but because the columns shifted by one the total deviated by -60.1 m3 and the region field of 1877 records is not a region name In Y2 the difference between the 38690.7 m3 the page itself reports and the 38630.6 m3 A1 computes is 60.1 m3, 1202 times the 0.05 m3 threshold In Y3 2 selectors of A2 broke (.summary .total, column:reading) and the records out are 0; in the same version A1 lost nothing ``` In the base structure, both sets produce the same set: 1,877 records, zero dropped, zero wrong, 38,690.7 m³. Two parsers that give the same result are not the same parser, and the difference can be measured only when the structure changes. In the Y1 row there is **one layer**, one selector, and 1,877 rows. The single `wrapper` node that came in between invalidated the four-step path selector of A1, because `section > table` asks for a direct child and the table is now a grandchild of section. The number of broken selectors is 1, the dropped rows 1,877. This loss is **loud**: the records out are zero, and not noticing it would require running no check at all. The row selector of A2, `row[data-kind=reading]`, looks for the marker and not the path, so it finds 1,877 rows on the same page. Y2 is the lesson's main finding. **None of the selectors of A1 broke**: the path is in place, all four indexes are within bounds, 1,877 records came out, and none dropped. But because the columns shifted by one, the previous period's reading came into the reading field, and **all 1,877 records carry a wrong value**. The total is 38,630.6 m³, that is, in fact only 60.1 m³, 0.16%, away. A threshold, a magnitude check, a glance asking "is it plausible" lets this number through. The region field, meanwhile, now carries the reading value: **the region field of 1,877 records is not even a region name**. The number of broken selectors does not measure the damage — when that number was 1 in Y1, not a single wrong value was produced; when it was 0 in Y2, not a single correct value was produced. Y3 turns the direction of the same problem around. When the presentation was renewed, the class-based total selector and the header-text-based reading column of A2 broke together: **2 broken selectors, zero records out**. A1, which relies on position, lost nothing in the same version, because neither the class name nor the header text matters to it. Semantic selectors are not more durable; they depend on **other things**, and which set will hold depends on which direction the structure changes. It cannot be known without measuring. The last lines show the only real defense. The page publishes its own total, and in Y2 this number is 38,690.7 m³ while the parsed set gives 38,630.6 m³; the difference is 60.1 m³, 1,202 times the threshold. In the case where no selector broke, no row dropped, and the total looked plausible, the only thing that caught the error was **comparison with the number the source itself reports**. Verifying a parser is not looking at whether the selectors match, it is counting the set that came out against the source's own declaration. #### Summary - The structure of a page is not a contract; the same 1,877 rows can be published in four layouts, and a layout change is ordinary maintenance work for the publishing side. - Inserting a single layer breaks the positional path selector: 1 broken selector, 1,877 dropped rows, zero records out. This failure is loud and hard to miss. - Inserting a column breaks no selector: 1,877 records come out, zero drop, and the value of all 1,877 is wrong; the total deviates by only 60.1 m³, 0.16%, and the region field stops being a region name. - Renewing the presentation layer this time breaks the semantic set (2 selectors, zero records) while the positional set is not harmed by the same version; durability comes not from the type of the set but from the direction of the change. - The number of broken selectors does not measure the damage; comparing the parsed set with the total the source itself reports is the only check that catches the silent shift. #### Next Step In four lessons the data came by four routes, and how many rows were lost on each route was counted. The common assumption of these lessons was that the whole set could be collected: the file could be read, the query could run, the interface could be paged to the end, the page could be parsed from start to finish. When the population grows, this assumption falls, and a decision takes its place — a subset is taken instead of all the records. The next lesson measures the four answers four sampling decisions give to the same question and asks the real question: how well the subset taken represents the population, and who the records left outside are. ### Sampling Source: https://academia.sh/en/courses/data-collection-and-preparation/sampling Four samples drawn from a population of 1,877 rows give four different answers to the same two questions: the convenience sample gives a mean of 19.99 m³ and a top-tier share of 33.0%, the simple random sample 20.04 m³ and 32.0%, the sample stratified by period 20.50 m³ and 35.0%, and the sample whose draw unit is the subscriber 20.70 m³ and 35.0%; the population's own answer is 20.61 m³ and 35.4%. The region representation error of the convenience sample is 0.0 points, and that is exactly why it inspires confidence — the period representation error of the same sample is 25.4 points, because all 477 rows of the second period and 1,200 of the 1,400 subscribers were left outside. The same random decision gives, over twelve seeds, a range 3.90 m³ wide for n=50 and 0.71 m³ wide for n=800: the error of a single sample cannot be known, its spread is measured. In four lessons the data came by four routes, and how many rows were lost on each route was counted. The common assumption of these lessons was that the whole set could be collected: the file could be read, the query could run, the interface could be paged to the end, the page could be parsed from start to finish. When the population grows, this assumption falls, and a decision takes its place — a subset is taken instead of all the records. The measure of sampling is not whether the subset is correct; a subset is not the population and always gives a different number. What is measured is **representation**: how accurately the sample reflects which dimension of the population, and who the rows left outside are. All four decisions of this lesson are defensible, and all four give a different answer to the same question. - **DC20.** The population is fiction and is the same as the set of the previous four lessons: 1,877 reading rows, 1,400 subscribers, 38,690.7 m³. The mean per reading row is 20.61 m³, that is, the same number computed in the second lesson of this course. **It is a model.** - **DC21.** The generator is this lesson's own generator and its seed is visible (20240). With the same seed the output is the same on every run; the mixing does not change anything in place, it works on a copy of the population. - **DC22.** The tariff tiers split at 10 and 25 m³, and the comparison is made on the deci-m³ integer; this way the boundary values never enter floating-point equality. - **DC23.** **Representation error** is the largest absolute difference between the sample share and the population share on the same dimension, and it is written in percentage points. - **DC24.** The threshold for counting two means as the same is 0.05 m³, because readings are kept with one decimal and a difference below that can only come from rounding. #### The Population's Own Answer ```js // population.mjs — the population the sampling will work on. IT IS A MODEL: the same 1877 // reading rows as in the previous four lessons, generated with the same seed. The // tariff tiers split at 10 and 25 m3; the comparison is made on the deci-m3 integer, // so the boundary values never enter floating-point equality. export const SEED = 4711; export const rng = (t) => { let s = t >>> 0; return () => { s = (Math.imul(s, 1103515245) + 12345) >>> 0; return s / 4294967296; }; }; export const REGION = ["north", "east", "central", "south", "west"]; export const POPULATION = []; const r = rng(SEED); for (let i = 0; i < 1877; i++) { // first the first period of 1400 subscribers, then 477 second const n = i < 1400 ? i : i - 1400, deci = 50 + Math.floor(r() * 321); POPULATION.push({ subscriber_no: 10000 + n, period: i < 1400 ? "2024-01" : "2024-02", deci, region: REGION[n % 5], tier: deci <= 100 ? "under 10" : deci <= 250 ? "10-25" : "over 25" }); } export const m3 = (k) => k.deci / 10; export const mean = (set) => set.reduce((t, k) => t + m3(k), 0) / set.length; export const topShare = (set) => 100 * set.filter((k) => k.tier === "over 25").length / set.length; // The category shares of a dimension (percent). export const share = (set, field) => { const s = new Map(); for (const k of set) s.set(k[field], (s.get(k[field]) ?? 0) + 1); return new Map([...s].map(([a, n]) => [a, 100 * n / set.length])); }; // Representation error: the largest absolute difference between the sample share and // the population share on the same dimension, in percentage points. export const error = (set, field) => { const o = share(set, field), k = share(POPULATION, field); return Math.max(...[...k.keys()].map((a) => Math.abs((o.get(a) ?? 0) - k.get(a)))); }; if (import.meta.url === `file://${process.argv[1]}`) { console.log(`seed ${SEED}; population ${POPULATION.length} reading rows, ` + `${new Set(POPULATION.map((k) => k.subscriber_no)).size} subscribers, ` + `${POPULATION.reduce((t, k) => t + m3(k), 0).toFixed(1)} m3`); console.log(`answer of the population: mean ${mean(POPULATION).toFixed(2)} m3, ` + `share of the top tier ${topShare(POPULATION).toFixed(1)}%`); for (const field of ["period", "region", "tier"]) console.log(`${field.padEnd(8)}` + [...share(POPULATION, field)] .map(([a, p]) => `${a} ${p.toFixed(1)}`).join(" | ")); } ``` ``` seed 4711; population 1877 reading rows, 1400 subscribers, 38690.7 m3 answer of the population: mean 20.61 m3, share of the top tier 35.4% period 2024-01 74.6 | 2024-02 25.4 region north 20.0 | east 20.0 | central 20.0 | south 20.0 | west 20.0 tier 10-25 46.6 | under 10 18.0 | over 25 35.4 ``` This lesson differs from the others because the answer of the population is known: the sampling error is not estimated, it is measured directly. In a real study this line does not exist, and since the right answer is not known, the only thing that can be checked is **the decision itself**. The lower three lines are the structure of the population on three dimensions, and they are the scale the sample will be compared against. The period dimension is unbalanced: 74.6% of the rows come from the first period, because only 477 of the 1,400 subscribers have a second period reading. The region dimension, on the other hand, is almost perfectly balanced, the share of the five regions is around 20%. This distinction determines the rest of the lesson: a sample can look correct on a balanced dimension without any effort. #### Four Sampling Decisions ```js // sample.mjs — four sampling decisions from the same population. The generator is this // lesson's own generator and the seed is visible; with the same seed the output is the // same on every run. What is measured is not whether the sample is "correct" but how // well it represents the population on which dimension and who the rows left outside are. import { POPULATION, rng, m3, mean, topShare, error } from "./population.mjs"; const N = 200, SAMPLE_SEED = 20240; const mix = (array, seed) => { const g = rng(seed), a = [...array]; for (let i = a.length - 1; i > 0; i--) { const j = Math.floor(g() * (i + 1)); [a[i], a[j]] = [a[j], a[i]]; } return a; }; // O1: the first N rows of the list. O2: simple random sample. O3: proportional // sample stratified by period — from each stratum as many rows are taken as its share in the population. const O1 = (n) => POPULATION.slice(0, n); const O2 = (n, seed = SAMPLE_SEED) => mix(POPULATION, seed).slice(0, n); const O3 = (n, seed = SAMPLE_SEED) => ["2024-01", "2024-02"].flatMap((d) => { const t = POPULATION.filter((k) => k.period === d); return mix(t, seed + d.length).slice(0, Math.round(n * t.length / POPULATION.length)); }); // O4: the draw unit is the subscriber, not the row. N subscribers are chosen and // all rows of the chosen subscribers are taken; the number of rows that come out is // determined not by the decision but by the number of rows per subscriber. const O4 = (n, seed = SAMPLE_SEED) => { const inside = new Set(mix([...new Set(POPULATION.map((k) => k.subscriber_no))], seed).slice(0, n)); return POPULATION.filter((k) => inside.has(k.subscriber_no)); }; const THRESHOLD = 0.05; // the threshold for counting two means as the same const y = (h) => h.map((v, j) => (j ? String(v).padStart([0, 9, 10, 8, 9, 8, 8, 8, 8][j]) : String(v).padEnd(28))).join(""); console.log(`population: mean ${mean(POPULATION).toFixed(2)} m3, top tier ` + `${topShare(POPULATION).toFixed(1)}%; last three columns representation error (percentage points)`); console.log(y(["sampling decision", "rows in", "rows out", "mean", "diff m3", "top %", "period", "region", "tier"])); const result = {}; for (const [name, f] of [["O1 convenience (first 200)", O1], ["O2 simple random", O2], ["O3 stratified (period)", O3], ["O4 subscriber unit", O4]]) { const o = result[name] = f(N); console.log(y([name, POPULATION.length, o.length, mean(o).toFixed(2), (mean(o) - mean(POPULATION)).toFixed(2), topShare(o).toFixed(1), error(o, "period").toFixed(1), error(o, "region").toFixed(1), error(o, "tier").toFixed(1)])); } // The same decision, twelve seeds: the error of a single sample cannot be known, its spread is measured. const y2 = (h) => h.map((v, j) => (j ? String(v).padStart([0, 8, 10, 10, 9, 14][j]) : String(v).padEnd(12))).join(""); console.log(`\nO2, twelve seeds (${SAMPLE_SEED}-${SAMPLE_SEED + 11})`); console.log(y2(["sample", "seeds", "minimum", "maximum", "range", "top % range"])); for (const n of [50, 200, 800]) { const t = [], u = []; for (let i = 0; i < 12; i++) { const o = O2(n, SAMPLE_SEED + i); t.push(mean(o)); u.push(topShare(o)); } console.log(y2([`n = ${n}`, 12, Math.min(...t).toFixed(2), Math.max(...t).toFixed(2), (Math.max(...t) - Math.min(...t)).toFixed(2), (Math.max(...u) - Math.min(...u)).toFixed(1)])); } const o1 = result["O1 convenience (first 200)"], o3 = result["O3 stratified (period)"]; const insideSubscribers = new Set(o1.map((k) => k.subscriber_no)); console.log(`\n${POPULATION.length - o1.length} rows remained outside O1: all ` + `${POPULATION.filter((k) => k.period === "2024-02").length} rows of the second period and ` + `${new Set(POPULATION.map((k) => k.subscriber_no)).size - insideSubscribers.size} of the ` + `${new Set(POPULATION.map((k) => k.subscriber_no)).size} subscribers in the population`); console.log(`The mean of O1 differs from the population by ${Math.abs(mean(o1) - mean(POPULATION)).toFixed(2)} ` + `m3, ` + `${Math.round(Math.abs(mean(o1) - mean(POPULATION)) / THRESHOLD)} times the ${THRESHOLD} m3 threshold; but the region representation ` + `error is ${error(o1, "region").toFixed(1)} points, that is, the sample looks flawless on one dimension`); console.log(`O3 brings the period dimension down to ${error(o3, "period").toFixed(1)} points, but its error on the region ` + `dimension is ${error(o3, "region").toFixed(1)} points: stratification guarantees only the ` + `dimension it stratifies on`); const o4 = result["O4 subscriber unit"]; console.log(`O4 selected ${N} subscribers but produced ${o4.length} rows: ${o4.length - N} of the selected ` + `subscribers also have a second period reading. Because the draw unit is the subscriber, the mean per ` + `subscriber, ${(o4.reduce((s, k) => s + m3(k), 0) / N).toFixed(2)} m3, can be read directly; the mean per ` + `subscriber of the population is ` + `${(POPULATION.reduce((s, k) => s + m3(k), 0) / new Set(POPULATION.map((k) => k.subscriber_no)).size) .toFixed(2)} m3`); ``` ``` population: mean 20.61 m3, top tier 35.4%; last three columns representation error (percentage points) sampling decision rows in rows out mean diff m3 top % period region tier O1 convenience (first 200) 1877 200 19.99 -0.63 33.0 25.4 0.0 3.5 O2 simple random 1877 200 20.04 -0.58 32.0 1.1 2.5 3.4 O3 stratified (period) 1877 200 20.50 -0.11 35.0 0.1 2.0 0.5 O4 subscriber unit 1877 274 20.70 0.09 35.0 1.6 3.7 0.5 O2, twelve seeds (20240-20251) sample seeds minimum maximum range top % range n = 50 12 18.41 22.32 3.90 22.0 n = 200 12 19.26 21.55 2.29 11.5 n = 800 12 20.33 21.04 0.71 4.3 1677 rows remained outside O1: all 477 rows of the second period and 1200 of the 1400 subscribers in the population The mean of O1 differs from the population by 0.63 m3, 13 times the 0.05 m3 threshold; but the region representation error is 0.0 points, that is, the sample looks flawless on one dimension O3 brings the period dimension down to 0.1 points, but its error on the region dimension is 2.0 points: stratification guarantees only the dimension it stratifies on O4 selected 200 subscribers but produced 274 rows: 74 of the selected subscribers also have a second period reading. Because the draw unit is the subscriber, the mean per subscriber, 28.36 m3, can be read directly; the mean per subscriber of the population is 27.64 m3 ``` All four decisions were drawn from the same 1,877 rows, and all four gave a different answer to the same two questions. The mean varies between 19.99 and 20.70 m³, the share of the top tier between 32.0% and 35.0%. No decision made a calculation error; all four gave the correct mean of their own set. O1 is the lesson's trap. The region representation error is **0.0 points** — the five regions are in the sample at exactly the same ratio as their share in the population. Because the list is ordered by subscriber number and the region derives from the subscriber number, the first 200 rows come out flawless on this dimension. The period representation error of the same sample, however, is **25.4 points**: the whole sample is from the first period, and **all 477 rows** of the second period and **1,200** of the 1,400 subscribers were left outside. A sample looking flawless on one dimension does not show that it is correct on other dimensions, and balanced dimensions feed this fallacy. The mean errors of O2 and O1 are close to each other: 0.58 and 0.63 m³, both more than ten times the threshold. The difference between them is not the size of the error but its **auditability**. The lower table gives the error of O2 as a spread: the same decision over twelve seeds produces values between 18.41 and 22.32 m³ for n=50, between 19.26 and 21.55 for n=200, and between 20.33 and 21.04 for n=800. The range goes down from 3.90 to 0.71 m³, and the range in the share of the top tier falls from 22.0 points to 4.3 points. The error of a random decision cannot be known in a single sample, but its spread can be measured and narrowed by enlarging the sample. The error of O1, on the other hand, does not depend on the seed; it depends on the order of the list, it cannot be narrowed by trying again, and it cannot be expressed as a range. O3 corrects the dimension where the population is unbalanced. The period representation error falls from 25.4 points to **0.1 points**, the mean error to 0.11 m³, and the share of the top tier to 35.0% — the closest value to the population's 35.4%. But the region representation error of the same sample is **2.0 points**, that is, larger than that of O1. Stratification is not a seal of quality, it is a targeted guarantee: it secures only the dimension it stratifies on and leaves the remaining dimensions to chance. Which dimension to stratify on depends on in which breakdown the question the sample will answer is going to be read. O4 changes the draw unit itself and produces the only irregular row in the table: 200 subscribers were selected but the set came out as **274 rows**, because 74 of the selected subscribers also have a second period reading. The number of rows was determined not by the decision but by the number of rows per subscriber. In return, this sample gives the mean per subscriber directly — 28.36 m³, against 27.64 m³ for the population. In the second lesson of this course the unit of the row was a query problem; here it is a sampling problem, and the answer is the same: **the draw unit must be the same as the row of the report.** A sample drawn at the row level gives a subscriber with two readings two chances of being selected and silently weights a question per subscriber. #### Summary - The measure of sampling is not the correctness of the subset but representation: the largest difference between the sample share and the population share, written dimension by dimension. - Four samples drawn from the same population give the mean as 19.99, 20.04, 20.50, and 20.70 m³; the population's answer is 20.61 m³, and none of the four decisions made a calculation error. - The convenience sample gives 0.0 points of error on the region dimension, and that is exactly why it inspires confidence; the period error of the same sample is 25.4 points, because all 477 rows of the second period and 1,200 of the 1,400 subscribers were left outside. - The error of a random decision is not known in a single sample, its spread is measured: over twelve seeds the range is 3.90 m³ for n=50 and 0.71 m³ for n=800. The error of the convenience sample cannot be narrowed this way. - Stratification guarantees only the dimension it stratifies on: while the period error falls to 0.1 points, the region error stays at 2.0 points. - The draw unit must be the same as the row of the report: a sample of 200 subscribers produces 274 rows and gives the mean per subscriber directly, while a sample drawn at the row level silently weights the same question. #### Next Step Over five lessons a single account was kept. The file gave 2,076 physical rows and one parsing decision silently dropped 142 rows; the query returned 1,877 rows and a join dropped 766 rows and multiplied 669 rows; the interface, with a narrow scope, never gave 477 rows, and pagination skipped 35 records and brought 35 records twice; when its structure changed, the publication page dropped all 1,877 rows in one version and made them be read from the wrong field in another. The 1,677 rows of this lesson, on the other hand, are not a loss but **a written decision**, and the difference between the two is the rule of the course: a cleaning whose loss you cannot write down is not a cleaning. But all five lessons counted the rows **from the outside**. How many rows came, how many rows dropped, how many rows came twice — all of it was read over the number of records. The **inside** of the rows that came was never opened: whether a field was empty, whether the same subscriber appeared twice with two different spellings, whether a date was corrupted while being converted from text to number was never looked at. The next topic goes inside the row and keeps the same account, this time at the field level: when missing values are deleted, which group is eliminated disproportionately, which records a similarity threshold merges wrongly, how many records a silent coercion corrupts. ## Data Cleaning Measuring, with row accounting, the defects that surface once the rows are opened up and the decisions that fix them: whether missingness is random and which group dropping removes disproportionately, the two counts that exact matching and a similarity threshold give and the false matches, the silent coercion that raises no error and yields a wrong value, the part where the sets flagged by the interquartile range and the standard score do not overlap and how removing outliers moves the mean, the number of categories left after category harmonization, the matches that text normalization rescues and the ones it destroys. ### Missing Values Source: https://academia.sh/en/courses/data-collection-and-preparation/missing-values The reading field of the 2,086 rows the collection step left behind is empty in three different ways: the cell is blank in 104 rows, holds only whitespace in 31 rows, and says 'none' in 70 rows. The missingness is not random: the rate varies by region from 3.7% to 28.1%, and one region carries 2.85 times the number a random distribution would predict. Dropping the rows with missing readings reduces the set from 2,068 to 1,864 rows, leaves the network mean at 22.45 m³, and looks harmless; yet 99 of the 204 dropped rows come from a single region, and that region's share of the set falls from 17.0% to 13.6%, a drop of 3.4 points. A filter that counts only the empty cell as missing fails to see 100 rows: 31 silently become zero consumption, 69 silently fall out of the numeric filter, and the mean sinks to 22.09 m³. 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 ```js // 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 ```js // 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**. ### Duplicate Records Source: https://academia.sh/en/courses/data-collection-and-preparation/duplicate-records Three deduplication criteria are applied to the 2,068 rows inherited from the previous lesson, and three numbers come out: comparing full rows merges no row, the raw meter code merges 68 rows, and the trimmed, case-normalized code merges 188 rows — the 120 rows in between are the price of a single text decision. In all 188 records that have a second entry the address is written differently, in 92% the tariff class, in 63.8% the meter code, and in none has the reading changed. In record linkage, a job separate from deduplication, 149 of 240 support requests are linked by raw text and 174 by the simplified code, and six go to the wrong subscriber; 17 of the 18 extra links built at a 0.80 threshold are wrong. The step enters with 2,068 rows and leaves with 1,880 rows. The previous lesson reduced the set to 2,068 rows and assumed that each remaining row represents a separate visit. The assumption does not hold: a number of visits have been entered into the form twice. This lesson measures two separate jobs. **Deduplication** works inside one set and reduces two copies of the same record to a single row. **Record linkage** works between two sources and ties a record to an entity in the other; it deletes nothing, it builds a link. The price of a wrong answer differs: in one, a row disappears; in the other, a row is **written to the wrong place**. - **CL5.** The set is inherited from the previous lesson: the 18 rows with an empty identifying field were dropped, and the 204 rows with missing readings were flagged and filled in. Same seed, same rows; the set is again **modeled** with `node` and is **fiction**. - **CL6.** A duplicate is the same visit entered a second time; a subscriber read in two periods is not a duplicate. This distinction was established in the Analysis with Spreadsheets course and is taken as given. - **CL7.** The deduplication criterion is a **key string** produced from the row. Which fields the criterion covers and how it compares the text are two separate decisions. - **CL8.** The similarity **threshold** is a decision. The overlap ratio is a floating-point number and is compared not with an equality operator but with a written threshold. #### How Many Rows Merge as the Criterion Changes ```js // dedupe.mjs — the raw set of the previous lesson is a MODEL: every field is text exactly // as it came from the field form. The set is fiction: 1400 subscribers of the regional // measurement network and their field visit records; same seed, same rows. 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(""); // Decision of the previous lesson: rows with an empty identifying field were dropped, rows with // a missing reading were flagged and filled with the region median. 2068 rows enter this step. const identified = RAW.filter((s) => s.meter_id.trim() !== ""); 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)))])); export const INCOMING = identified.map((s) => toNumber(s.reading_text) === null ? { ...s, reading_text: regionMedian.get(s.region).toFixed(2) } : s); // Deduplication criterion: produces a key string from the row. As the criterion changes, // a different number of rows is left from the same set. export const CRITERIA = [ ["C1 full row", (s) => [s.subscriber_id, s.meter_id, s.period, s.reading_text, s.index_text, s.date_text, s.tariff_text, s.address_text].join("|")], ["C2 subscriber + period + meter (raw)", (s) => `${s.subscriber_id}|${s.period}|${s.meter_id}`], ["C3 subscriber + period + meter (normalized)", (s) => `${s.subscriber_id}|${s.period}|${s.meter_id.trim().toUpperCase()}`], ]; export const dedupe = (l, k) => { const m = new Map(); for (const s of l) if (!m.has(k(s))) m.set(k(s), s); return [...m.values()]; }; if (import.meta.url === `file://${process.argv[1]}`) { console.log(`model (seed ${SEED}): set inherited from the previous step is ${INCOMING.length} rows`); const fmt1 = fmt([45, 8, 11]); console.log("\n" + fmt1(["deduplication criterion", "kept", "merged"])); for (const [name, f] of CRITERIA) { const l = dedupe(INCOMING, f); console.log(fmt1([name, l.length, INCOMING.length - l.length])); } // Second entries: under C3, which field is written differently in rows that fall into the same group. const groups = new Map(); for (const s of INCOMING) { const a = CRITERIA[2][1](s); groups.set(a, (groups.get(a) ?? []).concat([s])); } const pairs = [...groups.values()].filter((l) => l.length > 1); const differ = (field) => pairs.filter((l) => new Set(l.map((s) => s[field])).size > 1).length; const fmt2 = fmt([28, 8, 9]); console.log("\n" + fmt2([`${pairs.length} second entries differ`, "rows", "rate %"])); for (const field of ["meter_id", "address_text", "tariff_text", "reading_text"]) console.log(fmt2([field, differ(field), (100 * differ(field) / pairs.length).toFixed(1)])); } ``` ``` model (seed 20260305): set inherited from the previous step is 2068 rows deduplication criterion kept merged C1 full row 2068 0 C2 subscriber + period + meter (raw) 2000 68 C3 subscriber + period + meter (normalized) 1880 188 188 second entries differ rows rate % meter_id 120 63.8 address_text 188 100.0 tariff_text 173 92.0 reading_text 0 0.0 ``` Three criteria gave three numbers. **C1 full row** looks like the safest criterion and does no work at all; caution here leaves the whole problem exactly as it was. **C2** compares the meter code as it arrived and merges 68 rows; **C3** applies the same criterion to the trimmed, case-normalized code and rises to 188. The **120 rows** in between are the price not of the decision about which fields enter the criterion, but of the decision about **how those fields are compared**. The second table gives the reason. In **all** 188 second entries the address is written differently, in 92% the tariff class, in 63.8% the meter code; the only field that does not change is the reading. The measurement stayed the same, **every piece of text around it was rewritten**. What determines a duplicate is the fields the criterion covers, not the whole row. #### Record Linkage ```js // matching.mjs — a job separate from deduplication: record linkage. The support // requests are a MODEL; the meter code was typed by hand during the call. Two ways of // tying a request to a subscriber are compared: exact matching and a similarity threshold. import { INCOMING, CRITERIA, dedupe, rng, pick, fmt } from "./dedupe.mjs"; const UNIQUE = dedupe(INCOMING, CRITERIA[2][1]); // The meter registry is taken from the deduplicated set; subscribers whose code was never // written legibly are not in the registry. const CODES = new Map(); for (const s of UNIQUE) CODES.set(s.meter_id.trim().toUpperCase(), s.subscriber_id); const simplify = (t) => t.trim().toLocaleUpperCase("tr").replace(/[^A-Z0-9]/g, ""); const SIMPLE = new Map([...CODES.keys()].map((k) => [simplify(k), k])); // 240 support requests. The operator's typing errors come in five kinds: correct, lowercase // with a leading space, no separator, two digits swapped, last digit missing. const codes = [...CODES.keys()], REQUESTS = [], rr = rng(20260305 + 50000); for (let i = 0; i < 240; i++) { const correct = codes[Math.floor(rr() * codes.length)]; const kind = pick(rr(), [0.42, 0.18, 0.14, 0.14, 0.12]); const digits = correct.slice(3), swapped = digits.slice(0, 2) + digits[3] + digits[2]; REQUESTS.push({ request_no: 5001 + i, kind, true_code: correct, typed: kind === 0 ? correct : kind === 1 ? ` ${correct.toLowerCase()}` : kind === 2 ? correct.replace("-", "") : kind === 3 ? `${correct.slice(0, 3)}${swapped}` : correct.slice(0, -1) }); } console.log(`model: ${UNIQUE.length} unique measurement rows, ${CODES.size} unique meter codes, ` + `${REQUESTS.length} support requests`); const fmt1 = fmt([34, 9, 9, 12]); console.log("\n" + fmt1(["exact match", "linked", "wrong", "unlinked"])); for (const [name, f] of [["E1 raw text", (t) => CODES.get(t.typed.trim().toUpperCase())], ["E2 simplified code", (t) => CODES.get(SIMPLE.get(simplify(t.typed)))]]) { const links = REQUESTS.map((t) => [t, f(t)]).filter(([, a]) => a !== undefined); console.log(fmt1([name, links.length, links.filter(([t, a]) => a !== CODES.get(t.true_code)).length, REQUESTS.length - links.length])); } // Similarity: the overlap ratio of three-letter pieces. The threshold is a decision; the // floating-point ratio is not put through an equality operator, it is compared with the threshold. const trigrams = (t) => { const s = ` ${t} `, r = new Set(); for (let i = 0; i + 3 <= s.length; i++) r.add(s.slice(i, i + 3)); return r; }; const overlap = (a, b) => { let o = 0; for (const g of a) if (b.has(g)) o++; return (2 * o) / (a.size + b.size); }; const simples = [...SIMPLE.keys()], grams = new Map(simples.map((k) => [k, trigrams(k)])); const leftover = REQUESTS.filter((t) => !SIMPLE.has(simplify(t.typed))); const fmt2 = fmt([11, 13, 9, 17, 10]); console.log("\n" + fmt2(["threshold", "extra links", "wrong", "tied candidates", "unlinked"])); for (const threshold of [0.60, 0.70, 0.80, 0.90]) { let extra = 0, wrong = 0, tied = 0; for (const t of leftover) { const g = trigrams(simplify(t.typed)); let best = 0, candidates = []; for (const k of simples) { const d = overlap(g, grams.get(k)); if (d > best + 1e-9) { best = d; candidates = [k]; } else if (Math.abs(d - best) < 1e-9) candidates.push(k); } if (best < threshold) continue; if (candidates.length > 1) { tied++; continue; } extra++; if (SIMPLE.get(candidates[0]) !== t.true_code) wrong++; } console.log(fmt2([threshold.toFixed(2), extra, wrong, tied, leftover.length - extra - tied])); } console.log(`\na similarity threshold alone does not resolve the ${leftover.length} requests that ` + `exact matching leaves behind: of the 18 links it builds at the 0.80 threshold, 17 go to the ` + `wrong subscriber, because a code with two digits swapped is another subscriber's valid code`); console.log(`row accounting: in ${INCOMING.length}, out ${UNIQUE.length}; ` + `${INCOMING.length - UNIQUE.length} rows merged, 0 rows silently lost. Side ledger: ` + `of the 240 requests, 192 were linked, 23 of them to the wrong subscriber, 44 stayed ` + `unlinked, and 4 stayed ambiguous`); ``` ``` model: 1880 unique measurement rows, 1392 unique meter codes, 240 support requests exact match linked wrong unlinked E1 raw text 149 6 91 E2 simplified code 174 6 66 threshold extra links wrong tied candidates unlinked 0.60 20 19 46 0 0.70 18 17 4 44 0.80 18 17 4 44 0.90 2 2 0 64 a similarity threshold alone does not resolve the 66 requests that exact matching leaves behind: of the 18 links it builds at the 0.80 threshold, 17 go to the wrong subscriber, because a code with two digits swapped is another subscriber's valid code row accounting: in 2068, out 1880; 188 rows merged, 0 rows silently lost. Side ledger: of the 240 requests, 192 were linked, 23 of them to the wrong subscriber, 44 stayed unlinked, and 4 stayed ambiguous ``` Exact matching gives two numbers under two decisions: with raw text 149 requests are linked, with the simplified code **174** — twenty-five requests had been failing to link only because of the separator. The real warning is in the second column: under both decisions **six requests are linked to the wrong subscriber**. A meter code with two digits swapped is not a corrupted string, it is **another subscriber's valid code**; exact matching links it without hesitation. The similarity threshold does not resolve the remaining 66 requests. Of the 18 links built at a threshold of 0.80, **17 are wrong**; tightened to 0.90, the number of links falls to 2 and **both are wrong**; loosened to 0.60, only two more links are built, while 46 requests stay ambiguous with several equally good candidates. On a short, structured identity string, similarity finds the **neighboring record** more often than the correct one. The row accounting of the step is this: **2,068 rows in, 1,880 rows out.** The 188 rows in between merged; no row was silently dropped. In the side ledger, 192 of the 240 requests were linked, **23 to the wrong subscriber**, 44 stayed unlinked, and 4 stayed ambiguous among several equal candidates. These 23 records do not move the row count at all; from here on, every request count shows up under the wrong subscriber. #### Summary - The deduplication criterion is a key string produced from the row; on the same set of 2,068 rows, three criteria leave 2,068, 2,000, and 1,880 rows. - Comparing full rows merges no row: in all 188 records that have a second entry the address is written differently, in 92% the tariff class, in 63.8% the meter code, and the reading value has never changed. The decision about how fields are compared moves 120 rows on its own. - Record linkage is a separate job: of 240 requests, 149 are linked by raw text and 174 by the simplified code, and under both decisions six go to the wrong subscriber. - The similarity threshold does not resolve the remaining 66 requests: 17 of 18 links at 0.80 and 2 of 2 links at 0.90 are wrong; at 0.60, 46 requests stay ambiguous among several equal candidates. - Row accounting of the step: 2,068 in, 1,880 out; 188 merged, 0 silently lost, and in the side ledger 23 records written to the wrong subscriber. #### Next Step The reading, index, and date fields of the remaining 1,880 rows are still text and must be converted to numbers and dates before they enter any calculation. The conversion looks harmless: it gives no warning, and no operation stops. The next lesson converts the same set with two conversion decisions and counts how many records the common call corrupts while looking error-free, and how many rows it drops without a word. ### Type Conversions Source: https://academia.sh/en/courses/data-collection-and-preparation/type-conversions In the 1,880 inherited rows, 542 of the reading fields are written with a decimal comma and 407 of the index fields with a thousands separator. The common number-conversion call produces no error and stops no operation, yet it corrupts 827 rows: 541 readings and 407 indexes turn into wrong values, the reported consumption total comes out 263.6 m³ short, and the mean index falls from 13,481 to 10,558. In the date field, a parser that does not recognize the format resolves 1,054 of the 1,880 rows correctly, writes 361 to the wrong month, and cannot resolve 465 at all, and these rows silently drop out of the date filter; the number of visits in the January period is reported as 805 instead of 1,388. A conversion that recognizes the format first gives zero corrupted records and zero lost rows on the same set. The previous lesson reduced the set to 1,880 unique rows. The reading, index, and date fields of these rows are still text and must be converted before they can enter a calculation. Conversion is the quietest step in this topic. Dropping lowers the row count, and it is visible that something has gone missing. Conversion, on the other hand, **leaves no trace**: a common number-conversion call extracts a number from every text, raises no error, stops no operation, and the resulting set looks tidy. The number of this lesson is exactly that: **how many records were corrupted while looking error-free** and **how many rows dropped out without any notice**. - **CL9.** The set is inherited from the previous lesson: 188 rows merged, 1,880 unique rows remain. Same seed, same rows; the set is **modeled** with `node` and is **fiction**. - **CL10.** The same number can be written under two separator conventions: in the reading field the decimal separator is a dot or a comma, in the index field the thousands separator is absent or a dot. **The same character does two different jobs.** - **CL11.** **Silent coercion** is a conversion that produces a wrong value without producing an error. A record is counted as corrupted when the difference between the results of the two conversions exceeds a threshold of 0.005; two floating-point values are not compared with an equality operator. - **CL12.** A date that cannot be resolved is not an error but **a row that falls out of the filter**: it produces no warning, it fails to appear in the next count. #### Converting to Numbers ```js // convert.mjs — the same raw set is a MODEL: every field is text exactly as it came from // the field form. The set is fiction: 1400 subscribers of the regional measurement network // and their field visit records; same seed, same rows. 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(""); // Decision of the previous lesson: rows with an empty identifying field were dropped, rows with // a missing reading were flagged and filled with the region median. 2068 rows enter this step. const identified = RAW.filter((s) => s.meter_id.trim() !== ""); 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)))])); export const INCOMING = identified.map((s) => toNumber(s.reading_text) === null ? { ...s, reading_text: regionMedian.get(s.region).toFixed(2) } : s); // Decision of the previous lesson: rows were deduplicated with a key produced from the // subscriber, the period, and the normalized meter code; 188 rows merged. 1880 rows enter this step. const dedupe = (l, k) => { const m = new Map(); for (const s of l) if (!m.has(k(s))) m.set(k(s), s); return [...m.values()]; }; export const DEDUPED = dedupe(INCOMING, (s) => `${s.subscriber_id}|${s.period}|${s.meter_id.trim().toUpperCase()}`); // Two conversion decisions. D1 is the common call: it produces no error and extracts a // number from every text. D2 recognizes the format first and does not turn text it does // not recognize into a number. export const D1 = { reading: (t) => parseFloat(t), index: (t) => parseFloat(t) }; export const D2 = { reading: (t) => Number(t.trim().replace(",", ".")), index: (t) => Number(t.replace(".", "")) }; export const sum = (a) => a.reduce((x, z) => x + z, 0); export const THRESHOLD = 0.005; // half of the two reported decimal places if (import.meta.url === `file://${process.argv[1]}`) { const count = (f) => DEDUPED.filter(f).length; console.log(`model (seed ${SEED}): set inherited from the previous step is ${DEDUPED.length} rows`); const fmt1 = fmt([36, 8]); console.log("\n" + fmt1(["field format", "rows"])); console.log(fmt1(["reading, decimal separator dot", count((s) => !s.reading_text.includes(","))])); console.log(fmt1(["reading, decimal separator comma", count((s) => s.reading_text.includes(","))])); console.log(fmt1(["index, no thousands separator", count((s) => !s.index_text.includes("."))])); console.log(fmt1(["index, thousands separator dot", count((s) => s.index_text.includes("."))])); const fmt2 = fmt([32, 10, 9, 13, 12]); console.log("\n" + fmt2(["numeric conversion", "corrupted", "error", "total m3", "mean index"])); for (const [name, d] of [["D1 common call", D1], ["D2 format-aware", D2]]) { const corrupted = DEDUPED.filter((s) => Math.abs(d.reading(s.reading_text) - D2.reading(s.reading_text)) > THRESHOLD || Math.abs(d.index(s.index_text) - D2.index(s.index_text)) > THRESHOLD).length; console.log(fmt2([name, corrupted, 0, sum(DEDUPED.map((s) => d.reading(s.reading_text))).toFixed(1), (sum(DEDUPED.map((s) => d.index(s.index_text))) / DEDUPED.length).toFixed(0)])); } const gap = sum(DEDUPED.map((s) => D2.reading(s.reading_text) - D1.reading(s.reading_text))); console.log(`\nD1 produced no error and stopped no operation; it corrupted ` + `${count((s) => Math.abs(D1.reading(s.reading_text) - D2.reading(s.reading_text)) > THRESHOLD)} records in the reading field and ` + `${count((s) => Math.abs(D1.index(s.index_text) - D2.index(s.index_text)) > THRESHOLD)} records in the index field. The reported consumption total ` + `comes out ${gap.toFixed(1)} m3 short (the comparison is made with a ${THRESHOLD} m3 threshold, ` + `floating-point values are not put through an equality operator)`); } ``` ``` model (seed 20260305): set inherited from the previous step is 1880 rows field format rows reading, decimal separator dot 1338 reading, decimal separator comma 542 index, no thousands separator 1473 index, thousands separator dot 407 numeric conversion corrupted error total m3 mean index D1 common call 827 0 41511.2 10558 D2 format-aware 0 0 41774.8 13481 D1 produced no error and stopped no operation; it corrupted 541 records in the reading field and 407 records in the index field. The reported consumption total comes out 263.6 m3 short (the comparison is made with a 0.005 m3 threshold, floating-point values are not put through an equality operator) ``` The first table counts how the fields were written: in 542 of the readings the decimal separator is a comma, in 407 of the indexes the thousands separator is a dot; both are mixed within a single field, depending on the habit of the person filling in the form. The second table is the number of the lesson. **D1** is the common call and **produces no error** — the error column is zero in both rows. Even so, it corrupts 827 rows. In a reading text with a comma, the call reads up to the comma and stops: the value 12,74 becomes **12**, and the decimal part is silently discarded. In an index with a thousands separator, the dot is taken for a decimal separator: the value 12.480 becomes **12.48**. The first is a small inaccuracy, the second is wrong by a **factor of a thousand**, and both happen with the same silence. The totals confirm this. The reported consumption total comes out 263.6 m³ short — a deviation nobody would notice. The mean index falls from 13,481 to **10,558**; the index is the field used to compute consumption differences, and any difference computed with a field this corrupted is meaningless. D2 gives zero corrupted records; all it does is **recognize the format** before converting. #### Converting to Dates ```js // dates.mjs — the same set, this time the date field. Two parsing decisions: one counts // the first field as the month without knowing the written format, the other recognizes // the format first. import { DEDUPED, fmt } from "./convert.mjs"; // T1: format unknown, the first field is counted as the month (a four-digit first field means year). // T2: format recognized first; in the delimited notation the fields are in day, month, year order. const T1 = (t) => { const p = t.split(/[-./]/).map(Number); const [year, month, day] = p[0] > 999 ? p : [p[2], p[0], p[1]]; return month >= 1 && month <= 12 && day >= 1 && day <= 31 ? { year, month, day } : null; }; const T2 = (t) => { const p = t.split(/[-./]/).map(Number); const [year, month, day] = /^\d{4}-/.test(t) ? p : [p[2], p[1], p[0]]; return month >= 1 && month <= 12 && day >= 1 && day <= 31 ? { year, month, day } : null; }; const two = (n) => String(n).padStart(2, "0"); const fmt1 = fmt([26, 9, 13, 11, 11]); console.log(fmt1(["date parsing", "invalid", "wrong month", "correct", "error"])); for (const [name, f] of [["T1 format unknown", T1], ["T2 format recognized", T2]]) { let invalid = 0, wrongMonth = 0, correct = 0; for (const s of DEDUPED) { const t = f(s.date_text); if (t === null) invalid++; else if (two(t.month) !== s.period.slice(5)) wrongMonth++; else correct++; } console.log(fmt1([name, invalid, wrongMonth, correct, 0])); } // A number reported from the same set: the number of visits made in the January period. const january = DEDUPED.filter((s) => s.period === "2026-01").length; const t1January = DEDUPED.filter((s) => { const t = T1(s.date_text); return t !== null && t.month === 1; }).length; const t1Valid = DEDUPED.filter((s) => T1(s.date_text) !== null).length; console.log(`\nvisits made in the January period: ${january} by the period field, ` + `${t1January} by the dates T1 resolved; T1 could not resolve ${DEDUPED.length - t1Valid} rows at all ` + `and these rows silently dropped out of the date filter`); const fmt2 = fmt([22, 10, 16]); console.log("\n" + fmt2(["date format", "rows", "T1 resolves it"])); for (const [name, f, c] of [["year-month-day", (t) => /^\d{4}-/.test(t), "yes"], ["day.month.year", (t) => t.includes("."), "if day <= 12"], ["day/month/year", (t) => t.includes("/"), "if day <= 12"]]) console.log(fmt2([name, DEDUPED.filter((s) => f(s.date_text)).length, c])); console.log(`\nrow accounting: in ${DEDUPED.length}, out ${DEDUPED.length}; 0 rows removed, ` + `0 rows silently lost. Had the common conversion been chosen, ${DEDUPED.length - t1Valid} rows ` + `would silently drop, 948 fields would be corrupted while looking error-free, and no warning would appear`); ``` ``` date parsing invalid wrong month correct error T1 format unknown 465 361 1054 0 T2 format recognized 0 0 1880 0 visits made in the January period: 1388 by the period field, 805 by the dates T1 resolved; T1 could not resolve 465 rows at all and these rows silently dropped out of the date filter date format rows T1 resolves it year-month-day 1022 yes day.month.year 389 if day <= 12 day/month/year 469 if day <= 12 row accounting: in 1880, out 1880; 0 rows removed, 0 rows silently lost. Had the common conversion been chosen, 465 rows would silently drop, 948 fields would be corrupted while looking error-free, and no warning would appear ``` The date field is a harsher form of the same problem, because here a wrong value is sometimes **no value at all**. **T1** does not know the written format and applies a common assumption: the first field is the month. Of the 1,880 rows it resolves 1,054 correctly, writes 361 to the **wrong month**, and cannot resolve 465 at all. The three outcomes behave differently. A row written to the wrong month stays in the set, carries a well-formed date, and nothing anywhere reveals that it is wrong — a visit made on February 1 appears to have been made on January 2. A row that cannot be resolved produces no error message; it **drops off the list** at the first operation that filters by date. The reported number is the sum of the three. The number of visits made in the January period is 1,388 by the period field and **805** by the dates T1 resolved. Of the 583 visits in between, some were written to the wrong month and some became invisible. The row accounting of the step is this: **1,880 rows in, 1,880 rows out.** No row was removed, no row was silently dropped, no record was corrupted. The cost of this step is the number that appears when it is not done: had the common conversion been chosen, **465 rows would silently drop and 948 fields would be corrupted while looking error-free**. #### Summary - The same field can be written under two separator conventions: in 542 of the 1,880 rows the decimal separator is a comma, in 407 the thousands separator is a dot. - The common number-conversion call produces no error and corrupts 827 rows: a reading with a comma loses its decimal part, an index with a thousands separator shrinks a thousandfold. - The effect varies by field: the consumption total comes out 263.6 m³ short, and the mean index falls from 13,481 to 10,558. - A date parser that does not recognize the format resolves 1,054 rows correctly, writes 361 to the wrong month, and cannot resolve 465 at all; the January visits are reported as 805 instead of 1,388. - Row accounting of the step: 1,880 in, 1,880 out; 0 removed, 0 silently lost, 0 records corrupted — had the common conversion been chosen, 465 rows would drop and 948 fields would be corrupted. #### Next Step All the fields are now of the right type, and all 1,880 readings are numbers. Being numbers does not mean they are correct: in some, the decimal point has shifted and the value was written ten times too large, and the conversion faithfully converted them. The next lesson scans these values with the interquartile range and the standard score, counts the part of the sets flagged by the two methods that does not overlap, and measures how much setting the flagged values aside moves the mean. ### Outliers Source: https://academia.sh/en/courses/data-collection-and-preparation/outliers On the 1,880 inherited readings the interquartile range flags 64 rows and the standard score flags 14; the second set is a subset of the first and the non-overlapping part is 50 rows. The difference comes not from the sensitivity of the methods but from the standard score hiding its own outliers: on 1,880 rows the standard deviation is 19.68 m³, it falls to 6.35 m³ once the 64 flagged rows are set aside, and the same three-sigma criterion this time flags zero rows. Of the 15 readings in the model whose decimal point shifted, the interquartile range catches 15 and the standard score catches 14. Setting the flagged rows aside pulls the mean from 22.22 m³ to 20.14 m³ (2.08 m³, 9.4%), the median from 20.31 to 19.85, and the total from 41,774.8 m³ to 36,578.3 m³. The step enters with 1,880 rows and leaves with 1,816 rows. The previous lesson converted all the fields to the right type, and all 1,880 readings are now numbers. Being a number is not being correct: in a number of readings the decimal point has shifted and the value was written ten times too large, and the conversion faithfully converted them. These values sit at the extremes of the set, violate no rule, and are strange only **relative to their neighbors**. This lesson scans those extremes with two methods. The **interquartile range** rests on ordering and draws a fence outside by a coefficient multiple of the spread; the **standard score** rests on the mean and the standard deviation. Both ask the same question and give different numbers. The number of the lesson is the **non-overlapping part** of these two sets and **how much setting aside the flagged values moves** the reported number. - **CL13.** The set is inherited from the previous two lessons: 188 rows merged, and the reading field was converted to numbers with the format-aware conversion at zero loss. The set is **modeled** with `node` and is **fiction**. - **CL14.** The interquartile range is $Q_3 - Q_1$; the fences stand at $Q_1 - k(Q_3-Q_1)$ and $Q_3 + k(Q_3-Q_1)$. The coefficient $k$ is a decision, it does not come out of the data. - **CL15.** The standard score is $(x - \mu)/\sigma$. Both of its inputs — the mean and the standard deviation — are **computed from the very values to be flagged**; the criterion is affected by what it is looking for. - **CL16.** Flagging an outlier is not a deletion decision. In this lesson the flagged rows move to a **quarantine set**; they leave the set but do not vanish, and their count is written. #### Two Methods, Two Sets ```js // outliers.mjs — the same raw set is a MODEL. The set is fiction: 1400 subscribers of the // regional measurement network and their field visit records; same seed, same rows. The // correct conversion of the previous lesson is applied and the reading field becomes a number. 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 }; // address fields are used in lesson 06 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; // The address field is not used in this lesson; only the written style is kept. const addrStyle = pick(q(), [0.30, 0.18, 0.18, 0.16, 0.18]); 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_style: addrStyle, 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_style: addrStyle === 2 ? 4 : 2, 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(""); // Decision of the first lesson: rows with an empty identifying field were dropped, rows with // a missing reading were flagged and filled with the region median. 2068 rows enter this step. const identified = RAW.filter((s) => s.meter_id.trim() !== ""); 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)))])); export const INCOMING = identified.map((s) => toNumber(s.reading_text) === null ? { ...s, reading_text: regionMedian.get(s.region).toFixed(2) } : s); // Decision of the previous two lessons: deduplication (188 rows merged) and format-aware // conversion (0 lost, 0 corrupted records). 1880 rows enter this step. const dedupe = (l, k) => { const m = new Map(); for (const s of l) if (!m.has(k(s))) m.set(k(s), s); return [...m.values()]; }; export const READINGS = dedupe(INCOMING, (s) => `${s.subscriber_id}|${s.period}|${s.meter_id.trim().toUpperCase()}`) .map((s) => ({ ...s, reading_m3: Number(s.reading_text.trim().replace(",", ".")) })); export const THRESHOLD = 0.005; // half of the two reported decimal places export const sum = (a) => a.reduce((x, z) => x + z, 0); export const mean = (a) => sum(a) / a.length; // The quartile is computed by linear interpolation on the sorted array. export const quartile = (a, p) => { const l = [...a].sort((x, z) => x - z); const h = (l.length - 1) * p, i = Math.floor(h); return l[i] + (h - i) * (l[Math.min(i + 1, l.length - 1)] - l[i]); }; export const stdDev = (a) => { const m = mean(a); return Math.sqrt(mean(a.map((v) => (v - m) ** 2))); }; if (import.meta.url === `file://${process.argv[1]}`) { const d = READINGS.map((s) => s.reading_m3); const q1 = quartile(d, 0.25), q3 = quartile(d, 0.75), iqr = q3 - q1; const lo = q1 - 1.5 * iqr, hi = q3 + 1.5 * iqr; const m = mean(d), sd = stdDev(d); console.log(`model (seed ${SEED}): set inherited from the previous steps is ${READINGS.length} rows; ` + `mean ${m.toFixed(2)} m3, standard deviation ${sd.toFixed(2)} m3`); console.log(`quartiles: Q1 ${q1.toFixed(2)}, Q3 ${q3.toFixed(2)}, ` + `interquartile range ${iqr.toFixed(2)}; fences ${lo.toFixed(2)} and ${hi.toFixed(2)} m3`); // Two methods, two flagged sets. A: interquartile range. B: standard score. const A = new Set(), B = new Set(); d.forEach((v, i) => { if (v < lo || v > hi) A.add(i); if (Math.abs((v - m) / sd) > 3) B.add(i); }); const overlap = [...A].filter((i) => B.has(i)).length; const fmt1 = fmt([34, 11, 10, 12]); console.log("\n" + fmt1(["method", "flagged", "overlap", "only this"])); console.log(fmt1(["A interquartile range (1.5x)", A.size, overlap, A.size - overlap])); console.log(fmt1(["B standard score (3 sigma)", B.size, overlap, B.size - overlap])); console.log(`the non-overlapping part of the two sets is ${A.size + B.size - 2 * overlap} rows; ` + `set B is a subset of set A`); // In the model, readings written ten times too large through a decimal shift are flagged. const shifted = READINGS.map((s, i) => [s, i]) .filter(([s]) => s.written_m3 !== null && Math.abs(s.written_m3 - s.true_m3) > THRESHOLD); console.log(`\nthe model has ${shifted.length} readings with a decimal shift; ` + `A flags ${shifted.filter(([, i]) => A.has(i)).length} of them, ` + `B flags ${shifted.filter(([, i]) => B.has(i)).length}`); } ``` ``` model (seed 20260305): set inherited from the previous steps is 1880 rows; mean 22.22 m3, standard deviation 19.68 m3 quartiles: Q1 15.38, Q3 24.59, interquartile range 9.21; fences 1.55 and 38.41 m3 method flagged overlap only this A interquartile range (1.5x) 64 14 50 B standard score (3 sigma) 14 14 0 the non-overlapping part of the two sets is 50 rows; set B is a subset of set A the model has 15 readings with a decimal shift; A flags 15 of them, B flags 14 ``` On the same set the two methods flagged 64 and 14 rows. The second set is a **subset** of the first: the overlap is 14, the non-overlapping part is **50 rows**, and all 50 of those rows are seen only by the interquartile range. One criterion sees a fifth of what the other sees. The difference does not come from sensitivity tuning. Outliers hardly move the quartiles, because even if they travel from one end of the ordering to the other, they do not change where a quartile sits. The two inputs of the standard score, by contrast, are computed directly from those values — the standard deviation of 19.68 m³ is itself a product of the readings written ten times too large. The criterion has folded what it is looking for into its own base. The last line measures this: of the **15 readings** with a shifted decimal point, the interquartile range catches 15 and the standard score catches 14. #### When the Threshold Changes ```js // threshold.mjs — the same set, two questions: as the threshold changes, how many rows each // method flags, and how much setting aside the flagged rows moves the reported number. import { READINGS, quartile, mean, stdDev, sum, fmt } from "./outliers.mjs"; const d = READINGS.map((s) => s.reading_m3); const median = (a) => quartile(a, 0.5); const iqrFlag = (a, k) => { const q1 = quartile(a, 0.25), q3 = quartile(a, 0.75); const spread = q3 - q1; return a.map((v, i) => [v, i]) .filter(([v]) => v < q1 - k * spread || v > q3 + k * spread).map(([, i]) => i); }; const zFlag = (a, k) => { const m = mean(a), s = stdDev(a); return a.map((v, i) => [v, i]).filter(([v]) => Math.abs((v - m) / s) > k).map(([, i]) => i); }; const fmt1 = fmt([24, 11, 11, 12, 13, 11]); console.log(fmt1(["method", "threshold", "flagged", "rows left", "mean m3", "median m3"])); for (const [name, f, k] of [["interquartile range", iqrFlag, 1.5], ["interquartile range", iqrFlag, 3.0], ["standard score", zFlag, 2.0], ["standard score", zFlag, 2.5], ["standard score", zFlag, 3.0]]) { const flagged = new Set(f(d, k)), kept = d.filter((_, i) => !flagged.has(i)); console.log(fmt1([name, k.toFixed(1), flagged.size, kept.length, mean(kept).toFixed(2), median(kept).toFixed(2)])); } console.log(`\nraw set mean ${mean(d).toFixed(2)} m3, median ` + `${median(d).toFixed(2)} m3; between the widest and the narrowest flagging of the two methods ` + `there is a difference of ${iqrFlag(d, 1.5).length - zFlag(d, 3.0).length} rows`); // The standard score hides its own outliers: the deviation is computed from the values to be flagged. const A = new Set(iqrFlag(d, 1.5)), clean = d.filter((_, i) => !A.has(i)); console.log(`\noutliers inflate the deviation themselves: standard deviation is ` + `${stdDev(d).toFixed(2)} m3 over ${d.length} rows; once set A is set aside it falls to ` + `${stdDev(clean).toFixed(2)} m3 over ${clean.length} rows, and the same 3 sigma criterion this time flags ` + `${zFlag(clean, 3.0).length} rows`); // Effect of setting aside on the reported number. const remaining = READINGS.filter((_, i) => !A.has(i)); console.log(`setting aside moves the mean from ${mean(d).toFixed(2)} m3 to ` + `${mean(clean).toFixed(2)} m3 (${(mean(d) - mean(clean)).toFixed(2)} m3, ` + `${(100 * (mean(d) - mean(clean)) / mean(d)).toFixed(1)}%), the median from ` + `${median(d).toFixed(2)} to ${median(clean).toFixed(2)}; the total falls from ` + `${sum(d).toFixed(1)} m3 to ${sum(clean).toFixed(1)} m3`); console.log(`\nrow accounting: in ${READINGS.length}, out ${remaining.length}; ` + `${A.size} rows were set aside into the quarantine set (not deleted, kept for review), ` + `0 rows silently lost`); ``` ``` method threshold flagged rows left mean m3 median m3 interquartile range 1.5 64 1816 20.14 19.85 interquartile range 3.0 18 1862 20.71 20.30 standard score 2.0 15 1865 20.76 20.31 standard score 2.5 14 1866 20.78 20.31 standard score 3.0 14 1866 20.78 20.31 raw set mean 22.22 m3, median 20.31 m3; between the widest and the narrowest flagging of the two methods there is a difference of 50 rows outliers inflate the deviation themselves: standard deviation is 19.68 m3 over 1880 rows; once set A is set aside it falls to 6.35 m3 over 1816 rows, and the same 3 sigma criterion this time flags 0 rows setting aside moves the mean from 22.22 m3 to 20.14 m3 (2.08 m3, 9.4%), the median from 20.31 to 19.85; the total falls from 41774.8 m3 to 36578.3 m3 row accounting: in 1880, out 1816; 64 rows were set aside into the quarantine set (not deleted, kept for review), 0 rows silently lost ``` The table shows that the threshold is a decision and that the two methods react to it differently. In the interquartile range, raising the coefficient from 1.5 to 3.0 brings the flagged rows from 64 down to 18. In the standard score, moving the threshold from 2.0 to 3.0 brings it from 15 to 14: **one row**. The criterion does not react to the threshold, because its denominator has grown so large that no reasonable threshold can divide it. The second block measures this directly. The standard deviation is 19.68 m³ on the raw set and **6.35 m³** once the 64 flagged rows are set aside, and the same three-sigma criterion this time flags **zero rows**. After doing its own work the criterion makes itself unnecessary; wherever the standard score is used in a single pass, it undercounts. The cost of setting aside is in the last line. The mean falls from 22.22 m³ to **20.14 m³**: 2.08 m³, 9.4%. The median moves only 0.46 m³ — from 20.31 to 19.85 — because the median already rests on ordering. The total falls from 41,774.8 to 36,578.3 m³, by 12.4%. The same 64 rows move the median by 2.3%, the mean by 9.4%, and the total by 12.4%; **which number is reported is also a decision**. The row accounting of the step is this: **1,880 rows in, 1,816 rows out.** The 64 rows in between were set aside into the quarantine set — not deleted, but counted and written — and no row was silently lost. Not all of those set aside are erroneous; some are genuinely high consumption, and moving them to the quarantine set makes that investigable. #### Summary - On the same set of 1,880 rows the interquartile range flags 64 rows and the standard score flags 14; the second set is a subset of the first and the non-overlapping part is 50 rows. - Both inputs of the standard score are computed from the values to be flagged, and the criterion dulls itself against what it is looking for: the standard deviation is 19.68 m³ on the raw set and 6.35 m³ once the flagged rows are set aside, and the same three-sigma criterion this time flags zero rows. - Of the 15 readings in the model whose decimal point shifted, the interquartile range catches 15 and the standard score catches 14. - Setting aside pulls the mean from 22.22 to 20.14 m³ (9.4%), the median from 20.31 to 19.85, and the total from 41,774.8 to 36,578.3 m³. - Row accounting of the step: 1,880 in, 1,816 out; 64 rows were set aside into the quarantine set, 0 silently lost. #### Next Step The numeric fields have now been scanned and their extremes written down. There is one more field in the set that has not been looked at: the tariff class. That field is not a number but hand-typed text, and the same class has been written in several forms. The next lesson counts the spellings, applies the merging steps in order, writes how many categories remain after each step, and measures how many rows merging two categories carries into the **wrong class**. ### Fixing Categorical Values Source: https://academia.sh/en/courses/data-collection-and-preparation/fixing-categorical-values The tariff field of the inherited 1,816 rows holds 15 distinct spellings. The normalization ladder brings the category count down from 15 to 14, 9, 8, and 6 in turn, and the semantic mapping from the tariff schedule brings it to 5; locale-blind lowercasing merges only five spellings and leaves an invisible mark on two. The row count never changes, but the insides of the classes do: if workplace and commercial are merged, the category count falls to 4 and 368 rows move to the wrong tariff tier, taking the commercial class from 389 to 757. A seven-entry mapping table written against locale-blind lowercasing leaves 216 unmapped rows, raises no error, and makes the groups sum to 1,600 instead of 1,816 when grouping by tariff class. The previous four lessons cleaned the numeric fields, and one field in the set has not been looked at: the tariff class. That field is not a number but text typed by hand into the field form, and the same class has been written in several forms. This lesson fixes that field and shows that two separate jobs get mixed up while fixing it. **Spelling correction** comes out of the text: the same word was written with a leading space, a capital letter, or an inner space, and these are the same category. **Semantic judgment** does not come out of the text: whether two different words point to the same class is something only the tariff schedule can say. The first is a counting job, the second is a judgment, and when it is wrong it puts **rows in the wrong bucket** without moving the row count at all. - **CL17.** The set is inherited from the previous four lessons and is 1,816 rows. The set is **modeled** with `node` and is **fiction**; the tariff classes and the schedule are fiction too. - **CL18.** Lowercasing is **locale dependent**. Locale-blind conversion leaves some letters behind with a mark invisible to the eye, and two texts that look the same remain different; this is why the ladder counts them as two separate steps. - **CL19.** **Category harmonization** removes spelling differences. Semantic mapping is a separate step and its source is the tariff schedule. - **CL20.** A spelling with no counterpart in a mapping table **raises no error**: the row becomes an **unmapped row**, is left without a class, and silently falls outside the first operation that groups by class. #### How Many Categories Remain ```js // categories.mjs — the same raw set is a MODEL. The set is fiction: 1400 subscribers of the // regional measurement network and their field visit records; same seed, same rows. The // decisions of the previous four lessons are applied and 1816 rows enter this step. 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 }; // address fields are used in lesson 06 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; // The address field is not used in this lesson; only the written style is kept. const addrStyle = pick(q(), [0.30, 0.18, 0.18, 0.16, 0.18]); 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_style: addrStyle, 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_style: addrStyle === 2 ? 4 : 2, 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(""); // Decision of the first lesson: rows with an empty identifying field were dropped, rows with // a missing reading were flagged and filled with the region median. 2068 rows enter this step. const identified = RAW.filter((s) => s.meter_id.trim() !== ""); 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)))])); export const INCOMING = identified.map((s) => toNumber(s.reading_text) === null ? { ...s, reading_text: regionMedian.get(s.region).toFixed(2) } : s); // Decision of the previous four lessons: filling the missing values, deduplication, // format-aware conversion, and setting aside the 64 rows flagged by the interquartile range. const dedupe = (l, k) => { const m = new Map(); for (const s of l) if (!m.has(k(s))) m.set(k(s), s); return [...m.values()]; }; const READINGS = dedupe(INCOMING, (s) => `${s.subscriber_id}|${s.period}|${s.meter_id.trim().toUpperCase()}`) .map((s) => ({ ...s, reading_m3: Number(s.reading_text.trim().replace(",", ".")) })); const quartile = (a, p) => { const l = [...a].sort((x, z) => x - z); const h = (l.length - 1) * p, i = Math.floor(h); return l[i] + (h - i) * (l[Math.min(i + 1, l.length - 1)] - l[i]); }; const d = READINGS.map((s) => s.reading_m3); const q1 = quartile(d, 0.25), q3 = quartile(d, 0.75), iqr = q3 - q1; export const KEPT = READINGS.filter((s) => s.reading_m3 >= q1 - 1.5 * iqr && s.reading_m3 <= q3 + 1.5 * iqr); // Normalization ladder. Each step builds on the previous one; after each step // the number of distinct categories that remain is counted. export const STEPS = [ ["N0 raw text", (t) => t], ["N1 trim", (t) => t.trim()], ["N2 lowercase (locale-blind)", (t) => t.trim().toLowerCase()], ["N3 lowercase (locale-aware)", (t) => t.trim().toLocaleLowerCase("tr")], ["N4 mark and space cleanup", (t) => t.trim().toLocaleLowerCase("tr") .replace(/\u0307/g, "").replace(/ş/g, "s").replace(/ı/g, "i").replace(/\s+/g, "")], ]; // The semantic mapping does not come out of the text; it comes from the tariff schedule. export const MAPPING = { mesken: "mesken", konut: "mesken", ticari: "ticari", isyeri: "isyeri", sanayi: "sanayi", tarimsal: "tarimsal" }; export const keyOf = STEPS[4][1]; if (import.meta.url === `file://${process.argv[1]}`) { console.log(`model (seed ${SEED}): set inherited from the previous step is ${KEPT.length} rows; ` + `${new Set(KEPT.map((s) => s.tariff_text)).size} distinct spellings occur in the tariff field`); const fmt1 = fmt([39, 12, 9]); console.log("\n" + fmt1(["normalization step", "categories", "merged"])); let before = null; for (const [name, f] of STEPS) { const k = new Set(KEPT.map((s) => f(s.tariff_text))).size; console.log(fmt1([name, k, before === null ? 0 : before - k])); before = k; } const classes = new Set(KEPT.map((s) => MAPPING[keyOf(s.tariff_text)])); console.log(fmt1(["N5 semantic mapping (tariff schedule)", classes.size, before - classes.size])); const fmt2 = fmt([12, 14, 14, 14]); console.log("\n" + fmt2(["spelling", "N2", "N3", "N4"])); for (const sp of [" Mesken ", "TİCARİ", "Ticari", "İş Yeri", "Is yeri", "isyeri"]) console.log(fmt2([JSON.stringify(sp), ...STEPS.slice(2).map(([, f]) => JSON.stringify(f(sp)))])); } ``` ``` model (seed 20260305): set inherited from the previous step is 1816 rows; 15 distinct spellings occur in the tariff field normalization step categories merged N0 raw text 15 0 N1 trim 14 1 N2 lowercase (locale-blind) 9 5 N3 lowercase (locale-aware) 8 1 N4 mark and space cleanup 6 2 N5 semantic mapping (tariff schedule) 5 1 spelling N2 N3 N4 " Mesken " "mesken" "mesken" "mesken" "TİCARİ" "ti̇cari̇" "ticari" "ticari" "Ticari" "ticari" "ticari" "ticari" "İş Yeri" "i̇ş yeri" "iş yeri" "isyeri" "Is yeri" "is yeri" "ıs yeri" "isyeri" "isyeri" "isyeri" "isyeri" "isyeri" ``` The ladder takes 15 spellings down to 5 classes in six steps, and how many spellings each step merges is written separately. The real work is done by lowercasing: **five** spellings merge. The next step merges one more spelling, which means the locale-blind conversion **had failed to resolve one spelling**. The second table shows this. When `"TİCARİ"` is converted locale-blind, the resulting text looks like `"ticari"` on the screen but is not: the dot above the letter stays behind as a separate mark, and when the two texts are compared they come out different. The pair `"Is yeri"` and `"İş Yeri"` is the same problem in reverse — locale-aware conversion turns them into `"ıs yeri"` and `"iş yeri"` and they are **still separate**; only the step that simplifies marks and spaces merges the two under `"isyeri"`. The last step has nothing to do with text: `"konut"` and `"mesken"` are two separate words and no normalization merges them; they merge because the tariff schedule says they point to the same tier. This is the only **judgment** step of the ladder. #### Wrong Merging ```js // merging.mjs — the same set, two questions: how many rows merging two categories moves to // the wrong class, and how many rows an incomplete mapping table leaves without a class. import { KEPT, MAPPING, keyOf, STEPS, fmt } from "./categories.mjs"; const distribute = (mapping, key) => { const m = new Map(); for (const s of KEPT) { const k = mapping[key(s.tariff_text)]; m.set(k, (m.get(k) ?? 0) + 1); } return m; }; const correct = distribute(MAPPING, keyOf); // In the tariff schedule workplace and commercial are separate tiers; merging the two // is not a spelling correction but a semantic decision. const WRONG = { ...MAPPING, isyeri: "ticari" }; const wrong = distribute(WRONG, keyOf); const fmt1 = fmt([12, 16, 14, 12]); console.log(fmt1(["class", "correct mapping", "isyeri>ticari", "difference"])); for (const k of [...correct.keys()].sort()) console.log(fmt1([k, correct.get(k), wrong.get(k) ?? 0, (wrong.get(k) ?? 0) - correct.get(k)])); console.log(`total ${[...correct.values()].reduce((a, b) => a + b, 0)} rows stay in place under both mappings; ` + `the category count falls from ${correct.size} to ${wrong.size} and ` + `${correct.get("isyeri")} rows move to the wrong class`); // Common mapping table: written with the spellings that pass through locale-blind lowercasing. // A spelling with no counterpart in the table raises no error, the row is left without a class. const COMMON = { mesken: "mesken", konut: "mesken", ticari: "ticari", "is yeri": "isyeri", isyeri: "isyeri", sanayi: "sanayi", tarimsal: "tarimsal" }; const blindLower = STEPS[2][1]; const unclassed = KEPT.filter((s) => COMMON[blindLower(s.tariff_text)] === undefined); const fmt2 = fmt([26, 9, 20]); console.log("\n" + fmt2(["mapping table", "entries", "rows without class"])); console.log(fmt2(["common (N2 keys)", Object.keys(COMMON).length, unclassed.length])); console.log(fmt2(["N4 keys", Object.keys(MAPPING).length, KEPT.filter((s) => MAPPING[keyOf(s.tariff_text)] === undefined).length])); console.log(`tariff spellings of the ${unclassed.length} rows left without a class: ` + [...new Set(unclassed.map((s) => s.tariff_text))].map((v) => JSON.stringify(v)).join(", ")); console.log(`grouping by tariff class silently leaves these ${unclassed.length} rows out: ` + `the groups sum to ${KEPT.length - unclassed.length}, while the set is ${KEPT.length} rows`); console.log(`\nrow accounting: in ${KEPT.length}, out ${KEPT.length}; ` + `0 rows eliminated, 0 rows silently dropped. 15 spellings merged into ${correct.size} classes; ` + `if isyeri and ticari were merged ${correct.get("isyeri")} rows would go to the wrong class, ` + `if the common mapping table were used ${unclassed.length} rows would silently drop`); ``` ``` class correct mapping isyeri>ticari difference isyeri 368 0 -368 mesken 713 713 0 sanayi 215 215 0 tarimsal 131 131 0 ticari 389 757 368 total 1816 rows stay in place under both mappings; the category count falls from 5 to 4 and 368 rows move to the wrong class mapping table entries rows without class common (N2 keys) 7 216 N4 keys 6 0 tariff spellings of the 216 rows left without a class: "TİCARİ", "İş Yeri" grouping by tariff class silently leaves these 216 rows out: the groups sum to 1600, while the set is 1816 rows row accounting: in 1816, out 1816; 0 rows eliminated, 0 rows silently dropped. 15 spellings merged into 5 classes; if isyeri and ticari were merged 368 rows would go to the wrong class, if the common mapping table were used 216 rows would silently drop ``` The first table shows the cost of a wrong judgment. A workplace subscription and a commercial one are separate tiers in the schedule, but because the two words are used interchangeably in everyday speech, merging them looks plausible. When applied, the category count falls from 5 to 4, **the row count does not change at all**, and 368 rows move to commercial: commercial goes from 389 to 757, and the workplace class disappears. Nothing is missing from the set; only every tariff breakdown from here on comes out wrong. The second table measures the silent loss. The common mapping table was written with spellings that pass through locale-blind lowercasing and holds seven entries. Two spellings — `"TİCARİ"` and `"İş Yeri"` — have no counterpart in that table; the **216 rows** that carry these spellings are **unmapped rows**. No error is raised, no operation stops. When grouped by tariff class, the groups sum to **1,600**, while the set is 1,816 rows. The 216 rows in between appear nowhere in the table. The same mapping table, written with the simplified keys, leaves **zero** rows without a class with six entries. The difference is not in the size of the table but in **which normalization it was written after**. The accounting of the step is this: **1,816 rows in, 1,816 rows out.** No row was eliminated, no row was silently dropped; 15 spellings merged into 5 classes. The cost of this step again shows up only when it is not done: if workplace and commercial were merged, 368 rows would go to the wrong class, and if the common mapping table were used, 216 rows would silently drop. **The row count is not an audit measure**; in this step the input equals the output, and the insides of the set have still changed from top to bottom. #### Summary - The tariff field holds 15 distinct spellings; the normalization ladder brings the category count down to 15, 14, 9, 8, 6, and with the semantic mapping to 5. - Locale-blind lowercasing merges five spellings but leaves an invisible mark on two. Spelling correction comes from the text, semantic mapping from the tariff schedule. - Merging workplace and commercial drops the category count to 4, does not move the row count at all, and carries 368 rows to the wrong class: commercial goes from 389 to 757. - A seven-entry mapping table written with locale-blind keys leaves 216 unmapped rows and makes the groups sum to 1,600 instead of 1,816; a six-entry table written with simplified keys drops zero rows. - Accounting of the step: 1,816 in, 1,816 out; 0 eliminated, 0 silently dropped, 15 spellings merged into 5 classes. #### Next Step The tariff field is fixed, but the same work has not been done for all the text fields of the set. The meter code was trimmed and brought to a single case in two lessons; the address field has not been touched at all, and every address in the set is written differently from its counterpart in the subscriber register. The next lesson applies the normalization ladder to the address field, counts how many records each step **rescues** in matching against the register, and measures how many records one more step makes **indistinguishable**. ### Text Normalization Source: https://academia.sh/en/courses/data-collection-and-preparation/text-normalization The address field of the inherited 1,816 records matches the 1,178 distinct addresses in the subscriber register as raw text in only 325 records. The normalization ladder raises the match in turn to 604, 604, 937, 1,490, and 1,816 records: trimming and space collapsing rescues 279, locale-aware lowercasing 333, mark simplification 553, and abbreviation expansion 326 records — while locale-blind lowercasing rescues zero. In total the match is rescued in 1,491 records. Going one step further, dropping all separators, does not raise the match at all and overlays 26 distinct addresses on 13 keys: 'No 5/2' and 'No 52' fall on the same key and 36 records can no longer be tied to a single subscriber. The step enters with 1,816 rows and leaves with 1,816 rows. The previous lesson removed the spelling differences of the tariff field and brought 15 spellings down to 5 classes. The address field, however, has not been touched at all. This lesson compares the address field with the subscriber register. The register is a separate source and holds each subscriber with a single canonical address (its **canonical form**); the address in the field form is written in five different styles. Normalization brings the two together, but every step has a cost: a difference erased from the text **does not come back**. These are the two numbers of the lesson — in how many records a step **rescues** the match, and in how many records it **erases** a real difference. - **CL21.** The set is inherited from the previous five lessons and is 1,816 rows. The subscriber register is a second source. Both are **modeled** with `node` and are **fiction**. - **CL22.** **Text normalization** means applying the same transformation to two texts to make them comparable; it has nothing to do with normalization in database design. - **CL23.** The steps build on one another and are applied to **both the record and the register entry**; a transformation applied to only one side does not rescue the match. - **CL24.** The number of records a step **rescues** and the number of differences it **erases** are measured separately. The second is counted not in the set but in the register: how many distinct canonical addresses fall on the same key. #### How Many Records the Ladder Rescues ```js // address.mjs — the same raw set and subscriber register are a MODEL. The set is fiction: // 1400 subscribers of the regional measurement network and their field visit records; same // seed, same rows. The decisions of the previous five lessons are applied and 1816 rows // enter this step. 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 = [], REGISTER = new Map(); 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 }; // Subscriber register: a single canonical address for each subscriber. It is the second source. REGISTER.set(10001 + i, `${b.name[0].toUpperCase()}${b.name.slice(1)} Mahallesi ${street} ` + `Sokak No ${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; // The address is written in five different styles in the field form; the register style is addrStyle === 1. 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(""); // Decision of the first lesson: rows with an empty identifying field were dropped, rows with // a missing reading were flagged and filled with the region median. 2068 rows enter this step. const identified = RAW.filter((s) => s.meter_id.trim() !== ""); 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)))])); export const INCOMING = identified.map((s) => toNumber(s.reading_text) === null ? { ...s, reading_text: regionMedian.get(s.region).toFixed(2) } : s); // Decision of the previous four lessons: filling the missing values, deduplication, // format-aware conversion, and setting aside the 64 rows flagged by the interquartile range. const dedupe = (l, k) => { const m = new Map(); for (const s of l) if (!m.has(k(s))) m.set(k(s), s); return [...m.values()]; }; const READINGS = dedupe(INCOMING, (s) => `${s.subscriber_id}|${s.period}|${s.meter_id.trim().toUpperCase()}`) .map((s) => ({ ...s, reading_m3: Number(s.reading_text.trim().replace(",", ".")) })); const quartile = (a, p) => { const l = [...a].sort((x, z) => x - z); const h = (l.length - 1) * p, i = Math.floor(h); return l[i] + (h - i) * (l[Math.min(i + 1, l.length - 1)] - l[i]); }; const d = READINGS.map((s) => s.reading_m3); const q1 = quartile(d, 0.25), q3 = quartile(d, 0.75), iqr = q3 - q1; export const KEPT = READINGS.filter((s) => s.reading_m3 >= q1 - 1.5 * iqr && s.reading_m3 <= q3 + 1.5 * iqr); // Text normalization ladder. Each step builds on the previous one and is applied // in the same way to both the record and the register entry. export const STEPS = [ ["N0 raw text", (t) => t], ["N1 trim and space collapse", (t) => t.trim().replace(/\s+/g, " ")], ["N2 lowercase (locale-blind)", (t) => STEPS[1][1](t).toLowerCase()], ["N3 lowercase (locale-aware)", (t) => STEPS[1][1](t).toLocaleLowerCase("tr")], ["N4 mark simplification", (t) => STEPS[3][1](t).replace(/[.:]/g, " ").replace(/\s+/g, " ").trim()], ["N5 abbreviation expansion", (t) => STEPS[4][1](t).replace(/\bmah\b/, "mahallesi") .replace(/\bsk\b/, "sokak")], ["N6 dropping all separators", (t) => STEPS[5][1](t).replace(/[^a-z0-9çğıöşü]/g, "")], ]; if (import.meta.url === `file://${process.argv[1]}`) { const register = new Set(REGISTER.values()); console.log(`model (seed ${SEED}): set inherited from the previous step is ${KEPT.length} rows, ` + `the subscriber register holds ${REGISTER.size} addresses, ${register.size} of them distinct texts`); // Spellings that correspond to the same register address: the group carrying the most styles. const buckets = new Map(); for (const s of KEPT) { const k = STEPS[6][1](s.address_text); buckets.set(k, (buckets.get(k) ?? new Set()).add(s.address_text)); } const widest = [...buckets.values()].sort((a, b) => b.size - a.size)[0]; console.log(`\nthe same address is written in ${widest.size} distinct styles in the field form:`); for (const s of widest) console.log(` ${JSON.stringify(s)}`); const fmt1 = fmt([32, 11, 12, 13]); console.log("\n" + fmt1(["normalization step", "matched", "rescued", "rate %"])); let before = null; for (const [name, f] of STEPS) { const keys = new Set([...register].map(f)); const matched = KEPT.filter((s) => keys.has(f(s.address_text))).length; console.log(fmt1([name, matched, before === null ? 0 : matched - before, (100 * matched / KEPT.length).toFixed(1)])); before = matched; } } ``` ``` model (seed 20260305): set inherited from the previous step is 1816 rows, the subscriber register holds 1400 addresses, 1178 of them distinct texts the same address is written in 4 distinct styles in the field form: "SOUTH MAHALLESİ IHLAMUR SOKAK NO 29" "south mahallesi ıhlamur sokak no: 29" " South Mahallesi Ihlamur Sokak No 29 " "south mah. ıhlamur sk. no:29" normalization step matched rescued rate % N0 raw text 325 0 17.9 N1 trim and space collapse 604 279 33.3 N2 lowercase (locale-blind) 604 0 33.3 N3 lowercase (locale-aware) 937 333 51.6 N4 mark simplification 1490 553 82.0 N5 abbreviation expansion 1816 326 100.0 N6 dropping all separators 1816 0 100.0 ``` Of the 1,816 records, only **325** match the register as raw text. The remaining 1,491 records carry a real address, belong to the right subscriber, and establish no match. The steps do not do equal work. Trimming and space collapsing rescues 279 records, locale-aware lowercasing 333, mark simplification 553, and abbreviation expansion brings the last 326 records, and the match reaches 100%. The only step that rescues zero is **locale-blind lowercasing**. When `"MAHALLESİ"` is lowercased locale-blind, the dot above the letter stays behind as a separate mark and the text does not match `"mahallesi"`; `"IŞIK"` becomes `"işik"`, whereas its locale-aware counterpart is `"ışık"`. The step rescues no record and makes the work of the one that follows it harder. #### One Step Too Many ```js // erased.mjs — the same set, the reverse question: when normalization goes one step further, // how many distinct addresses become indistinguishable and how many records are affected. import { KEPT, REGISTER, STEPS, fmt } from "./address.mjs"; const register = [...new Set(REGISTER.values())]; const fmt1 = fmt([32, 17, 14, 13]); console.log(fmt1(["normalization step", "register records", "distinct keys", "swallowed"])); for (const [name, f] of STEPS.slice(4)) { const keys = new Set(register.map(f)); console.log(fmt1([name, register.length, keys.size, register.length - keys.size])); } // Which addresses N6 drops onto the same key. const groups = new Map(); for (const k of register) { const a = STEPS[6][1](k); groups.set(a, (groups.get(a) ?? []).concat([k])); } const colliding = [...groups.entries()].filter(([, l]) => l.length > 1); const collidingKeys = new Set(colliding.map(([a]) => a)); const affected = KEPT.filter((s) => collidingKeys.has(STEPS[6][1](s.address_text))); console.log(`\nN6 overlays ${colliding.reduce((a, [, l]) => a + l.length, 0)} distinct addresses ` + `on ${colliding.length} keys; in the set, ${affected.length} records carrying these keys ` + `can no longer be tied to a single subscriber`); const fmt2 = fmt([36, 36]); console.log("\n" + fmt2(["addresses on the same key", "N6 key"])); for (const [a, l] of colliding.slice(0, 2)) { console.log(fmt2([JSON.stringify(l[0]), a])); console.log(fmt2([JSON.stringify(l[1]), ""])); } console.log(`\nthe slash in the door number is dropped at N6: "No 5/2" and "No 52" give the same ` + `key. N5 keeps ${new Set(register.map(STEPS[5][1])).size} distinct keys in the same set ` + `and the match rate is 100% in both`); const raw = KEPT.filter((s) => new Set(register).has(s.address_text)).length; console.log(`\nrow accounting: in ${KEPT.length}, out ${KEPT.length}; 0 rows eliminated, ` + `0 rows silently dropped. The ladder was stopped at N5 and the match rose from ${raw} records ` + `to ${KEPT.length} records: the match was rescued in ${KEPT.length - raw} records, ` + `0 records became indistinguishable. Had it gone one step further, the match would not have risen at all, ` + `${affected.length} records would have become indistinguishable`); ``` ``` normalization step register records distinct keys swallowed N4 mark simplification 1178 1178 0 N5 abbreviation expansion 1178 1178 0 N6 dropping all separators 1178 1165 13 N6 overlays 26 distinct addresses on 13 keys; in the set, 36 records carrying these keys can no longer be tied to a single subscriber addresses on the same key N6 key "North Mahallesi Işık Sokak No 5/2" northmahallesiışıksokakno52 "North Mahallesi Işık Sokak No 52" "East Mahallesi Işık Sokak No 14" eastmahallesiışıksokakno14 "East Mahallesi Işık Sokak No 1/4" the slash in the door number is dropped at N6: "No 5/2" and "No 52" give the same key. N5 keeps 1178 distinct keys in the same set and the match rate is 100% in both row accounting: in 1816, out 1816; 0 rows eliminated, 0 rows silently dropped. The ladder was stopped at N5 and the match rose from 325 records to 1816 records: the match was rescued in 1491 records, 0 records became indistinguishable. Had it gone one step further, the match would not have risen at all, 36 records would have become indistinguishable ``` The last step does not raise the match at all — at N5 the rate is already 100% — but it breaks something. Dropping all separators also drops the slash in the door number: `"No 5/2"` and `"No 52"` fall on the same key. The 1,178 distinct addresses in the register come down to 1,165 keys; **13 keys overlay 26 distinct addresses**. This is not a matching error but a case of **loss of distinction**. The 36 records carrying those keys still find a match, but the match they find is one where it is not clear which of two addresses it is. The row count does not change, and someone looking at the table sees a 100% match. What was erased is not a row but the **distinction**. The accounting of the step is this: **1,816 rows in, 1,816 rows out.** No row was eliminated or silently dropped; the match was rescued in 1,491 records, and zero records became indistinguishable. Stopping the ladder at N5 is a decision: going one step further adds nothing to the match and erases the distinction of 36 records. **A normalization step stops being a cleanup the moment it erases more than the matches it rescues.** #### Summary - The address field matches the register as raw text in only 325 of the 1,816 records; the normalization ladder raises the match to 604, 604, 937, 1,490, and 1,816 records. - The steps do not do equal work: trimming and space collapsing rescues 279 records, locale-aware lowercasing 333, mark simplification 553, and abbreviation expansion 326; 1,491 in total. - Locale-blind lowercasing rescues zero records: it leaves part of the letters behind with an invisible mark and turns another part into the wrong letter. - Dropping all separators does not raise the match at all and brings the 1,178 addresses in the register down to 1,165 keys: 13 keys overlay 26 addresses, and 36 records in the set can no longer be tied to a single subscriber. - Accounting of the step: 1,816 in, 1,816 out; 0 eliminated, 0 silently dropped, the match was rescued in 1,491 records, and 0 distinctions were erased. - Total of the six steps: 2,086 rows entered, 1,816 rows left; 18 eliminated, 188 merged, 64 moved to the quarantine set, and the number of silently dropped rows stayed zero. #### Next Step Throughout the topic the insides of the rows were opened and the accounting of each step was written. The set is now clean — and still in the **wrong shape**. The rows stand according to the layout of the field form: each row is a visit, each visit a period, the tariff and the region one text column each. The questions to be asked, however, are asked per subscriber, per period, and per region, and cannot be answered in this shape. The next topic takes the set out of the shape of the source and puts it into the shape of the question: it joins two sources over a key and counts how the join **multiplies** the row count. ## Data Transformation Reshaping a dataset that is organized by its source into one organized by the question, and accounting for the rows of every transformation: the records a join leaves unmatched, duplicates, or drops; whether the conversion between wide and long format is lossless; the rows that remain after aggregation and the within-group variability that disappears; the number of rows where a derived variable is undefined; the wrong result produced by encoding an unordered category as an ordered one; documenting the preparation steps with a script; and how many steps can be reproduced. ### Merging and Relating Source: https://academia.sh/en/courses/data-collection-and-preparation/merging-and-relating The cleaned 1,329 subscribers and 3,471 readings are joined on a key, and the inner join returns 3,302 rows: 169 readings drop because they cannot find a subscriber and 69 subscribers drop because they carry no readings, all without a single warning. The outer join returns 3,371 rows from the same data. When the grain of the key is chosen wrongly, the reading and visit tables shrink from 3,302 rows to 2,047, but that drop is not a filter: 1,901 readings vanish while 488 readings fan out into 1,134 rows, and total usage shifts from 65,185.6 m³ to 40,374.1 m³. The 103 rows where the same subscriber and period pair appears twice inflate the total by 2,124.9 m³. The previous topic opened up the inside of the rows, and by the end the dataset was clean: missing values were flagged, duplicates removed, types converted, categories and text corrected. Even so, the dataset is not ready for analysis, because it is still shaped **by its source, not by the question**. Subscriber information sits in one table, meter readings in another, field visits in a third; no question can be put to any one of these three tables alone. This topic reshapes the dataset around the question, and the first step is to bring the tables side by side. The measure of this step is the following: **a join changes the row count**, and the direction of the change tells you nothing by itself. The row count can drop, grow, or stay the same; each of the three can be the sign of a correct result and of a silent error alike. For that reason this lesson writes down, item by item, what went in, what came out, and where the difference went. - **TF1.** The dataset is the cleaned version that came out of the previous topic and is **fictional**: the `subscriber` table holds one subscriber per row, the `reading` table holds one period reading per row, and the `visit` table holds one field visit per row. All three tables are **modeled** with `node`; no query interface is imitated. - **TF2.** A **join** places the rows of two tables side by side on a **key**. A **union**, introduced in the Databases curriculum, stacks the rows of two tables one under the other and leaves the column layout unchanged: one adds rows, the other adds columns and changes the row count according to the number of matches on the key. The one measured in this lesson is the second. - **TF3.** An **inner join** outputs only the matching rows; unmatched rows on both sides drop. An **outer join** keeps the unmatched rows of the left table with an empty right side. The difference between the two is not a preference but a **number**. - **TF4. The grain of the key** is a decision separate from the join itself. If a key in the left table corresponds to several rows in the right table, the left row appears that many times; this is called **fan-out**. - **TF5.** A join can drop and fan out at the same time. In that case, even when the output row count is lower than the input, no filtering has taken place, and the totals have been counted both too little and too much. #### What the Inner Join Drops ```js // join.mjs — MODEL: a join over the three tables that come out of cleaning, on a key. // The dataset is fictional; no query 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; }; export const REGION = [["north", 0.28, 21], ["south", 0.22, 17], ["east", 0.18, 26], ["west", 0.14, 14], ["central", 0.18, 23]].map(([name, share, base]) => ({ name, share, base })); // Subscriber table: those dropped in cleaning stay outside, every remaining row is a subscriber. export const SUBSCRIBER = [], DROPPED = []; for (let i = 0; i < RAW; i++) { const r = rng(SEED + i), b = REGION[pick(r(), REGION.map((x) => x.share))]; const k = { subscriber_id: 10001 + i, region: b.name, base: b.base, 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); } // Reading table: some readings belong to dropped subscribers, some subscribers // carry no readings at all, some subscriber+period pairs appear twice with two different values. export const READING = []; let s = 0; 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; READING.push({ row_id: ++s, subscriber_id: k.subscriber_id, period: PERIOD[d], usage_m3: t }); if (r() < 0.028) READING.push({ row_id: ++s, subscriber_id: k.subscriber_id, period: PERIOD[d], usage_m3: Math.round(t * 1.02 * 100) / 100 }); } } // Visit table: one row is one field visit; it does not depend on the period. export const VISIT = []; for (const k of SUBSCRIBER) { const r = rng(SEED + 31000 + k.subscriber_id), n = pick(r(), [0.58, 0.27, 0.11, 0.04]); for (let z = 0; z < n; z++) VISIT.push({ subscriber_id: k.subscriber_id, result: ["positive", "repeat", "unreachable"][pick(r(), [0.60, 0.28, 0.12])] }); } // Each row of the left table is placed beside the right rows whose key is equal. // The "outer" option keeps an unmatched left row with an empty right side. export const join = (left, right, ka, kb, outer = false) => { const index = new Map(); for (const r of right) { const k = kb(r); if (!index.has(k)) index.set(k, []); index.get(k).push(r); } const out = [], matched = new Set(); let unmatchedLeft = 0; for (const l of left) { const e = index.get(ka(l)); if (!e) { unmatchedLeft++; if (outer) out.push({ left: l, right: null }); continue; } for (const r of e) { out.push({ left: l, right: r }); matched.add(r); } } return { out, unmatchedLeft, unmatchedRight: right.length - matched.size }; }; 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 id = (x) => x.subscriber_id; console.log(`model (seed ${SEED}): subscriber ${SUBSCRIBER.length} rows, reading ` + `${READING.length} rows, visit ${VISIT.length} rows`); const y = gen([34, 11, 11, 9, 12, 12]); console.log("\n" + y(["join", "left in", "right in", "out", "left miss", "right miss"])); const B = [ ["B1 reading <- subscriber (inner)", READING, SUBSCRIBER, false], ["B2 subscriber <- reading (inner)", SUBSCRIBER, READING, false], ["B3 subscriber <- reading (outer)", SUBSCRIBER, READING, true], ]; const runs = {}; for (const [name, left, right, outer] of B) { const r = join(left, right, id, id, outer); runs[name.slice(0, 2)] = r; console.log(y([name, left.length, right.length, r.out.length, r.unmatchedLeft, r.unmatchedRight])); } console.log(`\ninner join drops ${runs.B1.unmatchedLeft} of ${READING.length} readings; ` + `the same join never shows ${runs.B1.unmatchedRight} of ${SUBSCRIBER.length} subscribers ` + `in the output table`); console.log(`outer join keeps those ${runs.B3.unmatchedLeft} subscribers with an empty reading: ` + `${runs.B3.out.length} rows instead of ${runs.B2.out.length}`); console.log(`neither join produced a warning`); } ``` ``` model (seed 20260218): subscriber 1329 rows, reading 3471 rows, visit 818 rows join left in right in out left miss right miss B1 reading <- subscriber (inner) 3471 1329 3302 169 69 B2 subscriber <- reading (inner) 1329 3471 3302 69 169 B3 subscriber <- reading (outer) 1329 3471 3371 69 169 inner join drops 169 of 3471 readings; the same join never shows 69 of 1329 subscribers in the output table outer join keeps those 69 subscribers with an empty reading: 3371 rows instead of 3302 neither join produced a warning ``` Three rows read the same two tables and give three different numbers. **B1** and **B2** differ only in which table is on the left, and both output 3,302 rows: in an inner join the order of the sides does not change the output rows, it only changes in which column the unmatched ones are reported. **B3** gives 69 more rows with the same key, because it keeps the 69 subscribers that carry no readings with an empty reading. The loss is accounted for in two items. **169 readings** carry a subscriber number, but that number does not exist in the subscriber table; these are the readings of the 71 records dropped in cleaning. **69 subscribers**, on the other hand, are real subscribers whose meters were not read at all in that period, and they never appear in the output of the inner join. The second is this lesson's **silent loss**: there is no error message, not even an empty cell; the row was never produced. If the question "average usage" is put to the output of the inner join, the 69 subscribers that were never read do not take part in the answer; whether those 69 dropped at random or in a pattern cannot be read from the output at all. This is where the measure comes from: **an inner join is a filtering decision**, and it is made while preparing the table that answers a question. An outer join makes the decision readable, because the 69 empty rows are visible in the output and can be counted. If the two sides are not reported separately, this distinction stays invisible. A single sentence such as "3,302 rows were joined" hides both the 169 dropped readings and the 69 dropped subscribers at once; yet the two items are independent of each other and arose from different causes. The first is a trace left by the previous step, that is, by cleaning: the readings of the 71 dropped records were left orphaned. The second is a gap in the data itself, and cleaning could not have seen it, because that a row is missing from the reading table becomes clear only when it is compared with the subscriber table. A join report therefore carries not one number but at least four: the input rows of both sides, the output rows, and the unmatched rows on both sides. #### When the Grain of the Key Is Wrong ```js // grain.mjs — same key, wrong grain: a reading row carries a period, a visit row // does not. If the two are joined on subscriber_id alone, nothing drops and nothing fans out. import { SUBSCRIBER, READING, VISIT, join, gen } from "./join.mjs"; const id = (x) => x.subscriber_id; const inner = join(READING, SUBSCRIBER, id, id).out.map((x) => x.left); const visitCount = new Map(); for (const z of VISIT) visitCount.set(z.subscriber_id, (visitCount.get(z.subscriber_id) ?? 0) + 1); const rv = join(inner, VISIT, id, id); const dropped = inner.filter((o) => !visitCount.has(o.subscriber_id)).length; const oneToOne = inner.filter((o) => visitCount.get(o.subscriber_id) === 1).length; const fanned = inner.filter((o) => (visitCount.get(o.subscriber_id) ?? 0) > 1).length; const y = gen([40, 10, 10]); console.log(y(["reading x visit (on subscriber_id)", "reading", "rows"])); console.log(y(["in", inner.length, inner.length])); console.log(y(["no visit: dropped", dropped, 0])); console.log(y(["one visit: kept one-to-one", oneToOne, oneToOne])); console.log(y(["many visits: fanned out", fanned, rv.out.length - oneToOne])); console.log(y(["out", "", rv.out.length])); const T = (a, f) => a.reduce((x, o) => x + f(o), 0); const beforeT = T(inner, (o) => o.usage_m3); const afterT = T(rv.out, (x) => x.left.usage_m3); console.log(`\ntotal usage was ${beforeT.toFixed(1)} m3, now ${afterT.toFixed(1)} m3; ` + `it went from ${inner.length} rows down to ${rv.out.length} rows but this is not a filter`); console.log(`${dropped} readings were never counted, ${fanned} readings ` + `were counted ${rv.out.length - oneToOne} times`); // Right grain: one period of one subscriber is one row. If the same pair appears twice, // cleaning did not treat it as an exact match, because the two reading values differ. const unique = new Map(); for (const o of inner) { const k = `${o.subscriber_id}|${o.period}`; if (!unique.has(k)) unique.set(k, o); } const uniqueT = T([...unique.values()], (o) => o.usage_m3); console.log(`\nsubscriber+period pair: ${inner.length} rows hold ${unique.size} unique pairs; ` + `${inner.length - unique.size} rows count the same period a second time`); console.log(`total ${beforeT.toFixed(1)} m3 instead of ${uniqueT.toFixed(1)} m3; ` + `difference ${(beforeT - uniqueT).toFixed(1)} m3`); console.log(`in 3471 readings -> out ${unique.size} rows: 169 unmatched, ` + `${inner.length - unique.size} duplicate pairs, 69 subscribers never shown`); ``` ``` reading x visit (on subscriber_id) reading rows in 3302 3302 no visit: dropped 1901 0 one visit: kept one-to-one 913 913 many visits: fanned out 488 1134 out 2047 total usage was 65185.6 m3, now 40374.1 m3; it went from 3302 rows down to 2047 rows but this is not a filter 1901 readings were never counted, 488 readings were counted 1134 times subscriber+period pair: 3302 rows hold 3199 unique pairs; 103 rows count the same period a second time total 65185.6 m3 instead of 63060.6 m3; difference 2124.9 m3 in 3471 readings -> out 3199 rows: 169 unmatched, 103 duplicate pairs, 69 subscribers never shown ``` This table is the real finding of the lesson. The reading table carries a **subscriber and a period**, while the visit table carries only a **subscriber**. If the two are joined on `subscriber_id` alone, the grain of the key does not match: three readings of a subscriber produce six rows with that subscriber's two visits. The resulting row count is **lower** than the input, from 3,302 to 2,047, and looked at by itself this resembles a filter. It is not. Two opposing things happened within the same operation: the **1,901 readings** of subscribers with no visit at all **dropped**, and the **488 readings** of subscribers with several visits **fanned out into 1,134 rows**. Because the net change is negative, the fan-out is invisible in the count. Total usage falls from 65,185.6 m³ to 40,374.1 m³; this is a number that could be read as "usage went down", and not a single reading has changed. The second measurement shows that grain must be asked about even with the right key. The 3,302 rows the inner join returned contain only **3,199 unique subscriber and period pairs**; 103 rows count the same period a second time. These did not fall under cleaning's exact-match rule: in 101 of the 103 pairs the usage values of the two records differ from each other, and in the remaining two the row numbers differ. The effect on the total is 2,124.9 m³, that is, more than 3% of the total. The closing account of the lesson is on the last line: 3,471 readings went in, 3,199 rows came out; 169 records did not match, 103 records were dropped because they counted the same period a second time, and 69 subscribers never appeared in the output. When all three items are written down, a join is a preparation step; when they are not, it is merely a change. #### Summary - A join places rows side by side on a key and adds columns; a union stacks rows one under the other. In a join the row count changes according to the number of matches on the key. - The inner join outputs 3,302 rows from 3,471 readings and 1,329 subscribers: 169 readings did not match and 69 subscribers never appeared in the output. The outer join keeps those 69 subscribers and returns 3,371 rows. - The silent dropping of the 69 subscribers that were never read produces no error, and the question "average usage" is answered without those 69 subscribers. - When the grain of the key does not match, one join drops and fans out at the same time: 1,901 readings are never counted, 488 readings are counted 1,134 times, and because the output has fewer rows than the input the fan-out stays invisible. - Even with the right key, grain must be asked about: the 3,302 rows hold 3,199 unique subscriber and period pairs, and the 103 repeats inflate the total by 2,124.9 m³. #### Next Step The tables are now side by side, and what we hold is a single dataset in which one row carries one subscriber's reading for one period. This shape does not suit every question. The question "the change between a subscriber's three periods" requires comparing three rows, yet the same information can sit in one row in a shape that turns each period into a column. Moving back and forth between the two shapes looks like a purely formal operation, but information can be lost in one direction, and the loss is noticed only on the way back. The next lesson defines these two shapes and measures whether the conversion is lossless, that is, whether the original dataset is reached again when we convert back, by counting. ### Reshaping Source: https://academia.sh/en/courses/data-collection-and-preparation/reshaping A long-format dataset of 3,199 rows is converted to a wide format of 1,260 rows and three period columns; 3,199 of the 3,780 cells are filled and 581 stay empty. A dataset of 3,302 rows that carries repeated subscriber and period pairs does not fit the same wide format, and 103 rows are overwritten without a warning. The three choices on the way back give three different row counts: skipping empty cells gives 3,199 rows and a mean of 19.713 m³, filling empty cells with zero gives 3,780 rows and 16.683 m³, and skipping through a truthiness test also deletes 119 genuine zero readings, leaving 3,080 rows and 20.474 m³. In all three paths the total stays fixed at 63,060.6 m³. The previous lesson gathered three tables into one dataset, and what remained was 3,199 rows, each carrying one subscriber's reading for one period. This shape answers some questions directly: "what is the total usage" is the sum of a single column. It does not answer others. The question "how much did a subscriber's usage change between January and March" requires finding two separate rows of the same subscriber and placing them side by side, and that cannot be done directly on a row-based dataset. This lesson changes the **shape** of the dataset. A change of shape is not a calculation; no value is summed, divided, or filtered out. Even so, the row count changes, and in some cases values disappear too. The measure of the lesson is this: is the conversion **lossless**, that is, when the data is converted to wide format and back, do we arrive at the original dataset again? - **TF6. Long format** makes every measurement a row: key columns (subscriber and period), a variable name, and a value. **Wide format** moves part of the key into the column header: one row is one subscriber, and three columns are three periods. The dataset is the same dataset; the row count is not. - **TF7.** In wide format the row count equals the number of **unique keys**, and the cell count equals the product of rows and columns. Every cell of the product that has no counterpart in long format stays **empty**. - **TF8.** Wide format carries a single cell for the same key pair. If a subscriber and period pair appears twice in long format, the second value **overwrites** the first and no warning is produced. - **TF9.** On the way back, the **empty-cell decision** is as decisive as the conversion itself: empty cells can be skipped, filled with a value, or, through a wrong test, can take genuine values down with them. - **TF10.** Losslessness is something that can be measured: the dataset that comes back is compared with the original in row count, key set, and total. If all three are the same, the conversion is lossless. #### The Same Dataset, Two Shapes ```js // shape.mjs — MODEL: the long-format dataset produced by the previous lesson and the wide // format of the same dataset. 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]]; 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 that passed the previous lesson's inner join: the 169 unmatched readings are gone, // the rows that carry the same subscriber+period pair a second time are still inside. export const DUPLICATED = []; 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; } DUPLICATED.push({ subscriber_id: k.subscriber_id, region: k.region, period: PERIOD[d], usage_m3: t }); if (r() < 0.028) DUPLICATED.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: one subscriber+period pair is one row. const first = new Map(); for (const o of DUPLICATED) { const a = `${o.subscriber_id}|${o.period}`; if (!first.has(a)) first.set(a, o); } export const LONG = [...first.values()]; // Widening: part of the key moves into the column header. A value written to the same // cell a second time overwrites the first; the operation produces no error. export const widen = (long) => { const rows = new Map(); let written = 0, overwritten = 0; for (const o of long) { if (!rows.has(o.subscriber_id)) rows.set(o.subscriber_id, { subscriber_id: o.subscriber_id, region: o.region, ...Object.fromEntries(PERIOD.map((d) => [d, null])) }); const s = rows.get(o.subscriber_id); if (s[o.period] !== null) overwritten++; else written++; s[o.period] = o.usage_m3; } return { wide: [...rows.values()], written, overwritten }; }; 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 G = widen(LONG), Y = widen(DUPLICATED); const cells = G.wide.length * PERIOD.length; console.log(`model (seed ${SEED}): long format ${LONG.length} rows, ` + `${G.wide.length} unique subscribers, ${PERIOD.length} periods`); const y = gen([14, 10, 12, 12, 12]); console.log("\nwide format (first five rows)"); console.log(y(["subscriber_id", "region", ...PERIOD])); for (const s of G.wide.slice(0, 5)) console.log(y([s.subscriber_id, s.region, ...PERIOD.map((d) => s[d] === null ? "-" : s[d])])); console.log(`\n${G.wide.length} rows x ${PERIOD.length} columns = ${cells} cells; ` + `${G.written} filled, ${cells - G.written} empty`); console.log(`the shape of the input changed, row count ${LONG.length} -> ${G.wide.length}`); console.log(`\nif the duplicated dataset is widened: in ${DUPLICATED.length} rows, ` + `out ${Y.wide.length} rows, filled cells ${Y.written}`); console.log(`${Y.overwritten} rows were written to the same cell a second time and overwrote the ` + `first; no warning was produced`); } ``` ``` model (seed 20260218): long format 3199 rows, 1260 unique subscribers, 3 periods wide format (first five rows) subscriber_id region 2026-01 2026-02 2026-03 10001 central 30.09 24.65 - 10002 north 20.21 22.14 21.57 10003 south 13.42 14.12 18.75 10004 north 20.01 20.29 17.48 10005 south 18.29 - - 1260 rows x 3 columns = 3780 cells; 3199 filled, 581 empty the shape of the input changed, row count 3199 -> 1260 if the duplicated dataset is widened: in 3302 rows, out 1260 rows, filled cells 3199 103 rows were written to the same cell a second time and overwrote the first; no warning was produced ``` Wide format and long format carry the same 3,199 measurements. The difference is where a measurement is read from: in long format the period is a **value**, in wide format it is a **column name**. The question "the change between January and March" is the difference of two columns in a single row in wide format; in long format it requires matching two rows. The price of the shape is 581 empty cells. These are not a deficiency but combinations that never existed in long format: not all of the 1,260 subscribers were read in all three periods. Wide format makes a missing row visible by turning it into an **empty cell**; in long format that combination did not exist at all and could not be counted. This is a real gain of wide format: a gap becomes countable only once the set of expected combinations has been built. Neither of the two formats is the correct one; the question decides which is suitable. Wide format has a limit, and it has nothing to do with the row count: the number of columns equals the number of **unique values** of the key that moved into the header. Three periods make three columns, thirty-six periods make thirty-six columns, and if the number of periods is not known in advance, the schema of the table changes together with the data. In long format the same growth produces not new columns but new rows, and the schema stays fixed. For this reason the output of preparation is mostly stored in long format, and wide format is produced only for a specific question, temporarily. The last two lines show the silent loss of the lesson. If the duplicates that the previous lesson removed had not been removed, there would be 3,302 rows, and when that dataset was converted to the same wide format it would still produce 1,260 rows and 3,199 filled cells. **103 rows were written to the same cell a second time and overwrote the first.** The operation completed successfully, no error was produced, and the drop in row count was a drop that was expected anyway. Converting to wide format can therefore be used as a deduplication tool; when it is used that way, nobody has chosen which value stays. #### Is the Way Back Lossless ```js // round-trip.mjs — three decisions for converting from wide format back to long format and // the difference of each from the original dataset. Losslessness is measured by rows, keys, and total. import { LONG, PERIOD, widen, gen } from "./shape.mjs"; const { wide } = widen(LONG); // Y1 skips empty cells. Y2 fills empty cells with zero. Y3 tests a cell for "truthiness"; // this test counts zero as empty too. const PATH = [ ["Y1 empty cells are skipped", (v) => v !== null], ["Y2 empty cells are filled with zero", () => true], ["Y3 cell passes the truthiness test", (v) => Boolean(v)], ]; const lengthen = (keep) => { const out = []; for (const s of wide) for (const d of PERIOD) { if (!keep(s[d])) continue; out.push({ subscriber_id: s.subscriber_id, period: d, usage_m3: s[d] ?? 0 }); } return out; }; const T = (a) => a.reduce((x, o) => x + o.usage_m3, 0); const originalT = T(LONG), zeros = LONG.filter((o) => o.usage_m3 === 0).length; console.log(`original long format: ${LONG.length} rows, ${originalT.toFixed(1)} m3, ` + `containing ${zeros} genuine zero readings`); const y = gen([38, 9, 8, 11, 10]); console.log("\n" + y(["convert-back decision", "rows", "diff", "total m3", "mean"])); for (const [name, keep] of PATH) { const g = lengthen(keep), f = g.length - LONG.length; console.log(y([name, g.length, f > 0 ? `+${f}` : f, T(g).toFixed(1), (T(g) / g.length).toFixed(3)])); } // Losslessness test: are row count, key set, and total the same? Because the total is // floating point, a threshold is used instead of equality; 0.05 m3 was chosen // because readings are recorded to 0.01 m3 precision and the total has 3199 terms. const LIMIT_M3 = 0.05; const keySet = (a) => new Set(a.map((o) => `${o.subscriber_id}|${o.period}`)); const originalK = keySet(LONG); console.log("\n" + gen([38, 9, 10, 10, 10])(["lossless test", "rows", "keys", "total", "lossless"])); for (const [name, keep] of PATH) { const g = lengthen(keep), a = keySet(g); const s1 = g.length === LONG.length; const s2 = a.size === originalK.size && [...originalK].every((k) => a.has(k)); const s3 = Math.abs(T(g) - originalT) < LIMIT_M3; console.log(gen([38, 9, 10, 10, 10])([name, s1 ? "same" : "different", s2 ? "same" : "different", s3 ? "same" : "different", s1 && s2 && s3 ? "yes" : "no"])); } console.log(`\nin all three paths the total stayed at ${originalT.toFixed(1)} m3; ` + `the mean moved between 16.683 and 20.474 m3`); ``` ``` original long format: 3199 rows, 63060.6 m3, containing 119 genuine zero readings convert-back decision rows diff total m3 mean Y1 empty cells are skipped 3199 0 63060.6 19.713 Y2 empty cells are filled with zero 3780 +581 63060.6 16.683 Y3 cell passes the truthiness test 3080 -119 63060.6 20.474 lossless test rows keys total lossless Y1 empty cells are skipped same same same yes Y2 empty cells are filled with zero different different same no Y3 cell passes the truthiness test different different same no in all three paths the total stayed at 63060.6 m3; the mean moved between 16.683 and 20.474 m3 ``` Only **Y1** is lossless: its row count, key set, and total are all the same as the original dataset's. The other two paths also run without error, and both give the same total. **Y2** fills the empty cell with zero and **invents** 581 rows. These rows are not wrong; the assumption is wrong: a period that was not read is not a period in which zero was consumed. The key set contains all of the original but has 581 extra, so it fails the test. Because the total does not change, the error does not show in the total; it shows in the mean, which falls from 19.713 m³ to 16.683 m³. The figure has moved by more than 15%, and not a single reading has changed. **Y3** is the most insidious decision in the lesson, and it comes from a coding habit: whether a cell is filled has been asked by checking whether its value passes a truthiness test. Zero does not pass this test. The result is that **119 genuine zero readings** are deleted together with the empty cells. The key set breaks here in the opposite direction: the set that comes back is **missing** 119 keys of the original. The total again does not change, because the deleted readings have a value of zero; the mean, however, moves the other way, up to 20.474 m³. The three paths have the same total and different means. This shows why losslessness cannot be tested with the total: adding or deleting rows whose value is zero does not move the total at all. A losslessness test therefore requires three separate comparisons: **row count, key set, and total**. Because the total comparison is floating point, it is made not with equality but with a threshold of 0.05 m³; the threshold was chosen from the 0.01 m³ precision of the readings and from the fact that the total has more than three thousand terms. #### Summary - Long format makes every measurement a row, and wide format moves part of the key into the column header: the same dataset moves between 3,199 rows and 1,260 rows without a single value changing. - In wide format, 1,260 rows and three columns produce 3,780 cells; 3,199 are filled and 581 stay empty, and these empty cells make a gap countable for the first time. - A dataset of 3,302 rows that carries repeated subscriber and period pairs does not fit the same wide format: 103 rows are overwritten without any warning, and nobody has chosen which value stays. - The three decisions on the way back give three row counts: 3,199, 3,780, and 3,080. Only the first is lossless. - Filling empty cells with zero lowers the mean from 19.713 to 16.683 m³; the truthiness test deletes 119 genuine zero readings and raises the mean to 20.474 m³. In all three paths the total is 63,060.6 m³. #### Next Step Reshaping did not sum a single measurement; when 3,199 rows fell to 1,260 rows, every value was still in its own cell. The number that goes into a report, however, is rarely a cell. The question "average usage per region" asks to reduce thousands of rows to five rows, and that reduction cannot be undone: someone who looks at five rows cannot see the distribution behind them, only the number the summary left behind. The next lesson counts the rows that grouping produces and measures how much of the variability within each group disappears together with the summary. ### Grouping and Aggregation Source: https://academia.sh/en/courses/data-collection-and-preparation/grouping-and-aggregation The dataset of 3,199 rows shrinks to 5, 15, 53, and 1,260 rows under four grouping decisions; of the 60 cells of the region, tariff tier, and period cross-break, 7 carry no rows and never appear in the output. Three overall means come out of the same dataset: 19.713 m³ from 3,199 rows, 19.532 m³ from five region rows, and 20.721 m³ from 53 cells. The five-row summary does not carry the fact that 431 of the 3,199 readings deviate from their own group mean by more than 50%, that 58 readings exceed 40 m³, and that the east and west regions swap places in 50 readings. 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 ```js // 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 ```js // 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. ### Derived Variables Source: https://academia.sh/en/courses/data-collection-and-preparation/derived-variables Two derived variables are added to the dataset of 3,199 rows, and neither changes the row count; 3,199 rows come out. Per-person usage stays undefined in 328 rows, 216 where the household is zero and 112 where the household information is missing altogether, and because both causes merge into the same result they cannot be told apart in the output. Four defensible paths to the same question give four answers: a not-a-number value, 9.972, 8.604, and 7.735 m³. The path that filters out the undefined rows leaves 6,372.0 m³, that is 10.1% of total usage, outside the calculation, and the filter does not fall evenly across regions: the share varies between 5.4% and 21.2%. The period change rate is undefined in 1,334 rows in the long format, while in the expected set of cells 581 cells cannot be counted at all. 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 ```js // 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 ```js // 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. ### Categorical Encoding Source: https://academia.sh/en/courses/data-collection-and-preparation/categorical-encoding Three categorical fields of the dataset of 3,199 rows are encoded: ordered encoding adds 3 columns, unordered encoding adds 14 columns, and neither drops a single row. When region, an unordered field, is encoded in order, the code comes from an order that does not exist in the data: three defensible orderings apply the same threshold to the same data and select 2,166, 1,678, and 1,633 rows, with means of 19.741, 18.480, and 20.661 m³. None of the 3,199 rows is selected in all three decisions, and 921 rows are selected in only one. In an ordered field the same experiment gives a single answer: two different encodings yield the same 1,615 rows and the same 19.584 m³ at the satisfaction threshold. The derivation produced numeric fields from numeric fields, and the fields that carry no number were left untouched in the dataset. `region` is a name, `satisfaction` is a grade, `tariff_tier` is a tier. The way to calculate on them goes through **categorical encoding**: a number is assigned to each category. The operation looks mechanical and does not touch the row count, but the number given carries a claim with it. When one category is given 3 and another 5, it is said that the second is "greater" than the first and that the distance between them is 2. The Measurement Scales lesson in the Introduction to Data Analytics course measured in which fields this claim is legitimate; this lesson does not repeat the scale definitions, it counts **how many rows the claim moves in the preparation step**. - **TF21. Categorical encoding** is the decision that turns a category into a number. It does not change the row count, it adds columns. The dataset is carried over from the previous lesson and is **fictional**. - **TF22. Ordered encoding** gives each category an integer and produces a single column. **Unordered encoding** opens a separate 0/1 column for each category, produces as many columns as there are categories, and claims no order. - **TF23.** Encoding an unordered field in order adds to the data an order that does not exist in it. That order is **arbitrary**: there are several defensible orderings over the same field, and which one was chosen is not visible in the encoded column. - **TF24.** In an ordered field, every increasing encoding carries the same order information; the result of a threshold defined by category is independent of the encoding. In an unordered field, the result of the same threshold is determined entirely by the encoding. #### Two Encodings, the Same Row Count ```js // encode.mjs — MODEL: the 3,199-row dataset of the previous lessons and the encoding of // categorical fields. The dataset is fictional; no interface is imitated. export const SEED = 20260218, RAW = 1400, PERIOD = ["2026-01", "2026-02", "2026-03"]; 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); } // The dataset produced by the previous lessons; the draws of dropped subscribers and of the // deduplicated second record are preserved so that the usage values stay the same. const SATISFACTION = new Map(SUBSCRIBER.map((a) => [a.subscriber_id, a.satisfaction])); const keys = new Set(SUBSCRIBER.map((a) => a.subscriber_id)); 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, satisfaction: SATISFACTION.get(k.subscriber_id), tier: t < TARIFF[0] ? 1 : t < TARIFF[1] ? 2 : t < TARIFF[2] ? 3 : 4 }); r(); } } export const FIELD = [ ["region", "unordered", (o) => o.region], ["satisfaction", "ordered", (o) => o.satisfaction], ["tariff_tier", "ordered", (o) => o.tier], ]; // Ordered encoding gives each category an integer: a single column. Unordered encoding // opens a separate 0/1 column for each category and claims no order. export const codeMap = (d) => Object.fromEntries(d.map((a, j) => [a, j + 1])); export const indicator = (o, f, d) => d.map((a) => (f(o) === a ? 1 : 0)); // In an unordered field the order does not come from the field itself; an ordering is chosen. const NAMES = [...new Set(LONG.map((o) => o.region))]; const freq = (a) => LONG.filter((o) => o.region === a).length; export const ORDERING = [ ["S1 register order", ["west", "east", "south", "north", "central"]], ["S2 first seen in the dataset", [...NAMES]], ["S3 by row frequency, descending", [...NAMES].sort((a, b) => freq(b) - freq(a))], ]; 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 T = LONG.reduce((a, o) => a + o.usage_m3, 0); console.log(`model (seed ${SEED}): in ${LONG.length} rows, ${T.toFixed(1)} m3, ` + `${FIELD.length} categorical fields — same as the previous lesson`); const y = gen([18, 10, 12, 14, 11, 8, 8]); console.log("\n" + y(["field", "scale", "categories", "ordered code", "indicator", "in", "out"])); let s1 = 0, s2 = 0; for (const [name, scale, f] of FIELD) { const d = [...new Set(LONG.map(f))]; s1 += 1; s2 += d.length; console.log(y([name, scale, d.length, 1, d.length, LONG.length, LONG.length])); } console.log(`\nencoding produces no rows: ${LONG.length} -> ${LONG.length}. Ordered encoding adds ` + `${s1} columns, unordered encoding adds ${s2} columns; neither drops any row`); const o1 = LONG[0], d1 = [...new Set(LONG.map((o) => o.region))]; console.log(`example row ${o1.subscriber_id} ${o1.region}: indicator columns ` + `[${indicator(o1, (o) => o.region, d1).join(" ")}] (column order ${d1.join(" ")})`); console.log(`\nthree defensible orderings for region and the codes they give:`); const y2 = gen([32, 44]); for (const [name, d] of ORDERING) { const k = codeMap(d); console.log(y2([name, d.map((a) => `${a}=${k[a]}`).join(" ")])); } console.log(`none of the three was read from the data; there is no order among region names ` + `and which ordering was chosen is not visible at all in the encoded column`); } ``` ``` model (seed 20260218): in 3199 rows, 63060.6 m3, 3 categorical fields — same as the previous lesson field scale categories ordered code indicator in out region unordered 5 1 5 3199 3199 satisfaction ordered 5 1 5 3199 3199 tariff_tier ordered 4 1 4 3199 3199 encoding produces no rows: 3199 -> 3199. Ordered encoding adds 3 columns, unordered encoding adds 14 columns; neither drops any row example row 10001 central: indicator columns [1 0 0 0 0] (column order central north south west east) three defensible orderings for region and the codes they give: S1 register order west=1 east=2 south=3 north=4 central=5 S2 first seen in the dataset central=1 north=2 south=3 west=4 east=5 S3 by row frequency, descending north=1 south=2 central=3 east=4 west=5 none of the three was read from the data; there is no order among region names and which ordering was chosen is not visible at all in the encoded column ``` Encoding is the second step in this course that leaves the row count unchanged: the in and out columns are 3,199 in all three fields. What changes is the column count. Ordered encoding produces a single column per field and comes to **3 columns** for three fields; unordered encoding opens a column for each category and comes to **14 columns** for the same three fields. That is the cost of unordered encoding and it grows with the number of categories; its gain is that it puts no category before or after another. The example row makes this concrete: a row in the `central` region sets only one of the five indicators to 1, and the remaining four are 0. In these five numbers no region is greater than another. In ordered encoding, on the other hand, the same information comes down to a single number, and that number settles somewhere. The lower block opens the core question of the lesson: **where will the code of the `region` field come from**. The field itself has no order, so an ordering is **chosen**, and all three choices are defensible: following the fixed order of the region register, keeping the order in which they are first seen in the dataset, and arranging them in descending order of row frequency. The three give three different codes. `west` is 1 in register order and 5 in frequency order. Someone looking at the encoded column sees only a number; which ordering was chosen leaves no trace in that column. There is no such choice in the `satisfaction` and `tariff_tier` fields. The order among the tiers comes from the field itself, and the person encoding cannot change it, only choose by which numbers it will be represented. The difference is here, and the next section measures it with row counts. #### The Same Threshold, Three Different Sets of Rows ```js // threshold.mjs — the same threshold selects three different sets of rows under three // orderings. In an ordered field the same experiment gives a single set; the difference // comes not from the encoding but from the scale. import { LONG, ORDERING, codeMap, mean, gen } from "./encode.mjs"; const LIMIT = 3; // the upper half of five categories; codes 3, 4, and 5 are selected const y = gen([32, 25, 9, 13]); console.log(`threshold: "rows whose region code is ${LIMIT} and above"`); console.log("\n" + y(["ordering", "selected regions", "rows", "mean m3"])); const sets = []; for (const [name, d] of ORDERING) { const k = codeMap(d), picked = LONG.filter((o) => k[o.region] >= LIMIT); sets.push(new Set(picked.map((o) => `${o.subscriber_id}|${o.period}`))); console.log(y([name, d.filter((a) => k[a] >= LIMIT).join(" "), picked.length, mean(picked.map((o) => o.usage_m3)).toFixed(3)])); } const inHowMany = LONG.map((o) => sets.filter((s) => s.has(`${o.subscriber_id}|${o.period}`)).length); const count = (n) => inHowMany.filter((x) => x === n).length; console.log(`\nof ${LONG.length} rows, ${count(3)} were selected in all three decisions, ` + `${count(2)} in two, ${count(1)} in only one, ${count(0)} in none`); const n = sets.map((s) => s.size); const m = sets.map((s) => mean(LONG.filter((o) => s.has(`${o.subscriber_id}|${o.period}`)) .map((o) => o.usage_m3))); console.log(`same data, same threshold, same calculation: between ${Math.min(...n)} and ${Math.max(...n)} rows ` + `and between ${Math.min(...m).toFixed(3)} and ${Math.max(...m).toFixed(3)} m3`); // In an ordered field the threshold is defined by category: "good and above". Every // increasing encoding puts the same category on the same side, because the order is in the field itself. const GRADE = ["very bad", "bad", "fair", "good", "very good"]; const CODE = [["K1 equal intervals", [1, 2, 3, 4, 5]], ["K2 widening intervals", [2, 5, 9, 14, 20]]]; const y2 = gen([26, 22, 15, 9, 13]); console.log(`\nthreshold: "rows whose satisfaction is ${GRADE[3]} and above"`); console.log("\n" + y2(["encoding", "codes", "threshold code", "rows", "mean m3"])); for (const [name, K] of CODE) { const e = K[GRADE.indexOf("good")], picked = LONG.filter((o) => K[o.satisfaction - 1] >= e); console.log(y2([name, K.join(" "), e, picked.length, mean(picked.map((o) => o.usage_m3)).toFixed(3)])); } console.log(`both encodings selected the same rows: because the order is in the field and not ` + `in the code, the threshold is independent of the encoding`); ``` ``` threshold: "rows whose region code is 3 and above" ordering selected regions rows mean m3 S1 register order south north central 2166 19.741 S2 first seen in the dataset south west east 1678 18.480 S3 by row frequency, descending central east west 1633 20.661 of 3199 rows, 0 were selected in all three decisions, 2278 in two, 921 in only one, 0 in none same data, same threshold, same calculation: between 1633 and 2166 rows and between 18.480 and 20.661 m3 threshold: "rows whose satisfaction is good and above" encoding codes threshold code rows mean m3 K1 equal intervals 1 2 3 4 5 4 1615 19.584 K2 widening intervals 2 5 9 14 20 14 1615 19.584 both encodings selected the same rows: because the order is in the field and not in the code, the threshold is independent of the encoding ``` The upper table is the finding of the lesson. The threshold is a single sentence and looks reasonable: "rows whose region code is 3 and above", that is, the upper half of five categories. The same sentence, on the same data, selects three different sets of regions under three defensible orderings. The numbers of selected rows are **2,166, 1,678, and 1,633**; the mean usage ranges from 18.480 m³ to 20.661 m³. All three numbers were calculated correctly, and none of them raises an error. The line below gives the measure of the matter: **none** of the 3,199 rows is selected in all three decisions. 2,278 rows are selected in two decisions, and **921 rows in only one decision**. That is, the expression "the regions whose code is 3 and above" has no fixed meaning; each ordering selects a different three, and the three selections do not intersect. This is the exact definition of the wrong result that encoding an unordered field in order produces: the result is not a wrongly calculated number but **a number that does not belong to the data**. The source of the number is how the region names were lined up. The second table repeats the same experiment on an ordered field, and the difference is clear. The threshold is again a single sentence: "rows whose satisfaction is good and above". The distances between the two encodings are entirely different, since one counts the tiers as equally spaced and the other widens the gaps among the upper tiers, but both select the same **1,615 rows** and give the same 19.584 m³. The reason is simple: the order is in the field itself, the job of the code is to carry it without breaking it, and every increasing encoding does so. Put side by side, the two tables give a decision rule. If a field is ordered, ordered encoding is legitimate, and the choice of code does not move thresholds based on categories. If a field is unordered, ordered encoding **makes the decision unreadable from the code**: the threshold has been applied not to the data but to the encoding order. Unordered encoding closes this trap, because in indicator columns there is no such thing as "3 and above"; which categories were selected has to be written **by name**, and at that moment the choice becomes visible. The row accounting of the lesson closes this way: 3,199 rows went in, 3,199 rows came out, and encoding dropped not a single row. In return, a single threshold that came after the encoding selected sets of rows from the same dataset that vary between 1,633 and 2,166, and whether 921 rows were counted or not depended on a single ordering decision. **Encoding itself is lossless; the loss appears at the first step where the false order carried by the code is used.** #### Summary - Encoding does not change the row count: 3,199 rows went in, 3,199 rows came out. Ordered encoding adds 3 columns for three fields, and unordered encoding adds 14 columns for the same three fields. - In an unordered field the code comes not from the data but from a chosen ordering: the register, first-seen, and frequency orderings give the `west` region different codes between 1 and 5. - The same threshold selects 2,166, 1,678, and 1,633 rows under three orderings; the mean varies between 18.480 and 20.661 m³ and no calculation raises an error. - None of the 3,199 rows is selected in all three decisions; whether 921 rows are counted depends on a single ordering decision. - In an ordered field the choice of code does not move the threshold: two different encodings give the same 1,615 rows and the same 19.584 m³, because the order is in the field itself and not in the code. #### Next Step This topic reshaped the dataset according to the question: tables were merged, the shape was changed, groups were summarized, new fields were derived, and categories were encoded. At every step the rows that went in, came out, and disappeared were counted, but all of these counts were made one by one with small, hand-written pieces of code, and none of them stands together in one place. Someone preparing the same dataset a second time cannot find which threshold was chosen with which justification or which ordering was used. The next lesson takes up documenting the preparation steps with a script: it counts how many steps can be reproduced and sums up the row accounting across the seventeen lessons of the course end to end. ### Reproducible Preparation Source: https://academia.sh/en/courses/data-collection-and-preparation/reproducible-preparation The course's seventeen preparation steps are written as data: 39 decisions, 13 items for each of the three topics. The silent loss the steps measured totals 4,614 records, and only 69 of these occurred in the adopted pipeline; the remaining 4,545 records are the measured cost of the common decision that was not chosen. Three steps take their parameters from the run environment rather than from the script, and two runs over the same data give different results in all three; 14 of the pipeline's 17 steps are reproducible, and once the values are written into the script all 17 are. The readability of decisions rises from 7 of 32 (22%) in cells to 36 of 39 in a script with no ledger, and to 39 of 39 with a run ledger. 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 ```js // 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 ```js // 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. | Lesson | Rows in | Rows out | Where it went | |---|---|---|---| | Collection 01 File Formats | 2,076 physical | 2,037 records | 142 silent in naive splitting | | Collection 02 Pulling Data from Databases | 1,877 | 1,877 | 766 silent in the wrong join | | Collection 03 Collecting Data from Interfaces | 1,877 | 1,877 | 477 in the narrow scope, 35 in paging | | Collection 04 Structural Parsing | 1,877 | 1,877 | 1,877 records from the wrong field | | Collection 05 Sampling | 1,877 | 200 | 1,677 left out by decision, 0 silent | | Cleaning 01 Missing Values | 2,086 | 2,068 | 18 dropped, 204 filled | | Cleaning 02 Duplicate Records | 2,068 | 1,880 | 188 merged, 23 wrong links | | Cleaning 03 Type Conversions | 1,880 | 1,880 | 0 dropped, 465 silent in the common approach | | Cleaning 04 Outliers | 1,880 | 1,816 | 64 to the quarantine set | | Cleaning 05 Fixing Categorical Values | 1,816 | 1,816 | 15 spellings in 5 classes | | Cleaning 06 Text Normalization | 1,816 | 1,816 | 1,491 matches recovered | | Transformation 01 Merging and Relating | 3,471 readings | 3,199 | 169 unmatched, 103 double matches, 69 subscribers silent | | Transformation 02 Reshaping | 3,199 | 1,260 wide | 581 empty cells, 103 overwritten | | Transformation 03 Grouping and Aggregation | 3,199 | 5 summaries | 3,194 melted away, 7 cells not visible | | Transformation 04 Derived Variables | 3,199 | 3,199 | 328 undefined, dropped at the filter | | Transformation 05 Categorical Encoding | 3,199 | 3,199 | threshold 2,166 / 1,678 / 1,633 | | Transformation 06 Reproducible Preparation | 3,199 | 3,199 | 39 decisions written to the ledger | 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.