基于客户ID与申请日期匹配两个数据框的事件ID

嘿，我看你需要把dataframe2里的event_id按照Cust_ID和App_date精准匹配到dataframe里，只填充能对上的记录，其他保持NA对吧？这其实是个典型的左连接匹配场景，给你两种实用的实现方式，都能得到你想要的结果！

首先先确认你的原始数据：

# 原始数据框1
dataframe <- data.frame(
  Cust_ID = c("1","2","2","3","1","3","1","1","2","2"),
  App_date = as.Date(c("2023-05-01","2023-05-02","2023-05-03","2023-05-06","2023-04-30","2023-04-04","2023-05-30","2023-05-31","2023-05-30","2023-05-31")),
  Product = c("AA","AA","BB","AA","CC","BB","AA","AA","CC","BB"),
  event_id = NA
)

# 原始数据框2
dataframe2 <- data.frame(
  Cust_ID = c("1","3","1","3","1","1"),
  App_date = as.Date(c("2023-05-01","2023-05-06","2023-04-30","2023-04-04","2023-05-30","2023-05-31")),
  Product = c("AA","AA","CC","BB","AA","AA"),
  Age = c(50,32,50,32,50,50),
  event_id = c(2,4,2,5,NA,NA)
)

方法一：用dplyr（tidyverse风格） #

如果你习惯用tidyverse工具链，left_join是最直观的选择，步骤清晰：

library(dplyr)

# 1. 先从dataframe2里提取需要匹配的关键列（避免多余列干扰）
match_columns <- dataframe2 %>% select(Cust_ID, App_date, event_id)

# 2. 左连接到dataframe，按Cust_ID和App_date匹配
resultant_data <- dataframe %>%
  left_join(match_columns, by = c("Cust_ID", "App_date")) %>%
  # 3. 替换原有的event_id列（join后会生成event_id.x和event_id.y，我们保留匹配来的y）
  mutate(event_id = event_id.y) %>%
  # 4. 删除多余的中间列
  select(-event_id.x, -event_id.y)

# 查看结果
print(resultant_data)

方法二：用base R原生函数（无需额外包） #

如果不想加载第三方包，用base R的merge函数也能实现：

# 1. 提取dataframe2的匹配关键列
match_df <- dataframe2[, c("Cust_ID", "App_date", "event_id")]

# 2. 执行左连接（all.x=TRUE表示保留dataframe的所有行）
resultant_data_base <- merge(
  dataframe, 
  match_df, 
  by = c("Cust_ID", "App_date"), 
  all.x = TRUE,
  suffixes = c("_original", "_matched")
)

# 3. 替换event_id列并清理多余列
resultant_data_base$event_id <- resultant_data_base$event_id_matched
resultant_data_base <- resultant_data_base[, !names(resultant_data_base) %in% c("event_id_original", "event_id_matched")]

# 查看结果
print(resultant_data_base)

验证结果是否符合预期 #

两种方法得到的结果都和你想要的完全一致，你可以用下面的代码确认：

# 你期望的目标数据框
expected_result <- data.frame(
  Cust_ID = c("1","2","2","3","1","3","1","1","2","2"),
  App_date = as.Date(c("2023-05-01","2023-05-02","2023-05-03","2023-05-06","2023-04-30","2023-04-04","2023-05-30","2023-05-31","2023-05-30","2023-05-31")),
  Product = c("AA","AA","BB","AA","CC","BB","AA","AA","CC","BB"),
  event_id = c(2,NA,NA,4,2,5,NA,NA,NA,NA)
)

# 检查是否完全一致
all.equal(resultant_data, expected_result) # 返回TRUE表示匹配成功

备注：内容来源于stack exchange，提问作者Priyansh