r语言 - 如何通过列表列和包含单个值的列连接数据框架

Question

我试图根据数据框列中的值列表中存在的一列中的值将两个数据框连接在一起

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)

最终的数据帧看起来像:

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]))

理想情况下，这将使用dplyr或其他tidyverse包

来完成。

Answer 1

你可以使用

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")

,

# 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]>

Answer 2

或者使用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

Answer 3

单个连接的替代方案:

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