R: Rearrange matrix into three columns

Question

I have a matrix in R. Each entry i,j is a score and the rownames and colnames are ids.

Instead of the matrix I just want a 3 column matrix that has: i,j,score

Right now I'm using nested for loops. Like:

for(i in rownames(g))
    {
        print(which(rownames(g)==i))
        for(j in colnames(g))
        {
            cur.vector<-c(cur.ref, i, j, g[rownames(g) %in% i,colnames(g) %in% j])
            rbind(new.file,cur.vector)->new.file
        }

    }

But thats very inefficient I think...I'm sure there's a better way I'm just not good enough with R yet. Thoughts?

Please post the content of your matrix using `dput(g)`- it will make it much easier to understand your problem — David Robinson, Mar 28 '12 at 18:32
Future searchers, see answers to the reverse operation here: http://stackoverflow.com/q/9617348/210673 — Aaron left Stack Overflow, Mar 29 '12 at 01:03

digEmAll · Accepted Answer · 2012-03-28T18:47:33.343

If I understand you correctly, you need to flatten the matrix.
You can use as.vector and rep to add the id columns e.g. :

m = cbind(c(1,2,3),c(4,5,6),c(7,8,9))
row.names(m) = c('R1','R2','R3')
colnames(m) = c('C1','C2','C3')

d <- data.frame(i=rep(row.names(m),ncol(m)),
                j=rep(colnames(m),each=nrow(m)),
                score=as.vector(m))

Result:

> m
   C1 C2 C3
R1  1  4  7
R2  2  5  8
R3  3  6  9

> d
   i  j score
1 R1 C1     1
2 R2 C1     2
3 R3 C1     3
4 R1 C2     4
5 R2 C2     5
6 R3 C2     6
7 R1 C3     7
8 R2 C3     8
9 R3 C3     9

Please, note that this code converts a matrix into a data.frame, since the row and col names can be string and you can't have a matrix with different column type.
If you are sure that all row and col names are numbers, you can coerced it to a matrix.

score 8 · Answer 2 · answered Mar 28 '12 at 18:56

If you convert your matrix first to a table (with as.table) then to a data frame (as.data.frame) then it will accomplish what you are asking for. A simple example:

> tmp <- matrix( 1:12, 3 )
> dimnames(tmp) <- list( letters[1:3], LETTERS[4:7] )
> as.data.frame( as.table( tmp ) )
   Var1 Var2 Freq
1     a    D    1
2     b    D    2
3     c    D    3
4     a    E    4
5     b    E    5
6     c    E    6
7     a    F    7
8     b    F    8
9     c    F    9
10    a    G   10
11    b    G   11
12    c    G   12

that worked most easy for me. however, I had to turn my data.frame first into a matrix, then into a table, and then into a data frame again :) — Jens, Oct 13 '15 at 13:31

R: Rearrange matrix into three columns

2 Answers2

Linked