rename_with but predicate based on value in other variable

user_5386834 0
Viewed 89

Is there a way to rename_with but instead of the predicate function be on the column name, the predicate function would be based on a value in another variable?

Say I have a dataset as follows:

data <- tibble(home_team = c("SF", "KC", "JAX", "WAS", "BUF"),
               away_team = c("GB", "CAR", "HOU", "NYG", "SEA"),
               home_total = c(21, 25, 30, 22, 23.5),
               home_plays = c(65, 64, 63, 57, 60),
               away_total = c(30, 22, 25, 22, 25),
               away_plays = c(56, 62, 66, 59, 62))

And I am trying to get it to look something like:

finalized_data <- tibble(team = c("SF", "KC", "JAX", "WAS", "BUF", "GB", "CAR", "HOU", "NYG", "SEA"),
                         total = c(21, 25, 30, 22, 23.5, 30, 22, 25, 22, 25),
                         plays = c(65, 64, 63, 57, 60, 56, 62, 66, 59, 62))

Currently the best way I know how is with a mutate function that gets long when theres a lot of variables, and there's got to be a cleaner way to do it since its essentially a rename I'm doing based on a variable in the data.

current_way <- data %>% 
  pivot_longer(c(home_team, away_team), names_to = "team_type", values_to = "team") %>%
  mutate(total = ifelse(str_detect(team_type, "home_team"), home_total, away_total),
         plays = ifelse(str_detect(team_type, "home_team"), home_plays, away_plays)) %>%
  select(team, total, plays)

Any thoughts, or is there even a way to do it in the pivot function that I am missing?

dplyr tidyverse r
1 Answers

Here is an option with pivot_longer by making use of the column names pattern to split into columns

library(dplyr)
library(tidyr)
data %>% 
  pivot_longer(cols = everything(), names_to = c("grp", ".value"), 
           names_sep = "_") %>%
   arrange(desc(grp)) %>%
   select(-grp)

-output

# A tibble: 10 x 3
#   team  total plays
#   <chr> <dbl> <dbl>
# 1 SF     21      65
# 2 KC     25      64
# 3 JAX    30      63
# 4 WAS    22      57
# 5 BUF    23.5    60
# 6 GB     30      56
# 7 CAR    22      62
# 8 HOU    25      66
# 9 NYG    22      59
#10 SEA    25      62
user_37322710
Related