4  Cleaning House

This chapter rarely gets much attention, and yet you will spend more of your career here than anywhere else in this book. Real data arrive dirty: typos, impossible values, blanks, dates stored as text, “999” standing in for “don’t know.” Before you fit a single model, you have to clean house. Do it carelessly and every analysis downstream rests on a shaky foundation.

4.1 Learning Objectives

  1. Treat your measures as sensors that produce data, not as the truth itself.
  2. Recognize the common ways data arrive dirty and how to check for them.
  3. Understand missingness and why why the data are missing matters.
  4. Handle outliers and recoding deliberately, not reflexively.

4.2 Measures Are Sensors, Not Truth

Start with a mindset. A measure is a sensor - a thermometer, a questionnaire, a reaction-time key. The number it emits is data about the construct, not the construct itself. Sensors drift, mis-fire, and get bumped. A survey respondent mis-clicks; a scale is off by a pound; a coder’s attention wanders. The data are a noisy readout of reality, and cleaning is the work of separating “the sensor hiccupped” from “the world is genuinely like this.” Keep that distinction and you will make better calls at every fork.

4.3 First, Actually Look

The cheapest, most-skipped step is to look at your data before touching it. str(), summary(), and a few plots catch a surprisingly large share of problems:

library(tidyverse)    # dplyr, ggplot2, purrr, tibble, readr, stringr, forcats
source("_common.R")   # book-wide helpers: round2(), fmt_p(), tidy2()

set.seed(3)
dat <- tibble(
  age    = c(34, 29, 41, 220, 38, NA, 45),       # 220 is impossible
  income = c(52, 61, 48, 55, -3, 60, 999),       # -3 impossible; 999 a code?
  group  = c("A", "A", "b", "B", "A", "B", "A")  # "b" should be "B"
)
glimpse(dat)   # the tidyverse's str(): one row per column, types and values
Rows: 7
Columns: 3
$ age    <dbl> 34, 29, 41, 220, 38, NA, 45
$ income <dbl> 52, 61, 48, 55, -3, 60, 999
$ group  <chr> "A", "A", "b", "B", "A", "B", "A"
summary(dat)   # min/max here is what exposes the impossible values
      age             income            group  
 Min.   : 29.00   Min.   : -3.0   Length   :7  
 1st Qu.: 35.00   1st Qu.: 50.0   N.unique :3  
 Median : 39.50   Median : 55.0   N.blank  :0  
 Mean   : 67.83   Mean   :181.7   Min.nchar:1  
 3rd Qu.: 44.00   3rd Qu.: 60.5   Max.nchar:1  
 Max.   :220.00   Max.   :999.0                
 NAs    :1

Three problems already stand out: an age of 220, a negative income (and a suspicious 999), and a stray lowercase “b”. None of these needed a model; a look was enough.

4.4 Impossible Values and Sanity Checks

Define what is possible and flag what is not. This is domain knowledge, not statistics:

NoteWorking in SPSS, Julia, or Python?

The code tabs below assume this chapter’s data is already loaded. Grab the one-file setup for your language from Getting the Book’s Data, run it once, then load what you need by name - this chapter uses ch04-dirty. For example, book_data("ch04-dirty") in R, Julia, or Python, or !bookdata name = "ch04-dirty". in SPSS. Every language reads the same shipped files, so your numbers will match the ones printed here exactly.

dat <- dat |>
  mutate(
    age    = if_else(age > 120, NA, age),        # no one is 220
    income = if_else(income < 0 | income == 999, # impossible, and the 999 code
                     NA, income),
    group  = str_to_upper(str_trim(group))       # fix "b" and stray spaces
  )

dat |> count(group)
group n
A 4
B 3 
INSERT FILE='data/sim/ch04-dirty.sps'.

* Recode impossible and coded-missing values to system-missing.
RECODE age (121 THRU HIGHEST=SYSMIS).
RECODE income (LOWEST THRU -0.001=SYSMIS) (999=SYSMIS).
* Fix "b" and stray spaces in group.
COMPUTE group = UPCASE(LTRIM(RTRIM(group))).
EXECUTE.
* Frequency table of the cleaned group variable (missing shown by default).
FREQUENCIES VARIABLES=group.
                            group
