不等式条件的滚动总和

Question

我正在处理来自多家公司的一些历史财务数据。

我的 data.frames/tables 具有以下形式:

library(data.table)

dt <- data.table(company = rep(1:2, each = 9),
                 period = c(1, 2, 2, 2, 3, 3, 4, 5, 6), # recycled
                 result = c(1, 2, 3, 4, 5, 6, 7, 8, 9), # recycled
                 filingDate = as.Date(c('2000-01-01', # 1 1
                                        '2000-02-01', # 2 2
                                        '2000-02-02', # 2 3
                                        '2000-03-02', # 2 4
                                        '2000-03-01', # 3 5
                                        '2000-03-02', # 3 6
                                        '2000-04-01', # 4 7
                                        '2000-05-01', # 5 8
                                        '2000-06-01', # 6 9
                                        # company 2
                                        '2000-03-01', # 1 1
                                        '2000-04-01', # 2 2
                                        '2000-04-02', # 2 3
                                        '2000-05-02', # 2 4
                                        '2000-05-01', # 3 5
                                        '2000-06-02', # 3 6
                                        '2000-06-01', # 4 7
                                        '2000-07-01', # 5 8
                                        '2000-08-01'))) # 6 9
dt
#>     company period result filingDate
#>  1:       1      1      1 2000-01-01
#>  2:       1      2      2 2000-02-01
#>  3:       1      2      3 2000-02-02
#>  4:       1      2      4 2000-03-02
#>  5:       1      3      5 2000-03-01
#>  6:       1      3      6 2000-03-02
#>  7:       1      4      7 2000-04-01
#>  8:       1      5      8 2000-05-01
#>  9:       1      6      9 2000-06-01
#> 10:       2      1      1 2000-03-01
#> 11:       2      2      2 2000-04-01
#> 12:       2      2      3 2000-04-02
#> 13:       2      2      4 2000-05-02
#> 14:       2      3      5 2000-05-01
#> 15:       2      3      6 2000-06-02
#> 16:       2      4      7 2000-06-01
#> 17:       2      5      8 2000-07-01
#> 18:       2      6      9 2000-08-01

创建于 2019-12-26 由 reprex package (v0.3.0)

所以我在这里有两家不同公司在 6 个时期(即季度)报告的结果。然而，公司有时会注意到他们报告了错误，在这种情况下他们会重述。在此示例中，公司必须对期间 2 进行两次重述(总共三份报告)，对期间 3 进行重述。

每份报告/重述的提交日期显示在 filingDate 中。 .

现在，我想计算每家公司随时间推移的两个(或更多)期间的已知滚动总和。

如果我们每个时期只有一份申请，那么使用 dt[, rSum = frollsum(result, 2), by = company] 就可以轻松实现这一目标。 .

但是，由于我们每个期间可以有多个申请，我们需要将这些过滤到“当前日期”已知的最新申请，因为我们可以在以下期间已经提交之后重新声明期间。一个例子是公司 1 在 2000 年 3 月 2 日的期间 2 重述，在期间 3 于 2000 年 3 月 1 日提交之后。

因此，预期的输出将是:

#>     company period rSum filingDate
#>  1:       1      1    1 2000-01-01
#>  2:       1      2    3 2000-02-01  # 2 + 1
#>  3:       1      2    4 2000-02-02  # 3 + 1
#>  4:       1      2    5 2000-03-02  # 4 + 1 (optional row, see below)
#>  5:       1      3    8 2000-03-01  # 5 + 3
#>  6:       1      3   10 2000-03-02  # 6 + 4
#>  7:       1      4   13 2000-04-01  # 7 + 6
#>  8:       1      5   15 2000-05-01  # 8 + 7
#>  9:       1      6   17 2000-06-01  # 9 + 8
#> 10:       2      1    1 2000-03-01 
#> 11:       2      2    3 2000-04-01  # 2 + 1
#> 12:       2      2    4 2000-04-02  # 3 + 1
#> 13:       2      3    8 2000-05-01  # 5 + 3
#> 14:       2      3    9 2000-05-02  # 5 + 4
#> 15:       2      4   12 2000-06-01  # 7 + 5
#> 16:       2      4   13 2000-06-02  # 7 + 6
#> 17:       2      5   15 2000-07-01  # 8 + 7
#> 18:       2      6   17 2000-08-01  # 9 + 8 

这里有一些细微差别应该明确。

查看输入表的第 4 行和第 6 行 dt ，我们看到公司 1 在 2000-03-02 对期间 2 和 3 进行了两次重述。由于我们有期间 3 的结果，我们不再处于期间 2，因此相关的“最近” rSum是第 3 期。这就是为什么上面的预期输出表明第 4 行是可选的:我真的只关心 rSum在第 6 行，但任何包含第 4 行虚拟结果的解决方案也非常好。

