How do I join repeatedly the tables in r?

Question

I would like to join repeatedly between two tables. Here is the table.

structure(list(key = structure(1:4, .Label = c("A", "B", "C", "D"), class = "factor")), class = "data.frame", row.names = c(NA, -4L))

structure(list(key = structure(c(1L, 2L, 2L, 3L), .Label = c("A", "B", "C"), class = "factor"), source = structure(c(1L, 1L, 2L, 2L), .Label = c("a", "b"), class = "factor"), value = c(1L, 1L, 2L, 2L)), class = "data.frame", row.names = c(NA, -4L))

<joined>
 key
   A
   B
   C
   D

<joining> 
key source value
 A      a     1
 B      a     1
 B      b     2
 C      b     2

If I use left_join function like left_join(joined, joining, by = "key"), the results is here.

  key source value
1   A      a     1
2   B      a     1
3   B      b     2
4   C      b     2
5   D   <NA>    NA

However, I want to join grouping by "source". My expected results are here.

joining_a <- joining %>%
  filter(source == "a")

joining_b <- joining %>%
  filter(source == "b")

left_join(joined, joining_a, by = "key")
left_join(joined, joining_b, by = "key")

bind_rows(left_join(joined, joining_a, by = "key"), left_join(joined, joining_b, by = "key"))

  key source value
1   A      a     1
2   B      a     1
3   C   <NA>    NA
4   D   <NA>    NA
5   A   <NA>    NA
6   B      b     2
7   C      b     2
8   D   <NA>    NA

How do I join the tables not dividing these tables?

I think you can place it in a list and use map list(joining, joining) %>% map_dfr(~ left_join(joined, .x, by = "key")) — akrun

akrun akrun · Accepted Answer · 2019-07-17T02:25:32

We can group_split(or split from base R) the 'joining' into a list and then do the left_join with 'joined' using map

library(tidyverse)
joining %>% 
    group_split(source) %>%
    map_dfr(~ left_join(joined, .x, by = 'key'))
#  key source value
#1   A      a     1
#2   B      a     1
#3   C   <NA>    NA
#4   D   <NA>    NA
#5   A   <NA>    NA
#6   B      b     2
#7   C      b     2
#8   D   <NA>    NA

Or without a lambda function

joining %>%
    group_split(source) %>%
    map_dfr(left_join, x = joined, by = 'key')

data

joined <- structure(list(key = structure(1:4, .Label = c("A", "B", "C", 
"D"), class = "factor")), class = "data.frame", row.names = c(NA, 
-4L))

joining <- structure(list(key = structure(c(1L, 2L, 2L, 3L), 
.Label = c("A", 
"B", "C"), class = "factor"), source = structure(c(1L, 1L, 2L, 
2L), .Label = c("a", "b"), class = "factor"), value = c(1L, 1L, 
2L, 2L)), class = "data.frame", row.names = c(NA, -4L))

How do I join repeatedly the tables in r?

1 Answers

data