skip to content

In R, revenue totals by region differ between aggregate(revenue ~ region, ...) and dplyr's group_by() |> summarise(); how do you diagnose which is right?

level: seniorimportance: should knowfreq 36%

answer

  1. same data, different NA rules
  2. formula method drops incomplete rows
  3. NA propagates in sum()
  4. NA can be a group
  5. count what was dropped

basics

~20 s

In R, aggregate()'s formula method drops rows with NA by default, while dplyr's sum() gives NA for any group with a missing value and keeps an NA region as a group. Count the NAs, then choose explicitly.

solid answer

~40 s

The two tools apply **different missing-value rules**. `aggregate(revenue ~ region, data = sales, FUN = sum)` uses `na.action = na.omit`, so rows where `revenue` **or** `region` is `NA` are dropped before grouping. Totals look clean, and a region with only missing revenue vanishes. In dplyr, `group_by(region) |> summarise(total = sum(revenue))` keeps every row: a region with any `NA` revenue gets `total = NA`, and rows with a missing `region` form their own `NA` group. To diagnose, count the missing values per group with `sum(is.na(revenue))`, check for an `NA` group, and decide what the report should say. Then write it explicitly: `sum(revenue, na.rm = TRUE)` **plus** a missing-count column, and `filter(!is.na(region))` if unassigned rows really should be excluded. Base R's `tapply()` behaves differently again: it propagates `NA` within groups but drops the `NA` group.

code

r · 28 lines
r
library(dplyr)

sales <- data.frame(
  region  = c("North", "North", "South", "South", "West", NA),
  revenue = c(100, NA, 80, 70, 50, 30)
)

aggregate(revenue ~ region, data = sales, FUN = sum)
#>   region revenue
#> 1  North     100
#> 2  South     150
#> 3   West      50

tapply(sales$revenue, sales$region, sum)
#> North South  West 
#>    NA   150    50 

sales |>
  group_by(region) |>
  summarise(n_missing = sum(is.na(revenue)),
            total = sum(revenue, na.rm = TRUE))
#> # A tibble: 4 × 3
#>   region n_missing total
#>   <chr>      <int> <dbl>
#> 1 North          1   100
#> 2 South          0   150
#> 3 West           0    50
#> 4 <NA>           0    30

go deeper

for a junior

Recall that sum() returns NA if any value is missing unless na.rm = TRUE, and that dplyr keeps NA as its own group.

for a middle

Explain the three rules: aggregate's na.omit, tapply dropping the NA group, and dplyr propagating NA, then show a summary that counts missing values per group.

for a senior

Diagnose a totals mismatch by measuring missingness per group, agree what the total should mean, and encode that rule explicitly with a missing-count column.

for a principal

Set reporting standards for grouped metrics, such as always publishing missing counts and naming the NA policy, so different tools cannot give silently different numbers.

