Data Cleaning

Data cleaning is the process of detecting and correcting or removing inaccurate, incomplete, duplicate, or inconsistent entries from a dataset to improve its quality before analysis. It is an essential step in virtually every quantitative research project, whether the data come from surveys, clinical records, laboratory instruments, or automated sensors. The goal is to ensure that the dataset accurately represents the phenomena being studied and that analytical results are not distorted by preventable errors.

Few real datasets are entirely free of errors. Manual data entry introduces typos and transposition mistakes, electronic health record extractions may include formatting inconsistencies across systems, and sensor data can contain artifacts from equipment malfunctions. Researchers who proceed to analysis without cleaning their data risk drawing conclusions that reflect noise rather than signal. In clinical trials, regulatory agencies such as the FDA and EMA expect documented data cleaning procedures as part of Good Clinical Practice, and audit findings related to poor data quality can jeopardize regulatory submissions. In epidemiology, uncleaned data can produce misleading prevalence estimates, distorted risk ratios, and unreliable trend analyses. The consequences of poor data quality extend beyond individual studies; when uncleaned data enter repositories and are reused by other researchers, errors propagate through the scientific literature.

Data cleaning typically follows a structured sequence of checks. Range checks flag values that fall outside plausible or valid bounds, such as a recorded systolic blood pressure of 900 mmHg or an age of negative five years. Consistency checks verify that logically related variables agree, for example confirming that a participant recorded as male does not have a pregnancy outcome variable populated. Completeness checks identify missing values and determine whether the missingness follows a pattern (such as a particular survey question being skipped systematically) or appears random.

Duplicate detection identifies records that appear more than once, which can occur when participants are enrolled at multiple sites, when electronic systems create redundant entries, or when data merging procedures go wrong. Deduplication requires careful judgment; apparent duplicates may actually be separate individuals who share the same name and birth date, so researchers often verify against additional fields before removing records.

Outlier detection examines the distribution of continuous variables for values that are technically possible but statistically improbable. Whether outliers are corrected, removed, or retained depends on the context. A recorded weight of 400 kg may be a data entry error (40.0 kg entered without the decimal), a measurement artifact, or a genuine extreme value. Documentation of how each outlier was handled is essential for transparency.

Automated cleaning scripts in R, Python, or SAS can standardize date formats, convert units, recode categorical variables, and log every modification. A cleaning log (or audit trail) records every change made to the original data, including what was changed, why, when, and by whom. This documentation is critical for reproducibility and regulatory compliance. Cleaning for the main analysis should be completed before that analysis begins (in a clinical trial, before the database is locked), and the cleaning should be described in the methods section of the manuscript.

Data cleaning is necessary in virtually every quantitative research project before the main analysis begins. It is especially important when data come from multiple sources or sites with different entry conventions, when data have been manually entered, or when the dataset is large enough that errors are statistically inevitable. It is not a substitute for good data collection design; problems that can be prevented at the point of entry (through validation rules, restricted response options, and training) should be addressed there rather than cleaned up after the fact.

One frequent error is performing ad hoc cleaning without a documented protocol, making it impossible for others to understand or reproduce the decisions made. Another mistake is deleting records with missing data without considering the impact on sample size and potential bias; listwise deletion of incomplete records can introduce selection bias if missingness is related to the outcome. Researchers sometimes correct obvious errors (like a decimal point shift) without verifying against source documents, introducing new errors in the process. Finally, some researchers clean data iteratively during analysis, which creates a risk of unconsciously shaping the data to fit the desired results, a practice that can shade into p hacking.

A multicenter trial coordinating center receives data from 14 clinical sites and identifies 312 out of range values, 89 cross field inconsistencies, and 17 duplicate participant records across 6,400 enrollment records. Range check queries are sent to sites for verification, resulting in 287 corrections and 25 confirmed true values. Date formatting is standardized from five different site conventions to a single ISO 8601 format. All changes are recorded in a cleaning log with timestamps, the original value, the corrected value, and the reason for each change. The final clean dataset is locked before analysis begins.