Compare by field, then combine
- Lesson 14
- 40 min
- Module 4: Compare records: similarity measures
Where we are
We can now score one field at a time. A pair of records has many fields. In this lesson, we build a comparison vector for each pair, combine the fields with weights that we choose by hand, and choose a threshold.
Lesson 14 of 29 · Entity Resolution and Record Matching: Is This the Same Thing?Compare by field, then combine
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
Compare a pair field by field, and keep the results as one row of numbers: a comparison vector. Treat empty values as missing, not as different. Then combine the fields into one score with weights chosen by hand. Choose a threshold on the validation pairs, and add the result to the comparison table.
NotebookBuild vectors and a weighted score in a notebook
The notebook makes the candidate pairs of the five keys and builds a six-field comparison vector for each pair. It adds weights by hand, and asks you to choose the threshold on the validation split. Its setup cells download 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
- Jaro–Winkler and token set ratio, from String similarity; sound codes, from Sounds the same.
- Pair precision, recall and F1, and the test split, from Measure a matcher.
- Why we choose settings on a validation set, from Train, validation and test sets in the machine learning course.
What you need on your computer
- The project
larkfield-matchwith the files of the last two lessons. Or use the notebook only.
Tested on macOS with Python 3.12.13. The Windows (PowerShell) commands are not tested.
A clinic checks a patient
Section titled “A clinic checks a patient”A patient arrives at a clinic. The receptionist finds a file with the same surname. Is it her file?
The receptionist does not decide on the surname alone. She asks for the date of birth, then the address, then the phone number. Each answer is a piece of evidence:
- The date of birth agrees: strong evidence. Few people share a surname and a birthday.
- The surname agrees: weak evidence. Many families share a common surname.
- The phone is not in the file: no evidence at all. It does not say “yes”, and it does not say “no”.
Then she weighs the pieces together. A matcher must do the same thing, for every candidate pair.
The problem: one field is never enough
Section titled “The problem: one field is never enough”In the last two lessons, we compared one field at a time. Every field fails somewhere:
- Names are shared by family members. In String similarity, 54% of the candidate pairs of two different people had a full-name token set ratio of 0.9 or more.
- Emails are shared by whole families (
thegreens@example.com), and the garden centres have no email in 60% of their records. - Postcodes change when a customer moves: 4,289 true pairs have two different postcodes.
- Dates of birth are empty in most garden-centre and shop records.
Each field gives some evidence. We need all of them, side by side, for each pair.
The idea: a comparison vector
Section titled “The idea: a comparison vector”A comparison vector is one row of numbers for one candidate pair: one number per field comparison. A vector here means a list of numbers in a fixed order. The project compares 16 things for each customer pair.
Each number is one of three kinds:
- A similarity from 0 to 1, for fields with typing errors. The first name, the surname and the part of the email before the @ use Jaro–Winkler. The full name and the street use token set ratio.
- An agreement: 1 when the clean values are equal, 0 when they differ. This is for fields where one wrong character makes another value: the email, phone, postcode, house number, town and date of birth.
- Missing, when one of the two records has no value for the field.
The diagram shows the vectors of two pairs. The first pair is Valérie Tremblay’s garden-centre and shop records: one customer. The second pair is the brothers Samuel and Nathan Green: two people.
Text description of the diagram
A table with 16 rows, one per field comparison, and two columns. Valérie’s pair agrees on the surname, email, phone, street, house number and town, and its first names are close (0.867: “val” and “valérie”). Its postcodes differ by one character, a typing error, so the postcode gets 0, but the first 3 characters agree. Her garden-centre record has no date of birth, so the three date fields are missing. The brothers agree on everything they share: surname, family email, landline, postcode, street, number and town. Only their first names differ (0.444). Their date fields are missing too.
Look at the brothers. They agree on more fields than Valérie’s own records. A household shares most of its fields. This is the hardest case for any matcher, and it comes back in every module.
Missing is not different
Section titled “Missing is not different”Why keep “missing” apart from 0? Because they mean different things. A 0 says “the two records disagree”: evidence against a match. Missing says “we do not know”: no evidence either way.
Missing values are common. On the 1,108,591 candidate pairs of the five keys, the date of birth is missing in 71.1% of pairs, the phone in 36.8% and the email in 28.4%. If we turned all of these into 0, most pairs would get evidence against them that does not exist.
In the code, missing is NaN (“not a number”): pandas and NumPy keep it apart from 0. Later modules use this. In Module 5, a missing field adds no weight, and a disagreement subtracts weight.
Dates and numbers need their own comparisons
Section titled “Dates and numbers need their own comparisons”A string measure on a date or a number gives strange results. “1975-01-31” and “1975-01-13” share most characters, but they are different days. So the project compares dates and numbers in their own ways:
- Date of birth: equal or not (
dob_eq), plus two common errors.dob_swapis 1 when the day and month are swapped (31/01 against 01/31).dob_year_eqis 1 when only the year agrees. - Postcode: equal or not, plus the first 3 characters (
postcode3_eq), the area. A customer who moves within one town often keeps them. - House number: equal or not. A different number in the same street is a different home.
- Products: the vector of product pairs has
size_ratioandprice_ratio: the smaller value divided by the larger. 12 inches against 16 inches gives 0.75, and two equal sizes give 1. It also haspack_eq, for a single item against a pack of 2. This is how numbers and units get their own fields, as String similarity asked.
Combine the fields with weights
Section titled “Combine the fields with weights”We now have 16 numbers per pair. To decide, we need one score. The simplest way is a weighted sum: multiply each number by a weight, and add up the results.
score = weight₁ × value₁ + weight₂ × value₂ + …
A missing value adds nothing. We chose the weights by hand, before we looked at any result. Strong identifiers count most, the town least:
| Field | Weight | Why |
|---|---|---|
| same email | 3 | one email usually belongs to one person (but families share some) |
| same phone | 2.5 | strong, but landlines are shared |
| same date of birth | 2.5 | strong when both records have it |
| same postcode | 2 | a home, not a person |
| surname (Jaro–Winkler) | 2 | shared by families |
| first name (Jaro–Winkler) | 1.5 | nicknames and initials lower it |
| street (token set) | 1.5 | a home again |
| same house number | 1 | adds to the street |
| same town | 0.5 | thousands of people share it |
The highest possible score is the sum of the weights: 16.5. The project uses 9 of the 16 fields here. The other 7 wait for Module 5, where the data chooses the weights.
Text description of the diagram
For each of the 9 weighted fields: its weight, and for each pair the value, the weight and the points. Valérie’s pair gets 11.80 points: everything except the date of birth (missing) and the postcode (a typing error). The brothers get 13.17 points: everything except the date of birth, with only 0.67 points from their different first names. With a threshold of 8, both pairs are called “the same”. The first decision is right; the second merges two people.
The weighted score cannot separate the brothers. Their first names differ, but the first name has a weight of only 1.5, and every other field agrees. Lowering the weight of shared household fields would hurt all the true pairs that rely on them. Hand weights cannot fix this. We need evidence that knows how often a value is shared: Module 5.
Choose the threshold on validation
Section titled “Choose the threshold on validation”A pair with a score at or above the threshold is called “the same”. Which threshold? The course splits the true customers into three groups: train (60%), validation (20%) and test (20%). We try every possible value on the validation pairs, and keep the one with the best F1. We never choose it on the test pairs. If we did, the test result would be too good: we would have tuned the matcher to the test.
The chart shows precision and recall on the validation pairs at each threshold.
- Precision
- Recall
Text description of the chart
Two lines over thresholds from 0 to 16.5. Precision rises from near 0 at low thresholds to 1.0 above about 11. Recall falls from near 1 to near 0. At the best threshold, 8.0, precision is 0.796 and recall 0.767 on the validation pairs. Just above 8, recall drops to 0.667, because 572 validation pairs score exactly 8.
Why the curve has steps
Section titled “Why the curve has steps”Most values in the vector are 0 or 1, so many pairs get exactly the same score. On the validation pairs, 572 pairs score exactly 8.0, and 542 of them are true pairs. The most common kind (285 pairs) agrees on the phone, postcode, surname and first name, and has no email, date of birth or street. That is 2.5 + 2 + 2 + 1.5 = 8. Most of these are loyalty records, which have no street address.
So a threshold of 8.0 keeps all of them, and a threshold of 8.1 loses all of them. A small change of threshold can move recall by 10 points. Look at the curve before you trust one number.
Run it in the project
Section titled “Run it in the project”Add the rest of this module’s code: the comparison vectors, the weighted run, the threshold tools and the tests.
-
In your project folder, copy the remaining files of the snapshot
end-of-m04, and itsREADME.md:Terminal window S=../nextia-learning-labs/C39/snapshots/end-of-m04cp $S/larkfield_match/vectors.py $S/larkfield_match/thresholds.py larkfield_match/cp $S/larkfield_match/runs/weighted.py larkfield_match/runs/cp $S/larkfield_match/commands/vector.py $S/larkfield_match/commands/threshold.py larkfield_match/commands/cp $S/tests/test_similarity.py tests/cp $S/README.md .Terminal window $S = "..\nextia-learning-labs\C39\snapshots\end-of-m04"Copy-Item $S\larkfield_match\vectors.py, $S\larkfield_match\thresholds.py larkfield_match\Copy-Item $S\larkfield_match\runs\weighted.py larkfield_match\runs\Copy-Item $S\larkfield_match\commands\vector.py, $S\larkfield_match\commands\threshold.py larkfield_match\commands\Copy-Item $S\tests\test_similarity.py tests\Copy-Item $S\README.md .No new package is needed.
-
Run the tests. There are 25 now:
Terminal window python -m pytest -q......................... [100%]25 passed in 15.27s -
Show the comparison vector of Valérie’s pair:
Terminal window match vector G-000001 S-009232G-000001: val tremblay | tremblay.valerie88@example.net | 5145558296 | rue des herons | st eloi du lac | H6P3H7 | -S-009232: valerie tremblay | tremblay.valerie88@example.net | 5145558296 | rue des herons | st eloi du lac | H6P3H1 | 1975-01-31first_jw 0.867last_jw 1.000name_tsr 0.857first_initial 1.000last_sound 1.000email_eq 1.000email_local_jw 1.000phone_eq 1.000postcode_eq 0.000postcode3_eq 1.000street_tsr 1.000number_eq 1.000town_eq 1.000dob_eq missingdob_swap missingdob_year_eq missingTrue answer: sameTry the brothers too:
match vector G-001688 G-002894.
Score and measure every candidate pair
Section titled “Score and measure every candidate pair”match eval --run weightedThe first run builds the vectors of all 1,108,591 candidate pairs and keeps them in outputs/vectors/:
Ran weighted: 25,270 pairs called the same, in 101.3 sweighted on the test split: pair precision 0.751, recall 0.766, F1 0.759 5,673 pairs called the same: 4,263 right, 1,410 wrong; 1,301 of 5,564 true pairs missed entities after clustering (all records): 38,755 (true: 37,500)On a quiet laptop, the same run took 54.8 seconds. On ours, it took 101.3 seconds and used about 0.9 GB of memory. Your time will differ; the counts will not.
To see the trade-off yourself, run match threshold. It prints precision and recall on the validation pairs at a few thresholds:
match thresholdweighted on the validation pairs: threshold precision recall F1 1.843 0.018 0.994 0.035 3.937 0.059 0.971 0.111 6.033 0.336 0.855 0.482 8.000 0.796 0.767 0.782 <- best F1 8.125 0.816 0.667 0.734 10.222 0.934 0.439 0.597 12.319 1.000 0.106 0.191 14.500 1.000 0.004 0.009 16.500 1.000 0.003 0.006How the code works
Section titled “How the code works”larkfield_match/vectors.py builds all vectors at once, one column per field. Here are two of the columns:
v["first_jw"] = _masked(_sim(L["first"], R["first"], JaroWinkler.similarity), L["first"], R["first"])v["phone_eq"] = _eq(L.phone, R.phone)_sim scores all pairs with rapidfuzz in one call. _masked and _eq set the value to NaN (missing) where one of the two records is empty. runs/weighted.py then adds the weighted values, with NaN counted as 0, and thresholds.best_f1 chooses the threshold on the validation pairs.
The comparison table
Section titled “The comparison table”Every module adds rows to one table: the same records, the same test pairs, the same measures. Here is the table so far.
Text description of the chart
Four bars: exact 0.478, exact-norm 0.571, rules 0.790, weighted 0.759.
| 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 |
weighted |
4 | 0.751 | 0.766 | 0.759 |
The weighted score is worse than the four rules of Exact and rule-based matching: F1 0.759 against 0.790. It is a little more precise, and it finds fewer true pairs. Is that a surprise? It should not be. We chose the weights by feel, and the rules were also written by hand, with care. A score with many fields is not better because it has many fields. It is better only when its weights are good, and we have not learned them yet.
The notebook of this lesson shows how fragile hand weights are. It uses only six fields, with the same weights for them. Its best threshold on validation is 5.5. It reaches F1 0.791 on the test pairs, which is better than the project’s 16-field score. Fewer fields gave a better result here only because our guesses happened to fit them better. Hand weights do not tell you which fields help.
Where it fails
Section titled “Where it fails”Run match eval --run weighted --by problem to see recall and precision for each practice problem that we made on purpose:
problem true found recall predicted precision brothers 39 19 0.487 30 0.633 family_email 487 234 0.480 268 0.873 moved 1277 788 0.617 992 0.794 none 3468 3008 0.867 4086 0.736placeholder_phone 13 7 0.538 8 0.875 saint_town 280 207 0.739 289 0.716- Moved customers: recall 0.617. The postcode, street and house number all disagree, so a customer who moved loses 4.5 points of 16.5.
- Family emails: recall 0.480. The family email and the person’s own email differ, so the email adds nothing.
- Brothers: 30 pairs called “the same”, only 19 right. The household fields add up, as in the diagram.
By source (--by source), the worst precision is on pairs inside one source: shop with shop (0.293) and garden centre with garden centre (0.296). These are mostly different people at one address.
When to use a hand-weighted score
Section titled “When to use a hand-weighted score”| Use it when | Do not use it when | What it costs |
|---|---|---|
| you have no labels, and need a first baseline with all fields | you have labels: learn the weights instead (Modules 5 and 6) | an afternoon to choose weights; they are guesses |
| you must explain every decision with simple arithmetic | fields depend on each other (a household shares five fields at once) | the vectors: 1.1 million pairs × 16 fields; under a minute on a laptop |
The comparison vector is the lasting part of this lesson. Every later method reads the same 16 numbers: Fellegi–Sunter and Splink in Module 5, the classifiers in Module 6. Only the way of combining them changes.
Common mistake
Empty equals empty
Symptom: TermTomásLarkfield’s AI engineer. He often suggests a quick shortcut, and the lesson shows why it fails. writes his own email comparison: left.email == right.email. Two garden-centre records with no email give "" == "", which is True. His weighted score rises, and so does the number of pairs called “the same”.
Cause: two empty values are equal as text, but they are not evidence. In 21.4% of the candidate pairs, both records have no date of birth. Both have no phone in 4.2%, and no email in 2.4%. We ran the weighted score with this mistake on four fields: email, phone, date of birth and postcode. Then we chose the threshold again on validation. On the test pairs: precision 0.675, recall 0.501, F1 0.575, against 0.759 without the mistake. The best threshold rises to 10.5, and half of the true pairs are lost. The empty fields give points to pairs of strangers, so the threshold must rise to push them out, and it pushes out true pairs too.
Fix: mark a comparison as missing when either value is empty, as vectors.py does with _eq and _masked. Normalise first, so that “N/A”, “-” and spaces also become empty.
Consequence in AI work: a placeholder value can look like real data. The garden centres typed their own phone number for 40 walk-in customers. That is the same mistake in another form, and Module 7 shows what it does to clusters.
Knowledge check
Section titled “Knowledge check”Check your understanding
Valérie's garden-centre record has no date of birth. Why does the comparison vector say “missing” and not 0 for dob_eq? What would change if it said 0?
Show the answer
0 means “the two records disagree”: evidence that they are two people. Missing means “we cannot compare”: no evidence either way. Valérie’s two records do not disagree on the date of birth; one of them does not have it. If we wrote 0, every pair with a garden-centre record would look less alike, because most garden-centre records have no date of birth. In a score that subtracts weight for a disagreement, as in Module 5, many true pairs would then fall below the threshold.
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
The brothers Samuel and Nathan Green score higher than Valérie's own two records. Which fields give them their points, and why can a better choice of hand weights not fix this?
Show the answer
They share the family email, the landline, the postcode, the surname, the street, the house number and the town: a household shares almost every field. Only the first name differs. To push the brothers below the threshold, we would have to lower the weights of the household fields. But true pairs need those same fields, for example a customer whose two records have a nickname. Hand weights treat every agreement the same. What helps is evidence that knows how common a value is: a shared landline is weak evidence. A disagreement must also count against a match. Module 5 does both.
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
A colleague chooses the threshold of the weighted score on the test pairs and reports F1 0.76. Why is this wrong, even if the number is close to ours?
Show the answer
The test pairs must stay unseen until the end. If we choose the threshold on them, the reported F1 is the best result for those pairs, not an honest estimate for new pairs. On another set of records, the matcher would probably do a little worse. Choose the threshold on the validation pairs, then measure once on the test pairs, as thresholds.best_f1 does. The number may be close here, but the method is what makes it trustworthy.
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 comparison vector is one row of numbers per candidate pair: similarities, agreements (1 or 0), and missing values.
- Missing is not different. An empty value is no evidence. Two empty values are not an agreement.
- Dates, postcodes, numbers and sizes need their own comparisons: equal, swapped, the first 3 characters, a ratio.
- The hand-weighted score adds weight × value. With a threshold of 8, chosen on validation, it reaches precision 0.751, recall 0.766 and F1 0.759 on the test pairs. That is worse than the four rules (0.790).
- Households share almost every field. The brothers score higher than one customer’s own records.
Our weights were guesses. Next, in Module 5, each field’s evidence is measured: how often it agrees for the same customer, and how often by chance.
Next: Evidence from each field