Is there a way to efficiently join two tables with keys stored on a list format

user_16177840 0
Viewed 61

Example of code to illustrate my issue here.

table_A <- tibble(A1 = c("a", "a/b"), A2 = seq(1,2))
table_A <- table_A %>% mutate(A1 = str_split(A1, "/"))

table_B <- tibble(A1 = c("a","b"), B1 = c("apple", "bananas"))

#does not work
#left_join(table_A, table_B)

The desired result would be to have a new B1 column with c("apple") and c("apple", "bananas") but obviously this code does not work. In practice the lists range from no key to 5 keys long. Happy to take any suggestions, maybe there is a way to re-frame the data or th problem to solve it elegantly.

tibble dplyr r merge join
3 Answers

We can unnest the list column before doing the join

library(dplyr)
library(tidyr)
table_A %>% 
    unnest(A1) %>%
    left_join(table_B)

-output

# A tibble: 3 x 3
  A1       A2 B1     
  <chr> <int> <chr>  
1 a         1 apple  
2 a         2 apple  
3 b         2 bananas

Or another option is to use separate_rows instead of strsplit and then do the join

table_A %>% 
    separate_rows(A1, sep="/") %>%
    left_join(table_B)
user_37322710

A data.table option

> rev(setDT(table_A)[, .(A1 = unlist(A1)), A2])[setDT(table_B), on = .(A1)]
   A1 A2      B1
1:  a  1   apple
2:  a  2   apple
3:  b  2 bananas
user_121587570

@akrun : thanks for your reply, that's what I was looking for!

To get exactly the expected result under table_C I simply added a chop line to your suggestion:

library(dplyr)
library(tidyr)
table_A <- tibble(A1 = c("a", "a/b"), A2 = seq(1,2))
table_B <- tibble(A1 = c("a","b"), B1 = c("apple", "bananas"))

table_C <- table_A %>% 
    separate_rows(A1, sep="/") %>%
    left_join(table_B) %>% 
    chop(c(A1,B1))

table_C
user_161778400
Related