Sum values based on date

Question

I have a data.frame in R containing data collected over different sites (geographical locations) over several years. I'd like to be able to get a cumulative figure for each year, by combining all the sites together as a 'Total', with the 'Total' falling under the year column in this example.

Here's an example

year<- rep(c(2010, 2011, 2012, 2013), 4)
type<- rep(c('A', 'B', 'C', 'D'),each = 4)
x <- floor(runif(16, 10, 25))
data<-data.frame(year, type,x)

data



year type  x
1  2010    A 22
2  2011    A 22
3  2012    A 11
4  2013    A 13
5  2010    B 23
6  2011    B 22
7  2012    B 12
8  2013    B 10
9  2010    C 22
10 2011    C 17
11 2012    C 14
12 2013    C 10
13 2010    D 23
14 2011    D 22
15 2012    D 19
16 2013    D 23

Here's what I'd like to look like

year type  x
1  2010    A 22
2  2011    A 22
3  2012    A 11
4  2013    A 13
5  Total   A 68
6  2010    B 23
7  2011    B 22
8  2012    B 12
9  2013    B 10
10 Total   B 67
11 2010    C 22
12 2011    C 17
13 2012    C 14
14 2013    C 10
15 Total   C 63
16 2010    D 23
17 2011    D 22
18 2012    D 19
19 2013    D 23
20 Total   D 87

Any help greatly appreciated.

Thanks

Answer 1

you may try

library(dplyr)

data2 <- data %>%
  group_by(type) %>%
  summarise(x = sum(x)) %>%
  mutate(year = "Total")

rbind(data, data2) %>%
  arrange(type, year)

    year type  x
1   2010    A 18
2   2011    A 23
3   2012    A 22
4   2013    A 16
5  Total    A 79
6   2010    B 22
7   2011    B 17
8   2012    B 16
9   2013    B 18
10 Total    B 73
11  2010    C 19
12  2011    C 11
13  2012    C 16
14  2013    C 12
15 Total    C 58
16  2010    D 10
17  2011    D 20
18  2012    D 24
19  2013    D 21
20 Total    D 75