Rebuild the clean customer file from the dirty one

applied · 60 min · Objective 2.3

Task

Apply everything in this objective to the raw customer file -- trimming, region mapping, date parsing and deduplication -- and compare your result with the course's clean reference file. The only differences left should be ones no cleaning can fix.

Steps

  1. From data/customers_raw.csv, trim names, map regions to the four standard values, parse every signup date to YYYY-MM-DD, and drop the near duplicates, keeping the lower customer_id.
  2. Write the result to lab/rebuild/customers.csv with the reference file's header, sorted by customer_id.
  3. Compare it with data/customers.csv row by row, and record in lab/rebuild/compare.txt the lines rows: N, identical rows: N and differing rows: N.
  4. Record in lab/rebuild/why.txt which column the differing rows differ in, and why cleaning cannot fix it.

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 '^(rows|identical rows|differing rows): ' lab/rebuild/compare.txt
python3 -c "import csv; a={r['customer_id']: r for r in csv.DictReader(open('lab/rebuild/customers.csv', encoding='utf-8'))}; b={r['customer_id']: r for r in csv.DictReader(open('data/customers.csv', encoding='utf-8'))}; print(sorted({k for i in a for k in a[i] if a[i][k] != b[i][k]}), 'differ')"

300 rows: 280 identical to the reference and 20 differing, and the only column that differs is email -- the 20 blank emails. Every other fault was cleaned away, but a value that was never recorded cannot be recovered by transforming what remains; it has to come from the source.

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