Skip to content
Français

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.

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 notebookColab · Kaggle · GitHub

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 readyAbout 35 minutes

What you need to know

  • The project larkfield-match, set up in Meet the records. match check must 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.

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.example
S-029535 shop desjardins.jean-francois77@mail.example

They 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 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.

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.

Five boxes from top to bottom, joined by arrows. As written: Dr. Jean-François Desjardins. Step 1, lower case: dr. jean-françois desjardins. Step 2, remove accents: dr. jean-francois desjardins. Step 3, punctuation to spaces and one space between words: dr jean francois desjardins. Step 4, remove titles such as Mr, Mrs, Dr, M. and Mme: jean francois desjardins. A note says that the shop record S-029535 writes Jean-François plus Desjardins, and the same steps give jean francois plus desjardins, so the two names are now equal.
One name through the four steps of the name rules. The last box is the normal form. The shop record of the same customer gives the same normal form.
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”.

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
Email 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-1313 has 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.

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 ==.

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.

The project gets one new file in this lesson: larkfield_match/normalise.py. It holds one function for each kind of value.

  1. In your larkfield-match folder, 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 snapshot end-of-m02:

    Terminal window
    cp ../nextia-learning-labs/C39/snapshots/end-of-m02/larkfield_match/normalise.py larkfield_match/
  2. Open larkfield_match/normalise.py in VS Code. Find the function name. It calls text, which does steps 1 to 3, and then it removes the words in TITLES.

  3. 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.

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.

A table with five rows: name, email, phone, postcode and date of birth. Left half, as written: name Dr. Jean-François Desjardins in loyalty L-005618 and Jean-François Desjardins in shop S-029535, different; email Desjardins.Jean-francois77@Mail.example and desjardins.jean-francois77@mail.example, different; phone 514-555-1313 and empty; postcode G6Z3W2 and g6z 3w2, different; date of birth 26/07/1960 and empty. Right half, normalised: name jean francois desjardins in both, equal; email desjardins.jean-francois77@mail.example in both, equal; phone 5145551313 and empty, one is empty; postcode G6Z3W2 in both, equal; date of birth 1960-07-26 and empty, one is empty.
Loyalty record L-005618 and shop record S-029535, one customer. As written, the name, email and postcode differ. After normalising, they are equal. The phone and the date of birth are empty in the shop record.
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.

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
The email join on the test split, as typed and after normalising (synthetic records). Normalising adds 559 right merges and 117 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.

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.

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.

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.

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.

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