0

I looked around the site, but I could not find this specific issue. I'm trying to prepare my dataset for further analysis, but I can't seem to fix something.

I have a list of Players and the club they currently play at:

PlayerID PlayerName        CurrentClub
1        Roland Alberg     ADO Den Haag
2        John Goossens     Feyenoord
3        Michael de Leeuw  Feyenoord
4        Kenny van der Weg NAC Breda
5        Alex Schalk       NAC Breda

Where I want to get is:

NewID CurrentClub       Player1             Player2

1     ADO Den Haag      Roland Alberg       NA
2     Feyenoord         John Goossens       Michael de Leeuw
3     NAC Breda         Kenny van der Weg   Alex Schalk

I've tried various methods with melt, group_by and transpose, but I never got it this result.

Does anybody know how to do this?

BdJ
  • 47
  • 4

2 Answers2

3

A combination of row_number and group_by should do the trick. Here is my solution: 

df <- tibble(PlayerID = c(1,2,3,4,5),
   PlayerName = c("Alberg", "Goossens","Leeuw","van der Weg","Schalk"),
   CurrentClub = c("ADO Den Haag", "Feyenoord", "Feyenoord", "NAC Breda", "NAC Breda"))

 new_df <- df %>% group_by(CurrentClub) %>% select(-PlayerID) %>%
 mutate(player_number = paste0("Player ",row_number())) %>%
 spread(player_number, PlayerName)

 new_df
Henry Cyranka
  • 2,783
  • 1
  • 14
  • 20
  • 2
    Oh, I see you suggested basically the same solution as me. row_number is a good call. Took me a while to realize that playerID was preventing from my solution to work... :) – iod Oct 30 '18 at 17:35
  • 1
    Bingo! Thanks a bunch Harro. – BdJ Oct 30 '18 at 18:09
0
df[,-1] %>% 
group_by(CurrentClub) %>% 
mutate(Player=seq(1:n())) %>% 
spread(Player, PlayerName, sep="")
iod
  • 7,094
  • 2
  • 15
  • 33