When the match is not exact
Somebody types "Manchestr" into a search box. The search still shows Manchester. A 999 call comes in, and the nearest ambulance is sent.
Neither match is exact. Both are still matches.
Key words
| Word | What it means |
|---|---|
| Fuzzy matching | matching values that are close but not the same |
| Similarity score | a number showing how alike two values are |
| Threshold | the score above which two values count as a match |
| Spatial matching | matching by where things are |
| Coordinates | numbers that give a position on a map or grid |
| Exact match | two values that are identical |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can match inexact text and match records by their position.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Fuzzy matching accepts close values
An exact join fails if one letter differs. Fuzzy matching scores how alike two values are. Close ones count as a match.
A similarity score runs from 0 to 1. You choose a threshold, such as 0.8. Search engines use the same idea to forgive typing mistakes.
Try it: score two spellings
import difflib
for word in ["Manchestr", "Yorkk", "Bath"]:
score = difflib.SequenceMatcher(None, word.lower(), "manchester").ratio()
print(word, round(score, 2))
# Try it: compare against "york" instead.
Try it: fuzzy match in DuckDB
select s.name, t.town, round(jaro_winkler_similarity(lower(s.name), lower(t.town)), 2) as score
from survey_names s
cross join towns t
qualify row_number() over (partition by s.name order by score desc) = 1
order by s.name;
-- Each typed name is paired with its closest town.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.