CODEWITHSUNDEEP
rdplyrdata.table
Misha
Misha

Reputation: 3126

joins multiple dataframes in r

a1 <- data.frame(id=c(1,1,1,1,2,2,2,3,3),
                var=c("6402","1","1","3","6406","6406","2","1","1"))
b1 <- data.frame(var=c("6402","6406"),
                txt=c("A","B"))
n1 <- data.frame(id=c(1,2,3))


desired <- data.frame(id=c(1,2,3),
                      txt=c("A","B",NA))

How can I join a1,b1 and n1 to generate the desired df?

Upvotes: 2

Views: 66

Answers (3)

akrun
akrun

Reputation: 887851

We can use tidyverse

library(dplyr)
distinct(a1) %>%
   left_join(b1, by = 'var') %>% 
   full_join(n1) %>%
   group_by(id) %>%
   summarise(txt = first(txt))
# A tibble: 3 x 2
#     id txt  
#  <dbl> <chr>
#1     1 A    
#2     2 B    
#3     3 <NA>

Or using data.table

unique(setDT(a1))[b1,  txt := txt, on = .(var)][n1, .SD[1],
      on = .(id), by = .EACHI][, var := NULL][]

Upvotes: 1

ThomasIsCoding
ThomasIsCoding

Reputation: 102625

Here is a base R option using nested merge

merge(n1,
  merge(unique(a1),
    b1,
    by = "var",
    all.y = TRUE
  ),
  by = "id",
  all = TRUE
)[c("id", "txt")]

which gives

  id  txt
1  1    A
2  2    B
3  3 <NA>

Upvotes: 1

Duck
Duck

Reputation: 39613

With next code you can get something similar to desired dataframe:

#Code
merge(n1,merge(a1,b1)[,-1],all.x = T)[!duplicated(merge(n1,merge(a1,b1)[,-1],all.x = T)[,'id']),]

Output:

  id  txt
1  1    A
2  2    B
4  3 <NA>

Upvotes: 2

Related Questions