R cumulative sum, but in reverse order

user_11939840 0
Viewed 485

I have a data.table in which I want to add a new column that will have a cumulative sum of var column but in reverse order.

structure(list(date = c("2020-09-18", "2020-09-25", "2020-09-30", 
"2020-10-02", "2020-10-09", "2020-10-16", "2020-10-23", "2020-10-30", 
"2020-11-20", "2020-12-31", "2021-01-15", "2021-03-19", "2021-03-31", 
"2021-04-16", "2021-06-30", "2022-01-21", "2022-06-17", "2023-01-20"
), var = c(641202L, 85464L, 868557L, 46256L, 13760L, 1034287L, 
6473L, 9769L, 653072L, 273695L, 1927442L, 455322L, 67728L, 12948L, 
184244L, 401747L, 70496L, 1235L)), row.names = c(NA, -18L), groups = structure(list(
    ExpDate = c("2020-09-18", "2020-09-25", "2020-09-30", "2020-10-02", 
    "2020-10-09", "2020-10-16", "2020-10-23", "2020-10-30", "2020-11-20", 
    "2020-12-31", "2021-01-15", "2021-03-19", "2021-03-31", "2021-04-16", 
    "2021-06-30", "2022-01-21", "2022-06-17", "2023-01-20"), 
    .rows = structure(list(1:2, 3:4, 5:6, 7:8, 9:10, 11:12, 13:14, 
        15:16, 17:18, 19:20, 21:22, 23:24, 25:26, 27:28, 29:30, 
        31:32, 33:34, 35:36), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), row.names = c(NA, 18L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("data.table", 
"data.frame"), .internal.selfref = <pointer: 0x5580c995ccb0>)

data.table looks as follows -

          date     var
 1: 2020-09-18  641202
 2: 2020-09-25   85464
 3: 2020-09-30  868557
 4: 2020-10-02   46256
 5: 2020-10-09   13760
 6: 2020-10-16 1034287
 7: 2020-10-23    6473
 8: 2020-10-30    9769
 9: 2020-11-20  653072
10: 2020-12-31  273695
11: 2021-01-15 1927442
12: 2021-03-19  455322
13: 2021-03-31   67728
14: 2021-04-16   12948
15: 2021-06-30  184244
16: 2022-01-21  401747
17: 2022-06-17   70496
18: 2023-01-20    1235

I want to add a new column that will cumulatively add the values in var column from bottom to top.

          date     var  reverse_sum
 1: 2020-09-18  641202
 2: 2020-09-25   85464
 3: 2020-09-30  868557
 4: 2020-10-02   46256
 5: 2020-10-09   13760
 6: 2020-10-16 1034287
 7: 2020-10-23    6473
 8: 2020-10-30    9769
 9: 2020-11-20  653072
10: 2020-12-31  273695
11: 2021-01-15 1927442
12: 2021-03-19  455322
13: 2021-03-31   67728
14: 2021-04-16   12948
15: 2021-06-30  184244
16: 2022-01-21  401747  (71731 + 401747) = 473478 (and so on upwards)
17: 2022-06-17   70496  (70496 + 1235) = 71731 (only the sum will be shown in this column)
18: 2023-01-20    1235  1235

I am sure there must be a simple one-line solution to this using data.table.

Thanks, Saurabh

data.table r
2 Answers

You can simply use rev and cumsum (and then rev again):

dat[, reverse_sum := rev(cumsum(rev(var)))]
dat
#           date     var reverse_sum
#  1: 2020-09-18  641202     6753697
#  2: 2020-09-25   85464     6112495
#  3: 2020-09-30  868557     6027031
#  4: 2020-10-02   46256     5158474
#  5: 2020-10-09   13760     5112218
#  6: 2020-10-16 1034287     5098458
#  7: 2020-10-23    6473     4064171
#  8: 2020-10-30    9769     4057698
#  9: 2020-11-20  653072     4047929
# 10: 2020-12-31  273695     3394857
# 11: 2021-01-15 1927442     3121162
# 12: 2021-03-19  455322     1193720
# 13: 2021-03-31   67728      738398
# 14: 2021-04-16   12948      670670
# 15: 2021-06-30  184244      657722
# 16: 2022-01-21  401747      473478
# 17: 2022-06-17   70496       71731
# 18: 2023-01-20    1235        1235
user_33582720

You can use the first argument of data.table to decide the order the rows are operated on

dt[order(-date), reverse_sum := cumsum(var)]
user_132039710
Related