How to join dataframes by a list column and column containing single values

user_8172980 0
Viewed 46

I am trying to join two dataframes together based on values in one column being present in the list of values in the column of the dataframe to join with

df1 <- tibble(Group = c("Group_1", "Group_2", "Group_3"),
              Members = list(letters[1:3],
                             letters[4:6],
                             letters[7:12]))
df2 <- tibble(Letters = c("a","g","f","b"),
              Value = 1:4)

The final data frame would look like:

df3 <- tibble(Letters = c("a","g","f","b"),
              Value = 1:4,
              Group = c("Group_1", "Group_3", "Group_2", "Group_1"),
              Members = list(letters[1:3],
                             letters[7:12],
                             letters[4:6],
                             letters[1:3]))

Ideally, this would be done using dplyr or another tidyverse package

dplyr tidyverse r
3 Answers

You could use

library(dplyr)
library(tidyr)

df1 %>% 
  unnest(Members) %>% 
  rename(Letters = Members) %>% 
#  left_join(df2, by = "Letters") %>% 
#  drop_na() %>% 
  inner_join(df2, by = "Letters") %>% # (c) by akrun
  right_join(df1, by = "Group")

to get

# A tibble: 4 x 4
  Group   Letters Value Members  
  <chr>   <chr>   <int> <list>   
1 Group_1 a           1 <chr [3]>
2 Group_1 b           4 <chr [3]>
3 Group_2 f           3 <chr [3]>
4 Group_3 g           2 <chr [6]>
user_125052510

An alternative with a single join:

library(tidyr)
library(dplyr)
library(purrr)

df1 %>%
  mutate(Letters = map(Members, ~ .x[.x %in% df2$Letters])) %>%
  unnest(Letters) %>%
  left_join(df2)

Joining, by = "Letters"
# A tibble: 4 x 4
  Group   Members   Letters Value
  <chr>   <list>    <chr>   <int>
1 Group_1 <chr [3]> a           1
2 Group_1 <chr [3]> b           4
3 Group_2 <chr [3]> f           3
4 Group_3 <chr [6]> g           2
user_28352610

Or using base R

cbind(df2, df1[sort(unlist(lapply(df1$Members, function(x) match(df2$Letters, x)))),])
  Letters Value   Group          Members
1       a     1 Group_1          a, b, c
2       g     2 Group_1          a, b, c
3       f     3 Group_2          d, e, f
4       b     4 Group_3 g, h, i, j, k, l
user_37322710
Related