Normalise
- Lesson 4
- 35 min
- Module 2: Clean first, then match exactly
Where we are
We saw the same person written in many ways: capitals, accents, titles and phone formats. Most of these differences are not real. In this lesson, we normalise names, phones, emails and company names so that equal things look equal.
Lesson 4 of 29 · Entity Resolution and Record Matching: Is This the Same Thing?Normalise
1. The matching problem
2. Clean first, then match exactly
3. Blocking: do not compare everything
4. Compare records: similarity measures
5. Probabilistic linkage
6. Learned matching: classifiers, embeddings and language models
7. From pairs to entities
8. Run matching in production
What you will be able to do
Write each value in one standard way, so that two records of the same person show equal values. You normalise names, emails, phones, postcodes, dates, towns and company names, and you measure how much this alone helps the email join.
NotebookDo this lesson in a notebook
The notebook writes normalising functions for emails and names with pandas, and measures the email join before and after. Then you write the function for phone numbers yourself, and check your result. It downloads the data, so it needs no local setup.
Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.
Before you startWhat to know and to have ready
What you need to know
- The project
larkfield-match, set up in Meet the records.match checkmust end with “Setup check passed.” - Pair precision and pair recall, from False merges and missed matches.
- A string is a piece of text in a program, such as a name or an email. Basic Python is enough: a function that takes a string and returns a string.
Tested on macOS with Python 3.12.13. The Windows (PowerShell) and Linux commands are written the same way, but we did not test them.
The clerk reads past the “Dr.”
Section titled “The clerk reads past the “Dr.””Think of the clinic from Module 1 again. The clerk has two cards. One says Dr. Jean-François Desjardins. The other says Jean-François Desjardins. She does not hesitate. The “Dr.” is a title, not a part of the name. She reads the same name on both cards.
A computer does not read like this. It compares two strings character by character. “Dr. Jean-François” and “Jean-François” start with different characters, so they are different. A capital letter, an accent or one extra space is enough.
So before a computer can compare records, someone must remove the differences that do not matter. That is this lesson.
The problem: equal values that look different
Section titled “The problem: equal values that look different”Here is the same customer in TermLarkfieldA made-up Canadian online shop for home and garden products. It is not a real company. All Nextia courses use its help desk and data as the example.Learn more Meet Larkfield’s data. Loyalty record L-005618 and shop record S-029535 belong to one true customer. Look at the two emails:
L-005618 loyalty Desjardins.Jean-francois77@Mail.exampleS-029535 shop desjardins.jean-francois77@mail.exampleThey are the same address. Email addresses do not care about capitals. But the email join of Module 1 compares the strings exactly, so it says “different”. This customer stays two customers.
This is not a rare case. In our synthetic records, 50,973 records have an email. Of these, 6,489 (12.7%) change when we write them in one standard way. 5,139 have capital letters, and 1,499 have spaces before or after the address. Every one of them can cause a missed match.
The idea: one normal form for each value
Section titled “The idea: one normal form for each value”The fix is to normalise each value before you compare it. You write every value in one standard way, its normal form. Two values that mean the same thing then have the same normal form, and an exact comparison finds them.
A normal form follows simple rules that never change. For an email: remove the spaces around it, and write it in lower case. For a name: lower case, no accents, no punctuation, no titles. The rules are different for each kind of value, because each kind has its own differences that do not matter.
A worked example with every step visible
Section titled “A worked example with every step visible”The diagram takes the name of loyalty record L-005618 through the four steps of the project’s name rules. Each step changes one thing.
Text description of the diagram
The name starts as “Dr. Jean-François Desjardins”. Step 1 writes it in lower case: “dr. jean-françois desjardins”. Step 2 removes the accent: “ç” becomes “c”. Step 3 turns the dot and the hyphen into spaces, and keeps one space between words: “dr jean francois desjardins”. Step 4 removes the title “dr”: “jean francois desjardins”. The shop record writes the name as “Jean-François” and “Desjardins”, and the same steps give the same words. Now the two names are equal.
Step 2 removes accents but keeps the letter: “é” becomes “e”, “ç” becomes “c”. Most of Larkfield’s customers live in Quebec and Ontario. Many customers have French names, and one list writes them with accents while another does not. Step 3 helps with hyphens: one list writes “Jean-François”, another writes “Jean François”.
A rule set for each kind of value
Section titled “A rule set for each kind of value”Each kind of value needs its own rules. The table shows the rules in the project, with real examples from the data.
| Value | Rules | As written | Normal form |
|---|---|---|---|
| Name | lower case, no accents, punctuation to spaces, no titles | Dr. Jean-François Desjardins |
jean francois desjardins |
no spaces around it, lower case; empty if it has no @ and dot |
Desjardins.Jean-francois77@Mail.example |
desjardins.jean-francois77@mail.example |
|
| Phone | digits only; drop a leading 1; drop an extension; empty unless 10 digits are left |
+1 514 555 8296 |
5145558296 |
| Postcode | no space or hyphen, capitals; empty unless it has the Canadian shape | g6z 3w2 |
G6Z3W2 |
| Date | year-month-day | 26/07/1960 |
1960-07-26 |
| Town | as a name, and “Sainte” → “ste”, “Saint” → “st” | SAINTE-ANNE-DES-PRÉS |
ste anne des pres |
| Street | as a name, and one short form for each street type | r. des Hérons |
rue des herons |
| Company | as a name, without legal endings such as Inc, Ltd, Ltée | Larkfield Ltée |
larkfield |
Two rules need a short explanation:
- Legal endings. “Ltée” is the French short form of “limitée”, the same as “Ltd” in English. “Larkfield Ltée” and “Larkfield Inc.” are names of the same company, so the ending goes. Larkfield’s customers are people, so this rule matters for supplier names and for the companies in the case studies.
- Empty values. A rule that cannot make a normal form returns an empty string.
555-1313has only 7 digits, so it is not a full phone number, and the rule returns “”. An empty value never matches anything. This is safer than a wrong value.
Six phone formats, one normal form
Section titled “Six phone formats, one normal form”The three lists write phones in many ways. These are all the formats in our data, with the shape of each one (9 stands for any digit):
| Format | Example | Records |
|---|---|---|
9999999999 |
5145558296 |
19,148 |
999-999-9999 |
514-555-1313 |
8,830 |
(999) 999-9999 |
(514) 555-1313 |
8,817 |
+9 999 999 9999 |
+1 514 555 8296 |
3,706 |
9-999-999-9999 |
1-418-555-3256 |
3,655 |
999.999.9999 |
519.555.4534 |
3,588 |
After the phone rules, all 47,744 phones are 10 digits, in one format. A computer can now compare them with ==.
Ten ways to write one town
Section titled “Ten ways to write one town”Town names in Quebec often start with “Saint” or “Sainte”. People write them in many ways. We made Sainte-Anne-des-Prés a practice problem on purpose: in our data, 1,647 records write it in 10 different ways. The five most common are Sainte-Anne-des-Prés (496 records), Sainte-Anne-des-Pres (220), Ste-Anne-des-Prés (195), Ste Anne-des-Prés (188) and Sainte Anne des Prés (180). The others are the same in capitals, with or without the accent.
The town rules turn all 10 into one normal form: ste anne des pres. Lower case and no accents remove six of the differences. The hyphens become spaces. And “Sainte” becomes “ste”, the short form.
Normalise in the project
Section titled “Normalise in the project”The project gets one new file in this lesson: larkfield_match/normalise.py. It holds one function for each kind of value.
-
In your
larkfield-matchfolder, with the TermVirtual environmentA folder with its own Python and its own packages, for one project. Packages that you install there do not change other projects.Learn more Isolate dependenciesPython for Practical AI Engineering active, copy the file from the snapshotend-of-m02:Terminal window cp ../nextia-learning-labs/C39/snapshots/end-of-m02/larkfield_match/normalise.py larkfield_match/Terminal window Copy-Item ..\nextia-learning-labs\C39\snapshots\end-of-m02\larkfield_match\normalise.py larkfield_match\ -
Open
larkfield_match/normalise.pyin VS Code. Find the functionname. It callstext, which does steps 1 to 3, and then it removes the words inTITLES. -
Start Python in the terminal with
python. Then type the lines that start with>>>:>>> from larkfield_match import normalise as nz>>> nz.name("Dr. Jean-François Desjardins")'jean francois desjardins'>>> nz.email(" Desjardins.Jean-francois77@Mail.example ")'desjardins.jean-francois77@mail.example'>>> nz.phone("+1 (514) 555-1313")'5145551313'>>> nz.phone("555-1313")''>>> nz.postcode("g6z 3w2")'G6Z3W2'>>> nz.date("26/07/1960")'1960-07-26'>>> nz.town("SAINTE-ANNE-DES-PRÉS")'ste anne des pres'>>> nz.company("Larkfield Ltée")'larkfield'Type
exit()to leave Python.
Two records, before and after
Section titled “Two records, before and after”Now apply every rule to the two records of Jean-François Desjardins. The diagram shows each field as the lists wrote it, and after normalising.
Text description of the diagram
The left half shows the values as written. Three fields differ. The name has a title in one record, and the email has capitals in one record. The postcode is in lower case with a space in the shop record. The right half shows the normal forms. The name, the email and the postcode are now equal. The phone and the date of birth stay empty in the shop record: normalising cannot add a value that is not there.
Three equal fields out of five. An exact email join now finds this pair. Two missing values are normal: the shop list leaves the phone empty for 30% of its records.
Does normalising help? Measure it
Section titled “Does normalising help? Measure it”Normalising is only useful if it finds more true pairs. So measure it on the same test pairs as the email join of Module 1. The notebook does this now. The project adds the same run, exact-norm, in Exact and rule-based matching.
Predict first. Normalising the email can turn a missed match into a right merge. Can it also make a false merge?
- Right merges
- False merges
Text description of the chart
A column chart with four columns. Right merges: 1,879 as typed, 2,438 after normalising. False merges: 421 as typed, 538 after normalising.
| Run | Precision | Recall | F1 | Customers after clustering |
|---|---|---|---|---|
exact (as typed) |
0.817 | 0.338 | 0.478 | 50,367 |
exact-norm (normalised) |
0.819 | 0.438 | 0.571 | 47,688 |
Recall rises from 0.338 to 0.438: normalising finds 559 more true pairs. The answer to the prediction is yes. It also adds 117 false merges, so precision stays almost the same.
Where do the new false merges come from? Some families share one email address. As typed, TheGreens@Example.com and thegreens@example.com did not match, which hid the problem. After normalising, they match, and two brothers become one customer. Normalising does not make a matcher careless. It removes accidents, both helpful and harmful ones. The brothers are a practice problem that we made on purpose. Module 5 comes back to them.
Even after normalising, 3,126 of the 5,564 true pairs are still missed. Many customers have two different emails, or no email in one list. An email alone cannot find them. That is why the next lessons parse the other fields and combine them in rules.
Common mistake
A rule that removes too much
Symptom: the project’s title list has “m”, because “M.” is the French title for “Mr”, as in M. Pelletier. But some records now have an empty first name. BROWN, M. from the garden centres becomes the surname “brown” and no first name.
Cause: “M.” has two meanings. It is the French title “Monsieur”, and it is also an initial, as in “M. Brown” for Michael or Mary Brown. A normalising rule cannot know which one is meant. The rule removes “m” as a title, and so it loses the initial too. You will meet these records again in the next lesson, where they make parsing fail.
A second example: the town rules know “Saint” and “Sainte”. If someone types St-Anne-des-Prés, the rules give st anne des pres, not ste anne des pres. The rules cover only the spellings that we wrote rules for.
Fix: keep the rules small, and check each new rule on real records before you use it. Count how many values it changes, and read some of them. Always keep the value as written next to its normal form, so that a later step can look at both.
Consequence in AI work: normalising is a set of decisions about meaning. A rule that is too strong makes two different values equal, and that becomes a false merge. A rule that is too weak leaves differences, and that becomes a missed match. Measure both, as with any matcher.
When to normalise, and what it costs
Section titled “When to normalise, and what it costs”Use it always, and first. Every later method in this course compares normalised values, also the similarity measures of Module 4 and the models of Module 6. Normalising costs almost nothing: a few functions with simple string operations.
Do not overwrite the source. The project keeps the source records as they are, and writes the normal forms to new columns. Then you can explain every merge, and change a rule later.
It is not a matcher by itself. Normalising makes equal values look equal. It does not help when the values really differ. Examples are a typing error (Gagnoj for Gagnon), a nickname (Matt for Matthew) or an old address. Module 4 handles these with similarity measures.
Knowledge check
Section titled “Knowledge check”Check your understanding
Write the normal form of each value with the project's rules: “ Mme Hélène O'Brien ”, “(418) 555-3349”, “h9k-1e3” and “19/05/2004”.
Show the answer
helene obrien: lower case, no accent, the apostrophe removed, the title “mme” removed, no spaces around.4185553349: the digits only, 10 digits.H9K1E3: capitals, without the hyphen.2004-05-19: year, month, day.
You can check each one with nz.name, nz.phone, nz.postcode and nz.date in Python.
Does your answer have the main points of ours?
Sign in and enroll in the course to save your answers. Every check counts toward the certificate.
Check your understanding
Normalising the email raised recall from 0.338 to 0.438, but precision stayed at about 0.82. Why did precision not rise too?
Show the answer
Normalising adds pairs. It found 559 more right merges, and 117 more false merges. Most false merges come from family members who share one email address. As typed, some of these family emails differed in capitals, so they did not match. After normalising, they match. So normalising removes accidental differences both in true pairs and in false pairs. Precision depends on the share of right merges among all merges, and that share stayed almost the same.
Does your answer have the main points of ours?
Sign in and enroll in the course to save your answers. Every check counts toward the certificate.
Check your understanding
Tomás wants a stronger email rule: “Remove all dots and digits before the @. Then jean.f77@x and jeanf@x are equal.” What is the risk? How would you decide?
Show the answer
The rule makes more emails equal, so it can find more true pairs. But it also makes different people equal: marc.roy1@… and marc.roy2@… can be two people with the same name. These become false merges, and in our cost model a false merge costs 150 dollars while a missed match costs 3 dollars a year. Decide by measurement: run the join with and without the rule on the test pairs. Compare precision and recall, and read a sample of the new pairs. Keep the rule only if the extra right merges are worth the extra false merges.
Does your answer have the main points of ours?
Sign in and enroll in the course to save your answers. Every check counts toward the certificate.
Recap
- A computer compares strings character by character, so capitals, accents, spaces and titles make equal values different.
- Normalising writes each value in one standard way, its normal form. Names get lower case, no accents, no punctuation and no titles. Phones become 10 digits, postcodes six characters, and dates year-month-day.
- In our synthetic records, 6,489 of 50,973 emails change when normalised. Six phone formats become one. Ten spellings of Sainte-Anne-des-Prés become one.
- Normalising the email raises recall from 0.338 to 0.438, and precision stays at about 0.82. It adds 559 right merges and 117 false merges.
- Keep the source value next to the normal form. A rule that removes too much, such as “M.” as a title, loses information.
The email is now clean. But an address such as 2709 boul. Saint-Joseph app. 27 is still one long string, with a house number, a street and a flat number in it. Next, you parse such values into parts, so that each part can be compared alone.
Next: Parse into parts