i argument in data.table (expression that evaluates to logical)
data.table, group-by, r, subset
Solution
Rightly or wrongly, `i` runs first then `j` runs by `by` on all the rows that pass `i`.
A common idiom is something like this (similar to HAVING in SQL) :
test[,list(id, u=length(unique(id))), by=t][u>1]
and to exclude `u` (the number of unique ids within each group) from the result :
test[,list(id, u=length(unique(id))), by=t][u>1][,u:=NULL]
Btw, doing vector scans in `i` on (much smaller) aggregated results (such as `u>1` in the line above) is much more efficient than doing vector scans on the (much larger) original data.
If `j` ran by `by` on the whole dataset, followed by `i` on the result (as you expected) then it would cause a problem for efficiency. Consider if it worked that way. Then a filter first followed by grouping on the result would need to be split up into two `[` calls: `DT[i][,j,by]`. Then `i` doesn't see `j` (within `[.data.table`) and doesn't know which columns it needs. Combining it into one `DT[i,j,by]` allows `i` to inspect `j` before evaluation and only subset the columns that `j` needs. This makes a very large difference in large datasets on queries which use a small subset of the columns.
To see what happened, take your `i` and make it `j` :
test[,length(unique(id))>1]
# [1] TRUE
Then the single `TRUE` was recycled. `DT[TRUE] == DT`. You can always test `i` by making it `j` like that.
Problem
i have the following: ``` test <- data.table(id=1:11, t=c(rep(1:2,5), 3)) test[length(unique(id))>1,list(id, t), by=t] id t 1: 1 1 2: 2 2 3: 3 1 4: 4 2 5: 5 1 6: 6 2 7: 7 1 8: 8 2 9: 9 1 10: 10 2 11: 11 3 ``` i expected this to group `test` by `t`, evaluate the `j` statement on each group, and return the rows where `i` is true (that is there is more than 1 unique id). instead what is returned is this: ``` > test id t 1: 1 1 2: 2 2 3: 3 1 4: 4 2 5: 5 1 6: 6 2 7: 7 1 8: 8 2 9: 9 1 10: 10 2 11: 11 3 ``` It seems as though the `by` applies to the `j` only and not to `i`. Any suggestions here?