R iterate over columns to create new columns (using mutate or base R)

Question

I have data as follows:

#create sample data frame
df <- data.frame(red_alice_type1 = runif(10, 0, 10),
                  blue_alice_type1 = runif(10, 0, 10),
                  green_alice_type1 = runif(10, 0, 10),
                  red_bob_type1 = runif(10, 0, 10),
                  blue_bob_type1 = runif(10, 0, 10),
                  green_bob_type1 = runif(10, 0, 10),
                  red_alice_type2 = runif(10, 0, 10),
                  blue_alice_type2 = runif(10, 0, 10),
                  green_alice_type2 = runif(10, 0, 10),
                  red_bob_type2 = runif(10, 0, 10),
                  blue_bob_type2 = runif(10, 0, 10),
                  green_bob_type2 = runif(10, 0, 10))

I'd like to multiply each color-name pair to create a new column. e.g. blue_alice_type1 * blue_alice_type2 = blue_alice_product

I attempt to iterate over columns to perform this multiplation as follows:

#categories
colors <- c('red', 'blue', 'green')
names <- c('alice', 'bob')

#Attempt 1: Mutate
for (i in colors){
  for (j in names){
    df <- mutate_(df,
                    paste(i, j, 'product', sep = "_") = paste(i, j, 'type1', sep = "_") * 
                    paste(i, j, 'type2', sep = "_"))
  }
}

#Attempt 2: Base R
for (i in colors){
  for (j in names){
    assign( paste0('df$', paste(i, j, 'product', sep = "_")), 
            eval(parse(text=paste0('df$', paste(i, j, 'type1', sep = "_")))) * 
              eval(parse(text=paste0('df$', paste(i, j, 'type2', sep = "_")))))
  }
}

Attempt 1 gives an error. Attempt 2 runs but doesn't create new columns. Any help?

This might help: https://stackoverflow.com/questions/46832436/dplyr-mutate-formula-based-on-similarities-in-column-names/46836700#46836700. For your example, this is how I would use my function: `df %>% dynamic_mutate(expression = "{x}_type1*{x}_type2", prefix = "prod")` — acylam, Nov 22 '17 at 15:24

score 1 · Answer 1 · answered Nov 22 '17 at 15:06

I would split the columns on type and then calculate the product:

#split on type and sort names
type1 <- df[sort(grep('type1', names(df), value = TRUE))]
type2 <- df[sort(grep('type2', names(df), value = TRUE))]

#multiply (both data.frames have the same number of rows and columns)
new_product <- type1 * type2
#change names
names(new_product) <- gsub('type1', 'product', names(new_product))
str(cbind(df, new_product))
#'data.frame':  10 obs. of  18 variables:
# $ red_alice_type1    : num  9.46 4.024 1.953 9.411 0.373 ...
# $ blue_alice_type1   : num  3.34 8.24 4.85 9.46 2.19 ...
# $ green_alice_type1  : num  1.365 5.804 1.23 6.509 0.966 ...
# $ red_bob_type1      : num  0.357 9.931 4.165 1.835 8.526 ...
# $ blue_bob_type1     : num  1.488 1.532 2.848 0.907 0.914 ...
# $ green_bob_type1    : num  8.275 8.482 9.895 0.657 1.457 ...
# $ red_alice_type2    : num  2.188 7.536 4.391 0.202 5.482 ...
# $ blue_alice_type2   : num  4.923 0.593 6.588 5.308 4.671 ...
# $ green_alice_type2  : num  3.76 6.96 2.54 7.53 9.93 ...
# $ red_bob_type2      : num  6.051 9.788 0.369 7.504 1.61 ...
# $ blue_bob_type2     : num  7.55 9.56 1.1 7.89 6.48 ...
# $ green_bob_type2    : num  2.36 2.94 7.57 2.62 6.74 ...
# $ blue_alice_product : num  16.47 4.89 31.94 50.23 10.25 ...
# $ blue_bob_product   : num  11.24 14.64 3.14 7.15 5.92 ...
# $ green_alice_product: num  5.13 40.38 3.13 49.05 9.59 ...
# $ green_bob_product  : num  19.56 24.94 74.9 1.72 9.82 ...
# $ red_alice_product  : num  20.7 30.32 8.58 1.9 2.05 ...
# $ red_bob_product    : num  2.16 97.2 1.54 13.77 13.73 ...

R iterate over columns to create new columns (using mutate or base R)

1 Answers1