如何用data.table语法按行对两列求和？

我有一个data.table，想要将data1和data2列按行求和，结果存入新列sum。以下是我的几次尝试，但结果都不正确或报错。我知道Stack Exchange上有类似问题，但没找到适配的解决方案，可能漏了简单的点。另外，后续我还需要添加列来计算这些列的标准差（sd）、均值（mean）、中位数（median）和标准误（sem）。求帮忙指出问题所在！

数据示例 #

> sum_tabnew
      location       bin       time data1 data2 loc_id condition loc_coord
   1:    Loc01 (-0.24,1]   0.966764   258     0      1    WT_CTL        a1
   2:    Loc01     (1,2]   2.000012   399     0      1    WT_CTL        a1
   3:    Loc01     (2,3]   2.999657   502     0      1    WT_CTL        a1
   4:    Loc01     (3,4]   3.999978   284     0      1    WT_CTL        a1
   5:    Loc01     (4,5]   4.999684   335     0      1    WT_CTL        a1
  ---                                                                     
8540:    Loc96 (114,115] 115.000607     0    90     96   MUT_CTL       h12
8541:    Loc96 (115,116] 115.984122     0   708     96   MUT_CTL       h12
8542:    Loc96 (116,117] 116.967636     0   383     96   MUT_CTL       h12
8543:    Loc96 (117,118] 117.967847     0     0     96   MUT_CTL       h12
8544:    Loc96 (118,119] 119.000967     0     0     96   MUT_CTL       h12

预处理代码 #

# 获取所有含'data'的列名向量
data_vec <- colnames(sum_tabnew)[grepl("data",colnames(sum_tabnew))]

# 将所有'data'列转为double类型，避免后续类型问题
sum_tabnew[ , (data_vec) := lapply(.SD, as.double), .SDcols = data_vec]

尝试的代码及问题 #

尝试1：错误引用未定义对象 #

test_sum <- sum_tabnew[, list(sum = sum(test_tab)), by = list(condition, time)]

结果：

> test_sum
      condition       time     sum
   1:    WT_CTL   0.966764 2294112
   2:    WT_CTL   2.000012 2294112
   3:    WT_CTL   2.999657 2294112
   4:    WT_CTL   3.999978 2294112
   5:    WT_CTL   4.999684 2294112
  ---                              
1066:   MUT_CTL 115.000607 2294112
1067:   MUT_CTL 115.984122 2294112
1068:   MUT_CTL 116.967636 2294112
1069:   MUT_CTL 117.967847 2294112
1070:   MUT_CTL 119.000967 2294112

问题：代码中错误使用了sum(test_tab)，但test_tab并非当前环境中的对象，实际计算的是整个表的总和，导致分组后每个组结果完全相同。

尝试2：分组整体求和而非按行求和 #

test_sum <- sum_tabnew[, sum(.SD), .SDcols = data_vec, by = list(condition, time)]

结果：

> test_sum
      condition       time   V1
   1:    WT_CTL   0.966764 3492
   2:    WT_CTL   2.000012  399
   3:    WT_CTL   2.999657 2194
   4:    WT_CTL   3.999978  284
   5:    WT_CTL   4.999684 2520
  ---                           
1066:   MUT_CTL 115.000607 3181
1067:   MUT_CTL 115.984122 4524
1068:   MUT_CTL 116.967636 6925
1069:   MUT_CTL 117.967847 2060
1070:   MUT_CTL 119.000967 2159

问题：sum(.SD)是对每个分组内的data1和data2列整体求和，而非按行将两列数值相加。

尝试3：未指定目标列导致处理非数值列 #

test_sum <- sum_tabnew[, lapply(.SD, function(x) sum(x)), by = list(condition, time)]

报错：

Error in sum(x) : invalid 'type' (character) of argument

问题：未指定.SDcols = data_vec，.SD默认包含所有列，其中location等字符类型列无法参与求和运算，因此触发错误。

更新：可复现数据 #

> dput(head(sum_tabnew,10))
structure(list(location = c("Loc01", "Loc01", "Loc01", "Loc01", 
"Loc01", "Loc01", "Loc01", "Loc01", "Loc01", "Loc01"), bin = structure(1:10, levels = c("(-0.24,1]", 
"(1,2]", "(2,3]", "(3,4]", "(4,5]", "(5,6]", "(6,7]", "(7,8]", 
"(8,9]", "(9,10]"), class = "factor"), time = c(0.966764, 
2.000012, 2.999657, 3.999978, 4.999684, 5.999687, 6.999671, 8, 
9.00001, 9.999827), data1 = c(258, 399, 502, 284, 335, 309, 0, 
82, 1916, 2), data2 = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0), loc_id = c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), condition = structure(c(12L, 
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L), levels = c("MUT1", 
"MUT2", "MUT3", "MUT4", "MUT5", "MUT6", "MUT7", "MUT8", "MUT9", 
"MUT10", "MUT_CTL", "WT_CTL"), class = "factor"), loc_coord = c("a1", 
"a1", "a1", "a1", "a1", "a1", "a1", "a1", "a1", "a1")), row.names = c(NA, 
-10L), class = c("data.table", "data.frame")

解决方案 #

1. 按行求和（生成sum列） #

要实现按行将data1和data2相加，不需要分组，直接使用以下两种方法：

# 方法1：用rowSums批量处理多列
sum_tabnew[, sum := rowSums(.SD), .SDcols = data_vec]

# 方法2：直接逐列相加（适合列数较少的场景）
sum_tabnew[, sum := data1 + data2]

2. 按分组计算统计量 #

如果需要按condition和time分组，计算每组内data1和data2的统计量，先定义标准误函数，再批量计算：

# 定义标准误计算函数
sem <- function(x) sd(x)/sqrt(length(x))

# 分组计算多个统计量（将data1和data2视为一组数据）
stats_tab <- sum_tabnew[, .(
  total_sum = sum(.SD),
  mean_val = mean(unlist(.SD)),
  median_val = median(unlist(.SD)),
  sd_val = sd(unlist(.SD)),
  sem_val = sem(unlist(.SD))
), by = .(condition, time), .SDcols = data_vec]

# 若需对每列单独计算统计量
stats_tab_per_col <- sum_tabnew[, lapply(.SD, function(x) .(
  mean = mean(x),
  median = median(x),
  sd = sd(x),
  sem = sem(x)
)), by = .(condition, time), .SDcols = data_vec]

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