Say I have this tibble:

df <- tibble::tribble(
  ~how_bright_txt, ~how_bright_num,   ~how_hard_txt, ~how_hard_num, ~how_hot_txt, ~how_hot_num,
  "Not very much",              1L,   "Really hard",            5L,       "Cold",           1L,
       "Somewhat",              2L, "Somewhat hard",            2L, "A bit cold",           2L,
         "Medium",              3L,        "Medium",            3L,     "Medium",           3L,
    "Quite a bit",              4L,    "Quite hard",            4L,  "Quite hot",           4L,
          "A lot",              5L, "Not very hard",            1L, "Really hot",           5L
  )

I want to make new columns named with the first part of the existing column names (minus the _txt or _num prefix) and which take the value of the _txt columns but converting them to factors ordered by the corresponding _num columns.

I can do this by repeating fct_reorder inside mutate for each column, like so:

require(tidyverse)

df %>% 
    mutate(how_bright = fct_reorder(how_bright_txt, -how_bright_num),
           how_hard = fct_reorder(how_hard_txt, -how_hard_num),
           how_hot = fct_reorder(how_hot_txt, -how_hot_num)) %>%
    select(-c(ends_with("_txt"), ends_with("_num")))

But I want to streamline this and use mutate(across()). So I tried doing this:

df %>% 
    mutate(across(ends_with("_txt"), 
         ~ fct_reorder(.x, str_replace(.x, "_txt", "_num")), 
                     .names = '{stringr::str_remove({col}, "_txt")}')) %>%
    select(-c(ends_with("_txt"), ends_with("_num")))

But the ordering of the resulting factors (how_bright, how_hard, how_hot) are incorrect and don’t correspond to the ordering in the original _num columns. I have also tried replacing the str_replace call with a gsub call but I get the same output

Can anyone see what I’m doing wrong?

>Solution :

What you need is cur_column() and get(). cur_column() gives the current column name, i.e. *_txt. After str_replace() is used on it, get() searches the new name (i.e. *_num) in the current dataset and returns its values.

library(tidyverse)

df %>% 
  mutate(across(ends_with("_txt"), 
                ~ fct_reorder(.x, get(str_replace(cur_column(), "_txt", "_num"))),
                .names = "{str_remove(.col, '_txt')}"),
         .keep = "unused")

# # A tibble: 5 × 3
#   how_bright    how_hard      how_hot   
#   <fct>         <fct>         <fct>     
# 1 Not very much Really hard   Cold      
# 2 Somewhat      Somewhat hard A bit cold
# 3 Medium        Medium        Medium    
# 4 Quite a bit   Quite hard    Quite hot 
# 5 A lot         Not very hard Really hot