R Programming / Analysis interfaces and table integrity / Lesson 8

Grouping, reshaping, and joins

Preserve observation grain while summarizing groups or combining tables by identity.

About 5 min readContent reviewed 2026-10-28

On this page

Lesson goals

Define group keys and aggregation denominators.
Predict join multiplicity before merging.
Validate long-to-wide uniqueness and row ordering.

State what one row represents before transforming it

A table can represent an item, an event, an item-time measurement, or a group summary. Grouping changes that grain by combining rows that share keys. Joining combines information through a relationship between keys, which can repeat rows. Reshaping changes how repeated measurements are arranged across rows and columns. These operations are not interchangeable ways to make a table look tidier. Write the input and output keys, intended multiplicity, and meaning of each value before choosing an R function. Preserve original identifiers when a later diagnostic must trace a summary or prediction back to source observations.

Aggregate values under an explicit missingness rule

The first fixture uses aggregate with an explicit list of group labels and one named numeric result. The groups are ordered before output so the expected display does not depend on an unstated ordering convention. The group totals sum to the original total because every supplied value is finite and belongs to one group. A mean requires its denominator, especially when missing values or unequal group sizes matter. A mean of group means generally differs from the overall observation mean. Missing grouping keys also need a policy; inspect the documented method behavior rather than assuming a missing key becomes an ordinary displayed group.

r Illustrative base-R script; R runtime not available during review

d <- data.frame(group = c("a", "b", "a"), minutes = c(2, 10, 4), stringsAsFactors = FALSE)
s <- aggregate(list(total = d$minutes), by = list(group = d$group), FUN = sum)
s <- s[order(s$group), , drop = FALSE]
cat("groups=", paste(s$group, collapse = ","), "\n", sep = "")
cat("totals=", paste(s$total, collapse = ","), "\n", sep = "")
cat("preserved total=", sum(s$total) == sum(d$minutes), "\n", sep = "")

Expected output

groups=a,b
totals=6,10
preserved total=TRUE

Explicit grouping produces one summary per group; conservation of the total is a useful invariant for this complete finite fixture.

A join matches relationships rather than row positions

merge should name its key columns explicitly, because the default can use all shared column names and thereby change after a schema update. The second fixture shows one-to-many matches: key a appears twice on the right and therefore produces two joined rows. A left-preserving join keeps unmatched left keys but does not promise one output row per left row. Many-to-many relationships can multiply rows further. Predict the expected cardinality, check duplicate keys on each side, and report unmatched keys before trusting a joined metric. A sum after joining may be inflated simply because measurements were repeated.

r Illustrative base-R script; R runtime not available during review

left <- data.frame(id = c("a", "b", "c"), value = c(1, 2, 3), stringsAsFactors = FALSE)
right <- data.frame(id = c("a", "a", "b"), tag = c("x", "y", "z"), stringsAsFactors = FALSE)
joined <- merge(left, right, by = "id", all.x = TRUE, sort = TRUE)
cat("left rows=", nrow(left), "; joined rows=", nrow(joined), "\n", sep = "")
cat("a rows=", sum(joined$id == "a"), "; unmatched=", sum(is.na(joined$tag)), "\n", sep = "")

Expected output

left rows=3; joined rows=4
a rows=2; unmatched=1

Duplicate right-side keys create multiple matches; preserving every left key does not imply preserving the left row count.

Reshaping requires a uniqueness decision

A long table might have columns id, occasion, and value. Creating one column per occasion assumes one value for every id-occasion pair. If that pair is duplicated, decide whether the records are errors, legitimate replicates, or values requiring an explicit aggregation. Choosing the first row silently is a data policy, not a neutral reshape. Conversely, widening can create missing combinations that were never observed; those are not automatically zeros. Keep units consistent across value columns and preserve a way to distinguish structural absence from a measured missing value when the task requires it.

Ordering and invariants make transformations reviewable

Base merge can sort keys, and an unsorted merge does not establish a stable original-row ordering contract. Retain a row identifier and restore an intended order explicitly if downstream alignment needs it. Useful checks include key uniqueness, coverage, row counts, total conservation where justified, and independently known id-value pairs. None is sufficient alone: an unchanged total can hide swapped labels, while matching keys can hide a wrong unit conversion. The examples stay entirely in memory with synthetic tables and derive expected results; no database, external data source, R parser, or R runtime was used.

Common errors and fixes

A join unexpectedly doubles a total

Why it happens: Repeated key matches duplicated measurements.
How to fix it: Audit key multiplicity and aggregate or repair under the intended grain.
Prevent it next time: Predict cardinality and inspect per-key counts before merging.

A new shared column changes the join result

Why it happens: The implicit default key expanded.
How to fix it: Name by, or by.x and by.y, explicitly.
Prevent it next time: Treat join keys as part of the schema contract.

Widening silently chooses one duplicate measurement

Why it happens: The id-occasion pair was not unique.
How to fix it: Define replicate handling before reshaping.
Prevent it next time: Assert the intended compound-key uniqueness.

Check your understanding

1. Two rows for key a on the left meet three rows for a on the right. How many a rows can a standard equality merge produce?

Show worked solution

All matching pairs contribute, so two times three gives six rows for a. If the desired output is one row per left observation, repair or aggregate the right relationship under a justified rule first. Merely using a left join does not prevent multiplication.

2. Group a has one value 2 and group b has values 8, 8, 8. Compare the overall mean and mean of group means.