Convert dataframe to m x n table

Question

I would like to convert my dataframe into a m x n table. Code and view for a simplified version of my original data as below:

set.seed(1)
dat <- data.frame(x = rep(c("A","B","C","D","E"),2),
                  y = c("R","S",rep(c("P","Q","R","S"),2)),
                  val = sample(2:20,10))

   x y val
1  A R   7
2  B S   8
3  C P  11
4  D Q  16
5  E R   5
6  A S  14
7  B P  15
8  C Q   9
9  D R  19
10 E S   2

Code for the desired result:

dat2 <- data.frame(x=c("A","B","C","D","E"),
                   P=c(NA,15,11,NA,NA),
                   Q=c(NA,NA,9,16,NA),
                   R=c(7,NA,NA,19,5),
                   S=c(14,8,NA,NA,2)
                   )

  x  P  Q  R  S
1 A NA NA  7 14
2 B 15 NA NA  8
3 C 11  9 NA NA
4 D NA 16 19 NA
5 E NA NA  5  2

So basically, I want to turn all unique values from y into columns and add the corresponding values from val, resulting in a 5x4 matrix.

Try `library(tidyverse); dat %>% spread(y, val)` – AntoniosK Aug 30 '18 at 12:08 — AntoniosK, Aug 30 '18 at 12:08

score 2 · Accepted Answer · answered Aug 30 '18 at 12:23

You can use dcast() from data.table:

library(data.table)
set.seed(1)
dat <- data.frame(x = rep(c("A","B","C","D","E"),2),
                  y = c("R","S",rep(c("P","Q","R","S"),2)),
                  val = sample(2:20,10))


dcast(dat, x ~ y, value.var = "val")
  x  P  Q  R  S
1 A NA NA  7 14
2 B 15 NA NA  8
3 C 11  9 NA NA
4 D NA 16 19 NA
5 E NA NA  5  2

Convert dataframe to m x n table

1 Answers1