Analysez
AI Platform ↗
02. The Clinical Data Cleaning Masterclass6 min readFree Lesson

Why Real-World Research Data Needs Auditing

Human entry errors, spreadsheet type coercion, and the risks of analysing unverified hospital exports.

In this lesson you will learn

  • 1Identify how spreadsheet software silently alters gene names, medical record IDs, and date formats.
  • 2Quantify the distortion caused by sentinel missing codes (such as 999 or -99) and embedded text units on sample means.
  • 3Detect trailing whitespace and casing inconsistencies that split a single category into multiple levels.
On this page▼
01

The Silent Hazards of Spreadsheets in Clinical Research

What this section is about

This section catalogues the four most frequent spreadsheet corruptions in biomedical datasets: auto-date conversion, trailing whitespace, embedded units, and sentinel codes.

Why it matters for your study

A single uncleaned '999' sentinel code or trailing space in a category label can falsify your Table 1 means and Chi-square degrees of freedom without raising a software warning.

A landmark 2016 audit published in Genome Biology examined 3,597 supplementary spreadsheets across major journals and found that roughly one-fifth contained gene symbol corruptions (such as SEPT2 converted into 02-Sep) introduced automatically by spreadsheet software.

When clinical teams export data from electronic health records or survey forms, four silent corruptions frequently appear:

  • Invisible Whitespace & Casing Splits: Entering "Male", " Male ", and "male" creates three separate categorical levels in R, Python, and SPSS.
  • Embedded Measurement Units: Typing "102 mg/dL" inside a numeric cell forces the entire column to be read as a text string, dropping valid rows or halting numeric calculations.
  • Sentinel Missing Codes: Hospital registries often encode unrecorded labs as 999 or -99. If left as numbers, a few 999 entries severely inflate the sample mean and standard deviation.
  • Leading-Zero Truncation: Medical Record Numbers such as 00428 are coerced to integer 428, breaking linkage across multi-sheet hospital exports.
Gene-to-Date CoercionSEPT2 → "02-Sep"Silent symbol corruptionTrailing Whitespace"Male" vs " Male "Splits 1 category into 2 levelsText Units in Numbers"120 mmHg"Coerces numeric column to TextSentinel Missing CodesHR = 999Artificially spikes the Mean
Figure:Four common spreadsheet corruptions that occur silently without triggering syntax errors in statistical software.

Spreadsheet Hazard Lab: How One Dirty Cell Corrupts Your Mean

Compare naive uncleaned calculation vs sanitized extraction on Fasting Blood Glucose (mg/dL)

Uncleaned Naive Output
Mean = 249.8 mg/dL

SD = 367.1 | n = 6

After Auditing & Sanitizing
Mean = 100.0 mg/dL

SD = 8.4 | n = 5

Artificial Mean Distortion: +149.8 mg/dL false inflation caused by treating sentinel code 999 as a real glucose level!

Clinical Example

A diabetes clinic exports fasting blood glucose (mg/dL) for 7 patients, where one missing lab is coded as 999 and one cell includes the unit '102 mg/dL'.

Raw: [96, 104, 110, 88, 999, '102 mg/dL', 'N/A'] → Uncleaned Mean = 279.4 mg/dL vs Cleaned Mean = 100.0 mg/dL

Interpretation: Leaving the single 999 sentinel code in the column inflates the cohort mean from a normoglycaemic 100.0 mg/dL to a severely hyperglycaemic 279.4 mg/dL.

Common Pitfall

Running descriptive statistics immediately after importing a CSV or Excel file without inspecting minimum/maximum bounds and distinct category counts.

Key takeaways

  • 1Garbage In, Garbage Out (GIGO): no downstream statistical model can rescue conclusions computed from unverified raw cells.
  • 2Spreadsheet programs automatically coerce strings resembling dates (e.g. SEPT2) and strip leading zeros from identifier columns unless imported as text.
  • 3Numeric sentinel codes for missingness (such as 999) must be converted to true null values before computing descriptive or inferential statistics.
Self-Assessment

Check your understanding

One question per screen with immediate methodological rationale after each answer

1 of 5
Question 1 of 5

Why did the 2016 Genome Biology audit warn researchers about opening genomics tables in default spreadsheet software?

Finished reading this lesson?

Mark as completed to track your progress through the handbook

Why Real-World Research Data Needs Auditing | Biostatistics Handbook | Analysez