Convert Table into Matrix by Column Names

Convert table into matrix by column names

Check method cast from reshape package

# generate test data    
x <- read.table(textConnection('
models cores  time
4 1 0.000365
4 2 0.000259
4 3 0.000239
4 4 0.000220
8 1 0.000259
8 2 0.000249
8 3 0.000251
8 4 0.000258'
), header=TRUE)

library(reshape)
cast(x, models ~ cores)

results:

  models        1        2        3        4
1      4 0.000365 0.000259 0.000239 0.000220
2      8 0.000259 0.000249 0.000251 0.000258

how to convert table() to matrix in r

I finally found the answer.

DF <- data.frame(table(M))
pmfY <- apply(as.matrix.noquote(DF),2,as.numeric)

The output of pmfY is which is exactly same as table(M).

pmfY
       M Freq
 [1,]  2    1
 [2,]  3    2
 [3,]  4    3
 [4,]  5    4
 [5,]  6    5
 [6,]  7    6
 [7,]  8    5
 [8,]  9    4
 [9,] 10    3
[10,] 11    2
[11,] 12    1

r - need help converting table to matrix

Here is a suggestion. Does it work as you wish?

 ## Test dataset
 data = data.frame(col1 = c(1,2,3,4), col2 = c(0,0, 1, 0), col3 = c(1,0,0,1))
 write.table(data, "data.txt", row.names = FALSE)
 data.txt = read.table("data.txt", header = TRUE)

 data.mat = as.matrix(data.txt[,-1])  # gets rid of the leftmost column(as you requested)
 dimnames(data.mat) <-list(rep("", dim(data.mat)[1]), rep("", dim(data.mat)[2]))

This would be the output

0 1
0 0
1 0
0 1

instead of:

1 0 1
2 0 0
3 1 0
4 0 1

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 a table without any col and row name created from a matrix to data frame

We can use as.data.frame with optional parameter set to TRUE

timestp <- as.data.frame(tstp, optional = TRUE)

colnames(df)
#NULL

---

Related Topics