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!
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