Combine groups within dataframe if they share at least one common item

user_13361198 0
Viewed 368

In my dataframe, I have a 'Groups' column and a 'Person' column. I want to join groups together if they share at least one common person. Consider the following example data:

Group Person
1     David
1     Sarah
1     John
2     Brian
2     Andrew
3     David
3     Charlie
4     Clare
4     Greg
5     Greg
5     Clare
5     Alan

In this example, Group 1 and Group 3 share a common person - David. The people in Group 2 do not overlap with the people in any other group. Group 4 and Group 5 share two common people Clare and Greg.

My desired output would be as follows:

Group Person
1     David
1     Sarah
1     John
1     Charlie
2     Brian
2     Andrew
3     Clare
3     Greg
3     Alan

Reproducible data:

structure(list(Group = c(1, 1, 1, 2, 2, 3, 3, 4, 5, 5), Person = c("David", 
"Sarah", "John", "Brian", "Andrew", "David", "Charlie", "Clare", 
"Greg", "Clare")), class = c("spec_tbl_df", "tbl_df", "tbl", 
"data.frame"), row.names = c(NA, -10L), spec = structure(list(
cols = list(Group = structure(list(), class = c("collector_double", 
"collector")), Person = structure(list(), class = c("collector_character", 
"collector"))), default = structure(list(), class = c("collector_guess", 
"collector")), skip = 1), class = "col_spec"))
dataframe r
2 Answers

Using igraph cluster membership:

library(igraph)

#convert to graph object
g <- graph_from_data_frame(df1)

#get cluster memberships
x <- clusters(g)$membership
x
# 1       2       3       4       5   David   Sarah    John   Brian  Andrew Charlie   Clare    Greg 
# 1       2       1       3       3       1       1       1       2       2       1       3       3 

# assign membership back to dataframe
df1$membership <- x[ df1$Person ]
df1
#    Group  Person membership
# 1      1   David          1
# 2      1   Sarah          1
# 3      1    John          1
# 4      2   Brian          2
# 5      2  Andrew          2
# 6      3   David          1
# 7      3 Charlie          1
# 8      4   Clare          3
# 9      5    Greg          3
# 10     5   Clare          3

We can use unique to avoid duplicated rows, and sort:

unique(df1[order(df1$membership), -1 ])
#    Person membership
# 1   David          1
# 2   Sarah          1
# 3    John          1
# 7 Charlie          1
# 4   Brian          2
# 5  Andrew          2
# 8   Clare          3
# 9    Greg          3
user_6800680

This first groups by Person and gets their list of Groups grouped_groups. Then it groups by Group and creates a new character variable, new_grouping which is the union of each list of groups within each Group. All using tidyverse verbs.

DF %>% 
  group_by(Person) %>% 
  mutate(grouped_groups = list(Group)) %>%
  group_by(Group) %>%
  mutate(new_grouping = paste0(list(sort(reduce(groups, union))), collapse = "-"))
user_30749470
Related