1

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?

Improve this question
5
  • 2
    try this as.data.frame(as.table(test)) from (stackoverflow.com/questions/15885111/…) Commented Jul 30, 2014 at 21:32
  • Can you provide dput(head(test)) ? Commented Jul 30, 2014 at 21:40
  • 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) Commented Jul 30, 2014 at 21:41
  • Thank you, Osssan! Unfortunately, I also did read question before I post it but it did not work for me. Commented Jul 30, 2014 at 21:43
  • 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), ....... Commented Jul 30, 2014 at 21:45

2 Answers 2

Reset to default
4

Use as.data.frame.table. Its the boss:

m <- matrix(data = c(0.1, 0.2, 0.2, 0.2, 0.4, 0.2, 0.3, 0.5, 0.3, 0.2, 0.5, 0.1), nrow = 4, byrow = TRUE, dimnames = list(row = 1:4, col = 1:3)) m # col # row 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 as.data.frame.table(m) # row col Freq # 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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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
Improve this answer

3 Comments

I was also going to answer with some a bit more terse: library(reshape2);melt(test);. Then I realized that your approach is over 3 times faster (on my computer). Odd, I wouldn't call this the most elegant way but definitely a faster one. Nice work.
maybe data.frame(row = c(row(test)), col = c(col(test)), Value = c(test))
@flodel When the matrix gets larger (e.g. 1000 x 1000), your suggestion is much faster (6x faster on my machine). Cool! On this small example, the one I posted is marginally (~ 8%) faster.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.