Re-ordering dataframe by switching from rows to columns

Question

I have data in the following format

V1    V2    V3    V4
a      1      A      xyz
a      3      B      xyz
a      2      C      xyz
a      1      D      xyz
b      4      A      abc
b      3      B      abc
b      8      C      abc
b      5      D      abc

and I'd like to end up with a dataframe looking like this:

U1    U2    A      B      C      D
a      xyz     1      3      2      1
b      abc     4      3      8      5

While I am new to R, I tried playing around with reshape to get to my result but couldn't. Any pointers would be great, thanks.

score 3 · Accepted Answer · answered Jun 07 '16 at 18:13

3

library(tidyr)
spread(df, V3, V2)
  V1  V4 A B C D
1  a xyz 1 3 2 1
2  b abc 4 3 8 5

Where df is your original data frame.

answered Jun 07 '16 at 18:13

Psidom

209,562
33
339
356

I need to go off and read up on tidyr. Many thanks! – sdsouza Jun 07 '16 at 18:24

score 1 · Answer 2 · answered Jun 07 '16 at 18:24

1

You can use reshape as

reshape(df, idvar=c("V1", "V4"), timevar="V3", direction="wide")

#    V1  V4  V2.A  V2.B V2.C V2.D
#1   a  xyz    1    3    2    1
#5   b  abc    4    3    8    5

answered Jun 07 '16 at 18:24

Ronak Shah

377,200
20
156
213

score 1 · Answer 3 · answered Jun 07 '16 at 18:30

library(data.table)
# read data to make a complete example
dt <- fread("
        V1    V2    V3    V4
        a      1      A      xyz
        a      3      B      xyz
        a      2      C      xyz
        a      1      D      xyz
        b      4      A      abc
        b      3      B      abc
        b      8      C      abc
        b      5      D      abc")

# transform from long to wide format
dcast(dt, V1 + V4 ~ V3, value.var = "V2")

returns

   V1  V4 A B C D
1:  a xyz 1 3 2 1
2:  b abc 4 3 8 5

Re-ordering dataframe by switching from rows to columns

3 Answers3