rdataframedatemultiple-conditionsright-join

How do I do right join with multiple conditions?


I have an example below where I need to perform a right join with multiple conditions:

1st dataframe:

df_1 <- data_frame(
  dates = c(as.Date("2023-03-01"), as.Date("2023-03-05"), as.Date("2023-03-03"), as.Date("2023-02-27"), as.Date("2023-02-26"), as.Date("2023-01-26"), as.Date("2023-01-29"), as.Date("2022-12-27")),
  id = c("03","02","03","04","04","09","09","05"),
  x2 = c(20, 21, 22, 23, 24, 25, 26, 27))

2nd dataframe:

df_2 <- data_frame(
  id = c("02","03","04","05","09"))

I'm trying to map the values from x2 column from 1st dataframe to 2nd dataframe. In this scenario, there might be multiple matches and I'd like to prioritise matching the values on 2023-03-01. If there are no values on 2023-03-01, I'll then try to match the value with the next closest day to 2023-03-01 in March (e.g. 2023-03-05).

If there are still no matches in March, then I'll try to match the value with the date closest to 2023-03-01 in the past (e.g. 2023-02-27). Please find the desired output below:

df_3 <- data_frame(
  id = c("02","03","04","05","09"),
  x2 = c(21, 20, 23, 27, 26))

It's a bit of a tricky situation, so if anyone has any suggestions, that would be greatly appreciated!


Solution

  • df_2 |>
      left_join(slice_min(df_1, abs(as.Date("2023-03-01")-dates), by=id)) |>
      select(-dates)
    
    # A tibble: 5 × 2
      id       x2
      <chr> <dbl>
    1 02       21
    2 03       20
    3 04       23
    4 05       27
    5 09       26