Use mutate with starts_with on columns and if_else

user_10413759 0
Viewed 603

I have multiple Tibbles and I would like to be mutate them all with a function. The issue is that the column names are partially different: That is what I want to apply

mpg %>% mutate(model_QQ = if_else(year == 1999, 0 , if_else(cty > 20, 1, -1)))

But the tibbles have colnames like:

c("audi","nissan","subaru") %>% set_names() %>% map(~mpg %>% rename_at(vars(model:class), list(~str_c(.,"_",!!quo(.x)))))

I was thinking to make something like:

mpg %>% mutate(model_QQ = if_else(starts_with("year") == 1999, 0 , if_else(starts_with("cty") > 20, 1, -1)))

but these are working only inside the select function. Any suggestions?

EDIT I remake the code with case when, maybe is made more clear that way about what I'm looking for:

mpg %>% 
mutate(
model_QQ = case_when(

starts_with("year") == 1999 ~ 0L,
  starts_with("cty") > 20 ~ 1L,
  starts_with("cty") <= 20 ~ -1L
))
tidyverse tidy r
2 Answers

You could do:

mpg %>% 
  mutate(across(starts_with("year"), ~if_else(.x >= 1999, 0 , 1)), 
         across(starts_with("cty"),  ~if_else(.x > 20, 1, -1)))
#> # A tibble: 234 x 11
#>    manufacturer model  displ  year   cyl trans  drv     cty   hwy fl    class
#>    <chr>        <chr>  <dbl> <dbl> <int> <chr>  <chr> <dbl> <int> <chr> <chr>
#>  1 audi         a4       1.8     0     4 auto(~ f        -1    29 p     comp~
#>  2 audi         a4       1.8     0     4 manua~ f         1    29 p     comp~
#>  3 audi         a4       2       0     4 manua~ f        -1    31 p     comp~
#>  4 audi         a4       2       0     4 auto(~ f         1    30 p     comp~
#>  5 audi         a4       2.8     0     6 auto(~ f        -1    26 p     comp~
#>  6 audi         a4       2.8     0     6 manua~ f        -1    26 p     comp~
#>  7 audi         a4       3.1     0     6 auto(~ f        -1    27 p     comp~
#>  8 audi         a4 qu~   1.8     0     4 manua~ 4        -1    26 p     comp~
#>  9 audi         a4 qu~   1.8     0     4 auto(~ 4        -1    25 p     comp~
#> 10 audi         a4 qu~   2       0     4 manua~ 4        -1    28 p     comp~
#> # ... with 224 more rows

Or if you want them renamed year_modelQQ and cty_modelQQ you can do:

mpg %>% 
  mutate(across(starts_with("year"), list(modelQQ = ~if_else(.x >= 1999, 0 ,1))), 
         across(starts_with("cty"),  list(modelQQ = ~if_else(.x > 20, 1, -1))))
#> # A tibble: 234 x 13
#>    manufacturer model displ  year   cyl trans drv     cty   hwy fl    class year_modelQQ
#>    <chr>        <chr> <dbl> <int> <int> <chr> <chr> <int> <int> <chr> <chr>        <dbl>
#>  1 audi         a4      1.8  1999     4 auto~ f        18    29 p     comp~            0
#>  2 audi         a4      1.8  1999     4 manu~ f        21    29 p     comp~            0
#>  3 audi         a4      2    2008     4 manu~ f        20    31 p     comp~            0
#>  4 audi         a4      2    2008     4 auto~ f        21    30 p     comp~            0
#>  5 audi         a4      2.8  1999     6 auto~ f        16    26 p     comp~            0
#>  6 audi         a4      2.8  1999     6 manu~ f        18    26 p     comp~            0
#>  7 audi         a4      3.1  2008     6 auto~ f        18    27 p     comp~            0
#>  8 audi         a4 q~   1.8  1999     4 manu~ 4        18    26 p     comp~            0
#>  9 audi         a4 q~   1.8  1999     4 auto~ 4        16    25 p     comp~            0
#> 10 audi         a4 q~   2    2008     4 manu~ 4        20    28 p     comp~            0
#> # ... with 224 more rows, and 1 more variable: cty_modelQQ <dbl>

Edit

With the updated information, this should do the trick:

mpg %>% 
  mutate(across(starts_with("year"), list(A = ~1 - (.x ==  1999))),
         across(starts_with("cty"), list(A = ~ -1 + 2*(.x > 20))),
         modelQQ = ifelse(year_A == 0, 0, cty_A)) %>%
  select(-ends_with("_A")
user_125003150

We could use case_when

library(dplyr)
mpg %>% 
   mutate(across(starts_with("year"), ~case_when(.x == 1999 ~ 0, TRUE ~ 1)), 
     across(starts_with("cty"),  ~case_when(.x > 20 ~ 1,  TRUE ~ -1)))

If it is to create a single column

mpg %>% 
   mutate(model_QQ = case_when(select(., starts_with("year")) == 
     1999 ~ 0L, 
      select(., starts_with('cty')) > 20 ~ 1L, TRUE ~ -1L))

-output

# A tibble: 234 x 12
#   manufacturer model      displ  year   cyl trans      drv     cty   hwy fl    class   model_QQ
#   <chr>        <chr>      <dbl> <int> <int> <chr>      <chr> <int> <int> <chr> <chr>      <int>
# 1 audi         a4           1.8  1999     4 auto(l5)   f        18    29 p     compact        0
# 2 audi         a4           1.8  1999     4 manual(m5) f        21    29 p     compact        0
# 3 audi         a4           2    2008     4 manual(m6) f        20    31 p     compact       -1
# 4 audi         a4           2    2008     4 auto(av)   f        21    30 p     compact        1
# 5 audi         a4           2.8  1999     6 auto(l5)   f        16    26 p     compact        0
# 6 audi         a4           2.8  1999     6 manual(m5) f        18    26 p     compact        0
# 7 audi         a4           3.1  2008     6 auto(av)   f        18    27 p     compact       -1
# 8 audi         a4 quattro   1.8  1999     4 manual(m5) 4        18    26 p     compact        0
# 9 audi         a4 quattro   1.8  1999     4 auto(l5)   4        16    25 p     compact        0
#10 audi         a4 quattro   2    2008     4 manual(m6) 4        20    28 p     compact       -1
# … with 224 more rows
user_37322710
Related