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

Question

I have a data frame (datadf) with 3 columns, 'x', 'y, and z. Several 'x' values are missing (NA). 'y' and 'z' are non measured variables.

x    y z
153  a 1
163  b 1
NA   d 1
123  a 2 
145  e 2
NA   c 2 
NA   b 1
199  a 2

I have another data frame (imputeddf) with the same three columns:

 x  y z
123 a 1
145 a 2
124 b 1
168 b 2
123 c 1
176 c 2
184 d 1
101 d 2

I wish to replace NA in 'x' in 'datadf' with values from 'imputeddf' where 'y' and 'z' matches between the two data sets (each combo of 'y' and 'z' has its own value of 'x' to fill in).

The desired result:

x    y z
153  a 1
163  b 1
184  d 1
123  a 2 
145  e 2
176  c 2 
124  b 1
199  a 2

I am trying things like:

finaldf <- datadf
finaldf$x <- if(datadf[!is.na(datadf$x)]){ddply(datadf, x=imputeddf$x[datadf$y == imputeddf$y & datadf$z == imputeddf$z])}else{datadf$x}

but it's not working.

What is the best way for me to fill in the NA in the using my imputed value df?

Answer 1

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.

---

From @Jakob's comment:

does this work for more than one x variable? If I want to fill up entire datasets with several columns?

You can enumerate the desired columns:

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

The expression could be constructed using lapply and substitute, probably, but if the set of columns is fixed, it might be cleanest just to write it out as above.

Answer 2

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

Answer 3

A dplyr solution, conceptually identical to the answers above. To pull out just the rows of imputeddf that correspond to NAs in datadf, use semi_join. Then, use another join to match back to datadf. (This step is not very clean, unfortunately.)

library(dplyr)
replacement_rows <- imputeddf %>%
  semi_join(datadf %>% filter(is.na(x)), by = c("y", "z"))
datadf <- datadf %>%
  left_join(replacement_rows, by = c("y", "z")) %>%
  mutate(x = if_else(is.na(x.x), x.y, x.x)) %>%
  select(x, y, z)

This gets what you want:

> datadf
# A tibble: 8 x 3
      x y         z
  <dbl> <chr> <dbl>
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