The same person, twice, under two identifiers

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

Start with the attributes that should not coincide

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.

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))

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)))

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.

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

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.

blocked = muac[muac["age_months"].notna() & muac["muac_mm"].notna()]

blocked <- muac |> filter(!is.na(age_months), !is.na(muac_mm))

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.

Blocking, and why you cannot skip it

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.

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

blocked |>
  count(commune, sex) |>
  summarise(pairs = sum(n * (n - 1) / 2))

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.

Normalise before you compare

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

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()
    )


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

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)

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.

Edit distance, and choosing a threshold

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.

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,
)

scores <- stringdist::stringsim(
  "jean baptiste pierre",
  households$head_key,
  method = "jw"
)
which(scores > 0.88)

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.

Score several fields, do not chain filters

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.

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

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
}

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.

The output is a review queue, not a clean table

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

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)

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"))

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.

A duplicate you merged is a record you destroyed. If the merge was wrong, the evidence that it was wrong went with it.

What you must not automate

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.

• A false merge removes someone. Two households become one; one of them stops receiving assistance and has no way to find out why. In a protection caseload, two survivors become one file, and one person’s history is attached to another person’s name.
• A false split double-counts. Costly and embarrassing, and it does not deprive anyone of anything.

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.

What comes next

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.