Duplicate survey responses: finding repeated IDs and interviews

Four kinds of duplicate

In a frequency table every duplicate looks the same: one ID, two rows. The causes differ, and so do the fixes, so the first job is to work out which kind you have.

The World Bank's DIME Wiki page on duplicates and survey logs calls duplicates common in ODK and SurveyCTO data. It names three kinds: the same interview submitted twice after an interrupted upload, the same interview sent again with changed answers, and two respondents given one ID by a typo. It also explains why they come first: the other checks depend on a unique ID. A back-check sample, a merge with the sample list or a comparison with last year's round all go wrong when one ID points at two interviews. That is why duplicates sit at the top of the daily high-frequency checks.

Repeated IDs

Start with every variable that should be unique: the household or respondent ID, and any other ID your sample assigns. The platform's own submission key (KEY in SurveyCTO, instanceID in ODK) is different for every submission, so a household interviewed twice has two keys, and only your own ID shows the repeat. Run the check on the whole dataset with every new batch, because the second interview can arrive weeks after the first. Count blank IDs separately, or they will all match each other.

* How many IDs repeat, and which interviews share them
* (key is the platform's submission key: KEY in SurveyCTO)
duplicates report hhid
duplicates tag hhid, generate(dup_id)
sort hhid
list hhid key enum_id village if dup_id > 0 & !missing(hhid), sepby(hhid)

* Copies that match on every answer, leaving out what the server adds
* (add any other fields your server fills in)
ds key submissiondate, not
duplicates tag `r(varlist)', generate(same_answers)

For a full workflow in Stata, DIME's ieduplicates (in the iefieldkit package) writes every repeated ID to an Excel sheet with columns to fill in: which interview is correct, which to drop, a new ID, initials and notes. It applies those decisions the next time it runs, so the sheet becomes the record of how each duplicate was resolved. Its companion, iecompdup, compares two interviews with the same ID in more detail.

Forms sent twice

When two interviews share an ID, compare their answers before anything else, leaving out the fields the server fills in on arrival. If every answer matches, including the start and end times, the same form reached the server twice: keep one copy and set the other aside. If the answers match but the start times differ, the enumerator filled the form in twice, which is worth a question. If many answers differ, you have two households under one ID, or one household interviewed twice. Copies can also be made at the desk: KoboToolbox can duplicate a submission from its data table, copying the answers into a new record with fresh metadata, so a duplicate made by mistake there looks like a second interview.

The same household under two IDs

An ID check can't see a household interviewed twice under two different IDs. Look for repeats in other fields that should be unique: the phone number, the household head's name within a village, a national ID number where one is collected. Names are spelled differently from one enumerator to the next, so clean them first (lower case, no extra spaces) and expect to review the matches by hand. In listing and census work, two interviews recorded a few metres apart may be the same dwelling.

* The same phone number, or the same head's name in the same village
generate head_clean = strlower(strtrim(stritrim(head_name)))
duplicates tag phone, generate(dup_phone)
duplicates tag head_clean village, generate(dup_name)
sort village head_clean
list hhid enum_id village head_name phone ///
    if (dup_phone > 0 & !missing(phone)) | (dup_name > 0 & head_clean != ""), sepby(village)

Compare the IDs with the sample list as well. An ID that isn't on the list, or a sampled household with two interviews while its neighbour has none, usually means the wrong household was visited or the wrong ID typed. Names and phone numbers are personal data, so run these checks where the identified data is kept, and share the results only with the people who follow them up (see de-identifying survey data).

Near-copies

An enumerator who invents interviews often starts from a real one and changes a few answers. The copies have different IDs, so they pass every ID check. To find them, compare each enumerator's interviews with each other, pair by pair, and work out the share of questions on which the two give the same answer, counting only questions answered in at least one of them. Leave out questions that nearly everyone answers the same way, or every pair will look alike.

* Pairs of one enumerator's interviews that share most of their answers
* (run after repeated IDs are resolved; the questions to compare are named q_* here)
use "survey.dta", clear
keep hhid enum_id q_*
save "pairs_a.dta", replace
rename hhid hhid_b
rename q_* b_q_*
joinby enum_id using "pairs_a.dta"
keep if hhid < hhid_b
generate same = 0
generate asked = 0
foreach q of varlist q_* {
    replace asked = asked + 1 if !missing(`q') | !missing(b_`q')
    replace same = same + 1 if `q' == b_`q' & !missing(`q')
}
generate share = same / asked
gsort -share
list enum_id hhid hhid_b share if share >= 0.85, noobs

A pair with high agreement isn't proof. Neighbours in a small village answer many questions the same way. What stands out is an enumerator with many more such pairs than colleagues working in the same area. Treat it as a reason to back-check those respondents first, and read it alongside the other signs in enumerator monitoring.

Preventing duplicates in the form

A typo in an ID is cheaper to stop than to fix. Where you have a sample list, let the enumerator pick the household from it instead of typing the ID: a select_one_from_file question in ODK and KoboToolbox reads the list from a CSV attached to the form, and SurveyCTO's pre-loaded data does the same job. Where the ID has to be typed, ask for it twice:

The regular expression here expects six digits; change it to match your IDs. Showing the head's name from the sample list once the ID is entered, with a note asking the enumerator to confirm it, catches an ID that is valid but wrong.

What to do with each duplicate

Never delete a duplicate, or drop it with duplicates drop, without a record. Log each decision: the ID, which submission was kept and which set aside, any new ID, who decided and why. In Stata that is a do-file that starts from the raw data, or the sheet ieduplicates keeps. The raw file stays as it arrived, so anyone can check the decision later and reverse it if it was wrong.

How DataFlow handles duplicates

DataFlow keeps one copy of each submission key: a submission it already holds is updated when it syncs again, not added twice. On import it adds a duplicate check for each question named like an ID (id, hhid, uid, or a name starting id_ or ending _id). The check flags every interview whose value appears in more than one interview, ignores blanks, and lists the other interviews with the same value. You can add the same check to any other question, such as a phone number.

In the Today list, the flags are grouped by value. For each set, DataFlow compares the interviews while leaving out the submission details (key, submission, start and end times, duration, device ID, form version and the enumerator's name). If the answers match, it says the interviews were sent twice; if they differ, that the same household may have been visited twice. It proposes keeping the most complete interview of each set (the most answers, then the longest) and setting the others aside, each with the reason “Duplicate of” the one kept. When the interviews are two households, skip the proposal and correct the ID on the flag itself, with a reason. Nothing is deleted: a set-aside interview stays in the raw data and the step is in the audit log. A download can leave set-aside interviews out, and its Stata do-file marks each interview's status, set aside included, alongside the corrections it replays from the raw data.

Near-copies are one of DataFlow's six signs of made-up interviews. For each enumerator with at least 10 interviews, it counts the interviews that share at least 85% of their answers with another of that enumerator's own, and compares the share with at least four colleagues. DataFlow works alongside the collection platform: it connects to SurveyLoopr, SurveyCTO and ODK Central, and reads exports from KoboToolbox and other tools.

Questions