Data.table多聚合操作：如何分组对不同列执行sum和mean（使用.SD）

Great question! When you need to apply different aggregation functions to different columns in data.table while still leveraging .SD, there are a couple of straightforward approaches to get this done. Let me walk you through them with concrete examples.

First, let's set up a sample data.table to work with:

library(data.table)
dt <- data.table(
  category = rep(c("X", "Y"), each = 3),
  a = c(1, NA, 3, 4, 5, NA),
  b = c(2, 4, 6, 8, NA, 12)
)

Method 1: Directly extract columns from .SD and apply functions #

This is the most intuitive approach for a small number of columns with different aggregations:

dt[, .(
  sum_a = sum(.SD[["a"]], na.rm = TRUE),
  mean_b = mean(.SD[["b"]], na.rm = TRUE)
), by = category]

What's happening here? #

• .SD represents the subset of data for each group defined by by = category
• We use .[["a"]] and .[["b"]] to pull the specific columns from .SD
• We apply sum() to column a and mean() to column b, adding na.rm = TRUE to handle missing values
• The .() syntax lets us name our output columns clearly (sum_a and mean_b)

Method 2: Combine .SDcols and lapply for scalable scenarios #

If you have multiple columns that need the same aggregation (e.g., sum multiple columns, mean multiple others), you can extend this approach using .SDcols to target specific columns:

# Sum column 'a', mean column 'b'
dt[, .(
  sum_a = lapply(.SD[, .(a)], sum, na.rm = TRUE)[[1]],
  mean_b = lapply(.SD[, .(b)], mean, na.rm = TRUE)[[1]]
), by = category]

Why the [[1]]? #

lapply() returns a list, so adding [[1]] converts the list result to a scalar value, making the output table clean and easy to read.

If you had more columns to sum (e.g., a and c) and more to mean (e.g., b and d), you could adjust this to:

dt[, c(
  lapply(.SD[, .(a, c)], sum, na.rm = TRUE),
  lapply(.SD[, .(b, d)], mean, na.rm = TRUE)
), by = category]

Both methods leverage .SD to work with grouped subsets, giving you the flexibility to mix and match aggregations per column.

内容的提问来源于stack exchange，提问作者wizard_draziw