Reformatting data from columns to rows

Question

I have a data set that looks like this:

ID  C1 C2
A   S1  1
A   S2  1
A   S3  2
A   S4  2
B   S1  2
B   S2  2
B   S3  2
B   S4  2
C   S1  2
C   S2  2
C   S3  1
C   S4  1

And would like to reformat to:

ID S1 S2 S3 S4
A 1 1 2 2
B 2 2 2 2
C 2 2 1 1

Any advice?

Yes I would like to do this in perl. Would you recommend using hash? — mxttgen31, Mar 08 '16 at 18:44
If you're using Perl, why did you add the `r` tag to your question? I would recommend [searching for a similar question](https://www.google.com/search?q=perl+columns+to+rows+site%3Astackoverflow.com&ie=utf-8&oe=utf-8) and if you can't find a working answer after a few hours of research and coding attempts, post a new question with only the `perl` tag. — ThisSuitIsBlackNot, Mar 08 '16 at 18:51

akrun · Answer 1 · 2016-03-08T18:22:36.690

1

In R, we can use dcast

library(reshape2)
dcast(df1, ID~C1, value.var='C2')
#   ID S1 S2 S3 S4
# 1  A  1  1  2  2
# 2  B  2  2  2  2
# 3  C  2  2  1  1

Or using base R

with(df1, tapply(C2, list(ID, C1), FUN=I))

Or

xtabs(C2~ID+C1, df1)

edited Mar 08 '16 at 18:22

answered Mar 08 '16 at 18:16

akrun

score 1 · Answer 2 · answered Mar 08 '16 at 18:19

1

One alternative using tidyr::spread():

library(tidyr)
spread(df, C1, C2)
  ID S1 S2 S3 S4
1  A  1  1  2  2
2  B  2  2  2  2
3  C  2  2  1  1

answered Mar 08 '16 at 18:19

DatamineR

2 Answers2