Transpose data by groups in R

Question

I have data in the following structure:

x <- read.table(header=T, text=" XYDS ae 1 10 ae 2 20 af 1 50 bc 1 40 bc 2 30 bc 3 60 bd 1 10 bd 2 20") 

And I want to get the following result:

 XY 1 2 3 ae 10 20 af 50 bc 40 30 60 bd 10 20 

For each combination of columns X and Y I would like to transpose the data in column S in order in column D

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

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

As a 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 in empty entries 0 instead of NA (by default), then

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

Answer 2

The solution in the R base:

 > reshape(x, timevar="D", idvar=c("X","Y"), direction="wide") XY S.1 S.2 S.3 1 ae 10 20 NA 3 af 50 NA NA 4 bc 40 30 60 7 bd 10 20 NA 

Answer 3

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

 ftable(xtabs(S ~ ., x)) # D 1 2 3 # XY # ac 0 0 0 # d 0 0 0 # e 10 20 0 # f 50 0 0 # bc 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))) # XY X1 X2 X3 # 1 ae 10 20 0 # 3 af 50 0 0 # 4 bc 40 30 60 # 7 bd 10 20 0