Data Cleaning and Quality Assessment

Overview

Data cleaning is the systematic process of detecting, investigating and resolving suspected data problems before analysis. It is not a search-and-delete exercise. A value that looks unusual may be a transcription error, a unit error, a duplicate record, an unusual but genuine clinical observation or evidence that the original variable definition was unclear.

A useful cleaning cycle has three stages:

1. screening to identify values or patterns requiring review;
2. diagnosis to determine the most likely explanation; and
3. editing or resolution according to documented evidence and rules.

This iterative framework was described specifically for epidemiological data by Van den Broeck and colleagues [1].

Data-quality assessment is broader. It evaluates whether the data are fit for their intended use. A dataset may contain no obvious typographical errors yet still be unsuitable because key outcomes are incomplete, dates are inconsistent, categories differ between sites or measurements were taken using incompatible procedures.

Why this matters

An apparently small data problem can change a medical conclusion.

Examples include:

• body weight entered in pounds but labelled as kilograms;
• a diastolic blood pressure entered in the systolic field;
• two records for the same randomised participant;
• an outcome date before the enrolment date;
• medication dose recorded without units;
• a participant allocated twice because duplicate identifiers were created; and
• an impossible value silently changed to the group mean.

Automated checks are useful, but they cannot establish truth. A value of 210 mmHg may be clinically alarming but valid. A value of 121 mmHg may fall within a permitted range but belong to the wrong participant. Data cleaning therefore combines programmed screening, clinical knowledge, source verification and documentation. Reproducible organisation also avoids error-prone practices such as encoding meaning only through spreadsheet formatting.

Data cleaning, validation and quality assessment

These related activities should be distinguished.

Data validation

Validation checks whether data conform to predefined rules.

Examples include:

• permitted category codes;
• minimum and maximum values;
• required fields;
• date formats;
• uniqueness of identifiers; and
• expected relationships between fields.

Validation may occur during data entry, import or analysis-dataset creation.

Data cleaning

Cleaning investigates validation failures and other suspicious patterns. It may lead to:

• confirmation that the value is valid;
• correction using a verified source;
• recoding;
• derivation from source fields;
• classification as missing;
• retention with a flag; or
• an unresolved query.

Data-quality assessment

Quality assessment asks whether the dataset is fit for the intended decision or analysis. Frameworks for clinical and electronic-health-record data commonly examine dimensions such as conformance, completeness and plausibility [2].

A dataset may be adequate for describing clinic attendance but inadequate for estimating medication adherence if dispensing dates are incomplete.

Core dimensions of data quality

Completeness

Completeness asks whether expected data are present.

Useful denominators include:

• all randomised participants;
• participants expected at a visit;
• measurements expected for each visit; and
• records expected from each site.

For example, 226/240 = 94.2% week-12 outcome completeness is more informative than stating that 14 values are missing.

Completeness should be assessed overall and by clinically relevant groups, sites and time periods.

Conformance

Conformance asks whether values follow the required format, type, coding and structure.

Examples include:

• dates stored as dates rather than free text;
• treatment codes limited to defined values;
• units consistent with the data dictionary;
• identifiers matching the required pattern; and
• one row per intended observational unit.

Plausibility

Plausibility asks whether values are believable in the clinical and study context.

Checks may be:

• univariate, considering one variable alone;
• relational, comparing related variables; or
• temporal, evaluating sequences over time.

A systolic blood pressure of 230 mmHg is physiologically possible but requires review. A week-12 visit before randomisation is temporally impossible under the protocol.

Consistency

Consistency assesses whether related values agree.

Examples include:

• allocated group matching the randomisation file;
• death status agreeing with date of death;
• pregnancy status compatible with recorded context where applicable;
• an adverse-event end date not preceding its start date; and
• medication-change records agreeing with the visit form.

A discrepancy does not identify which source is correct. Source hierarchy and query procedures are required.

Uniqueness

Uniqueness asks whether records that should occur once appear only once.

Examples include:

• one participant-level row per randomised identifier;
• one allocation per participant;
• one primary week-12 assessment under the protocol rule; and
• no duplicated device transmission imported twice.

Duplicate values are not necessarily duplicate records. Two participants may genuinely share the same age and blood pressure.

Timeliness

Timeliness concerns whether data are available at the required time and reflect the relevant period.

Late data may be clinically correct but unavailable for interim monitoring or database lock. A register may be complete historically but outdated for the current study population.

The World Health Organization’s data-quality guidance similarly emphasises completeness, consistency and verification when assessing health-service data [3].

Build checks from the protocol and dictionary

Cleaning rules should arise from variable definitions and study processes.

Table 1. Example checks for the simulated trial

Note: Plausibility limits are screening thresholds, not diagnostic or exclusion thresholds.

Hard and soft checks

A hard check prevents entry or processing when a rule must never be violated, such as an invalid category code.

A soft check produces a warning that can be overridden with justification, such as a very high but possible blood pressure.

Overly restrictive hard checks can force staff to enter false values. Clinically possible extremes should usually remain enterable with verification.

Screening the dataset

Frequency tables

Frequency tables identify:

• unexpected category codes;
• rare categories;
• excessive missingness;
• inconsistent spelling; and
• impossible combinations.

For example, Telemonitoring, telemonitor, T and 1 may represent the same category but will be treated as different unless harmonised.

Numerical summaries and plots

Minimum, maximum, percentiles, histograms and box plots help identify unusual values and distributional problems.

These tools do not determine validity. Outliers and influence are covered in BIO-P02-M04.

Duplicate checks

Duplicate assessment should proceed at several levels:

