0

I would like to conditionally merge two datasets such that the values in dataframe2 replace the values in dataframe1, unless dataframe2 contains missing values. This should be performed in the case of a full join such that rows from both dataframe are preserved.

This question is inspired from Conditional merge/replacement in R (which seems to work only for inner join). 

df1 <- data.frame(x1=1:4,x2=letters[1:4],stringsAsFactors=FALSE)
df2 <- data.frame(x1=2:5,x2=c("zz","qq", NA, "qy"),stringsAsFactors=FALSE)

I would like the following result:

  x1   x2
1  1    a
2  2   zz
3  3   qq
4  4   d
5  5   qy

I tried the following code though it returns NA for the 4th column but I would like the original value to be preserved since in this case df2 contains missing value for 4.

df3 <- anti_join(df1, df2, by = "x1")
rbind(df3, df2)

 x1   x2
1  1    a
2  2   zz
3  3   qq
4  4 <NA>
5  5   qy
sparkh2o
  • 254
  • 1
  • 4
  • 16

1 Answers1

3

It can be done with dplyr.

library(dplyr)

full_join(df1,df2,by = c("x1" = "x1")) %>% 
  transmute(x1 = x1,x2 = coalesce(x2.y,x2.x))

  x1 x2
1  1  a
2  2 zz
3  3 qq
4  4  d
5  5 qy
Wil
  • 3,076
  • 2
  • 12
  • 31