无需循环实现DataFrame的拆分与聚合优化方案问询

问题描述 #

我有一个DataFrame，需要按Name和Type分组；当Scenario字段包含“up”或“down”时，将其拆分为Up和Down独立列（对应Value取两者的最小值）。之前用字符串拆分加循环实现，操作繁琐，求无循环的更优方案。

输入数据 #

input = structure(list(Name = c("Alice", "Alice ", "Tim", "Tim", "Greg", 
"Greg"), Value = c("-5", "6", "5", "-2", "5", "7"), Type = c("Sales", 
"Sales", "Returns", "Returns", "Promo", "Promo"), Scenario = c("Down", 
"Up", "Down_RED_One", "Up_RED_One", "BLUE", "YELLOW")), row.names = c(NA, 
6L), class = "data.frame")

期望输出 #

output = structure(list(Name = c("Alice", "Tim", "Greg", "Greg"), Value = c("-5", 
"-2", "5", "7"), Type = c("Sales", "Returns", "Promo", "Promo"
), Scenario = c("N/A", "RED_One", "BLUE", "YELLOW"), Up = c("6", 
"-2", "N/A", "N/A"), Down = c("-5", "5", "N/A", "N/A")), row.names = 1:4, class = "data.frame")

无循环解决方案 #

使用tidyverse工具链实现向量化操作，完全避免循环，代码清晰且效率更高：

library(tidyverse)

# 1. 数据预处理：统一格式、提取关键信息
input_clean <- input %>%
  mutate(
    Name = str_trim(Name),  # 去除Name字段的多余空格
    Direction = str_extract(Scenario, regex("Up|Down", ignore_case = TRUE)),  # 提取Up/Down方向
    # 提取Scenario中除Up/Down之外的基础部分，空值替换为N/A
    Scenario_base = str_remove(Scenario, regex("^(Up|Down)(_)?", ignore_case = TRUE)) %>%
      if_else(. == "", "N/A", .),
    Value_num = as.numeric(Value)  # 转数值型用于计算最小值
  )

# 2. 标记分组属性：判断是否为Up/Down组，计算组内最小值和统一Scenario后缀
group_info <- input_clean %>%
  group_by(Name, Type) %>%
  summarize(
    is_updown = any(!is.na(Direction)),
    scenario_common = if_else(is_updown, first(Scenario_base), NA_character_),
    min_value = if_else(is_updown, min(Value_num), NA_real_),
    .groups = "drop"
  )

# 3. 处理Up/Down分组：转宽表生成Up/Down列
updown_groups <- input_clean %>%
  inner_join(group_info %>% filter(is_updown), by = c("Name", "Type")) %>%
  select(Name, Type, Direction, Value) %>%
  pivot_wider(
    names_from = Direction,
    values_from = Value,
    values_fill = "N/A"
  ) %>%
  left_join(group_info %>% filter(is_updown), by = c("Name", "Type")) %>%
  mutate(
    Value = as.character(min_value),
    Scenario = scenario_common
  ) %>%
  select(Name, Value, Type, Scenario, Up, Down)

# 4. 处理非Up/Down分组：保留原行，填充Up/Down为N/A
non_updown_groups <- input_clean %>%
  inner_join(group_info %>% filter(!is_updown), by = c("Name", "Type")) %>%
  mutate(
    Up = "N/A",
    Down = "N/A",
    Scenario = Scenario_base
  ) %>%
  select(Name, Value, Type, Scenario, Up, Down)

# 5. 合并结果并排序
output <- bind_rows(updown_groups, non_updown_groups) %>%
  arrange(Name, Type)

代码说明 #

• 数据预处理：统一Name格式，从Scenario中拆分出方向和基础场景，同时把Value转成数值型方便后续计算最小值。
• 分组标记：通过分组统计判断每个(Name, Type)组是否包含Up/Down场景，提前计算组内最小值和统一的场景后缀。
• 转宽表处理：用pivot_wider直接把方向列转成Up和Down列，替代循环的行处理逻辑。
• 合并结果：分别处理两类分组后合并，保证输出结构和期望一致。

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