Guides / data cleaning
Data cleaning: a practical guide
Data cleaning routinely takes longer than the analysis it prepares for, and the decisions taken during it affect results more than the choice of test. This guide sets out a defensible workflow, explains the missing-data distinctions that determine what you are allowed to do, and covers how to document the process so it can be reproduced and defended.
Written and reviewed by Hafiz Ahmad Tariq, Senior BiostatisticianData cleaning is the process of preparing a raw dataset for analysis: correcting structural problems, checking values are valid and within range, identifying duplicates, deciding how to handle missing values and outliers, and constructing derived variables. Every decision should be recorded and applied through a script, so the cleaned dataset can be reproduced exactly from the raw file.
Definition
Why cleaning decisions matter
Cleaning decisions change results more often than the choice of statistical test does. Whether an outlier stays, how missing values are handled, and where a variable is cut all move the answer — frequently by more than switching between a t-test and a Mann-Whitney would.
That makes them analytic decisions, not clerical ones, and they carry the same obligations: decide the rule before seeing whether it helps your hypothesis, record it, and report it. A cleaning stage that is never described in the methods section is a set of undocumented choices sitting between the data and every number you report.
The scale of the problem is easy to underestimate. Studies giving the same raw dataset to many independent analysts have repeatedly found substantial variation in results — not because anyone made an error, but because each team made a different defensible chain of decisions about exclusions, missingness and variable construction. Some of those studies found teams reaching opposite conclusions from identical data. That is the strongest available argument for documenting every choice: not that any one of them is wrong, but that a reader cannot evaluate your result without knowing which path you took.
Deciding to exclude an outlier after noticing that removing it makes a result significant is a recognised form of p-hacking, and it is invisible to a reader unless you disclose it. Write the exclusion rules into your analysis plan, and if you deviate, say so and report the result both ways.
Overview
The workflow
Version the script alongside the manuscript, so the exact code that produced a given set of numbers can always be recovered later.
R, Python, Stata and SPSS syntax all work. Point-and-click cleaning in a spreadsheet does not, because six months later neither you nor an examiner can establish what was changed. If a viva question asks how many cases were excluded and why, a script answers it in seconds and a manually edited spreadsheet cannot answer it at all.
Structure
Step 1: structure
Before checking any values, confirm the dataset is shaped correctly.
Being clear about the unit of analysis sounds trivial and frequently is not. A dataset of clinical appointments might have one row per appointment, but if your question is about patients, the unit is the patient and appointments must be aggregated first — otherwise a patient attending twelve times contributes twelve observations and dominates the analysis. Getting this wrong is easy to do and produces conclusions about a population you did not intend to study.
Wide or long?
| Format | One row per | Needed for |
|---|---|---|
| Wide | Participant, with repeated measures in columns | Repeated measures ANOVA, correlations between time points |
| Long | Observation, with a time or condition column | Mixed models, multilevel models, most plotting |
Reshaping between the two is straightforward in any package, but knowing which your intended analysis requires prevents a great deal of wasted effort. Mixed models need long format; classic repeated measures ANOVA needs wide.
Spreadsheet software silently converts some values — identifiers with leading zeros lose them, and certain codes are read as dates. Check any ID column has retained its original form, and import as text where necessary. This class of error is invisible until it breaks a merge.
Validity
Step 2: validity checks
Every variable should be checked against what it can legitimately contain.
| Check | Looking for | Example |
|---|---|---|
| Range | Values outside the possible | Age of 999; a 5-point item scored 7 |
| Type | Text in a numeric field | “approx 40” in an age column |
| Categories | Unexpected or inconsistent levels | “Female”, “female”, “F” as three categories |
| Logical consistency | Impossible combinations | Age 19 with 25 years of experience |
| Dates | Ordering and plausibility | A follow-up date before baseline |
Run a frequency table for every categorical variable and minimum, maximum and a histogram for every continuous one. This takes minutes and catches most problems. Inconsistent category labels are especially common with free-text entry and will silently split a group in two if not caught.
Surveys often code missing as 99, 999 or −1. If these are not declared as missing before analysis, a mean age will be computed including the 999s and will be badly wrong — without any error message. Check the codebook and declare them explicitly.
Duplicates
Step 3: duplicates
Duplicates arise from double submission, repeated data entry and merges gone wrong. They inflate the sample and violate the independence assumption every test relies on.
Online surveys need a slightly different eye. Repeat submissions from one respondent may share an IP address or a device fingerprint but differ in every answer, and platforms sometimes record a partial submission alongside the completed one. Decide in advance which record you will keep — usually the most complete, or the first — and apply that rule consistently rather than choosing case by case.
If a merge multiplies your row count, the key was not unique in one of the tables. Fix the join rather than deleting the surplus rows, because deleting them silently discards whichever record happened to come second.
Missingness
Step 4: missing data
Handling missing data correctly requires knowing why it is missing, and that is a judgement rather than a test result.
| Approach | How it works | When acceptable |
|---|---|---|
| Listwise deletion | Drop any case with a missing value | MCAR, and little missingness |
| Pairwise deletion | Use all available data per analysis | Rarely — different n per statistic causes problems |
| Mean imputation | Replace with the variable mean | Almost never — shrinks variance, distorts correlations |
| Prorated scale score | Mean of answered items, scaled up | If a stated minimum of items was answered |
| Multiple imputation | Generate several plausible datasets and pool | MAR; the preferred approach |
| Maximum likelihood | Estimate using all available data | MAR; built into many model packages |
Why mean imputation is worse than it looks
Replacing missing values with the mean adds cases that sit exactly at the centre, which artificially reduces the variance and pulls every correlation towards zero. It also produces standard errors that are too small, because the analysis treats imputed values as though they had been observed. It is simple, widely used, and one of the few genuinely indefensible choices in this list.
A practical distinction worth drawing is between item-level and unit-level missingness. Item-level means a respondent skipped some questions, and prorating or imputing at the item level is usually reasonable. Unit-level means a participant is absent entirely from a wave or dropped out, which is a different problem and, in a trial, one that bears directly on whether randomisation still holds. They are often reported together as a single percentage, which obscures the distinction that matters.
Give the percentage missing per variable, describe the pattern, and state which mechanism you judged it to be and on what grounds. Above about 5% missing on a key variable, listwise deletion needs an explicit justification; above 20%, most reviewers will expect multiple imputation or a serious discussion of why it was not used.
Outliers
Step 5: outliers
An outlier is an unusual value, not automatically a wrong one. The first question is always why it is there, and the answer determines what to do.
| Cause | What to do |
|---|---|
| Data entry error | Correct from source if possible; otherwise set to missing |
| Impossible value | Set to missing and record it |
| Participant outside the target population | Exclude, using a pre-specified rule |
| Genuine extreme value | Keep it — it is real data |
Detection
| Method | Rule | Note |
|---|---|---|
| Boxplot | Beyond 1.5 × IQR from the quartiles | Standard visual check |
| z-score | |z| > 3.29 | Assumes normality; the mean is itself pulled by outliers |
| Mahalanobis distance | Chi-square cut-off | For multivariate outliers |
| Cook's distance | Influence on a fitted model | Identifies cases that change the result |
Cook's distance asks the most useful question. A value can be extreme without affecting anything, and a value can be unremarkable in isolation while exerting substantial leverage on a regression line. What matters is influence on the conclusion, not distance from the mean.
Where a genuine extreme value is retained but might be questioned, run the analysis with and without it and report both. If the conclusion holds, that strengthens it considerably; if it does not, that is an important finding about the fragility of the result rather than something to conceal.
Multivariate outliers deserve a mention because they are invisible to any single-variable check. A participant aged 19 is unremarkable, and 25 years of professional experience is unremarkable, but the combination is impossible. Neither value appears in a boxplot of its own variable. Mahalanobis distance flags exactly this kind of case, and running it before a multivariate analysis catches problems that univariate screening cannot.
Alternatives to deletion
Derived variables
Step 6: derived variables
Most datasets need variables constructing before analysis, and each construction is a decision to record.
x with 6 − xComposite variables built by combining several sources need particular care about their direction and scale. If one contributing measure runs from 1 to 5 and another from 0 to 100, summing them lets the second dominate entirely; standardise first if each is meant to contribute equally. Record the formula used, because a composite defined slightly differently in two chapters of the same thesis is a common and avoidable inconsistency.
Run a correlation matrix of the items. If one correlates negatively with all the others, it is reverse-worded and has not been reverse-scored. Failing to catch this corrupts every total and every reliability coefficient that follows, and a negative Cronbach's alpha is almost always this error rather than a real finding.
Categorising continuous variables
Cutting a continuous variable into groups — median splits, tertiles, high and low — is common and usually costs you. It discards real information, typically sacrifices around 20% of statistical power, and treats two people either side of an arbitrary cut as different while treating the extremes of each group as identical. Keep the variable continuous unless the categories are substantively meaningful, such as a clinically established threshold.
Documentation
Documenting it reproducibly
| Artefact | Contents |
|---|---|
| Raw data | Untouched, read-only, backed up |
| Cleaning script | Every step, commented, running raw → clean |
| Cleaning log | Each decision, its reason, and cases affected |
| Codebook | Every variable: type, range, labels, missing codes |
| Cleaned dataset | Regenerable at any time from the script |
In the methods section, report the number of cases at each stage — recruited, completed, excluded and why, analysed. This is the equivalent of a PRISMA flow for participants, and reviewers check that the numbers reconcile.
“Of 248 responses, 12 were removed as incomplete (under 80% of items answered), 4 as duplicate submissions identified by matching timestamp and IP, and 2 for completion times under 90 seconds against a median of 11 minutes, leaving 230 for analysis. Exclusion criteria were specified before data inspection. Missing item-level data (1.8%) were handled by prorating scale scores where at least 80% of items were answered.”
Send the raw file and your codebook. A named statistician structures and validates the dataset, handles missingness appropriately, and returns a commented script alongside a cleaning log you can cite in your methods.
See data cleaningPitfalls
Six mistakes that cost marks
1. Cleaning by hand in a spreadsheet
Unreproducible, and impossible to defend when asked what was changed.
2. Deciding exclusion rules after seeing the results
Set them in advance. If you deviate, disclose it and report both versions.
3. Mean imputation
It shrinks variance, distorts correlations and produces standard errors that are too small.
4. Not declaring missing value codes
A 999 treated as a real value corrupts every statistic silently, with no error message.
5. Deleting outliers because they are inconvenient
Establish why the value is there. A genuine extreme value is data, not noise.
6. Forgetting to reverse-score
It corrupts every scale total and reliability estimate computed afterwards.
Answers
Frequently asked questions
What is data cleaning in research?
The process of preparing raw data for analysis: fixing structure, checking values are valid, removing duplicates, deciding how to treat missing values and outliers, and constructing derived variables. Every decision should be scripted and logged, because these choices affect results as much as the choice of statistical test.
Should I delete outliers?
Only if you can establish why the value is there. Data entry errors and impossible values should be corrected or set to missing; participants outside your target population can be excluded under a pre-specified rule. A genuine extreme value is real data and should normally be kept, with the analysis reported both with and without it.
What is the difference between MCAR, MAR and MNAR?
MCAR means missingness is unrelated to anything, so deletion is safe though wasteful. MAR means it is explained by other variables you measured, which is when multiple imputation works well. MNAR means it depends on the missing value itself — highest earners declining to state income — and no technique recovers it.
Is mean imputation acceptable?
Almost never. Replacing missing values with the mean adds cases at the centre of the distribution, which artificially reduces variance, pulls correlations towards zero and produces standard errors that are too small. Multiple imputation or maximum likelihood estimation are the defensible alternatives.
How much missing data is too much?
There is no fixed threshold. Below about 5% on a key variable, listwise deletion is usually tolerated; above 20%, most reviewers will expect multiple imputation or a substantial discussion. What matters more than the amount is the mechanism — a small amount of MNAR data can bias results more than a large amount of MCAR.
Should I use a median split to create groups?
Generally no. Categorising a continuous variable discards information, typically costs around 20% of statistical power, and treats participants either side of an arbitrary cut-off as different while treating the extremes within each group as the same. Keep it continuous unless the categories are substantively meaningful.
How do I document my data cleaning?
Keep the raw file untouched, do all cleaning in a commented script that runs from raw to cleaned, maintain a log of each decision with its reason and the cases affected, and keep a codebook. In the methods section, report case numbers at each stage so a reader can reconcile them.
How long does data cleaning take?
Frequently longer than the analysis. For a moderately sized survey dataset, several days is normal, and a messy multi-source dataset can take weeks. Budget for it explicitly, since underestimating it is one of the more common causes of a project running late.
Send the data. Get a fixed quote.
Attach your dataset or just describe the project. A named statistician replies with a price and a deadline, usually within one working day.