Groups, counts and joined tables

Totals per group, in one call

aggregate(total ~ customer, data = orders, FUN = sum) reads as "the column total, grouped by the column customer, summed". The piece before the tilde is what you want back, the piece after it is how the rows are grouped, and FUN is the function applied to each group.

The answer is itself a data frame, with one row per group, sorted by the grouping column. Because it is a frame, everything from the previous chapters still works on it: you can sort it, filter it, or add a percentage column. The left side can be an expression, as in aggregate(units * each ~ customer, ...), but the answer column then gets a generated name, so naming the column first is usually clearer.

Counting how often things appear

table(orders$product) counts how many times each value occurs, and the names on the result are the values it found. names(counts) lists those values and counts[["Mug"]] picks one count out by name, which is all you need for most reporting.

factor is the tool when the counts should include a value that never appeared. A factor carries a list of levels, and a level with no rows shows up as a count of zero rather than vanishing from the table. table also drops NA unless you pass useNA = "ifany", so a column with missing values reports fewer rows than the frame actually holds.

Two tables, one shared column

merge(orders, prices, by = "product") joins two frames on a column they have in common. Rows whose key matches on both sides are combined; a row whose key is missing from the other table is dropped, which is the default and the common case.

The result comes back sorted by the key column, not in the order you started with. That is worth remembering before you assign anything back to the original frame, and it is why the example checks the join against a number it already trusts. by can name several columns with c("product", "region"), and a match then needs every one of them to agree; a near-match on one column is no match at all.

Group, count, join

aggregate(total ~ customer, data = orders, FUN = sum)

counts <- table(orders$product)
counts[["Mug"]]
names(counts)

levels(factor(c("Mug", "Lamp")))

prices <- data.frame(
  product = c("Mug", "Lamp"),
  price = c(12, 24)
)
joined <- merge(orders, prices, by = "product")
nrow(joined)
head(joined, 2)

Split, apply, combine

aggregate is three steps in one call: split the rows by the grouping column, apply FUN to each piece, and combine the answers into a frame. Once you see those steps, the formula reads naturally, and the result is a frame you can sort and filter like any other. The grouping column comes back named, with one row per distinct value.

More than one grouping column is a + on the right: aggregate(total ~ customer + region, ...) gives one row per combination that actually appears. FUN can be any function that takes a vector and returns one value, including length for a count and mean for an average, but the answer column keeps the name of the left-hand side, so rename it afterwards if the name no longer fits.

Joins: matched, kept and multiplied

merge finds rows whose key matches on both sides and glues them together. By default it keeps only the matches — an inner join. all.x = TRUE keeps every row of the first table and fills the new columns with NA where the second has nothing; all = TRUE keeps both sides. The choice is a statement about which rows you refuse to lose.

Two checks make a join trustworthy. Count the rows before and after: an inner join should not gain rows unless a key is duplicated, and a duplicated key multiplies matches. Then compare a total you already know, such as revenue computed before the join, with the same total after it. Both checks are cheap, and both catch the silent doubling that makes a report wrong.

Try these next

• Group orders by customer and region at once, with total ~ customer + region.
• Count orders per customer with aggregate(id ~ customer, data = orders, FUN = length).
• Run a left join, count the rows, and list the products whose price is NA.