Join with Not Join in data.table?

Question

My question relates to R data.table with multiple keys. take this example:

library(data.table) example(data.table) key(DT) [1] "x" "y" 

and suppose I want the variation "x not equal b and y not equal 3", as here:

 DT[!J("b",3)] xyv v2 m 1: a 1 42 NA 42 2: a 3 42 NA 42 3: a 6 42 NA 42 4: b 1 4 84 5 5: b 6 6 84 5 6: c 1 7 NA 8 7: c 3 8 NA 8 8: c 6 9 NA 8 

I need a variation of "x EQUAL b and y NOT is 3", as here:

 DT[J("b",!3)] Error in `[.data.table`(DT, J("b", !3)) : typeof xy (double) != typeof i.V2 (logical) 

Is it likely to say J () to undo some keys? Thanks!

Answer 1

For compound keys, you can use the following

  DT[.("b")][!.(x, 3)] # x is the name of first column of key 

In general, you can link several [ ] [ ] to filter to the desired results.

---

Please note that you can also easily use logical operators in i of data.table .
The syntax is J() - or now .( ) Is just a shorthand convenience.

You can use almost everything that could be included in the if clause in order to access the column names as variables.

In your specific example, you would use x=="b" & y != 3 pay attention to a single & , not && .

  DT[ x=="b" & y != 3] 

You can also combine vector checks with binary search data.table as follows

  DT[.("b")][y != 3]