Conditional JOIN on two data frames in R

Question

Suppose there are two data frames likes the following (given from this post):

df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)))
df2 = data.frame(CustomerId = c(2, 4, 6), State = c(rep("Alabama", 2), rep("Ohio", 1)))

df1
#  CustomerId Product
#           1 Toaster
#           2 Toaster
#           3 Toaster
#           4   Radio
#           5   Radio
#           6   Radio

df2
#  CustomerId   State
#           2 Alabama
#           4 Alabama
#           6    Ohio

The question is how can I do the following sql query in R:

SELECT * FROM df1 JOIN df2 on df1.CustomerId <= df2.CustomerId

What I have known is that I can do the inner join using merge(df1, df2, by = "CustomerId"). But it is not satisfied the condition of the join.

`library(sqldf); sqldf("SELECT * FROM df1 JOIN df2 on df1.CustomerId <= df2.CustomerId")` — G. Grothendieck, Oct 15 '17 at 14:06
@G.Grothendieck So, it can not be done using `merge` function? — OmG, Oct 15 '17 at 14:08
See this [link](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — Buddhi, Oct 15 '17 at 14:13
You can do it but it will create a very large intermediate result with `nrow(df1) * nrows(df2)` rows from `merge`: `subset(merge(df1, df2, by = NULL), as.character(CustomerId.x) <= as.character(CustomerId.y))` — G. Grothendieck, Oct 15 '17 at 14:14
@L30n1d45 it is not conditional. As you've read my question, I've referenced to it. — OmG, Oct 15 '17 at 14:14
Ok I thought ur Q was about how to do it in R and not about running SQL queries in R env — mtoto, Oct 18 '17 at 10:14

score 0 · Answer 1 · answered Oct 15 '17 at 16:01

This one confusing way to do this. But it works though:

library(tidyverse)
df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)))
df2 = data.frame(CustomerId = c(2, 4, 6), State = c(rep("Alabama", 2), rep("Ohio", 1)))

map2_df(
  df1$CustomerId, df1$Product,
  .f = ~ {
    temp <- df2 %>% filter(.x <= CustomerId)
    tibble(CustomerId.x = .x, Product = .y, 
           CustomerId.y = temp$CustomerId, State = temp$State)
  }
)

score 0 · Accepted Answer · answered Oct 23 '17 at 19:00

0

As I found in comments by dear Grothendieck, one straightforward solution is using sqldf package and get exactly my result in sql format:

library(sqldf)
sqldf("SELECT * FROM df1 JOIN df2 on df1.CustomerId <= df2.CustomerId")

answered Oct 23 '17 at 19:00

OmG

18,337
10
57
90

Conditional JOIN on two data frames in R

2 Answers2