Data Cleaning Log Template (Statistics, Missing Data, R and Python)

Data Cleaning Log: [Your data set and project]

A working cleaning log for your own data set: it sets up a reproducible workflow in which the raw file is never edited, every change is made by a script and recorded in a log table, outliers are handled by a rule fixed in advance, and missing data are described by mechanism before any imputation. It is for students handling their first real data set, thesis writers who must defend every excluded row to an examiner, and analysts who inherit messy exports. It speeds up learning by turning cleaning from a series of ad hoc fixes into a fixed sequence of checks you can repeat, explain and test yourself on. The worked examples use fictional data; replace them with your own. Source basis: Rubin (1976) and Little and Rubin on missing data, Tukey (1977) on fences, and the NIST/SEMATECH e-Handbook, written in original words.

Document control

Principles of a reproducible cleaning workflow

Data cleaning changes your data, and therefore your results. A reproducible workflow means that anyone with the raw file and your script can recreate the cleaned file exactly, and can see every decision and its reason. Five rules make that possible.

1. Raw data are never edited. Store the original export in a data/raw/ folder and make it read-only. Never "fix a typo" in the spreadsheet: the fix is invisible and cannot be undone or reviewed.
2. All changes are made by a script. The script reads the raw file and writes a new file to data/clean/. Running it again from scratch gives the same result.
3. Every step is logged. Each rule gets one row in the cleaning log: what the rule was, how many rows it touched, why, and where in the script it lives. Rows touched are counted by the script, not by hand.
4. Rules are fixed before results are seen. Valid ranges, outlier policy and missing-data handling come from the codebook (academic/stats-variable-codebook) and the analysis plan, not from what makes the result significant.
5. Flag before you delete. Prefer a flag column (flag_credits = TRUE) over deleting rows, so the analysis can be run with and without the flagged cases.

Rules fixed in advance

Fill this table before opening the data. Each rule later becomes a script step and a log row.

The cleaning log

Rows at start: [ ] · Rows at end: [ ] · Difference explained by steps: [ ]

Checks in order

Two kinds of duplicate need different actions. An exact duplicate (every column identical) is usually a double submission and can be dropped with a log row. A key duplicate (same ID, different answers) is a real conflict: two people sharing an ID, or one person answering twice with changed answers. Do not drop either copy silently; decide by a rule (first submission, most complete submission) and log it.

An impossible value is different from an unusual one. An age of 210 is impossible, so it is an error: set it to missing and log it. A student with 40 library visits per term is unusual but possible, so it is a candidate outlier: apply the pre-set rule, which usually means flag, keep, and check the effect in a sensitivity analysis.

Outlier handling policy

An outlier is a value far from the rest of the data. It may be an error that the range check missed, a real but rare case, or a sign that the variable has a skewed distribution. Deleting outliers because they weaken a result is one of the most common forms of research bias, so the policy is fixed before the analysis and applied blindly.

A widely used rule, from Tukey (1977), uses the quartiles $Q_1$ and $Q_3$ and the interquartile range $\text{IQR} = Q_3 - Q_1$:

$\text{inner fences: } Q_1 - 1.5\,\text{IQR},\; Q_3 + 1.5\,\text{IQR} \qquad \text{outer fences: } Q_1 - 3\,\text{IQR},\; Q_3 + 3\,\text{IQR}$

Fences are robust: one extreme value barely moves them. A rule based on the mean and standard deviation ("more than 3 SD from the mean") is weaker, because the extreme value itself inflates the SD and can hide itself. Note also that software computes quartiles in slightly different ways; R and pandas both default to the same linear interpolation method, but other tools may give slightly different fences.

