Resolve Entities Across Sources
The same real-world entity appears as "Apple", "Apple Inc.", "AAPL", and "苹果" across sources. Match on identifiers and controlled keys, not on display names, or your join silently splits and duplicates entities.
Procedure
- Prefer an authoritative identifier over any name string: company → LEI or CIK; paper → DOI; researcher → ORCID; place → ISO 3166 / GeoNames id; person → national id where lawful; product → GTIN.
- Normalise the fallback key: Unicode NFKC, casefold, strip legal suffixes (
Inc, Ltd, GmbH, Co), collapse punctuation, expand common abbreviations (Corp → Corporation).
- Build candidate pairs by blocking on a cheap key (first token, country, first author), never all-pairs.
- Score candidates with string similarity, then set a two-threshold policy:
- score ≥ 0.92 → auto-merge
- 0.75 ≤ score < 0.92 → human-queue
- below 0.75 → not the same
from difflib import SequenceMatcher
s = SequenceMatcher(None, norm(a), norm(b)).ratio()
- Resolve, don't delete. Assign a canonical id and keep every alias; a mapping table (
alias → canonical_id) beats overwriting rows.
- Handle one-to-many and many-to-one: an acquired subsidiary and its parent, a paper with two versions, a person with two affiliations.
- Record unresolved entities as their own cluster and report their count — do not force-merge them.
# gather all distinct spellings before matching
cut -d, -f2 entities.csv | sort | uniq -c | sort -rn | head -30
Pitfalls
- Fuzzy-matching on names alone merges distinct people ("J. Smith") and splits variants ("Jon", "Jonathan").
- The same name across languages ("Apple" vs "苹果") needs a cross-lingual link table, not string distance.
- Identifiers can be recycled or reassigned; check the id's validity date when sources span years.
- A shared address or phone is weak evidence — shell companies and families share them.
- Merging then joining loses the ability to audit; always keep the alias map.
Verification
python3 -c "import pandas as pd; print(pd.read_csv('alias_map.csv').canonical_id.nunique(), 'canonical entities')"
Report: "1,204 raw name strings resolved to 817 canonical entities via LEI (93%) + similarity; 41 borderline pairs queued for review; 0 force-merges."
1---2name: resolve-entities-across-sources3description: Use when joining records about the same company, person, or paper across datasets. Maps to stable identifiers before comparing or counting.4metadata:5 source: https://skillmd.com/skills/jiayaoqijia/resolve-entities-across-sources6---78# Resolve Entities Across Sources910The same real-world entity appears as "Apple", "Apple Inc.", "AAPL", and "苹果" across sources. Match on identifiers and controlled keys, not on display names, or your join silently splits and duplicates entities.1112## Procedure13141. Prefer an authoritative identifier over any name string: company → LEI or CIK; paper → DOI; researcher → ORCID; place → ISO 3166 / GeoNames id; person → national id where lawful; product → GTIN.152. Normalise the fallback key: Unicode NFKC, casefold, strip legal suffixes (`Inc`, `Ltd`, `GmbH`, `Co`), collapse punctuation, expand common abbreviations (`Corp` → `Corporation`).163. Build candidate pairs by blocking on a cheap key (first token, country, first author), never all-pairs.174. Score candidates with string similarity, then set a two-threshold policy:18 - score ≥ 0.92 → auto-merge19 - 0.75 ≤ score < 0.92 → human-queue20 - below 0.75 → not the same21 ```python22 from difflib import SequenceMatcher23 s = SequenceMatcher(None, norm(a), norm(b)).ratio()24 ```255. Resolve, don't delete. Assign a canonical id and keep every alias; a mapping table (`alias → canonical_id`) beats overwriting rows.266. Handle one-to-many and many-to-one: an acquired subsidiary and its parent, a paper with two versions, a person with two affiliations.277. Record unresolved entities as their own cluster and report their count — do not force-merge them.2829```bash30# gather all distinct spellings before matching31cut -d, -f2 entities.csv | sort | uniq -c | sort -rn | head -3032```3334## Pitfalls3536- Fuzzy-matching on names alone merges distinct people ("J. Smith") and splits variants ("Jon", "Jonathan").37- The same name across languages ("Apple" vs "苹果") needs a cross-lingual link table, not string distance.38- Identifiers can be recycled or reassigned; check the id's validity date when sources span years.39- A shared address or phone is weak evidence — shell companies and families share them.40- Merging then joining loses the ability to audit; always keep the alias map.4142## Verification4344 python3 -c "import pandas as pd; print(pd.read_csv('alias_map.csv').canonical_id.nunique(), 'canonical entities')"4546Report: "1,204 raw name strings resolved to 817 canonical entities via LEI (93%) + similarity; 41 borderline pairs queued for review; 0 force-merges."