Replace NAs in one DF with values from another DF-CodePudding

I have a df with some missing values:

df <- data.frame(key = c('ID1','ID2','ID3','ID4','ID5'),
             a = c(7,3,5,7,5),
             b = c(1,7,5,7,4),
             c = c(1,NA,NA,4,1),
             d = c(8,NA,NA,7,6))

I created a second df with the values that I'd like to insert into the gap:

to_insert <- data.frame(key = c('ID2','ID3'),
                    c = c(4,5),
                    d = c(1,1))

How would I make the merge? If I try a full outer join, I get duplicate columns:

merge(df,to_insert,by="key",all=TRUE)

Gives this:

  key a b c.x d.x c.y d.y
1 ID1 7 1   1   8  NA  NA
2 ID2 3 7  NA  NA   4   1
3 ID3 5 5  NA  NA   5   1
4 ID4 7 7   4   7  NA  NA
5 ID5 5 4   1   6  NA  NA

When I would like this:

  key a  b  c  d 
1 ID1 7  1  1  8 
2 ID2 3  7  4  1 
3 ID3 5  5  5  1
4 ID4 7  7  4  7
5 ID5 5  4  1  6

CodePudding user response：

You rather want to replace than to merge. Try using match.

df[match(to_insert$key, df$key), match(names(to_insert), names(df))] <- to_insert
df
#   key a b c d
# 1 ID1 7 1 1 8
# 2 ID2 3 7 4 1
# 3 ID3 5 5 5 1
# 4 ID4 7 7 4 7
# 5 ID5 5 4 1 6