R语言：如何在保留其余行与列顺序的前提下合并指定行

需求说明 #

在保留所有行和列顺序的前提下，用另一数据集的部分行数据填充主数据集中的缺失值。

现有数据 #

主数据集df1：

df1 <- data.frame(by1=c(1,3,5,7,9),
                 by2=c(6:10),
                 x=c("a",NA_character_,NA_character_,NA_character_,"e"),
                 y=c("q","w","e","r","t"),stringsAsFactors = F)
df1
#   by1 by2    x y
# 1   1   6    a q
# 2   3   7 <NA> w
# 3   5   8 <NA> e
# 4   7   9 <NA> r
# 5   9  10    e t

补充数据集df2：

df2 <- data.frame(by1=c(3,5),x=c("b","c"),stringsAsFactors = F)
df2
#   by1 x
# 1   3 b
# 2   5 c

预期输出 #

expected <- data.frame(by1=c(1,3,5,7,9),
                       by2=c(6:10),
                       x=c("a","b","c",NA_character_,"e"),
                       y=c("q","w","e","r","t"),stringsAsFactors = F )
expected
#   by1 by2    x y
# 1   1   6    a q
# 2   3   7    b w
# 3   5   8    c e
# 4   7   9 <NA> r
# 5   9  10    e t

当前实现代码（非最优写法） #

df1%>%
  merge(df2%>%rename(x2=x),by="by1",all.x=T)%>%
  mutate(x=coalesce(x,x2))%>%
  select(-x2)

更简洁的优化方案 #

方案1：left_join + coalesce 简化版 #

无需额外重命名列，通过后缀区分后直接合并填充：

library(dplyr)

df1 %>%
  left_join(df2, by = "by1", suffix = c("", ".y")) %>%
  mutate(x = coalesce(x, x.y)) %>%
  select(-x.y)

方案2：rows_update（dplyr 1.0.0及以上版本） #

这是最直观简洁的方式，直接匹配by1列，用df2的x值更新df1对应行的缺失值，自动保留原有行列顺序：

library(dplyr)

df1 %>%
  rows_update(df2, by = "by1", unmatched = "keep")

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