Transposing data with R

Question

I have the following dataset :

prod    month    count
sub12  2012-06  566
sub1    2012-06  10239
sub6    2012-06   524
sub12  2012-07   2360
sub1    2012-07   13853
sub6    2012-07    2352
sub12  2012-08   3950
sub1    2012-08   14738
sub6    2012-08   4104

I'm trying to have this :

prod    2012-06   2012-07
sub12  566          2360
sub1   10239       13853
sub6   524           2352

I've run the following command lines:

aperm(table(data))
data.frame(table(data))

but I'm getting the wrong results.

Where am I wrong, please?

You do not want to transpose, you want to reshape/cast. – Roland Jul 11 '13 at 10:56 — Roland, Jul 11 '13 at 10:56

score 5 · Accepted Answer · answered Jul 11 '13 at 10:54

Search for "reshape long wide".

data <- read.table(text="prod    month    count
sub12  2012-06  566
sub1    2012-06  10239
sub6    2012-06   524
sub12  2012-07   2360
sub1    2012-07   13853
sub6    2012-07    2352
sub12  2012-08   3950
sub1    2012-08   14738
sub6    2012-08   4104",header=TRUE)

library(reshape2)
dcast(prod~month,data=data)
#    prod 2012-06 2012-07 2012-08
# 1  sub1   10239   13853   14738
# 2 sub12     566    2360    3950
# 3  sub6     524    2352    4104

score 0 · Answer 2 · answered Jul 11 '13 at 11:47

0

Sticking with base R, you can use xtabs:

> xtabs(count ~ prod + month, data)
       month
prod    2012-06 2012-07 2012-08
  sub1    10239   13853   14738
  sub12     566    2360    3950
  sub6      524    2352    4104

If you prefer a data.frame as the output, wrap your xtabs with as.data.frame.matrix:

> as.data.frame.matrix(xtabs(count ~ prod + month, data))
      2012-06 2012-07 2012-08
sub1    10239   13853   14738
sub12     566    2360    3950
sub6      524    2352    4104

answered Jul 11 '13 at 11:47

A5C1D2H2I1M1N2O1R2T1

190,393
28
405
485

Or `reshape(data, direction = "wide", idvar="prod", timevar="month")`. – Roland Jul 11 '13 at 12:12
@Roland, but then with a comment like that you spoil the fun of having 10 answers to achieve the same thing! Now... [where is Aaron](http://stackoverflow.com/a/9617424/1270695)? – A5C1D2H2I1M1N2O1R2T1 Jul 11 '13 at 12:27

Transposing data with R

2 Answers2