Meet the records
- Lesson 3
- 40 min
- Module 1: The matching problem
Where we are
In the last two lessons, we saw why Larkfield counts too many customers, and what a false merge and a missed match each cost. So far, we only read records and results that someone else picked. In this lesson, we set up the project, open the three customer lists ourselves, list the kinds of differences, and run the email join on our own computer. The clinic’s new clerk starts in the same way: she reads the files before she joins any.
Lesson 3 of 29 · Entity Resolution and Record Matching: Is This the Same Thing?Meet the records
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
Set up the course project on your computer, and check that it works. Then read 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 three customer lists yourself, list the kinds of differences between records of one customer, and run your first matcher.
NotebookDo the data part of this lesson in a notebook
The notebook reads the three customer lists and the supplier feeds with pandas. It shows one customer with three records. Then you compute the number of records per customer, and the share of customers in more than one list. Its setup cells download the data, so it needs no local setup. The project setup below needs your own computer.
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
- Entities, records, true pairs, and the three lists, from Three lists, one customer.
- False merges, missed matches, pair precision and pair recall, from False merges and missed matches.
- How to open a terminal and run a command. A virtual environment is a folder with its own Python packages for one project. If this is new for you, read Python for Practical AI Engineering first.
What you need on your computer
- Python 3.12. Check it with
python3.12 --versionon macOS and Linux, orpy -3.12 --versionon Windows. - A code editor, such as VS Code, and a terminal.
- Git, to download the course files. Without Git, you can download a ZIP file instead (step 1).
- About 200 MB of free disk space for the project, its packages and the data.
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 new clerk’s first day
Section titled “The new clerk’s first day”Think of the clinic again. It hires a new clerk to clean up the patient files. On her first day, she does not join any files. First, she opens the three filing cabinets and reads. Which cabinet keeps the date of birth? Which one writes names in capitals? What mistakes come up again and again?
Only then can she plan the work. And she keeps one rule from the start: she never writes on the original files. She works on copies, so that every mistake can be undone.
You do the same in this lesson. You set up the project, read the records, and list the kinds of differences. You also learn the commands that keep the data safe.
The steps of a matcher
Section titled “The steps of a matcher”Every matcher in this course follows the same steps. The picture shows them, and the module that teaches each one.
Text description of the diagram
The diagram has nine boxes, in two rows. It starts with the three customer lists: 60,108 records, as people typed them. Normalise writes each value in one way (Module 2). Block chooses the pairs of records that are worth comparing (Module 3). Compare computes a similarity for each field (Module 4). Score gives each pair a number for “same or not” (Modules 5 and 6). Then cluster joins the pairs into customers, and the golden record keeps one best value for each field (both Module 7). Review sends the unsure cases to a person (Module 8). The result is one list of customers, which every module measures against the true customers.
The email join that you run today is the simplest matcher. It skips normalising, blocking and comparing: two records are the same when their emails are exactly equal. Every later module replaces or adds one step, and measures the result on the same test pairs.
Set up the project
Section titled “Set up the project”The project is larkfield-match: Larkfield’s record matching as a small Python program, with the match command. You copy its first version, the snapshot start, from the course’s labs repository. Later modules add files to it, one module at a time.
-
Get the course files. If you have Git, open a terminal and clone the labs repository once, in a folder such as
~/projects. If you have the clone already, rungit -C nextia-learning-labs pullinstead.Terminal window cd ~/projectsgit clone https://github.com/Nextia-Intelligence/nextia-learning-labs.gitWithout Git, download the ZIP file of the repository, and unzip it in
~/projects. Its folder is thennextia-learning-labs-main. Use that name in the next steps. -
Copy the snapshot
startto a new folderlarkfield-match. Then copy the course data into it, asdata:Terminal window cp -R nextia-learning-labs/C39/snapshots/start larkfield-matchcp -R nextia-learning-labs/C39/data larkfield-match/datacd larkfield-matchTerminal window Copy-Item -Recurse nextia-learning-labs\C39\snapshots\start larkfield-matchCopy-Item -Recurse nextia-learning-labs\C39\data larkfield-match\datacd larkfield-match -
Open the folder
larkfield-matchin VS Code. You seelarkfield_match/(the code),tests/,data/,README.mdandrequirements.txt. -
Make 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, and install the packages and the project. This module needs only NumPy, pandas and pytest.
Terminal window python3.12 -m venv .venvsource .venv/bin/activatepython -m pip install -r requirements.txt -e .Terminal window py -3.12 -m venv .venv.venv\Scripts\Activate.ps1python -m pip install -r requirements.txt -e .The
-e .part installs the project itself, so thematchcommand works in this terminal. On Windows, if PowerShell says that running scripts is disabled, runSet-ExecutionPolicy -Scope CurrentUser RemoteSignedonce. Then activate again. -
Run the setup check:
Terminal window match checkData: 30 files, checksums right (data)exact on the test split: pair precision 0.817 (expected 0.817), recall 0.338 (expected 0.338)exact makes 50,367 entities (expected 50,367)Setup check passed.The check compares every data file with its checksum, a short code computed from the file’s bytes. A changed file gives a different checksum. Then it runs the email join and measures it. These are the numbers of the last lesson.
-
Run the tests. They use a tiny made-up data folder, so they take less than a second:
Terminal window python -m pytest -q........... [100%]11 passed in 0.47s
If a step fails, read When the setup check fails below.
Three lists, three shapes
Section titled “Three lists, three shapes”Start with match info. It counts what is in the data:
match infoCustomer records (synthetic): 60,108 in 3 sources: shop accounts 31,128, loyalty members 17,037, garden centres 11,943True customers: 37,500 (1.60 records per customer); 17,936 customers have more than one record, the largest has 5 shop accounts: first_name, last_name, email, phone, address, city, province, postcode, date_of_birth, created loyalty members: full_name, email, phone, postcode, date_of_birth, joined, points garden centres: name, phone, address, email, date_of_birth, first_visitSupplier feeds (synthetic): Northfield Supply 4,460 products, Copperleaf Wholesale 3,501 products; 2,750 products are in both feedsLabelled pairs: 3,000 (1,050 same, 1,950 different)Look at the three lines of column names. Each list has its own shape, because each team built its own system. The shop keeps first and last name apart. The loyalty programme keeps one full name, no street address, and writes dates as day/month/year. The garden centres’ old till writes “SURNAME, FIRST” in capitals, and the whole address in one field. The diagram shows how the project reads them.
Text description of the diagram
Three source files lead to one table. The shop file has 31,128 records, with IDs that start with S. The loyalty file has 17,037 records, with IDs that start with L. The garden-centre file has 11,943 records, with IDs that start with G. The project puts them into one table, with the same columns for every record. The columns are record ID, source, name, email, phone, address, postcode and date of birth. It changes no value: a name in capitals stays in capitals. A fourth file, truth.csv, gives the true customer of every record, and its split: train, validation or test. A dotted arrow shows that the project uses it only to measure a matcher, never to decide a match.
The letter at the start of a record ID tells you the list: S for the shop, L for loyalty, G for the garden centres. The chart uses these letters to show in which lists each true customer appears.
- In one list
- In two or three lists
Text description of the chart
A column chart with seven columns. 14,256 customers are only in the shop list (S). 7,735 are in the shop and loyalty lists (S+L). 4,242 are in the shop and garden-centre lists (S+G). 3,939 are only in the loyalty list (L). 3,383 are in all three lists (S+L+G). 2,215 are only in the garden-centre list (G). 1,730 are in the loyalty and garden-centre lists (L+G). The four columns of customers in two or three lists add up to 17,090.
So 17,090 customers, 45.6%, are in more than one list. That is the record-linkage work. A few customers also have two records in one list, for example two shop accounts: that is the deduplication work. The notebook computes the 45.6% with pandas.
Values that are not there
Section titled “Values that are not there”A matcher can only compare values that both records have. The chart shows how often each list leaves two useful fields empty: the email and the date of birth. The phone is empty less often: in 30.1% of shop records, 7.8% of loyalty records and 14.1% of garden-centre records.
- Shop
- Loyalty
- Garden centres
Text description of the chart
A column chart with six columns in three colours, one colour per list, on an axis from 0 to 100%. The shop: email empty 0%, date of birth 54.6%. Loyalty: email 11.9%, date of birth 3.0%. Garden centres: email 59.5%, date of birth 84.7%.
The garden centres are the hardest list. Most of their records have no email and no date of birth, so a matcher must rely on the name, the phone and the address. An empty value is not a disagreement: “no date of birth” does not mean “another person”. Module 4 deals with this.
One customer, several records
Section titled “One customer, several records”match show prints a record and every other record of the same true customer. Look at Valérie Tremblay from the first lesson:
match show G-000001Entity E-08582 (train split): 3 record(s) G-000001 garden | TREMBLAY, VAL | tremblay.valerie88@example.net | 5145558296 | 6760, r. des Hérons, Saint-Éloi-du-Lac QC H6P3H7 | - | - L-010072 loyalty | Valérie Tremblay | tremblay.valerie88@example.net | 5145558296 | - | H6P 3H7 | 31/01/1975 S-009232 shop | Valérie Tremblay | tremblay.valerie88@example.net | +1 514 555 8296 | 6760, r. des Hérons, Saint-Éloi-du-Lac QC | H6P 3H1 | 1975-01-31Each line is one record: the ID, the list, then name, email, phone, address, postcode and date of birth. A dash means an empty value. The first line says that the true customer is E-08582, and that it belongs to the train split. The course keeps the test split for measuring, as you know from the machine learning course.
Some records carry a practice problem that we made on purpose. match show names it. Look at Samuel Green, one of the brothers of the last lesson:
match show G-001688Entity E-05554 (test split): 4 record(s) G-001688 garden | GREEN, SAMUEL | thegreens@example.com | 9055551835 | 3294 KESTREL AVENUE, HERONVALE ON M7K 4L8 | - | - G-008438 garden | GREEN, SAMUEL | - | 5195554060 | 6659 ORCHARD LANE, STONEWICK ON L0H 6T9 | - | - L-011126 loyalty | Samuel Green | Thegreens@Example.com | 9055551835 | - | M7K 4L8 | 14/03/1966 S-007770 shop | Samuel Green | Samuel.Green@Mail.example | - | 3294 Kestrel Avenue, Heronvale ON | M7K 4L8 | 1966-03-14Practice problems we made on purpose: brothers, movedSamuel has four records, two of them in the garden-centre list. His own records disagree: the family email with and without capitals, a personal email, and another address in Stonewick. His brother Nathan’s records are not here, because Nathan is another entity.
The kinds of differences
Section titled “The kinds of differences”These are the differences that the course data has, each with a real pair of one customer. Check any of them with match show and the first record ID.
| Kind of difference | Example (records of one customer) | Records |
|---|---|---|
| Another format | +1 514 555 8296 and 5145558296; 31/01/1975 and 1975-01-31 |
S-009232, L-010072 |
| Capitals and spaces | Thibault.Helene93@Example.net and thibault.helene93@example.net; " ryan.campbell81@example.com " with spaces |
S-028040, L-000482; L-007742 |
| Missing accents | Geneviève Côté and Genevieve Cote |
L-010673, S-009281 |
| Short name or nickname | TAYLOR, MATTHEW and Matt Taylor |
G-011133, S-008399 |
| Names swapped | Thibault Helene and Hélène Thibault |
L-000482, S-028040 |
| Typing error | Nathalie Gagnon and Nathalie Gagnoj; mariepaqquette@… and mariepaquette@… |
L-007451, S-011570; L-016234, S-026828 |
| Short street words | 9225 Tamarack St. and 9225 Tamarack St; r. for rue |
G-011133, S-008399 |
| An old address (moved) | 234 BOULEVARD DES LILAS … J8Y 1L3 and 8147 r. Sainte-Cécile … J0C-6V5 |
G-009906, S-005041 |
| Another email | the family’s thebouchards@example.org and her own cbouchard@post.example |
L-014546, S-022315 |
| An empty value | no date of birth, no email | G-000001 |
And the opposite trap, where two different people look alike:
| Looks alike | Example (records of two people) | Records |
|---|---|---|
| Almost the same name | Marc Fortin and Marie Fortin |
S-014459, S-016782 |
| One family: same address, phone and email | GREEN, SAMUEL and GREEN, NATHAN |
G-001688, G-002894 |
Every kind in the first table comes from the error model of the data generator. The dataset card, data/dataset-card.md, lists how often each one happens. For example, 10% of first names are nicknames, and 35% of shop records have an old address. These shares are choices of ours, not facts about real shops.
Run the email join
Section titled “Run the email join”The project calls one complete matcher a run. match score --list shows the runs that the project has now:
match score --list exact customers same email, exactly as typedmatch eval runs a matcher and measures it on the test split:
match eval --run exactexact on the test split: pair precision 0.817, recall 0.338, F1 0.478 2,300 pairs called the same: 1,879 right, 421 wrong; 3,685 of 5,564 true pairs missed entities after clustering (all records): 50,367 (true: 37,500)You know the first two lines from the last lesson. F1 is one number that combines precision and recall: it is high only when both are high. The machine learning course explains it in Classification metrics.
The last line counts customers. To turn pairs into customers, the project clusters the pairs. If record A matches B, and B matches C, then A, B and C are one customer. Module 7 explains clustering. match cluster shows the largest groups:
match cluster --run exactexact: 60,108 records -> 50,367 entities (true: 37,500)Largest clusters: L-008538 (5 records), L-004017 (5 records), L-003697 (5 records)Saved to outputs/clusters/exact.csvThis is the first row of the course’s comparison table. Every later module adds rows to it, measured on the same 5,564 test pairs in the same way:
| Run | Module | Precision | Recall | F1 |
|---|---|---|---|---|
exact |
1 | 0.817 | 0.338 | 0.478 |
Label pairs yourself
Section titled “Label pairs yourself”Before any method decides for you, decide yourself. match sample writes 40 pairs of records for you to label. The pairs come from the train split, and the two records in each pair have surnames that start with the same four letters.
match sampleWrote 40 pairs to outputs/to_label.csv. Fill 'label' with same, different or unsure, and a reason.Then check your labels with: match sample-check-
Open
outputs/to_label.csvin VS Code. Each row has the two records side by side:name_landname_r,email_landemail_r, and so on. -
For each row, fill the column
labelwithsame,differentorunsure. -
For each
unsurerow, write in the columnreasonwhat you would need to know to decide. -
Save the file, and check your labels:
Terminal window match sample-check
When we labelled these 40 pairs, we said unsure for three of them and different for the rest. Here is the result:
Labelled 37 pairs as same or different: 37 agree with the true entities; 3 unsure; 0 empty.True pairs in the sample: 0 of 40None of the 40 pairs is one customer. Two records with a similar surname are almost always two people. Two of our unsure pairs were women with the same first name and another surname: a married name? The third was the same name with another email, phone and province: a move? All three were two people.
This is the last lesson’s point, seen from the inside. Most pairs that look a little alike are different people. A matcher that merges on a weak sign, such as the surname, makes many false merges.
Try it yourself
Section titled “Try it yourself”Run match sample --seed 9, and label the new 40 pairs. This sample has one true pair. Can you find it before you check? Then use match show with one of its record IDs, and name the kinds of differences between its records.
Check and reset
Section titled “Check and reset”Two commands keep the project in a known state:
match checkchecks the data files and measures the email join.match resetdeletesoutputs/and checks the data files.outputs/holds everything that the project writes: pairs, clusters and, later, prepared records and models. Your labels inoutputs/to_label.csvare deleted too.
match resetReset: removed 3 files from outputs/; data: 30 files, all files are right.The project never changes the files in data/. Like the clinic’s clerk, it works on copies. Matchers write pairs and clusters to outputs/, and the source records stay as they are. If a data file changes by mistake, copy the course data again from the labs repository.
When the setup check fails
Section titled “When the setup check fails”match check stops with a message when something is wrong. Read its last line, and find it here:
| Message | Cause | Fix |
|---|---|---|
No course data found. Put the course data folder next to the project as data/, or set LARKFIELD_DATA to its path. |
data/ is missing, or you are in the wrong folder |
cd into larkfield-match, and copy the data again (step 2) |
These data files are missing or changed: sources/loyalty_members.csv. Download the course data again. |
a data file was changed or deleted | copy that file again from nextia-learning-labs/C39/data/, then run match reset |
command not found: match |
the virtual environment is not active in this terminal | activate it again (step 4) |
ModuleNotFoundError: No module named 'pandas' |
the packages are not installed in this environment | run python -m pip install -r requirements.txt -e . again |
Setup check failed: see the lesson's troubleshooting list. |
the email join gave other numbers: the code was changed | run match reset; if it still fails, copy the snapshot start again |
Common mistake
Clean a data file by hand
Symptom: you open data/sources/loyalty_members.csv in a spreadsheet program, and you fix a few names that look wrong. You save the file. Later, match check stops:
These data files are missing or changed: sources/loyalty_members.csv. Download the course data again.Cause: the change made the file’s checksum different, so the project refuses to measure. Saving in a spreadsheet can change a file even when you change nothing: line endings, quotes, or a date format. And the change itself is a problem: you changed the evidence. A measurement on changed data cannot be compared with anyone else’s. Your “fix” may also be wrong: “Thibault Helene” looks like a typing error, but it is a real record with swapped names.
Fix: copy the original file from nextia-learning-labs/C39/data/ back to data/. Then run match reset. It must end with “all files are right”. To look at the data, use match info, match show or the notebook. They read the files and never write them.
Consequence in AI work: never clean the source records in place. Matching keeps every source record as it was, and puts the cleaned values next to it. Then every merge can be explained, and undone. Module 2 builds the cleaned values in this way.
Knowledge check
Section titled “Knowledge check”Check your understanding
Name four kinds of difference between Valérie Tremblay's three records, and say for each one whether it means “another person”.
Show the answer
- Another format:
+1 514 555 8296and5145558296,31/01/1975and1975-01-31. The same value, so not another person. - A short name, capitals and no accent:
TREMBLAY, VALagainstValérie Tremblay. The same person. - A typing error: the postcode
H6P 3H1againstH6P 3H7. One wrong character, not another person. - Empty values: no address in the loyalty record, no date of birth in the garden-centre record. An empty value is not a disagreement. None of them means another person. But almost the same name, as Marc and Marie Fortin, can be two people, so a matcher cannot ignore small differences.
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
Why do the garden-centre records make matching harder than the loyalty records? Use the chart of empty values in your answer.
Show the answer
The garden centres leave the email empty in 59.5% of their records, and the date of birth in 84.7%. The loyalty list leaves them empty in only 11.9% and 3.0%. So for most garden-centre records, a matcher has only the name, the phone and the address. And these fields have the most differences: names in capitals without accents, short first names, and old addresses. In the error model, 60% of garden-centre records have an old address. Fewer fields to compare, and more noise in them.
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
You labelled 40 sampled pairs, and none of them was one customer. Tomás says: “Then the sample is broken.” What do you answer?
Show the answer
The sample is not broken. It chose pairs whose surnames start with the same four letters, and most such pairs are two different people. This is the normal situation in matching: true pairs are rare. Even among records that look a little alike, most pairs are different people. So a sign such as a similar surname is weak evidence, and a matcher that merges on it makes many false merges. With match sample --seed 9, one of the 40 pairs is a true pair.
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
- The project
larkfield-matchis set up: the snapshotstart, the data indata/, a virtual environment, andmatch checkends with “Setup check passed.” The 11 tests pass. - The three lists have different shapes. The project reads them into one table and changes no value.
truth.csvis only used to measure. - 45.6% of the true customers are in more than one list. The garden centres leave the email empty in 59.5% of their records and the date of birth in 84.7%.
- Records of one customer differ in many ways: formats, capitals, accents, short or swapped names, typing errors and old addresses. Some values are empty. And different people can still look alike.
exactgives precision 0.817 and recall 0.338 on the test split, and 50,367 customers: the first row of the comparison table.match resetdeletesoutputs/and checks the data, which never changes.
You can now run the project and read the records. But many differences in the table are only different ways to write the same value. Next, you normalise the values, so that +1 514 555 8296 and 5145558296 become equal, and you measure how much that alone helps.
Next: Normalise