R语言按条件分组计算均值并生成新列的实现问题

需求说明 #

需要为数据框lr2_analysis_CO_FIL创建新列R_CO_GC_D，规则如下：

• 仅对HOLIDAY == 'N'的数据执行计算
• 按SEASON和TIME_SLOT分组，计算每组R_CO_GC的均值
• 符合过滤条件的原始值执行公式：R_CO_GC - (分组均值 - 整体均值)

原实现通过生成均值数据框结合case_when硬编码所有分组，导致代码冗余且新列出现大量NA，需优化为自动化方案。

原尝试代码 #

Season_time_slot_media <- lr2_analysis_CO_FIL %>%
  filter(HOLIDAY == 'N') %>%
  group_by(SEASON, TIME_SLOT) %>%
  summarise(media = mean(R_CO_GC))

lr2_analysis_CO_FIL_DE <- lr2_analysis_CO_FIL %>%
  mutate(R_CO_GC_D = case_when(HOLIDAY == 'N' & SEASON == "AUTUMN" & TIME_SLOT == "07-09" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[1]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "AUTUMN" & TIME_SLOT == "10-12" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[2]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "AUTUMN" & TIME_SLOT == "13-15" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[3]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "AUTUMN" & TIME_SLOT == "16-17" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[4]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "AUTUMN" & TIME_SLOT == "18-20" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[5]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "AUTUMN" & TIME_SLOT == "21-06" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[6]) - mean(R_CO_GC)))) %>%
  mutate(R_CO_GC_D = case_when(HOLIDAY == 'N' & SEASON == "SPRING" & TIME_SLOT == "07-09" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[7]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "SPRING" & TIME_SLOT == "10-12" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[8]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "SPRING" & TIME_SLOT == "13-15" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[9]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "SPRING" & TIME_SLOT == "16-17" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[10]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "SPRING" & TIME_SLOT == "18-20" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[11]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "SPRING" & TIME_SLOT == "21-06" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[12]) - mean(R_CO_GC)))) %>%
  mutate(R_CO_GC_D = case_when(HOLIDAY == 'N' & SEASON == "SUMMER" & TIME_SLOT == "07-09" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[13]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "SUMMER" & TIME_SLOT == "10-12" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[14]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "SUMMER" & TIME_SLOT == "13-15" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[15]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "SUMMER" & TIME_SLOT == "16-17" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[16]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "SUMMER" & TIME_SLOT == "18-20" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[17]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "SUMMER" & TIME_SLOT == "21-06" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[18]) - mean(R_CO_GC)))) %>%
  mutate(R_CO_GC_D = case_when(HOLIDAY == 'N' & SEASON == "WINTER" & TIME_SLOT == "07-09" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[19]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "WINTER" & TIME_SLOT == "10-12" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[20]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "WINTER" & TIME_SLOT == "13-15" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[21]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "WINTER" & TIME_SLOT == "16-17" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[22]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "WINTER" & TIME_SLOT == "18-20" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[23]) - mean(R_CO_GC)), 
                               HOLIDAY == 'N' & SEASON == "WINTER" & TIME_SLOT == "21-06" 
                               ~ R_CO_GC - ((Season_time_slot_media$media[24]) - mean(R_CO_GC))))

优化方案 #

使用dplyr窗口函数实现全自动化计算，无需硬编码分组，同时解决NA问题：

lr2_analysis_CO_FIL_DE <- lr2_analysis_CO_FIL %>%
  # 计算非工作日的整体均值
  mutate(overall_mean = mean(R_CO_GC[HOLIDAY == 'N'], na.rm = TRUE)) %>%
  # 按SEASON和TIME_SLOT分组，计算每组非工作日的均值
  group_by(SEASON, TIME_SLOT) %>%
  mutate(group_mean = mean(R_CO_GC[HOLIDAY == 'N'], na.rm = TRUE)) %>%
  ungroup() %>%
  # 生成目标列，仅对非工作日执行公式
  mutate(R_CO_GC_D = case_when(
    HOLIDAY == 'N' ~ R_CO_GC - (group_mean - overall_mean),
    TRUE ~ NA_real_ # 非工作日可设为NA，可根据需求调整
  ))

方案说明 #

• 整体均值计算：直接筛选HOLIDAY == 'N'的数据计算均值，避免包含工作日干扰值
• 分组均值计算：分组后仅计算组内非工作日数据的均值，保证分组统计准确性
• NA处理：添加na.rm = TRUE忽略原始数据中的NA，同时窗口函数自动匹配对应组的均值，避免原代码中索引匹配错误导致的NA
• 扩展性：新增SEASON或TIME_SLOT类别时无需修改代码，自动适配所有分组

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