r - R 中所有列的 1 天滞后、3 天平均滞后和 7 天平均滞后

Question

我的数据集中有 158 列。我想为每一列创建 3 个新列(1_day、3_day 和 7_day 滞后)。

data <- data.frame(DATE = c("1/1/2016","1/2/2016","1/3/2016","1/4/2016","1/5/2016","1/6/2016","1/7/2016","1/8/2016","1/9/2016","1/10/2016",
Attr1 = c(5,8,7,6,2,1,4,1,2),
Attr2 = c(10,23,32,12,3,2,5,3,21), 
Attr3 = c(12,23,43,3,2,4,1,23,33))

想要的结果如下:

Attr1_3D = ATTR1 最近 3 天的平均值Attr1_7D = ATTR1 最近 7 天的平均值

Attr2_3D = ATTR2 最近 3 天的平均值Attr2_7D = ATTR2 最近 7 天的平均值

Attr3_3D = ATTR3 最近 3 天的平均值Attr3_7D = ATTR3 最近 7 天的平均值

Answer 1

下面是一种使用 tidyverse 和 zoo 的方法。您可以使用 zoo 包中的 rollapply 获取滚动方式(按 1、3 或 7 天)。

编辑:还添加了 1 天的偏移量(因为滚动平均值包含在 X 天窗口之后的那一天)。还加入原始数据框以包含原始 Attr 列。

library(tidyverse)
library(zoo)

data %>%
  pivot_longer(starts_with("Attr"), names_to = "Attr", values_to = "Value") %>%
  group_by(Attr) %>%
  mutate(Attr_1D = rollapply(Value, 1, mean, align = 'right', fill = NA),
         Attr_3D = rollapply(Value, 3, mean, align = 'right', fill = NA),
         Attr_7D = rollapply(Value, 7, mean, align = 'right', fill = NA),
         DATE = lead(DATE)) %>%
  pivot_wider(id_cols = DATE, names_from = "Attr", values_from = c("Attr_1D", "Attr_3D", "Attr_7D")) %>%
  right_join(data)

输出

# A tibble: 9 x 13
  DATE     Attr_1D_Attr1 Attr_1D_Attr2 Attr_1D_Attr3 Attr_3D_Attr1 Attr_3D_Attr2 Attr_3D_Attr3 Attr_7D_Attr1 Attr_7D_Attr2 Attr_7D_Attr3 Attr1 Attr2 Attr3
  <fct>            <dbl>         <dbl>         <dbl>         <dbl>         <dbl>         <dbl>         <dbl>         <dbl>         <dbl> <dbl> <dbl> <dbl>
1 1/1/2016            NA            NA            NA         NA            NA            NA            NA             NA            NA       5    10    12
2 1/2/2016             5            10            12         NA            NA            NA            NA             NA            NA       8    23    23
3 1/3/2016             8            23            23         NA            NA            NA            NA             NA            NA       7    32    43
4 1/4/2016             7            32            43          6.67         21.7          26            NA             NA            NA       6    12     3
5 1/5/2016             6            12             3          7            22.3          23            NA             NA            NA       2     3     2
6 1/6/2016             2             3             2          5            15.7          16            NA             NA            NA       1     2     4
7 1/7/2016             1             2             4          3             5.67          3            NA             NA            NA       4     5     1
8 1/8/2016             4             5             1          2.33          3.33          2.33          4.71          12.4          12.6     1     3    23
9 1/9/2016             1             3            23          2             3.33          9.33          4.14          11.4          14.1     2    21    33

数据

data <- structure(list(DATE = structure(1:9, .Label = c("1/1/2016", "1/2/2016", 
"1/3/2016", "1/4/2016", "1/5/2016", "1/6/2016", "1/7/2016", "1/8/2016", 
"1/9/2016"), class = "factor"), Attr1 = c(5, 8, 7, 6, 2, 1, 4, 
1, 2), Attr2 = c(10, 23, 32, 12, 3, 2, 5, 3, 21), Attr3 = c(12, 
23, 43, 3, 2, 4, 1, 23, 33)), class = "data.frame", row.names = c(NA, 
-9L))