如何基于起止日期条件在R中合并DataFrame并匹配x_line？

背景信息 #

用户拥有以下两个数据集：

df1 数据 #

library(dplyr)
library(tidyverse)

df1 = data.frame(ID = c(100,101,101,102,102,103,103,104,104,105,106),
                 x_line = c(1,1,2,1,2,1,2,1,2,1,1),
                 start_date = c('04/01/2018','05/01/2019','25/08/2021','08/03/2017','07/08/2018',
                                '09/04/2016','29/12/2018','04/08/2018','03/05/2022','04/01/2018','04/01/2018'),
                 end_date = c('04/05/2019','07/02/2020','27/09/2021','18/07/2018','17/10/2019',
                              '19/12/2018','22/12/2019','14/09/2021','26/12/2022','15/02/2020','24/08/2020') 
                 )

df2 数据 #

df2 = data.frame(ID = c(100,100,100,101,101,102,102,103,103,104,104,105,105,106,106,106),
                 product_name = c('AA','BB','CC','AA','CC','DD','EE','DD','FF',
                                  'AA','FF','DD','AA','CC','AA','BB'),
                 start_taken_date = c('04/05/2018','25/08/2018','27/09/2018','18/07/2019','25/11/2019',
                                      '29/01/2018','07/09/2018','14/09/2017','01/01/2019','15/02/2019','24/08/2020',
                                      '04/03/2019','04/08/2018',
                                      '05/05/2018','06/06/2019','08/09/2018'),
                 end_taken_date = c('01/05/2019','26/09/2018','25/03/2019','25/09/2019','02/01/2020',
                                    '19/06/2018','22/09/2019','16/01/2018','04/03/2019','25/06/2022','23/07/2022',
                                    '05/04/2019','05/09/2018',
                                    '29/03/2019','07/07/2019','04/05/2020'))

用户尝试通过left_join按ID合并两个数据集，再用ifelse判断日期区间归属生成line_m字段，但未得到预期输出。预期输出为：

ID     product_name start_taken_date end_taken_date x_line
1  100           AA       04/05/2018     01/05/2019      1
2  100           BB       25/08/2018     26/09/2018      1
3  100           CC       27/09/2018     25/03/2019      1
4  101           AA       18/07/2019     25/09/2019      1
5  101           CC       25/11/2019     02/01/2020      1
6  102           DD       29/01/2018     19/06/2018      1
7  102           EE       07/09/2018     22/09/2019      2
8  103           DD       14/09/2017     16/01/2018      1
9  103           FF       01/01/2019     04/03/2019      2
10 104           AA       15/02/2019     25/06/2022      1
11 104           FF       24/08/2020     23/07/2022      1
12 105           DD       04/03/2019     05/04/2019      1
13 105           AA       04/08/2018     05/09/2018      1
14 106           CC       05/05/2018     29/03/2019      1
15 106           AA       06/06/2019     07/07/2019      1
16 106           BB       08/09/2018     04/05/2020      1

问题根源 #

1. 日期格式错误：原数据中日期为字符型，直接进行字符串比较会导致逻辑错误（例如字符"04/05/2018"和"05/01/2019"的比较结果与实际日期比较结果不符）。
2. 合并后数据冗余：left_join按ID合并时，对于存在多个x_line的ID（如101、102），会生成笛卡尔积，导致每个df2的行对应多个df1的行，后续的ifelse无法正确匹配唯一的目标x_line。

正确实现方法 #

步骤1：转换日期列为日期类型 #

使用lubridate包的dmy()函数（因为日期格式为日/月/年）将字符型日期转换为日期类型：

library(lubridate)

# 处理df1的日期
df1 <- df1 %>%
  mutate(
    start_date = dmy(start_date),
    end_date = dmy(end_date)
  )

# 处理df2的日期
df2 <- df2 %>%
  mutate(
    start_taken_date = dmy(start_taken_date),
    end_taken_date = dmy(end_taken_date)
  )

步骤2：合并并筛选匹配的日期区间 #

方法一：使用left_join后筛选符合条件的行

df_result <- df2 %>%
  left_join(df1, by = "ID") %>%
  # 筛选产品服用区间完全落在df1对应line区间内的记录
  filter(start_taken_date >= start_date & end_taken_date <= end_date) %>%
  # 保留所需列
  select(ID, product_name, start_taken_date, end_taken_date, x_line)

方法二：使用fuzzyjoin包进行模糊匹配（更高效，避免笛卡尔积）

library(fuzzyjoin)

df_result <- fuzzy_left_join(
  df2,
  df1,
  by = c(
    "ID" = "ID",
    "start_taken_date" = "start_date",
    "end_taken_date" = "end_date"
  ),
  match_fun = list(`==`, `>=`, `<=`)
) %>%
  filter(!is.na(x_line)) %>% # 过滤无匹配的记录（如果存在）
  select(ID = ID.x, product_name, start_taken_date, end_taken_date, x_line)

验证结果 #

运行上述代码后，df_result将与预期输出完全一致。

内容的提问来源于stack exchange，提问作者An116