Converting a dataframe into a long format contingency table for network analysis purposes

Question

This is probably very basic (sorry), but I'm a beginner in coding and haven't been able to find instructions online. I use R and have a dataframe like this:

id<-c(1:4)
happy<-c(1,0,1,0)
sad<-c(0,0,0,1)
angry<-c(0,1,0,0)
excited<-c(1,0,1,1)
emot<-data.frame(id, happy, sad, angry, excited)
emot
  id happy sad angry excited
  1     1   0     0       1
  2     0   0     1       0
  3     1   0     0       1
  4     0   1     0       1

id signifies a person and the other variables signify whether the person mentioned a certain emotion (1) or not (0). I'd like to convert the dataframe to obtain this result:

source target  count
happy  sad      0
happy  angry    0
happy  excited  2
sad    angry    0
sad    excited  1
angry  excited  0

I really tried with the table function, but to no avail. Thank you in advance!

Answer 1

In base R you could do something like:

combo <- combn(names(emot)[-1], 2)

as.data.frame(t(combo)) |>
  setNames(c("target", "source")) |>
  transform(count = sapply(asplit(combo, 2), \(x) sum(rowMeans(emot[,x]) == 1L)))

Basically, get all the combinations of your column names, excluding id, and then iterate to retrieve those columns and apply some logic.

Output

  target  source count
1  happy     sad     0
2  happy   angry     0
3  happy excited     2
4    sad   angry     0
5    sad excited     1
6  angry excited     0

Answer 2

a <- crossprod(as.matrix(emot[-1]))
names(dimnames(a)) <- c('target', 'source')
as.data.frame.table(a, responseName = 'count')[c(upper.tri(a)),]

   target  source count
5   happy     sad     0
9   happy   angry     0
10    sad   angry     0
13  happy excited     2
14    sad excited     1
15  angry excited     0