how to merge two dataframes in r with NAs present in common column in it in r

Question

I have a master dataset looks like

A1       B1         V1
1100    10129       45
NA      1012        65
NA      101         47
NA      10132       36
1101    10133       425
36475   NA          74
54757   NA          85
1102    10136       NA

Here the secondary table where I need to update V2 column from V1 using two primary ids (A1 and B1)

A2        B2    V2
1100     10129  
NA       1012   
NA       10132  
36475    NA 
54757    NA 
1102     10136

Excpected output:

    A2       B2     V2
   1100     10129   45
   NA       1012    65
   NA       10132   36
   36475    NA      74
   54757    NA      85
   1102     10136   NA

kindly suggest the code to met the criteria

Thanks in advance

In base R : `merge(df1, df2, by.x = c('A1', 'B1'), by.y = c('A2', 'B2'))` — Ronak Shah, Aug 24 '20 at 05:55

score 0 · Accepted Answer · answered Aug 24 '20 at 05:51

I think you just need to do an inner_join would be suffice.

library(dplyr)

primary_df <- data.frame(
  A1 = c(1100, NA, NA, NA, 1101, 36475, 54757, 1102),
  B1= c(10129, 1012, 101, 10132, 10133, NA, NA, 10136),
  V1 = c(45, 65, 47, 36, 425, 74, 85, NA)
)

secondary_df <- data.frame(
  A2 = c(1100, NA, NA, 36475, 54757, 1102),
  B2 = c(10129, 1012, 10132, NA, NA, 10136)
)

inner_join(primary_df, secondary_df, by=c("A1"="A2", "B1"="B2"))

Output

> inner_join(primary_df, secondary_df, by=c("A1"="A2", "B1"="B2"))
     A1    B1 V1
1  1100 10129 45
2    NA  1012 65
3    NA 10132 36
4 36475    NA 74
5 54757    NA 85
6  1102 10136 NA

how to merge two dataframes in r with NAs present in common column in it in r

1 Answers1