Back to the lessonLesson 4 of 8Identity and duplication

The same person, twice, under two identifiers

The same deck as the downloads, rendered as a page. Start the slideshow to present it full screen — arrow keys or a click advance one slide, Escape leaves.

Slides · PDF Slides · PowerPoint

Slide 1 / 25
What this lesson covers
- The duplicate with no key
- Start with the attributes that should not coincide
- Look at the false ones before the real ones
- Blocking, and why you cannot skip it
- Normalise before you compare
- Edit distance, and choosing a threshold
- Score several fields, do not chain filters
- The output is a review queue, not a clean table
- What you must not automate
- What comes next
Speaker notes
Record linkage when nothing joins — blocking, normalisation, edit distance and a score. Twelve candidate pairs in the screening register, six of them real, and four manufactured by the missing ages from lesson 2.
Slide 2 / 25
The duplicate with no key
- A child screened in the morning, sent home, and brought back in the afternoon by a different carer is registered twice with two identifiers.
Speaker notes
A child screened in the morning, sent home, and brought back in the afternoon by a different carer is registered twice with two identifiers. So is a household visited by two enumerators on the same street. So is a beneficiary on three distribution lists under three spellings of the same name. None of these share a key. duplicated() will never find them. And they matter: in a caseload figure they inflate the numerator, in a coverage figure they inflate the numerator and not the denominator, and in a beneficiary count they are the thing an audit is specifically looking for. Finding them is record linkage, and it is a different discipline from deduplication. Deduplication removes rows that are identical. Record linkage decides whether two rows that are not identical describe the same thing — and it can be wrong in both directions, which is why the output of this lesson is a list for a human to review rather than a cleaned table.

Slide 3 / 25

Start with the attributes that should not coincide — In Python

CANDIDATE_KEY = ["commune", "screening_date", "age_months", "sex", "muac_mm"]

candidates = (
    muac.groupby(CANDIDATE_KEY, dropna=False)
    .filter(lambda g: g["child_id"].nunique() > 1)
    .sort_values(CANDIDATE_KEY)
)
print(candidates[["child_id"] + CANDIDATE_KEY].to_string(index=False))

Speaker notes

The screening register has no name and no household. What it has is a combination that should be near-unique by accident: commune, date, age, sex and measurement.

Slide 4 / 25

Start with the attributes that should not coincide — In R

CANDIDATE_KEY <- c("commune", "screening_date", "age_months", "sex", "muac_mm")

candidates <- muac |>
  group_by(across(all_of(CANDIDATE_KEY))) |>
  filter(n_distinct(child_id) > 1) |>
  ungroup() |>
  arrange(across(all_of(CANDIDATE_KEY)))

Speaker notes

Twelve groups come back. Six of them are the same child re-registered under a new identifier. Six of them are two different children who happen to match.

Slide 5 / 25

Look at the false ones before the real ones

Commune	Date	Age	Sex	MUAC	Identifiers
Desdunes	2024-01-20	36	f	134	CH03315, CH09241
Gros-Morne	2024-06-10	missing	f	131	CH00576, CH02413
Gros-Morne	2024-06-10	missing	m	123	CH01302, CH04050
Gros-Morne	2024-06-13	missing	m	-99	CH00519, CH00755
Gros-Morne	2024-06-14	missing	m	137	CH01558, CH02913
Verrettes	2024-11-09	26	f	127	CH02193, CH03129

Slide 6 / 25
Look at the false ones before the real ones
- A blocking column with missing values manufactures matches — Either exclude missing rows from the block or block on…
Speaker notes
Four of the twelve are from Gros-Morne, in the week beginning 10 June, and every one of them has a missing age. That is the same week whose form was misconfigured — the missingness from lesson 2 arriving in a different lesson wearing a different hat. Here is why it happens. Grouping on a column that is missing makes every missing row match every other missing row on that column. The block collapses: instead of comparing children of the same age, you are comparing all children of unknown age. In a commune screening eighty-five children in a week, two boys with the same MUAC to the millimetre is not surprising at all. A blocking column with missing values manufactures matches. Either exclude missing rows from the block or block on something else — never let a hole count as an agreement.
Slide 7 / 25
Look at the false ones before the real ones — In Python
```
blocked = muac[muac["age_months"].notna() & muac["muac_mm"].notna()]
```
Slide 8 / 25
Look at the false ones before the real ones — In R
```
blocked <- muac |> filter(!is.na(age_months), !is.na(muac_mm))
```
Speaker notes
Do that and eight candidates remain: the six real re-registrations and two genuine coincidences in Verrettes and Saint-Marc. Eight is a list a supervisor can check against the paper register in an afternoon. Twelve, four of them nonsense, is a list that teaches people to ignore the list.
Slide 9 / 25
Blocking, and why you cannot skip it
- Blocking — means only comparing records that already agree on something cheap and reliable — the commune, the…
Speaker notes
Comparing every row against every other row is quadratic. On 4,218 records that is 8.9 million pairs, which is slow but survivable. On a 300,000-record beneficiary registry it is 45 billion, which is not. Blocking means only comparing records that already agree on something cheap and reliable — the commune, the distribution site, the month, the first letter of the surname. You then do expensive comparisons inside each block only.

