R数据表 [英] R Data.Table Join on Conditionals
本文介绍了R数据表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表,我想以类似于以下SQL的方式连接在一起,其中我在多个条件,不只是平等连接。
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')
我想避免SQL解决方案,出于性能和灵活性的原因。同样的做一个交叉连接,然后过滤/聚合 - 它会创建一个中间表,有很多不必要的记录,我可以过滤掉。
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.
谢谢非常!
更新 - 我的初始示例是匆忙完成。
Update -- my initial example was done in haste. In my actual problem, I'm not doing a self join.
推荐答案
这有点丑陋,但工作原理:
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)
这篇关于R数据表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文