基于均值/等增长规则的R语言数据集NA值填充方案求助

原始数据集 #

library(tibble)
df1 <- tibble::tribble(
  ~Place,  ~Year,  ~Cake,  ~Coffee,    ~Tea,
  "Local Cafe 1", 2022,   50, 100,    30,
  "Local Cafe 1", 2021,   50, NA, 30,
  "Local Cafe 1", 2020,   50, 80, NA,
  "Local Cafe 1", 2019,   50, 70, 20,
  "Local Cafe 1", 2018,   NA, 60, 20,
  "Local Cafe 2", 2022,   60, NA, 40,
  "Local Cafe 2", 2021,   NA, 50, NA,
  "Local Cafe 2", 2020,   40, 40, NA,
  "Local Cafe 2", 2019,   30, 30, NA,
  "Local Cafe 3", 2022,   30, 40, NA,
  "Local Cafe 3", 2021,   NA, NA, NA
)

实现思路与代码 #

你的填充规则本质是按门店分组，对各指标的时间序列做线性插值+趋势外插/常数填充。用dplyr做分组排序，zoo包的na.approx()可完美匹配所有规则：

• 中间单个/多个NA：自动线性插值（单个取均值，多个按等增长填充）
• 首尾NA：序列有≥2个有效值时按趋势外插；仅1个有效值时用该值填充所有首尾NA

library(dplyr)
library(zoo)

filled_df <- df1 %>%
  # 按门店分组
  group_by(Place) %>%
  # 按年份升序排列，保证时间序列顺序正确
  arrange(Year, .by_group = TRUE) %>%
  # 对所有数值列应用填充逻辑
  mutate(across(c(Cake, Coffee, Tea), ~na.approx(.x, extend = TRUE))) %>%
  # 取消分组
  ungroup()

# 查看完整填充结果
print(filled_df, n = Inf)

结果验证示例 #

• Local Cafe 1的Coffee列：原始60,70,80,NA,100 → 填充后60,70,80,90,100（符合规则1）
• Local Cafe 2的Cake列：原始30,40,NA,60 → 填充后30,40,50,60（符合规则2）
• Local Cafe 2的Coffee列：原始30,40,50,NA → 填充后30,40,50,60（符合规则3）
• Local Cafe 3的Cake列：原始NA,30 → 填充后30,30（符合规则4）

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