Data Cleaning Checklist: 12 Steps Before Statistical Analysis
Data cleaning is not the act of making inconvenient observations disappear. It is the documented process of making the analytical dataset consistent with its definitions, source systems, and intended use.
1. Preserve the raw data
Keep an immutable copy of the received files or source extract. Record the date, source, query or export method, row count, column count, and file checksum when appropriate. Perform cleaning in a separate reproducible workflow.
2. Define the unit of observation
State what one row represents. A participant, visit, transaction, facility, or day produces different duplication and aggregation rules. Many analytical errors begin when the row grain is assumed rather than verified.
3. Validate the schema
Compare actual column names, types, units, formats, and allowed values with a data dictionary. Check whether identifiers were read as numbers, leading zeros were lost, or dates were interpreted in the wrong locale.
4. Audit row and key uniqueness
Count exact duplicate rows and duplicate keys separately. A repeated participant ID may be an error in a cross-sectional file but correct in a longitudinal file. Investigate before deleting.
5. Profile missing values
Calculate missingness by variable, row, group, site, and time. Distinguish system missing, not applicable, refused, below detection, and other coded states. See missing data in statistical analysis before selecting a remedy.
6. Check numeric ranges and units
Compare values with physical, clinical, business, or protocol limits. Confirm that units are consistent across sites and periods. An apparent outlier may be a unit conversion problem.
7. Standardize categories
Trim whitespace, normalize case only where safe, and map documented synonyms. Preserve the original value and transformation rule. Do not collapse categories merely because a group is small without an analysis rationale.
8. Validate dates and sequences
Check impossible dates, future dates, overlapping periods, negative durations, and events that occur out of order. Confirm time zones and daylight-saving treatment where timestamps cross systems.
9. Audit joins and reshaping
Before a join, test key uniqueness on both sides and predict the expected relationship: one-to-one, one-to-many, or many-to-many. Compare row counts and unmatched keys after the join. Silent row multiplication can corrupt every later statistic.
10. Investigate unusual values
Use tables and charts to examine extreme, rare, or internally inconsistent observations. Determine whether each is a valid event, data-entry error, measurement issue, or unresolved case. Do not remove observations only because they change significance.
11. Document derived variables
For each recode, score, transformation, and composite, record the formula, source variables, missing-value rule, valid range, and version. Test boundary cases and independently verify important calculations.
12. Freeze the analysis dataset
Save a versioned dataset and cleaning log. Record final row and column counts, exclusions, unresolved issues, and software versions. Analysis code should read this frozen dataset or reconstruct it deterministically from the raw source.
A minimum data-quality report
| Check | Evidence to retain |
|---|---|
| Structure | Row grain, dimensions, key fields |
| Completeness | Missingness by variable and important groups |
| Validity | Range, type, category, date, and unit checks |
| Uniqueness | Duplicate rows and duplicate keys |
| Consistency | Cross-field and cross-source rules |
| Transformations | Code, rationale, tests, and version |
Use the DataStatPro data management workflow to inspect and prepare analytical data, then connect the final dataset to the appropriate method through the statistical test guide.
Frequently asked questions
What is the first step in data cleaning?
Preserve the raw source and define what one row represents. Without those controls, later changes may be irreversible or misinterpreted.
Should outliers always be removed?
No. Investigate their origin and influence. Exclusion requires a defensible rule connected to data quality or the analysis plan, not the desired result.
How do I check whether a join duplicated rows?
Test key uniqueness before the join, predict the relationship, and compare row counts, key counts, and unmatched records afterward.
What should a cleaning log contain?
Record the source version, each transformation, rationale, affected observations, validation result, unresolved issue, software version, and final dataset identifier.