Reshape matrix to data frame

Question

I have association matrix file that looks like this (4 rows and 3 columns) .

test=read.table("test.csv", sep=",", header=T)
head(test)
            LosAngeles   SanDiego    Seattle    
            1            2           3          
A    1      0.1          0.2         0.2        
B    2      0.2          0.4         0.2        
C    3      0.3          0.5         0.3        
D    4      0.2          0.5         0.1

What I want to is reshape this matrix file into data frame. The result should look something like this (12(= 4 * 3) rows and 3 columns):

RowNum    ColumnNum    Value
1         1            0.1
2         1            0.2
3         1            0.3
4         1            0.2
1         2            0.2
2         2            0.4
3         2            0.5
4         2            0.5
1         3            0.2
2         3            0.2
3         3            0.3
4         3            0.1

That is, if my matrix file has 100 rows and 90 columns. I want to make new data frame file that contains 9000 (= 100 * 90) rows and 3 columns.

I've tried to use reshape package but but I do not seem to be able to get it right. Any suggestions how to solve this problem?

try this as.data.frame(as.table(test)) from (stackoverflow.com/questions/15885111/…) — Silence Dogood
Perhaps melt in the reshape2 package could help (qualify this with i am a bit unsure of your data format - doesn't look like 4 rows / 3 columns) — user20650
Thank you, Osssan! Unfortunately, I also did read question before I post it but it did not work for me. — user2913161
Thank you very much, David!!!Actually the data I have looks like this....dput(head(test))......structure(list(X.1 = c(NA, 1L, 2L, 3L, 4L, 5L), GO.0000165 = c(1, 0.285, 0.336, 0.183, 0.067, 0.062), GO.0001505 = c(2, 0.215, 0.197, 0.19, 0.084, 0.078), GO.0001568 = c(3, 0.11, 0.104, 0.13, 0.091, 0.084), GO.0001944 = c(4, 0.086, 0.083, 0.105, 0.089, 0.082), GO.0001975 = c(5, 0.043, 0.093, 0.05, 0.086, 0.079), GO.0002755 = c(6, 0.176, 0.245, 0.116, 0.101, 0.096), ....... — user2913161

Jota Jota · Accepted Answer · 2014-07-30T21:50:21

This should do the trick:

test <- as.matrix(read.table(text="
            1            2           3          
    1      0.1          0.2         0.2        
    2      0.2          0.4         0.2        
    3      0.3          0.5         0.3        
    4      0.2          0.5         0.1", header=TRUE)) 

data.frame(which(test==test, arr.ind=TRUE), 
                 Value=test[which(test==test)], 
                 row.names=NULL)
#   row col Value
#1    1   1   0.1
#2    2   1   0.2
#3    3   1   0.3
#4    4   1   0.2
#5    1   2   0.2
#6    2   2   0.4
#7    3   2   0.5
#8    4   2   0.5
#9    1   3   0.2
#10   2   3   0.2
#11   3   3   0.3
#12   4   3   0.1

Reshape matrix to data frame

2 Answers