Reshape row into column of the 2d array in a matrix in R

Question

Consider the following matrix:

M <- cbind(c("ID001", "ID003", "ID002", "ID002", "ID003"),
           c("BK101", "BK145", "BK101", "BK125", "BK101"), 
           c(6, 3, 2, 7, 3))

I want to reshape the matrix, so I get the following matrix:

       BK101 BK125 BK145
ID001    6     0     0
ID002    2     7     0
ID003    3     0     3

I have tried with:

reshape(M, idvar=[,1], timevar=[,2])

But that does not work.

Any particular reason why it's a matrix and not a dataframe ? Seems like reshaping long to wide problem https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format — Ronak Shah, Oct 04 '19 at 10:00

score 2 · Answer 1 · answered Oct 04 '19 at 10:01

2

If you name your columns and put them in a data frame:

M <- data.frame("a"=c("ID001","ID003","ID002","ID002","ID003"),
                "b"=c("BK101","BK145","BK101","BK125","BK101"),
                "c"=c(6,3,2,7,3))
xtabs(c~a+b,data=M)

       b
a       BK101 BK125 BK145
  ID001     6     0     0
  ID002     2     7     0
  ID003     3     0     3

answered Oct 04 '19 at 10:01

user2974951

9,535
1
17
24

Or `as.data.frame(unclass(xtabs(as.numeric(as.character(V3)) ~ V1 + V2, data=as.data.frame(M))))` (Note: OP's `M`). – jay.sf Oct 04 '19 at 10:11
1

Thanks. That is perfect, jay.sf :-) – Michael Oct 04 '19 at 11:41

score 2 · Answer 2 · answered Oct 04 '19 at 10:36

2

The general pattern when creating a matrix:

First create an empty matrix where you will store your results.

X <- matrix(0, nrow=length(unique(M[,1])), ncol=length(unique(M[,2])),
            dimnames=list(sort(unique(M[,1])), sort(unique(M[,2]))))


X
      BK101 BK125 BK145
ID001     0     0     0
ID002     0     0     0
ID003     0     0     0

Then add the data.

X[M[,1:2]] <- as.numeric(M[,3])

X
      BK101 BK125 BK145
ID001     6     0     0
ID002     2     7     0
ID003     3     0     3

answered Oct 04 '19 at 10:36

Karolis Koncevičius

9,417
9
56
89

Most likely this is because your `M` is a `data.frame` and not a `matrix`, as indicated by the answer. Try changing the last line to `x[as.matrix(M[,1:2])] <- as.numeric(M[,3])` – Karolis Koncevičius Oct 04 '19 at 11:40

score 1 · Answer 3 · answered Oct 04 '19 at 15:47

Another option with dcast

library(reshape2)
dcast(M, a~b, fill = 0)
#      a BK101 BK125 BK145
#1 ID001     6     0     0
#2 ID002     2     7     0
#3 ID003     3     0     3

Or with pivot_wider from tidyr

library(dplyr)
library(tidyr)
 M %>%
     pivot_wider(names_from = b, values_from = c, values_fill = list(c = 0))
# A tibble: 3 x 4
#  a     BK101 BK145 BK125
#  <fct> <dbl> <dbl> <dbl>
#1 ID001     6     0     0
#2 ID003     3     3     0
#3 ID002     2     0     7

data

M <- data.frame("a"=c("ID001","ID003","ID002","ID002","ID003"),
                "b"=c("BK101","BK145","BK101","BK125","BK101"),
                "c"=c(6,3,2,7,3))

Reshape row into column of the 2d array in a matrix in R

3 Answers3

data