--- title: 'Fixing Categorical Values' source: 'https://academia.sh/en/courses/data-collection-and-preparation/fixing-categorical-values' course: 'Data Collection and Preparation' language: en updated: '2026-10-07T04:31:11+00:00' license: 'CC BY-SA 4.0' objectives: - 'Applies the category harmonization ladder step by step and counts how many categories remain after each step' - 'Separates spelling correction from semantic judgment: the first comes from the text, the second from the tariff schedule' - 'Measures how many rows merging two categories moves to the wrong class and how it changes the class distribution' - 'Counts how many rows an incomplete mapping table leaves unmapped and silently drops from grouping' --- # 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**.