1. exact duplicate rows;
2. duplicate participant identifiers;
3. duplicate participant-visit combinations;
4. probable duplicates based on selected fields; and
5. duplicate source events imported more than once.

Probable duplicate identification must protect confidentiality and should not rely on insecure combinations of personal data.

Sequence and logic checks

Longitudinal data require sequence checks:

• baseline before follow-up;
• treatment start after allocation;
• adverse-event start before end;
• death date before administrative censoring;
• visit number consistent with timing; and
• no measurements after death unless the variable has another documented interpretation.

Diagnosing a flagged value

A flagged value should be traced through available evidence.

Possible sources include:

• source documents;
• electronic audit trail;
• device metadata;
• laboratory information system;
• case-report form;
• query response;
• data-transfer specification; and
• derivation code.

The investigator should ask:

1. Is the value impossible, improbable or merely unusual?
2. Is the unit correct?
3. Was the decimal point shifted?
4. Were fields transposed?
5. Does another source agree?
6. Is the problem isolated or systematic?
7. Could the screening rule be wrong?
8. Does correction require authorised source-data change?

Do not infer a replacement solely because it appears more reasonable.

Resolving problems

Possible resolutions include:

• retain as valid;
• correct from verified source;
• convert units using a documented rule;
• recode a known category;
• derive from verified component values;
• set to missing when the original value cannot be recovered;
• retain with an issue flag;
• exclude a record only under a justified rule; or
• leave unresolved and assess sensitivity.

Every edit should record:

• original value;
• revised value;
• reason;
• evidence;
• person or program making the change;
• date; and
• relevant query or rule.

For regulated clinical trials, current good-clinical-practice guidance emphasises reliable data, metadata, traceability and proportionate quality management [4]. Reproducible spreadsheet organisation also reduces reliance on fragile formatting and undocumented manual edits [5].

Worked example

Simulated cleaning log

simulated data

Table 2. Examples of flagged records and resolutions

Participant	Flag	Investigation	Resolution
HTM021	Weight 680 kg	Source form shows 68.0 kg	Correct to 68.0 kg with query reference
HTM044	Systolic 82, diastolic 128 mmHg	Source form shows fields reversed	Correct both fields
HTM087	Duplicate week-12 rows	One is a repeated export of the same form	Remove duplicate import, retain source record
HTM103	Week-12 date before randomisation	Date format was interpreted as month/day	Reparse using documented day/month source format
HTM156	Systolic 238 mmHg	Source and device log agree	Retain as valid; mark clinically verified
HTM214	Missing week-12 blood pressure	Participant did not attend and no alternative valid measure exists	Retain missing outcome and reason

Fit for purpose and quality by design MMed extension

Data quality is not an absolute property. It is evaluated against an intended use.

A dataset may be adequate for estimating mean week-12 systolic blood pressure but inadequate for analysing day-to-day home variability if device timestamps are unreliable.

Quality by design identifies data and processes that are critical to the study’s conclusions, then applies proportionate controls. This is preferable to applying the same level of checking to every field regardless of importance.

A critical-data review should focus on:

• eligibility;
• randomisation;
• treatment exposure;
• primary and key secondary outcomes;
• important baseline covariates;
• serious adverse events;
• follow-up status; and
• variables required for the prespecified estimand.

Suggested reporting

Common mistakes

Knowledge check

A systolic blood pressure of 238 mmHg is flagged by a range warning. Should it be deleted?

Answer and reasoning

No. It is unusual but clinically possible. The value should be investigated using source and contextual information. If verified, it should be retained and considered during exploratory and influence assessment.

What is the difference between screening and diagnosis in data cleaning?

Answer and reasoning

Screening identifies values or patterns requiring review. Diagnosis investigates why the flag occurred and whether it represents an error, valid unusual observation, structural problem or incorrect rule.

Why is a week-12 date before randomisation more serious than an uncommon blood-pressure value?

Answer and reasoning

The date sequence is impossible under the protocol and indicates a date, identifier or linkage error. An uncommon blood pressure may still be a genuine clinical observation.

Can a dataset be complete but still have poor quality?

Answer and reasoning

Yes. Every field may be populated yet use incorrect units, unreliable measurements, inconsistent definitions or duplicated records. Completeness is only one quality dimension.

Why should verified extreme values be retained?

Answer and reasoning

They may represent real patients and clinically important variation. Deleting them solely because they are extreme can bias estimates, understate variability and conceal data-generating features.

Key take-home messages

References

1. Van den Broeck J, Cunningham SA, Eeckels R, Herbst K. Data cleaning: detecting, diagnosing, and editing data abnormalities. PLoS Med. 2005;2(10):e267. https://doi.org/10.1371/journal.pmed.0020267
2. Kahn MG, Callahan TJ, Barnard J, Bauck AE, Brown J, Davidson BN, et al. A harmonized data quality assessment terminology and framework for the secondary use of electronic health record data. EGEMS (Wash DC). 2016;4(1):1244. https://doi.org/10.13063/2327-9214.1244
3. World Health Organization. Data quality assurance: module 1—framework and metrics. Geneva: World Health Organization; 2023. https://www.who.int/publications/i/item/9789240047365
4. International Council for Harmonisation of Technical Requirements for Pharmaceuticals for Human Use. ICH E6(R3) guideline for good clinical practice: principles and Annex 1. Geneva: ICH; 2025. https://database.ich.org/sites/default/files/ICH_E6%28R3%29_Step4_FinalGuideline_2025_0106.pdf
5. Broman KW, Woo KH. Data organization in spreadsheets. Am Stat. 2018;72(1):2–10. https://doi.org/10.1080/00031305.2017.1375989