如何按id分组对variable为V1的value求和并写入组内所有行

我有如下data.table数据：

library(data.table)

dt <- data.table("value" = c(10,15,30,55,20,10,40,70,80,20,40,140),
                 "id" = c(1,1,1,1,2,2,2,2,3,3,3,3),
                 "variable" = c("V1","V1","V1","Total","V1","V1","V1","Total",
                                "V1","V1","V1","Total"))

输出的数据结构：

value id variable
 1:    10  1       V1
 2:    15  1       V1
 3:    30  1       V1
 4:    55  1    Total
 5:    20  2       V1
 6:    10  2       V1
 7:    40  2       V1
 8:    70  2    Total
 9:    80  3       V1
10:    20  3       V1
11:    40  3       V1
12:   140  3    Total

需求是：按id分组，仅计算variable=="V1"时value列的总和，同时将该总和显示在variable为Total的行中。

尝试过以下代码，但variable为Total的行中sum_value为NA：

dt[variable != "Total", sum_value := sum(value), by = id]

得到的结果：

value id variable sum_value
 1:    10  1       V1        55
 2:    15  1       V1        55
 3:    30  1       V1        55
 4:    55  1    Total        NA
 5:    20  2       V1        70
 6:    10  2       V1        70
 7:    40  2       V1        70
 8:    70  2    Total        NA
 9:    80  3       V1       140
10:    20  3       V1       140
11:    40  3       V1       140
12:   140  3    Total        NA

期望的结果：

value id variable sum_value
 1:    10  1       V1        55
 2:    15  1       V1        55
 3:    30  1       V1        55
 4:    55  1    Total        55
 5:    20  2       V1        70
 6:    10  2       V1        70
 7:    40  2       V1        70
 8:    70  2    Total        70
 9:    80  3       V1       140
10:    20  3       V1       140
11:    40  3       V1       140
12:   140  3    Total       140

解决方案 #

之前的代码仅给variable != "Total"的行赋值，导致Total行未被填充。可以通过以下几种方式实现需求：

方法1：直接在分组内计算并赋值所有行 #

dt[, sum_value := sum(value[variable == "V1"]), by = id]

在每个id组内，仅筛选variable=="V1"的value求和，再将该值赋值给组内所有行（包括Total行）。

方法2：先计算统计量再合并回原表 #

如果需要更清晰的步骤，可以先单独计算每个id的V1总和，再关联到原表：

sum_dt <- dt[variable == "V1", .(sum_value = sum(value)), by = id]
dt <- dt[sum_dt, on = "id"]

方法3：基于原代码修正填充NA #

如果想保留原代码的基础逻辑，可以取组内非NA值填充所有行：

dt[variable != "Total", sum_value := sum(value), by = id]
dt[, sum_value := sum_value[1], by = id]  # 取组内第一个非NA值填充

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