Spreading data with a shift to the top

December 2, 2022

I have data of a tennis tournament. The column name are the name of player, game the game’s number ( It is not 1,2,3 because there is a second pool ) and rank which is the rank of the player after the game.

The structure of the data is as follow

structure(list(player = c("Bob", "Luc", "Bob", "Carl", "Alex", 
"John", "Alex", "Mike", "Carl", "Alex"), game = c(1, 1, 3, 3, 
4, 4, 6, 6, 8, 8), rank = c(100, 110, 110, 120, 100, 90, 110, 
80, 110, 120)), class = "data.frame", row.names = c(NA, -10L))

Using

data %>% pivot_wider(names_from = player, values_from = rank)

I get the following result :

# A tibble: 5 x 7
   game   Bob   Luc  Carl  Alex  John  Mike
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     1   100   110    NA    NA    NA    NA
2     3   110    NA   120    NA    NA    NA
3     4    NA    NA    NA   100    90    NA
4     6    NA    NA    NA   110    NA    80
5     8    NA    NA   110   120    NA    NA

But I would like something looking like that :

# A tibble: 5 x 7
   game   Bob   Luc  Carl  Alex  John  Mike
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     1   100   110   120   100    90    80
2     2   110    NA   110   110    NA    NA
3     3    NA    NA    NA   120    NA    NA
4     4    NA    NA    NA    NA    NA    NA
5     5    NA    NA    NA    NA    NA    NA

I want the column game (to be i from 1 to n) with i corresponding to the i-th game for each of the player. And the columns representing all the players. For example, Alex played 3 times, so the 3 first row of his column should be filled as above.

Any help would be appreciated

>Solution :

data %>% group_by(player) %>% mutate(game=rank(game)) %>% pivot_wider(names_from = player, values_from = rank)

will return:

# A tibble: 3 × 7
   game   Bob   Luc  Carl  Alex  John  Mike
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     1   100   110   120   100    90    80
2     2   110    NA   110   110    NA    NA
3     3    NA    NA    NA   120    NA    NA