Unusual (to me) data structure: count how often two columns mutually point to one another

user_8742237 0
Viewed 75

I'm working with a dataset that has a structure I haven't worked with before. It has this format (code for data input at the end):

df
#>   Instance Alice_prefers Bob_prefers Charlie_prefers
#> 1        1           Bob     Charlie           Alice
#> 2        2           Bob       Alice             Bob
#> 3        3           Bob     Charlie             Bob

I'm trying to create a variable that shows when two people "match", i.e. when they both prefer each other, eg if Alice prefers Bob, and Bob also prefers Alice--it's a mutual match: Alice and Bob match.

So my desired output is:

output
#>   Instance Alice_prefers Bob_prefers Charlie_prefers      Match
#> 1        1           Bob     Charlie           Alice       <NA>
#> 2        2           Bob       Alice             Bob   AliceBob
#> 3        3           Bob     Charlie             Bob BobCharlie

Using case_when() seems to do the trick, but is there an easier way than listing every single case, as I've started doing below? In my real dataset, I'll be looking to have many more people than just Alice, Bob and Charlie.

Second Question:

When I have more people, eg. Alice, Bob, Charlie, and Denise, the Match column may contain multiple matches (eg. Alice and Bob match, and Charlie and Denise match)--what's the tidy solution to this? Should I have an indicator variable for every single possible match, eg AliceBob, AliceCharlie, etc, that takes the value of 1 or 0?

The reason I'm doing this is: I would like to be able to quickly look at the number of matches and who is in the matches.

#df <- df %>% mutate(Match = ifelse(Alice_prefers=="Bob" & Bob_prefers =="Alice", "AliceBob", NA))
df <- df %>% mutate(
      Match = case_when(
        (Alice_prefers=="Bob" & Bob_prefers=="Alice") ~  "AliceBob",
        (Charlie_prefers=="Bob" & Bob_prefers=="Charlie") ~  "BobCharlie"
                       )
                    )
df

Code for data input:

df <- data.frame(stringsAsFactors=FALSE,
          Instance = c(1, 2, 3),
     Alice_prefers = c("Bob", "Bob", "Bob"),
       Bob_prefers = c("Charlie", "Alice", "Charlie"),
   Charlie_prefers = c("Alice", "Bob", "Bob")
)
dplyr tidyverse r
2 Answers

You have a graph problem and that generally means that you should use igraph. You are looking for mutual edges in the directed graphs.

I use data.table for group-by but you can also use the tidyverse if you prefer.

First of all, you should reshape your data to tidy (long) format. This is also the tidy output format.

names(df) <- gsub("_prefers", "", names(df), fixed = TRUE)
library(reshape2)
mdf <- melt(df, id.vars = "Instance")
#  Instance variable   value
#1        1    Alice     Bob
#2        2    Alice     Bob
#3        3    Alice     Bob
#4        1      Bob Charlie
#5        2      Bob   Alice
#6        3      Bob Charlie
#7        1  Charlie   Alice
#8        2  Charlie     Bob
#9        3  Charlie     Bob

library(data.table)
setDT(mdf) #for group-by

library(igraph)
mdf[, Match := {
  #turn subsets into graphs
  g <- graph_from_data_frame(.SD[, .(variable, value)]) 
  #initialize result
  res <- character(.N)
  #find reciproc pairs
  m <- which_mutual(g)
  #I'd probably just return m
  #but just for fun,
  #get names of reciproc pairs
  res[m] <- attr(E(g)[m], "vnames")
  res
}, by = Instance]

#   Instance variable   value       Match
#1:        1    Alice     Bob            
#2:        2    Alice     Bob   Alice|Bob
#3:        3    Alice     Bob            
#4:        1      Bob Charlie            
#5:        2      Bob   Alice   Bob|Alice
#6:        3      Bob Charlie Bob|Charlie
#7:        1  Charlie   Alice            
#8:        2  Charlie     Bob            
#9:        3  Charlie     Bob Charlie|Bob

If you only want counts, it is even easier:

mdf[, .(count = {
  #turn subsets into graphs
  g <- graph_from_data_frame(.SD[, .(variable, value)]) 
  reciprocity(g) * .N
}), by = Instance]
#   Instance count
#1:        1     0
#2:        2     2
#3:        3     2
user_14120590

Here's one tidyverse approach where the columns are reshaped into longer form so that we can pair up the two people. This should work with any number of matches within one line.

library(tidyverse)
df %>%
  pivot_longer(-Instance, "A", values_to = "B") %>%
  mutate(A = str_remove(A, "_prefers"),
         match = if_else(A < B, paste(A,B), paste(B,A))) %>%
  count(Instance, match) %>%
  filter(n > 1)

## A tibble: 2 x 3
#  Instance match           n
#     <dbl> <chr>       <int>
#1        2 Alice Bob       2
#2        3 Bob Charlie     2
user_68518250
Related