How do I use pivot_longer and/or pivot_wider to restructure my data?

Question

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!

Answer 1

  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