The best data cleaning tools for survey data: an honest list

“Data cleaning tools” covers everything from spreadsheet add-ins to enterprise data quality platforms. Field surveys ask something particular of them: interviews arrive every day, each one has an enumerator behind it, and at the end a client or a journal will ask what was changed and why. This list covers the tools survey teams actually use, what each is good for, what it costs to run in skills and time, and where it falls short during fieldwork. What to require of any survey software is on data cleaning software for field surveys; this page is the tools themselves.

The tools at a glance

1. Stata do-files and IPA's ipacheck

Many research teams check field data in Stata. Innovations for Poverty Action's ipacheck is a ready-made template: a package of commands that export duplicate IDs, outdated form versions, missing values, outliers, constraint violations, “other, specify” answers, SurveyCTO comments and text audits, enumerator statistics and a survey progress dashboard, with ipacheckcorrections to apply corrections and ipabcstats to compare back-checks. Results come out as formatted Excel sheets for the field team. It installs from GitHub, and ipacheck new sets up a project folder with a master do-file, a globals do-file and Excel input sheets that you fill in for your survey.

net install ipacheck, all replace from("https://raw.githubusercontent.com/PovertyAction/high-frequency-checks/master")
ipacheck new, surveys("household") folder("C:/projects/livelihoods")

Good for: teams with a Stata analyst, especially economists who will analyse the data in Stata anyway. The do-file is the cleaning record, and a replication package can include it.

What it costs to run: a Stata licence for whoever runs it, and analyst time: filling in the input sheets for each survey, running the checks every day, emailing the Excel files, and fixing the code when the form changes.

Where it falls short in fieldwork: checks run only when someone runs them, and quality depends on the one person who understands the setup. Keeping personal data apart from the working files is up to you.

If your team already runs ipacheck well and someone owns it, keep it. ARCED Foundation's arceddataflow, a Stata workflow under the MIT licence, is the other open option, and the one DataFlow grew out of. DataFlow and Stata high-frequency checks compares the two approaches in detail.

2. R: validate, janitor and pointblank

R is free software for statistics, and three packages on CRAN, R's package archive, cover most of a cleaning script. validate lets you declare rules and confront the data with them, including rules within one record, across records and across datasets. janitor cleans column names, makes frequency tables and crosstabs, and finds duplicate records with get_dupes(). pointblank builds a validation plan of readable steps, sets thresholds for how many failures trigger a warning, and produces a report. To pull data straight from ODK Central, ODK's documentation points R users to ruODK.

library(validate)
rules <- validator(
  hhsize >= 1, hhsize <= 30,
  children_school <= hhsize,
  respondent_age >= 15
)
out <- confront(survey, rules)
summary(out)

library(janitor)
get_dupes(survey, hhid)   # every household ID used more than once

Good for: teams that already analyse in R and have someone who writes it. Everything is free and the script is reproducible.

What it costs to run: R skills, and time to write the checks for each survey. None of the three packages knows what an XLSForm, an enumerator or a back-check is, so the comparisons by enumerator, the skip logic and the back-check comparison are code you write. Running every morning without someone pressing a button means setting up a scheduler.

Where it falls short in fieldwork: the same as Stata. The scripts depend on whoever wrote them, and field supervisors won't run R, so flags still travel as files.

3. Excel and Power Query

Excel is the tool nearly every team already has, and for a few hundred interviews, filtering, sorting and a pivot table by enumerator get you a long way. Power Query, built into Excel for Windows and Mac, does better than hand work: it records each transformation as a step in a query you can refresh when new data arrives, and it doesn't modify the source data. ODK Central's OData feed, a live link to the submissions, lets Excel refresh as new ones come in.

Good for: small surveys, a one-off look at a batch, and the supervisor's side of corrections: a sheet of flagged cases where they write the right value and why.

What it costs to run: spreadsheet skills and someone's time every day, which grows with the number of interviews.

Where it falls short in fieldwork: a value typed into a cell replaces the old one and keeps no reason, so the raw data and the record of changes have to be kept by discipline. Checks done by hand vary from day to day, and outliers within villages, rules across many columns and back-check matching become fragile formulas. Power Query records how the data was reshaped, not why one household's income was corrected. See DataFlow and Excel for the side-by-side.

4. OpenRefine

OpenRefine is a free, open-source tool for messy data. It runs on your own computer, filters large datasets with facets, merges similar values with clustering (the same village spelled five ways), and keeps a history of operations you can rewind or replay on a new version of the file.

Good for: text: place names, respondent-written answers, harmonising spellings across rounds, and reading “other, specify” answers in bulk.

