Add values of columns based on condition of another variable in R

user_12197202 0
Viewed 33

I want to create a variable that adds the values from other columns based on the condition of variable year. That is if variable YEAR = 2013 then add columns YR_2006, YR_2007, YR_2008, YR_2009, YR_2010, YR_2011. So for group A the sum would be 12,793

GROUP   YEAR    YR_2006 YR_2007 YR_2008 YR_2009 YR_2010 YR_2011
A       2013            636     3653    4759    3745
B       2019    1417    2176    3005    2045    2088    1849
C       2007    4218    3622    4651    4574    4122    4711
E       2017    5956    6031    6032    4885    5400    5828
if-statement r
1 Answers

Here is an option with apply and MARGIN = 1 to loop over the rows, get the index where the 'YEAR' matches the names, do a sequence from the 2nd element to that index, subset the values and get the sum

df1$Sum <- apply(df1[-1], 1, function(x) 
    sum(x[2:c(grep(as.character(x[1]), names(x)[-1]) +1, 
        length(x))[1]], na.rm = TRUE))
df1$Sum
#[1] 12793 12580  7840 28886

Or we can use a vectorized option with rowSums after replaceing some of the elements in each row to NA based on matching the 'YEAR' column with the column names that startsWith 'YR_'

i1 <- startsWith(names(df1), "YR_")
i2 <- match(df1$YEAR, sub("YR_", "", names(df1)[i1]), nomatch = sum(i1))
rowSums(replace(df1[i1], col(df1[i1]) > i2[row(df1[i1])], NA), na.rm = TRUE)
#[1] 12793 12580  7840 28886

Or using tidyverse to reshape to 'long' format with pivot_longer and then do a group_by sum after sliceing the rows based on the match

library(dplyr)
library(tidyr)
df1 %>% 
    pivot_longer(cols = starts_with("YR_"), values_drop_na = TRUE) %>%
    group_by(GROUP) %>%
    slice(seq(match(first(YEAR), readr::parse_number(name), nomatch = n()))) %>% 
    summarise(Sum = sum(value)) %>% 
    left_join(df1, .)
  GROUP YEAR YR_2006 YR_2007 YR_2008 YR_2009 YR_2010 YR_2011   Sum
1     A 2013      NA     636    3653    4759    3745      NA 12793
2     B 2019    1417    2176    3005    2045    2088    1849 12580
3     C 2007    4218    3622    4651    4574    4122    4711  7840
4     E 2017    5956    6031    6032    4885    5400     582 28886

data

df1 <- structure(list(GROUP = c("A", "B", "C", "E"), YEAR = c(2013L, 
2019L, 2007L, 2017L), YR_2006 = c(NA, 1417L, 4218L, 5956L), YR_2007 = c(636L, 
2176L, 3622L, 6031L), YR_2008 = c(3653L, 3005L, 4651L, 6032L), 
    YR_2009 = c(4759L, 2045L, 4574L, 4885L), YR_2010 = c(3745L, 
    2088L, 4122L, 5400L), YR_2011 = c(NA, 1849L, 4711L, 582L)), 
   class = "data.frame", row.names = c(NA, 
-4L))
user_37322710
Related