data.table WHERE before BY

Question

I have the following problem, which has probably a pretty simple solution: When I use

library (data.table)
actions = data.table(User_id = c("Carl","Carl","Carl","Lisa","Moe"),
                     category = c(1,1,2,2,1),
                     value= c(10,20,30,40,50))

   User_id category value
1:    Carl        1    10
2:    Carl        1    20
3:    Carl        2    30
4:    Lisa        2    40
5:     Moe        1    50

actions[category==1,sum(value),by= User_id]

The problem is, that apparently it first sorts out the rows where category is 1 and then uses the by command. So what I get is:

   User_id V1
1:    Carl 30
2:     Moe 50

But what I want is:

   User_id V1
1:    Carl 30
2:    Lisa 0
3:     Moe 50

I am building a data.table just containing information about the users, so:

users = actions[,User_id,by= User_id]
users$value_one = actions[category==1,.(value_one =sum(value)),by= User_id]$value_one

which throws errors or includes wrong values, when there are some users that have no entry.

Answer 1

This is almost as succinct, and gets the job done.

actions[, .SD[category==1, sum(value)], by=User_id]
#    User_id V1
# 1:    Carl 30
# 2:    Lisa  0
# 3:     Moe 50

## Or, better yet, no need to muck around with .SD, (h.t. David Arenburg)
actions[, sum(value[category == 1]), by = User_id]
#    User_id V1
# 1:    Carl 30
# 2:    Lisa  0
# 3:     Moe 50

If the relative inefficiency of the above is a problem in your use case, here's a more efficient alternative:

res <- actions[, .(val=0), by=User_id]
res[actions[category==1, .(val=sum(value)), by=User_id], val:=i.val, on="User_id"]    
res
#    User_id val
# 1:    Carl  30
# 2:    Lisa   0
# 3:     Moe  50