Convert from N X M Matrix to Long Matrix in R

Convert from n x m matrix to long matrix in R

If you need a single column matrix

 matrix(m, dimnames=list(t(outer(colnames(m), rownames(m), FUN=paste)), NULL))
 #    [,1]
 #a d    1
 #a e    4
 #b d    2
 #b e    5
 #c d    3
 #c e    6

For a data.frame output, you can use melt from reshape2

 library(reshape2)
 melt(m)

Fastest conversion of matrix to long format data frame in R

So many options:

library(dplyr)
library(tidyr)
library(data.table)

library(microbenchmark)
library(ggplot2)

set.seed(1)
ex <- matrix(data = round(runif(100000), 1), nrow = 1000, ncol = 100)
rownames(ex) <- paste0("row", 1:nrow(ex))
colnames(ex) <- paste0("col", 1:ncol(ex))

comp <- microbenchmark(
    table = {
        df1 <- as.data.frame(as.table(ex))
    },

    reshape = {
        df2 <- reshape2::melt(ex)
    },

    dplyr = {
        df3 <- ex %>%
            as.data.frame() %>%
            tibble::rownames_to_column("Var1") %>%
            gather("Var2", "value", -Var1)
    },

    data.table = {
        dt = melt(data.table(ex, keep.rownames = TRUE) , id.vars = c("rn"))
    },

    data.table2 = {
        melt(as.data.table(ex)[, rn := seq_len(.N)], id.var = 'rn')
    },

    data.table3 = {
        data.table(Var1 = rownames(ex), Var2 = colnames(ex), value = c(ex))
    }

)

autoplot(comp)

Reshape three column data frame to matrix (long to wide format)

There are many ways to do this. This answer starts with what is quickly becoming the standard method, but also includes older methods and various other methods from answers to similar questions scattered around this site.

tmp <- data.frame(x=gl(2,3, labels=letters[24:25]),
                  y=gl(3,1,6, labels=letters[1:3]), 
                  z=c(1,2,3,3,3,2))

Using the tidyverse:

The new cool new way to do this is with pivot_wider from tidyr 1.0.0. It returns a data frame, which is probably what most readers of this answer will want. For a heatmap, though, you would need to convert this to a true matrix.

library(tidyr)
pivot_wider(tmp, names_from = y, values_from = z)
## # A tibble: 2 x 4
## x         a     b     c
## <fct> <dbl> <dbl> <dbl>
## 1 x       1     2     3
## 2 y       3     3     2

The old cool new way to do this is with spread from tidyr. It similarly returns a data frame.

library(tidyr)
spread(tmp, y, z)
##   x a b c
## 1 x 1 2 3
## 2 y 3 3 2

Using reshape2:

One of the first steps toward the tidyverse was the reshape2 package.

To get a matrix use acast:

library(reshape2)
acast(tmp, x~y, value.var="z")
##   a b c
## x 1 2 3
## y 3 3 2

Or to get a data frame, use dcast, as here: Reshape data for values in one column.

dcast(tmp, x~y, value.var="z")
##   x a b c
## 1 x 1 2 3
## 2 y 3 3 2

Using plyr:

In between reshape2 and the tidyverse came plyr, with the daply function, as shown here: https://stackoverflow.com/a/7020101/210673

library(plyr)
daply(tmp, .(x, y), function(x) x$z)
##    y
## x   a b c
##   x 1 2 3
##   y 3 3 2

Using matrix indexing:

This is kinda old school but is a nice demonstration of matrix indexing, which can be really useful in certain situations.

with(tmp, {
  out <- matrix(nrow=nlevels(x), ncol=nlevels(y),
                dimnames=list(levels(x), levels(y)))
  out[cbind(x, y)] <- z
  out
})

Using xtabs:

xtabs(z~x+y, data=tmp)

Using a sparse matrix:

There's also sparseMatrix within the Matrix package, as seen here: R - convert BIG table into matrix by column names

with(tmp, sparseMatrix(i = as.numeric(x), j=as.numeric(y), x=z,
                       dimnames=list(levels(x), levels(y))))
## 2 x 3 sparse Matrix of class "dgCMatrix"
##   a b c
## x 1 2 3
## y 3 3 2

Using reshape:

You can also use the base R function reshape, as suggested here: Convert table into matrix by column names, though you have to do a little manipulation afterwards to remove an extra columns and get the names right (not shown).

reshape(tmp, idvar="x", timevar="y", direction="wide")
##   x z.a z.b z.c
## 1 x   1   2   3
## 4 y   3   3   2

convert raster-like matrix to x,y,z structure - column names to x, row names to y, values to z

