Cartesian join in data.table

12
votes

I am trying to do a full Cartesian join using data.table but with little luck.

Code: 

a = data.table(dt=c(20131017,20131018))
 setkey(a,dt)

 b = data.table(ticker=c("ABC","DEF","XYZ"),ind=c("MISC1","MISC2","MISC3"))
 setkey(b,ticker)

Expected output: 

merge(data.frame(a),data.frame(b),all.x=TRUE,all.y=TRUE)

I have tried merge(a,b,allow.cartesian=TRUE) but it gives me following error - "Error in merge.data.table(a, b, allow.cartesian = TRUE) : A non-empty vector of column names forbyis required."

I am using "R version 3.0.1 (2013-05-16)" with latest data.table packages. Any help would be greatly appreciated!

Regards

2
rdata.table
This looks more like an expand.grid-like problem than a merge-problem. You have no common variable. - IRTFM
The required output is achieved by Cartesian join using merge function but it works on data.frame and I am looking for a data.table solution if its possible. - Manoj
I usually add a dummy variable, merge by that variable, and have allow.cartesian = TRUE. CJ and expand.grid can cross join vectors but I could never find a base function to create a cross-join of two tables. Can anybody point to such a function, if it exists? - TheComeOnMan
@Codoremifa Yeah, I always just take the CJ or expand.grid of row numbers; never have found a better way. Here's an alternative, clumsy, solution: data.table(merge.data.frame(a,b,all=TRUE)). - Frank
As DWin points out, it is not really a merge operation. However to be compatible with merge.data.frame, I guess this functionality is required. Would you mind filing a feature request here? Thank you. - Arun

2 Answers

26
votes

I think a better solution is:

a[,as.list(b),by=dt]

         dt ticker   ind
1: 20131017    ABC MISC1
2: 20131017    DEF MISC2
3: 20131017    XYZ MISC3
4: 20131018    ABC MISC1
5: 20131018    DEF MISC2
6: 20131018    XYZ MISC3
0
votes

Expanding on @Codoremifa:

> dt <- c(20131017,20131018)
> b <- data.table(ticker=c("ABC","DEF","XYZ"), ind=c("MISC1","MISC2","MISC3"), key="ticker")
> b[CJ(ticker=ticker, dt=dt)][, c(3, 1, 2)]
         dt ticker   ind
1: 20131017    ABC MISC1
2: 20131018    ABC MISC1
3: 20131017    DEF MISC2
4: 20131018    DEF MISC2
5: 20131017    XYZ MISC3
6: 20131018    XYZ MISC3

Would be nicer if a single command would do it, but this is relatively straightforward.