Sum different range of columns per row

user_9944194 0
Viewed 93

I would like to create a new variable in my dataset that sums a certain range of columns, however the range of columns to sum varies for each row.

Here is my sample dataset. I want to create a new variable, NUM1 that is the sum of the columns 1 to ARRAYEND, where the variable ARRAYEND specifies what column NUM1 should sum up to.

#create sample dataset
r1<-c(1,1,1,1,0,2)
r2<-c(1,1,0,1,1,4)
df<-as.data.frame(rbind(r1,r2))
names(df)<-c("Flag1","Flag2","Flag3","Flag4","Flag5","ARRAYEND")

   Flag1 Flag2 Flag3 Flag4 Flag5 ARRAYEND
r1     1     1     1     1     0        2
r2     1     1     0     1     1        4

This is the result I want:

  Flag1 Flag2 Flag3 Flag4 Flag5 ARRAYEND NUM1
r1     1     1     1     1     0        2    2
r2     1     1     0     1     1        4    3

I tried to do this through dplyr , but it is giving me an error.

df <-df %>% mutate(NUM1 = rowSums(.[1:df$ARRAYEND]))

Warning messages:
1: Problem with `mutate()` input `NUM1`.
ℹ numerical expression has 2 elements: only the first used
ℹ Input `NUM1` is `rowSums(.[1:df$ARRAYEND])`. 
2: In 1:df$ARRAYEND :
  numerical expression has 2 elements: only the first used
row r for-loop sum loops
2 Answers

A base R option would be to loop over the rows with apply and MARGIN = 1, then get the sequence of 'ARRAYEND', subset the elements of 'x' and get the sum

df$NUM1 <- apply(df, 1, function(x)  sum(x[seq(x['ARRAYEND'])]))

-output

df
#   Flag1 Flag2 Flag3 Flag4 Flag5 ARRAYEND NUM1
#r1     1     1     1     1     0        2    2
#r2     1     1     0     1     1        4    3

NOTE: tidyverse doesn't keep the rownames. So, it may be better to use base R

if we want tidyverse, an option is to get the rownames as a column first

library(tibble)
library(dplyr)
df %>%
    rownames_to_column('rn') %>% 
    rowwise %>% 
    mutate(NUM1 = sum(c_across(starts_with("Flag"))[seq(ARRAYEND)])) %>%    
    ungroup %>%
    column_to_rownames('rn')

-output

#   Flag1 Flag2 Flag3 Flag4 Flag5 ARRAYEND NUM1
#r1     1     1     1     1     0        2    2
#r2     1     1     0     1     1        4    3
user_37322710

Another base R option using Reduce

transform(
  df,
  NUM1 = list2DF(Reduce("+", df, accumulate = TRUE))[cbind(seq_along(ARRAYEND), ARRAYEND)]
)

gives

   Flag1 Flag2 Flag3 Flag4 Flag5 ARRAYEND NUM1
r1     1     1     1     1     0        2    2
r2     1     1     0     1     1        4    3
user_121587570
Related