I am trying to join two data.frames based on a condition. Consider the following situation where I have df_a and df_b.
library(tidyverse)
# Dummy data A
df_a <- tibble(
id = c("a", "b", "c", "a"),
text = c("hi","why", "bye","cry"),
created_date = c(as.Date("2020-01-01"), as.Date("2020-02-02"), as.Date("2020-03-01"), as.Date("2020-04-04"))
)
# Dummy data B
df_b <- tibble(
id = c("a", "b", "c", "a"),
group = c("GROUP A","GROUP B","GROUP C", "GROUP C"),
start_date = c(as.Date("2020-01-01"), as.Date("2020-01-01"), as.Date("2020-01-01"), as.Date("2020-02-04"))
)
> df_a
# A tibble: 4 x 3
id text created_date
<chr> <chr> <date>
1 a hi 2020-01-01
2 b why 2020-02-02
3 c bye 2020-03-01
4 a cry 2020-04-04
> df_b
# A tibble: 4 x 3
id group start_date
<chr> <chr> <date>
1 a GROUP A 2020-01-01
2 b GROUP B 2020-01-01
3 c GROUP C 2020-01-01
4 a GROUP C 2020-02-04
# Current solution
d_current_sol <- df_a %>%
left_join(
df_b %>%
distinct(id, .keep_all = T), by = "id"
)
> d_current_sol
# A tibble: 4 x 5
id text created_date group start_date
<chr> <chr> <date> <chr> <date>
1 a hi 2020-01-01 GROUP A 2020-01-01
2 b why 2020-02-02 GROUP B 2020-01-01
3 c bye 2020-03-01 GROUP C 2020-01-01
4 a cry 2020-04-04 GROUP A 2020-01-01
# Desired solution
> d_desired
# A tibble: 4 x 5
id text created_date start_date group
<chr> <chr> <date> <date> <chr>
1 a hi 2020-01-01 2020-01-01 GROUP A
2 b why 2020-02-02 2020-01-01 GROUP B
3 c bye 2020-03-01 2020-01-01 GROUP C
4 a cry 2020-04-04 2020-02-04 GROUP C
As you can see in df_b the start date for id = a is first 2020-01-01 and then 2020-02-04.
What I would like is for any row in df_a after 2020-02-04, it's associated group to be "GROUP C". This is illustrated in the final data frame d_desired
However, with a traditional left_join where we match the row to a single row item, we only get the earliest entry (d_current_sol). This would be easy enough to do using map() and using a custom function with mutate(), however, SQL doesn't support that when translating dplyr queries.
Does anyone know a way of doing this through the tidyverse paradigm? As I will be using dbplyr to run this on a PostgreSQL database.
this?
df_a %>%
left_join(df_b, by = "id" ) %>%
filter( created_date >= start_date) %>%
group_by(id, created_date) %>%
top_n(1,start_date)
# A tibble: 4 x 5
# Groups: id, created_date [4]
id text created_date group start_date
<chr> <chr> <date> <chr> <date>
1 a hi 2020-01-01 GROUP A 2020-01-01
2 b why 2020-02-02 GROUP B 2020-01-01
3 c bye 2020-03-01 GROUP C 2020-01-01
4 a cry 2020-04-04 GROUP C 2020-02-04