I have two dataframes df1 and df2. I would like to join the two with the following conditions:

1. merge df1 and df2 on gender and Test
2. TestDate in df1 need to be within Date1 and Date2 from df2
3. all.x = TRUE (keep df1 records)

I am not sure how to handle the 2nd part. Could anyone guide me on this part?

df1 <- structure(list(ID = c(1, 2, 3, 5, 4), Gender = c("F", "M", "M", 
"F", "F"), TestDate = structure(c(17897, 17898, 18630, 18262, 
17900), class = "Date"), Test = c("Weight", "Weight", "ELA", 
"ELA", "Math")), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))


df2 <- structure(list(Test = c("Weight", "Weight", "ELA", "ELA", "ELA", 
"ELA", "Math", "Math"), Gender = c("F", "M", "F", "M", "F", "M", 
"F", "M"), Date1 = structure(c(17532, 17534, 17536, 17537, 18266, 
18267, 17897, 17539), class = "Date"), Ave = c(97, 99, 85, 84, 
83, 82, 88, 89), Date2 = structure(c(18993, 18995, 18266, 18267, 
18997, 18998, 18999, 19000), class = "Date")), row.names = c(NA, 
-8L), class = c("tbl_df", "tbl", "data.frame"))

CodePudding user response：

Does this work for you?

library(dplyr)
library(data.table)
merge(x = df1, 
      y = df2) %>% 
  filter(TestDate