如何在R中基于类别权重实现总额向子类别按比例分配？

错误原因 #

你之前的代码问题出在分组逻辑：group_by(region, Country, Employee)会把每个员工组内的数值求和作为分母，但实际上总额是区域（region）级别的，应该以整个区域下的V1/V2/V3总和作为分母计算占比。

正确实现代码 #

library(dplyr)

df1 <- data.frame(region = c("a","a","a","a","a","a","a"),
                  Country = c("ax","ax","ax","ay","ay","az","az"),
                  Employee = c("axa","axc","axc","aya","ayb","aza","azc"),
                  V1 = c(10,10,10,15,12,18,19),
                  V2 = c(20,20,15,13,17,20,18),
                  V3 = c(8,9,10,12,15,30,10))

df2 <- data.frame (territory= c("a","b","c","d"),
                   total = c(2000,1000,5000,3000))

df_calc <- df1 %>% 
  left_join(df2, by = c("region" = "territory")) %>%
  # 仅按region分组，计算区域内各V列的总和
  group_by(region) %>%
  mutate(
    # 对V1-V3列，计算分配值：总额 * 该行值 / 区域内该列总和
    across(V1:V3, ~ total * . / sum(.), .names = "{.col}_allocation")
  ) %>%
  ungroup()

# 查看结果
print(df_calc)

逻辑说明 #

• 关联两个数据框后，按region分组，确保分母是整个区域下对应V列的总和
• across(V1:V3, ...)批量处理所有需要分配的列，.names参数自动生成带_allocation后缀的新列
• 最终每行的分配值 = 区域总额 × 该行V值 / 区域内该V列的总和，完全符合三率法的分配逻辑

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