Replace missing values (NA) in one data set with values from another where columns match

I would do this:

library(data.table)
setDT(DF1); setDT(DF2)

DF1[DF2, x := ifelse(is.na(x), i.x, x), on=c("y","z")]

which gives

     x y z
1: 153 a 1
2: 163 b 1
3: 184 d 1
4: 123 a 2
5: 145 e 2
6: 176 c 2
7: 124 b 1
8: 199 a 2

Comments. This approach isn't so great, since it merges the whole of DF1, while we only need to merge the subset where is.na(x). Here, the improvement looks like (thanks, @Arun):

DF1[is.na(x), x := DF2[.SD, x, on=c("y", "z")]]

This way is analogous to @RHertel's answer.


Here's an alternative with base R:

df1[is.na(df1$x),"x"] <- merge(df2,df1[is.na(df1$x),][,c("y","z")])$x
> df1
#    x y z
#1 153 a 1
#2 163 b 1
#3 124 b 1
#4 123 a 2
#5 145 e 2
#6 176 c 2
#7 184 d 1
#8 199 a 2

Tags:

R

Plyr