Use apply family function to create multiple data frame columns by operating on multiple other columns

user_12196908 0
Viewed 45

I am doing some data manipulation involving creating multiple columns by operating on other columns. It is easy to work using a for loop but I am stuck using an apply like function to do the coding.

One problem is how to refer to the new columns which haven't been created yet. Second is, each of the new column involves operation on two other columns in the same data frame.

Example: let's say I have a simple data frame (actually I have much more columns):

> df <- data.frame("x1" = 1:2, "x2" = 3:4, "y1"= 1:2, "y2"= 3:4)

> df

x1 x2 y1 y2

1  3  1  3

2  4  2  4

I wanted to create two other variables called z1 and z2, so that z1 = (x1 + y1)/3, and z2 = (x2 + y2)/3

Using for loop, I can do this easily.

x.var <- paste("x", 1:2, sep = '')

y.var <- paste("y", 1:2, sep = '')

z.var <- paste("z", 1:2, sep = '')

for (i in 1:2) {

  df[[z.var[i]]] <- (df[[x.var[i]]] + df[[y.var[i]]])/3

}

df

  x1 x2 y1 y2        z1       z2

1  1  3  1  3 0.6666667 2.000000

2  2  4  2  4 1.3333333 2.666667

How can this for loop be transformed using some compact code using apply family functions?

apply multiple-columns r
2 Answers

We can use mutate

library(dplyr)
df %>%
    mutate(z1 = (x1 + y1)/3, z2 = (x2 + y2)/3)

Or with transform from base R

df <- transform(df, z1 = (x1 + y1)/3, z2 = (x2 + y2)/3)
df
#    x1 x2 y1 y2        z1       z2
#1  1  3  1  3 0.6666667 2.000000
#2  2  4  2  4 1.3333333 2.666667
user_37322710

In case you have hundreds of columns, consider rowSums (similar to apply(mat, margin=1, sum)). And as ?rowSums docs indicate:

These functions are equivalent to use of apply with FUN = mean or FUN = sum with appropriate margins, but are a lot faster. 

df$z1 <- (rowSums(df[grepl("1", names(df))])) /3

df$z2 <- (rowSums(df[grepl("2", names(df))])) /3

Alternatively, you can assign multiple columns at once:

df[paste0("z", 1:2)] <- cbind((rowSums(df[grepl("1", names(df))])) / 3,
                              (rowSums(df[grepl("2", names(df))])) / 3)

Still more with sapply for matrix build:

df[paste0("z", 1:2)] <- sapply(1:2, function(i) rowSums(df[grepl(i, names(df))]) / 3)

df
#   x1 x2 y1 y2        z1       z2
# 1  1  3  1  3 0.6666667 2.000000
# 2  2  4  2  4 1.3333333 2.666667
user_14224510
Related