Let's say I want to know which of four basketball players is the best and I set up a little tournament where two players play each other 1 vs 1 and I record a set of stats
#rm(list=ls())
set.seed(1234)
# some made up scores from my tournament
df <- data.frame(
player1 = c("a", "a", "b", "c", "d", "d"),
player2 = c("b", "c", "d", "b", "a", "c"),
date = c("2021-01-01", "2021-01-02", "2021-01-04", "2021-01-05", "2021-01-06", "2021-01-08"),
p1_dunks = sample(c(4:11), 6, replace = TRUE),
p2_dunks = sample(c(3:12), 6, replace = TRUE),
p1_blocks = sample(c(8:10), 6, replace = TRUE),
p2_blocks = sample(c(10:12), 6, replace = TRUE),
p1_threepointers = sample(c(2:7), 6, replace = TRUE),
p2_threepointers = sample(c(1:5), 6, replace = TRUE)
)
In order to calculate how well a player has been doing by any point of the tournament, I can pivot this to longer twice and replace the count of each stat with the cumulative sum of each count
# cast to long and get cumulative stats per player
melted_df <- df %>%
pivot_longer(cols = starts_with(c("p1", "p2")), names_to = "stat", values_to = "number") %>%
pivot_longer(cols = starts_with("player"), names_to = "player", values_to = "name") %>%
filter(
(player == "player1" & grepl("^p1", stat)) |
(player == "player2" & grepl("^p2", stat))
) %>%
arrange(date) %>%
group_by(player, stat) %>%
mutate(number = cumsum(number))
then I can query against this easily enough
melted_df %>%
filter(date < "2021-01-05") %>%
filter(!duplicated(name)) %>%
filter(grepl("dunks$", stat))
but let's say for my use case I need to then coerce this long format data back to its original form (with player1, player2, then the stats for each player 1 and player 2). I can try
# try to cast back to original format
back_to_wider_df <- melted_df %>%
pivot_wider(names_from = "player", values_from = "name") %>%
pivot_wider(names_from = "stat", values_from = "number")
but this instead gives a data frame which is 'offset' per match with a row half full of NA values:
> head(back_to_wider_df)
# A tibble: 6 × 9
date player1 player2 p1_dunks p1_blocks p1_threepointers p2_dunks p2_blocks p2_threepointers
<chr> <chr> <chr> <int> <int> <int> <int> <int> <int>
1 2021-01-01 a NA 7 9 6 NA NA NA
2 2021-01-01 NA b NA NA NA 11 11 4
3 2021-01-02 a NA 18 18 9 NA NA NA
4 2021-01-02 NA c NA NA NA 18 22 8
5 2021-01-04 b NA 23 27 15 NA NA NA
6 2021-01-04 NA d NA NA NA 26 32 11
Is there a simple way to fix this back to the original shape such that the first three rows should read:
> df
date player1 player2 p1_dunks p1_blocks p1_threepointers p2_dunks p2_blocks p2_three_pointers
1 2021-01-01 a b 7 9 6 11 11 4
2 2021-01-02 a c 18 18 9 18 22 8
3 2021-01-04 b d 23 27 15 26 32 11
thanks,