3

I have the following data:

request   user   group
1         1      1
4         1      1
7         1      1
5         1      2
8         1      2
1         2      3
4         2      3
7         2      3
9         2      4

I would like to extract the request-sequences and transpose these into columns.

The result should look like this:

user   group   request1   request2   request3
1      1       1          4          7
1      2       5          8          NA
2      3       1          4          7
2      4       9          NA         NA

I tried to do this with ddply (plyr) but did not come to a working solution.

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ahs85
  • 1,927
  • 2
  • 13
  • 11

2 Answers2

4
library(reshape)

# Make some fake data
dat <- data.frame(user = c(1,1,1,2,2,3), group = c(1,1,1,1,1,2), request = c(1,4,7,5,8,1))
# Add in an ordered id
newdat <- ddply(dat, .(user, group), transform, idx = paste("request", 1:length(request), sep = ""))
# Use cast to get what we want
cast(newdat, user + group ~ idx, value = .(request))

There is probably a nicer way to get what I call idx which is essentially what becomes the column title. It might be possible to do this without creating the newdat data set but this is what I thought of.

Dason
  • 60,663
  • 9
  • 131
  • 148
  • Ok, this does work quite well! Thanks a lot! One Question remains: In my original data I have up to 120 requests per user and group. Cast seems to change the order of the idx-variable like `request1 request2 request3 request4 request5 request6 request10 request11 request7` etc. How to handle that? – ahs85 Nov 24 '11 at 20:13
  • Found the answer [here](http://stackoverflow.com/questions/8266915/create-vector-with-fixed-width-fill-with-0/8267036#8267036): Instead of `idx = paste("request", 1:length(request), sep = "")`, `idx = sprintf("request_%03d", 1:length(request))` works great! – ahs85 Nov 25 '11 at 09:41
2

aggregate gets you very close:

dat <- data.frame(
    user = c(1,1,1,2,2,3,3,3,4), 
    group = c(1,1,1,1,1,2,2,2,2), 
    request = c(1,4,7,5,8,1,4,7,9)
)

aggregate(request~group + user, dat, FUN=c)

  group user request
1     1    1 1, 4, 7
2     1    2    5, 8
3     2    3 1, 4, 7
4     2    4       9
Andrie
  • 176,377
  • 47
  • 447
  • 496
  • I'm getting an error here: `Error in get(as.character(FUN), mode = "function", envir = envir) : object 'FUN' of mode 'function' was not found` – ahs85 Nov 24 '11 at 19:59