dplyr join with OR condition?

zoowalk 0
Viewed 29

I am wondering whether there is any way, preferably in the tidyverse, to join two dataframes based on OR conditions.

There are two dataframes: df_obs and df_event.

a) The join should happen if there is a match between the obs_id and event_id; and obs_date or event_date, or both are NA.

OR

b) the obs_date and event_date are identical; and obs_id, event_id, or both are NA.

The match should not happen if obs_id is not identical to event_id (if both are not NA) OR obs_date and event_date are not identical (both being not NA).

The result should look like df_res below. The column 'event' from the df_event is added to the df_obs.

I have seen the answer to this question, but maybe there is a way around SQL?

df_obs <- tibble::tribble(
    ~obs,    ~obs_date, ~obs_id,
    "a1",           NA,     10L,
    "a2", "01/01/2000",      NA,
     "b", "02/01/2000",      NA,
    "a3", "03/01/2000",     10L
    )
df_obs
#> # A tibble: 4 × 3
#>   obs   obs_date   obs_id
#>   <chr> <chr>       <int>
#> 1 a1    <NA>           10
#> 2 a2    01/01/2000     NA
#> 3 b     02/01/2000     NA
#> 4 a3    03/01/2000     10

df_event <- tibble::tribble(
    ~event,  ~event_date, ~event_id,
       "A", "01/01/2000",       10L,
       "B", "02/01/2000",        NA
    )

df_event
#> # A tibble: 2 × 3
#>   event event_date event_id
#>   <chr> <chr>         <int>
#> 1 A     01/01/2000       10
#> 2 B     02/01/2000       NA

df_res <- tibble::tribble(
        ~obs,    ~obs_date, ~obs_id, ~event,
        "a1",           NA,     10L,    "A",
        "a2", "01/01/2000",      NA,    "A",
         "b", "02/01/2000",      NA,    "B",
        "a3", "03/01/2000",     10L,     NA
        )
df_res
#> # A tibble: 4 × 4
#>   obs   obs_date   obs_id event
#>   <chr> <chr>       <int> <chr>
#> 1 a1    <NA>           10 A    
#> 2 a2    01/01/2000     NA A    
#> 3 b     02/01/2000     NA B    
#> 4 a3    03/01/2000     10 <NA>

Created on 2022-09-13 with reprex v2.0.2

left-join dplyr r
1 Answers

I can come up only with this solution:

df_obs %>%
    left_join(df_event, by = c("obs_id" = "event_id"), na_matches='never') %>%
    mutate(event = ifelse(!(is.na(obs_date)|is.na(event_date)|obs_date == event_date), NA, event)) %>%
    select(-event_date) %>%
    left_join(df_event, by = c("obs_date" = "event_date"), na_matches='never') %>%
    mutate(event.y = ifelse(!(is.na(obs_id)|is.na(event_id)|obs_id == event_id), NA, event.y)) %>%
    select(-event_id) %>%
    mutate(event = ifelse(is.na(event.x), event.y, event.x)) %>%
    select(-c(event.x, event.y))
userYE4D0
Related