R中聚合结果的滚动平均

Question

我有一个数据库，为属于不同行业的个别公司提供销售价值。在下面的示例数据集中：

set.seed(123)
df <- data.table(year=rep(1980:1984,each=4),sale=sample(100:150,20),ind=sample(LETTERS[1:2],20,replace = TRUE))
df[order(year,ind)]
    year sale ind
 1: 1980  114   A
 2: 1980  102   A
 3: 1980  130   B
 4: 1980  113   B
 5: 1981  136   A
 6: 1981  148   A
 7: 1981  141   B
 8: 1981  142   B
 9: 1982  124   A
10: 1982  125   A
11: 1982  104   A
12: 1982  126   B
13: 1983  108   A
14: 1983  128   A
15: 1983  140   B
16: 1983  127   B
17: 1984  134   A
18: 1984  107   A
19: 1984  106   A
20: 1984  146   B

列"ind“代表行业，我省略了公司标识符(在本例中没有用)。我想要一个平均值的定义如下：

在过去的三年里，期望的平均水平是行业内所有公司的平均水平。如果没有过去三年的数据，至少两项观察也是可以接受的。

例如，在上面的数据集中，如果year=1982和ind=A，过去几年只有两个观测值(这仍然是可以接受的)，所以期望的平均值是1980年和1981年A行业所有销售价值的平均值。

如果year=1983和ind=A，我们有前三年，期望的平均值是1980年、1981年和1982年A工业的所有销售价值的平均值。

如果year=1984和ind=A，我们有前三年，所期望的平均值是1981年、1982年和1983年A行业所有销售价值的平均值。

因此，期望的产出如下：

    year sale ind   mymean
 1: 1980  130   B       NA
 2: 1980  114   A       NA
 3: 1980  113   B       NA
 4: 1980  102   A       NA
 5: 1981  141   B       NA
 6: 1981  142   B       NA
 7: 1981  136   A       NA
 8: 1981  148   A       NA
 9: 1982  124   A 125.0000
10: 1982  125   A 125.0000
11: 1982  126   B 131.5000
12: 1982  104   A 125.0000
13: 1983  140   B 130.4000
14: 1983  127   B 130.4000
15: 1983  108   A 121.8571
16: 1983  128   A 121.8571
17: 1984  134   A 124.7143
18: 1984  107   A 124.7143
19: 1984  146   B 135.2000
20: 1984  106   A 124.7143

data.table解决方案是快速实现的首选方案。在此之前，非常感谢您。

Answer 1

我在data.table方面不是很好。这里有一个tidyverse解决方案，如果您愿意，或者可以将它转换为data.table

library(tidyverse)

df %>% group_by(ind, year) %>% 
  summarise(ds = sum(sale),
            dn = n()) %>%
  mutate(ds = (lag(ds,1)+lag(ds,2)+ifelse(is.na(lag(ds,3)), 0, lag(ds,3)))/(lag(dn,1)+lag(dn,2)+ifelse(is.na(lag(dn,3)), 0, lag(dn,3)))
  ) %>% select(ind, year, mymean = ds) %>%
  right_join(df, by = c("ind", "year"))

`summarise()` regrouping output by 'ind' (override with `.groups` argument)
# A tibble: 20 x 4
   ind    year mymean  sale
   <chr> <int>  <dbl> <int>
 1 A      1980    NA    114
 2 A      1980    NA    102
 3 A      1981    NA    136
 4 A      1981    NA    148
 5 A      1982   125    124
 6 A      1982   125    125
 7 A      1982   125    104
 8 A      1983   122.   108
 9 A      1983   122.   128
10 A      1984   125.   134
11 A      1984   125.   107
12 A      1984   125.   106
13 B      1980    NA    130
14 B      1980    NA    113
15 B      1981    NA    141
16 B      1981    NA    142
17 B      1982   132.   126
18 B      1983   130.   140
19 B      1983   130.   127
20 B      1984   135.   146

Answer 2

您可以使用zoo的rollapply函数来执行滚动计算。请注意，有专门的函数来计算滚动均值，比如data.table中的frollmean和zoo中的rollmean，但是它们没有rollapply中的参数partial = TRUE。partial = TRUE在这里很有用，因为您想计算这个平均值，即使窗口的大小小于3。

我们可以首先计算每个mean和year的sale值，然后执行窗口大小为3的滚动平均计算，并将这些数据与原始数据连接起来，以获得原始数据的所有行。

library(data.table)
library(zoo)

df1 <- df[, .(sale = mean(sale)), .(ind, year)]
df2 <- df1[, my_mean := shift(rollapplyr(sale, 3, function(x) 
  if(length(x) > 1) mean(x, na.rm = TRUE) else NA, partial = TRUE)), ind]
df[df2, on = .(ind, year)]

可以使用dplyr将其编写为：

library(dplyr)

df %>%
  group_by(ind, year) %>%
  summarise(sale = mean(sale)) %>%
  mutate(avg_mean = lag(rollapplyr(sale, 3, partial = TRUE, function(x) 
                    if(length(x) > 1) mean(x, na.rm = TRUE) else NA))) %>% 
  left_join(df, by = c('ind', 'year'))

Answer 3

根据Ronak的回答(以前均值的平均值)，一种更一般的方法(所有以前值的平均值)和一个data.table解决方案可以是：

library(data.table)
library(roll)

df1 <- df[, .(sum_1 = sum(sale), n=length(sale)), .(ind, year)]
df1[,`:=`(
  my_sum = roll_sum(shift(sum_1),3,min_obs = 2),
  my_n = roll_sum(shift(n),3,min_obs = 2)
  ),by=.(ind)]
df1[,`:=`(my_mean=(my_sum/my_n))]
> df[df1[,!c("sum_1","n","my_sum","my_n")] ,on = .(ind, year)]
    year sale ind  my_mean
 1: 1980  130   B       NA
 2: 1980  113   B       NA
 3: 1980  114   A       NA
 4: 1980  102   A       NA
 5: 1981  141   B       NA
 6: 1981  142   B       NA
 7: 1981  136   A       NA
 8: 1981  148   A       NA
 9: 1982  124   A 125.0000
10: 1982  125   A 125.0000
11: 1982  104   A 125.0000
12: 1982  126   B 131.5000
13: 1983  140   B 130.4000
14: 1983  127   B 130.4000
15: 1983  108   A 121.8571
16: 1983  128   A 121.8571
17: 1984  134   A 124.7143
18: 1984  107   A 124.7143
19: 1984  106   A 124.7143
20: 1984  146   B 135.2000