How to calculate running total for prior years in R?

Question

I have a set of variables in the dataset -- I want to simply calculate the running total (and the running mean) for all these variables, based on all prior years.

To illustrate. This is how my data looks like, including the total run variable that I want to generate.

country year    X1  X2  X3  X4  X5  running_total

Bahamas 1990    0   0   0   0   1   NA
Bahamas 1991    0   0   1   1   0   1
Bahamas 1992    1   1   0   0   1   3
Bahamas 1993    0   0   0   0   0   6
Bahamas 1994    1   1   0   1   1   6
Bahamas 1995    0   0   1   0   0   10
Bahamas 1996    0   1   0   1   0   11
Bahamas 1997    1   0   1   0   1   13
Bahamas 1998    0   1   0   1   0   16
Bahamas 1999    1   0   1   0   1   18
Bahamas 2000    0   1   0   1   0   21
Bahamas 2001    1   0   1   0   1   23
Bahamas 2002    0   1   0   1   0   26
Bahamas 2003    1   0   0   0   1   28
Bahamas 2004    0   0   0   1   0   30
Bahamas 2005    1   1   0   0   0   31
Bahamas 2006    0   0   1   1   1   33
Bahamas 2007    1   0   0   0   0   36
Bahamas 2008    0   0   1   1   1   37
Bahamas 2009    1   1   0   0   0   40
Bahamas 2010    0   0   1   1   1   42
Bahamas 2011    1   1   0   0   0   45
Bolivia 1990    0   0   0   0   0   NA
Bolivia 1991    0   0   1   1   0   0
Bolivia 1992    0   0   0   0   0   2
Bolivia 1993    0   0   1   0   0   2
Bolivia 1994    0   0   0   0   0   3
Bolivia 1995    0   0   0   0   0   3
Bolivia 1996    0   0   0   0   0   3
Bolivia 1997    0   0   0   0   0   3
Bolivia 1998    0   0   0   0   0   3
Bolivia 1999    0   0   0   0   0   3
Bolivia 2000    0   1   0   1   0   3
Bolivia 2001    0   0   0   0   0   5
Bolivia 2002    0   0   0   0   0   5
Bolivia 2003    0   0   0   0   0   5
Bolivia 2004    0   0   0   0   0   5
Bolivia 2005    0   0   0   0   0   5
Bolivia 2006    0   0   0   0   0   5
Bolivia 2007    0   0   0   0   0   5
Bolivia 2008    0   0   0   0   1   5
Bolivia 2009    0   0   0   0   0   6
Bolivia 2010    0   0   0   0   1   6
Bolivia 2011    0   0   0   0   0   7

Starting year 1990 ==NA. For example, running total for 1991 is based on 1990. Running total for 1992 is based on 1990-1991. running total for 1993 is based on 1990-1992- running total for 1994 is based on 1990-1993. And so on...until 2011. Then it starts the same procedur for new country B.

I tried the following code below but it doesn't work the way I want. Surely, I need to specify it better, but how?

DF$csum <- ave(DF$X1, DF$X2,DF$X3,DF$X4,DF$X5,FUN=cumsum)

In addition, I would like to generate running mean based on the same logic.

Any help here would be much appreciated!

