Search code examples
rdplyrlag

R: lagged "cumulative" difference between two values

I have a data.frame df with many groups (series) where the data area are presented annually. I'm trying to create a new column where diff is the difference between area in row 1 and row 2. But I need continue subtracting from the "new" difference. This needs to be done in descending order by year for each series.

df<-
structure(list(series = c("A218t23", "A218t23", "A218t23", "A218t23", 
"A218t23", "A218t23", "A218t23", "A218t23", "A218t23"), year = 2018:2010, 
    area = c(16409.3632611811, 274.5866082, 293.8540619, 323.0603775, 
    544.7366938, 108.0737561, 134.8579038, 143.14125, 167.8244576
    )), row.names = c(NA, -9L), groups = structure(list(series = "A218t23", 
    .rows = structure(list(1:9), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), row.names = c(NA, -1L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"))

My desired output would look like this:

So in the above data, I would ha

Hence, 16409-275=16135 then, 16135-294=15841 and so on.

The code I've been using:

df_diffs <- df %>%
   dplyr::group_by(series) %>%
   dplyr::mutate(diff = area - dplyr::lag(area, default=0, order_by = desc(year)))

However, this just returns the lagged difference between rows in the area column. The result I'm looking for is a "cumulative" or running difference. I've checked out RcppRoll and some other SO posts, but no luck. Ideally, I could keep this all within a piping framework since I have other functions going on. Bonus points if there's a way to replace the NA in the first row with the corresponding area value for that year.

Suggestions very much appreciated!

Solution

  • Another option, using Reduce()

    
 df %>%
  group_by(series) %>%
  mutate(diff = Reduce("-", area, accumulate = T))

# A tibble: 9 × 4
# Groups:   series [1]
  series   year   area   diff
  <chr>   <int>  <dbl>  <dbl>
1 A218t23  2018 16409. 16409.
2 A218t23  2017   275. 16135.
3 A218t23  2016   294. 15841.
4 A218t23  2015   323. 15518.
5 A218t23  2014   545. 14973.
6 A218t23  2013   108. 14865.
7 A218t23  2012   135. 14730.
8 A218t23  2011   143. 14587.
9 A218t23  2010   168. 14419.