Skip to content
Français

Exact and rule-based matching

  • Lesson 6
  • 40 min
  • Module 2: Clean first, then match exactly

Where we are

Our records are now clean and in parts. The simplest matcher compares them exactly. In this lesson, we build exact keys and a few rules, such as "same email, or same phone and surname", and we get the baseline numbers to beat.

What you will be able to do

Build matchers from exact keys and rules on the prepared records. Measure each rule alone, and four rules together, on the test pairs. The result is the baseline: the numbers that every later method in this course must beat.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook cleans the records, builds exact keys with pandas, and measures one rule. Then you build the other rules and the four rules together, and report pair precision and recall for each. 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 40 minutes

What you need to know

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 clinic’s clerk has worked with the files for years. She has a few rules of thumb. “Same name and same date of birth: same patient.” “Same phone and same surname: probably the same family, maybe the same patient.” She knows which rules are safe and which ones need a second look.

A computer can follow rules like these, if each one is exact: two records are the same when some fields are equal. This lesson writes four such rules, and measures how safe each one is.

After normalising, the email join finds 43.8% of the true pairs. Many customers have two different emails, or no email in one list. Here is one of them: Samuel Green, whom you met in Module 1. His loyalty record and his shop record are one person, with two emails:

L-011126 samuel green thegreens@example.com 9055551835 M7K4L8 1966-03-14
S-007770 samuel green samuel.green@mail.example (no phone) M7K4L8 1966-03-14

The emails differ, so the email join misses them. But look at the other fields. The first name, the surname, the postcode and the date of birth are all equal. A matcher that looks at these fields together can find this pair.

A key is a value built from one or more fields of a record. For example, the key “first name + surname + date of birth” of L-011126 is samuel|green|1966-03-14. An exact key match says: two records are the same when their keys are equal, and no field of the key is empty.

A rule is one such key. A rule set joins several rules with “or”: two records are the same when any rule says so. This kind of matcher is called rule-based or deterministic matching: the same records always give the same answer, and you can say exactly why.

The project’s rule set has four rules:

A decision flow. Two records, after normalising and parsing, go to Rule 1: same email? Alone, it has precision 0.819 and recall 0.438. Yes leads to SAME. No leads to Rule 2: same phone and same surname? Alone, precision 0.696, recall 0.438. No leads to Rule 3: same first name, surname and date of birth? Alone, precision 1.000, recall 0.170. No leads to Rule 4: same first name, surname and postcode? Alone, precision 0.921, recall 0.621. Each yes leads to SAME. The last no leads to DIFFERENT, for now. The SAME box says: all four rules together, precision 0.723, recall 0.870.
The four rules as a decision flow, with each rule's precision and recall alone on the test split (synthetic records). One yes is enough for SAME.
Text description of the diagram

Two records go through four questions. Rule 1: the same email? Rule 2: the same phone and the same surname? Rule 3: the same first name, surname and date of birth? Rule 4: the same first name, surname and postcode? A yes to any question gives SAME. Four noes give DIFFERENT. Alone, the rules have very different precision: 1.000 for name and date of birth, and 0.696 for phone and surname. All four together have precision 0.723 and recall 0.870.

Each rule uses fields that are rarely equal by chance for two different people. A surname alone is too common: many Tremblays live in Quebec. A surname with a phone is much rarer. A full name with a date of birth is rarer still.

Here are Samuel Green’s four records, and one record of his brother Nathan. The brothers are a practice problem we made on purpose: they share a family email, a landline and an address. The values are the prepared values.

Record First Surname Email Phone Postcode Birth date True customer
G-001688 samuel green thegreens@example.com 9055551835 M7K4L8 Samuel
G-008438 samuel green 5195554060 L0H6T9 Samuel
L-011126 samuel green thegreens@example.com 9055551835 M7K4L8 1966-03-14 Samuel
S-007770 samuel green samuel.green@mail.example M7K4L8 1966-03-14 Samuel
G-002894 nathan green thegreens@example.com 9055551835 M7K4L8 1966-05-20 Nathan

Five records make 10 pairs. The table shows which rules say yes for each pair:

Pair Rules that say yes The rules decide The truth Result
G-001688, L-011126 1, 2, 4 same one person right merge
G-001688, S-007770 4 same one person right merge
L-011126, S-007770 3, 4 same one person right merge
G-001688, G-002894 1, 2 same two people false merge
L-011126, G-002894 1, 2 same two people false merge
G-008438 with each other Samuel record none different one person 3 missed matches
S-007770, G-002894 none different two people right
G-008438, G-002894 none different two people right

The rules find 3 of Samuel’s 6 true pairs. They miss the 3 pairs with G-008438: an old address in Stonewick, another phone, and no email. And rules 1 and 2 merge Samuel with his brother, because the family shares the email and the phone. Rules 3 and 4 do not merge the brothers: their first names differ.

So in one small family, you see the whole story of this lesson. Rules that use the name are safe. Rules on shared family fields are risky.

