Clean a messy contact list

A worksheet with a downloadable list of 14 fictional contacts, several of them entered twice in slightly different ways. In eight tasks, learners find the duplicates with a free browser tool, check what it removed and decide what counts as the same record.

Worksheet45 minBachelor +3CC BY 4.0

What you will learn

  • Explain why duplicates are rarely identical
  • Choose a key column and justify it
  • Use normalisation rules and check what a cleaning step removed
  • Decide which copy of a record to keep
  • Connect data quality to the GDPR principle of accuracy

What you need

  • The file messy-contacts.csv from the Downloads on this page: 14 rows with first name, last name, email, company and city. Every person and company in it is made up, and .example addresses cannot exist.

  • The CSV Deduplicator. It runs in the browser; the file is not uploaded.

Words you need

  • Record: one row in the list, here one contact.

  • Duplicate: a second record for something that is already in the list.

  • Key column: the column, or columns, that decide whether two records are the same. In the tool these are the columns selected under Match on.

  • Normalisation: making values comparable before comparing them, for example by ignoring capital letters or spaces at the start and end.

How the tool decides

Two rows count as duplicates when every selected column matches after the rules are applied. All columns are selected when you load a file. The rules sit under Rules: Ignore capitalisation and Ignore extra spaces (both on at the start) and Ignore punctuation (off). The same place lets you keep the first or the last match. The two download buttons save the unique rows and the removed rows as separate files.

Tasks

  1. Load the file. Select all columns and switch off every "Ignore" rule. How many duplicates does the tool find, and whose?

  2. Switch on Ignore capitalisation and Ignore extra spaces. How many duplicates now? Which new one appears, and what was different about its two rows?

  3. Select only the email column, with the same two rules. How many unique contacts are left? Which person is now caught, and why was the row missed before?

  4. One person is still in the list twice. Find them and explain why the email column cannot catch it.

  5. Select only the company column and switch on Ignore punctuation as well. How many companies are on the list? When would you want one row per company instead of one per person?

  6. Go back to the email column only, with capitalisation and spaces ignored and punctuation not ignored. Under Rules, change "Keep the" from First match to Last match. Which version of Anna Berger's and of Carla Mendes's record is kept now? Which setting is better for this file, and why?

  7. Set it back to First match and download the Removed rows. Which three rows are in the file? Why is it worth keeping this file until someone has checked the result?

  8. Select only the city column. How many different cities does the tool count? What does the result tell you about the limits of the rules?

Answers for teachers
  1. One duplicate: Ben Okafor, whose two rows are identical.

  2. Two duplicates. Anna Berger is added: her second row has lower-case names and an address with capital letters and spaces around it.

  3. 11 unique contacts. Carla Mendes is caught: her rows share the email address but differ in "Fabrikam Inc." and "Munich"/"München", so matching on all columns missed them.

  4. Farid Haddad appears with two different addresses, farid.haddad@ and f.haddad@. Matching on email cannot know they are one person; first and last name together can, but that would also merge two different people who share a name.

  5. Seven companies. One row per company suits a list of organisations to contact once, for example when inviting businesses to a school career fair.

  6. With "Last match", the kept rows are Anna's second row (lower-case names, address with capitals and spaces) and Carla's row with "Fabrikam Inc." and "München". "First match" is better here, because the first rows are the cleaner ones. In a file where later rows are updates (a new job title, a new phone number), "Last match" would be better. Either way, check and tidy the row you keep.

  7. Anna Berger's second row, Carla Mendes's row with "München", and Ben Okafor's second row. The removed rows show what the cleaning step did. If a count later looks wrong, or someone asks where a contact went, this file is the evidence, and a row removed by mistake can be put back.

  8. Eight cities, although there are only seven: Munich and München are two spellings of the same city. The rules only ignore capitals, spaces and punctuation; they cannot know that two different words mean the same place. Such values have to be made consistent first, for example with find and replace.

Common mistakes

  • Matching on all columns only. It finds identical rows and misses the real duplicates, which almost always differ somewhere.

  • Matching on too little. First name alone, or city alone, merges people who have nothing to do with each other.

  • Ignoring punctuation in the email column. The rule removes dots, hyphens and the @ sign, so different addresses can suddenly look the same. Use it for company names, not for email addresses.

  • Deleting without checking. Always look at the removed rows before you use the clean list.

  • Trusting the tool for every case. Farid Haddad needs a human decision: is f.haddad@ the same person, or a colleague with the same initial?

Extension for fast finishers

Select first_name and last_name together. How many unique rows are left, and which pair is merged that the email column did not merge? Write a short rule for your class, in two or three sentences, that says when two rows count as the same contact and what to do with the cases the tool cannot decide.

Answer to the extension

Ten unique rows. Farid Haddad's two rows are merged as well. A good rule: "Two rows are the same contact when the email address matches, ignoring capitals and spaces. If the names match but the addresses differ, a person checks before anything is merged." Any rule that names a key and a manual check for unclear cases is fine.

Teacher notes

How to run it (45 minutes). 5 minutes: show the file and ask how many people are really in it. 5 minutes: read "How the tool decides" together and load the file on the projector. 25 minutes: pairs work through the eight tasks and write short answers. 10 minutes: compare answers to tasks 4, 6 and 8, then discuss "Why it matters" below.

Typical mistakes. Learners forget to switch the rules back on between tasks, so their counts differ. Ask them to read the summary line under Rules, which shows the active rules. Some count duplicates and unique rows as the same thing: 14 rows with 3 duplicates leaves 11 unique rows.

Follow-up questions.

  • Who in a company should decide which column is the key?

  • What could go wrong if a school merged two pupils with the same name?

  • Why might a company keep duplicate rows on purpose, for example one row per order?

Differentiation.

  • Easier: do tasks 1 to 3 and 5 together on the projector, then let learners do task 4 alone.

  • Harder: learners open the file in a spreadsheet, use functions such as TRIM and LOWER in a new column to normalise the email addresses, and then sort to find the duplicates without the tool.

Why it matters

Under the GDPR (Article 5), personal data must be accurate and kept up to date. Duplicate records break that easily: a person who unsubscribes in one row may still be contacted through the other. When someone objects to direct marketing, their data must no longer be used for it (Article 21), so a forgotten duplicate can turn a tidy-up job into a breach. This is an overview, not legal advice.

Downloads

Reuse this material

CC BY 4.0

You may copy, adapt and share it, also commercially, as long as you credit ManyPI and link back.

"Clean a messy contact list" by ManyPI (https://manypi.com/edu/clean-a-contact-list), licensed under CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/)