Create an edges dataframe using the values of in the cells of other dataframe-CodePudding

I have the dataframe below:

dummy<-structure(list(Name = c("A", "B", "C", "A", "B", "C"), `#BISB` = c(2, 
6, 4, 0, 4, 6), `#BISC` = c(2, 6, 4, 0, 4, 6)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

and I have created its node file like

nodes<-structure(list(id = c(1, 2, 3, 4, 5), group = c("A", "B", "C", 
"#BISB", "#BISC")), class = "data.frame", row.names = c(NA, -5L
))

now given the information from these 2 dfs I would like to create the edges dataframe based on the dummy dataframe logic that every Name (A,B,C) is connected to different departments (#BISB,#BISC) With edge title and label the relative values.it will be like:

from to label arrows title length
1     1  4     2     to     2    300
2     1  5     2     to     2    300
3     1  4     0     to     0    300
4     1  5     0     to     0    300
5     2  4     6     to     6    300
6     2  5     6     to     6    300
7     2  4     4     to     4    300
8     2  5     4     to     4    300
9     3  4     4     to     4    300
10    3  5     4     to     4    300
11    3  4     6     to     6    300
12    3  5     6     to     6    300
>

CodePudding user response：

I'n not sure where all values in your desired output are coming from.. but I believe this is a good startingpoint.

library(data.table)
setDT(dummy)
setDT(nodes)
ans <- melt(dummy, id.vars = "Name", variable.factor = FALSE, value.name = "arrows")
ans[nodes, from := i.id, on = .(Name = group)]
ans[nodes, to := i.id, on = .(variable = group)]
#    Name variable arrows from to
# 1:    A    #BISB      2    1  4
# 2:    B    #BISB      6    2  4
# 3:    C    #BISB      4    3  4
# 4:    A    #BISB      0    1  4
# 5:    B    #BISB      4    2  4
# 6:    C    #BISB      6    3  4
# 7:    A    #BISC      2    1  5
# 8:    B    #BISC      6    2  5
# 9:    C    #BISC      4    3  5
#10:    A    #BISC      0    1  5
#11:    B    #BISC      4    2  5
#12:    C    #BISC      6    3  5