R Data.Table Join on Conditionals

Question

I have two tables that I would like to join together in a way equivalent to the following SQL, where I join on multiple conditions, not just equality.

require(sqldf)
require(data.table)

dt <- data.table(num=c(1, 2, 3, 4, 5, 6), 
char=c('A', 'A', 'A', 'B', 'B', 'B'), 
bool=c(TRUE, FALSE, TRUE, FALSE, TRUE, FALSE))

dt_two <- data.table(
num =c(6, 1, 5, 2, 4, 3), 
char=c('A', 'A', 'A', 'B', 'B', 'B'), 
bool=c(TRUE, FALSE, TRUE, FALSE, TRUE, FALSE))


dt_out_sql <- sqldf('
    select dtone.num, dtone.char, dtone.bool, SUM(dttwo.num) as SUM,  
   MIN(dttwo.num) as MIN
    from dt as dtone INNER join dt_two as dttwo on 
    (dtone.char = dttwo.char) and 
    (dtone.num >= dttwo.num OR dtone.bool)
GROUP BY dtone.num, dtone.char, dtone.bool')

I would like to avoid the SQL solution, for both performance and flexibility reasons. The same goes for doing a cross join, and then filtering/aggregating -- it would create an intermediate table with lots of unnecessary records for me to filter out.

Thank you very much!

Update -- my initial example was done in haste. In my actual problem, I'm not doing a self join.

score 11 · Answer 1 · edited May 23 '17 at 12:32

11

Here's one way:

require(data.table)
setkey(dt, char)
setkey(dt_two, char)

dt_two[dt, {
   val = num[i.bool | i.num >= num]; 
   list(num=i.num, bool=i.bool, sum=sum(val), min=min(val))
}, by=.EACHI]
#    char num  bool sum min
# 1:    A   1  TRUE  12   1
# 2:    A   2 FALSE   1   1
# 3:    A   3  TRUE  12   1
# 4:    B   4 FALSE   9   2
# 5:    B   5  TRUE   9   2
# 6:    B   6 FALSE   9   2

To read about by=.EACHI, have a look at this post (until vignettes on joins are finished).

HTH

edited May 23 '17 at 12:32

Community

1
1

answered Feb 26 '15 at 17:18

Arun

116,683
26
284
387

thanks for posting this; it avoids the cartesian product. Is there a way to do something similar, but summarizing the index column instead? I've asked a new question here: http://stackoverflow.com/questions/28761809/summarize-the-self-join-index-while-avoiding-cartesian-product-in-r-data-table – nsheff Feb 27 '15 at 10:02

score 5 · Accepted Answer · edited Feb 12 '16 at 08:43

It's a bit ugly but works:

library(data.table)
library(sqldf)

dt <- data.table(num=c(1, 2, 3, 4, 5, 6), 
                 char=c('A', 'A', 'A', 'B', 'B', 'B'), 
                 bool=c(TRUE, FALSE, TRUE, FALSE, TRUE, FALSE))

dt_two <- data.table(
  num =c(6, 1, 5, 2, 4, 3), 
  char=c('A', 'A', 'A', 'B', 'B', 'B'), 
  bool=c(TRUE, FALSE, TRUE, FALSE, TRUE, FALSE))


dt_out_sql <- sqldf('
    select dtone.num,
            dtone.char,
            dtone.bool,
            SUM(dttwo.num) as SUM,  
            MIN(dttwo.num) as MIN
    from    dt as dtone
    INNER join dt_two as dttwo on 
          (dtone.char = dttwo.char) and 
          (dtone.num >= dttwo.num OR dtone.bool)
    GROUP BY dtone.num, dtone.char, dtone.bool
  ')

setDT(dt_out_sql)

setkey(dt, char)
setkey(dt_two, char)

dt_out_r <- dt[dt_two,
               list(dtone.num = num,
                    dttwo.num = i.num,
                    char,
                    bool) ,
               nomatch = 0, allow.cartesian = T
               ][
                 dtone.num >= dttwo.num | bool,
                 list(SUM = sum(dttwo.num),
                      MIN = min(dttwo.num)),
                 by = list(num = dtone.num,
                           char,
                           bool)
                 ]

setkey(dt_out_r, num, char, bool)


all.equal(dt_out_sql, dt_out_r, check.attributes = FALSE)

I benchmarked each of the possible options, and this actually turned out to be fastest. In this case, I can live with ugly. :) — Netbrian, Feb 26 '15 at 23:54

nikola · Answer 3 · 2017-04-18T08:40:10.627

-2

Since data.table 1.9.8, for cases where join conditions could be relaxed, there's the simple non-equi join syntax like so:

dt_two[dt, on=.(char, num >= num)]

edited Apr 18 '17 at 08:40

answered Apr 12 '17 at 16:03

nikola

5,286
3
22
19

This doesn't return the expected result as shown [here](http://stackoverflow.com/a/28748717/3817004), e.g. – Uwe Apr 12 '17 at 17:37
Sorry, yes the reason this doesn't return the same result is because the second condition is actually `num >= num OR dtone`. As far as I could see this isn't valid in current data.table. However, I still think it's relevant to mention the non-equi join feature for cases where the join conditions are more relaxed. – nikola Apr 18 '17 at 08:38

R Data.Table Join on Conditionals

3 Answers3

Linked