This lesson adds one file: larkfield_match/runs/rules.py. It defines the runs exact-norm, the four rules alone, the rule set rules, and three rules for the product feeds.

  1. Copy the file from the snapshot end-of-m02:

    Terminal window
    cp ../nextia-learning-labs/C39/snapshots/end-of-m02/larkfield_match/runs/rules.py larkfield_match/runs/
  2. List the runs. The project finds the new ones by the file:

    Terminal window
    match score --list
    exact customers same email, exactly as typed
    exact-norm customers same email after normalising
    p-gtin products same barcode (GTIN) when both feeds have one
    p-rules products same barcode, or same parsed title
    p-title products same brand, title words, size and pack after parsing
    rule-name-dob customers same first name, surname and date of birth
    rule-name-postcode customers same first name, surname and postcode
    rule-phone customers same phone (and nothing else)
    rule-phone-surname customers same phone and same surname
    rules customers same email, or phone and surname, or name and birth date, or name and postcode
  3. Open larkfield_match/runs/rules.py. Each rule is one line: a list of prepared columns. _key_pairs keeps the records where none of these columns is empty, and joins the columns into one key. Then it returns every pair of records with the same key. rules takes the union of four key lists.

  4. Measure the rule set on the test split:

    Terminal window
    match eval --run rules
    Ran rules: 29,071 pairs called the same, in 14.2 s
    rules on the test split: pair precision 0.723, recall 0.870, F1 0.790
    6,693 pairs called the same: 4,840 right, 1,853 wrong; 724 of 5,564 true pairs missed
    entities after clustering (all records): 36,756 (true: 37,500)

    The first line counts the pairs in all splits, and the time. The time depends on your computer. The other lines are the test split only.

  5. Measure each rule alone: run match eval --run with exact-norm, rule-phone-surname, rule-name-dob and rule-name-postcode. For example:

    Terminal window
    match eval --run rule-name-dob
    Ran rule-name-dob: 4,969 pairs called the same, in 1.7 s
    rule-name-dob on the test split: pair precision 1.000, recall 0.170, F1 0.291
    946 pairs called the same: 946 right, 0 wrong; 4,618 of 5,564 true pairs missed
    entities after clustering (all records): 55,409 (true: 37,500)

The chart puts every rule on two axes: recall to the right, precision up. A perfect matcher is in the top right corner.

Predict first. Rule 3 alone has precision 1.000, and rule 4 has 0.921. What happens to precision when you join all four rules with “or”? Does it go up, stay, or go down?

  • One rule alone
  • All four rules together
Pair precision and recall of each rule alone, and of the four rules together, on the test split (synthetic records). 'phone only' is not in the rule set; it shows what the phone does without the surname.
Text description of the chart

A scatter plot with recall from 0 to 1 across, and precision from 0.4 to 1.05 up. Recall 0.438 and precision 0.819 for the email. Recall 0.438 and precision 0.696 for phone and surname. Recall 0.170 and precision 1.000 for name and birth date. Recall 0.621 and precision 0.921 for name and postcode. Recall 0.494 and precision 0.539 for the phone only. Recall 0.870 and precision 0.723 for all four rules together.

Rule Precision Recall F1 Pairs called the same Wrong
1. same email 0.819 0.438 0.571 2,976 538
2. same phone and surname 0.696 0.438 0.537 3,501 1,065
3. same name and date of birth 1.000 0.170 0.291 946 0
4. same name and postcode 0.921 0.621 0.742 3,754 296
all four (rules) 0.723 0.870 0.790 6,693 1,853

Precision goes down. With “or”, the rule set keeps every pair of every rule, and so it keeps every false merge of every rule too. Recall goes up for the same reason: each rule finds some true pairs that the others miss. Together, the four rules find 87.0% of the true pairs, twice as many as the email.

Rule 3 is perfect on precision, but it finds only 17.0% of the true pairs. The date of birth is empty in most shop and garden-centre records, and the rule needs both records to have it. A rule can only decide when its fields are filled.

Now the comparison table of the course has two more rows. Every later method runs on the same test pairs, and must beat these numbers:

Run Module Precision Recall F1
exact 1 0.817 0.338 0.478
exact-norm 2 0.819 0.438 0.571
rules 2 0.723 0.870 0.790

The rule set is a strong baseline. It took four lines of code and runs in seconds. In later modules, you will see a method with labels reach the same F1, and a hand-weighted score stay below it.

The supplier feeds are easier. Two rules match products: the same barcode (GTIN), or the same parsed title (brand, words, size and pack).

Terminal window
match eval --run p-rules
Ran p-rules: 2,086 pairs called the same, in 0.4 s
p-rules on the test split: pair precision 1.000, recall 0.772, F1 0.871
436 pairs called the same: 436 right, 0 wrong; 129 of 565 true pairs missed
entities after clustering (all records): 5,875 (true: 5,211)

