Transpose data by groups in R

Question

I have data in the following structure:

x <- read.table(header=T, text="
X Y D S
a e 1 10
a e 2 20
a f 1 50
b c 1 40
b c 2 30
b c 3 60
b d 1 10 
b d 2 20")

And I want to get the following result:

X Y   1   2   3
a e  10  20
a f  50
b c  40  30  60
b d  10  20

For every combination of columns X and Y I would like to transpose data in column S by order in column D.

I thought xtabs() will work, but I don't think so, my best version is:

xtabs(formula=S~Y+D,data=x)

With result:

   D
Y    1  2  3
  c 40 30 60
  d 10 20  0
  e 10 20  0
  f 50  0  0

Answer 1

require(reshape2)
dcast(x, X + Y ~ D, value.var="S")

If you want to fill empty entries with 0 instead of NA (which is the default), then,

dcast(x, X + Y ~ D, value.var="S", fill=0)

Answer 2

A solution in base R:

> reshape(x, timevar="D", idvar=c("X","Y"), direction="wide")
  X Y S.1 S.2 S.3
1 a e  10  20  NA
3 a f  50  NA  NA
4 b c  40  30  60
7 b d  10  20  NA

Answer 3

The other two answers are very good, but for what it's worth, since you mentioned you started your attempts with xtabs, you can get close to what you were looking for with a combination of xtabs and ftable. However, the result will include all factor levels

ftable(xtabs(S ~ ., x))
#     D  1  2  3
# X Y           
# a c    0  0  0
#   d    0  0  0
#   e   10 20  0
#   f   50  0  0
# b c   40 30 60
#   d   10 20  0
#   e    0  0  0
#   f    0  0  0

Alternatively, you can do something like this:

data.frame(unique(x[1:2]), 
           as.data.frame.matrix(xtabs(S ~ do.call(paste, x[1:2]) + D, x)))
#   X Y X1 X2 X3
# 1 a e 10 20  0
# 3 a f 50  0  0
# 4 b c 40 30 60
# 7 b d 10 20  0