Restructure Team Data in R

Question

I have a dataset that looks something like this:

Person      Team
36471430    15326406
37242356    15326406
34945710    15326406
29141024    15326406
10323768    15326124
647293      15326124
32358093    15326124
2144524     15326124
35199422    6692854
32651004    6692854
32309524    6692854
22701991    6692854
32343507    8540767
8343828     8540767
22669737    8540767
1128141     6596680
34840462    6596680
513193      6596523
8748403     6596523
29284130    15326509
8554552     15326509
33051835    15326628
32339184    15326628
32979394    15326628
30357112    15326628

I would like this data to look this this:

Team        Person 1    Person 2    Person 3    Person 4
15326406    36471430    37242356    34945710    29141024
15326124    10323768    647293      32358093    2144524
6692854     35199422    32651004    32309524    22701991
8540767     32343507    8343828     22669737    NA
6596680     1128141     34840462    NA          NA
6596523     513193      8748403     NA          NA
15326509    29284130    8554552     NA          NA
15326628    33051835    32339184    32979394    30357112

I have been working in R but I can't figure it out.

FYI - 4 is not the maximum amount of people per group. There are sometimes as many as 30 people per group...I just didn't want to type an example that large out here. Also, there are many more variables in the dataset, but these are really the only ones you need to answer my questions (I think).

Answer 1

The rbind.fill.matrix can do that with the loss of names. I think that other reshape2 or plyr functions will be better:

> plyr::rbind.fill.matrix( tapply(dat$Person, dat$Team, matrix, nrow=1) )
            1        2        3        4
[1,]   513193  8748403       NA       NA
[2,]  1128141 34840462       NA       NA
[3,] 35199422 32651004 32309524 22701991
[4,] 32343507  8343828 22669737       NA
[5,] 10323768   647293 32358093  2144524
[6,] 36471430 37242356 34945710 29141024
[7,] 29284130  8554552       NA       NA
[8,] 33051835 32339184 32979394 30357112

I think this might be better in some ways:

library(reshape2)
dcast(dat, Team ~ .,  list)
Using Team as value column: use value.var to override.
      Team                                     NA
1  6596523                       6596523, 6596523
2  6596680                       6596680, 6596680
3  6692854     6692854, 6692854, 6692854, 6692854
4  8540767              8540767, 8540767, 8540767
5 15326124 15326124, 15326124, 15326124, 15326124
6 15326406 15326406, 15326406, 15326406, 15326406
7 15326509                     15326509, 15326509
8 15326628 15326628, 15326628, 15326628, 15326628

Answer 2

You could use split-apply-combine to build this data frame in base R. First I would compute the number of columns to create, then I would actually build the data frame, and finally I would create the column names.

num.person <- max(table(dat$Team))
teams <- do.call(rbind, lapply(split(dat, dat$Team), function(x) {
  c(x$Team[1], x$Person, rep(NA, num.person-nrow(x)))
}))
colnames(teams) <- c("Team", paste("Person", seq(num.person)))
teams
#              Team Person 1 Person 2 Person 3 Person 4
# 6596523   6596523   513193  8748403       NA       NA
# 6596680   6596680  1128141 34840462       NA       NA
# 6692854   6692854 35199422 32651004 32309524 22701991
# 8540767   8540767 32343507  8343828 22669737       NA
# 15326124 15326124 10323768   647293 32358093  2144524
# 15326406 15326406 36471430 37242356 34945710 29141024
# 15326509 15326509 29284130  8554552       NA       NA
# 15326628 15326628 33051835 32339184 32979394 30357112