structure(list(country = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("Bahamas", "Bolivia"), class = "factor"), year = c(1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L), X1 = c(0L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X2 = c(0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X3 = c(0L, 1L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X4 = c(0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X5 = c(1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L), running_total = c(NA, 1L, 3L, 6L, 6L, 10L, 11L, 13L, 16L, 18L, 21L, 23L, 26L, 28L, 30L, 31L, 33L, 36L, 37L, 40L, 42L, 45L, NA, 0L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 6L, 6L, 7L)), .Names = c("country", "year", "X1", "X2", "X3", "X4", "X5", "running_total"), class = "data.frame", row.names = c(NA, -44L))

Answer 1

library(data.table)
setDT(df)
df[, xt := X1+X2+X3+X4+X5]
df[, rt2 := shift(cumsum(xt)), by = country]

Actually it can be solved with an one-liner:

df[, rt3 := {xt=X1+X2+X3+X4+X5; shift(cumsum(xt))}, by = country]
# Or as Ryan points out:
df[, rt2 := shift(cumsum(Reduce(`+`, .SD))) , by = country , .SDcols = grep('^X.*', names(df), value = T)]

All resulting in:

    country year X1 X2 X3 X4 X5 running_total xt rt2
 1: Bahamas 1990  0  0  0  0  1            NA  1  NA
 2: Bahamas 1991  0  0  1  1  0             1  2   1
 3: Bahamas 1992  1  1  0  0  1             3  3   3
 4: Bahamas 1993  0  0  0  0  0             6  0   6
 5: Bahamas 1994  1  1  0  1  1             6  4   6
 6: Bahamas 1995  0  0  1  0  0            10  1  10
 7: Bahamas 1996  0  1  0  1  0            11  2  11
 8: Bahamas 1997  1  0  1  0  1            13  3  13
 9: Bahamas 1998  0  1  0  1  0            16  2  16
10: Bahamas 1999  1  0  1  0  1            18  3  18
11: Bahamas 2000  0  1  0  1  0            21  2  21
12: Bahamas 2001  1  0  1  0  1            23  3  23
13: Bahamas 2002  0  1  0  1  0            26  2  26
14: Bahamas 2003  1  0  0  0  1            28  2  28
15: Bahamas 2004  0  0  0  1  0            30  1  30
16: Bahamas 2005  1  1  0  0  0            31  2  31
17: Bahamas 2006  0  0  1  1  1            33  3  33
18: Bahamas 2007  1  0  0  0  0            36  1  36
19: Bahamas 2008  0  0  1  1  1            37  3  37
20: Bahamas 2009  1  1  0  0  0            40  2  40
21: Bahamas 2010  0  0  1  1  1            42  3  42
22: Bahamas 2011  1  1  0  0  0            45  2  45
23: Bolivia 1990  0  0  0  0  0            NA  0  NA
24: Bolivia 1991  0  0  1  1  0             0  2   0
25: Bolivia 1992  0  0  0  0  0             2  0   2
26: Bolivia 1993  0  0  1  0  0             2  1   2
27: Bolivia 1994  0  0  0  0  0             3  0   3
28: Bolivia 1995  0  0  0  0  0             3  0   3
29: Bolivia 1996  0  0  0  0  0             3  0   3
30: Bolivia 1997  0  0  0  0  0             3  0   3
31: Bolivia 1998  0  0  0  0  0             3  0   3
32: Bolivia 1999  0  0  0  0  0             3  0   3
33: Bolivia 2000  0  1  0  1  0             3  2   3
34: Bolivia 2001  0  0  0  0  0             5  0   5
35: Bolivia 2002  0  0  0  0  0             5  0   5
36: Bolivia 2003  0  0  0  0  0             5  0   5
37: Bolivia 2004  0  0  0  0  0             5  0   5
38: Bolivia 2005  0  0  0  0  0             5  0   5
39: Bolivia 2006  0  0  0  0  0             5  0   5
40: Bolivia 2007  0  0  0  0  0             5  0   5
41: Bolivia 2008  0  0  0  0  1             5  1   5
42: Bolivia 2009  0  0  0  0  0             6  0   6
43: Bolivia 2010  0  0  0  0  1             6  1   6
44: Bolivia 2011  0  0  0  0  0             7  0   7
    country year X1 X2 X3 X4 X5 running_total xt rt2

Answer 2

df = structure(list(country = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("Bahamas", "Bolivia"), class = "factor"), year = c(1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L), X1 = c(0L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X2 = c(0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X3 = c(0L, 1L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X4 = c(0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X5 = c(1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L), running_total = c(NA, 1L, 3L, 6L, 6L, 10L, 11L, 13L, 16L, 18L, 21L, 23L, 26L, 28L, 30L, 31L, 33L, 36L, 37L, 40L, 42L, 45L, NA, 0L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 6L, 6L, 7L)), .Names = c("country", "year", "X1", "X2", "X3", "X4", "X5", "running_total"), class = "data.frame", row.names = c(NA, -44L))

df <- df %>% mutate(sums = X1 + X2 + X3 +X4 + X5) %>% 
  group_by(country) %>% mutate(sum_shift = shift(sums), 
                              sum_shift = ifelse(is.na(sum_shift), 0, sum_shift),
                              running_total = cumsum(sum_shift))

head(df)

country year    X1  X2 X3 X4 X5   running_total sums sum_shift
1: Bahamas 1990  0  0  0  0  1             0    1         0
2: Bahamas 1991  0  0  1  1  0             1    2         1
3: Bahamas 1992  1  1  0  0  1             3    3         2
4: Bahamas 1993  0  0  0  0  0             6    0         3
5: Bahamas 1994  1  1  0  1  1             6    4         0
6: Bahamas 1995  0  0  1  0  0            10    1         4

This is the dplyr solution but it is basically the same as the data table solution. We create a column where we sum across the rows. Then we group by the country and and sum across and create a cumulative sum. We have to set the nas to 0 for the cumulative sums to work.

Answer 3

A solution using dplyr and purrr. We can split the data frame by country, create the running_total column, and then combine the data frames. Notice that this solution does not need to specify individual column names, such as X1 and X2. dat2 is the final output.

library(dplyr)
library(purrr)

dat2 <- dat %>%
  split(.$country) %>%
  map_dfr(~mutate(.x, 
                  running_total = 
                    as.integer(lag(cumsum(rowSums(select(.x, starts_with("X"))))))))

To calculate the running mean, we can follow the same logic by adding the command to the mutate function. Notice that the cummean function is from the dplyr package.

dat2 <- dat %>%
  split(.$country) %>%
  map_dfr(~mutate(.x, 
                  running_total = 
                    as.integer(lag(cumsum(rowSums(select(.x, starts_with("X")))))),
                  running_mean =
                    lag(cummean(rowSums(select(.x, starts_with("X")))))))

DATA

dat <- structure(list(country = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("Bahamas", "Bolivia"), class = "factor"), year = c(1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L), X1 = c(0L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X2 = c(0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X3 = c(0L, 1L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X4 = c(0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), X5 = c(1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L), running_total = c(NA, 1L, 3L, 6L, 6L, 10L, 11L, 13L, 16L, 18L, 21L, 23L, 26L, 28L, 30L, 31L, 33L, 36L, 37L, 40L, 42L, 45L, NA, 0L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 6L, 6L, 7L)), .Names = c("country", "year", "X1", "X2", "X3", "X4", "X5", "running_total"), class = "data.frame", row.names = c(NA, -44L))

dat$running_total <- NULL