Is there a way to do a merge with one table and if not found with the second table?

user_11643574 0
Viewed 42
df1 <- data.frame(id=c(1,2,3,4,5,8), var=c("a","b","c","d","e","t"), stringsAsFactors = F)
df2 <- data.frame(id=c(1,2,3,4,5,6,7), var=c("e","f","c","d","e","g","h"), stringsAsFactors = F)
df <- data.frame(id=c(1,2,3,4,5,6,7,8))

I need to join to get the var value for df but I would like the var value for df2 rather than df1, and if there is not an equivalent in df2, then I would like to take it from df1. I have this but is there an easier way to do this? and how can I add a column to see where var came from? 

df %>% left_join(df1, by="id") %>% left_join(df2, by="id") %>%
  dplyr::mutate(var=ifelse(!is.na(var.x), var.x, var.y))
dplyr r merge
2 Answers

We can use an SQL triple join like this:

library(sqldf)
sqldf("select a.*, coalesce(b.var, c.var) as var
 from df a
 left join df1 b using(id)
 left join df2 c using(id)")

giving:

  id var
1  1   a
2  2   b
3  3   c
4  4   d
5  5   e
6  6   g
7  7   h
8  8   t

If you need to put it into a pipeline:

df %>%
    { sqldf("select a.*, coalesce(b.var, c.var) as var
     from [.] a
     left join df1 b using(id)
     left join df2 c using(id)") }
user_5165480

Use bind_rows on df1 and df2 first and you can see where var came from if the argument .id is set.

library(dplyr)

bind_rows(df1 = df1, df2 = df2, .id = "from") %>% 
  distinct(id, .keep_all = T) %>%
  right_join(df)

#   from id var
# 1  df1  1   a
# 2  df1  2   b
# 3  df1  3   c
# 4  df1  4   d
# 5  df1  5   e
# 6  df2  6   g
# 7  df2  7   h
# 8  df1  8   t
user_100689850
Related