多选项列数据集按选项聚合weight列求和的简洁方法咨询

问题描述 #

我有一个数据集，其中一道多项选择题的每个选项被拆分为单独列，同时包含weight列。需要对每个选项列进行聚合，计算选择该选项的weight列总和，希望用比for循环更简洁的方法实现。

数据集结构如下：

structure(list(`Medical (treatment for any diagnosed medical condition)` = c("0", NA, "0", "Medical (treatment for any diagnosed medical condition)", "Medical (treatment for any diagnosed medical condition)", "Medical (treatment for any diagnosed medical condition)", "Medical (treatment for any diagnosed medical condition)", "0", "0", "0"), `Dental (preventive or routine care for oral health)` = c("0", NA, "0", "Dental (preventive or routine care for oral health)", "0", "Dental (preventive or routine care for oral health)", "Dental (preventive or routine care for oral health)", "0", "0", "0"), `Vision (preventive or routine care for eye health)` = c("0", NA, "Vision (preventive or routine care for eye health)", "Vision (preventive or routine care for eye health)", "0", "Vision (preventive or routine care for eye health)", "0", "0", "0", "0"), `Wellness incentives program (such as smoking cessation or weight loss programs)` = c("0", NA, "0", "0", "0", "0", "0", "0", "Wellness incentives program (such as smoking cessation or weight loss programs)", "Wellness incentives program (such as smoking cessation or weight loss programs)"), `Gym membership` = c("0", NA, "0", "0", "0", "0", "0", "Gym membership", "Gym membership", "Gym membership"), `Life insurance` = c("Life insurance", NA, "Life insurance", "Life insurance", "0", "Life insurance", "0", "0", "0", "Life insurance"), `Accidental death and dismemberment` = c("0", NA, "0", "Accidental death and dismemberment", "0", "Accidental death and dismemberment", "0", "0", "0", "0"), `Health savings account (HSA)` = c("0", NA, "0", "0", "Health savings account (HSA)", "Health savings account (HSA)", "Health savings account (HSA)", "0", "0", "Health savings account (HSA)"), `Flexible spending account (FSA)` = c("0", NA, "0", "0", "0", "0", "0", "0", "0", "0"), `Health reimbursement arrangements (HRA)` = c("Health reimbursement arrangements (HRA)", NA, "0", "0", "0", "0", "Health reimbursement arrangements (HRA)", "0", "0", "0"), `State medical savings program (MSP)` = c("0", NA, "0", "0", "0", "0", "0", "State medical savings program (MSP)", "0", "0"), `Pharmacy card` = c("0", NA, "0", "0", "0", "0", "0", "0", "0", "0"), `Over-the-counter/supplemental benefits` = c("0", NA, "0", "0", "Over-the-counter/supplemental benefits", "Over-the-counter/supplemental benefits", "0", "0", "0", "0"), `Other, please specify:` = c("0", NA, "0", "0", "0", "0", "0", "0", "0", "0"), weight = c(102099.073110486, 99927.5001779742, 100569.577453382, 98542.6996469168, 38237.391427588, 82498.4815047276, 99924.9006748349, 82498.4815047276, 79954.3106465319, 91795.9294203397)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))

简洁解决方案（基于tidyverse） #

使用tidyverse工具集里的pivot_longer将宽格式数据转为长格式，再分组求和，完全替代for循环，代码更简洁易读：

完整代码 #

# 加载tidyverse包
library(tidyverse)

# 假设你的数据集名为df
df <- structure(...) # 这里替换为你的数据集结构

# 处理逻辑
result <- df %>%
  # 将所有选项列转为长格式，保留weight列
  pivot_longer(
    cols = -weight, # 排除weight列，其余都是选项列
    names_to = "option", # 新列名：选项名称
    values_to = "selected" # 新列名：是否选择该选项
  ) %>%
  # 过滤掉未选择的情况（值为"0"或NA）
  filter(selected != "0" & !is.na(selected)) %>%
  # 按选项分组，计算weight总和
  group_by(option) %>%
  summarise(total_weight = sum(weight, na.rm = TRUE)) %>%
  # 取消分组（可选，方便后续处理）
  ungroup()

# 查看结果
print(result)

代码解释 #

• pivot_longer：把原来每个选项单独一列的宽表，转换为两列（option存储选项名称，selected存储是否选择），让所有选项的选择情况集中在同一列，无需循环处理每个列。
• filter：只保留实际选择了该选项的行，排除代表未选择的"0"和NA值。
• group_by + summarise：按选项分组后，直接计算每组的weight总和，一步完成所有选项的聚合。

这种方法代码简洁、逻辑清晰，后续维护和修改也更方便。

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