Greg Rov Greg Rov - 2 months ago 15
R Question

Organize my results in a table like a correlation matrix

I need to organize my results of correlation in a matrix, for example. I have a file like this:

trait1 trait2 GeCor PCor
a b -1.00 0.28
b c 0.40 0.45
d e -0.39 0.35
a c -0.39 0.50
b d 0.36 0.30
a d -0.35 0.30
b e -0.29 0.36
a e 0.26 0.33
c e 0.18 0.38
c d 0.04 0.31


And I need that file stay this way:

a b c d e
a - 0.28 0.50 0.30 0.33
b -1.00 - 0.45 0.30 0.36
c -0.39 0.40 - 0.31 0.38
d -0.35 0.36 0.04 - 0.35
e 0.26 -0.29 0.18 -0.39 -


The symbol on the diagonal (
-
) only is that this space should be left blank. The
GeCor
are genetic correlation between
trait1
and
trait2
, these should be below of the blank diagonal, and
PCor
are the phenotype correlation between
trait1
and
trait2
, and should be above of the blank diagonal. Well, just to tell this database is just a sample and I have many more traits. I accept programming suggestions for unix or R. Thank you very much.

Answer

I think it is time to post my solution with R.

First, with read.table, read in your original data into a data frame, say x. Then

n <- ceiling(sqrt(2 * nrow(x)))
z <- matrix(NA, n, n, dimnames = list(letters[1:n],letters[1:n]))
z[lower.tri(z)] <- with(x, GeCor[order(trait1, trait2)])
z[upper.tri(z)] <- with(x, PCor[order(trait2, trait1)])

#      a     b    c     d    e
#a    NA  0.28 0.50  0.30 0.33
#b -1.00    NA 0.45  0.30 0.36
#c -0.39  0.40   NA  0.31 0.38
#d -0.35  0.36 0.04    NA 0.35
#e  0.26 -0.29 0.18 -0.39   NA

write.table(z, file = "z.txt" na = "-", sep = "\t", quote = FALSE)

## what the text file looks like
a   b   c   d   e
a   -   0.28    0.5 0.3 0.33
b   -1  -   0.45    0.3 0.36
c   -0.39   0.4 -   0.31    0.38
d   -0.35   0.36    0.04    -   0.35
e   0.26    -0.29   0.18    -0.39   -