I have the following data:
dat <- tribble(
~name, ~current_hit, ~future_hit, ~current_raw_power, ~future_raw_power, ~current_game_power, ~future_game_power,
"Jackson Holliday", 45, 60, 50, 60, 25, 60,
"James Wood", 30, 35, 70, 80, 55, 80)
My desired output is:
player names current future
"Jackson Holliday" hit 45 60
"Jackson Holliday" game_power 50 60
"Jackson Holliday" raw_power 25 60
"James Wood" hit 30 35
"James Wood" raw_power 70 80
"James Wood" game_power 55 80
I've tried referencing a similar question here: pivot_longer into multiple columns. However, this use case is slightly different from mine and the use of regular expressions is a bit beyond my coding skills at the moment.
My intent is to add additional current/future pairings to the dataset later in time, but I figured I'd start with a simpler example from which I can scale a solution.
Appreciate any and all help!
dat %>%
pivot_longer(-name, names_to = "names") %>%
mutate(tmp = sub("(\\w)_.*", "\\1", names),
names = sub("current_|future_", "", names)) %>%
pivot_wider(names_from = tmp, values_from = value) %>%
rename(Player = name)
# A tibble: 6 × 4
Player names current future
<chr> <chr> <dbl> <dbl>
1 Jackson Holliday hit 45 60
2 Jackson Holliday raw_power 50 60
3 Jackson Holliday game_power 25 60
4 James Wood hit 30 35
5 James Wood raw_power 70 80
6 James Wood game_power 55 80