更复杂的是输入的第 15 行和预期输出的第 16 行发生的情况:公司 2 于 2000 年 6 月 2 日重述了其第 3 期的结果。但是，到那时我们已经有了第 4 阶段的结果(于 2000-06-01 给出)。所以，再一次，“最近的” rSum那个日期实际上在第 3 和第 4 期之间( 6 + 7 = 13 )，并且应该与最近的第 4 期而非第 3 期相关联。

我看不出如何优雅地做到这一点。我能想到的就是获取所有唯一的提交日期并通过它们进行 for 循环，以便获得以前时期的最新结果。请注意，这甚至不能满足上面的第二个细微差别，因为 2000-06-02 的结果与时期 3 相关联。

r <- c()
for(comp in unique(dt$company)) {
  x <- dt[company == comp]
  for(d in x[, sort(unique(filingDate))]) {
    y <- tail(x[filingDate <= d,
                .SD[filingDate == max(filingDate)],
                by = .(company, period)], 2)
    r <- c(r, sum(y$result))
  }
}

x <- dt[, .SD[period == max(period)],
        by = .(company, filingDate)]
setorder(x, company, filingDate, period)
x[, rSum := r]
setorder(x, company, period, filingDate)
x
#>     company filingDate period result rSum
#>  1:       1 2000-01-01      1      1    1
#>  2:       1 2000-02-01      2      2    3
#>  3:       1 2000-02-02      2      3    4
#>  4:       1 2000-03-01      3      5    8
#>  5:       1 2000-03-02      3      6   10
#>  6:       1 2000-04-01      4      7   13
#>  7:       1 2000-05-01      5      8   15
#>  8:       1 2000-06-01      6      9   17
#>  9:       2 2000-03-01      1      1    1
#> 10:       2 2000-04-01      2      2    3
#> 11:       2 2000-04-02      2      3    4
#> 12:       2 2000-05-02      2      4    9
#> 13:       2 2000-05-01      3      5    8
#> 14:       2 2000-06-02      3      6   13  # should be Period 4
#> 15:       2 2000-06-01      4      7   12
#> 16:       2 2000-07-01      5      8   15
#> 17:       2 2000-08-01      6      9   17

但是，这感觉非常不合理，那么有没有更惯用的方法来做到这一点？

Answer 1

一种选择是使用非对等连接:

winsz <- 2L
dt[, c("start", "end") := .(period - winsz + 1L, period)] 

dt[, rSum :=
    dt[dt, on=.(company, end>=start, end<=end),
        by=.EACHI, i.result +
            #filter right table for rows with periods before current period and filingDate before current filingDate 
            #and select the latest result for each period and then sum those result
            sum(.SD[period<i.period & filingDate<=i.filingDate, result[.N], period]$V1)
    ]$V1
]

输出:

    company period result filingDate start end rSum
 1:       1      1      1 2000-01-01     0   1    1
 2:       1      2      2 2000-02-01     1   2    3
 3:       1      2      3 2000-02-02     1   2    4
 4:       1      2      4 2000-03-02     1   2    5
 5:       1      3      5 2000-03-01     2   3    8
 6:       1      3      6 2000-03-02     2   3   10
 7:       1      4      7 2000-04-01     3   4   13
 8:       1      5      8 2000-05-01     4   5   15
 9:       1      6      9 2000-06-01     5   6   17
10:       2      1      1 2000-03-01     0   1    1
11:       2      2      2 2000-04-01     1   2    3
12:       2      2      3 2000-04-02     1   2    4
13:       2      2      4 2000-05-02     1   2    5
14:       2      3      5 2000-05-01     2   3    8
15:       2      3      6 2000-06-02     2   3   10
16:       2      4      7 2000-06-01     3   4   12
17:       2      5      8 2000-07-01     4   5   15
18:       2      6      9 2000-08-01     5   6   17

数据:

library(data.table)
dt <- data.table(company = rep(1:2, each = 9),
    period = c(1, 2, 2, 2, 3, 3, 4, 5, 6),
    result = c(1, 2, 3, 4, 5, 6, 7, 8, 9),
    filingDate = as.Date(c('2000-01-01','2000-02-01','2000-02-02','2000-03-02','2000-03-01','2000-03-02','2000-04-01','2000-05-01','2000-06-01','2000-03-01','2000-04-01','2000-04-02','2000-05-02','2000-05-01','2000-06-02','2000-06-01','2000-07-01','2000-08-01')))
dt[, filingDate := as.IDate(filingDate, format="%Y-%m-%d")]