所有行从一个月前到当前日期的累计总和 [英] Cumulative sum from a month ago until the current day for all the rows

问题描述

我有一个数据表，其中包含ID，日期和值，如下所示:

I have a data.table with ID, dates and values like the following one:

DT <- setDT(data.frame(ContractID= c(1,1,1,2,2), Date = c("2018-02-01", "2018-02-20", "2018-03-12", "2018-02-01", "2018-02-12"), Value = c(10,20,30,10,20)))

   ContractID       Date Value
1:          1 2018-02-01    10
2:          1 2018-02-20    20
3:          1 2018-03-12    30
4:          2 2018-02-01    10
5:          2 2018-02-12    20

我想获得一个新列，其中包含一个月前到当日每一行的每个ID的累计总金额，如下表所示.注意:第三行是第二行和自己的第三行的总和，因为2018-03-12减去1个月大于2018-02-01，所以我们在总和中不包括第一行.

I'd like to get a new column with the total cumulative sum per ID from a month ago until the current day for each row, like in the table below. NB: the third row is the sum of the second and the own third, because 2018-03-12 minus 1 month is greater than 2018-02-01, so we exclude the first row in the cum sum.

   ContractID       Date Value Cum_Sum_1M
1:          1 2018-02-01    10         10
2:          1 2018-02-20    20         30
3:          1 2018-03-12    30         50
4:          2 2018-02-01    10         10
5:          2 2018-02-12    20         30

有什么方法可以使用data.table实现此目的吗?

Is there any way to achieve this using data.table?

谢谢！

推荐答案

使用 tidyverse 和 lubridate ，我们首先将 Date 转换为实际的<使用 as.Date 使用code> Date 对象，然后使用 group_by ContractID ，并使用每个 Date sum Value ，该值位于当前 Date 和当前 Date 前一个月之间.

Using tidyverse and lubridate, we first convert Date to actual Date object using as.Date, then group_by ContractID and for each Date sum the Value which is between current Date and one month before the current Date.

library(tidyverse)
library(lubridate)

DT %>%
  mutate(Date = as.Date(Date)) %>%
  group_by(ContractID) %>%
  mutate(Cum_Sum_1M = map_dbl(1:n(), ~ sum(Value[(Date >= (Date[.] - months(1))) &
                                            (Date <= Date[.])], na.rm = TRUE)))


# A tibble: 5 x 4
# Groups:   ContractID [2]
#  ContractID Date       Value Cum_Sum_1M
#       <dbl> <date>     <dbl>      <dbl>
#1          1 2018-02-01    10         10
#2          1 2018-02-20    20         30
#3          1 2018-03-12    30         50
#4          2 2018-02-01    10         10
#5          2 2018-02-12    20         30

这篇关于所有行从一个月前到当前日期的累计总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！