CODEWITHSUNDEEP
rjoin
sbac
sbac

Reputation: 2081

How to join 3 tables with incomplete values in R

I want to join df1 and df2 to get df_me. As I couldn't get the result, I tried to use also df_p as a star scheme, but I couldn't get the result that I want.

library(tidyverse)

df1 <- tibble(c = c('b','c','d'),
              x = c(1, 2, 3),
              z = c(10, 11, 12))

df2 <- tibble(c = c('a','b','d'),
              y = c(4,5,6),
              z = c(20, 10, 12))

df_p <- tibble(c = c('a','b','c','d'),
               z = c(20, 10, 11, 12))

# This is the result that I want

df_me <- tibble(c = c('a','b','c','d'),
                x = c(NA, 1, 2, 3),
                y = c(4, 5, NA, 6),
                z = c(20, 10, 11, 12))

# This is (part of) what I tried without success

df_left2 <- left_join(df_p, df1, by = 'c')
df_left3 <- left_join(df_p, df2, by = 'c')
df_left4 <- left_join(df_left2, df_left3, by = 'c')

df_left4 %>% arrange(c)
#> # A tibble: 4 x 7
#>   c     z.x.x     x z.y.x z.x.y     y z.y.y
#>   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 a        20    NA    NA    20     4    20
#> 2 b        10     1    10    10     5    10
#> 3 c        11     2    11    11    NA    NA
#> 4 d        12     3    12    12     6    12
Created on 2021-06-11 by the reprex package (v2.0.0)

Upvotes: 0

Views: 30

Answers (1)

AnilGoyal
AnilGoyal

Reputation: 26238

why not?

merge(df1, df2, by = c('c', 'z'), all = T)

  c  z  x  y
1 a 20 NA  4
2 b 10  1  5
3 c 11  2 NA
4 d 12  3  6

Or in dplyr?

df1 %>% full_join(df2, by = c('c', 'z'))

Upvotes: 1

Related Questions