2

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?

Dennis C
  • 23
  • 4

3 Answers3

1

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
Tyler Byers
  • 131
  • 5
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

Parfait
  • 104,375
  • 17
  • 94
  • 125
  • I was looking for that regex function and had a feeling cbind might be involved. It was tough to choose the correct answer between the 3 of these answers. Yours was the simplest solution given my question. Thank you – Dennis C Sep 19 '18 at 15:28
  • Good to hear! Glad to help the learning process. You can even shorten the `cbind` by retrieving all non-zero columns with *invert* in `grep`! See edit and demo. – Parfait Sep 19 '18 at 16:08
0

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
Jon Spring
  • 55,165
  • 4
  • 35
  • 53
  • I've been trying to utilize the tidyverse packages for this but I couldn't figure out the correct logic for it. This does a great job of that and puts them into perspective. Thank you – Dennis C Sep 19 '18 at 15:23