Merge dataframes using "between"

Question

I have the following two dataframes:

df1:
column_01 column_02
a         3
b         7

df2:
column_01 column_02 column_03 column_04
a         1         4         a-low
a         5         8         a-high
b         1         4         b-low
b         5         8         b-high

I have to merge df1 with column_04 of df2, where column_01 of df1 matches column_01 of df2 AND column_02 of df1 has to be between the values of df2 column_02 and column_03. The result would be:

df_result:
column_01 column_02 column_03
a         3         a-low
b         7         b-high

In sql it would be a join with a between clause. But how can I get this done in R?

Have a look at `data.table` package and search for rolling join. — Eric Lecoutre, Feb 09 '17 at 08:19
if you are good at sql queries, [sqldf](https://cran.r-project.org/web/packages/sqldf/index.html) package might be very useful. — Imran Ali, Feb 09 '17 at 08:31
Rolling join was the term I was looking for. Thank you Eric. — Kasper, Feb 09 '17 at 12:08

Wietze314 · Accepted Answer · 2017-02-09T08:28:19.297

0

You can do this in two steps. First join, then filter. I use the package dplyr for this.

df1 <- data.frame(c1 = c('a','b'),
                  c2 = c(3,7))

df2 <- data.frame(c1 = c('a','a','b','b'),
                  c2 = c(1,5,1,5),
                  c3 = c(4,8,4,8),
                  c4 = c('al','ah','bl','bh'))

library(dplyr)

df1 %>% left_join(df2, by='c1') %>%
  filter(c2.x > c2.y, c2.x < c3)

edited Feb 09 '17 at 08:28

answered Feb 09 '17 at 08:22

Wietze314

5,942
2
21
40

1

Or use `filter(between(pos, start, end))` – zx8754 Feb 09 '17 at 08:26
Thanks Wietze314 and zx8754. Works! – Kasper Feb 09 '17 at 12:05

Merge dataframes using "between"

1 Answers1