Thirst for Knowledge - 7 months ago 82

R 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

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))]
```