Using behead (unpivotr package) inside a function, reassigning the "name" argument - not working. Tidyxl

Question

I'm trying to use tidyxl and unpivotr to clean messy excel data.

I'm trying to use the "behead" command inside of a function, with the "name" argument of the "behead" command as one of the arguments of my function.

Code:

data_prep <- function(variable_col){
  
  # Read in excel cells
  cells <- xlsx_cells(paste0(data_folder, "Data_name.xlsx")),
                      include_blank_cells = TRUE) 
  
  # Cell manipulation
  cells1 <- cells %>%
    
    # Select cells to be columns
    behead("up-left", "year") %>%
    behead("left", variable_col) 

Here, as "variable_col" is one of the data_prep function arguments, I want this to be changeable and change to the desired column name (eg. dog_names).
But instead, when running the function, eg.

data_prep(variable_col = 'dog_names')

the output still has the column name as "variable_col", and not "dog_names".

Therefore, assigning the variable inside a function isn't working.

I've tried putting different quotation and speech marks etc around "variable_col", but no luck.
Anyone used unpivotr::behead in a function before and/or can help me with this?

Thanks.

Answer 1

We may escape (!!) the argument so that the object can be evaluated

library(unpivotr)
data_prep <- function(variable_col){
  
  # Read in excel cells
  cells <- xlsx_cells(paste0(data_folder, "Data_name.xlsx")),
                      include_blank_cells = TRUE) 
  
  # Cell manipulation
  cells1 <- cells %>%
    
    # Select cells to be columns
    behead("up-left", "year") %>%
    behead("left", !!variable_col) 
}

-reproducible example

x <- data.frame(a = 1:2, b = 3:4)
cells <- as_cells(x, col_names = TRUE)
> variable_col <- "Sex"
> cells %>%
+   behead("up-left", variable_col)
# A tibble: 4 × 6
    row   col data_type chr     int variable_col
  <int> <int> <chr>     <chr> <int> <chr>       
1     2     1 int       <NA>      1 a           
2     3     1 int       <NA>      2 a           
3     2     2 int       <NA>      3 b           
4     3     2 int       <NA>      4 b           
> cells %>%
+   behead("up-left", !!variable_col)
# A tibble: 4 × 6
    row   col data_type chr     int Sex  
  <int> <int> <chr>     <chr> <int> <chr>
1     2     1 int       <NA>      1 a    
2     3     1 int       <NA>      2 a    
3     2     2 int       <NA>      3 b    
4     3     2 int       <NA>      4 b