Why Duplicate Checks Require Context
A repeated row or identifier can look like a simple data-cleaning problem: find the repetition and delete it. But the same pattern can have very different meanings. Two identical rows might result from importing a record twice, or they might describe two distinct units that happen to share the same recorded values. A repeated identifier might be an error in a data set intended to contain one row per person, or it might be correct when each person contributes one row per visit.
The key question is therefore not only “Does something repeat?” It is also “What does one row represent, and what records should be unique under that layout?” The earlier material on observational units and identifiers provides the needed foundation: a key is a field, or combination of fields, used to identify the unit represented by a row.
Start With the Intended Row and Key
Before running a duplicate check, state the intended layout. For example, “each row represents one patient” implies that a patient identifier should occur at most once. By contrast, “each row represents one patient at one visit” implies that a patient identifier may repeat; the combination of patient identifier and visit identifier may be expected to be unique.
A key may consist of one field or several fields. A single patient ID could identify a person, while a patient ID together with a visit date could identify one person-visit record. The appropriate key depends on the observational unit, not on which column happens to contain numbers or labels that look unique in the current file.
For a specified key, let \(m_k\) be the number of rows carrying key value \(k\). A key is repeated when \(m_k>1\). If the intended layout requires exactly one row per key, the number of extra rows associated with repeated keys is:
This counts the rows beyond the first occurrence of each key. It is not necessarily the number of records to delete: that decision requires investigating what the rows represent. A missing key also cannot identify a unit. Several rows with a missing key should not automatically be treated as repeated instances of the same unit.
Two Checks, Two Different Questions
An exact-row comparison asks whether all selected recorded fields match. A key comparison asks whether the identifier designated for the intended unit repeats, whether or not the other fields match. These checks overlap, but neither replaces the other.
| Pattern | What it establishes | What it does not establish |
|---|---|---|
| All compared fields match in two rows | The rows are exact matches on those fields | That both rows describe the same real-world event or unit |
| A key value appears more than once | The key is not unique in the current rows | That one of the rows is invalid |
| A key repeats and other fields differ | There are multiple records associated with that key | Whether this is an error or a legitimate repeated record |
The exact-row check should compare substantive fields. A temporary row number added during data import will differ by design and can conceal otherwise identical records if included in the comparison. Conversely, excluding a field can make rows appear identical even though they differ on that field. State which fields are being compared so the result has a clear meaning.
Worked Example: Counting Repeated Keys in a One-Row-Per-Person File
A hypothetical file is intended to contain one row per participant. Its participant IDs are 201, 202, 202, 203, 204, and 204. Check key uniqueness and count the extra rows.
The intended row unit is one participant, so the participant ID is expected to occur once per row-unit. The key counts are \(m_{201}=1\), \(m_{202}=2\), \(m_{203}=1\), and \(m_{204}=2\). Thus two key values repeat.
Using the extra-row formula:
There are six rows and four distinct nonmissing participant IDs, so the same result follows from \(6-4=2\), provided every row has a valid key. This calculation identifies two records beyond one per ID. It does not show which record, if any, should be removed. The rows for IDs 202 and 204 need to be compared with the source or study design to determine why each ID occurs twice.
Exact Matches and Repeated Measurements
Exact matches deserve review, but equality of recorded values is not proof of duplicate data. Two different units may have the same values for every recorded field, especially when the file contains few variables or values are rounded. Also, if a data set does not record a field that distinguishes two events, two genuinely distinct records can appear identical in the available columns.
Repeated measurements are a particularly important reason for repeated IDs. In a long-format file, the same person may have a row at each visit. The person ID is then not expected to be unique; a more suitable key may combine the person ID with the visit. If the same person-visit combination appears twice, that is a signal to investigate, not automatic proof of an error: the study might include repeat measurements within a visit, or the visit label might not fully distinguish records.
Worked Example: Identical Visit Records in a Repeated-Measurement File
A hypothetical clinic file is designed to have one row per participant per scheduled visit. The recorded fields are participant ID, visit, and a symptom score:
| Participant ID | Visit | Symptom score |
|---|---|---|
| P14 | 1 | 6 |
| P14 | 2 | 4 |
| P14 | 2 | 4 |
| P29 | 1 | 3 |
The patient ID repeats because the observational unit is not simply a participant. Under the stated layout, the candidate key is the combination of participant ID and visit. That combination appears twice for P14 at visit 2, and the two rows match exactly across the three recorded fields.
There are four rows but three distinct participant-visit combinations: (P14, 1), (P14, 2), and (P29, 1). Therefore, if the one-row-per-participant-per-visit rule is confirmed, the repeated combination accounts for \(4-3=1\) extra row. The exact match makes accidental duplication plausible, but does not prove it: the data may omit a measurement identifier that distinguishes two legitimate records. The correct next step is to check the collection or source records and confirm the intended visit-level layout before changing the file.
Worked Example: A Repeated ID With Different Values
A hypothetical employee file is intended to contain one row per employee. Employee E07 appears twice:
| Employee ID | Department | Weekly hours |
|---|---|---|
| E07 | Shipping | 35 |
| E07 | Shipping | 40 |
The employee ID is repeated, so the one-row-per-employee uniqueness rule is not met. These are not exact duplicate rows because weekly hours differ: the department matches, but \(35\ne40\). If the file truly represents one employee per row and weekly hours is a single value for that employee at the relevant time, the two entries conflict and require investigation.
It would be premature to keep the larger value, average the two values, or delete one. The discrepancy might reflect a correction, two reporting periods that were not recorded, or a mistaken ID. The row pattern alone cannot distinguish these explanations. Check the intended time reference and original records; then retain, correct, or otherwise handle the records according to what the source supports. If the rows actually represent different periods, the one-row-per-employee layout is not the appropriate description of the data.
A Careful Duplicate-Checking Process
A duplicate check is most useful when it produces a review list rather than an automatic deletion rule. The following sequence keeps the check tied to the meaning of the data.
Write down what one row is meant to represent, such as one participant or one participant at one visit.
Select the field or combination of fields that should uniquely identify that row-unit, based on the intended layout.
Record which keys recur and which rows match across the stated comparison fields. Do not treat a missing key as a shared identifier.
Compare the relevant rows with the study design, collection structure, or source records. Ask whether the repetition is expected, unexplained, or inconsistent with the stated unit.
Retain legitimate repeated records. Correct or remove records only when supported by the investigation, and preserve enough information to explain the change.
Why Automatic Deletion Is a Pitfall
A common mistake is: “The ID repeats, so the later row must be a duplicate; delete it.” This reasoning silently assumes that one row represents one ID, even when the file may contain repeated visits or events. It also assumes that row order tells us which record is correct. Neither assumption follows from seeing a repeated key.
The correction is to test uniqueness against the intended row-unit and key, then investigate records that violate that expectation. Exact matches should also be reviewed rather than deleted automatically. If two distinct units share every recorded value, deleting one would erase a valid observation. If a repeated key marks a legitimate second measurement, removing it would discard information. Duplicate detection identifies patterns; deciding what they mean requires context.
Check Your Understanding
Use the intended row-unit and key to reason about each situation.
- A file is intended to contain one row per school. A school ID appears three times. How many extra rows does this repeated key contribute, and what does that count not tell you?
- A file contains one row per person per visit. Is a person ID expected to be unique? Give an example of a more suitable key for this layout.
- Two rows match on every recorded field. Name one reason they might still represent distinct units.
- A key appears twice, but the other recorded values differ. Why is this not an exact duplicate, and what should be investigated before editing the file?
- Several rows have missing key values. Why should they not automatically be counted as repeated records for one shared unit?