## The symptom Two analysts summarise the same data frame and get different numbers: ```r sales <- data.frame( region = c("North", "North", "South", "South", "West", NA), revenue = c(100, NA, 80, 70, 50, 30) ) ``` | Method | North | South | West | NA region | |---|---|---|---|---| | `aggregate(revenue ~ region, data = sales, FUN = sum)` | 100 | 150 | 50 | *(absent)* | | `tapply(sales$revenue, sales$region, sum)` | NA | 150 | 50 | *(absent)* | | `sales \|> group_by(region) \|> summarise(total = sum(revenue))` | NA | 150 | 50 | 30 | Nothing is broken. Each tool is doing exactly what it is documented to do, with a different rule for missing values. ## Why each tool gives its answer 1. **`aggregate()`, formula method.** Its `na.action` argument defaults to `na.omit`. Any row with an `NA` in **any variable named in the formula** is removed before grouping. North loses its `NA` row, so its total is 100. The row with no region is dropped entirely. A region whose revenue is all `NA` disappears from the output rather than showing `NA`. 2. **`tapply()`.** Groups are built by converting `region` to a factor, which excludes `NA`, so the unassigned row is dropped. Inside each group `sum()` runs without `na.rm`, so North is `NA`. 3. **dplyr.** `group_by()` treats `NA` as a real group value and sorts it last. `sum()` without `na.rm` propagates the missing value, so North is `NA`, and the 30 in the `NA` group is kept. ## Diagnosing: measure the missingness first Before choosing a rule, find out what is missing and where: ```r sales |> group_by(region) |> summarise( n = n(), n_missing = sum(is.na(revenue)), total = sum(revenue, na.rm = TRUE) ) ``` Questions this answers: - **Which groups contain missing revenue, and how many rows?** One `NA` among thousands is a footnote. Half a group missing makes its total meaningless. - **Is there an `NA` group?** Rows with no region are revenue that belongs somewhere. Dropping them silently understates the grand total. - **Does any group vanish under one method?** Compare the set of groups, not just the numbers. One dplyr detail matters here. Inside `summarise()`, later expressions see columns created earlier in the same call. If you write `revenue = sum(revenue, na.rm = TRUE)` and then `n_missing = sum(is.na(revenue))`, the second expression reads the new one-row summary, not the original column. Use a new name such as `total`, or compute the count first. ## Making the rule explicit Pick the behaviour the report needs, then write it so a reader can see it: - **Total of known revenue, with transparency:** `sum(revenue, na.rm = TRUE)` alongside `n_missing`. - **Exclude unassigned rows on purpose:** `filter(!is.na(region))` before grouping, with a note of how much revenue that removes. - **Keep unassigned rows visible:** leave the `NA` group, or recode it with `tidyr::replace_na()` or `ifelse(is.na(region), "Unassigned", region)`. - **In base R:** `aggregate(revenue ~ region, data = sales, FUN = sum, na.action = na.pass)` keeps rows with missing revenue, though rows with a missing region still fall out of the groups. Add `na.rm = TRUE`, which is passed through to `sum()`, if you want known-value totals. ## The pipe in this code The examples use R's **native pipe** `|>`, available since **R 4.1.0**. `x |> f(y)` is rewritten to `f(x, y)`. The right-hand side must be a function call, so write `x |> f()`, not `x |> f`. The placeholder `_` arrived in R 4.2.0 and must be passed to a named argument: `sales |> lm(revenue ~ region, data = _)`. The `%>%` pipe re-exported by dplyr also works, uses `.` as its placeholder, and accepts a bare function name. In dplyr pipelines the two are usually interchangeable. ## What a senior answer sounds like "The numbers differ because the two methods handle missing values differently, not because one is buggy. I'd count missing values per group, check for an `NA` group, agree with the report owner what the total should mean, and then write that rule explicitly with a missing-count column so it can't silently change."

  • With dplyr, why can summarise(revenue = sum(revenue, na.rm = TRUE), n_missing = sum(is.na(revenue))) report zero missing values?
    Expressions in `summarise()` are evaluated in order, and later ones see columns created earlier in the same call. After the first expression, `revenue` refers to the one-row group total, not the original column, so `is.na()` checks the total. Put the count first or give the total a new name such as `total`.
  • In R, how does the native pipe |> differ from the %>% pipe that dplyr re-exports?
    `|>` is built into R since 4.1.0 and needs a function call on the right, `x |> f()`. Its placeholder `_` (R 4.2.0 and later) must go to a named argument. `%>%` accepts a bare function name, uses `.` as its placeholder, and works on older R. For ordinary dplyr pipelines they give the same result.

saying these in an interview costs you the question

  • aggregate() and dplyr always give identical grouped totals in R.
  • dplyr's group_by() silently drops rows where the group column is NA.
  • Adding na.rm = TRUE everywhere fixes the discrepancy with no further checks.
  • aggregate()'s formula method keeps NA rows unless told otherwise.
  • The native pipe |> works in every R version.