Efficiently sum across multiple columns in R

Question

I have the following condensed data set:

a<-as.data.frame(c(2000:2005))
a$Col1<-c(1:6)
a$Col2<-seq(2,12,2)

colnames(a)<-c("year","Col1","Col2")

for (i in 1:2){
  a[[paste("Var_", i, sep="")]]<-i*a[[paste("Col", i, sep="")]]
}

I would like to sum the columns Var1 and Var2, which I use:

a$sum<-a$Var_1 + a$Var_2

In reality my data set is much larger - I would like to sum from Var_1 to Var_n (n can be upto 20). There must be a more efficient way to do this than:

 a$sum<-a$Var_1 + ... + a$Var_n

Thanks, works well in the following where i is the column index of Var_1 and j is the column index of Var_n `a$sum <- apply(a[,c(i:j)], 1, sum)` — user2568648, Mar 12 '15 at 09:40
And automating the process even further (using http://stackoverflow.com/questions/9277363/get-the-column-number-in-r-given-the-column-name/9277935#9277935) : `a$sum <- apply(a[,c(match("Var_1",names(a)):match("Var_n",names(a)))], 1, sum)` — user2568648, Mar 12 '15 at 09:44

score 53 · Answer 1 · answered Jan 02 '19 at 02:27

Here's a solution using the tidyverse. You can extend it to as many columns as you like using the select() function to select the appropriate columns within a mutate().

library(tidyverse)

a<-as.data.frame(c(2000:2005))
a$Col1<-c(1:6)
a$Col2<-seq(2,12,2)

colnames(a)<-c("year","Col1","Col2")

for (i in 1:2){
    a[[paste("Var_", i, sep="")]]<-i*a[[paste("Col", i, sep="")]]
}
a
#>   year Col1 Col2 Var_1 Var_2
#> 1 2000    1    2     1     4
#> 2 2001    2    4     2     8
#> 3 2002    3    6     3    12
#> 4 2003    4    8     4    16
#> 5 2004    5   10     5    20
#> 6 2005    6   12     6    24

# Tidyverse solution
a %>%
    mutate(Total = select(., Var_1:Var_2) %>% rowSums(na.rm = TRUE))
#>   year Col1 Col2 Var_1 Var_2 Total
#> 1 2000    1    2     1     4     5
#> 2 2001    2    4     2     8    10
#> 3 2002    3    6     3    12    15
#> 4 2003    4    8     4    16    20
#> 5 2004    5   10     5    20    25
#> 6 2005    6   12     6    24    30

^{Created on 2019-01-01 by the reprex package (v0.2.1)}

How to calculate percent of each columns (Col1/total, caol2/total)? — Masoud, Sep 18 '21 at 13:59
@Masoud - I'd add a mutate_at(), which mutates Col1:Col2, with a function that divides by the total column. — Matt Dancho, Sep 22 '21 at 17:27
@Matt Dancho, how would you modify it if they columns you wanted to sum across were not adjacent to each other? — tnt, Dec 06 '22 at 18:09

score 35 · Answer 2 · answered Mar 12 '15 at 09:37

35

You can use colSums(a[,c("Var1", "Var2")]) or rowSums(a[,c("Var_1", "Var_2")]). In your case you want the latter.

answered Mar 12 '15 at 09:37

psoares

4,733
7
41
55

score 13 · Answer 3 · answered May 18 '21 at 15:57

13

with dplyr you can use

a %>%
rowwise() %>%
mutate(sum = sum(Col1,Col1, na.rm = T))

or more efficiently

a %>%
rowwise() %>%
mutate(sum = sum(across(starts_with("Col")), na.rm = T))

answered May 18 '21 at 15:57

Jose Victor Zambrana

489
8
15

score 2 · Answer 4 · answered Jul 02 '20 at 14:27

If you're working with a very large dataset, rowSums can be slow.

An alternative is the rowsums function from the Rfast package. This requires you to convert your data to a matrix in the process and use column indices rather than names. Here's an example based on your code:

## load Rfast
library(Rfast)

## create dataset
a <- as.data.frame(c(2000:2005))
a$Col1 <- c(1:6)
a$Col2 <- seq(2,12,2)

colnames(a) <- c("year","Col1","Col2")

for (i in 1:2){
  a[[paste("Var_", i, sep="")]] <- i*a[[paste("Col", i, sep="")]]
}

## get column indices based on names
col_st <- grep("Var_1", colnames(a))  # index of "Var_1" col
col_en <- grep("Var_2", colnames(a))  # index of "Var_2" col
cols   <- c(col_st:col_en)  # indices of all cols from "Var_1" to "Var_2"

## sum rows 4 to 5
a$Total <- rowsums(as.matrix(a[,cols]))

score 2 · Answer 5 · answered Mar 03 '22 at 05:27

Benchmarking seems to show that plain Reduce('+', ...) is the fastest. Libraries just make it (at least slightly) slower, at least for mtcars, even if I expand it to be huge.

Unit: milliseconds
         expr        min         lq       mean     median         uq        max
      rowSums   8.672061   9.014344  13.708022   9.602312  10.672726  148.47183
       Reduce   2.994240   3.157500   6.331503   3.223612   3.616555   99.49181
        apply 524.488376 651.549401 771.095002 743.286441 857.993418 1235.53153
        Rfast   5.649006   5.901787  11.110896   6.387990   9.727408   66.03151
   DT_rowSums   9.209539   9.566574  20.955033  10.131163  12.967030  294.32911
    DT_Reduce   3.590719   3.774761  10.595256   3.924592   4.259343  340.52855
 tidy_rowSums  15.532917  15.997649  33.736883  17.316108  27.072343  343.21254
  tidy_Reduce   8.627810   8.960008  12.271105   9.603124  11.089334   79.98853

Code:

library('data.table')
library('tidyverse')
library('Rfast')
DFcars = data.table::copy(mtcars)
DFcars = do.call("rbind", replicate(10000, DFcars, simplify = FALSE))
DT_cars = data.table::copy(DFcars)
DFcars2 = data.table::copy(DFcars)
setDT(DT_cars)
colnms = c("mpg", "cyl", "disp", "hp", "drat")

microbenchmark::microbenchmark(
    rowSums =
        {
            DFcars$new_col = rowSums(DFcars[, colnms])
            (as.numeric(DFcars$new_col))
        },
    Reduce =
        {
            DFcars$new_col = Reduce('+', DFcars[, colnms])
            (as.numeric(DFcars$new_col))
        },
    apply =
        {
            DFcars$new_col = apply(DFcars[, 1:5], 1, sum)
            (as.numeric(DFcars$new_col))
        },
    Rfast =
        {
            DFcars$new_col = rowsums(as.matrix(DFcars[, colnms]))
            (as.numeric(DFcars$new_col))
        },
    DT_rowSums =
        {
            DT_cars[, new_col := rowSums(.SD), .SDcols = colnms]
            (as.numeric(DT_cars$new_col))
        },
    DT_Reduce =
        {
            DT_cars[, new_col := Reduce('+', .SD), .SDcols = colnms]
            (as.numeric(DT_cars$new_col))
        },
    tidy_rowSums =
        {
            DFcars2 = DFcars2 %>% mutate(new_col = select(., colnms) %>% rowSums())
            (as.numeric(DFcars2$new_col))
        },
    tidy_Reduce =
        {
            DFcars2 = DFcars2 %>% mutate(new_col = select(., colnms) %>% Reduce('+', .))
            (as.numeric(DFcars2$new_col))
        },
    check = 'equivalent'
)

score 2 · Answer 6 · answered Mar 23 '23 at 07:56

Still another solution , with the janitor package:

janitor::adorn_totals(a, where = "col", ... = Var_1:Var_2)

or equivalently, using the compact tidyselect syntax,

janitor::adorn_totals(a, where = "col", ... = starts_with("Var"))

Remark that just janitor::adorn_totals(a, where = "col") would add up all numeric columns, except for the first one.

score 1 · Answer 7 · answered Aug 29 '21 at 17:36

1

You can use this:

library(dplyr)
a$Sum <- apply(a[,select(a, starts_with("Var_"))], 1, sum)

answered Aug 29 '21 at 17:36

Mohamed Rahouma

1,084
9
20

score 1 · Answer 8 · answered Oct 13 '21 at 09:32

1

In Base R:

You could simply just use sapply:

sapply(unique(sub(".$", "", colnames(a))), function(x) rowSums(a[startsWith(colnames(a), x)]))

This is very reliable, it works for anything.

answered Oct 13 '21 at 09:32

U13-Forward

69,221
14
89
114

score 0 · Answer 9 · answered Jul 28 '23 at 19:55

0

pick() is introduced in dplyr v1.1.0 to select the columns in mutate() and summarise():

library(dplyr)
a %>%
  mutate(Total = rowSums(pick(Var_1:Var_2), na.rm = TRUE))

Here are more applications of pick()

answered Jul 28 '23 at 19:55

one

3,121
1
4
24

Efficiently sum across multiple columns in R

9 Answers9

Linked

Related