Data Table Optimization - Conditional Amount

Question

I am trying to make a notional amount on data.table and managed to do this in a dirty way now. I was wondering if this can be done 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"))

This 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 transfer the shares from location B to and find out what the total value of the shares that have no sales will be. Therefore, I need the sum of the cost of all products with the Y flag in NoSales at location A, combined with the value of all products at location B that do not have a sales flag Y at location A.

So far I have succeeded:

# 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 not very elegant. I believe this is possible with ifelse, maybe? Any suggestions are welcome and appreciated :)

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

rowSums .SD @Frank,

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

Answer 2

Sotos answer. "" , data.table, dplyr :

stock_profile %>% 
  filter(Location=="A" & NoSales=="Y") %>% 
  left_join(filter(stock_profile, Location=="B"), by="Pcode") %>% 
  mutate(value=Value.x+Value.y) %>% 
  summarise_at(vars(value),sum)

   value
1 263.13

Answer 3

Here is my contribution.

setkey(stock_profile, Location, NoSales)
DT1 = stock_profile[.("A","Y"), sum(Value),by=.(Pcode)]
DT2 = stock_profile[.("B"), sum(Value),by=.(Pcode)]
DT = merge(DT1, DT2, by="Pcode", all.x=TRUE)
DT[, .SD, .SDcols = names(DT) %like% "V1.x|V1.y"][,sum(V1.x,V1.y)]