Clean regions and names with a mapping table, and count every change

short · 40 min · Objective 2.3

Task

Standardise the raw file's region and name fields using a mapping table and trimming, keep the raw values beside the cleaned ones, and prove by re-profiling that the problems profiling found are gone.

Steps

  1. Write lab/clean/regions.csv with header raw,standard, mapping every region value that appears in data/customers_raw.csv to North, South, East or West.
  2. Write lab/clean/customers.csv with the raw columns plus region_clean, first_name_clean and last_name_clean, trimmed and mapped.
  3. Record in lab/clean/summary.txt the lines regions changed: N, names trimmed: N and unmapped: N.
  4. Re-profile the cleaned columns and add distinct clean regions: N to the summary.

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 '^(regions changed|names trimmed|unmapped|distinct clean regions): ' lab/clean/summary.txt
awk -F, 'NR>1 {n++} END {print n" mapping row(s)"}' lab/clean/regions.csv

40 regions changed, 25 names trimmed, 0 unmapped values and 4 distinct clean regions, from a mapping table of 16 rows -- one per raw value, including the four already correct. Those counts match the profile exactly, which is the evidence the cleaning is complete.

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