合并到一组数据并替换当前值，如果在更新列中

Question

使用以下数据帧。我想创建一个新数据

df1 <- data.frame(ID = LETTERS[1:10],Quantity = c(1,2,3,4,5,6,7,8,9,10))
df2 <- data.frame(Cur_ID = c("A","C","D","H"), Update_ID = c("A","C","X","Y"), Update_Quantity = c(20, 21, 22, 23))

desired df3:
ID  letters
A   20
B   2
C   21
X   22
E   5
F   6
G   7
Y   23
I   9
J   10

框DF2中的所有内容均为更新，如果update_id与cur_id不同。

Answer 1

使用dplyr，您可以合并df2中df1，并使用coalesce（）更新id id 和数量。

library(dplyr)

df1 %>%
  left_join(df2, by = c("ID" = "Cur_ID")) %>%
  mutate(ID = coalesce(Update_ID, ID),
         Quantity = coalesce(Update_Quantity, Quantity), .keep = "unused")

---

带有PowerJoin的另一个选项：

library(powerjoin)

power_left_join(df1, setNames(df2, c("Cur_ID", "ID", "Quantity")),
                by = c("ID" = "Cur_ID"), conflict = coalesce_yx)

输出

#    ID Quantity
# 1   A       20
# 2   B        2
# 3   C       21
# 4   X       22
# 5   E        5
# 6   F        6
# 7   G        7
# 8   Y       23
# 9   I        9
# 10  J       10

Answer 2

使用data.table JOIN

library(data.table)
setDT(df1)[df2, c('ID', 'Quantity') := .(Update_ID, 
         Update_Quantity), on = .(ID = Cur_ID)]

-OUTPUT

> df1
        ID Quantity
    <char>    <num>
 1:      A       20
 2:      B        2
 3:      C       21
 4:      X       22
 5:      E        5
 6:      F        6
 7:      G        7
 8:      Y       23
 9:      I        9
10:      J       10

Answer 3

采用基本方法：

type.convert(         
  data.frame(
    t(with(df2, apply(df1, 1, \(x) {
      if (x[1] %in% Cur_ID)
      {
        y <- x[1];
        x[1] <- Update_ID[Cur_ID == x[1]]; 
        x[2] <- Update_Quantity[Cur_ID == y]
      }
      x
    })))), 
  as.is = T
)

#>    ID Quantity
#> 1   A       20
#> 2   B        2
#> 3   C       21
#> 4   X       22
#> 5   E        5
#> 6   F        6
#> 7   G        7
#> 8   Y       23
#> 9   I        9
#> 10  J       10