adding together multiple sets of columns in r

Question

I'm trying to add several sets of columns together.

Example df:

  df <- data.frame(
  key = 1:5,
  ab0 = c(1,0,0,0,1),
  ab1 = c(0,2,1,0,0),
  ab5 = c(1,0,0,0,1),
  bc0 = c(0,1,0,2,0),
  bc1 = c(2,0,0,0,0),
  bc5 = c(0,2,1,0,1),
  df0 = c(0,0,0,1,0),
  df1 = c(1,0,3,0,0),
  df5 = c(1,0,0,0,6)
)

Giving me:

  key ab0 ab1 ab5 bc0 bc1 bc5 df0 df1 df5
1   1   1   0   1   0   2   0   0   1   1
2   2   0   2   0   1   0   2   0   0   0
3   3   0   1   0   0   0   1   0   3   0
4   4   0   0   0   2   0   0   1   0   0
5   5   1   0   1   0   0   1   0   0   6

I want to add all sets of columns with 0s and 5s in them together and place them in the 0 column.

So the end result would be:

  key ab0 ab1 ab5 bc0 bc1 bc5 df0 df1 df5
1   1   2   0   1   0   2   0   0   1   1
2   2   0   2   0   3   0   2   0   0   0
3   3   0   1   0   1   0   1   0   3   0
4   4   0   0   0   2   0   0   2   0   0
5   5   2   0   1   1   0   1   0   0   6

I could add the columns together using 3 lines:

df$ab0 <- df$ab0 + df$ab5
df$bc0 <- df$bc0 + df$bc5
df$df0 <- df$df0 + df$df5

But my real example has over a hundred columns so I'd like to iterate over them and use apply.

The column names of the first set are contained in col0 and the names of the second set are in col5.

col0 <- c("ab0","bc0","df0")
col5 <- c("ab5","bc5","df5")

I created a function to add the columns to gether using mapply:

fun1 <- function(df,x,y) {
  df[,x] <- df[,x] + df[,y]
}

mapply(fun1,df,col0,col5)

But I get an error: Error in df[, x] : incorrect number of dimensions

Thoughts?

Answer 1

Simply add two data frames together by their subsetted columns, assuming they will be the same length. No loops needed. All vectorized operation.

final_df <- df[grep("0", names(df))] + df[grep("5", names(df))]

final_df <- cbind(final_df, df[grep("0", names(df), invert=TRUE)])     

final_df <- final_df[order(names(final_df))]
final_df

#   ab0 ab1 ab5 bc0 bc1 bc5 df0 df1 df5 key
# 1   2   0   1   0   2   0   1   1   1   1
# 2   0   2   0   3   0   2   0   0   0   2
# 3   0   1   0   1   0   1   0   3   0   3
# 4   0   0   0   2   0   0   1   0   0   4
# 5   2   0   1   1   0   1   6   0   6   5

Rextester demo

Answer 2

You could use map2 from the purrr package to iterate over the two vectors at once:

df <- data.frame(
  key = 1:5,
  ab0 = c(1,0,0,0,1),
  ab1 = c(0,2,1,0,0),
  ab5 = c(1,0,0,0,1),
  bc0 = c(0,1,0,2,0),
  bc1 = c(2,0,0,0,0),
  bc5 = c(0,2,1,0,1),
  df0 = c(0,0,0,1,0),
  df1 = c(1,0,3,0,0),
  df5 = c(1,0,0,0,6)
)

col0 <- c("ab0","bc0","df0")
col5 <- c("ab5","bc5","df5")

purrr::map2(col0, col5, function(x, y) {
  df[[x]] <<- df[[x]] + df[[y]]
})

> df
  key ab0 ab1 ab5 bc0 bc1 bc5 df0 df1 df5
1   1   2   0   1   0   2   0   1   1   1
2   2   0   2   0   3   0   2   0   0   0
3   3   0   1   0   1   0   1   0   3   0
4   4   0   0   0   2   0   0   1   0   0
5   5   2   0   1   1   0   1   6   0   6

Answer 3

Here's an approach using tidyr and dplyr from the tidyverse meta-package.

First, I bring the table into long ("tidy") format, and split out the column into two components, and spread by the number part of those components.

Then I do the calculation you describe.

Finally, I bring it back into the original format using the inverse of step 1.

library(tidyverse)
df_tidy <- df %>%
  # Step 1
  gather(col, value, -key) %>%
  separate(col, into = c("grp", "num"), 2) %>%
  spread(num, value) %>%
  # Step 2
  mutate(`0` = `0` + `5`) %>%
  # Step 3, which is just the inverse of Step 1.
  gather(num, value, -key, - grp) %>%
  unite(col, c("grp", "num")) %>%
  spread(col, value)

df_tidy
key ab_0 ab_1 ab_5 bc_0 bc_1 bc_5 df_0 df_1 df_5
1   1    2    0    1    0    2    0    1    1    1
2   2    0    2    0    3    0    2    0    0    0
3   3    0    1    0    1    0    1    0    3    0
4   4    0    0    0    2    0    0    1    0    0
5   5    2    0    1    1    0    1    6    0    6