r - fill in NA values for multiple columns in 1 df based on values from another df

Question

I have 2 dfs. There are NA values for 2 variables in 1 data frame that I want to replace with values in another df. Here is my sample data:

df1
id    Sex    Race     Income
1     M      White      1
2     NA     Hispanic   2
3     NA     NA         3

df2
id    Sex    Race
1     M      White
2     F      Hispanic
3     M      White
4     F      Black

I want the data to look like this where the NA values for df1 for sex and race are filled in by the values for df2.

df2
id    Sex    Race      Income
1     M      White       1
2     F      Hispanic    2
3     M      White       3
4     F      Black       NA

Can someone please help?

标签： rdplyr

Answer 1

We can use a join here

library(data.table)
setDT(df2)[df1, Income := Income, on = .(id)]

-output

df2
#   id Sex     Race Income
#1:  1   M    White      1
#2:  2   F Hispanic      2
#3:  3   M    White      3
#4:  4   F    Black     NA

---

If we need to choose the 'Sex', 'Race' between the non-NA elements

nm1 <- names(df2)[-1]
setDT(df2)[df1, c(nm1, 'Income') := c(Map(fcoalesce, 
  .SD[, nm1, with = FALSE], mget(paste0('i.', nm1))), list(Income)), on = .(id)]

-output

df2
#   id Sex     Race Income
#1:  1   M    White      1
#2:  2   F Hispanic      2
#3:  3   M    White      3
#4:  4   F    Black     NA

---

Or using tidyverse, with just dplyr functions

library(dplyr)
left_join(df2, df1, by = 'id') %>% 
  transmute(id,  Sex = coalesce(Sex.x, Sex.y),
                Race = coalesce(Race.x, Race.y),
           Income)

-output

#  id Sex     Race Income
#1  1   M    White      1
#2  2   F Hispanic      2
#3  3   M    White      3
#4  4   F    Black     NA

data

df1 <- structure(list(id = 1:3, Sex = c("M", NA, NA), Race = c("White", 
"Hispanic", NA), Income = 1:3), class = "data.frame", row.names = c(NA, 
-3L))

df2 <- structure(list(id = 1:4, Sex = c("M", "F", "M", "F"), Race = c("White", 
"Hispanic", "White", "Black")), class = "data.frame", row.names = c(NA, 
-4L))