在R语言中如何依据有序列表填充数据框内的NA缺失值

核心逻辑：按Town_ID分组后，对每个NA值，先获取其前后最近的非空收入作为上下限，再从给定的有序收入参考列表中筛选处于对应区间的值完成填充，匹配示例规则：区间内有多个候选值时默认取第一个匹配值，分组首尾的NA分别按临近单侧非空值匹配参考列表。

示例数据准备 #

# 给定的有序家庭收入参考列表
HouseholdIncome_list <- c(10000, 20000, 30000, 40000, 50000, 60000, 70000) 

# 原始数据框
Town_ID <- c("A", "A", "A", "A", "B", "B", "B", "B", "B")
HouseholdIncome <- c(10000, 40000, 50000, NA, 20000, 40000, NA, NA, 60000)
df <- data.frame(Town_ID, HouseholdIncome)

tidyverse 实现方案 #

代码简洁易读，适合习惯tidyverse语法的场景：

library(dplyr)
library(zoo)

df_filled <- df %>%
  group_by(Town_ID) %>%
  mutate(
    # 前向填充获取每个NA的下限（最近的前序非空值）
    lower = na.locf(HouseholdIncome, na.rm = FALSE),
    # 后向填充获取每个NA的上限（最近的后序非空值）
    upper = na.locf(HouseholdIncome, na.rm = FALSE, fromLast = TRUE),
    HouseholdIncome = case_when(
      !is.na(HouseholdIncome) ~ HouseholdIncome,
      # 处理分组末尾的NA：取参考列表中大于下限的第一个值
      is.na(upper) ~ HouseholdIncome_list[HouseholdIncome_list > lower][1],
      # 处理分组开头的NA：取参考列表中小于上限的最后一个值
      is.na(lower) ~ rev(HouseholdIncome_list[HouseholdIncome_list < upper])[1],
      # 处理中间的NA：取上下限之间的第一个值
      TRUE ~ HouseholdIncome_list[HouseholdIncome_list > lower & HouseholdIncome_list < upper][1]
    )
  ) %>%
  select(-lower, -upper) %>%
  ungroup()

基础R实现方案 #

无需依赖额外语法框架：

library(zoo)

df_filled <- do.call(rbind, lapply(split(df, df$Town_ID), function(town_df) {
  town_df$lower <- ave(town_df$HouseholdIncome, FUN = function(x) na.locf(x, na.rm = FALSE))
  town_df$upper <- ave(town_df$HouseholdIncome, FUN = function(x) na.locf(x, na.rm = FALSE, fromLast = TRUE))
  town_df$HouseholdIncome <- mapply(function(val, l, u) {
    if (!is.na(val)) return(val)
    if (is.na(u)) return(HouseholdIncome_list[HouseholdIncome_list > l][1])
    if (is.na(l)) return(rev(HouseholdIncome_list[HouseholdIncome_list < u])[1])
    return(HouseholdIncome_list[HouseholdIncome_list > l & HouseholdIncome_list < u][1])
  }, town_df$HouseholdIncome, town_df$lower, town_df$upper)
  return(town_df[, c("Town_ID", "HouseholdIncome")])
}))
rownames(df_filled) <- NULL

规则调整说明 #

如果后续填充规则变更，比如区间内有多个候选值时需要取最后一个，只需要把代码中提取候选值的索引[1]替换为取最后一个的逻辑即可。

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