Practice: Cleaning messy customer data: data mapping and entity resolution

Generated by Claude through GigAI 0.1.11.10 on 2026-10-11.

Main tool: pandas · pandas shows every stage of the cleanup, from missing-value handling to match keys and survivorship, as plain inspectable tables on a laptop, so you see exactly what each rule does to each record. · Alternatives: Splink does probabilistic linkage with trained match weights on a SQL backend, OpenRefine is a point-and-click tool for clustering and cleanup, DuckDB runs the same profiling and joins as in-process SQL over files, and Apache Spark spreads the same logic across a cluster when an extract will not fit on one machine.

Every step links into the tool's own documentation; links were checked on 2026-10-11 · version 1

Setup (one time)

1. Create a fresh Python virtual environment, install pandas at one pinned version, record that version in a requirements file, and build a tiny DataFrame from a dictionary to confirm the install.
A pinned version keeps missing-value and dtype behaviour the same on every rerun. The constructor is the entry point for everything that follows.
Explained in: pandas.DataFrame — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: A three-row DataFrame prints in your Python session and the requirements file names one exact pandas version.
2. Write two small overlapping customer extracts by hand, one shaped like a CRM export and one like an ERP export, of about thirty rows each, with repeated customers, blank cells, placeholder words such as unknown, and inconsistent phone and date formats.
You need data whose flaws you planted yourself so you can check that each later step finds them. Knowing what pandas counts as missing tells you which flaws it will miss at first.
Explained in: Working with missing data — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: Two files exist on disk and you have a written list of which rows are true duplicates and which cells are really missing.
3. Install the optional pyarrow package into the same environment and pin it too, so Arrow-backed column types are available.
Arrow-backed types give strings and integers one consistent missing marker. You will rely on that when comparing fields across sources.
Explained in: pandas.ArrowDtype — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: A Series created with an Arrow-backed string type prints its dtype without an import error.

Steps

1. Load both extracts into DataFrames with every column read as text, then inspect shape, column names, dtypes and the first rows of each.
Reading everything as text stops pandas from guessing types and silently mangling identifiers and postcodes. This first look is the start of profiling an unknown extract.
Explained in: pandas.DataFrame — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: You can state the row count, column count and dtype of every column for both extracts.
2. Run isna over each extract and compute the count and share of missing values per column, saving the result as a small profile table.
A per-column missingness profile shows which fields can serve as match keys and which are too sparse to trust. It is also the baseline for every later comparison.
Explained in: pandas.isna — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: A profile table lists each column with its missing count, and the counts are lower than the missing cells you planted because the placeholder words are not yet detected.
3. Replace the placeholder tokens you planted, such as unknown, a lone dash and empty strings, with real missing values, then rerun the profile and compare it to the first one.
Customer exports hide missing data behind sentinel text, and pandas only treats it as missing after you convert it. The before and after profiles show how much was hidden.
Explained in: Working with missing data — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: The second profile shows higher missing counts that match the number of cells you planted as missing.
4. Run convert_dtypes on both cleaned extracts and compare the dtypes before and after.
Nullable dtypes keep integer identifiers as integers when values are missing and give every column the same missing marker. That makes later comparisons predictable.
Explained in: pandas.DataFrame.convert_dtypes — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: Identifier columns show a nullable integer or string dtype instead of object or float, and no identifier has gained a decimal point.
5. Write a source-to-target mapping spec as a table with one row per source column, giving its target name and target type. Add a crosswalk dictionary for coded values such as country and status codes, and apply both with rename and replace so the two extracts share one schema.
A written spec and crosswalk make the mapping reviewable by the customer and repeatable when a new export arrives. Applying the crosswalk through a dictionary keeps the lookup as data rather than scattered logic.
Explained in: pandas.DataFrame.replace — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: Both DataFrames have identical column names and each coded column contains only values from the target code list.
6. Write regular expression replacements that normalize names, phone numbers and postcodes into a new set of columns: strip punctuation and repeated whitespace and remove legal suffixes. Keep the raw columns untouched.
Normalization is what lets two spellings of the same entity compare as equal. Keeping the raw values beside the normalized ones keeps the work traceable.
Explained in: Working with missing data — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: Rows you planted as the same customer now carry identical normalized name and phone values while their raw values still differ.
7. Configure dropna with a subset and a threshold so rows lacking every usable identifier go to a quarantine DataFrame instead of being deleted, and write down the rule you chose.
Records with nothing to match on cannot be resolved and will produce false links if left in. Quarantining rather than deleting keeps the decision reversible.
Explained in: pandas.DataFrame.dropna — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: The kept and quarantined row counts add up to the original row count.
8. Create a deterministic match key from the normalized fields and set it as the index of the stacked extracts. Run the duplicate label detection checks to list every key that appears more than once, and assign a cluster identifier to each group.
Exact-key matching is the cheap, explainable first pass that resolves most duplicates. Detecting repeated keys on the index is how you find and count them.
Explained in: Duplicate Labels — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: The list of repeated keys covers the exact duplicates you planted, and each has one cluster identifier.
9. Compare a missing match field against another missing match field and against a real value, and record what each comparison returns.
A missing value does not equal another missing value, so a naive comparison can neither confirm nor rule out a match. You must decide explicitly how missing fields contribute to a link.
Explained in: Working with missing data — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: You can show that comparing two missing values yields missing rather than true, and you have written the rule your linkage will use for that case.
10. Create candidate pairs by joining the remaining unmatched records to each other on a blocking key such as postcode. Compute per-field agreement columns, then use where to turn each agreement into a positive weight, each disagreement into a negative weight and each missing comparison into zero. Sum the weights and pick a threshold.
Blocking keeps the number of comparisons manageable and match weights let strong fields count for more than weak ones. This is the core of probabilistic record linkage, and building it by hand shows what a dedicated linkage library automates.
Explained in: pandas.DataFrame.where — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: A pair table shows a total score per candidate pair, the planted fuzzy duplicates score above your threshold and unrelated pairs in the same block score below it.
11. Create one golden record per cluster by ordering the sources by trust and filling each missing field from the next most trusted source. Save a crosswalk table that links every source row identifier to its golden identifier and the rule that merged it.
Survivorship rules decide which value wins when sources disagree. The crosswalk lets you explain any golden value and undo a wrong merge.
Explained in: pandas.DataFrame.fillna — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: The golden table has one row per cluster with fewer missing cells than any single source, and you can pick any golden row and list the source rows behind it.
12. Configure the golden table to disallow duplicate labels on its golden identifier index, then try to append a row with an existing identifier and confirm the operation fails.
A golden record set is only trustworthy if one identifier means one entity. An enforced invariant catches a bad merge when it happens instead of in a downstream feature.
Explained in: Duplicate Labels — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: Adding a repeated golden identifier raises an error instead of producing a second row.
13. Write the whole pipeline as one script that runs in the pinned environment from the raw files to the golden table and crosswalk. Add explicit isna-based checks that stop the run when a required field is missing or a missing count exceeds the profiled baseline, then run it twice.
A missing value cannot be used directly as a true or false condition, so automated checks must test for it explicitly. A pinned, rerunnable script with failing checks is the closest laptop equivalent to a production data job.
Explained in: Working with missing data — pandas 3.0.6 documentation (pandas.pydata.org), checked 2026-10-11
Done when: Two consecutive runs produce identical golden and crosswalk outputs, and blanking a required field in the input makes the run stop with a clear message.

Back to Cleaning messy customer data: data mapping and entity resolution