如何用另一数据框的3列过滤R数据框，保留最新状态行？

我有两个R语言数据框：df记录了临床试验对象的多条状态信息，df2仅存储每个对象在对应试验中的最新状态（通过UserNumber、Protocol、Status唯一标识）。需要过滤df，仅保留与df2中UserNumber、Protocol、Status完全匹配的行——由于部分用户参与多个试验且UserNumber可能重复，必须通过这三列联合匹配。

数据结构如下：

df<-structure(list(Name = c("JoeBob", "JoeBob", "JoeBob", "JoeBob", "JoeBob", "AliC", "AliC", "AliC", "AliC"), UserNumber = c(1, 1, 1, 4, 4, 2, 53, 53, 53), Protocol = c("LS-P-Tylenol", "LS-P-Tylenol", "LS-P-Tylenol", "AdvilScience", "AdvilScience", "LS-P-Tylenol", "CancerDrug", "CancerDrug", "CancerDrug"), Status = c("On Study", "Consented", "Eligible", "Consented", "Eligible", "Off Study", "On Study", "Eligible", "Consented")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -9L))

以及

df2<-structure(list(UserNumber = c(1, 4, 2, 53), Protocol = c("LS-P-Tylenol", "AdvilScience", "LS-P-Tylenol", "CancerDrug"), Status = c("On Study", "Consented", "Off Study", "On Study")), row.names = c(NA, -4L), class = c("tbl_df", "tbl", "data.frame"))

这里提供两种常用实现方式，分别适配base R原生环境和tidyverse工作流：

方法1：Base R 原生实现 #

使用merge()函数做内连接，指定三列作为匹配键，自动筛选出完全匹配的行：

# 内连接，仅保留三列同时匹配的记录
result_base <- merge(df, df2, by = c("UserNumber", "Protocol", "Status"))
print(result_base)

运行结果：

UserNumber       Protocol     Status   Name
1           1 LS-P-Tylenol   On Study JoeBob
2           2 LS-P-Tylenol  Off Study   AliC
3           4 AdvilScience  Consented JoeBob
4          53    CancerDrug   On Study   AliC

方法2：Tidyverse（dplyr）实现 #

使用semi_join()函数更精准地过滤——它只保留df中存在于df2的匹配行，不会额外添加df2的列，适合单纯的筛选需求：

library(dplyr)

result_tidy <- df %>%
  semi_join(df2, by = c("UserNumber", "Protocol", "Status"))

print(result_tidy)

运行结果：

# A tibble: 4 × 4
  Name   UserNumber Protocol       Status   
  <chr>       <dbl> <chr>          <chr>    
1 JoeBob          1 LS-P-Tylenol   On Study 
2 JoeBob          4 AdvilScience   Consented
3 AliC            2 LS-P-Tylenol   Off Study
4 AliC           53 CancerDrug     On Study 

• 两种方法均通过UserNumber、Protocol、Status三列联合匹配，避免了单一UserNumber重复导致的错误匹配
• merge()是base R原生函数，无需额外安装包；semi_join()属于dplyr，代码风格更简洁易读，适配tidyverse工作流

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