For skewed variables, such as counts or incomes, many "outliers" disappear after a log transformation; see academic/stats-data-transformations. For regression, unusual points are judged by their influence on the fitted model (leverage, Cook's distance) rather than by fences on single variables; see academic/stats-assumption-checklist.

Missing data: mechanisms and what they mean

Plain language first. Missing values are not just holes; the reason they are missing matters. Rubin (1976) defined three mechanisms, by asking what the probability of a value being missing depends on.

The names are confusing: "missing at random" does not mean "missing for no reason". It means the reason is captured by variables you have.

What complete-case analysis assumes. Complete-case (listwise) analysis drops every row with any missing value in the variables used. It is the default in R (na.omit, model functions) and in statsmodels formulas. It is unbiased when data are MCAR, and in some regression settings also when missingness depends only on the predictors, but it always loses cases: in worked example A, three variables with only five missing values between them reduce 11 rows to 6. The loss of power is often larger than people expect, because each row is dropped for any one missing value.

Simple imputation. Replacing missing values with one guess (the mean, the median, the last observation) is quick but treats the guess as if it were observed. Mean imputation shrinks the standard deviation and weakens correlations, and every simple method understates uncertainty, so standard errors come out too small and p-values too small. Worked example A shows the shrinkage: the SD of visits drops from 11.74 to 10.51 after mean imputation.

Multiple imputation (MI). MI creates several (often 20 or more) completed data sets, each filling the gaps with plausible values drawn from a model that uses the other variables, analyses each one, and combines the results with Rubin's rules so that the final standard errors include the uncertainty about the missing values. It is valid under MAR if the imputation model includes the analysis variables (including the outcome) and the variables that predict missingness. In R the standard tool is the mice package (van Buuren and Groothuis-Oudshoorn 2011, install.packages("mice")); in Python, statsmodels provides statsmodels.imputation.mice (classes MICEData and MICE). Both use chained equations: each incomplete variable is imputed from the others in turn.

Little's MCAR test, with caveats. Little (1988) proposed a test of the null hypothesis that data are MCAR (in R, for example, naniar::mcar_test()). Use it with care:

• It can only reject MCAR; a non-significant result does not show MCAR, especially in small samples with low power.
• It cannot distinguish MAR from MNAR. No test on the observed data can, because MNAR depends on values you do not have.
• It assumes approximately multivariate normal data, which is doubtful for Likert items, counts and categories.
• With large samples it rejects for trivial departures.

A more useful habit is to compare rows with and without missing values on the observed variables (for example, the year of study for students with and without a credits value). Differences point away from MCAR and tell you which variables to include in the imputation model.

The chart shows missing values per variable in worked example A after the range check. Seeing the counts side by side helps decide where to focus: here, age and visits each lose two values.

Missing-data plan for my project

Worked example A: cleaning a messy survey export

Setting. A Lakemoor University (fictional) library-use survey export has 12 rows: student ID, faculty (typed by students), age, year of study, credits earned and library visits this term. The rules fixed in advance are the example rows in the "Rules fixed in advance" table.

# Raw export from the Lakemoor University library-use survey (fictional). Never edit raw.
raw <- data.frame(
  id      = c(101, 102, 103, 103, 104, 105, 106, 107, 108, 109, 110, 111),
  faculty = c("Arts", "SCI", "sci", "sci", "Business", "Arts", "Science", "ENG", "Arts", "BUS", "Eng", "SCI"),
  age     = c(19, 21, 20, 20, 210, 22, 18, NA, 23, 19, 20, 24),
  year    = c(1, 3, 2, 2, 3, 4, 1, 2, 4, 1, 2, 1),
  credits = c(30, 150, 80, 80, 140, 220, 20, 95, 230, 300, 70, NA),
  visits  = c(4, 12, 7, 7, 9, NA, 2, 5, NA, 3, 6, 40),
  stringsAsFactors = FALSE
)
d <- raw
log <- data.frame(step = integer(), rule = character(), rows = integer())
add <- function(rule, rows) log[nrow(log) + 1, ] <<- list(nrow(log) + 1, rule, rows)
# 1. Standardise faculty labels
map <- c(ARTS = "ARTS", SCI = "SCI", SCIENCE = "SCI", BUSINESS = "BUS", BUS = "BUS", ENG = "ENG")
new <- unname(map[toupper(d$faculty)])
add("faculty labels to codebook codes", sum(new != d$faculty)); d$faculty <- new
# 2. Exact duplicates
dup <- duplicated(d)
add("drop exact duplicate rows", sum(dup)); d <- d[!dup, ]
# 3. Range check: age 16-80, else NA
bad_age <- !is.na(d$age) & (d$age < 16 | d$age > 80)
add("age outside 16-80 set to NA", sum(bad_age)); d$age[bad_age] <- NA
# 4. Consistency: year 1 cannot have more than 60 credits
bad_cr <- !is.na(d$credits) & d$year == 1 & d$credits > 60
add("year 1 with credits > 60 flagged", sum(bad_cr)); d$flag_credits <- bad_cr
print(log, row.names = FALSE)
cat("rows:", nrow(raw), "->", nrow(d), "\n")
print(colSums(is.na(d[, c("age", "credits", "visits")])))
cc <- complete.cases(d[, c("age", "credits", "visits")])
cat("complete cases:", sum(cc), "of", nrow(d), "\n")
# Outlier rule fixed in advance: visits > Q3 + 3*IQR flagged, not deleted
q <- quantile(d$visits, c(.25, .75), na.rm = TRUE)
fence <- q[2] + 3 * diff(q)
cat("visits fence:", fence, " flagged ids:", d$id[which(d$visits > fence)], "\n")
# Mean imputation shrinks the spread
v_imp <- ifelse(is.na(d$visits), mean(d$visits, na.rm = TRUE), d$visits)
cat(sprintf("SD visits observed %.2f, after mean imputation %.2f\n",
            sd(d$visits, na.rm = TRUE), sd(v_imp)))

 step                             rule rows
    1 faculty labels to codebook codes    8
    2        drop exact duplicate rows    1
    3      age outside 16-80 set to NA    1
    4 year 1 with credits > 60 flagged    1
rows: 12 -> 11 
    age credits  visits 
      2       1       2 
complete cases: 6 of 11 
visits fence: 24  flagged ids: 111 
SD visits observed 11.74, after mean imputation 10.51

import numpy as np, pandas as pd
raw = pd.DataFrame({
    "id": [101, 102, 103, 103, 104, 105, 106, 107, 108, 109, 110, 111],
    "faculty": ["Arts", "SCI", "sci", "sci", "Business", "Arts", "Science", "ENG", "Arts", "BUS", "Eng", "SCI"],
    "age": [19, 21, 20, 20, 210, 22, 18, np.nan, 23, 19, 20, 24],
    "year": [1, 3, 2, 2, 3, 4, 1, 2, 4, 1, 2, 1],
    "credits": [30, 150, 80, 80, 140, 220, 20, 95, 230, 300, 70, np.nan],
    "visits": [4, 12, 7, 7, 9, np.nan, 2, 5, np.nan, 3, 6, 40],
})
d = raw.copy()
log = []
add = lambda rule, rows: log.append({"step": len(log) + 1, "rule": rule, "rows": int(rows)})
fmap = {"ARTS": "ARTS", "SCI": "SCI", "SCIENCE": "SCI", "BUSINESS": "BUS", "BUS": "BUS", "ENG": "ENG"}
new = d["faculty"].str.upper().map(fmap)
add("faculty labels to codebook codes", (new != d["faculty"]).sum()); d["faculty"] = new
dup = d.duplicated()
add("drop exact duplicate rows", dup.sum()); d = d[~dup].copy()
bad_age = d["age"].notna() & ~d["age"].between(16, 80)
add("age outside 16-80 set to NA", bad_age.sum()); d.loc[bad_age, "age"] = np.nan
bad_cr = d["credits"].notna() & (d["year"] == 1) & (d["credits"] > 60)
add("year 1 with credits > 60 flagged", bad_cr.sum()); d["flag_credits"] = bad_cr
print(pd.DataFrame(log).to_string(index=False))
print("rows:", len(raw), "->", len(d))
print(d[["age", "credits", "visits"]].isna().sum().to_dict())
cc = d[["age", "credits", "visits"]].notna().all(axis=1)
print("complete cases:", cc.sum(), "of", len(d))
q1, q3 = d["visits"].quantile([.25, .75])
fence = q3 + 3 * (q3 - q1)
print("visits fence:", fence, " flagged ids:", d.loc[d["visits"] > fence, "id"].tolist())
v_imp = d["visits"].fillna(d["visits"].mean())
print(f"SD visits observed {d['visits'].std():.2f}, after mean imputation {v_imp.std():.2f}")

 step                             rule  rows
    1 faculty labels to codebook codes     8
    2        drop exact duplicate rows     1
    3      age outside 16-80 set to NA     1
    4 year 1 with credits > 60 flagged     1
rows: 12 -> 11
{'age': 2, 'credits': 1, 'visits': 2}
complete cases: 6 of 11
visits fence: 24.0  flagged ids: [111]
SD visits observed 11.74, after mean imputation 10.51

Reading the output, step by step.

1. Labels (8 rows). Arts (3 rows), sci (2), Business, Science and Eng were mapped to the codebook codes. Without this step a table of faculty would show eight different spellings instead of four codes.
2. Duplicates (1 row). ID 103 appears twice with identical values. Note the order: the two copies were both typed "sci", so they were duplicates before step 1 too, but if one had been "sci" and the other "SCI", only the label fix would reveal it. Rows go from 12 to 11.
3. Range (1 row). Age 210 (ID 104) is impossible. It is set to NA rather than "corrected" to 21, because the true value is unknown; guessing would be inventing data. Age now has 2 missing values (ID 104 and ID 107).
4. Consistency (1 row). ID 109 is in year 1 with 300 credits. Both values are within range on their own, but together they are impossible. The row is flagged and sent to the registry, not deleted.
5. Missing data. Age 2, credits 1, visits 2: five missing values in total, spread over five different rows, so complete-case analysis keeps only 6 of 11 rows (55%). That is a large loss from a small amount of missingness.
6. Outlier rule. The non-missing visits are 2, 3, 4, 5, 6, 7, 9, 12, 40. By hand: $Q_1 = 4$, $Q_3 = 9$, $\text{IQR} = 5$, outer fence $9 + 3 \times 5 = 24$. ID 111 (40 visits) is flagged and kept; the analysis will be reported with and without it.
7. Imputation warning. Filling the two missing visits with the mean (9.78) lowers the SD from 11.74 to 10.51, because two values are placed exactly at the centre. Any test using the imputed column would look more certain than the data justify.

R and Python agree on every count. One design difference to know: duplicated() in R and DataFrame.duplicated() in pandas both mark the second and later copies, keeping the first; pandas lets you change this with keep=. And the log counts come from the code (sum(...)), so if the raw file changes, the log updates itself.

Report sentence. "The raw export contained 12 records. One exact duplicate was removed (n = 11). One impossible age was set to missing, and one record with inconsistent year and credits was flagged for registry review. Five values were missing (age 2, credits 1, visits 2), leaving 6 complete cases; [the planned main analysis uses multiple imputation under a MAR assumption, with complete-case results reported as a sensitivity analysis]. One extreme visits value (40) exceeded the pre-specified outer fence and was retained; results without it are reported in the appendix."

Worked example B: Brightwell Bakery loaf weights (half solved)

Setting. Brightwell Bakery (fictional) records the weight in grams of 10 sampled loaves from one batch: 798, 802, 805, 810, 795, 800, 8000, 803, 799, 650. The codebook range is 300 to 1,200 g. The outlier policy, fixed in advance, is "flag values beyond the outer fences; keep unless shown to be errors". Following the order of checks, the range check runs first, so the fences are computed on the nine weights left after it: software gives $Q_1 = 798$ and $Q_3 = 803$ (R quantile() default; pandas gives the same).

Exercise C: Harbour City Library daily visits

Harbour City Library (fictional) has 60 days of door-counter visits for its Central branch. Eight days are missing: five because the counter battery failed (the failures happened on random days according to the maintenance log), and three because the branch closed early on stormy days, when staff did not record the partial count. On two other days the counter recorded 0 visits while the branch was open.

Tasks: (a) propose a mechanism for each group of missing days, with a reason; (b) how should the two zero days be treated, and in which check do they appear? (c) the analyst plans to compare average visits in wet and dry weeks using complete cases only. What is the risk? (d) write two cleaning log rows for this data set.

Cleaning checklist

• Raw file stored read-only; cleaned file written to a separate folder by script.
• Rules (ranges, consistency, outliers, missing data) written before looking at results.
• Labels standardised to codebook codes before duplicate checks.
• Exact and key duplicates checked; every removal logged with a reason.
• Impossible values set to NA (not guessed); unusual values handled by the pre-set outlier rule.
• Missing data counted per variable; complete-case count reported; mechanism argued per variable.
• Imputation method chosen to match the mechanism; simple imputation not used for inference.
• Row count at start and end reconciled with the log.
• Sensitivity analyses listed for outliers and missing data.
• Script, log and both data versions saved together (see academic/replication-package-readme).

Common mistakes and misconceptions

Self-test questions

1. Why should label standardisation come before the duplicate check? Use worked example A.
2. A health survey finds that people with low income skip the income question more often, and education (recorded) predicts both. Missing income is fully explained by education. Which mechanism is this?
3. Students who failed an exam are less likely to report their exam score, even among students with the same attendance and year. Which mechanism, and can multiple imputation from the observed variables fix it?
4. Data on four variables each have 5% missing values in different rows of 100 rows. Roughly how many complete cases might remain in the worst case, and what does that say about complete-case analysis?
5. Compute the inner fences for data with $Q_1 = 10$ and $Q_3 = 18$. Is a value of 31 beyond the inner or the outer upper fence?
6. Name two reasons why a non-significant Little's test does not justify complete-case analysis.

My notes and next steps

Sources and further reading

Based on the missing-data framework of Rubin and Little and on common reproducible-research practice, written in original words; the fence rule follows Tukey's exploratory data analysis.

• Rubin, D. B. (1976). Inference and missing data. Biometrika, 63(3), 581–592.
• Rubin, D. B. (1987). Multiple Imputation for Nonresponse in Surveys. Wiley.
• Little, R. J. A. (1988). A test of missing completely at random for multivariate data with missing values. Journal of the American Statistical Association, 83(404), 1198–1202.
• Little, R. J. A., and Rubin, D. B. (2019). Statistical Analysis with Missing Data (3rd ed.). Wiley.
• van Buuren, S., and Groothuis-Oudshoorn, K. (2011). mice: Multivariate imputation by chained equations in R. Journal of Statistical Software, 45(3), 1–67.
• van Buuren, S. (2018). Flexible Imputation of Missing Data (2nd ed.). CRC Press.
• Sterne, J. A. C., White, I. R., Carlin, J. B., Spratt, M., Royston, P., Kenward, M. G., et al. (2009). Multiple imputation for missing data in epidemiological and clinical research: potential and pitfalls. BMJ, 338, b2393.
• Tukey, J. W. (1977). Exploratory Data Analysis. Addison-Wesley.
• Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1–23.
• NIST/SEMATECH e-Handbook of Statistical Methods, chapter 1 Exploratory Data Analysis, and section 1.3.5.17 Detection of Outliers, https://www.itl.nist.gov/div898/handbook/ (accessed 2026-10-25). Public domain.
• Further reading (by name only): de Smith, M. J. (2024). Statistical Analysis Handbook (2024 edition, StatsRef); Diez, D. M., Çetinkaya-Rundel, M., and Barr, C. D., OpenIntro Statistics (CC BY-SA).

Answer key

Worked example B. 8000 g breaks the range. Action: set to NA and log it; restore 800 only if the original batch sheet confirms the value. IQR $= 5$. Lower outer fence $= 798 - 3 \times 5 = 783$ (upper outer fence $803 + 15 = 818$). If you had kept 8000 in the quartile calculation you would get $Q_1 = 798.25$, $Q_3 = 804.5$: this is why range errors are removed before fences are computed. 650 g is within range, so it is a possible value, and it is below the lower outer fence, so it is an outlier, not an error. Action: flag and keep; it is a real quality signal to investigate, not noise to remove. Log rows: Step 1, "weight outside 300 to 1,200 g set to NA", 1 row, likely decimal slip, script line [n]. Step 2, "flag weights beyond outer fences", 1 row (650 g), pre-set policy, script line [n].

Exercise C. (a) Battery failures on random days: plausibly MCAR (the maintenance log shows no link to the day or the visits). Storm closures: MAR if weather is recorded and included (missingness depends on observed rainfall), and arguably MNAR if the partial counts were unusually low for reasons not captured. (b) Zero visits on an open day is impossible for a busy branch, so it fails a consistency check (open day with zero visits) and should be set to NA as a probable counter fault, with a log row. (c) Missing days are concentrated in wet weather, so complete cases under-represent the stormiest days; the wet-week average is biased (probably upward if storm days have fewer visits). Include rainfall in an imputation model or analyse with a model that uses it. (d) For example: "Counter value 0 on open days set to NA, 2 rows, probable counter fault"; "Days with counter battery failure left NA and marked miss_reason = battery, 5 rows".

Self-test.

1. Because different spellings hide duplicates: "sci" and "SCI" would look like different rows. Standardise first, then compare.
2. MAR: missingness depends on an observed variable (education), not on income itself once education is known.
3. MNAR: missingness depends on the unobserved score itself. Imputation from observed variables does not fix it; use sensitivity analyses (for example, assume missing scores are lower by a set amount) and report the assumption.
4. Worst case: missing values never overlap, so $4 \times 5 = 20$ rows are incomplete and only 80 complete cases remain. Even small per-variable missingness can remove many rows, wasting information and possibly adding bias.
5. IQR $= 8$; inner fences $10 - 12 = -2$ and $18 + 12 = 30$; outer upper fence $18 + 24 = 42$. A value of 31 is beyond the inner upper fence but within the outer fence: a possible outlier, not a far-out value.
6. The test has low power in small samples (not rejecting is not proof), it cannot detect MNAR, and it relies on multivariate normality; also, even under MCAR, complete-case analysis loses precision.

Revision history