Lagging a variable by adding up the previous 5 years?

Question

I am working with data that look like this:

Country Year       Aid
Angola 1995 416420000
Angola 1996 459310000
Angola 1997 354660000
Angola 1998 335270000
Angola 1999 387540000
Angola 2000 302210000

I want to create a lagged variable by adding up the previous five years in the data

So that the observation for 2000 looks like this:

Country Year       Aid  Lagged5
Angola 2000  416420000  1953200000

Which was derived by adding the Aid observations from 1995 to 1999 together:

416420000 + 459310000 + 354660000 + 335270000 + 387540000 = 1953200000

Also, I will need to group by country as well.

Thank You!

Answer 1

You could do:

library(dplyr)

df %>%
  group_by(Country) %>%
  mutate(Lagged5 = sapply(Year, function(x) sum(Aid[between(Year, x - 5, x - 1)])))

Output:

# A tibble: 6 x 4
# Groups:   Country [1]
  Country  Year       Aid    Lagged5
  <chr>   <int>     <int>      <int>
1 Angola   1995 416420000          0
2 Angola   1996 459310000  416420000
3 Angola   1997 354660000  875730000
4 Angola   1998 335270000 1230390000
5 Angola   1999 387540000 1565660000
6 Angola   2000 302210000 1953200000