Written and maintained by CASRAI Editorial Board
Last updated
Data cleaning steps are best run in a fixed order, on a copy of the raw file, with every change written down. The order that works for most tabular research data is: keep the raw copy, inspect, fix whitespace and text, handle duplicates, handle missing values, then record what you did. This page gives that workflow with the exact commands and menu names from the current OpenRefine, pandas and tidyr documentation, and it shows the points where cleaning silently goes wrong.
For tool-by-tool reshaping and text-cleaning recipes, see data wrangling for research. This page is the ordered checklist that sits above those recipes.
What data cleaning is used for in research
Cleaning makes a dataset consistent enough that an analysis result depends on the data and not on typing accidents: stray spaces that split one category into two, the same record entered twice, or blanks that a program reads as zero. It is a step in the research data lifecycle, not a substitute for a quality framework; a data quality framework for research data describes the dimensions you check against. Cleaning decisions also belong in your reproducibility record, because each one changes what the final numbers mean.
Install or access (official sources only)
- OpenRefine (no-code): download from openrefine.org/download. It is free software released under the BSD 3-clause license. The download page lists version 3.10.0, dated 2026-02-26, with Windows, Mac OS and Linux builds; the Windows ZIP includes an embedded Java install.
- pandas (Python): installation instructions are on the official site, pandas.pydata.org. The commands below follow the current pandas reference documentation, where
inplaceis deprecated, so they assign results back to a variable instead. - tidyr (R): documented at tidyr.tidyverse.org; the drop_na reference page is for tidyr 1.3.2.
Step-by-step: the data cleaning workflow
- Keep the raw file untouched. Save the original as read-only and do all cleaning on a copy or in a script that reads the original. A script that goes raw file to clean file can be re-run; manual edits in a spreadsheet cannot be replayed.
- Inspect before changing anything. Count rows, list column types, and look at the values in each categorical column. Look at the distinct values in every categorical column, since spelling and spacing variants show up there first. For the structure itself (one variable per column, one observation per row), see tidy data rules.
- Fix whitespace and text variants.
- OpenRefine: Edit cells > Common transforms > Trim leading and trailing whitespace, then Collapse consecutive whitespace.
- OpenRefine: Edit cells > Cluster and edit groups values that probably mean the same thing. The key collision methods are Fingerprint, N-gram Fingerprint, Metaphone3, Cologne Phonetic, Daitch-Mokotoff and Beider-Morse; the nearest neighbor methods are Levenshtein Distance and PPM. Keying means assigning a group, or key, to a data value, and you choose which grouped values to merge.
- Find duplicates, inspect them, then drop them. In pandas the signature is
DataFrame.duplicated(subset=None, keep='first'). By default all columns are used to identify a duplicate; passsubsetto compare only an identifier column such as a participant ID.keep='first'marks every duplicate except the first occurrence,keep='last'marks all except the last, andkeep=Falsemarks every copy. The method returns a boolean Series. Usekeep=Falseto look at every copy side by side before deciding which row to retain, then remove the unwanted ones. - Decide what to do about missing values. First work out why values are missing; MCAR, MAR and MNAR explains why that decides whether deleting rows is safe. Then:
- pandas
DataFrame.dropna(*, axis=0, how=<no_default>, thresh=<no_default>, subset=None, inplace=<no_default>, ignore_index=False).df.dropna()drops rows containing any missing value,df.dropna(how="all")drops only rows that are entirely missing,df.dropna(thresh=2)keeps rows with at least two non-missing values (threshcannot be combined withhow), anddf.dropna(subset=["name","toy"])checks only the listed columns. - R tidyr:
drop_na(data, ...)drops rows where any column named in...contains a missing value; with no columns named, all columns are checked.df |> drop_na(x)checks only columnx.
- pandas
- Write the cleaning log. Record each step, the tool and command, the number of rows before and after, and the reason. A log with row counts lets a reviewer see that step 4 removed 12 rows and step 5 removed 40, without re-running anything. Document the final column meanings in a codebook.
Output and how to read it
After each step, compare row counts to the step before. A step that removes far more rows than expected usually means a subset or how setting was wrong, not that the data was that dirty. duplicated() returns True or False per row, so df.duplicated(subset=["id"], keep=False).sum() counts every row involved in a duplicate pair, while a plain keep='first' count only counts the surplus copies. The cleaning log from step 6 is the output you hand to collaborators along with the clean file.
Troubleshooting
Why does == np.nan find nothing?
Because the comparison is always False. The pandas missing-data guide states that equality comparisons between np.nan, NaT and NA do not act like None, and np.nan == np.nan is False. Use pd.isna() and pd.notna(); isna() also treats None as missing.
Why did an integer column turn into decimals?
pandas uses numpy.nan as the missing marker for NumPy dtypes, so an int64 column that gets a missing value is coerced to float64. Nullable types such as Int64, which use NA, avoid this coercion; convert_dtypes() converts a frame to them.
Why does inplace=True warn?
For dropna, inplace is deprecated since pandas 3.1.0 and will be removed in pandas 4.0. Assign the result instead: df = df.dropna(subset=["id"]).
Should I delete outliers as part of cleaning?
Not automatically. Flag unusual values, check them against the source record or instrument, and decide with the study protocol in hand; record the decision in the log either way.
Free alternatives
| Tool | Use it in this workflow for | Documented feature |
|---|---|---|
| OpenRefine | Step 3, text and whitespace | Trim leading and trailing whitespace, Collapse consecutive whitespace, Cluster and edit |
| pandas | Steps 4 and 5, duplicates and missing values | duplicated(subset, keep), dropna(how, thresh, subset), isna(), convert_dtypes() |
| tidyr (R) | Step 5, missing values | drop_na() |
FAQ
What are the main steps of data cleaning?
Keep a raw copy, inspect the data, standardise text and whitespace, deal with duplicates, deal with missing values, and log every change. The order matters because trimming whitespace first lets the duplicate check match records that differed only by a space.
Should I clean data before or after writing the data management plan?
Plan it first. A data management plan is where you say which versions of the data you will keep and how changes are documented, so the cleaning log has a place to live.
Is deleting rows with missing values acceptable?
Sometimes. It depends on why the values are missing and how many rows you lose; see the MCAR, MAR and MNAR guide linked above before using dropna() or drop_na() on a whole dataset.
