Transform Names Matrix into Dataframe rows

Question

Lets say I have a matrix mat which has both column names and row names:

mat <- matrix(1:25, ncol = 5)
rownames(mat) <- LETTERS[1:5]
colnames(mat) <- LETTERS[6:10]

^^ Simple and reproducible example to make the following matrix:

     F  G  H  I  J
   A 1  6 11 16 21
   B 2  7 12 17 22
   C 3  8 13 18 23
   D 4  9 14 19 24
   E 5 10 15 20 25

I need to turn this matrix into a data frame that looks like the following:

ROWNAME   COLNAME    VALUE
   A        F          1
   B        F          2
   C        F          3
       ......

Is there a function built in to do this? How would I tackle such a problem.

Also, this matrix would be one of many matrices so there would need to be a Matrix Name column added so that it could parse over multiple matrices and create a data.frame that is actually 4 columns and as many observations as values in all of the matrices combined. If there is a shortcut for any of these steps it would be greatly appreciated.

Answer 1

Using tidyr:

mat <- matrix(1:25, ncol = 5)

rownames(mat) <- LETTERS[1:5]
colnames(mat) <- LETTERS[6:10]

mat <- as.data.frame(mat)
mat$ROWNAME <- rownames(mat)


library(tidyr)

mat %>% gather(COLNAME, VALUE, F:J)

    ROWNAME COLNAME VALUE
1        A      F     1
2        B      F     2
3        C      F     3
4        D      F     4
5        E      F     5
6        A      G     6
7        B      G     7
...

Using data.table:

library(data.table)

melt(setDT(mat), id.vars = 6, measure.vars = 1:5, 
     variable.name = "COLNAME", value.name = "VALUE")

Answer 2

Try this

df = expand.grid(rownames(mat),colnames(mat))
df$val = as.vector(mat)

Answer 3

You can use melt:

library(reshape2)
melt(mat)
head(setNames(melt(mat), c("ROWNAME", "COLNAME", "VALUE")))
#   ROWNAME COLNAME VALUE
# 1       A       F     1
# 2       B       F     2
# 3       C       F     3
# 4       D       F     4
# 5       E       F     5
# 6       A       G     6

Answer 4

We can do this without any packages. We replicate the rownames and colnames and the elements of 'mat' and create a data.frame.

data.frame(ROWNAME = rownames(mat)[row(mat)], 
           COLNAME = colnames(mat)[col(mat)], 
           VALUE = c(mat))
#   ROWNAME COLNAME VALUE
#1        A       F     1
#2        B       F     2
#3        C       F     3
#4        D       F     4
#5        E       F     5
#6        A       G     6
#7        B       G     7
#8        C       G     8
#9        D       G     9
#10       E       G    10
#11       A       H    11
#12       B       H    12
#13       C       H    13
#14       D       H    14
#15       E       H    15
#16       A       I    16
#17       B       I    17
#18       C       I    18
#19       D       I    19
#20       E       I    20
#21       A       J    21
#22       B       J    22
#23       C       J    23
#24       D       J    24
#25       E       J    25

---

Or if we need a faster approach, an option using Matrix would be

library(Matrix)
summary(Matrix(mat, sparse=TRUE))