在R中基于含重复ID的另一数据框日期范围筛选数据框

问题需求 #

从数据框df1中筛选记录，要求每条记录的Collected日期与df2中对应Accession的Collected日期相差±7天以内，同时保留所有符合条件的重复MRN观测。

输入数据 #

数据框df1 #

Name <- c("Doe, John","Doe, John","Doe, John", "Doe, Jane", "Doe, Jane","Doe, Jane","Parker, Peter","Parker, Peter","Parker, Peter", "Stark, Tony","Stark, Tony","Stark, Tony")
Accession <- c(123, 234, 345, 456, 567, 678, 789, 8910, 1023, 1134, 1567, 1769)
MRN <-c(55555, 55555, 55555, 66666, 66666, 66666, 77777, 77777, 77777, 88888, 88888, 88888)
Collected <-c("2022-02-05", "2022-02-06", "2022-01-07", "2022-01-08", "2022-01-09", "2022-01-10", "2022-01-11", "2022-02-12", "2022-01-13", "2022-01-04", "2022-01-15", "2022-01-16")
Result <-c("Detected", "Detected", "Detected", "Detected", "Detected","Detected", "Detected", "Detected", "Detected", "Detected", "Detected", "Detected")

df1 <- data.frame(Name, Accession, MRN, Collected, Result)

数据框df2 #

Name <- c("Doe, John","Doe, John","Doe, John", "Doe, Jane", "Doe, Jane","Doe, Jane","Parker, Peter","Parker, Peter","Parker, Peter", "Stark, Tony","Stark, Tony","Stark, Tony")
Accession <- c(123, 234, 345, 456, 567, 678, 789, 8910, 1023, 1134, 1567, 1769)
MRN <-c(55555, 55555, 55555, 66666, 66666, 66666, 77777, 77777, 77777, 88888, 88888, 88888)
Collected <-c("2022-01-22", "2022-01-20", "2022-01-07", "2022-01-28", "2022-01-12", "2022-01-15", "2022-01-17", "2022-01-31", "2022-01-16", "2022-01-20", "2022-01-25", "2022-01-26")
Result <-c("Presumptive", "Presumptive", "Presumptive", "Presumptive", "Presumptive","Presumptive", "Presumptive", "Presumptive", "Presumptive", "Presumptive", "Presumptive", "Presumptive")

df2 <- data.frame(Name, Accession, MRN, Collected, Result)

实现代码 #

使用dplyr包完成数据转换、合并与筛选：

library(dplyr)

# 转换日期列为日期格式
df1 <- df1 %>% mutate(Collected = as.Date(Collected))
df2 <- df2 %>% mutate(Collected = as.Date(Collected))

# 合并数据并筛选符合日期条件的记录
filtered_df <- df1 %>%
  # 按Accession匹配df2的日期
  inner_join(df2 %>% select(Accession, Collected_df2 = Collected), by = "Accession") %>%
  # 计算日期差的绝对值
  mutate(date_diff = abs(Collected - Collected_df2)) %>%
  # 筛选相差7天以内的记录
  filter(date_diff <= 7) %>%
  # 移除中间辅助列
  select(-Collected_df2, -date_diff)

# 输出结果
print(filtered_df)

输出结果 #

Name Accession   MRN  Collected  Result
1      Doe, John       345 55555 2022-01-07 Detected
2      Doe, Jane       456 66666 2022-01-08 Detected
3      Doe, Jane       567 66666 2022-01-09 Detected
4      Doe, Jane       678 66666 2022-01-10 Detected
5 Parker, Peter       789 77777 2022-01-11 Detected
6 Parker, Peter      1023 77777 2022-01-13 Detected
7    Stark, Tony      1567 88888 2022-01-15 Detected
8    Stark, Tony      1769 88888 2022-01-16 Detected

内容的提问来源于stack exchange，提问作者T.McMillen