How do i calculate row sums based on the the row value across partial matched column names in R

Question

My original data has over 30, 000 rows and 100 column.This is an example: dataset

df <- data.frame(Outcomes= c(3, 4, 5, 6), 
ADMDATE_3=c(10,7,9, 7), 
ADMDATE_4=c(4,10,6,9),
ADMDATE_5=c(2, 11 ,8,8), 
ADMDATE_6=c(4.5,7,9,12))

My expected results is that I want to use the value in the outcomes column, for example 4 to sum across ADMDATE column which has a value of 4 at the end and the subsequent columns (the remaining length of mydataset). Another example is if outcomes = 5 then I want the sum for ADMDate 5 to 6.

This my expected table

 Outcomes ADMDATE_3 ADMDATE_4 ADMDATE_5 ADMDATE_6 sum_all
         3        10         4         2       4.5    20.5
         5         7        10        11       7.0    18.0
         6         9         6         8       9.0    9.0
         4         7         9         8      12.0    29.0

Answer 1

You can use apply and match the column names and sum :

apply(df, 1, function(x) 
     sum(x[match(paste0('ADMDATE_', x[1]),names(x)):length(x)]))
#[1] 20.5 28.0 17.0 12.0

Or using tidyverse get the data in long format, keep all the values which are >= Outcomes and sum for each Outcomes.

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = -Outcomes, names_to = c('col','num'), names_sep = "_") %>%
  filter(num >= Outcomes) %>%
  group_by(Outcomes) %>%
  summarise(sum_all = sum(value)) %>%
  left_join(df, by = 'Outcomes')

Answer 2

# translator of Outcomes number to column index
outcomes2index <- 1:ncol(df)
names(outcomes2index) <- gsub("ADMDATE_", "", colnames(df))

df$sum_all <- sapply(1:nrow(df), function(i) sum(df[i, outcomes2index[as.character(df$Outcomes[i])]:ncol(df)]))

df
  Outcomes ADMDATE_3 ADMDATE_4 ADMDATE_5 ADMDATE_6 sum_all
1        3        10         4         2       4.5    20.5
2        4         7        10        11       7.0    28.0
3        5         9         6         8       9.0    17.0
4        6         7         9         8      12.0    12.0

Your given df deviates from your expected table.