Left Join with Multiple Criteria

user_13501029 0
Viewed 53

I have two datas and I'd like to do a left join with 2 criteria.

Criteria 1 : Same Title

Criteria 2 : Date between StartDate & EndDate

Data B

Title  Date
A      2018-07-01
B      2019-12-30

Data A

Title StartDate  EndDate     Score
A     2018-01-01 2018-05-18    0
A     2018-05-19 2019-01-01    1
B     2019-10-01 2020-02-01    4
B     2020-02-02 2020-10-01    7

This is what I want to get

Title  Score
A       1
B       4

Thanks in Advance!

r merge
1 Answers

We can use a non-equi join with data.table

library(data.table)
setDT(DataA)[DataB,  .(Title, Score), on = 
      .(Title, StartDate < Date, EndDate >= Date)]
#   Title Score
#1:     A     1
#2:     B     4

data

DataA <- structure(list(Title = c("A", "A", "B", "B"), 
StartDate = structure(c(17532, 
17670, 18170, 18294), class = "Date"), EndDate = structure(c(17669, 
17897, 18293, 18536), class = "Date"), Score = c(0L, 1L, 4L, 
7L)), row.names = c(NA, -4L), class = "data.frame")

DataB <- structure(list(Title = c("A", "B"), Date = structure(c(17713, 
18260), class = "Date")), row.names = c(NA, -2L), class = "data.frame")
user_37322710
Related