Sorting a Square Matrix

I have a 75 x 75 square matrix. All the values in it, other than labels are decimals. I want to get all the pairs of labels having values above or below the cutoff I set. Say, I have the following

     A     B     C     D
A    1     0.2   0.43  0.16
B    0.2   1     0.86  0.28
C    0.43  0.86  1     0.76
D    0.16  0.28  0.76  1

I want

B   C   0.86
C   D   0.76
A   C  开发者_C百科 0.43

How can I go about it (without getting duplicates)?

Thanks

---

First you need to convert to a representation with one row for each value; one way to do this is by converting to a matrix and then to a data.frame. To avoid duplicates, set the lower triangle (possibly including the diagonal) to NA before converting to a data frame, and then take the subset of the data frame where the values aren't NAs.

mtx <- matrix( rnorm(16), 4, 4, dimnames = list( LETTERS[1:4], LETTERS[1:4]))
mtx[lower.tri(mtx, diag=TRUE)] <- NA
dx <- as.data.frame(as.table(mtx))
dx <- subset(dx, !is.na(Freq))

You can then take a subset as desired from the resulting data frame.

> subset(dx, Freq>0)
   Var1 Var2      Freq
5     A    B 1.9564158
9     A    C 1.7188939
14    B    D 0.1848542

---

One way is to use the melt fn from the reshape package (UPDATED to avoid duplication in symmetric matrices, and sorting the final result).

> mtx <- matrix( round(10*rnorm(16)),
                 4, 4, dimnames = list( LETTERS[1:4], LETTERS[1:4]))
> mtx[ upper.tri(mtx)] <- NA
> mtx
    A  B  C  D
A  -7 NA NA NA
B  23 17 NA NA
C   6 -2 20 NA
D -23  8 15 -6
> require(reshape)
> df <- cbind( data.frame(Row = rownames(mtx)), as.data.frame(mtx))
> df.m <- melt(df, id = 'Row', variable_name = 'Col')
> df.m
   Row Col value
1    A   A    -7
2    B   A    23
3    C   A     6
4    D   A   -23
5    A   B    NA
6    B   B    17
7    C   B    -2
8    D   B     8
9    A   C    NA
10   B   C    NA
11   C   C    20
12   D   C    15
13   A   D    NA
14   B   D    NA
15   C   D    NA
16   D   D    -6
> filt <- subset(df.m, value < 0)
> filt[order(filt$value),]
   Row Col value
4    D   A   -23
1    A   A    -7
16   D   D    -6
7    C   B    -2

Of course you could then format the result any way you want, e.g.

> cat(with(filt, paste(Row, Col, value, '\n')))
 A A -7 
 D A -23 
 C B -2 
 D D -6 

---

library(fBasics)
Z <- Triang(X)
data.frame(t(apply(which(Z >= 0.43 & Z < 1 ,arr.ind = T),1,
     function(y) c(rownames(Z)[y[1]],colnames(Z)[y[2]],Z[y[1],y[2]]))))

gives

  X1 X2   X3
A  A  C 0.43
B  B  C 0.86
C  C  D 0.76

---

With the matrix and cut-off

m <- matrix( rnorm(16), 4, 4, dimnames = list( LETTERS[1:4], LETTERS[1:4]))
cut <- 0

index the values satisfying your criteria and use that to extract the desired values and row names. Sort after the fact

i <- lower.tri(m, diag=TRUE) * m > cut
df <- data.frame(Row=rownames(m)[row(m)[i]], Col=colnames(m)[col(m)[i]],
                 Val=m[i])
df[order(df$Val),]