Creating Multi dimension pivot table in R

Question

I have the following data frame:

Event   Scenario   Year   Cost
1         1          1     10
2         1          1      5
3         1          2      6
4         1          2      6
5         2          1     15
6         2          1     12
7         2          2     10
8         2          2      5
9         3          1      4
10        3          1      5
11        3          2      6
12        3          2      5

I need to produce a pivot table/ frame that will sum the total cost per year for each scenario. So the result will be.

      Scenario   Year   Cost
        1          1     15                
        1          2     12
        2          1     27
        2          2     15
        3          1      9
        3          2     11

I need to produce a ggplot line graph that plot the cost of each scenario per year. I know how to do that, I just can't get the right data frame.

Answer 1

Try

library(dplyr)
df %>% group_by(Scenario, Year) %>% summarise(Cost=sum(Cost))

Or

library(data.table)
setDT(df)[, list(Cost=sum(Cost)), by=list(Scenario, Year)]

Or

  aggregate(Cost~Scenario+Year, df,sum)

data

df <- structure(list(Event = 1:12, Scenario = c(1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 3L, 3L, 3L, 3L), Year = c(1L, 1L, 2L, 2L, 1L, 1L, 
2L, 2L, 1L, 1L, 2L, 2L), Cost = c(10L, 5L, 6L, 6L, 15L, 12L, 
10L, 5L, 4L, 5L, 6L, 5L)), .Names = c("Event", "Scenario", "Year", 
"Cost"), class = "data.frame", row.names = c(NA, -12L))

Answer 2

The following does it:

library(plyr)
ddply(df, .(Scenario, Year), summarize, Cost = sum(Cost))

#Scenario Year Cost
#1        1    1   15
#2        1    2   12
#3        2    1   27
#4        2    2   15
#5        3    1    9
#6        3    2   11