The Spreadsheet Is Not the Study

The spreadsheet says a patient’s temperature is 101. In Fahrenheit, that suggests a fever. In Celsius, it suggests the patient was briefly inside an industrial process.

The computer will calculate the average either way.

A mathematical operation on a column is not evidence that the column represents a coherent quantity. We have to understand the records first.

Start with the unit of observation

What does one row describe: one person, one appointment, one hospital admission, or one measurement? If a patient has five visits and another patient has one, treating six rows as six independent people changes the scientific question.

A data dictionary records what variables mean, allowed values, units, collection dates, and whether a value is a category, count, or measurement.

Missing does not mean zero

Suppose recorded incomes are 40, 50, 60, and blank. Replacing the blank with zero produces a mean of 37.5. Omitting the blank produces 50. Neither is guaranteed to estimate the intended population mean; the correct treatment depends on why the value is missing.

Unknown is not the same thing as none. Use explicit missing-value markers rather than inventing a number.

Also inspect duplicates, impossible dates, changing codes, mismatched units, and whether identifiers inadvertently reveal personal information.

A reproducible cleaning log

Keep the original source read-only. Record transformations as scripts or documented operations: which rows were excluded, which categories were recoded, which measurement conversions were made, and why.

For example, converting pounds to kilograms is reproducible if the original units and conversion are documented. Deleting “suspicious” high observations without stating the criterion is not.

A tidy-looking result can still be inaccurate if someone has quietly deleted the events that made the dataset interesting.

Example audit in R

R’s airquality example includes historical observations and missing measurements:

data(airquality, package = "datasets")
dim(airquality)
colSums(is.na(airquality))
summary(airquality)

We can inspect missingness before fitting a model. If we use na.omit(), report how many rows were dropped and which variables caused the exclusion. Our air-quality field notebook demonstrates this.

Check yourself

1. Why does replacing a missing measurement with zero alter the research claim?
2. An observation appears twice. When should it be removed, and when might it be legitimate?
3. What metadata would you require before sharing an analysis-ready dataset?

Worked responses

1. Zero is a measured value with scientific meaning, whereas a missing measurement is unknown.

2. Remove a confirmed erroneous duplicate; repeated real events or measurements might be legitimate separate records.

3. Source, units, observation grain, definitions, collection period, transformations, exclusions, licensing, and known limitations.

Further reading

The R documentation for the airquality dataset demonstrates why source details matter.

Demonstrate a transformation without destroying the evidence

Suppose five synthetic records report minutes as $[10,12,-99,15,1200]$, and the documented codebook says $-99$ means missing. The raw mean is 227.6, a valid arithmetic calculation of an invalidly mixed code and measurement. Converting only the documented code to missing leaves observed values $[10,12,15,1200]$ and mean 309.25. That surprising result does not authorize deleting 1200. It requires checking units, provenance, and whether the long duration is genuine.

If source verification establishes that 1200 was recorded in seconds while the other entries were minutes, a documented conversion gives $[10,12,15,20]$, mean 14.25. Preserve the raw entry, record the corrected units and reason, and keep the missing record as missing. This sequence illustrates why a cleaning log must distinguish confirmed correction from a guess made to make results look reasonable.

Duplicate detection also needs a key. Two people can share a name and outcome; one person can legitimately have repeated visits. Dropping identical-looking rows without defining record identity can erase valid observations. A reproducible pipeline should assert expected keys and retain counts at every filtering stage.

Further practice

1. Why is converting every zero to missing unsafe?
2. Which file should remain unchanged when a verified unit error is corrected?
3. What should be reported if the origin of the value 1200 cannot be resolved?

Worked further responses

1. Zero may be a valid measurement, and blanket replacement changes the distribution and denominator.
2. The immutable raw source. Write a derived dataset and transformation log instead.
3. State the uncertainty, retain the raw record, and compare results under defensible handling scenarios. Do not silently declare the value erroneous because it is inconvenient.