Not one false merge, and 77.2% of the true pairs. Product titles carry rare words and exact numbers, so two different products rarely share a whole parsed title. Of the 129 missed pairs, 93 differ only in the size, such as 1.22 m and 1.2 m from the last lesson. Another 34 differ only in the title words. Customers are the hard case of this course; products are the easy one.

Common mistake

A rule on one shared field

Symptom: you add a simple rule, “same phone”. It finds more true pairs than “same phone and surname”, but precision falls to 0.539:

Terminal window
match eval --run rule-phone
Ran rule-phone: 19,873 pairs called the same, in 4.3 s
rule-phone on the test split: pair precision 0.539, recall 0.494, F1 0.515
5,101 pairs called the same: 2,748 right, 2,353 wrong; 2,816 of 5,564 true pairs missed
entities after clustering (all records): 45,230 (true: 37,500)

Cause: a phone is not a person. Families share one landline. And the garden centres typed their own shop number, 450-555-0100, for 40 walk-in customers who gave no phone: a practice problem we made on purpose. The rule joins all 40. In the test split, 345 of its pairs come from these walk-in records, and none of them is right.

Fix: never write a rule on one field that many people can share: a phone, a family email, a postcode, a street. Combine it with a field of the person, such as the surname or the first name. Also count how many records share each value of a key: a value that 40 records share is not a person. match eval --run rules --by problem, in the next lesson, shows how each rule does on each practice problem.

Consequence in AI work: one rule with a weak key can merge dozens of people into one customer. Module 7 shows the result, one cluster of 40 different people, and how to cut such links. Before you add a rule, look at how many records share its most common key.

Use rules as the first matcher and as the baseline. They are cheap, fast and easy to explain: “these two records have the same name and date of birth.” Some teams keep a very strict rule, such as rule 3, as an automatic merge, and send the rest to other methods.

Rules are brittle. They need exact equality. Gagnoj and Gagnon are different, and so are Matt and Matthew. They also need the key fields to be filled: a rule on the date of birth cannot help a record without one. Modules 4 to 6 replace “equal or not” with a score that allows small differences.

Rules grow. Each new case asks for a new rule, and every rule adds its false merges. Measure each rule alone and in the set, every time you change one.

What it costs. Almost nothing to run: the rule set takes seconds on a laptop. The real cost is in its errors. The course assumes that a false merge costs 150 dollars, and a missed match 3 dollars a year. With these costs, the rule set’s 1,853 false merges on the test split cost far more than its missed matches. You computed this in False merges and missed matches.

Check your understanding

Rule 3 (same first name, surname and date of birth) has precision 1.000 but recall only 0.170. Give two reasons for the low recall, from what you know of the data.

Show the answer
  1. Empty dates of birth. The rule needs the date in both records. The shop leaves it empty in 54.6% of its records, and the garden centres in 84.7%. Most pairs cannot use this rule.
  2. Names that differ. The rule needs the same first name and surname after normalising. Nicknames (Matt and Matthew), short names (VAL and Valérie), typing errors and swapped names all make the names unequal.

The precision is high because two different people rarely share a full name and a date of birth.

Check your understanding

In the Green family example, which rules merge Samuel with his brother Nathan, and why do the other two rules not merge them?

Show the answer

Rule 1 (same email) and rule 2 (same phone and surname) merge them. The brothers share the family email thegreens@example.com, the landline 9055551835 and the surname. Rules 3 and 4 need the same first name, and samuel is not nathan. So the rules that use the person’s own name are safe here, and the rules on shared family fields make the false merges.

Check your understanding

Omar asks: “Why not add rule 5, same surname and same postcode? It would find more people who changed their first name or use a nickname.” What do you expect to happen? How do you check?

Show the answer

Recall would go up a little: it would find some pairs with a nickname or a short first name. But precision would fall a lot. Everybody in one household has the same surname and the same postcode, so the rule merges spouses, parents and children. The brothers in the Green family would be merged by it too. Check by measurement: build the rule, measure it alone and in the set, and read some of its new pairs. The notebook’s last task does this. With the notebook’s cleaning, rule 5 alone has precision 0.243. Added to the four rules, it raises recall from 0.870 to 0.904, and lowers precision from 0.723 to 0.271.

Recap

  • A key joins one or more normalised fields. An exact key match says “same” when two records have equal keys with no empty field. A rule set joins rules with “or”.
  • Alone, the rules have very different precision. Name and date of birth: 1.000. Name and postcode: 0.921. Email: 0.819. Phone and surname: 0.696.
  • All four together: precision 0.723, recall 0.870, F1 0.790, on the test split. With “or”, recall adds up, and so do false merges.
  • Rules on fields that people share (a family email, a landline, a shop’s own phone number) make false merges. “Same phone” alone has precision 0.539.
  • rules is the baseline of the course. The product feeds are the easy case: p-rules has precision 1.000 and recall 0.772.

You now have numbers for each rule. But what exactly do they count? Next, you look inside pair precision and recall, and you see why the same matcher looks better or worse when you count pairs or customers.

Next: Measure a matcher