Convert a matrix with dimnames into a long format data.frame

Question

Hoping there's a simple answer here but I can't find it anywhere.

I have a numeric matrix with row names and column names:

#      1    2    3    4
# a    6    7    8    9
# b    8    7    5    7
# c    8    5    4    1
# d    1    6    3    2

I want to melt the matrix to a long format, with the values in one column and matrix row and column names in one column each. The result could be a data.table or data.frame like this:

#  col  row  value
#    1    a      6
#    1    b      8
#    1    c      8
#    1    d      1
#    2    a      7
#    2    c      5
#    2    d      6
    ...

Any tips appreciated.

Future searchers: see the opposite here: http://stackoverflow.com/q/9617348 — Aaron left Stack Overflow, Apr 19 '12 at 19:45

score 21 · Accepted Answer · answered Apr 19 '12 at 18:51

21

Use melt from reshape2:

library(reshape2)
#Fake data
x <- matrix(1:12, ncol = 3)
colnames(x) <- letters[1:3]
rownames(x) <- 1:4
x.m <- melt(x)
x.m

   Var1 Var2 value
1     1    a     1
2     2    a     2
3     3    a     3
4     4    a     4
...

answered Apr 19 '12 at 18:51

Chase

67,710
18
144
161

1

Error in as.data.frame.default(x[[i]], optional = TRUE) : cannot coerce class ‘structure("dpoMatrix", package = "Matrix")’ to a data.frame – Mox Sep 06 '18 at 21:22

score 19 · Answer 2 · answered Apr 19 '12 at 19:44

19

The as.table and as.data.frame functions together will do this:

> m <- matrix( sample(1:12), nrow=4 )
> dimnames(m) <- list( One=letters[1:4], Two=LETTERS[1:3] )
> as.data.frame( as.table(m) )
   One Two Freq
1    a   A    7
2    b   A    2
3    c   A    1
4    d   A    5
5    a   B    9
6    b   B    6
7    c   B    8
8    d   B   10
9    a   C   11
10   b   C   12
11   c   C    3
12   d   C    4

answered Apr 19 '12 at 19:44

Greg Snow

48,497
6
83
110

OK +1, that's a new one on me... and that's kind rare. – John Apr 19 '12 at 22:10

score 11 · Answer 3 · answered Apr 19 '12 at 19:00

11

Assuming 'm' is your matrix...

data.frame(col = rep(colnames(m), each = nrow(m)), 
           row = rep(rownames(m), ncol(m)), 
           value = as.vector(m))

This executes extremely fast on a large matrix and also shows you a bit about how a matrix is made, how to access things in it, and how to construct your own vectors.

answered Apr 19 '12 at 19:00

John

23,360
7
57
83

2

This is quite interesting, thank you. All three solutions posted are within 2% on my machine for a 1k x 1k matrix. – Chase Apr 19 '12 at 20:26

score 1 · Answer 4 · answered Feb 21 '21 at 13:01

A modification that doesn't require you to know anything about the storage structure, and that easily extends to high dimensional arrays if you use the dimnames, and slice.index functions:

data.frame(row=rownames(m)[as.vector(row(m))],
           col=colnames(m)[as.vector(col(m))],
           value=as.vector(m))

Convert a matrix with dimnames into a long format data.frame

4 Answers4

Linked

Related