r - data.table优化-条件求和

Question

我正在尝试对 data.table 进行条件求和，但目前设法以一种困惑的方式完成它。我想知道是否可以做得更优雅？

考虑以下因素:

library(data.table)
stock_profile <- data.table(Pcode = c(123456L, 234567L, 345678L, 456789L, 567891L, 678912L, 789123L, 891234L, 912345L, 123456L, 234567L, 345678L, 456789L, 567891L, 678912L, 789123L, 891234L, 912345L), 
    Value = c(51.96, 89.64, 21.56, 56.04, 47.56,83.68, 42.21, 66.56, 62.72, 35.00, 3.40, 30.82, 59.83, 82.17, 14.02, 25.70, 81.38, 50.33), 
    Location = c("A", "A", "A", "A", "A", "A", "A", "A", "A","B", "B", "B", "B", "B", "B", "B", "B", "B"), 
    NoSales = c("","", "Y", "", "", "Y", "", "", "Y", "", "", "Y", "Y", "","", "", "Y", "Y"))

这应该导致以下结果:

Pcode   Value   Location    NoSales
123456  51.96   A   
234567  89.64   A   
345678  21.56   A           Y
456789  56.04   A   
567891  47.56   A   
678912  83.68   A           Y
789123  42.21   A   
891234  66.56   A   
912345  62.72   A           Y
123456  35      B   
234567  3.4     B   
345678  30.82   B           Y
456789  59.83   B           Y
567891  82.17   B   
678912  14.02   B   
789123  25.7    B   
891234  81.38   B           Y
912345  50.33   B           Y

我想做的是将库存从地点 B 转移到地点 A，并计算出没有销售的库存的总值(value)是多少。因此，我需要位置 A 中无销售中带有 Y 标志的所有产品的值(value)总和，以及位置 B 中位置 A 中具有无销售标志 Y 的所有产品的值(value)的总和。

到目前为止，我已完成以下操作:

# get all NoSales flag Y products in Location A
ANoSales <- stock_profile[Location == "A" & NoSales == "Y"]    
# get all prodcuts in location B 
BStock <- stock_profile[Location == "B"]
# left merge 
NoSalesAll <- merge(ANoSales,BStock,by="Pcode",all.x = TRUE)
# create new column aggregating the value and give the total sum
NoSalesAll[,Value := Value.x + Value.y][,sum(Value)]

它可以工作，但并不是很优雅。我认为 ifelse 应该可以吗？欢迎并赞赏任何建议:)

Answer 1

我不确定这有多优雅，但就是这样，

library(data.table)

sum(
  rowSums(dcast(stock_profile, Pcode ~ Location + NoSales, value.var = 'Value')
            [!is.na(A_Y), -1], na.rm = TRUE)
  )
#[1] 263.13

根据 @Frank 的评论，我们可以使用 .SD 来避免 rowSums，

dcast(dt, Pcode ~ Location + NoSales, value.var = 'Value')[
  !is.na(A_Y), sum(.SD, na.rm=TRUE), .SDcols=-1]