12

I would like to join two data frames. Some of the column names overlap, and there are NA entries in one of the data frame's overlapping columns. Here is a simplified example:

df1 <- data.frame(fruit = c('apples','oranges','bananas','grapes'), var1 = c(1,2,3,4), var2 = c(3,NA,6,NA), stringsAsFactors = FALSE)
df2 <- data.frame(fruit = c('oranges','grapes'), var2=c(5,6), var3=c(7,8), stringsAsFactors = FALSE)

Can I use dplyr join functions to join these data frames and automatically prioritize the non-NA entry so that I get the "var2" column to have no NA entries in the joined data frame? As it is now, if I call left_join, it keeps the NA entries, and if I call full_join it duplicates the rows.

Example Data

> df1
    fruit var1 var2
1  apples    1    3
2 oranges    2   NA
3 bananas    3    6
4  grapes    4   NA
> df2
    fruit var2 var3
1 oranges    5    7
2  grapes    6    8
micstr
  • 5,080
  • 8
  • 48
  • 76
qdread
  • 3,389
  • 19
  • 36

2 Answers2

25

coalesce might be something you need. It fills the NA from the first vector with values from the second vector at corresponding positions:

library(dplyr)
df1 %>% 
        left_join(df2, by = "fruit") %>% 
        mutate(var2 = coalesce(var2.x, var2.y)) %>% 
        select(-var2.x, -var2.y)

#     fruit var1 var3 var2
# 1  apples    1   NA    3
# 2 oranges    2    7    5
# 3 bananas    3   NA    6
# 4  grapes    4    8    6

Or use data.table, which does in-place replacing:

library(data.table)
setDT(df1)[setDT(df2), on = "fruit", `:=` (var2 = i.var2, var3 = i.var3)]
df1
#      fruit var1 var2 var3
# 1:  apples    1    3   NA
# 2: oranges    2    5    7
# 3: bananas    3    6   NA
# 4:  grapes    4    6    8
Psidom
  • 209,562
  • 33
  • 339
  • 356
  • 4
    Thanks for this answer! For the dplyr solution, is there a way for the mutate step to coalesce all columns with the same name from x and y if there are multiple overlapping columns? – qdread Aug 23 '16 at 21:51
  • A more general way without hard coding your columns might be reshaping your data after the merge. Here is an example `df1 %>% left_join(df2, by = "fruit") %>% reshape(varying = 3:4, dir = "long") %>% group_by(fruit, var1, var3) %>% summarise_each(funs(na.omit(.)), -id, -time)` that I can give. – Psidom Aug 23 '16 at 22:46
2

Using purrr along with dplyr might be solution to apply with multiple columns:

library(purrr)
library(dplyr)

df<-left_join(df1,df2,by="fruit")
map2_dfr(df[3],df[4],~ifelse(is.na(.x),.y,.x)) %>% 
bind_cols(df[c(1,2,5)],.)

    fruit var1 var3 var2.x
1  apples    1   NA      3
2 oranges    2    7      5
3 bananas    3   NA      6
4  grapes    4    8      6
José
  • 921
  • 14
  • 21