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.
Lesson 6 of 29 · Entity Resolution and Record Matching: Is This the Same Thing?Exact and rule-based matching
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
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 notebook
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 ready
What you need to know
- The prepared records, from Parse into parts.
- Pair precision, pair recall and F1, from False merges and missed matches and Meet the records.
- A join in SQL or pandas: rows of two tables that have the same value in a column. The SQL course teaches joins.
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’s rules of thumb
Section titled “The clerk’s rules of thumb”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.
The problem: one field is not enough
Section titled “The problem: one field is not enough”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-14S-007770 samuel green samuel.green@mail.example (no phone) M7K4L8 1966-03-14The 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.
The idea: exact keys and rules
Section titled “The idea: exact keys and rules”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:
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.
A worked example: the Green family
Section titled “A worked example: the Green family”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 | 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.
Rules in the project
Section titled “Rules in the project”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.
-
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/Terminal window Copy-Item ..\nextia-learning-labs\C39\snapshots\end-of-m02\larkfield_match\runs\rules.py larkfield_match\runs\ -
List the runs. The project finds the new ones by the file:
Terminal window match score --listexact customers same email, exactly as typedexact-norm customers same email after normalisingp-gtin products same barcode (GTIN) when both feeds have onep-rules products same barcode, or same parsed titlep-title products same brand, title words, size and pack after parsingrule-name-dob customers same first name, surname and date of birthrule-name-postcode customers same first name, surname and postcoderule-phone customers same phone (and nothing else)rule-phone-surname customers same phone and same surnamerules customers same email, or phone and surname, or name and birth date, or name and postcode -
Open
larkfield_match/runs/rules.py. Each rule is one line: a list of prepared columns._key_pairskeeps 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.rulestakes the union of four key lists. -
Measure the rule set on the test split:
Terminal window match eval --run rulesRan rules: 29,071 pairs called the same, in 14.2 srules on the test split: pair precision 0.723, recall 0.870, F1 0.7906,693 pairs called the same: 4,840 right, 1,853 wrong; 724 of 5,564 true pairs missedentities 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.
-
Measure each rule alone: run
match eval --runwithexact-norm,rule-phone-surname,rule-name-dobandrule-name-postcode. For example:Terminal window match eval --run rule-name-dobRan rule-name-dob: 4,969 pairs called the same, in 1.7 srule-name-dob on the test split: pair precision 1.000, recall 0.170, F1 0.291946 pairs called the same: 946 right, 0 wrong; 4,618 of 5,564 true pairs missedentities after clustering (all records): 55,409 (true: 37,500)
Each rule, and all four together
Section titled “Each rule, and all four together”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
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.
The baseline
Section titled “The baseline”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.
Products: the easy case
Section titled “Products: the easy case”The supplier feeds are easier. Two rules match products: the same barcode (GTIN), or the same parsed title (brand, words, size and pack).
match eval --run p-rulesRan p-rules: 2,086 pairs called the same, in 0.4 sp-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:
match eval --run rule-phoneRan rule-phone: 19,873 pairs called the same, in 4.3 srule-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.
When to use rules, and what they cost
Section titled “When to use rules, and what they cost”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.
Knowledge check
Section titled “Knowledge check”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
- 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.
- Names that differ. The rule needs the same first name and surname after normalising. Nicknames (
MattandMatthew), short names (VALandValé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.
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
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.
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
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.
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 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.
rulesis the baseline of the course. The product feeds are the easy case:p-ruleshas 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