data.table optimisation - conditional sum

Question

I'm trying to do a conditional sum on a data.table and managed to do it currently in a messy way. I was wondering if it would be possible to do it more elegantly?

Consider the following:

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"))

Which should result in the following:

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

What I'm trying to do is to transfer stock from Location B to A and figure out what the total value of stock that has no sales is going to be. So I need a sum of the value of all products with flag Y in NoSales in Location A combined with the value of all products in Location B which have No Sales flag Y in Location A.

So far I've managed the following:

# 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)]

It works, but is not really elegant. I reckon it should be possible with ifelse perhaps? Any suggestions welcome and appreciated :)

Answer 1

I am not sure how elegant this is, but here it is,

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

We can avoid rowSums with the use of .SD as per @Frank's comment,

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