What it costs to run: nothing to buy, and some time to learn facets and clustering.

Where it falls short in fieldwork: it works on a file you load, not on interviews arriving each day, and it has no idea of forms, enumerators or back-checks. Use it beside a checking tool, not instead of one.

5. Your collection platform's own tools

The platforms do more than collect, and their tools are the first line of defence. Keep them switched on whatever else you use.

• SurveyLoopr hosts ODK Central servers, so Central's review tools below apply. Its DataSnap reports turn ODK audit logs into views of timing, location, question friction and work patterns, with flags meant to prompt a question rather than settle one.
• SurveyCTO has the most built in. In the form: speed limits on questions, text audits of the time spent on each question, and random audio audits (collecting high-quality data). On the server: automated quality checks for thresholds, outliers, frequencies, means and differences between groups such as enumerators, run nightly into a CSV of warnings, though not on fields inside repeat groups or encrypted fields not marked publishable. Its review and correction workflow holds submissions until someone approves them, rates each as good, okay, poor or fake, and exports a correction log with the old value, new value, user, time and comment.
• ODK Central gives each submission a review state, comments and browser editing that keeps each version. Its documentation says the review states “don't cause anything to happen automatically” and describes no checks across submissions; for analysis it points to the CSV export and the OData feed, read by pyODK, ruODK, Excel or Power BI.
• KoboToolbox lets reviewers mark submissions approved, not approved or on hold, edit them as a web form, and search, filter and sort the table. Its guide to validating data suggests exporting the data to filter on several conditions at once.

What they cost to run: nothing beyond the platform, and little new to learn. Where they fall short: review mostly happens one submission at a time, and the clean dataset is usually made outside the platform, so the record that rebuilds it from the raw export has to live somewhere else. The guides for SurveyCTO, ODK Central and KoboToolbox set out the checks each platform leaves to you, and DataFlow and platform monitoring compares the two.

Which tool fits which team

• A Stata analyst already runs ipacheck and owns it: keep it. Add the platform's constraints and a written plan for corrections.
• An R team with a programmer: validate or pointblank, with ruODK if the data is on ODK Central.
• One finished dataset to tidy, no fieldwork to monitor: Excel with Power Query, or OpenRefine for the text.
• A small survey on SurveyCTO where someone reviews every submission: its review workflow and automated checks may be all you need.
• Many enumerators, no programmer, and a client who wants to see the work: a tool built for field surveys, such as DataFlow.
• Data that isn't from surveys, such as a customer database: a generic data quality tool will suit it better than anything here.

Whichever you choose, write the checks and thresholds down before fieldwork; the data quality assurance plan template and the Field Data Quality Standard give you the list.

How DataFlow cleans survey data

DataFlow is a desktop app for Windows, macOS and Linux, made for checking field survey data during fieldwork. It connects to SurveyLoopr, SurveyCTO (encrypted forms too), ODK Central and KoboToolbox, imports CSV and Excel exports from any other tool, and can sync and check on a timetable you set. On import it reads the XLSForm and the data and adds checks itself: duplicate IDs, interview length against the median, ranges from the form's constraints, outliers beyond 3 interquartile ranges, blanks in required questions, the share of “don't know” answers, “other, specify” answers, the form's skip logic, form versions, night-time and overlapping interviews, and GPS. A suggested check that would flag more than 5% of interviews starts switched off, unless most of its flags come from one enumerator.

It reads results by enumerator, with six statistical signs of made-up interviews compared within each area when every area has enough enumerators, and across the team otherwise, and compares back-checks with the original interviews, counting differences per enumerator, back-checker and question. The raw data is never modified: every correction needs a reason, can be undone, and goes into an activity log where each entry carries a hash of the one before. A download comes as Stata, CSV, Excel or R files with a codebook, the full correction log and, by default, a Stata do-file that replays every correction from the raw data. Its duplicate and outlier checks are tested to flag exactly the interviews arceddataflow flags. Personal data goes into a separate encrypted store and is left out of downloads unless someone unlocks it. A small language model that runs on the computer answers questions about the data and prepares changes for a person to approve.

What it costs to run: no code. Monthly is $19 per seat a month; Annual is $99 a year with 5 seats, with extra seats at $4.99 a month billed yearly. There is a 7-day free trial, and the sample study works without an account. When a plan ends, projects stay open to browse and export.

Where it falls short: it runs on the computers where it's installed, not as a website. It doesn't replace the constraints in your form, and analysis still happens in Stata or R. R, OpenRefine and ipacheck cost nothing to download, and if one of them already works for your team, that is a good reason to stay with it.

Questions