Skip to content
Français

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.

Plays from YouTube. Nothing loads from YouTube until you press play. Privacy

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

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

What you need to know

What you need on your computer

  • The project larkfield-match with 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 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.

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.

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:

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

Two comparison vectors with 16 fields each. Valérie Tremblay, G-000001 and S-009232, one customer: first name 0.867, surname 1, full name 0.857, first initial 1, surname sound 1, email 1, email before @ 1, phone 1, postcode 0, first 3 of postcode 1, street 1, house number 1, town 1, date of birth missing, day and month swapped missing, year of birth missing. Samuel and Nathan Green, G-001688 and G-002894, two brothers: first name 0.444, surname 1, full name 0.588, first initial 0, surname sound 1, email 1, email before @ 1, phone 1, postcode 1, first 3 of postcode 1, street 1, house number 1, town 1, date of birth missing, swapped missing, year missing.
The comparison vectors of two pairs. The brothers agree on more fields than Valérie's own two records. Only the first name separates them.
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.

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_swap is 1 when the day and month are swapped (31/01 against 01/31). dob_year_eq is 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_ratio and price_ratio: the smaller value divided by the larger. 12 inches against 16 inches gives 0.75, and two equal sizes give 1. It also has pack_eq, for a single item against a pack of 2. This is how numbers and units get their own fields, as String similarity asked.

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.

The hand-weighted score of the two pairs. Valérie Tremblay: email 1 × 3 = 3, phone 1 × 2.5 = 2.5, date of birth missing → 0, postcode 0 × 2 = 0, surname 1 × 2 = 2, first name 0.867 × 1.5 = 1.30, street 1 × 1.5 = 1.5, house number 1 × 1 = 1, town 1 × 0.5 = 0.5; total 11.80, at least 8, called the same, right. Samuel and Nathan Green: email 3, phone 2.5, date of birth missing → 0, postcode 2, surname 2, first name 0.444 × 1.5 = 0.67, street 1.5, house number 1, town 0.5; total 13.17, at least 8, called the same, wrong.
Weight × value, added up, for the two pairs. Both pass the threshold of 8. The brothers score higher than Valérie's own records.
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.

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
Precision and recall of “score ≥ threshold” on the validation pairs of our synthetic records. The best F1 is at a threshold of 8. Just above 8, recall falls by 10 points.
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.

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.

Add the rest of this module’s code: the comparison vectors, the weighted run, the threshold tools and the tests.

  1. In your project folder, copy the remaining files of the snapshot end-of-m04, and its README.md:

    Terminal window
    S=../nextia-learning-labs/C39/snapshots/end-of-m04
    cp $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 .

    No new package is needed.

  2. Run the tests. There are 25 now:

    Terminal window
    python -m pytest -q
    ......................... [100%]
    25 passed in 15.27s
  3. Show the comparison vector of Valérie’s pair:

    Terminal window
    match vector G-000001 S-009232
    G-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-31
    first_jw 0.867
    last_jw 1.000
    name_tsr 0.857
    first_initial 1.000
    last_sound 1.000
    email_eq 1.000
    email_local_jw 1.000
    phone_eq 1.000
    postcode_eq 0.000
    postcode3_eq 1.000
    street_tsr 1.000
    number_eq 1.000
    town_eq 1.000
    dob_eq missing
    dob_swap missing
    dob_year_eq missing
    True answer: same

    Try the brothers too: match vector G-001688 G-002894.

Terminal window
match eval --run weighted

The 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 s
weighted 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:

Terminal window
match threshold
weighted 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.006

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.

Every module adds rows to one table: the same records, the same test pairs, the same measures. Here is the table so far.

F1 of each run on the test split of our synthetic records. The hand-weighted score (orange) is a second baseline. It does not beat the four rules of Module 2.
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.

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.736
placeholder_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.

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.

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.

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.

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.

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