Another aggregation

Question

(sorry if the title is not very informative: I do not know how best to determine this question)

I have data in the following form:

In each group, I have one pre value and one or two post values. I would like to convert this table to the following:

I thought to group the data with something like:

 aggregate(mydata, by = group, FUN = myfunction) 

or

 ddply(mydata, .(group), .fun = myfunction) 

and handle the elements of each group in my function. But I don’t see how to do this, because I need to pass both type and value at the same time. Is there a better way to do this?

Update: quick and dirty data sample:

 mydata <- data.frame(group = sample(letters[1:5], 10, replace = TRUE), type = sample(c("pre", "post"), 10, replace = TRUE), value = rnorm(10)) 

Answer 1

Try something like this:

 mydf <- data.frame(group = c("A", "A", "B", "B", "C", "C", "C", "D", "D", "E", "E"), type = c("pre", "post", "pre", "post", "pre", "post", "post", "pre", "post", "pre", "post"), value = 1:11) times <- with(mydf, ave(value, group, type, FUN = seq_along)) xtabs(value ~ group + interaction(type, times), mydf) # interaction(type, times) # group post.1 pre.1 post.2 pre.2 # A 2 1 0 0 # B 4 3 0 0 # C 6 5 7 0 # D 9 8 0 0 # E 11 10 0 0 

Or:

 times <- with(mydf, ave(value, group, type, FUN = seq_along)) mydf$timevar <- interaction(mydf$type, times) reshape(mydf, direction = "wide", idvar = "group", timevar="timevar", drop="type") # group value.pre.1 value.post.1 value.post.2 # 1 A 1 2 NA # 3 B 3 4 NA # 5 C 5 6 7 # 8 D 8 9 NA # 10 E 10 11 NA 

The key in both solutions is to create a “time” variable, which is represented by a combination of “type” and a variable sequence that can be created using ave .

For completeness, here is the dcast from "reshape2":

 times <- with(mydf, ave(value, group, type, FUN = seq_along)) library(reshape2) dcast(mydf, group ~ type + times) # group post_1 post_2 pre_1 # 1 A 2 NA 1 # 2 B 4 NA 3 # 3 C 6 7 5 # 4 D 9 NA 8 # 5 E 11 NA 10