How to do a data.table merge operation
You are quoting the wrong part of documentation. If you have a look at the doc of [.data.table you will read:
When i is a data.table, x must have a key, meaning join i to x and return the rows in x that match. An equi-join is performed between each column in i to each column in x’s key in order. This is similar to base R functionality of sub- setting a matrix by a 2-column matrix, and in higher dimensions subsetting an n-dimensional array by an n-column matrix
I admit the description of the package (the part you quoted) is somewhat confusing, because it seems to say that the "["-operation can be used instead of merge. But I think what it says is: if x and y are both data.tables we use a join on an index (which is invoked like merge) instead of binary search.
One more thing:
The data.table library I installed via install.packages was missing the merge.data.table method, so using merge would call merge.data.frame. After installing the package from R-Forge R used the faster merge.data.table method.
You can check if you have the merge.data.table method by checking the output of:
methods(generic.function="merge")EDIT [Answer no longer valid]: This answer refers to data.table version 1.3. In version 1.5.3 the behaviour of data.table changed and x[y] returns the expected results. Thank you Matthew Dowle, author of data.table, for pointing this out in the comments.
Thanks for the answers. I missed this thread when it was originally posted. data.table has moved on since February. 1.4.1 was released to CRAN a while ago and 1.5 is out soon. For example the DT() alias has been replaced with list(); as a primitive its much faster, and data.table now inherits from data.frame so it works with packages that only accept data.frame such as ggplot and lattice, without any conversion required (faster and more convenient).
Is it possible to subscribe to the data.table tag so I get an email when someone posts a question with that tag? The datatable-help list has grown to about 30-40 messages a month, but I'm happy to answer here too if I can get some kind of notification.
Matthew
I think using the base::merge function is not needed, as using data.table joins can be a lot faster. E.g. see the following. I make x and y data.tables with 3-3 columns:
x <- data.table( foo = 1:5, a=20:24, zoo = 5:1 )y <- data.table( foo = 1:5, b=30:34, boo = 10:14)setkey(x, foo)setkey(y, foo)And merge both with base:merge and data.table joins to see the speed of executions:
system.time(merge(x,y))## user system elapsed ## 0.027 0.000 0.023 system.time(x[,list(y,x)])## user system elapsed ## 0.003 0.000 0.006 The results are not identical, as the latter has one extra column:
merge(x,y)## foo a zoo b boo## [1,] 1 20 5 30 10## [2,] 2 21 4 31 11## [3,] 3 22 3 32 12## [4,] 4 23 2 33 13## [5,] 5 24 1 34 14x[,list(x,y)]## foo a zoo foo.1 b boo## [1,] 1 20 5 1 30 10## [2,] 2 21 4 2 31 11## [3,] 3 22 3 3 32 12## [4,] 4 23 2 4 33 13## [5,] 5 24 1 5 34 14Which could not make a big trouble :)