Greg Rov - 1 year ago 76
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.

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   -
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download