transform matrix to 3 column dataframe in R

April 16, 2022

I have a matrix such as :

     G1       G2       G3       G4
G1   0.000000 3.575791 3.961912 4.102760
G2   3.575791 0.000000 4.515661 4.656509
G3   3.961912 4.515661 0.000000 2.114352
G4   4.102760 4.656509 2.114352 0.000000

(dput format)

structure(c(0, 3.5757909, 3.9619119, 4.1027599, 3.5757909, 0, 
4.515661, 4.656509, 3.9619119, 4.515661, 0, 2.114352, 4.1027599, 
4.656509, 2.114352, 0), .Dim = c(4L, 4L), .Dimnames = list(c("G1", 
"G2", "G3", "G4"), c("G1", "G2", "G3", "G4"
)))

And I would like to transform it as :

C1 C2 Value
G1 G1 0.000000
G1 G2 3.575791
G1 G3 3.961912
G1 G4 4.102760
G2 G2 0.000000
G2 G3 4.515661
G2 G4 4.656509
G3 G3 0.000000
G3 G4 2.114352
G4 G4 0.000000

>Solution :

We can convert the matrix to table and then use as.data.frame (if we don’t want the upper.tri or lower.tri, change it to NA and then reshape)

m1[upper.tri(m1)] <- NA
out <- na.omit(as.data.frame.table(m1))
names(out) <- c("C1", "C2", "Value")

-output

> out
   C1 C2    Value
1  G1 G1 0.000000
2  G2 G1 3.575791
3  G3 G1 3.961912
4  G4 G1 4.102760
6  G2 G2 0.000000
7  G3 G2 4.515661
8  G4 G2 4.656509
11 G3 G3 0.000000
12 G4 G3 2.114352
16 G4 G4 0.000000