使用data.table实现多条件累计求和（calc1与calc2）

样本数据集 #

library(data.table)
sampleDT <- data.table(
  sortcol1 = c("Ai","Ai","Ai","Ai","Ai","Ai","Ai","Ai","Ai","Ai","Ai","Ai","Ai","Bs","Bs","Bs","Bs","Bs","Bs","Bs","Bs","Bs","Bs","Bs","Bs","Bs","Bs","Ez","Ez","Ez","Ez","Ez","Ez","Ez","Jd","Jd","Jd","Jd","Jd","Jd","Jd","Jd","Jd","Nl","Nl","Nl","Nl","Nl","Nl","Nl","Nl","Nl","Nl","Ok","Ok","Ok","Ok","Ok","Ok","Ok","Ok","Ok","Ok","Ok","Ok","Oz","Oz","Oz","Oz","Oz","Oz","Oz","Oz","Qm","Qm","Qm","Qm","Qm","Qm","Qm","Qm","Rn","Rn","Rn","Rn","Rn","Rn","Rn","Rn","Rn","Yv","Yv","Yv","Yv","Yv","Yv","Yv","Yv","Yv","Yv"),
  sortcol2 = c("26g","A8q","A8q","A8q","A8q","A8q","F20z","K12s","K12s","K12s","M12g","M15h","T9c"," 26g"," 26g","A8q","A8q","A8q","F20z","K12s","K25h","M12g","M12g","M15h","M15h","N22p","N22p","A8q","A8q","F20z","M12g","M12g","N22p","N22p","A8q","K12s","K12s","M12g","N15z","N15z","N15z","N15z","N22p"," 26g","A8q","F20z","F20z","K12s","K12s","K25h","N15z","T9c","T9c"," 26g","A8q","F20z","M12g","M12g","M15h","M15h","M15h","M15h","N15z","N15z","N22p"," 26g","A8q","A8q","K25h","K25h","K25h","M15h","T9c","K12s","K25h","K25h","M12g","N15z","N22p","N22p","N22p","A8q","F20z","K12s","K12s","K12s","M15h","N15z","T9c","T9c"," 26g","A8q","K12s","M12g","M12g","M12g","M15h","N15z","N22p","T9c"),
  sortcol3 = c(52024,5149,17725,52024,71627,71627,17725,5149,5149,121714,121714,5149,52024,17725,71627,5149,52024,71627,241210,16325,1966,1966,121714,1966,171721,121714,241210,5149,221324,171721,17725,71627,71627,241210,241210,5149,17725,16325,1966,5149,16325,121714,16325,1966,241210,17725,121714,16325,221324,52024,17725,171721,241210,1966,5149,16325,17725,121714,1966,121714,171721,221324,1966,71627,1966,221324,52024,71627,16325,221324,221324,241210,5149,17725,17725,221324,16325,17725,1966,71627,121714,52024,52024,5149,71627,221324,5149,241210,5149,71627,16325,1966,16325,5149,17725,52024,71627,5149,121714,121714)
)

计算calc1列 #

规则说明 #

• 按sortcol1分组，组内基于sortcol3排序后对sortcol3做累计求和
• 每组第一个值为0，从第二个值开始累计
• 优先使用现有排序，若数据未排序可先执行排序操作

代码实现 #

# 若数据未按sortcol1+sortcol3排序，先执行排序（可选，根据实际情况）
# sampleDT <- sampleDT[order(sortcol1, sortcol3)]

# 计算calc1
sampleDT[, calc1 := c(0, cumsum(sortcol3)[-.N]), by = sortcol1]

逻辑解释 #

• by = sortcol1指定以sortcol1为分组依据
• cumsum(sortcol3)[-.N]计算组内除最后一个值外的累计和，再在开头拼接0，确保每组首个值为0，后续值为前面所有sortcol3的累计结果

计算calc2列 #

规则说明 #

• 按sortcol1与sortcol2的组合分组，组内基于sortcol3排序后对sortcol3做累计求和
• 每组第一个值为0，从第二个值开始累计
• 优先使用现有排序，若数据未排序可先执行排序操作

代码实现 #

# 若数据未按sortcol1+sortcol2+sortcol3排序，先执行排序（可选，根据实际情况）
# sampleDT <- sampleDT[order(sortcol1, sortcol2, sortcol3)]

# 计算calc2
sampleDT[, calc2 := c(0, cumsum(sortcol3)[-.N]), by = .(sortcol1, sortcol2)]

逻辑解释 #

• by = .(sortcol1, sortcol2)指定以两列的组合为分组依据
• 同样通过c(0, cumsum(sortcol3)[-.N])构造每组的累计序列，保证首个值为0，后续值为前面所有sortcol3的累计结果

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