如何在R中基于company_id匹配与日期规则关联数据框并添加rfp_id列

直接按company_id做表连接会触发many-to-many错误，因为单个company_id在summary中可能对应多条记录。我们可以针对hauled的每一条记录，在同company_id的summary子集里，按规则筛选出对应的rfp_id，从根源避免连接冲突。

1. 准备数据并转换日期格式 #

首先将示例数据转为R数据框，同时把字符型日期转为R标准Date格式（这是日期比较的前提）：

# 加载依赖包
library(dplyr)
library(purrr)

# 创建summary数据框
summary_df <- tibble(
  rfp_id = c(1,2,3,4,5),
  start_date = as.Date(c("12/30/2022", "4/1/2022", "7/1/2022", "1/16/2022", "1/1/2023"), format = "%m/%d/%Y"),
  end_date = as.Date(c("2/28/2023", "6/30/2022", "8/30/2022", "1/16/2023", "2/6/2023"), format = "%m/%d/%Y"),
  company_id = c(7,8,8,9,9)
)

# 创建hauled数据框（不含期望的rfp_id列）
hauled_df <- tibble(
  trans_num = c(11,12,13,14,15),
  company_id = c(7,8,8,8,9),
  trans_date = as.Date(c("1/14/2023", "7/2/2022", "3/20/2022", "9/1/2022", "1/15/2023"), format = "%m/%d/%Y")
)

2. 编写匹配逻辑函数 #

定义一个函数，输入单条hauled记录和对应的summary子集，返回符合规则的rfp_id：

match_rfp <- function(hauled_row, summary_data) {
  # 筛选当前company_id对应的summary记录
  company_summary <- filter(summary_data, company_id == hauled_row$company_id)
  
  # 第一步：判断trans_date是否落在某个区间内
  in_range_records <- filter(company_summary, trans_date >= start_date & trans_date <= end_date)
  
  if(nrow(in_range_records) > 0) {
    # 若有多个区间匹配，返回end_date最晚的rfp_id
    return(slice_max(in_range_records, end_date)$rfp_id)
  } else {
    # 第二步：计算trans_date到每个summary记录的最近日期距离（取start/end中更近的）
    company_summary <- company_summary %>%
      mutate(
        closest_dist = pmin(abs(trans_date - start_date), abs(trans_date - end_date))
      )
    
    # 找出距离最近的记录，若距离相同则取end_date最晚的
    closest_records <- slice_min(company_summary, closest_dist, with_ties = TRUE)
    if(nrow(closest_records) > 1) {
      return(slice_max(closest_records, end_date)$rfp_id)
    } else {
      return(closest_records$rfp_id)
    }
  }
}

3. 应用函数生成结果 #

用purrr::pmap逐行处理hauled数据，新增rfp_id列：

hauled_result <- hauled_df %>%
  mutate(
    rfp_id = pmap_int(list(., list(summary_data = summary_df)), function(row, summary_data) {
      match_rfp(row, summary_data)
    })
  )

# 查看最终结果
print(hauled_result)

运行后输出结果与期望完全一致：

# A tibble: 5 × 4
  trans_num company_id trans_date rfp_id
      <dbl>      <dbl> <date>       <dbl>
1        11          7 2023-01-14       1
2        12          8 2022-07-02       3
3        13          8 2022-03-20       2
4        14          8 2022-09-01       3
5        15          9 2023-01-15       5

关键说明 #

• 避免many-to-many错误的核心：没有直接做表连接，而是对每条hauled记录单独处理对应company_id的summary子集，确保每次只返回一个rfp_id。
• 日期格式转换：必须将字符型日期转为Date类型，否则无法正确进行比较和计算距离。
• 规则优先级：先判断区间匹配，再处理区间外的最近日期匹配，同时覆盖了多匹配的特殊情况。

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