R left_join() replacing joined values rather than adding in new columns-CodePudding

I have the following dataframes:

    A<-data.frame(AgentNo=c(1,2,3,4,5,6),
                  N=c(2,5,6,1,9,0),
                  Rarity=c(1,2,1,1,2,2))
    
      AgentNo N Rarity
    1       1 2      1
    2       2 5      2
    3       3 6      1
    4       4 1      1
    5       5 9      2
    6       6 0      2
        
    B<-data.frame(Rank=c(1,5),
                  AgentNo.x=c(2,5),
                  AgentNo.y=c(1,4),
                  N=c(3,1), 
                  Rarity=c(1,2))
    
      Rank AgentNo.x AgentNo.y N Rarity
    1    1         2         1 3      1
    2    5         5         4 1      2

I would like to left join B onto A by columns "AgentNo"="AgentNo.y" and "N"="N" but rather than add new columns to A from B I want the same columns from A but where joined values have been updated and taken from B.

For any joined rows I want A.AgentNo to now be B.AgentNo.x, A.N to be B.N and A.Rarity to be B.Rarity. I would like to drop B.Rank and B.Agent.y completely.

The result should be:

Result<-data.frame(AgentNo=c(2,2,3,5,5,6), N=c(2,5,6,1,9,0), Rarity=c(1,2,1,1,2,2))

  AgentNo N Rarity
1       2 3      1
2       2 5      2
3       3 6      1
4       5 1      2
5       5 9      2
6       6 0      2

CodePudding user response：

After some data wrangling, you can use rows_update to update the rows of A by the values of B:

library(dplyr)
A <- A %>% 
  mutate(AgentNo.y = AgentNo)
B <- select(B, AgentNo = AgentNo.x, AgentNo.y, N, Rarity)

rows_update(A, B, by = "AgentNo.y") %>% 
  select(-AgentNo.y)

output

  AgentNo N Rarity
1       2 3      1
2       2 5      2
3       3 6      1
4       5 1      1
5       5 9      2
6       6 0      2