I have similar data
mydf <- data.frame(p1=c('a','a','a','b','b','b','c','c','d'),
p2=c('b','c','d','c','d','e','d','e','e'),
p3=c('a','a','c','c','d','d','d','a','a'),
p4=c('a','a','b','c','c','e','d','a','b'),
p5=c('a','b','c','d','e','b','b','c','c'),
source=c('a','b','c','d','e','e','a','b','d'))
Which gives:
p1 p2 p3 p4 p5 source
1 a b a a a a
2 a c a a b b
3 a d c b c c
4 b c c c d d
5 b d d c e e
6 b e d e b e
7 c d d d b a
8 c e a a c b
9 d e a b c d
I want to create two adjacency matrix as, number of connections between source to rest columns.For example:
a b c d e
a 4 2
b 5 1
c 1 1
d 1 2
e 0 3
Is there any way to do it easily. Would appreciate any help
In base R, we can use unlist and table :
table(rep(mydf$source, ncol(mydf) - 1), unlist(mydf[-ncol(mydf)]))
# a b c d e
# a 4 2 1 3 0
# b 5 1 3 0 1
# c 1 1 2 1 0
# d 1 2 4 2 1
# e 0 3 1 3 3
Another approach could be to get the data in long format, count based on source and get data in wide format again.
library(dplyr)
library(tidyr)
mydf %>%
pivot_longer(cols = -source) %>%
count(source, value) %>%
pivot_wider(names_from = value, values_from = n, values_fill = list(n = 0))
# source a b c d e
# <fct> <int> <int> <int> <int> <int>
#1 a 4 2 1 3 0
#2 b 5 1 3 0 1
#3 c 1 1 2 1 0
#4 d 1 2 4 2 1
#5 e 0 3 1 3 3