Introduction
A dataset can be tidy and still be unready for analysis. A column may have an unclear unit, a category may have several spellings, a missing value may be coded as a number, or a filtering step may have removed rows without a clear record. These problems can change what the data mean before any statistical method is chosen.
A final quality checklist makes the preparation work reproducible. It does not certify that data are error-free or decide which model is appropriate. Instead, it records what each row and variable means, which checks were performed, what decisions were made, and what uncertainty remains.
The Checklist: Meaning, Structure, and Evidence
Start by writing down the intended use of the prepared data and the meaning of one row. “One row per patient” differs from “one row per patient visit”; confusing those units can lead to counting the same person more than once. State how identifiers work and whether they should be unique under the chosen row definition.
Next, make a variable inventory. For every field, record its name, plain-language meaning, type, unit or category levels, and any permitted missing-value representation. A number stored in a file is not necessarily quantitative: an ID may be numeric-looking but still be a label. For a quantitative measurement, write the unit explicitly. For a categorical variable, state the allowed levels and their meanings.
Then apply the relevant checks to the actual data and retain evidence of their results. Earlier material on plausible ranges, missing-value codes, duplicate records, cross-variable consistency, exclusions, derived variables, and recoding provides the detailed checks; the final checklist brings their outcomes together. A checked item should have a result and, where needed, a decision—not merely a tick indicating that somebody looked at it.
| Checklist area | Record |
|---|---|
| Rows and keys | What one row represents; key definition; duplicate count and disposition |
| Variables | Meaning, type, units or allowed levels, and source or derivation |
| Missingness and validity | Missing codes, counts, range or consistency checks, and actions taken |
| Eligibility | Written inclusion and exclusion rules; counts before and after filtering |
| Open issues | Unresolved question, affected records or variables, owner or next action, and analysis limitation |
The same checklist should be applied in a repeatable order and saved with the prepared dataset or its accompanying documentation. If a correction or exclusion changes the data, record the rule and the resulting row count. This gives another analyst a way to understand how the final file was produced without having to infer decisions from the values alone.
Worked Example: Reconciling Rows After Duplicate and Eligibility Checks
Consider a wholly hypothetical file intended to contain one record per participant in a short program. The prespecified eligibility rule is age 18 years or older. The candidate quantitative variables are age in years and symptom scores in points at baseline and follow-up. The raw file has 10 rows. Two rows have participant ID P04 and contain identical values; the other distinct IDs are P01 through P09. The distinct records are:
| ID | Age (years) | Baseline score (points) | Follow-up score (points) |
|---|---|---|---|
| P01 | 34 | 12 | 8 |
| P02 | 52 | 10 | 7 |
| P03 | 29 | 8 | 5 |
| P04 | 41 | 11 | 9 |
| P05 | 63 | 14 | 10 |
| P06 | 37 | 9 | 6 |
| P07 | 46 | 13 | Missing |
| P08 | 17 | 7 | 5 |
| P09 | 55 | Missing | 8 |
Duplicate check. The row definition is one record per participant, and participant ID is the key. P04 appears twice with identical values, so the repeated row is documented and one copy is removed. The count is \(10-1=9\) distinct participant records.
Eligibility check. The rule is age at least 18 years. P08 is 17 and is excluded; the other eight distinct participants meet the age rule. Thus \(9-1=8\) eligible records remain. P09 is not excluded for a missing baseline score, because missingness is not part of the stated age-based eligibility rule.
Missingness check. Among the eight eligible records, P07 lacks a follow-up score and P09 lacks a baseline score. Six records have both scores, so \(8-2=6\) are complete for a comparison requiring both baseline and follow-up. The complete-pair proportion is \(6/8=0.750\), or 75.0%. This calculation describes completeness for those two fields; it does not by itself justify dropping the other two records from every possible analysis.
The reconciliation is \(10-1-1=8\): raw rows minus one duplicate row minus one ineligible participant equals eight eligible records. The prepared file can retain all eight eligible records with missing scores explicitly represented. Any later analysis that requires both scores must state how it handles the two incomplete records.
Check Types, Units, Categories, and Missing Codes Together
A checklist is most useful when variable definitions are compared with both the recorded values and a trustworthy source, such as a data dictionary, collection form, or codebook. A value can look plausible and still be wrong for the defined field. For example, a value of 8 might mean eight points, eight months, or a category code; its interpretation depends on the documented variable definition.
For quantitative variables, check that the unit and permitted range match the variable’s meaning. The range should come from a defensible source or rule, not from the observed minimum and maximum alone. For categorical variables, compare observed levels with the intended set and confirm any recoding against a traceable map. For missingness, establish which values mean missing before calculating summaries: a special number such as -9 must not be treated as a real measurement if the data documentation defines it as “not recorded.”
Worked Example: Verifying a Category Map and Missing-Value Code
In a second hypothetical review, eight eligible participant records have a group field with these recorded values: “Program” four times, “Usual care” twice, “UC” once, and blank once. The study documentation defines two intended groups—Program and Usual care—and explicitly states that “UC” is an abbreviation for Usual care. It also defines a blank group field as missing.
Confirm the category map. Because the documentation verifies that “UC” means Usual care, the mapped counts are Program \(=4\) and Usual care \(=2+1=3\). One group value remains missing. The check is not merely that there are two levels after recoding: the meaning of the abbreviation and the mapping rule are documented.
Report missingness. The group missingness rate is \(1/8=0.125\), or 12.5% of eligible records. This rate is calculated using all eight eligible records as the denominator, not just the seven records with a recorded group.
Check units and special codes. Suppose the same file stores symptom scores as -9 when no score was recorded. The data dictionary defines scores as points on a scale from 0 to 20 and identifies -9 as a missing code. The checklist therefore records that -9 is converted to the dataset’s explicit missing representation before score summaries or range checks are made. After that conversion, an observed score of 21 would fail the documented 0-to-20 range check and require investigation; it should not be silently changed to 20.
If the documentation had not established what “UC” meant, the correct result would be an unresolved category—not an assumed recoding. The distinction matters because a convenient-looking label is not evidence that two categories have the same meaning.
Document Decisions and Keep Unresolved Issues Visible
A final checklist should separate confirmed errors, valid unusual values, and unresolved questions. For each issue, record the field or records affected, the evidence considered, the action taken or deferred, and the resulting limitation. “Fixed bad values” is not an adequate record: it gives neither the rule used nor a way to reproduce the change.
Do not force a decision when available information cannot support one. If two records share an ID but differ on measurements, the repeated key signals an issue; it does not prove which record is correct or whether one row is a legitimate separate observation. Record the conflict and seek a source that can resolve it. Until then, state whether affected records are held out, retained provisionally, or otherwise handled, and how that choice affects the planned use of the data.
Worked Example: Reporting a Duplicate-Key Conflict
Suppose a hypothetical intake file has 120 rows and is intended to contain one record per participant. An ID check finds two IDs that each occur twice: one repeated pair is an exact copy, while the other pair has different baseline scores. The source form is not available at the time of review.
There are two excess rows across the repeated IDs: \(120-118=2\). The exact-copy pair has one redundant row, which can be removed under the documented one-row-per-participant rule. The conflicting pair cannot yet be resolved, so it is flagged rather than arbitrarily reduced to one record. After removing only the confirmed exact copy, \(120-1=119\) rows remain provisionally, including both conflicting rows.
The issue log states that the unresolved pair affects the unique-participant count and may affect any analysis using the baseline score. The provisional file is not described as having 119 confirmed unique participants. Instead, the report says that 119 rows remain, one duplicate copy was removed, and one duplicate-key conflict awaits source verification. That wording distinguishes a row count from a count of verified distinct participants.
Reconcile Counts and State the Readiness Decision
Counts should be reported at each stage where the set of rows changes: received, after confirmed duplicate handling, eligible, excluded, and complete for any specified fields. These counts can be checked because the row groups form partitions. If every row is either eligible or excluded, then the distinct-record count equals eligible records plus excluded records. If every eligible record is classified as complete or incomplete for specified variables, then eligible records equal complete plus incomplete records.
For the first example, \(9=8+1\). For the two score fields, \(8=6+2\), where the two incomplete records are P07 and P09. These identities do not prove the data are correct; they help reveal missing or double-counted rows in the preparation record.
Finish with a short readiness statement tied to the intended task. It should say what checks passed, what actions changed the file, and what issues remain open. If a limitation could affect interpretation, name it plainly. The statement should not imply that quality checks establish a statistical model’s assumptions or make an association causal. Data preparation supports later analysis; it cannot replace those later judgments.
State the intended task, row meaning, key, and written eligibility rule.
Record each field’s meaning, type, units or category levels, and missing-value representation.
Summarize range, consistency, category, missingness, duplicate, and exclusion checks that apply.
Show row counts at each stage, document changes, and list unresolved issues and their consequences.
Common Mistake: Treating “Clean” as “Ready”
A common mistake is to assume that a file is analysis-ready once obvious errors have been removed. That reasoning overlooks unclear row meaning, undocumented units, category labels that have not been verified, and exclusions whose counts do not reconcile. It also encourages unresolved questions to disappear from view.
The correction is to define “ready” for a particular task, preserve evidence for each check, and make uncertainty explicit. A documented decision to retain an unusual but valid value is different from overlooking it. A documented missing value is different from a code that is accidentally read as a measurement. A provisional dataset with a clearly stated limitation is more transparent than a file presented as fully resolved when it is not.
Check Your Understanding
Use the checklist logic to answer these questions.
- A file is intended to contain one row per person, but some IDs appear multiple times. What should be clarified or checked before treating the repeated rows as duplicates?
- A variable stores -9, and the data dictionary says -9 means “not recorded.” How should this code be handled before checking the variable’s measurement range?
- There are 50 eligible records, of which 44 have both measurements required for a planned analysis. What fraction is complete for those measurements, and what should happen to the other six records in the preparation report?
- Why is an observed value outside a plausible range a reason to investigate rather than automatic proof that the value is an error?
- What information should be recorded when two records have the same key but conflicting values and no source is available to resolve them?