CODEWITHSUNDEEP
r
alpha
alpha

Reputation: 1

How can I use AND OR with left join

I Want to Use Left Join or any join with an AND OR Condition in R. To illustrate with an example; if you have two data frames such as:

DF1

Name Date1
a    01/20/2021
b    01/25/2021
c    02/10/2021
d    02/16/2021
e    02/18/2021

DF2

Last Date       Date+1     Date+2 
f    01/20/2021 01/21/2021 01/22/2021
g    01/24/2021 01/25/2021 01/26/2021
h    02/16/2021 02/17/2021 02/18/2021

The goal is to obtain the following Output

Name Date1      Last Date       Date+1     Date+2 
a    01/20/2021 f    01/20/2021 01/21/2021 01/22/2021
b    01/25/2021 g    01/24/2021 01/25/2021 01/26/2021
d    02/16/2021 h    02/16/2021 02/17/2021 02/18/2021
e    02/18/2021 h    02/16/2021 02/17/2021 02/18/2021

What I wanted to do is I want to apply is any of the following rules.

  1. Date1 = Date OR Date1 = Date+1 OR Date1 = Date +2
  2. Date1>=Date AND Date1<=Date+3 I wanted to use any of these rules to get my desired output. I m a bit confused about how to apply this.Do let me know if it is difficult to understand.

Upvotes: 0

Views: 69

Answers (1)

Jon Spring
Jon Spring

Reputation: 66900

Here's an approach using dplyr and tidyr (both part of the tidyverse meta-package).

Start with DF1, and join with a version of DF2 in "long" format so that Date1 can be matched with any of the Date/Date+1/Date+2 columns. Having identified the "Last" value in the DF2 row we want, bring in all from that row in DF2.

library(tidyverse)
DF1 %>%        
  inner_join(DF2 %>% pivot_longer(-Last, names_to = "col", values_to = "Date"),
     by = c("Date1" = "Date")) %>%       
  left_join(DF2, by = "Last")

Result

  Name      Date1 Last    col       Date     Date+1     Date+2
1    a 01/20/2021    f   Date 01/20/2021 01/21/2021 01/22/2021
2    b 01/25/2021    g Date+1 01/24/2021 01/25/2021 01/26/2021
3    d 02/16/2021    h   Date 02/16/2021 02/17/2021 02/18/2021
4    e 02/18/2021    h Date+2 02/16/2021 02/17/2021 02/18/2021

Data:

DF1 <- data.frame(
  stringsAsFactors = FALSE,
        Name = c("a", "b", "c", "d", "e"),
  Date1 = c("01/20/2021","01/25/2021", "02/10/2021", "02/16/2021",
                       "02/18/2021"))

DF2 <- tribble(
  ~Last, ~Date,       ~`Date+1`,     ~`Date+2`,
  "f",   "01/20/2021", "01/21/2021", "01/22/2021",
  "g",   "01/24/2021", "01/25/2021", "01/26/2021",
  "h",   "02/16/2021", "02/17/2021", "02/18/2021"
)

Upvotes: 1

Related Questions