Entity Resolution: One Real Thing, Many Messy Names

This guide walks through five steps for working out which records are the same real thing, and merging them without wrecking your data. It runs on real chart data, and it includes the two times the rules came out wrong. Here is the problem in one example. Count the distinct artists in Billboard's public chart history and the number is wrong. "Elvis Presley" and "Elvis Presley With The Jordanaires" are the same man, and so are five other credit strings. One real-world entity , seven database strings . Every dataset with human-entered names has this. Customers who signed up twice. "IBM" against "I.B.M." against "International Business Machines". The same supplier in two systems, spelled two ways. The work of fixing it is called entity resolution . Matching across two datasets is record linkage . Removing duplicates inside one is deduplication . They are the same skill pointed at different situations, and it is one of the most common tasks an analyst actually gets handed. The vocabulary map Term Meaning Entity The real-world thing: one artist, one customer, one company Entity resolution Figuring out which records refer to the same entity Record linkage The same problem across two datasets. "Is row 5 in file A the same person as row 90 in file B?" Formalized by Fellegi & Sunter (1969) Deduplication The same problem inside one dataset Normalization / standardization Transforming values toward a canonical form (lowercasing, trimming, cutting suffixes) so equal things become equal strings Match key The cleaned column(s) you actually join on Match rate The share of records that found their counterpart. This is the number that keeps the whole exercise honest Clerical review Human eyes on the records the rules could not decide. This is a formal stage of the classic framework, not an admission of failure Step 1: measure the fragmentation before fixing anything The worked example is Billboard Hot 100 history, 1958 to present. The goal is one clean row per artist. Before writing