面板数据NA值自定义插补方法问询（含R语言示例）

原始数据 #

policy.1 <- data.frame(country= c("A","B","C","D","E"),
                       year1=c(2,4,NA,3,5),
                       year2=c(3,NA,NA,4,6),
                       year3=c(4,8,2,NA,7),
                       year4=c(5,10,4,NA,8),
                       year5=c(6,12,1,NA,9))

自定义插补规则 #

• 单个NA：用该NA前后数值的均值填补（如国家B的NA用4和8的均值6填补）
• 行首连续NA：用该行第一个有效数值填补（如国家C的前两个NA用2填补）
• 行尾连续NA：用该行最后一个有效数值填补（如国家D的后三个NA用4填补）
• 中间连续NA：用该段NA前后有效数值的均值填补（如国家E的三个NA用(5+9)/2=7填补）

R语言实现代码 #

通过数据长宽格式转换结合分组处理可实现上述规则，需用到dplyr、tidyr和zoo包：

# 安装并加载所需包
install.packages(c("dplyr", "tidyr", "zoo"))
library(dplyr)
library(tidyr)
library(zoo)

# 转成长格式，按国家分组处理时间序列
policy_long <- policy.1 %>%
  pivot_longer(cols = starts_with("year"), names_to = "year", values_to = "value") %>%
  arrange(country, year)

# 分组执行插补逻辑
policy_imputed <- policy_long %>%
  group_by(country) %>%
  mutate(
    # 获取组内首尾非NA值
    first_val = first(value[!is.na(value)]),
    last_val = last(value[!is.na(value)]),
    # 填充行首连续NA
    value = ifelse(is.na(value) & cumsum(!is.na(value)) == 0, first_val, value),
    # 填充行尾连续NA
    value = ifelse(is.na(value) & rev(cumsum(!is.na(rev(value)))) == 0, last_val, value),
    # 处理中间NA：取前后最近非NA值的均值
    prev_non_na = na.locf(value, na.rm = FALSE),
    next_non_na = na.locf(value, na.rm = FALSE, fromLast = TRUE),
    value = ifelse(is.na(value), (prev_non_na + next_non_na)/2, value)
  ) %>%
  select(-first_val, -last_val, -prev_non_na, -next_non_na) %>%
  ungroup()

# 转回宽格式得到最终结果
policy.2 <- policy_imputed %>%
  pivot_wider(names_from = "year", values_from = "value")

最终结果 #

policy.2 <- data.frame(country= c("A","B","C","D","E"),
                       year1=c(2,4,2,3,5),
                       year2=c(3,6,2,4,7),
                       year3=c(4,8,2,4,7),
                       year4=c(5,10,4,4,7),
                       year5=c(6,12,1,4,9))

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