R Sum every n rows across n columns

Question

I have a data.frame that looks like this:

Geotype <- c(1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3)
Strategy <- c("Demand", "Strategy 1", "Strategy 2", "Strategy 3", "Strategy 4", "Strategy 5", "Strategy 6")
Year.1  <- c(1:21)
Year.2  <- c(1:21)
Year.3  <- c(1:21)
Year.4  <- c(1:21)
mydata <- data.frame(Geotype,Strategy,Year.1, Year.2, Year.3, Year.4) 

I want to sum each Strategy for each Year.

This means I need to sum 6 rows down each column in the data frame and then skip the Demand row. I then want to repeat this for all columns (40 years).

I want the output data frame to look like this:

Geotype.output <- c(1, 2, 3)
Year.1.output  <- c(27, 69, 111)
Year.2.output  <- c(27, 69, 111)
Year.3.output  <- c(27, 69, 111)
Year.4.output  <- c(27, 69, 111)
output <- data.frame(Geotype.output,Year.1.output, Year.2.output, Year.3.output, Year.4.output) 

Any suggestions on how to do this elegantly? I tried to hack a solution together using this, this and this, but I wasn't successful because I need to skip a row.

Answer 1

You can try with base R aggregate function (to aggregate data by Geotype, using function sum as "unique value") but using a reduced data.frame (without the "Demand" rows and the Strategy column):

aggregate(.~Geotype, data=mydata[mydata$Strategy !="Demand", -2], FUN=sum)
#  Geotype Year.1 Year.2 Year.3 Year.4
#1       1     27     27     27     27
#2       2     69     69     69     69
#3       3    111    111    111    111

Answer 2

Using data.table:

library(data.table)
setDT(mydata)
output = mydata[Strategy != "Demand", 
             .(Year.1.output = sum (Year.1), 
               Year.2.output = sum (Year.2), 
               Year.3.output = sum (Year.3), 
               Year.4.output = sum (Year.4)),
             by = Geotype]

#    Geotype Year.1.output Year.2.output Year.3.output Year.4.output
# 1:       1            27            27            27            27
# 2:       2            69            69            69            69
# 3:       3           111           111           111           111

We can simplify this to deal more easily with many year columns by

setDT(mydata)[Strategy != "Demand", 
             lapply(.SD, sum), 
             by=Geotype, 
             .SDcols=grep("Year", names(mydata))]