基于两个DataFrame多列比较新增列的R语言实现问题

需求说明 #

在df1中新增一列RTF，取值规则：

• 当df1的RT等于df2的RT 或 df1的RT等于df2的MRT，且df1的Date大于等于对应df2的Date时，取df1的RTa值
• 否则取df1的RT值

可复现数据 #

# 构建df1
df1 <- data.frame(
  Date = as.Date(c("2024-01-01", "2024-02-01", "2024-03-01", "2024-04-01", "2024-05-01", "2024-06-01", "2024-07-01")),
  RT = c("150.5-40", "150.5-40", "150.5-40", "150.5-40", "150.5-140", "150.5-80", "150.5-80"),
  RTa = c("150.5-40a", "150.5-40a", "150.5-40a", "150.5-40a", "150.5-140a", "150.5-80a", "150.5-80a")
)

# 构建df2
df2 <- data.frame(
  Date = as.Date(c("2024-03-01", "2024-04-01")),
  RT = c("150.5-40", "150.5-10"),
  MRT = c("150.5-140", "150.5-110")
)

之前尝试的问题分析 #

1. 直接使用df2$RT/df2$MRT进行向量匹配时，会因为df1和df2行数不一致，触发R的循环补齐机制，导致条件判断逻辑混乱
2. 循环代码中错误使用了df（R内置函数）而非目标数据框df1，且逻辑未覆盖完整条件
3. case_when的条件逻辑不仅存在循环补齐问题，还颠倒了取值规则（满足条件时取了RT而非RTa）

解决方案 #

方法1：逐行判断（适合逻辑验证） #

通过rowwise()让df1逐行与df2的所有行进行条件匹配，只要有一行满足规则就取RTa：

library(dplyr)

df1_result <- df1 %>%
  rowwise() %>%
  mutate(
    RTF = if_else(
      any((RT == df2$RT | RT == df2$MRT) & Date >= df2$Date),
      RTa,
      RT
    )
  ) %>%
  ungroup()

# 查看结果
print(df1_result)

方法2：连接式判断（高效适配8万行数据） #

先将df2的规则整理为长格式，再通过连接匹配条件，避免逐行循环的性能损耗：

library(dplyr)
library(tidyr)

# 将df2的RT和MRT整理为长格式，统一匹配规则
df2_rules <- df2 %>%
  pivot_longer(cols = c(RT, MRT), names_to = "type", values_to = "match_RT") %>%
  select(Date, match_RT)

# 左连接后判断是否满足条件，生成RTF列
df1_result <- df1 %>%
  left_join(df2_rules, by = c("RT" = "match_RT")) %>%
  group_by(Date.x, RT, RTa) %>%
  mutate(
    meets_condition = any(Date.x >= Date.y, na.rm = TRUE)
  ) %>%
  ungroup() %>%
  mutate(
    RTF = if_else(meets_condition, RTa, RT)
  ) %>%
  select(Date = Date.x, RT, RTa, RTF)

# 查看结果
print(df1_result)

两种方法都能得到预期结果：

#         Date        RT        RTa       RTF
# 1 2024-01-01  150.5-40  150.5-40a  150.5-40
# 2 2024-02-01  150.5-40  150.5-40a  150.5-40
# 3 2024-03-01  150.5-40  150.5-40a 150.5-40a
# 4 2024-04-01  150.5-40  150.5-40a 150.5-40a
# 5 2024-05-01 150.5-140 150.5-140a 150.5-140a
# 6 2024-06-01  150.5-80  150.5-80a  150.5-80
# 7 2024-07-01  150.5-80  150.5-80a  150.5-80

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