R语言时间序列缺失值填充：前16天均值填x、前后16天最大值填y

原始数据集 #

d1<- structure(list(date = c("8/11/2020", "8/7/2020", "8/4/2020", 
"7/28/2020", "7/27/2020", "7/23/2020", "7/20/2020", "7/13/2020", 
"7/8/2020", "6/25/2020", "6/24/2020", "6/23/2020", "6/18/2020", 
"6/15/2020", "6/10/2020", "6/9/2020", "6/8/2020", "6/5/2020", 
"6/3/2020", "6/1/2020", "5/31/2020", "5/24/2020", "5/21/2020", 
"5/14/2020", "5/11/2020", "5/9/2020", "5/8/2020", "5/7/2020", 
"5/4/2020", "4/22/2020", "4/21/2020", "4/16/2020", "4/14/2020", 
"4/9/2020", "4/6/2020", "4/5/2020"), x = c(-3.444434096, -5.554643467, 
-5.819128168, -6.528574452, -6.528574452, -8.59555826, -3.025536602, 
-2.698376659, -3.483474963, -7.696235263, NA, -7.572170935, -7.185040842, 
-7.492766547, -3.728460293, -7.773342378, -7.773342378, NA, -6.601276462, 
NA, -6.315658227, -5.421106712, -5.421106712, -2.3212135, -3.40345796, 
-2.942817915, -2.942817915, NA, -1.858551108, -0.264005923, -0.264005923, 
0.192899359, -0.204841155, -0.107794142, -0.087664372, NA), y = c(-0.095663228, 
-2.228724135, NA, -1.287448285, -2.090320147, -2.090320147, 1.269873112, 
-2.64716097, -2.680828961, -2.335090584, -4.117893947, 0.375126608, 
0.375126608, NA, -3.939176552, NA, -1.797405353, -5.27192525, 
-5.27192525, -4.937367195, -4.885790778, -5.611024982, -5.215911023, 
-9.277147196, -9.277147196, -4.896429851, -7.053994787, 1.022381641, 
-1.398690039, 0.058918339, 0.058918339, -1.900802177, -1.253476157, 
1.283432302, 0.519769206, 0.519769206)), class = "data.frame", row.names = c(NA, 
-36L))

填充需求 #

• 变量x的NA值按对应自然日期向前16天的所有非NA数值的均值填充，不按观测行顺序取前15条计算
• 变量y的NA值按对应自然日期前后各7天范围内的所有非NA数值的最大值填充

实现代码 #

小数据量方案（tidyverse基础实现，无需额外安装其他包） #

依赖dplyr和lubridate，先完成数据预处理：

library(dplyr)
library(lubridate)

# 把字符型日期转为Date格式，原日期是月/日/年格式
d1 <- d1 %>% mutate(date = mdy(date))

逐行计算窗口值填充缺失：

d1_filled <- d1 %>%
  rowwise() %>%
  mutate(
    # 填充x：取当前日期往前16天的x均值
    x = ifelse(
      is.na(x),
      mean(d1$x[d1$date >= date - days(16) & d1$date <= date], na.rm = TRUE),
      x
    ),
    # 填充y：取当前日期前后各7天的y最大值
    y = ifelse(
      is.na(y),
      max(d1$y[d1$date >= date - days(7) & d1$date <= date + days(7)], na.rm = TRUE),
      y
    )
  ) %>%
  ungroup()

大数据量优化方案（slider包实现，性能更高） #

先安装依赖包：install.packages("slider")

library(slider)

d1_filled <- d1 %>%
  arrange(date) %>% # 先按日期升序排序
  mutate(
    # 滑动窗口计算x的前16天均值
    x = slide_index_dbl(
      .x = x,
      .i = date,
      .f = ~mean(.x, na.rm = TRUE),
      .before = days(16)
    ),
    # 滑动窗口计算y的前后7天最大值
    y = slide_index_dbl(
      .x = y,
      .i = date,
      .f = ~max(.x, na.rm = TRUE),
      .before = days(7),
      .after = days(7)
    )
  )

两种方案的计算结果完全一致，可根据数据规模选择使用。

内容的提问来源于stack exchange，提问作者Lincon