Loop through and mutate multiple columns using an incrementing variable

user_13344120 0
Viewed 840

My goal is to perform multiple column operations in one line of code without hard coding the variable names.

structure(list(Subject = 1:6, Congruent_1 = c(359, 391, 384, 
316, 287, 403), Congruent_2 = c(361, 378, 322, 286, 276, 363), 
    Congruent_3 = c(342, 355, 334, 274, 297, 335), Congruent_4 = c(365, 
    503, 324, 256, 266, 388), Congruent_5 = c(335, 354, 320, 
    272, 260, 337), Incongruent_1 = c(336, 390, 402, 305, 310, 
    400), Incongruent_2 = c(366, 407, 386, 280, 243, 393), Incongruent_3 = c(323, 
    455, 317, 308, 259, 325), Incongruent_4 = c(361, 392, 357, 
    274, 342, 350), Incongruent_5 = c(300, 366, 378, 263, 258, 
    349)), row.names = c(NA, 6L), class = "data.frame")

My data looks like this.

I want need to do column subtraction and save those new values into new columns. For example, a new column by the name of selhist_1 should be computed as Incongruent_1 - Congruent_1. I tried to write a for loop that indexes the existing columns by their names and creates new columns using the same indexing variable:

for(i in 1:5)(
DP4 = mutate(DP4, as.name(paste("selhistB_",i,sep="")) = as.name(paste("Incongruent_",i,sep="")) - as.name(paste("Congruent_",i,sep="")))
)

but I received this error:

Error: unexpected '=' in: "for(i in 1:5)( DP4 = mutate(DP4, as.name(paste("selhistB_",i,sep="")) ="

I rather use this modular approach, as opposed to hard coding and writing out "selhistB = incongruent_1 - congruent_1" five times, using the mutate() function.

I also wonder if i could achieve the same goal on the long version of this data, and maybe it would make more sense.

dplyr r
4 Answers
library(dplyr)

d %>% 
  pivot_longer(-Subject, 
               names_to = c(".value", "id"), 
               names_sep = "_") %>% 
  mutate(selhistB = Incongruent - Congruent) %>% 
  pivot_wider(names_from = id, values_from = c(Congruent, Incongruent, selhistB))

Or just skip the last pivot, and keep everything long.

user_90951270

You can use split.default and split on column names suffix, then loop over the list and subtract column 2 from column 1, i.e.

lapply(split.default(df[-1], sub('.*_', '', names(df[-1]))), function(i) i[1] - i[2])
user_56355800

Using subtract over all matching columns, then cbind, try:

x <- df1[, grepl("^C", colnames(df1)) ] - df1[, grepl("^I", colnames(df1)) ]
names(x) <- paste0("selhistB_", seq_along(names(x)))

res <- cbind(df1, x)
res

  Subject Congruent_1 Congruent_2 Congruent_3 Congruent_4 Congruent_5
1       1         359         361         342         365         335
2       2         391         378         355         503         354
3       3         384         322         334         324         320
4       4         316         286         274         256         272
5       5         287         276         297         266         260
6       6         403         363         335         388         337
  Incongruent_1 Incongruent_2 Incongruent_3 Incongruent_4 Incongruent_5
1           336           366           323           361           300
2           390           407           455           392           366
3           402           386           317           357           378
4           305           280           308           274           263
5           310           243           259           342           258
6           400           393           325           350           349
  selhistB_1 selhistB_2 selhistB_3 selhistB_4 selhistB_5
1         23         -5         19          4         35
2          1        -29       -100        111        -12
3        -18        -64         17        -33        -58
4         11          6        -34        -18          9
5        -23         33         38        -76          2
6          3        -30         10         38        -12
user_6800680

As long as you are already using tidyverse packages, the following code will do exactly what you need:

library(dplyr)

for(i in 1:5){
  DP4 <- DP4 %>% mutate(UQ(sym(paste0("selhistB_",i))) := 
  UQ(sym(paste0("Incongruent_",i))) - UQ(sym(paste0("Congruent_",i))))
}

DP4

  Subject Congruent_1 Congruent_2 Congruent_3 Congruent_4 Congruent_5
1       1         359         361         342         365         335
2       2         391         378         355         503         354
3       3         384         322         334         324         320
4       4         316         286         274         256         272
5       5         287         276         297         266         260
6       6         403         363         335         388         337
  Incongruent_1 Incongruent_2 Incongruent_3 Incongruent_4 Incongruent_5
1           336           366           323           361           300
2           390           407           455           392           366
3           402           386           317           357           378
4           305           280           308           274           263
5           310           243           259           342           258
6           400           393           325           350           349
  selhistB_1 selhistB_2 selhistB_3 selhistB_4 selhistB_5
1         23         -5         19          4         35
2          1        -29       -100        111        -12
3        -18        -64         17        -33        -58
4         11          6        -34        -18          9
5        -23         33         38        -76          2
6          3        -30         10         38        -12
user_90762670
Related