Fast melted data.table operations

Quick untested answer: seems like you're looking for by-without-by, a.k.a. grouping-by-i :

setkey(input,variable)
input[c("x","y"),sum(value)]

This is like a fast HAVING in SQL. j gets evaluated for each row of i. In other words, the above is the same result but much faster than :

input[,sum(value),keyby=variable][c("x","y")]

The latter subsets and evals for all the groups (wastefully) before selecting only the groups of interest. The former (by-without-by) goes straight to the subset of groups only.

The group results will be returned in long format, as always. But reshaping to wide afterwards on the (relatively small) aggregated data should be relatively instant. That's the thinking anyway.

The first setkey(input,variable) might bite if input has a lot of columns not of interest. If so, it might be worth subsetting the columns needed :

DT = setkey(input[ , c("variable","value")], variable)
DT[c("x","y"),sum(value)]

In future when secondary keys are implemented that would be easier :

set2key(input,variable)              # add a secondary key 
input[c("x","y"),sum(value),key=2]   # syntax speculative

To group by id as well :

setkey(input,variable)
input[c("x","y"),sum(value),by='variable,id']

and including id in the key might be worth setkey's cost depending on your data :

setkey(input,variable,id)
input[c("x","y"),sum(value),by='variable,id']

If you combine a by-without-by with by, as above, then the by-without-by then operates just like a subset; i.e., j is only run for each row of i when by is missing (hence the name by-without-by). So you need to include variable, again, in the by as shown above.

Alternatively, the following should group by id over the union of "x" and "y" instead (but the above is what you asked for in the question, iiuc) :

input[c("x","y"),sum(value),by=id]

> setkey(input, "id")
> input[ , list(sum(value)), by=id]
   id V1
1:  1  6
2:  2 15
3:  3 34

> input[ variable %in% c("x", "y"), list(sum(value)), by=id]
   id V1
1:  1  6
2:  2 15
3:  3 24

The last one:

> input[ variable %in% c("x", "y"), list(sum(value)), by=list(id, variable)]
   id variable V1
1:  1        x  1
2:  1        y  5
3:  2        x  4
4:  2        y 11
5:  3        x 15
6:  3        y  9

I'm not sure if this is the best way, but you can try:

input[, list(x = sum(value[variable == "x"]), 
             y = sum(value[variable == "y"])), by = "id"]
#    id  x  y
# 1:  1  1  5
# 2:  2  4 11
# 3:  3 15  9