使用dplyr实现带条件的Left Join问题求助

我有两个数据框dists和networkDistances，需求是：仅当dists$sameCol == TRUE且ID1、ID2完全匹配时，将networkDistances的列合并到dists中；其余行的对应新列需全部设为NA。但尝试了多种dplyr左连接写法均无效，目前sameCol为FALSE的行也被填入了数据。

数据示例 #

dists数据框： #

label1 label2      dist     sameCol ID1  ID2
193    194       0.7219847      NA  N53 <NA>
193    195       0.5996300   FALSE  N53  N43
193    196       0.2038451   FALSE  N5  N45
194    195       0.2190454      NA <NA>  N43
194    196       0.8894645      NA <NA>  N45
195    196       0.7910169    TRUE  N38  N5

networkDistances数据框： #

ID1  ID2   colony   value    networkDist
N38  N5     10      0.05           1
N36  N5     10      0.03           1
N4   N3     12      10.00          1
N4   N5     12      10.00          1
N4   N15     12     5.00           1
N15  N14     12     5.00           1

期望输出结果 #

label1 label2      dist     sameCol ID1  ID2   colony   value    networkDist
193    194       0.7219847      NA  N53 <NA>   NA        NA            NA
193    195       0.5996300   FALSE  N53  N43   NA        NA            NA
193    196       0.2038451   FALSE  N5   N45   NA        NA            NA
194    195       0.2190454      NA <NA>  N43   NA        NA            NA
194    196       0.8894645      NA <NA>  N45   NA        NA            NA
195    196       0.7910169    TRUE  N38  N5    10       0.05           1

尝试过的无效代码 #

r <- left_join(dists, networkDistances, by = c("ID1" = "ID1", "ID2" = "ID2")) 

r <- left_join(dists, networkDistances, by = c("ID1" = "ID1", "ID2" = "ID2")) %>%
  mutate(networkDist = case_when(sameCol  %in% T ~ networkDist))

r <-dists %>% 
  left_join(networkDistances, by = c("ID1","ID2"))%>%
  mutate(networkDist = case_when(sameCol== T ~ networkDist))

解决方案 #

之前的写法只修改了networkDist一列，未处理colony和value，导致不符合条件的行仍有数据。正确做法是先左连接，再批量将所有来自networkDistances的列在不符合条件时设为NA：

library(dplyr)

# 左连接两个数据框
result <- left_join(dists, networkDistances, by = c("ID1", "ID2"))

# 对合并得到的列进行条件赋值
result <- result %>%
  mutate(across(colony:networkDist, ~ ifelse(sameCol != TRUE, NA, .x)))

或者用case_when实现更清晰的逻辑：

result <- left_join(dists, networkDistances, by = c("ID1", "ID2")) %>%
  mutate(across(colony:networkDist, ~ case_when(sameCol == TRUE ~ .x, TRUE ~ NA)))

这样就能确保只有sameCol == TRUE且ID匹配的行保留合并来的数据，其余行对应列均为NA。

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