如何基于饮品类型对drink_vol列行求和并生成对应新列？

目标 #

针对每种饮品类型（1、2、3），根据drink_type_1和drink_type_2标记的类型，创建包含drink_vol_1和drink_vol_2行求和结果的新列。

样本数据 #

study_id <- c(1,2,3,4,5,6)
drink_type_1 <-c(1,2,3,1,2,3)
drink_vol_1 <- c(100,500,1000,100,500,750)
drink_type_2 <-c(NA,3,2,1,NA,3)
drink_vol_2 <- c(NA,125,300,200,NA,500)

drink_df  <- data.frame(study_id,drink_type_1,drink_vol_1,drink_type_2,drink_vol_2) 

drink_df <- drink_df %>% mutate_at(c('drink_type_1','drink_type_2'), as.factor) 

尝试及困惑 #

我一开始觉得需要转换数据框格式，试了这段代码：

drink_pivot <- drink_df %>% pivot_longer(cols = c('drink_type_1','drink_type_2'), names_to = "drink", values_to = "drink_type") 

但之后不知道怎么继续，求后续实现方法。

期望输出 #

study_id <- c(1,2,3,4,5,6)
a <- c(100,0,0,300,0,0)
b <- c(0,500,300,0,500,0)
c <- c(0,125,1000,0,0,1250)

DF  <- data.frame(study_id,a,b,c)

你已经用pivot_longer找对了方向，接下来需要把体积列和类型列对应起来处理，再通过分组求和、转回宽格式得到目标结果。这里提供两种实现方式：

方式一：手动匹配类型与体积列 #

library(tidyverse)

# 1. 转换类型列为长格式，同时匹配对应体积列
drink_long <- drink_df %>%
  pivot_longer(
    cols = starts_with("drink_type_"),
    names_to = "type_col",
    values_to = "drink_type",
    values_drop_na = TRUE
  ) %>%
  # 替换列名中的type为vol，得到对应体积列的名称
  mutate(vol_col = str_replace(type_col, "type", "vol")) %>%
  # 提取对应体积值
  rowwise() %>%
  mutate(drink_vol = get(vol_col)) %>%
  ungroup() %>%
  select(study_id, drink_type, drink_vol)

# 2. 分组求和后转回宽格式
result <- drink_long %>%
  group_by(study_id, drink_type) %>%
  summarise(total_vol = sum(drink_vol), .groups = "drop") %>%
  pivot_wider(
    names_from = drink_type,
    values_from = total_vol,
    values_fill = 0
  ) %>%
  # 重命名列匹配期望输出
  rename(a = `1`, b = `2`, c = `3`)

# 3. 合并回原数据的study_id，确保无遗漏
final_df <- drink_df %>%
  select(study_id) %>%
  left_join(result, by = "study_id")

print(final_df)

方式二：用正则拆分列名（更简洁） #

利用pivot_longer的names_pattern参数，直接拆分类型和体积列的前缀与分组，自动对应匹配：

library(tidyverse)

final_df <- drink_df %>%
  pivot_longer(
    cols = -study_id,
    # 正则拆分列名：提取type/vol和数字分组
    names_pattern = "drink_(type|vol)_(\\d)",
    names_to = c(".value", "group"),
    values_drop_na = TRUE
  ) %>%
  group_by(study_id, type) %>%
  summarise(vol = sum(vol), .groups = "drop") %>%
  pivot_wider(
    names_from = type,
    values_from = vol,
    values_fill = 0
  ) %>%
  rename(a = `1`, b = `2`, c = `3`) %>%
  right_join(drink_df %>% select(study_id), by = "study_id") %>%
  arrange(study_id)

print(final_df)

两种方式运行后都会得到与期望输出一致的结果：

study_id   a   b    c
1        1 100   0    0
2        2   0 500  125
3        3   0 300 1000
4        4 300   0    0
5        5   0 500    0
6        6   0   0 1250

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