+-------+---------+-------+-------------+------------------+
|       |Frequency|Percent|Valid Percent|Cumulative Percent|
+-------+---------+-------+-------------+------------------+
|Valid A|        4|  57.1%|        57.1%|             57.1%|
|      B|        3|  42.9%|        42.9%|            100.0%|
+-------+---------+-------+-------------+------------------+
|Total  |        7| 100.0%|             |                  |
+-------+---------+-------+-------------+------------------+
using CSV, DataFrames, StatsBase
df = CSV.read("data/sim/ch04-dirty.csv", DataFrame)

df.age    = [ismissing(x) || x > 120 ? missing : x for x in df.age]                # no one is 220
df.income = [ismissing(x) || x < 0 || x == 999 ? missing : x for x in df.income]   # impossible; 999 a code
df.group  = uppercase.(strip.(string.(df.group)))                                  # fix "b" and stray spaces
countmap(df.group)
Dict{String, Int64} with 2 entries:
  "B" => 3
  "A" => 4
import numpy as np, pandas as pd
df = pd.read_csv("data/sim/ch04-dirty.csv")

df.loc[df.age > 120, "age"]      = np.nan     # no one is 220
df.loc[df.income < 0, "income"]  = np.nan     # negative income is impossible here
df.loc[df.income == 999, "income"] = np.nan   # 999 was a "missing" code, not a value
df.group = df.group.astype(str).str.strip().str.upper()   # fix "b" and stray spaces
df.group.value_counts(dropna=False)
group
A    4
B    3
Name: count, dtype: int64

Note the tricky one: 999 was never a real income. It was a placeholder for “declined to answer,” and software cannot know that. If you had left it in, the mean income would have been badly wrong. Always look for these coded-missing sentinels (999, -1, “N/A”, empty strings) and convert them to a genuine NA.

4.5 Missing Data: The Why Matters

Once the sentinels are cleaned, you are left with genuine gaps. How you should handle them depends entirely on why they are missing - a point important enough that one of us (PEM) co-wrote a whole book on it (McKnight et al. 2007), plus a handbook chapter (McKnight and McKnight 2013). The three flavors:

  • MCAR (Missing Completely At Random): the gap is unrelated to anything - a dropped test tube. Dropping these cases loses power but does not bias you.
  • MAR (Missing At Random): missingness depends on things you observed (older people skip the income item, and you recorded age). Recoverable with the right methods.
  • MNAR (Missing Not At Random): missingness depends on the unobserved value itself (high earners hide their income). The hardest case: no method fully fixes it, and it can quietly bias every result.
# how much is missing, and where?
dat |> summarise(across(everything(), \(v) sum(is.na(v))))
age income group
2 2 0 
* Real SPSS answers this with MVA VARIABLES=ALL, a dedicated missing-value
* analysis. PSPP has not implemented MVA, and FREQUENCIES reports the same
* valid/missing counts, so that is what runs here.
INSERT FILE='data/sim/ch04-dirty.sps'.

FREQUENCIES VARIABLES=age income group.
            Statistics
+---------+----------+----------+
|         |    age   |  income  |
+---------+----------+----------+
|N Valid  |         6|         7|
|  Missing|         1|         0|
+---------+----------+----------+
|Mean     |     67.83|    181.71|
+---------+----------+----------+
|Std Dev  |     74.75|    361.07|
+---------+----------+----------+
|Minimum  | 29.000000| -3.000000|
+---------+----------+----------+
|Maximum  |220.000000|999.000000|
+---------+----------+----------+

                                  age
+------------------+---------+-------+-------------+------------------+
|                  |Frequency|Percent|Valid Percent|Cumulative Percent|
+------------------+---------+-------+-------------+------------------+
|Valid   29.000000 |        1|  14.3%|        16.7%|             16.7%|
|        34.000000 |        1|  14.3%|        16.7%|             33.3%|
|        38.000000 |        1|  14.3%|        16.7%|             50.0%|
|        41.000000 |        1|  14.3%|        16.7%|             66.7%|
|        45.000000 |        1|  14.3%|        16.7%|             83.3%|
|        220.000000|        1|  14.3%|        16.7%|            100.0%|
+------------------+---------+-------+-------------+------------------+
|Missing .         |        1|  14.3%|             |                  |
+------------------+---------+-------+-------------+------------------+
|Total             |        7| 100.0%|             |                  |
+------------------+---------+-------+-------------+------------------+

                                income
