R语言dplyr group_by分组后如何计算去重唯一值的权重均值

需求说明 #

按country、state、date三个字段对数据框分组，需计算3项汇总指标：

• 唯一household_id与individual_id组合的计数
• 去重后唯一个体的weights平均值：每个唯一household_id + individual_id组合仅取1次权重值参与计算，剔除同一主体的重复记录
• duration字段的全量记录总和

现有代码已实现唯一个体计数、总时长统计，但权重均值计算未做去重处理，结果存在偏差：

data %>% group_by(country,state,date) %>% 
 summarise(Total_unique = n_distinct(household_id,individual_id),
        Tot_Duration = sum(duration))

示例数据构造代码：

library(dplyr)
data <- data.frame(country = c("US","US","US","US","US","US","IND","IND"),
              state = c("TX","TX","TX","TX","TX","TX","AP","AP"),
              date = c(20220601,20220601,20220601,20220601,20220601,20220601,20220601,20220601),
              household_id = c(100,100,100,101,101,101,102,102),
              individual_id=c(1,2,1,1,2,3,1,1),
              weights = c(100,50,100,200,200,200,100,100),
              duration = c(10,20,30,40,50,60,70,80))

实现代码 #

核心逻辑：计算权重均值时，仅对当前分组下的唯一个体及对应权重去重后求均值，禁止提前对全表去重，避免丢失重复记录对应的duration值导致总时长统计错误。

result <- data %>% 
  group_by(country, state, date) %>% 
  summarise(
    Total_unique = n_distinct(household_id, individual_id),
    Average_weights = {
      # 提取当前分组下唯一个体对应的权重，每个个体仅保留1条记录
      unique_ind_df <- distinct(cur_data(), household_id, individual_id, weights)
      mean(unique_ind_df$weights)
    },
    Tot_Duration = sum(duration),
    .groups = "drop"
  )

结果校验 #

运行代码后输出结果完全符合预期：

• US、TX、20220601分组：共5个唯一个体，权重均值为(100 + 50 + 200 + 200 + 200)/5 = 150，总时长为210
• IND、AP、20220601分组：共1个唯一个体，权重均值为100，总时长为150

常见误区：直接使用mean(weights)计算均值会将同一个体的重复记录权重重复计入，例如示例中100户1号个体出现2次，权重100会被计算2次，导致结果偏差。

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