-2

I have next DataFrame in R:

col1 col2 col3
a    W    1
a    Q    1
b    T    2
b    W    3
b    Q    1
b    Z    2
c    T    3
c    Z    1
....

I want to transform it in the next Data Frame

col1 T W Q Z
a    0 1 1 0
b    2 3 1 2
c    3 0 0 1
...

What is the most efficient way to do it in R?

Guforu
  • 3,835
  • 8
  • 33
  • 52

2 Answers2

2
reshape(df,dir='w',idvar='col1',timevar='col2');
##   col1 col3.W col3.Q col3.T col3.Z
## 1    a      1      1     NA     NA
## 3    b      3      1      2      2
## 7    c     NA     NA      3      1

If we want to match the expected output exactly (except for column order which doesn't appear to have a pattern AFAICT):

res <- reshape(df,dir='w',idvar='col1',timevar='col2');
names(res)[-1L] <- sub('.*\\.','',names(res)[-1L]);
res[is.na(res)] <- 0L;
rownames(res) <- NULL;
res;
##   col1 W Q T Z
## 1    a 1 1 0 0
## 2    b 3 1 2 2
## 3    c 0 0 3 1
bgoldst
  • 34,190
  • 6
  • 38
  • 64
2

We can use dcast from data.table to convert to 'wide' format.

library(data.table)
dcast(setDT(df1), col1~col2, value.var='col3', fill = 0)
#   col1 Q T W Z
#1:    a 1 0 1 0
#2:    b 1 2 3 2
#3:    c 0 3 0 1

Or another option is spread

library(tidyr)
spread(df1, col2, col3, fill=0)    
#  col1 Q T W Z
#1    a 1 0 1 0
#2    b 1 2 3 2
#3    c 0 3 0 1
akrun
  • 874,273
  • 37
  • 540
  • 662