Create new data columns by dividing two columns, determining divident/divisor in third column

user_14721015 0
Viewed 55

I have a dataframe structured like the following, with the number of x_L and x_R pair being possibly up to 100.

ID  Side  A_L   A_R   B_L   B_R
1    0     7     5     6     3
2    1     3     2     3     1
3    0     6     3     4     5

I know want to create a new column A_ratio for each pair A_Land A_R. The value of that new column for each ID should either be A_L / A_R if Side==0, or A_R / A_L if Side==1.

My attempt was implementing a for loop with an increment of 2, to cover each pair only once, and using ifelse to differentiate between the two possible values of Side

for (i in seq(from = 3, to = length(df), by = 2)) {
  df[sub("_L", "_ratio",(names(df[i]))] <-ifelse(df[2]==0, df[i] / df[i+1], df[i+1] / df[i])
}

This however leads to the following warning:

In `[<-.data.frame`(`*tmp*`, sub("_L", "_ratio", names(df[i])),  : provided 3 variables to replace 1 variables

The resulting values seem to be A_L / A_R for all cases, even for the IDs with Side==1. Could someone point out my mistake? I know that there should be an easier dplyr approach as well, but I'm unfortunately not familiar with that yet. Thanks in advance!

dataframe r
2 Answers

A base R option

cols <- c("A", "B")
cbind(
    df,
    `colnames<-`(
        sapply(
            cols,
            function(x) {
                with(
                    df,
                    do.call("/", df[startsWith(names(df), x)])^(1 - 2 * Side)
                )
            }
        ), paste0(cols, "_ratio")
    )
)

gives

  ID Side A_L A_R B_L B_R   A_ratio   B_ratio
1  1    0   7   5   6   3 1.4000000 2.0000000
2  2    1   3   2   3   1 0.6666667 0.3333333
3  3    0   6   3   4   5 2.0000000 0.8000000
user_121587570

Here's a dplyr approach.

It relies on only mutating columns that end with "L". Then using cur_column() to get the name of the column being worked on, we can create the name of the matching column with str_replace and get() its value.

We can combine this approach with case_when to consider the effect of Side.

Finally, we can use rename_with to fix up the column names since they will be based on the original L column name.

library(dplyr) 
library(stringr)
df %>%
  mutate(across(ends_with("L"), ~case_when(Side == 1 ~ get(str_replace(cur_column(),"L$","R"))/.,
                                           Side == 0 ~ ./get(str_replace(cur_column(),"L$","R"))),
                .names = "{.col}_Ratio")) %>%
  rename_with(~str_replace(.,"_L",""),contains("_L_"))
  ID Side A_L A_R B_L B_R   A_Ratio   B_Ratio
1  1    0   7   5   6   3 1.4000000 2.0000000
2  2    1   3   2   3   1 0.6666667 0.3333333
3  3    0   6   3   4   5 2.0000000 0.8000000
user_130953260
Related