Merge the duplicates with a written survivorship rule

applied · 50 min · Objective 2.2

Task

Resolve the raw file's near duplicates into one record per customer, following a survivorship rule you write down first, and keep an audit of every merge so the decision can be checked and reversed.

Steps

  1. Write the rule in lab/merge/rule.txt before touching the data: which record survives, and what happens to fields that differ.
  2. Apply it to data/customers_raw.csv: for each pair of rows sharing a lower-cased email, keep the lower customer_id and discard the other.
  3. Write the result to lab/merge/customers.csv, and an audit of every merge to lab/merge/audit.csv with header kept_id,removed_id,reason.
  4. Record before: N and after: N row counts in lab/merge/counts.txt.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

grep -E '^(before|after): ' lab/merge/counts.txt
awk -F, 'NR>1 {n++} END {print n" merge(s) audited"}' lab/merge/audit.csv
python3 -c "import csv; k={r['removed_id'] for r in csv.DictReader(open('lab/merge/audit.csv'))}; s={r['customer_id'] for r in csv.DictReader(open('lab/merge/customers.csv', encoding='utf-8'))}; print(len(k & s), 'removed id(s) still present')"
grep -c . lab/merge/rule.txt

314 rows before and 300 after, 14 merges audited, 0 removed ids still present, and a written rule. Anyone who disagrees with a merge can find it in the audit and reverse it, which is what makes deduplication safe to do at all.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.