Why Record Identity Matters
A data set can contain many rows without containing the same number of distinct people, firms, or other units. Some rows are valid repeated observations, such as monthly records for one firm. Others are duplicate records: the same intended observation has been recorded more than once. A separate problem arises when records from different files are joined using an incorrect or ambiguous match. In both cases, the rows used in an analysis may no longer represent the units the researcher intends to study.
The distinction matters because many statistics give each row equal weight. If one person appears twice by mistake while others appear once, that person contributes twice to a row-level average or count. If an outcome from one person is attached to another person's characteristics, the analysis can describe a relationship that is not present in the underlying units. These are data-structure problems, not evidence that any particular observation is extreme or incorrectly coded.
Before deduplicating or joining files, specify the observational unit and the meaning of one row in each file. For example, a file with one row per patient visit can legitimately contain several rows for a patient. The same rows would be duplicates only if the intended unit were a patient and the repeated rows represented the same visit recorded again. A repeated identifier is therefore a signal to investigate, not by itself proof of an error.
Duplicate Records and Row-Weighted Summaries
Suppose the intended analysis gives equal weight to each of \(N\) distinct entities, and entity \(i\) has value \(y_i\). The entity-level mean is the sum of the entity values divided by the number of entities. If an accidental duplicate gives entity \(i\) a record multiplicity \(m_i\), the mean across rows instead weights that entity \(m_i\) times.
The two means agree when every entity has the same multiplicity, because each value is then multiplied by the same constant. When multiplicities differ, the row mean generally differs from the entity mean. This is the precise reason accidental duplication can change a summary: it changes the implicit weights. Removing rows without first determining whether they are genuine repeated observations can create the opposite error by discarding valid data.
Worked Example: A Duplicate Changes the Mean
A hypothetical file is intended to contain one annual income observation, in thousands of dollars, for each of three workers. The values are 40, 60, and 100. A repeated import has accidentally added a second copy of the record with value 40. Compare the intended worker-level mean with the mean calculated over rows.
Intended unit and assumption. The target is the mean across distinct workers, with one annual income value per worker. Assume the two records with value 40 are confirmed to refer to the same worker and the same year, rather than to two different workers.
The intended mean is
The four rows contain 40, 40, 60, and 100, so the row mean is
The duplicate lowers the calculated mean by \(66.67-60.00=6.67\) thousand dollars. It has not changed any worker's income; it has given the worker with income 40 twice the weight of each other worker.
The same issue can affect counts and proportions. For example, if the duplicated entity belongs to a particular category, that category's row count rises by one even though the number of distinct entities does not. Whether an estimate changes depends on which records are duplicated and how the estimate weights observations. It is not enough to say that duplicates always increase every statistic.
Linkage Errors Across Files
Linkage is the task of deciding which record in one file refers to the same entity as a record in another file. A false positive match links two records that belong to different entities. A false negative match fails to link records that belong to the same entity. A match is unambiguous only relative to the available identifiers and the intended unit. A shared name, date, or location may narrow the candidates without uniquely identifying an entity.
A deterministic linkage rule uses specified fields and conditions, such as an exact match on a unique, stable identifier. A rule based on a non-unique field, such as surname alone, can match unrelated entities. A more flexible rule may use several identifying fields to propose candidate matches, but a plausible candidate is not automatically a confirmed match. For consequential or unresolved cases, retain the uncertainty for review rather than silently treating an uncertain pair as certain.
Worked Example: A False Match Reverses a Difference
A hypothetical study records program participation in one file and annual earnings, in thousands of dollars, in another. Both files are intended to contain one record per person. The participation file is:
| Person | Participated? |
|---|---|
| E1 | Yes |
| E2 | No |
| E3 | Yes |
| E4 | No |
The earnings file has the correct person identifiers and values: E1 has 10, E2 has 0, E3 has 12, and E4 has 2. Assume the identifiers are correct and that each person has one earnings observation for the same period. First calculate the difference in mean earnings between participants and nonparticipants using the correct links.
Among participants, the mean is \((10+12)/2=11\) thousand dollars. Among nonparticipants, it is \((0+2)/2=1\) thousand dollars. The participant-minus-nonparticipant difference is \(11-1=10\) thousand dollars.
Now suppose a faulty match swaps the earnings records for E2 and E3, while E1 and E4 remain correctly linked. The joined data then assign earnings 10 and 0 to participants, and 12 and 2 to nonparticipants. The calculated means become
The linked-data difference is now \(5-7=-2\) thousand dollars. A two-record matching error has changed the difference from \(10\) to \(-2\) thousand dollars, reversing its sign. This is a descriptive comparison, not a causal effect: the example isolates the way the linkage error changes the measured relationship, without making a causal claim about participation.
A false negative can also affect the analysis. For instance, an inner join that retains only records found in both files may drop an entity whose valid counterpart was not recognized. This can reduce the number of linked units and change the composition of the analysis sample. A left join can preserve the first file's rows but leave unmatched fields empty; it does not repair the missing match. The choice of join operation should therefore follow the intended unit and analysis, not substitute for checking the match quality.
Join Cardinality and Ambiguous Matches
A join combines records using a key or matching rule. Before performing it, determine how many rows should exist on each side for a given key. In a one-to-one join, each record on one side is expected to match at most one record on the other. In a one-to-many join, one record is expected to match several distinct records, as when a person has multiple valid visits. A many-to-many join can produce multiple output rows for one or more records; it may be intended, but it can also create accidental row multiplication.
If one record matches \(a\) records in the first file and \(b\) in the second file under a many-to-many key, a conventional join can produce \(a\times b\) paired rows for that key. That output does not mean there are \(a\times b\) distinct entities or events. It reflects the join structure. Check row counts, key uniqueness, and unmatched records before using joined rows to calculate totals, means, or relationships.
Worked Example: An Ambiguous Key Inflates a Total
A hypothetical registry contains two customers, 201 (Anna Lee) and 202 (Anne Lee). A transaction file contains one clearly identified purchase of 25 dollars by customer 201 and a second 75-dollar purchase labelled only “A. Lee.” Assume each transaction is a distinct event, and the transaction file's total is intended to count each purchase once. The abbreviated name does not identify which customer made the second purchase.
If the ambiguous purchase is joined to the registry by a rule that assigns it to both customers, the output contains three rows: the 25-dollar purchase for customer 201, plus the 75-dollar purchase paired with each registry record. Summing the joined amount column gives
But the transaction file contains only two purchases, whose total is \(25+75=100\) dollars. The extra 75 dollars is a join expansion, not an additional transaction. The evidence supplied does not establish whether customer 201 or 202 made the ambiguous purchase; the correct response is to preserve it as unresolved or seek additional identifying information, not to count it twice or choose a customer without justification.
A Practical Record-Level Audit
A careful audit separates identifying duplicate records within a file from deciding whether records across files refer to the same entity. The intended unit, the fields that identify it, and the expected relationship between files should be stated before a deletion or join is made. A useful sequence is:
Specify whether a row represents a person, a person-period, a transaction, or another unit. Decide whether repeated rows can be valid under that definition.
Compare the fields that should identify the same unit and event. Identical rows may be evidence of duplication, but check whether the unit permits genuine repeated events before removing any.
Count how often each intended key occurs in each file. A key that appears multiple times may indicate valid repeated records, an incorrect key definition, or an error that requires investigation.
Record which rules created matches, which records have no match, and which have multiple plausible candidates. Use additional evidence or manual review where needed.
Compare the number of input records, distinct intended units, matched units, unmatched units, and output rows. Explain any increase or decrease before calculating summaries.
This audit makes the assumptions visible. For example, a claim that an identifier is unique is an assumption about how the files were created and what each row represents; it should be checked against the records. Retaining the original files, recording the linkage rule, and documenting how ambiguous cases were handled make later review possible. They do not guarantee a correct match, but they make the basis of the analysis inspectable.
Common Mistake: Treating Every Repeated Identifier as a Duplicate
Wrong reasoning: “The same person identifier appears on several rows, so I should keep one row and delete the rest.” This assumes that a person is always the row-level unit. In a person-month file, several rows for one person may be necessary to represent distinct months; deleting them would discard valid observations. Conversely, in a one-row-per-person file, repeated records for the same person and period may be accidental duplicates.
Correction: Define the intended unit and time or event scope first. Then ask whether the repeated rows represent the same unit-level observation or distinct valid observations. Apply the same discipline to file linkage: matching a name is not proof that two records belong to the same entity, and a join that produces more rows must be explained by the intended relationship between records.
Check Your Understanding
Answer each question by referring to the intended observational unit and the record-level evidence.
- A file has one row per patient visit. A patient appears four times for four different visit dates. Why is the repeated patient identifier not, by itself, evidence of duplicate records?
- Three distinct workers have values 20, 50, and 80. The record with value 80 is accidentally duplicated. Calculate the mean across distinct workers and the mean across rows.
- Define a false positive match and a false negative match. Give one possible consequence of each for a joined analysis.
- A key occurs twice in one file and three times in another. How many joined rows can that key produce under a conventional many-to-many join, and why is that not necessarily the number of distinct entities?
- Two records share a surname but have different birth dates. What additional checks or evidence could help assess whether they refer to the same entity?