Diagnose join multiplication and lost observations

Understand the likely causes

• Likely causes include duplicate matches, implicit join keys, accidental inner-join exclusions, normalized-key collisions, missing-key matching rules, or downstream reliance on a changed row order. An apparently correct row count can also hide replacement or reordering of observations.

1. Write the relationship the join is supposed to represent

State what each left row means and whether the right side should contain one row per key, several legitimate labels, or repeated measurements. A one-to-many relationship is not automatically bad data; it may simply be inappropriate for a downstream one-row-per-item summary. Identify the compound key if time or replicate distinguishes records. Check both sides for duplicate and missing keys, preserving the raw and normalized forms when a cleaning step created them. Do not apply unique to the whole result as a generic repair, because it can discard legitimate observations while leaving the wrong relationship intact.

2. Predict matches per key and reconcile totals

The fixture has one item a with two labels, so its duration appears twice after merging. The total rises from thirty to forty without any arithmetic failure. For each key, standard equality matches contribute the product of its left and right counts. Compare that prediction with the result and identify unmatched keys separately. If a summary must remain one row per item, define a right-side aggregation, a selected label policy, or a separate relational representation before joining. Merely changing to a left-preserving join does not enforce uniqueness.

items <- data.frame(id = c("a", "b"), minutes = c(10, 20), stringsAsFactors = FALSE)
labels <- data.frame(id = c("a", "a", "b"), label = c("x", "y", "z"), stringsAsFactors = FALSE)
joined <- merge(items, labels, by = "id", all.x = TRUE)
cat("before rows=", nrow(items), "; after rows=", nrow(joined), "\n", sep = "")
cat("before total=", sum(items$minutes), "; after total=", sum(joined$minutes), "\n", sep = "")
cat("duplicate label keys=", anyDuplicated(labels$id) > 0L, "\n", sep = "")

before rows=2; after rows=3
before total=30; after total=40
duplicate label keys=TRUE

3. Audit key coverage and restore intended alignment

Name by, by.x, or by.y explicitly instead of letting all common column names define the relationship. Record whether unmatched rows should remain, and inspect them before deciding on exclusion. Key normalization can collapse distinct raw identities; test collisions after trimming or case handling. Base merge can change row ordering, so preserve a stable left-row id when later arrays must align with the original table. Missing-key behavior can differ from expectations learned from another tool, so choose and test a deliberate missing-key policy.

4. Verify a corrected relational contract

After repair, assert the intended key uniqueness or multiplicity, reconcile input and output counts, and inspect known id-to-value associations. Check total conservation only when the relationship should preserve that total; a legitimate one-to-many output has another grain. Include unmatched left and right keys, duplicate keys, missing keys, and a singleton case in the fixture set. If widening follows the join, verify id-occasion uniqueness before creating columns. Keep a short written explanation of the relationship so a future schema change cannot silently redefine the join.

Keep it from recurring

• Treat keys and multiplicity as schema requirements. Validate normalized-key collisions and right-side uniqueness when appropriate, keep explicit row identity through ordering, and review any change in join keys or output grain before accepting a metric difference.