Restructuring command data in R
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 like 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 worked in R but cannot figure it out.
FYI - 4 is not the maximum number of people in the group. There are sometimes 30 people in a group ... I just didn't want to type an example that is big here. Also, there are many more variables in the dataset, but they are actually the only ones you need to answer my questions for (I think).
source to share
You can use split-apply-comb to create this dataframe in the R database. First I would calculate the number of columns to create, then I would actually create the dataframe, 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
source to share
rbind.fill.matrix can do this with name loss. I think other reshape2 or plyr functions would 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 it 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
source to share
Lots of good answers. Here's a short one that only uses the R base. Two simple steps:
First add the "Player" column to your data:
dat <- transform(dat, Player = ave(Team, Team, FUN = seq_along))
head(dat)
# Person Team Player
# 1 36471430 15326406 1
# 2 37242356 15326406 2
# 3 34945710 15326406 3
# 4 29141024 15326406 4
# 5 10323768 15326124 1
# 6 647293 15326124 2
Then reformat from long to wide:
reshape(dat, idvar = "Team", timevar = "Player", direction = "wide")
# Team Person.1 Person.2 Person.3 Person.4
# 1 15326406 36471430 37242356 34945710 29141024
# 5 15326124 10323768 647293 32358093 2144524
# 9 6692854 35199422 32651004 32309524 22701991
# 13 8540767 32343507 8343828 22669737 NA
# 16 6596680 1128141 34840462 NA NA
# 18 6596523 513193 8748403 NA NA
# 20 15326509 29284130 8554552 NA NA
# 22 15326628 33051835 32339184 32979394 30357112
Brought to you locally, from Atlanta GA! Hooray!
source to share
Here's another approach. ana - your data
library(dplyr)
library(tidyr)
ana %>%
group_by(Team) %>%
mutate(count = row_number(Person)) %>%
do(spread(., count,Person))
Team 1 2 3 4
1 6596523 513193 8748403 NA NA
2 6596680 1128141 34840462 NA NA
3 6692854 22701991 32309524 32651004 35199422
4 8540767 8343828 22669737 32343507 NA
5 15326124 647293 2144524 10323768 32358093
6 15326406 29141024 34945710 36471430 37242356
7 15326509 8554552 29284130 NA NA
8 15326628 30357112 32339184 32979394 33051835
source to share