Unit 05.01: Duplicate on the key, not on every column

A duplicate is not a repeated row. It is a repeated *key* — and those are the ones that inflate your totals.

Duplicate on the key, not on every column

duplicated() finds rows identical in every column. Real data rarely obliges: the second submission carries a different timestamp, a different user, a corrected figure. Every column differs somewhere, so the row is not a duplicate by that test, and it is still the same ward and month counted twice.

The right test names the key — what combination of columns should identify one row. count() on the key, filtered to counts above one, gives you the offenders.

Which copy to keep is then a judgement: the latest submission, the first, the one with fewer blanks. Any of these can be right. Writing down which you chose is what makes the analysis defensible.

This block tests both ways and then deduplicates by a stated rule.

suppressPackageStartupMessages(library(dplyr))

submissions <- data.frame(
  ward      = c("North", "South", "North", "East", "North"),
  month     = c("Jan", "Jan", "Jan", "Jan", "Feb"),
  visits    = c(412, 388, 412, 502, 455),
  submitted = c("09:14", "09:20", "11:02", "09:31", "09:12")
)

# "Duplicate" means duplicate on the KEY, not identical across every column.
cat("Fully identical rows:", sum(duplicated(submissions)), "\n")

key_dupes <- submissions |> count(ward, month) |> filter(n > 1)
cat("Duplicated ward-month keys:", nrow(key_dupes), "\n")
print(key_dupes)

cat("\nThe two North/Jan rows differ only in submission time:\n")
print(submissions |> filter(ward == "North", month == "Jan"))

# Keeping the latest submission is a decision, and it should be written down.
deduped <- submissions |>
  arrange(ward, month, desc(submitted)) |>
  distinct(ward, month, .keep_all = TRUE)
cat("\nRows:", nrow(submissions), "->", nrow(deduped), "(kept the later submission)\n")

Fully identical rows: 0. Duplicated ward-month keys: 1. The two North/January rows carry the same 412 visits and differ only in submission time, 09:14 and 11:02 — invisible to duplicated() and fatal to a sum. Sorting by submission time and keeping one row per key takes the table from 5 rows to 4, under the stated rule that the later submission wins.

The mistake this prevents

The mistake is running distinct() on the whole table and considering the job done. It removes only the perfectly identical rows, which are usually the rare kind, and leaves the key duplicates that actually distort totals.

Takeaway

Define the key before you look for duplicates, count on that key, and write down which copy you keep and why. Check row counts before and after.

Mark complete