如何从多个R数据框提取ID列并筛选全时间点受试者ID

问题背景 #

需要从纵向数据中筛选出在产前、3个月、6个月、12个月所有时间点均出现的受试者ID，尝试重命名各时间点ID列后用dplyr::full_join合并时触发参数错误。

原始数据 #

antenatal_pids <- structure (list(subject_id = c("191-5467", "191-6784", 
"191-3457", "191-0987", "191-1245", "191-1945", "191-2834","191-1930","191-2890","191-1232", "191-8326", "191-3000")), 
class = "data.frame", row.names = c(NA, -12L))

df_3m <- structure (list(subject_id = c("191-5467", "191-6784", "191-3457", "191-0987","191-8326", "191-1245", "191-1945", "191-3000", "191-1930"), 
edta_code = c("EDTA45", "EDTA79", "EDTA20", "EDTA66", "EDTA12", "EDTA74", "EDTA01", "EDTA05"), 
ipv = c("0", "3", "2", "2", "1", "2", "0", "4", "1")), 
class = "data.frame", row.names = c(NA, -9L)) 

df_6m <- structure (list(subject_id = c("191-5467", "191-6784", "191-3457", "191-0987", "191-1245", "191-1945", "191-3000", "191-2834", "191-2890", "191-1232"), 
edta_code = c("EDTA45", "EDTA79", "EDTA20", "EDTA66", "EDTA12", "EDTA74", "EDTA01","EDTA02","EDTA02"), 
epds = c("1", "4", "5", "1", "2", "3", "5", "2", "3", "5")), class = "data.frame", row.names = c(NA, -10L))

df_12 <- structure (list(subject_id = c("191-5467", "191-6784", "191-3457", "191-0987", "191-1245", "191-1945", "191-3000", "191-1930"), 
edta_code = c("EDTA45", "EDTA79", "EDTA20", "EDTA66", "EDTA12", "EDTA74", "EDTA01"), 
epds = c("1", "4", "5", "1", "2", "3", "5", "2","1","2")), 
class = "data.frame", row.names = c(NA, -8L))

错误代码及报错信息 #

尝试的代码 #

ids_3m <- df_3m %>% select(subject_id) %>% 
  rename ("subject_id_3m" = "subject_id")

ids_6m <- df_6m %>% select(subject_id) %>% 
  rename ("subject_id_6m" = "subject_id")

ids_12m <- df_12m %>% select(subject_id) %>%
rename ("subject_id_12m" = "subject_id")

all_ids <- full_join(antenatal_pids, ids_3m, ids_6m, ids_12m)

错误信息 #

Error in `full_join()`:
! `by` must be a (named) character vector, list, `join_by()` result, or NULL, not a
  <tbl_df/tbl/data.frame> object.
Backtrace:
 1. dplyr::full_join(antenatal_pids, ids_3m, ids_6m, ids_12m)
 2. dplyr:::full_join.data.frame(antenatal_pids, ids_3m, ids_6m, ids_12m)

错误原因分析 #

1. full_join参数限制：dplyr的full_join()每次仅支持合并两个数据框，无法一次性传入多个数据框作为参数。
2. 列名重命名逻辑错误：重命名后各数据框的ID列名不一致，失去了关联合并的依据。
3. 笔误问题：代码中ids_12m <- df_12m应为df_12（原始数据对象名为df_12而非df_12m）。

解决方案 #

方案1：直接计算ID交集（更高效） #

既然目标是筛选所有时间点都存在的ID，直接提取各数据集的ID向量后计算交集即可，无需合并数据框：

library(dplyr)

# 提取各时间点的subject_id向量
antenatal_ids <- antenatal_pids$subject_id
ids_3m <- df_3m$subject_id
ids_6m <- df_6m$subject_id
ids_12m <- df_12$subject_id

# 计算所有向量的交集
common_ids <- intersect(antenatal_ids, intersect(ids_3m, intersect(ids_6m, ids_12m)))

# 转换为数据框输出
common_ids_df <- data.frame(subject_id = common_ids)
print(common_ids_df)

输出结果：

subject_id
1   191-5467
2   191-6784
3   191-3457
4   191-0987
5   191-1245
6   191-1945
7   191-3000

方案2：正确使用多步join合并 #

如果需要保留各时间点的关联信息，可通过多步inner_join（仅保留匹配的ID）实现，无需重命名ID列：

library(dplyr)

# 逐步内连接，仅保留所有时间点都存在的ID
common_data <- antenatal_pids %>%
  inner_join(df_3m, by = "subject_id") %>%
  inner_join(df_6m, by = "subject_id", suffix = c("_3m", "_6m")) %>%
  inner_join(df_12, by = "subject_id", suffix = c("_6m", "_12m"))

# 提取唯一的subject_id
common_ids_df <- common_data %>% select(subject_id) %>% distinct()
print(common_ids_df)

该方案不仅能得到共同ID，还能保留各时间点的其他变量数据。

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