大数据框分组下多条件case_when运行缓慢，如何优化提速？

我有一个100万行的长格式数据集，包含约7万个Instrument（企业），每个企业平均有15年数据。按Instrument分组后用case_when生成新变量时效率极低，100行数据耗时约3秒，全量数据预计要8小时以上。现有代码可正常运行但重复计算过多，希望用tidyverse/dplyr优化提速。

数据示例 #

test <- tibble::tibble(
  Year = seq(1998, 2007, by = 1),
  Instrument = rep("4295196544", 10L),
  p_pbt_situation = rep(
    c(
      "payouts_rise_more_than_profits_rise", "payouts_fall_less_than_profits_fall",
      "no current payout", "rising_payout_falling_profit"
    ),
    c(1L, 2L, 6L, 1L)
  ),
  has_ever_payout = rep(1, 10L),
  has_had_payout = rep(1, 10L),
)

原低效代码 #

test %>% group_by(Instrument) %>% 
    mutate(
        Ratchet_occurrence = 
case_when(
     sum(p_pbt_situation == "rising_payout_falling_profit", na.rm=TRUE) == 0 & has_ever_payout==1 ~"never",
     sum(p_pbt_situation == "rising_payout_falling_profit", na.rm=TRUE) == 1 ~"once",
     sum(p_pbt_situation == "rising_payout_falling_profit", na.rm=TRUE) == 2 ~ "twice",
     sum(p_pbt_situation == "rising_payout_falling_profit", na.rm=TRUE) >2 & sum(p_pbt_situation == "rising_payout_falling_profit", na.rm=TRUE) < 5   ~ "three or four",
     sum(p_pbt_situation == "rising_payout_falling_profit", na.rm=TRUE) >4 & sum(p_pbt_situation == "rising_payout_falling_profit", na.rm=TRUE) < 7   ~ "five or six",
     sum(p_pbt_situation == "rising_payout_falling_profit", na.rm=TRUE) >6   ~ "seven or more",
     has_ever_payout == 0 ~ "no payout",
     TRUE ~ NA_character_
        )) %>% ungroup()

优化思路 #

原代码的核心问题是每个case_when条件都重复计算了一次sum(p_pbt_situation == "rising_payout_falling_profit")，分组后每个企业要重复计算7次这个统计量，极大浪费计算资源。优化的关键是先计算一次分组内的统计值，再基于这个值做判断。

优化后的代码 #

方法1：先汇总再关联（最高效，适合大型数据集）

先对每个Instrument计算目标统计量，再通过left_join关联回原数据，彻底避免分组内的重复计算：

# 先计算每个Instrument的目标计数和结果标签
count_df <- test %>%
  group_by(Instrument) %>%
  summarize(
    rpf_count = sum(p_pbt_situation == "rising_payout_falling_profit", na.rm = TRUE),
    has_ever_payout = first(has_ever_payout)  # 假设每个Instrument的has_ever_payout值一致
  ) %>%
  mutate(
    Ratchet_occurrence = case_when(
      has_ever_payout == 0 ~ "no payout",
      rpf_count == 0 ~ "never",
      rpf_count == 1 ~ "once",
      rpf_count == 2 ~ "twice",
      rpf_count %in% 3:4 ~ "three or four",
      rpf_count %in% 5:6 ~ "five or six",
      rpf_count >=7 ~ "seven or more",
      TRUE ~ NA_character_
    )
  ) %>%
  select(Instrument, Ratchet_occurrence)

# 关联回原数据
result <- test %>%
  left_join(count_df, by = "Instrument") %>%
  ungroup()

方法2：分组内先计算统计量再判断（代码更简洁，效率显著提升）

如果不想拆分步骤，可以在分组内先计算一次统计量，再用这个变量做case_when判断：

result <- test %>%
  group_by(Instrument) %>%
  mutate(
    # 只计算一次分组内的目标计数
    rpf_count = sum(p_pbt_situation == "rising_payout_falling_profit", na.rm = TRUE),
    Ratchet_occurrence = case_when(
      has_ever_payout == 0 ~ "no payout",
      rpf_count == 0 ~ "never",
      rpf_count == 1 ~ "once",
      rpf_count == 2 ~ "twice",
      rpf_count %in% 3:4 ~ "three or four",
      rpf_count %in% 5:6 ~ "five or six",
      rpf_count >=7 ~ "seven or more",
      TRUE ~ NA_character_
    )
  ) %>%
  ungroup()

额外优化细节 #

• 用rpf_count %in% 3:4替代rpf_count >2 & rpf_count <5，代码更简洁且执行效率更高
• 把has_ever_payout ==0的判断放在最前面，因为这是全局条件，不需要依赖分组统计量
• 如果has_ever_payout在每个Instrument内的值是一致的，方法1中用first(has_ever_payout)可以避免重复存储，进一步减少内存占用

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