Unit 05.07: Every removal, with its reason and its count

The cleaned table cannot tell anyone what was removed. That is what the log is for.

Every removal, with its reason and its count

Cleaning changes the answer. Rows disappear, values are corrected, categories are merged — and the resulting table looks exactly like a table that never needed any of it. A reader has no way to tell how much of the original survived.

A cleaning log fixes that. One row per step: what it did, how many rows went in, how many came out. Built as you go, it costs a line per step and answers the first question any careful reviewer asks.

It is also a check on yourself. A step that removes far more rows than you expected is a bug you would otherwise discover in the report.

This block records each cleaning step as it happens.

suppressPackageStartupMessages(library(dplyr))

raw <- data.frame(
  ward   = c("North", "north ", "South", "East", "East", "West"),
  visits = c(412, 88, -3, 502, 502, NA)
)

log <- list()
note <- function(step, before, after) {
  log[[length(log) + 1]] <<- data.frame(step = step, rows_before = before,
                                        rows_after = after, removed = before - after)
}

n0 <- nrow(raw)
step1 <- raw |> mutate(ward = trimws(tools::toTitleCase(tolower(ward))))
note("normalise ward labels", n0, nrow(step1))

step2 <- step1 |> filter(is.na(visits) | visits >= 0)
note("drop impossible negative counts", nrow(step1), nrow(step2))

step3 <- step2 |> distinct(ward, visits, .keep_all = TRUE)
note("drop exact duplicates", nrow(step2), nrow(step3))

cleaning_log <- do.call(rbind, log)
print(cleaning_log)

cat("\nStarted with", n0, "rows, finished with", nrow(step3), "\n")
cat("Total removed:", sum(cleaning_log$removed), "\n")
cat("Every removal has a named reason. That table goes in the report.\n")

The log shows three steps. Normalising ward labels removed 0 rows, which is correct — it changes values, not row counts, and the log records that it ran. Dropping impossible negative counts removed 1. Dropping exact duplicates removed 1. Six rows in, 4 out, 2 removed in total, and every one of them attributable to a named rule.

The mistake this prevents

The mistake is cleaning interactively and reconstructing the log afterwards from memory. The steps you remember are the ones that were interesting, not the ones that removed the most rows.

Takeaway

Build the log as you clean, one entry per step, with counts either side. Check that the removals sum to the total change, and put the log in the report rather than in a comment.

Mark complete