无循环实现数据分组与汇总：R语言数据处理需求

需求 #

• 对SalesType = "Sales"的数据：
  • 按Name分组，每组仅保留一行
  • Value取分组内最小值，Level1设为该最小值的字符串形式
  • Level2设为分组内Value最大值的字符串形式
• 对SalesType = "Promo"的数据：保留所有行及原有Level1、Level2、Value值

输入数据 #

input = structure(list(BusinessDate = structure(c(1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000), class = c("POSIXct", "POSIXt"), tzone = ""), Name = c("JANE_T", "JANE_T", "JANE_T", "JANE_T", "FRANK_E", "FRANK_E", "FRANK_E", "FRANK_E", "TOM_S", "TOM_S", "TOM_S", "TOM_S", "REG_W", "REG_W", "REG_W", "REG_W", "SALLY_M", "SALLY_M", "SALLY_M", "SALLY_M"), Report = c("Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual"), SalesType = c("Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Sales", "Promo", "Promo", "Promo", "Promo"), CustCode = c("N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A"), Currency = c("USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD"), Level1 = c("VER0.75", "VER0.75", "VER1.25", "VER1.25", "VER0.75", "VER0.75", "VER1.25", "VER1.25", "VER0.75", "VER0.75", "VER1.25", "VER1.25", "VER0.75", "VER0.75", "VER1.25", "VER1.25", "VER0.75", "VER0.75", "VER1.25", "VER1.25"), Level2 = c("PAT0.50", "PAT2.00", "PAT0.50", "PAT2.00", "PAT0.50", "PAT2.00", "PAT0.50", "PAT2.00", "PAT0.50", "PAT2.00", "PAT0.50", "PAT2.00", "PAT0.50", "PAT2.00", "PAT0.50", "PAT2.00", "PAT0.50", "PAT2.00", "PAT0.50", "PAT2.00"), Value = c(12, 12, -12, 12, 7, 7, -4, -8, -1, 11, 13, -1, -2, -2, 2, 2, 1, 1, 1, 1)), row.names = c(NA, 20L), class = "data.frame")

无循环实现代码 #

使用dplyr包的分组和条件逻辑处理，全程无需循环：

library(dplyr)

output <- input %>%
  # 按销售类型和姓名分组，确保Sales类型的分组计算准确，Promo类型分组不影响结果
  group_by(SalesType, Name) %>%
  # 计算每组Value的最小、最大值，作为临时列
  mutate(
    min_val = min(Value, na.rm = TRUE),
    max_val = max(Value, na.rm = TRUE)
  ) %>%
  # 根据销售类型更新字段：Sales类型替换Level1/Level2和Value，Promo类型保留原值
  mutate(
    Level1 = case_when(
      SalesType == "Sales" ~ as.character(min_val),
      TRUE ~ Level1
    ),
    Level2 = case_when(
      SalesType == "Sales" ~ as.character(max_val),
      TRUE ~ Level2
    ),
    Value = case_when(
      SalesType == "Sales" ~ min_val,
      TRUE ~ Value
    )
  ) %>%
  # Sales类型每组只保留一行（处理后所有行内容一致），Promo类型保留所有行
  distinct(SalesType, Name, .keep_all = TRUE) %>%
  # 取消分组状态
  ungroup() %>%
  # 删除临时计算的列
  select(-min_val, -max_val)

输出结果 #

运行代码后得到的output与需求完全匹配：

output = structure(list(BusinessDate = structure(c(1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000, 1678626000), class = c("POSIXct", "POSIXt"), tzone = ""), Name = c("JANE_T", "FRANK_E", "TOM_S", "REG_W", "SALLY_M", "SALLY_M", "SALLY_M", "SALLY_M"), Report = c("Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual", "Annual"), SalesType = c("Sales", "Sales", "Sales", "Sales", "Promo", "Promo", "Promo", "Promo"), CustCode = c("N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A", "N/A"), Currency = c("USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD"), Level1 = c("-12", "-8", "-1", "-2", "VER0.75", "VER0.75", "VER1.25", "VER1.25"), Level2 = c("12", "7", "13", "2", "PAT0.50", "PAT2.00", "PAT0.50", "PAT2.00"), Value = c(-12, -8, -1, -2, 1, 1, 1, 1)), row.names = 13:20, class = "data.frame")

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