Get number of ties ego and alter have in common in R

Question

I have a directed network dataset of adolescent friendships. I'd like to make an edgelist that includes the number of friends ego has in common with alter (someone ego and alter both nominated as a friend). Below is some sample data:

HAVE DATA:

 id   alter
  1      3
  1      5
  1      9
  2      3
  2      5
  3      2
  3      5
  3      9
  3      6

WANT DATA:

 id   alter   num_common
  1      3            2
  1      5            0
  1      9            0
  2      3            1
  2      5            0
  3      2            1
  3      5            0 
  3      9            0
  3      6            0

Answer 1

A solution could be to transform the edgelist into an adjacency matrix (using the igraph package) and multiple it by its transpose to count the number of shared neighbors:

el <- read.table(text= " id   alter
  1      3
  1      5
  1      9
  2      3
  2      5
  3      2
  3      5
  3      9
  3      6", header =T)

g <- graph_from_edgelist(as.matrix(el), directed = T)
m <- get.adjacency(g, sparse = F)
m2 <- m %*% t(m)           

Afterwards transform the resulting matrix back to an edgelist and merge it with the original data set:

el2 <- reshape2::melt(m2)
dplyr::left_join(el, el2, by = c("id" = "Var1", "alter" = "Var2"))

  id alter value
1  1     3     2
2  1     5     0
3  1     9     0
4  2     3     1
5  2     5     0
6  3     2     1
7  3     5     0
8  3     9     0
9  3     6     0

To see who how often ego and alter were both nominated by the same friend change the direction of the relation by using t(m) %*% m instead of m %*% t(m). To ignore direction, set the directed argument to FALSE in the graph_from_edgelist function.

Answer 2

this is a possible though not very simple solution:

# your dummy data
df <- data.table::fread("id   alter
                          1      3
                          1      5
                          1      9
                          2      3
                          2      5
                          3      2
                          3      5
                          3      9
                          3      6")

library(dplyr)
library(tidyr)

# all pairs vertically with pair ID
pairs_v <- combn(unique(c(df$id, df$alter)), 2) %>% 
  dplyr::as_tibble() %>% 
  tidyr::pivot_longer(cols = everything()) %>% 
  dplyr::arrange(name) 

# number of comon friends per group ID
pairs_comp <- pairs_v %>% 
  dplyr::left_join(df, by = c("value" = "id"))  %>% 
  dplyr::count(name, alter) %>% 
  dplyr::filter(n > 1 & !is.na(alter)) %>% 
  dplyr::count(name) 

# all pairs horizontally with pair ID
pairs_h <-pairs_v %>% 
  dplyr::group_by(name) %>% 
  dplyr::mutate(G_ID = dplyr::row_number()) %>% 
  tidyr::pivot_wider(names_from = G_ID, values_from = "value") 

# multiple left joins to get repeated comon friends for each direction of combination
df %>% 
  dplyr::left_join(pairs_h, by = c("id" = "1", "alter" = "2")) %>% 
  dplyr::left_join(pairs_comp) %>% 
  dplyr::left_join(pairs_h, by = c("id" = "2", "alter" = "1")) %>% 
  dplyr::left_join(pairs_comp, by = c("name.y" = "name")) %>% 
  dplyr::mutate(num_common = case_when(!is.na(n.x) ~ as.numeric(n.x),
                                      !is.na(n.y) ~ as.numeric(n.y),
                                      TRUE ~ 0)) %>% 
  dplyr::select(id, alter, num_common)

      id alter num_common
  1:  1     3          2
  2:  1     5          0
  3:  1     9          0
  4:  2     3          1
  5:  2     5          0
  6:  3     2          1
  7:  3     5          0
  8:  3     9          0
  9:  3     6          0