Joining 3 dataframes, in which duplicate entries can be present

Admin 1
Viewed 55

I have a data frame like so...

df = tibble(id = c(64512, 64513, 64514, 64515), 
            customer=c("a", "a", "b", "b"))

and want to join two further data frames by id like these...

uvp_new = tibble(id=c(64512, 64513, 64514), uvp=c(12, 14, 16))

uvp_old = tibble(id=c(64512, 64515), uvp=c(10, 18))

with the following logic: whenever there is an entry for a uvp in uvp_new, i want to take this one (ignoring uvp_old), if there is no entry for uvp in uvp_new, i want to take the entry for uvp from uvp_old.

Any help appreciated

dplyr r merge
4 Answers

You can left_join() uvp_old and then use rows_update() with uvp_new:

library(dplyr)

df %>%
  left_join(uvp_old, by = "id") %>%
  rows_update(uvp_new, by = "id")

# A tibble: 4 x 3
     id customer   uvp
  <dbl> <chr>    <dbl>
1 64512 a           12
2 64513 a           14
3 64514 b           16
4 64515 b           18

Or it might be safer if there are duplicated ids in df to use rows_upsert() first and join the result to df:

uvp_old %>%
  rows_upsert(uvp_new, by = "id") %>%
  right_join(df, by = "id")
user_28352610

Here is a base R option using transform + merge

transform(
  merge(merge(df, uvp_new, by = "id", all.x = TRUE), uvp_old, by = "id", all.x = TRUE),
  uvp = ifelse(is.na(uvp.x), uvp.y, uvp.x)
)[c("id","customer","uvp")]

which gives

     id customer uvp
1 64512        a  12
2 64513        a  14
3 64514        b  16
4 64515        b  18
user_121587570

You can join the three together using two joins, keeping track of which data.frame the uvp column came from with suffixes. Then, you can select the first non-NA one with coalesce.

df %>% 
  left_join(uvp_new, by = "id") %>% 
  left_join(uvp_old, by = "id", suffix = c("_new", "_old")) %>% 
  mutate(uvp = coalesce(uvp_new, uvp_old))

#      id customer uvp_new uvp_old   uvp
#   <dbl> <chr>      <dbl>   <dbl> <dbl>
# 1 64512 a             12      10    12
# 2 64513 a             14      NA    14
# 3 64514 b             16      NA    16
# 4 64515 b             NA      18    18
user_10622250

You can do a full join between uvp_new and uvp_old to have all the id's in one dataframe and then join this combined dataframe with df and select the new uvp value if present or else the old one using coalesce.

library(dplyr)

uvp_new %>%
  rename(uvp_n = uvp) %>%
  full_join(uvp_old %>%
  rename(uvp_o = uvp), by = 'id') %>%
  right_join(df, by = 'id') %>%
  mutate(uvp = coalesce(uvp_n, uvp_o))

#     id uvp_n uvp_o customer   uvp
#  <dbl> <dbl> <dbl> <chr>    <dbl>
#1 64512    12    10 a           12
#2 64513    14    NA a           14
#3 64514    16    NA b           16
#4 64515    NA    18 b           18

You can remove uvp_n and uvp_o columns if they are not needed.

user_39629140
Related