duplicates: report, tag, and inspect repeated records
Measure duplicate groups and expose their rows before making any irreversible decision about which records to keep.
Choose a duplicates subcommand
- Count duplicate groups
duplicates report id- Inspect every repeated row
duplicates list id- Create a diagnostic variable
duplicates tag id, generate(duplicate_count)- Remove surplus copies
duplicates drop id, but only after checking the key, other columns, and current row order.
The varlist defines what “duplicate” means. Repeated IDs are not necessarily duplicate records when other columns legitimately differ.
Complete example
The example deliberately copies records with IDs 1 and 2 so the duplicate checks have a visible result.
clear
import delimited "data/student_scores.csv"
expand 2 if id <= 2
duplicates report id
duplicates tag id, generate(duplicate_count)
list id math duplicate_count if duplicate_count > 0
duplicates drop id
duplicates report idOutput from stats.camp
Duplicates in terms of id groups with duplicates: 2 observations in duplicate groups: 4 surplus observations: 2 (2) duplicate groups tagged in variable duplicate_count +-------------------------------------+ | # id math duplicate_count | |-------------------------------------| | 1 1 45 1 | | 2 1 45 1 | | 3 2 51 1 | | 4 2 51 1 | +-------------------------------------+
Duplicates depend on the key
The report treats rows as duplicates in terms of id, so two groups contain four observations and two are surplus copies. A different varlist can produce a different answer: records can repeat an identifier while differing on another field.
The tag is zero for unique rows and reports the number of extra copies for rows in a duplicate group. It supports further inspection without deleting anything.
Drop duplicates after inspecting them
After the report, tag, and listed rows confirm that id is the intended key, the example removes surplus copies and immediately checks the key again:
duplicates drop id
duplicates report id(2) observations dropped; key variables: id Duplicates in terms of id groups with duplicates: 0 observations in duplicate groups: 0 surplus observations: 0
duplicates drop id keeps the first observation for each repeated ID. The retained row depends on the current order, so compare every relevant column and sort deliberately before deleting records.
Common subcommands and options
duplicates report [varlist]summarizes the problem; omit the varlist only when equality across all variables is the intended definition.duplicates listshows every repeated row, whileduplicates examplesshows one row per duplicate group.duplicates tag ..., generate(name)creates a reusable diagnostic variable.sepby(),noobs,nolabel, andabbreviate()control supported list displays.duplicates drop [varlist]keeps the first row for each duplicate key. Omitting the varlist requiresforcebecause all variables then define a duplicate.
See the current duplicates entry and Stata's duplicates manual for an external reference.
Related guides
Run this exact example
Clone the example repository in stats.camp and open the exact do-file shown on this page. No stats.camp account is required.
Open this example