Slide 10 / 25

Blocking, and why you cannot skip it — In Python

blocks = blocked.groupby(["commune", "sex"])
print(sum(len(g) * (len(g) - 1) // 2 for _, g in blocks), "pairs to compare")

Slide 11 / 25
Blocking, and why you cannot skip it — In R
```
blocked |>
  count(commune, sex) |>
  summarise(pairs = sum(n * (n - 1) / 2))
```
Speaker notes
The trade is explicit: a block that is too coarse is slow, and a block that is too fine misses the pairs that disagree on the blocking column. Two spellings of a village name in different blocks will never be compared. That is why the blocking column should be the one you trust most, and why blocking on a free-text field is usually wrong.

Slide 12 / 25

Normalise before you compare — In Python (cont.)

import re
import unicodedata


def normalise(series):
    return (
        series.fillna("")
        .str.normalize("NFKD")
        .str.encode("ascii", "ignore").str.decode("ascii")
        .str.lower()
        .str.replace(r"[^a-z0-9 ]", " ", regex=True)
        .str.replace(r"\s+", " ", regex=True)
        .str.strip()
    )

Speaker notes

For anything involving text, most of the work is done before any distance is computed.

Slide 13 / 25

Normalise before you compare — In Python (cont.)

households["head_key"] = normalise(households["head_of_household"])

Slide 14 / 25

Normalise before you compare — In R

normalise <- function(x) {
  x |>
    tidyr::replace_na("") |>
    stringi::stri_trans_general("Latin-ASCII") |>
    tolower() |>
    stringr::str_replace_all("[^a-z0-9 ]", " ") |>
    stringr::str_squish()
}

households$head_key <- normalise(households$head_of_household)

Slide 15 / 25
Normalise before you compare
- Strip accents for the comparison key only, never in the data you keep — Étienne is the person's name
Speaker notes
Case, accents, double spaces and punctuation account for the large majority of "different" names in this sector's data. Strip them and a surprising share of your fuzzy matching problem becomes exact matching. Strip accents for the comparison key only, never in the data you keep. Étienne is the person's name. etienne is an index.
Slide 16 / 25
Edit distance, and choosing a threshold — In Python
```
from rapidfuzz import fuzz, process

matches = process.extract(
    "jean baptiste pierre",
    households["head_key"].tolist(),
    scorer=fuzz.token_sort_ratio,
    score_cutoff=88,
    limit=10,
)
```
Speaker notes
For what survives normalisation, compare with a string distance. Any of the standard ones will do; what matters is that you pick a threshold deliberately and say what it was.
Slide 17 / 25
Edit distance, and choosing a threshold — In R
```
scores <- stringdist::stringsim(
  "jean baptiste pierre",
  households$head_key,
  method = "jw"
)
which(scores > 0.88)
```
Speaker notes
token_sort_ratio and Jaro-Winkler are both reasonable defaults here for a reason worth knowing: names in this sector arrive with their parts reordered (Pierre Jean Baptiste against Jean Baptiste Pierre) and with a typo in the first few characters far less often than in the last few. The threshold is a precision-recall dial, not a setting. At 95 you get few pairs and miss real duplicates. At 80 you get many pairs, most of them wrong, and the reviewer stops reading. Tune it by taking a sample of fifty pairs at your proposed cut-off and checking how many are real. Write the number you chose and the hit rate you measured into the cleaning log.

Slide 18 / 25

Score several fields, do not chain filters — In Python

def pair_score(a, b):
    name = fuzz.token_sort_ratio(a["head_key"], b["head_key"]) / 100
    same_site = 1.0 if a["community"] == b["community"] else 0.0
    size_gap = abs(a["household_size"] - b["household_size"])
    size = max(0.0, 1 - size_gap / 5)
    return 0.6 * name + 0.25 * same_site + 0.15 * size

Speaker notes

One field is never enough. Compare a handful and combine them, so that a strong agreement on one field can offset a weak one on another.

Slide 19 / 25
Score several fields, do not chain filters — In R
```
pair_score <- function(a, b) {
  name      <- stringdist::stringsim(a$head_key, b$head_key, method = "jw")
  same_site <- as.numeric(a$community == b$community)
  size      <- pmax(0, 1 - abs(a$household_size - b$household_size) / 5)
  0.6 * name + 0.25 * same_site + 0.15 * size
}
```
Speaker notes
The weights are a judgement, and they should be visible rather than buried in a chain of and conditions. A chained filter treats every criterion as absolute: one typo in the community name and the pair is gone. A score lets the evidence add up, which is how a human reviewer reasons about it anyway.

Slide 20 / 25

The output is a review queue, not a clean table — In Python

review = (
    pairs.sort_values("score", ascending=False)
    .loc[:, ["id_a", "id_b", "score", "head_a", "head_b", "community", "size_gap"]]
    .assign(decision="", reviewer="", reviewed_on="")
)
review.to_csv("outputs/review/duplicate-candidates.csv", index=False)

Speaker notes

This is the part that separates a linkage that helps from one that causes an incident.

Slide 21 / 25

The output is a review queue, not a clean table — In R

review <- pairs |>
  arrange(desc(score)) |>
  select(id_a, id_b, score, head_a, head_b, community, size_gap) |>
  mutate(decision = "", reviewer = "", reviewed_on = "")

readr::write_csv(review, here::here("outputs", "review", "duplicate-candidates.csv"))

Slide 22 / 25
The output is a review queue, not a clean table
A duplicate you merged is a record you destroyed. If the merge was wrong, the evidence that it was wrong went with it.
Speaker notes
Three empty columns, and they are the point. The file goes to whoever holds the register, comes back with a decision on every row, and that returned file is what the cleaning script reads — not the score, and not a threshold applied automatically.
Slide 23 / 25
What you must not automate
- A false merge removes someone. Two households become one; one of them stops receiving assistance and has no way to…
- A false split double-counts. Costly and embarrassing, and it does not deprive anyone of anything.
Speaker notes
In beneficiary, protection and case management data, automatic merging is not a technical shortcut with a small error rate. It is a decision about a person, and the two failure modes are not symmetric. The asymmetry means the default is to under-merge. Flag, review, and let the person who holds the register decide. Where a merge does happen, keep both original identifiers in the merged record so it can be undone — a linkage table with kept_id, merged_id, score, reviewer and date beside the data. None of this is caution for its own sake. The GBV information management principles that govern part of this sector's data make the point directly: the harm from mishandling a record falls on the person in it, not on the analyst.
Slide 24 / 25
What comes next
- You now know which rows are the same thing and which columns identify them.
Speaker notes
You now know which rows are the same thing and which columns identify them. The next unit turns to the values themselves — the measurements that cannot be true, the outcome codes that contradict their own measurement, and the sector thresholds that turn "that looks wrong" into a rule a script can apply.
Slide 25 / 25
Where this goes next
Read the full lesson, with runnable code Back to the lesson

What this lesson covers

The duplicate with no key

Start with the attributes that should not coincide — In Python

Start with the attributes that should not coincide — In R

Look at the false ones before the real ones

Look at the false ones before the real ones

Look at the false ones before the real ones — In Python

Look at the false ones before the real ones — In R

Blocking, and why you cannot skip it

Blocking, and why you cannot skip it — In Python

Blocking, and why you cannot skip it — In R

Normalise before you compare — In Python (cont.)

Normalise before you compare — In Python (cont.)

Normalise before you compare — In R

Normalise before you compare

Edit distance, and choosing a threshold — In Python

Edit distance, and choosing a threshold — In R

Score several fields, do not chain filters — In Python

Score several fields, do not chain filters — In R

The output is a review queue, not a clean table — In Python

The output is a review queue, not a clean table — In R

The output is a review queue, not a clean table

What you must not automate

What comes next

Where this goes next