+----------------+---------+-------+-------------+------------------+
|                |Frequency|Percent|Valid Percent|Cumulative Percent|
+----------------+---------+-------+-------------+------------------+
|Valid -3.000000 |        1|  14.3%|        14.3%|             14.3%|
|      48.000000 |        1|  14.3%|        14.3%|             28.6%|
|      52.000000 |        1|  14.3%|        14.3%|             42.9%|
|      55.000000 |        1|  14.3%|        14.3%|             57.1%|
|      60.000000 |        1|  14.3%|        14.3%|             71.4%|
|      61.000000 |        1|  14.3%|        14.3%|             85.7%|
|      999.000000|        1|  14.3%|        14.3%|            100.0%|
+----------------+---------+-------+-------------+------------------+
|Total           |        7| 100.0%|             |                  |
+----------------+---------+-------+-------------+------------------+

                            group
+-------+---------+-------+-------------+------------------+
|       |Frequency|Percent|Valid Percent|Cumulative Percent|
+-------+---------+-------+-------------+------------------+
|Valid A|        4|  57.1%|        57.1%|             57.1%|
|      B|        2|  28.6%|        28.6%|             85.7%|
|      b|        1|  14.3%|        14.3%|            100.0%|
+-------+---------+-------+-------------+------------------+
|Total  |        7| 100.0%|             |                  |
+-------+---------+-------+-------------+------------------+
using CSV, DataFrames
df = CSV.read("data/sim/ch04-dirty.csv", DataFrame)

describe(df, :nmissing)     # how much is missing, and where?
3×2 DataFrame
 Row │ variable  nmissing
     │ Symbol    Int64
─────┼────────────────────
   1 │ age              1
   2 │ income           0
   3 │ group            0
import pandas as pd
df = pd.read_csv("data/sim/ch04-dirty.csv")

df.isna().sum()                # how much is missing, and where?
age       1
income    0
group     0
dtype: int64

The default in most software is listwise deletion - throw out any row with a gap - which is fine under MCAR and quietly biasing otherwise. Modern practice prefers multiple imputation, but the first, non-negotiable step is simply to report how much is missing and think hard about which flavor you are facing.

4.6 Outliers: Investigate, Don’t Reflexively Delete

An outlier is not automatically an error. It might be your most important data point (the one patient who responded, the fraud you were hired to find). The rule: investigate before you act. A value can be (1) an impossible error - fix or remove it; (2) a real but extreme observation - keep it, and consider a robust method or a transformation; or (3) genuinely interesting - study it. Deleting outliers just because they are inconvenient turns honest data into a misleading result.

tibble(income = c(52, 61, 48, 55, 60, 58, 300)) |>   # 300 stands out
  mutate(z = (income - mean(income)) / sd(income)) |> # z-scores flag the extreme
  round2()
income z
52 -0.42
61 -0.32
48 -0.46
55 -0.38
60 -0.33
58 -0.35
300 2.27

A common screen is “more than 3 standard deviations from the mean,” but treat it as a flag for inspection, not a delete key.

ImportantCleaning Is a Documented Decision, Not a Secret

Every value you change, drop, or recode is a decision that shapes your results - so write it down. Do your cleaning in a script (never by hand-editing the raw file), keep the raw data untouched, and comment why each change was made. Reproducibility starts here, in the least glamorous chapter, long before the p-values.

4.7 Challenge

TipDo One Yourself

Take a messy vector of your own making - mix in an impossible value, a “-99” missing-code, and a genuine extreme.

  1. Write a short cleaning script that converts the impossible value and the -99 code to NA, and report how many NAs you created and why.
  2. Compute the mean before and after cleaning. How much did the fake 999/-99 code distort it?
  3. For your genuine extreme value, argue in two sentences whether you would keep it, transform, or remove it - and what that choice does to your conclusion.

4.8 Where We Go Next

With clean, honest data in hand, we can finally start making inferences. The bridge from describing a sample to reasoning about a population runs through one beautifully standardized distribution - the z-distribution.

McKnight, Patrick E., and Katherine M. McKnight. 2013. “Missing Data in Psychological Science.” In The Oxford Handbook of Research Strategies for Clinical Psychology, edited by Jonathan S. Comer and Philip C. Kendall. Oxford University Press.
McKnight, Patrick E., Katherine M. McKnight, Souraya Sidani, and Aurelio José Figueredo. 2007. Missing Data: A Gentle Introduction. Methodology in the Social Sciences. Guilford Press.