Retrieve data that has the same factor in column B for both factors in column A and finding which assets that male does not share with female

user_8140973 0
Viewed 35

In column A, there are two factors, male and female. In column B, there are 5 assets, a to e.

df <- data.frame(ID = c(1:7),                                   
                 gender = c("male","male", "male", "female", "female","female","female"),               
                 assets = c("a,e","a,b,e,d", "b,c,e","b,c,e", "a,b,e,d", "c,d","a,d"))

How do I retrieve data where both male and female shared the same asset combinations?

I've zero clue how to write the R syntax for it and below is what I have tried

sameassets <- df %>% filter(filter(gender="male",assets) == filter(gender="female",assets))

desired output

sameassests <- data.frame(ID = c(2,5,3,4),                                   
                          gender = c("male", "female", "male", "female"),               
                          assets = c("a,b,e,d", "a,b,e,d", "b,c,e","b,c,e"))

Can someone help please?

Edit to include additional question. I also want to find out which asset combination that male does not share with female.

So the desired output for this will look like

diffassests <- data.frame(ID = c(1,6,7),                                   
                              gender = c("male", "female", "female"),               
                              assets = c("a,e", "c,d", "a,d"))
filter r
3 Answers
df %>%
   group_by(assets) %>%
   filter(all(c('male', 'female') %in% gender))

# A tibble: 4 x 3
# Groups:   assets [2]
     ID gender assets 
  <int> <chr>  <chr>  
1     2 male   a,b,e,d
2     3 male   b,c,e  
3     4 female b,c,e  
4     5 female a,b,e,d
user_83802720

You can do this by grouping by assets and then filtering to remove any asset groups that have only one row, as in:

library(dplyr)

df <- data.frame(ID = c(1:7),                                   
                 gender = c("male","male", "male", "female", "female","female","female"),               
                 assets = c("a,e","a,b,e,d", "b,c,e","b,c,e", "a,b,e,d", "c,d","a,d"))  

df |> 
  group_by(assets) |> 
  filter(n() > 1) |> 
  arrange(assets, ID)
user_106778840

Another option is to first group by assets, then determine whether the number of distinct gender for that assets is equal to the dataframe level number of distinct gender. The first gender refers to gender for each group, then .$gender refers to the overall gender column. See here for original idea.

library(dplyr)

df1 %>% 
  group_by(assets) %>% 
  filter(n_distinct(gender) == n_distinct(.$gender))

Or a possible base R solution:

df[df$assets %in% Reduce(intersect, split(df$assets, df$gender)), ]

Output

     ID gender assets 
  <int> <chr>  <chr>  
1     2 male   a,b,e,d
2     3 male   b,c,e  
3     4 female b,c,e  
4     5 female a,b,e,d
user_152931910
Related