I have a data frame in R (df) which looks like this:
colA colB
A,B 0.5
A,C 8
B,A 0.5
B,C 9
C,A 8
C,B 9
It represents correlation values obtained by running a certain software.
Now, I would like to convert this data frame to a correlation matrix to be plotted with the Corr() function:
DESIRED OUTPUT:
A B C
A 1 0.5 8
B 0.5 1 9
C 8 9 1
Please, any suggestion about the code I can utilise?
>Solution :
Data:
input <- structure(list(colA = c("A,B", "A,C", "B,A", "B,C", "C,A", "C,B"
), colB = c(0.5, 8, 0.5, 9, 8, 9)), class = "data.frame", row.names = c(NA, -6L))
Solution:
rc <- matrix(scan(text = input$colA, what = "", sep = ","), ncol = 2, byrow = T)
tapply(input$colB, asplit(rc, 2), FUN = sum, default = 1)
# A B C
#A 1.0 0.5 8
#B 0.5 1.0 9
#C 8.0 9.0 1
Note: As commented, you have carelessly made-up data in your question. Correlation is never bigger than 1.
Remark 1:
Someone may propose xtabs. That is fine, but then you have to modify diagonal elements to 1.
Remark 2:
Matrix indexing is also a good approach, but takes more lines of code.