Use melt from reshape2 to obtain a data frame output:

reshape2::melt(foo)

there are multiple options in r to do this. pivot_longer etc. A google search such as 'converting from wide to long' will result in many solutions.

Generating an nxm matrix from n length vector and m length vector in R

I was just about to write the same as @user2554330 in the comments above. Here an example:

m <- 4
n <- 3
longitude <- runif(m)
latitude <- runif(n)

outer(longitude, latitude, function(x, y) paste(x, ":", y))

The third argument for outer is a function of (at least) two arguments. It has to be vectorized on these two arguments and has to return a vector of equal length.

However, if you are looking for a way to store a grid of points together with some associated value, then expand.grid is probably better suited. This does not give you a nxm matrix but a dataframe with nxm rows, each of them representing an entry from the matrix. You can then add values to this dataframe, preferably using a vectorized function:

m <- 4
n <- 3
longitude <- sort(runif(m, 35, 45))
latitude <- sort(runif(n, 35, 45))

points <- expand.grid(long = longitude, lat = latitude)
points$dist <- geosphere::distHaversine(points, c(40,40))
points
#>        long      lat     dist
#> 1  35.47003 36.08055 589824.8
#> 2  38.80128 36.08055 448775.9
#> 3  42.37399 36.08055 483359.7
#> 4  44.59732 36.08055 593811.2
#> 5  35.47003 39.27063 396699.9
#> 6  38.80128 39.27063 130967.4
#> 7  42.37399 39.27063 219108.9
#> 8  44.59732 39.27063 402352.0
#> 9  35.47003 42.59421 476254.2
#> 10 38.80128 42.59421 305683.5
#> 11 42.37399 42.59421 350418.6
#> 12 44.59732 42.59421 480743.1

Convert a matrix to a 1 dimensional array

Either read it in with 'scan', or just do as.vector() on the matrix. You might want to transpose the matrix first if you want it by rows or columns.

> m=matrix(1:12,3,4)
> m
     [,1] [,2] [,3] [,4]
[1,]    1    4    7   10
[2,]    2    5    8   11
[3,]    3    6    9   12
> as.vector(m)
 [1]  1  2  3  4  5  6  7  8  9 10 11 12
> as.vector(t(m))
 [1]  1  4  7 10  2  5  8 11  3  6  9 12

Convert matrix to three column data.frame

You can use melt

library(reshape2)
setNames(melt(m1), c('rows', 'vars', 'values'))
#  rows vars values
#1 row1 var1      1
#2 row2 var1      3
#3 row1 var2      2
#4 row2 var2      4

Or

data.frame(rows=rownames(m1)[row(m1)], vars=colnames(m1)[col(m1)],
                      values=c(m1))
#   rows vars values
#1 row1 var1      1
#2 row2 var1      3
#3 row1 var2      2
#4 row2 var2      4

Or

 as.data.frame(as.table(m1))
 #    Var1 Var2 Freq
 #1 row1 var1    1
 #2 row2 var1    3
 #3 row1 var2    2
 #4 row2 var2    4

data

m1 <- structure(c(1L, 3L, 2L, 4L), .Dim = c(2L, 2L), .Dimnames = list(
c("row1", "row2"), c("var1", "var2")))

melting matrices with logical values

While you have other answers already, this can be achieved with reshape2 and melt, if the appropriate function is called. In this case you don't want reshape2:::melt.data.frame but rather reshape2:::melt.matrix to be applied. So, try:

melt(as.matrix(df), na.rm=TRUE)
#  Var1 Var2 value
#2    b    a     1
#3    c    a     2
#6    c    b     3

If you then take a look at ?reshape2:::melt.data.frame you will see the statement:

This code is conceptually similar to ‘as.data.frame.table’

which means you could also use the somewhat more convoluted:

na.omit(as.data.frame.table(as.matrix(df), responseName="value"))
#  Var1 Var2 value
#2    b    a     1
#3    c    a     2
#6    c    b     3

Convert upper triangular part of a matrix to 3-column long format

Suppose X is your matrix, we can do:

ind <- which(upper.tri(X, diag = TRUE), arr.ind = TRUE)
cbind(ind, X[ind])

In some cases you may want to use dim names. In that case, we have to arrange the result in a data frame, as the first two columns are character, while the third column is numeric.

ind <- which(upper.tri(X, diag = TRUE), arr.ind = TRUE)
nn <- dimnames(X)
data.frame(row = nn[[1]][ind[, 1]],
           col = nn[[2]][ind[, 2]],
           val = X[ind])

---

Related Topics