I have two tables that I would like to join together in a way equivalent to
the following SQL. Note that I'm using a "greater than" statement in my
join, rather than checking for 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'))
dt_out_sql <- sqldf('
select dtone.num as num1, dttwo.num as num2, dttwo.char
from dt as dtone INNER join dt as dttwo on
(dtone.char = dttwo.char) and
(dtone.num *>=* dttwo.num)
')
I realize that I can use the below code, but would like to do the merging
and filtering in the same step (my data sets are large enough for
performance/memory concerns to come into play.
dt_out_r <- merge(x=dt, y=dt, by = c('char'), allow.cartesian=TRUE)
dt_out_r <- dt_out_r[num.x >= num.y]
Thank you very much!
[[alternative HTML version deleted]]
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
