--- title: 'Text Normalization' source: 'https://academia.sh/en/courses/data-collection-and-preparation/text-normalization' course: 'Data Collection and Preparation' language: en updated: '2026-10-07T04:31:11+00:00' license: 'CC BY-SA 4.0' objectives: - 'Applies the text normalization ladder to the address field and counts in how many records each step rescues the match' - 'Separates locale-aware from locale-blind lowercasing: compares the number of records each one rescues' - 'Measures how many distinct values one step of excess normalization makes indistinguishable' - 'Adds up the row accounting of the six steps of the topic and shows what shape the set still stands in' --- # 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.