r: How to merge groups from two seperate dataframes only if they have the same contents

user_13361198 0
Viewed 78

Consider the example (my actual datasets are larger) where I have two datasets: one where people are contained in groups and another where people are contained in houses.

Dataset 1:

Group   Person 
Group1  Andy   
Group2  Andy
Group2  Richard
Group3  Richard
Group4  Andy
Group4  Richard
Group4  Meg

Dataset 2:

House  Person
HouseA Andy
HouseA Richard
HouseB Andy
HouseB Richard
HouseB Meg

From this example, one can see that Group 2 and House A both contain Andy and Richard. Group 4 and House B both contain Andy, Richard and Meg. My desired output is:

Group  House  Person
Group2 HouseA Andy
Group2 HouseA Richard
Group4 HouseB Andy
Group4 HouseB Richard
Group4 HouseB Meg

Reproducible data:

df1 <- structure(list(Group = c("Group1", "Group2", "Group2", "Group3", 
"Group4", "Group4", "Group4"), Names = c("Andy", "Andy", "Richard", 
"Richard", "Andy", "Richard", "Meg")), class = "data.frame", row.names = c(NA, 
-7L))

df2 <- structure(list(House = c("HouseA", "HouseA", "HouseB", "HouseB", 
"HouseB"), Names = c("Andy", "Richard", "Andy", "Richard", "Meg"
)), class = "data.frame", row.names = c(NA, -5L))
dataframe r
4 Answers

Alternative approach with data.table + digest. Hopefully it's readable:

library(digest)
library(data.table)
setDT(df1)
setDT(df2)

out <- merge(
  df1[, .(People = list(sort(Names)), hash = digest(sort(Names))), by = Group],
  df2[, .(hash = digest(sort(Names))), by = House],
  by = "hash")

out[, .(Person = unlist(People)), by = .(Group, House)]

Which produces:

   Group  House  Person
1: Group2 HouseA    Andy
2: Group2 HouseA Richard
3: Group4 HouseB    Andy
4: Group4 HouseB     Meg
5: Group4 HouseB Richard
user_17969140

A solution with dplyr

library(dplyr)
merge (
  df1 %>% group_by(Group) %>% mutate(nGroup = n()),
  df2 %>% group_by(House) %>% mutate(nHouse = n())) %>% 
  filter(nGroup == nHouse) %>% 
  arrange(Group, House) %>% 
  select(Group, House, Names)

##    Group  House   Names
##1 Group2 HouseA    Andy
##2 Group2 HouseA Richard
##3 Group4 HouseB    Andy
##4 Group4 HouseB     Meg
##5 Group4 HouseB Richard
user_21955550

Here is one base R attempt :

#split df2 on house value
tmp <- split(df2, df2$House)  
#split df1 on Group value 
result <- do.call(rbind, by(df1, df1$Group, function(x) {
  #Check which house and group combination has exact same names
  val <- sapply(tmp, function(y) all(y$Names %in% x$Names) & 
                                 all(x$Names %in% y$Names))
  if(any(val))
    #attach group name and combine the result
    do.call(rbind, Map(cbind, tmp[val], Group = x$Group[1]))
}))
#Remove rownames
rownames(result)  <- NULL
result  

#   House   Names  Group
#1 HouseA    Andy Group2
#2 HouseA Richard Group2
#3 HouseB    Andy Group4
#4 HouseB Richard Group4
#5 HouseB     Meg Group4
user_39629140

One option using tidyr::unnest + subset + aggregate

tidyr::unnest(
  subset(
    aggregate(Names ~ ., df1, function(x) sort(unique(x))),
    Names %in% aggregate(Names ~ ., df2, function(x) sort(unique(x)))$Names
  ),
  cols = "Names"
)

which gives

  Group  Names
  <chr>  <chr>
1 Group2 Andy
2 Group2 Richard
3 Group4 Andy
4 Group4 Meg
5 Group4 Richard
user_121587570
Related