CODEWITHSUNDEEP
rdplyrtidyverseacross
nefosl
nefosl

Reputation: 416

Can you use dplyr across() to iterate across pairs of columns?

I have 18 pairs of variable and I would like to do pair-wise math on them to calculate 18 new variables. The across() function in dplyr is quite handy when applying a formula to one column. Is there a way to apply across() to pairs of columns?

Tiny example with simple division of 2 variables (my actual code will be more complex, some ifelse, ...):

library(tidyverse)
library(glue)

# filler data
df <- data.frame("label" = c('a','b','c','d'),
                 "A" = c(4, 3, 8, 9),
                 "B" = c(10, 0, 4, 1),
                 "error_A" = c(0.4, 0.3, 0.2, 0.1),
                 "error_B" = c(0.3, 0, 0.4, 0.1))

# what I want to have in the end 
# instead of just 2 (A, B), I have 18
df1 <- df %>% mutate(
  'R_A' = A/error_A,
  'R_B' = B/error_B
)

# what I'm thinking about doing to use both variables A and error_A to calculate the new column
df2 <- df %>% mutate(
  across(c('A','B'),
         ~.x/{HOW DO I USE THE COLUMN WHOSE NAME IS glue('error_',.x)}
         .names = 'R_{.col}'
)

Upvotes: 13

Views: 1828

Answers (5)

G. Grothendieck
G. Grothendieck

Reputation: 270045

1) The dplyover package on github has a convenient across2 which can be used here:

# library(remotes)
# install_github("TimTeaFan/dplyover")

library(dplyr)
library(dplyover)

df %>%
  mutate(across2(A:B, starts_with("error"), "/", .names = "R_{xcol}"))

giving

  label A  B error_A error_B R_A      R_B
1     a 4 10     0.4     0.3  10 33.33333
2     b 3  0     0.3     0.0  10      NaN
3     c 8  4     0.2     0.4  40 10.00000
4     d 9  1     0.1     0.1  90 10.00000

2) Alternately use map2_dfr in purrr

library(dplyr)
library(purrr)

df %>%
  mutate(map2_dfr(pick(A:B), pick(starts_with("error")), `/`) %>% 
    rename_with(~ paste0("R_", .x))
  )

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 389205

For such cases I find base R solution straight-forward and efficient too. It doesn't require to loop over columns or unique values. You define two groups of column and divide them directly.

For the example that you have shared we can identify "A" and "B" columns by looking for columns names that have only one character in them.

cols <- grep('^.$', names(df), value = TRUE)
error_cols <- grep('error', names(df), value = TRUE)

df[paste0('R_', cols)] <- df[cols]/df[error_cols]
df

#  label A  B error_A error_B R_A  R_B
#1     a 4 10     0.4     0.3  10 33.3
#2     b 3  0     0.3     0.0  10  NaN
#3     c 8  4     0.2     0.4  40 10.0
#4     d 9  1     0.1     0.1  90 10.0

Upvotes: 2

tmfmnk
tmfmnk

Reputation: 40171

One option could be:

df %>%
 mutate(across(c(A, B), .names = "R_{col}")/across(starts_with("error")))

  label A  B error_A error_B R_A      R_B
1     a 4 10     0.4     0.3  10 33.33333
2     b 3  0     0.3     0.0  10      NaN
3     c 8  4     0.2     0.4  40 10.00000
4     d 9  1     0.1     0.1  90 10.00000

Upvotes: 7

TimTeaFan
TimTeaFan

Reputation: 18581

I like akruns answer above, especially the approach with cur_column(). Interestingly, cur_column() can't be used with {rlang}'s evaluation (!! sym(paste0("error_", cur_column()))), but get is a nice workaround.

Just to add one more approach, which also works under dpylr < 1.0.0. I usually use a mutate custom function together with purrr::reduce(). In this function x is your string stem and you construct all variables you want to access with !! sym(paste0(...)). On the left hand-side you can just use {rlang}'s glue syntax.

You apply this custom function by calling reduce() on the vector of strings and your data.frame goes in the .init = . argument.

library(tidyverse)
library(glue)


# filler data
df <- data.frame("label" = c('a','b','c','d'),
                 "A" = c(4, 3, 8, 9),
                 "B" = c(10, 0, 4, 1),
                 "error_A" = c(0.4, 0.3, 0.2, 0.1),
                 "error_B" = c(0.3, 0, 0.4, 0.1))

gen_vars1 <- function(df, x) {
  
  mutate(df,
         "R_{x}" := !! sym(x) / !! sym(paste0("error_", x)))
}

df %>% 
  reduce(c("A", "B"), gen_vars1, .init = .)
#>   label A  B error_A error_B R_A      R_B
#> 1     a 4 10     0.4     0.3  10 33.33333
#> 2     b 3  0     0.3     0.0  10      NaN
#> 3     c 8  4     0.2     0.4  40 10.00000
#> 4     d 9  1     0.1     0.1  90 10.00000

Created on 2021-01-02 by the reprex package (v0.3.0)

I once opened a feature request for this kind of problem, but apparently it is too special case for {dplyr}. When you follow the link you can also find another option to do this kind of operation.

Upvotes: 3

akrun
akrun

Reputation: 887711

One option is map/reduce. Specify the columns of interest ('nm1'), loop over them in map, select those columns from the dataset, reduce by dividing, rename the columns after column binding (_dfc), and bind those with the original dataset

library(dplyr)
library(purrr)
library(stringr)
nm1 <- c('A', 'B')
map_dfc(nm1, ~ df %>% 
                select(ends_with(.x)) %>% 
                reduce(., `/`) ) %>%
    rename_all(~ str_c('R_', nm1)) %>%
    bind_cols(df, .)

-output

#  label A  B error_A error_B R_A      R_B
#1     a 4 10     0.4     0.3  10 33.33333
#2     b 3  0     0.3     0.0  10      NaN
#3     c 8  4     0.2     0.4  40 10.00000
#4     d 9  1     0.1     0.1  90 10.00000

Or another option with across

df %>% 
    mutate(across(c(A, B), ~ 
     ./get(str_c('error_', cur_column() )), .names = 'R_{.col}' ))
#  label A  B error_A error_B R_A      R_B
#1     a 4 10     0.4     0.3  10 33.33333
#2     b 3  0     0.3     0.0  10      NaN
#3     c 8  4     0.2     0.4  40 10.00000
#4     d 9  1     0.1     0.1  90 10.00000

Upvotes: 10

Related Questions