--- title: 'Duplicate Records' source: 'https://academia.sh/en/courses/data-collection-and-preparation/duplicate-records' course: 'Data Collection and Preparation' language: en updated: '2026-10-07T04:31:11+00:00' license: 'CC BY-SA 4.0' objectives: - 'Defines the deduplication criterion as a key produced from the row and counts how many rows merge as the criterion changes' - 'Separates deduplication from record linkage: the first works inside one set, the second between two sources' - 'Compares the number of records linked by exact matching and by a similarity threshold, and the number of records linked wrongly' - 'Writes the row accounting of the step and shows item by item where the merged rows went' --- # 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.