R语言中按参考数据份额拆分估算目标表格NA值的实现方法

核心计算逻辑 #

• 按行业维度拆分为独立计算单元，每个行业的核算过程互不干扰，适配任意数量的行业、区域分类的批量场景
• 每个行业的待分配总值 = 该行业REGSUM行的合计值 - 该行业下所有非合计、非NA的已知值总和，自动将负数待分配额修正为0，避免不合理的负向估算
• 分配权重仅统计df中为NA的非合计行对应的df2参考值，排除已知值行、合计行的参考数据，与示例规则完全一致
• 内置边界兜底：当NA行的参考值总和为0时，自动规避除零错误，可根据业务需求调整兜底值

代码实现（tidyverse版，适配大规模数据） #

library(dplyr)

estimate_na_value <- function(df_main, df_ref, sum_region_label = "REGSUM") {
  # 关联主表与参考表，提取参考值
  df_merged <- df_main %>%
    left_join(
      df_ref %>% rename(ref_VALUE = VALUE),
      by = c("REGION", "INDUSTRY")
    ) %>%
    # 补全匹配缺失的参考值为0
    mutate(ref_VALUE = ifelse(is.na(ref_VALUE), 0, ref_VALUE)) %>%
    group_by(INDUSTRY) %>%
    # 计算行业层面的核算指标
    mutate(
      # 提取行业合计值
      industry_total = VALUE[REGION == sum_region_label] %>% unique(),
      # 计算非合计非NA的已知值总和
      known_sum = sum(VALUE[REGION != sum_region_label & !is.na(VALUE)], na.rm = T),
      # 计算待分配总额，负数截断为0
      allocate_total = pmax(industry_total - known_sum, 0),
      # 计算NA行对应的参考值组内总和
      na_ref_sum = sum(ref_VALUE[REGION != sum_region_label & is.na(VALUE)], na.rm = T)
    ) %>%
    # 计算估算值
    mutate(
      estimated_value = case_when(
        # 保留原有非NA值
        !is.na(VALUE) ~ VALUE,
        # 保留合计行原值不被修改
        REGION == sum_region_label ~ VALUE,
        # 按权重分配待分配值
        is.na(VALUE) & na_ref_sum > 0 ~ allocate_total * (ref_VALUE / na_ref_sum),
        # 参考值为0时的兜底值，可根据业务调整为等权分配或其他规则
        T ~ 0
      )
    ) %>%
    ungroup()
  
  # 输出原数据结构的结果
  return(df_merged %>% select(REGION, INDUSTRY, VALUE = estimated_value))
}

# 示例数据测试
df  <- data.frame(REGION   = c("REG01","REG02","REG03","REGSUM"),
                  INDUSTRY = c("INDU01","INDU01","INDU01","INDU01"),
                  VALUE    = c(NA,10,NA,30))
df2 <- data.frame(REGION   = c("REG01","REG02","REG03","REGSUM"),
                  INDUSTRY = c("INDU01","INDU01","INDU01","INDU01"),
                  VALUE    = c(5,15,20,40))

result <- estimate_na_value(df, df2)

运行测试得到的结果与示例完全匹配：

REGION INDUSTRY VALUE
1  REG01  INDU01     4
2  REG02  INDU01    10
3  REG03  INDU01    16
4 REGSUM  INDU01    30

Base R 实现版本（无需依赖额外包） #

estimate_na_value_base <- function(df_main, df_ref, sum_label = "REGSUM") {
  # 合并主表与参考表
  df_merged <- merge(df_main, df_ref, by = c("REGION", "INDUSTRY"), suffixes = c("", "_ref"))
  df_merged$VALUE_ref[is.na(df_merged$VALUE_ref)] <- 0
  
  # 按行业拆分循环计算
  res_list <- lapply(split(df_merged, df_merged$INDUSTRY), function(sub_df) {
    # 提取行业合计值
    ind_total <- sub_df$VALUE[sub_df$REGION == sum_label]
    # 计算已知值总和
    known_sum <- sum(sub_df$VALUE[sub_df$REGION != sum_label & !is.na(sub_df$VALUE)], na.rm = T)
    allocate_total <- max(ind_total - known_sum, 0)
    # 定位NA行
    na_row_mask <- sub_df$REGION != sum_label & is.na(sub_df$VALUE)
    na_ref_sum <- sum(sub_df$VALUE_ref[na_row_mask], na.rm = T)
    # 生成新值
    sub_df$VALUE_new <- sub_df$VALUE
    if(na_ref_sum > 0 & allocate_total > 0) {
      sub_df$VALUE_new[na_row_mask] <- allocate_total * (sub_df$VALUE_ref[na_row_mask] / na_ref_sum)
    }
    return(sub_df[, c("REGION", "INDUSTRY", "VALUE_new")])
  })
  
  # 合并结果还原结构
  res <- do.call(rbind, res_list)
  rownames(res) <- NULL
  colnames(res)[3] <- "VALUE"
  return(res)
}

# 测试
result_base <- estimate_na_value_base(df, df2)

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