如何在R中基于多列条件生成定制格式的新变量？

现有如下R语言data.frame，其中CP系列列（CP_A/CP_B/CP_C/CP_D）的取值格式为hueX:Y-Z，且每行的hueX与hue列值一致。需要生成新变量output，规则如下：

• 当TCK_A不等于"Yes"时，output取值为CP_A的值；
• 当TCK_A等于"Yes"时，output为基于CP_B、CP_C、CP_D计算得到的hueX:mean(Y)-mean(Z)格式字符串，其中mean(Y)是这三列中Y值的均值，mean(Z)是这三列中Z值的均值。

原始数据 #

data_trial = data.frame(hue=c(2,8,3,2,5),
          CP_A=c("hue2:6789-99987", "hue8:7854-98743","hue3:60987-123423","hue2:7658-873457","hue5:45658-676549"),
          CP_B=c("hue2:6782-99987", "hue8:7859-98734","hue3:60989-123407","","hue5:45697-676598"),
          CP_C=c("hue2:6785-99989", "hue8:6797-99980","hue3:60995-123434","hue2:7657-8734509","hue5:45667-676500"),
          CP_D=c("", "hue8:6756-99987","hue3:60942-123412","hue2:7650-87345065","hue5:45699-676565"),
          TCK_A=c("Yes", "", "Yes", "Yes", "Yes"))

期望输出 #

hue              CP_A              CP_B              CP_C               CP_D TCK_A             output
1   2   hue2:6789-99987   hue2:6782-99987   hue2:6785-99989                      Yes    hue2:6784-99988
2   8   hue8:7854-98743   hue8:7859-98734   hue8:6797-99980    hue8:6756-99987          hue8:7854-98743
3   3 hue3:60987-123423 hue3:60989-123407 hue3:60995-123434  hue3:60942-123412   Yes  hue3:60975-123418
4   2  hue2:7658-873457                   hue2:7657-8734509 hue2:7650-87345065   Yes hue2:7654-48039787
5   5 hue5:45658-676549 hue5:45697-676598 hue5:45667-676500  hue5:45699-676565   Yes  hue5:45688-676554

已尝试的方法及问题 #

方法一：拆分列后结合rowwise和mutate #

该方法仅在TCK_A不等于"Yes"时结果正确，均值部分出现NA：

data_trial %>% 
  separate(CP_B, into=c("hueX_B","Y_BxZ_B"), sep=":") %>%
  separate(CP_C, into=c("hueX_C","Y_CxZ_C"), sep=":") %>%
  separate(CP_D, into=c("hueX_D","Y_DxZ_D"), sep=":") %>%
  separate(Y_BxZ_B, into=c("Y_B", "Z_B"), sep="-") %>%
  separate(Y_CxZ_C, into=c("Y_C", "Z_C"), sep="-") %>%
  separate(Y_DxZ_D, into=c("Y_D", "Z_D"), sep="-") %>%
  mutate(Y_B=as.numeric(Y_B)) %>%
  mutate(Y_C=as.numeric(Y_C)) %>%
  mutate(Y_D=as.numeric(Y_D)) %>%
  mutate(Z_B=as.numeric(Z_B)) %>%
  mutate(Z_C=as.numeric(Z_C)) %>%
  mutate(Z_D=as.numeric(Z_D)) %>%
  rowwise %>%
  mutate(CP_output=ifelse(TCK_A=="Yes", paste0("hue", hue, ":", mean(across(c(Y_B, Y_C, Y_D)), na.rm=TRUE), "-", mean(across(c(Z_B,Z_C,Z_D)), na.rm=TRUE)), CP_A))

方法二：手动计算均值 #

该方法得到了正确的均值，但hue值错误，将所有行的hue值拼接在一起：

data_trials_2 = data_trial %>% 
  separate(CP_B, into=c("hueX_B","Y_BxZ_B"), sep=":") %>%
  separate(CP_C, into=c("hueX_C","Y_CxZ_C"), sep=":") %>%
  separate(CP_D, into=c("hueX_D","Y_DxZ_D"), sep=":") %>%
  separate(Y_BxZ_B, into=c("Y_B", "Z_B"), sep="-") %>%
  separate(Y_CxZ_C, into=c("Y_C", "Z_C"), sep="-") %>%
  separate(Y_DxZ_D, into=c("Y_D", "Z_D"), sep="-") %>%
  mutate(Y_B=as.numeric(Y_B)) %>%
  mutate(Y_C=as.numeric(Y_C)) %>%
  mutate(Y_D=as.numeric(Y_D)) %>%
  mutate(Z_B=as.numeric(Z_B)) %>%
  mutate(Z_C=as.numeric(Z_C)) %>%
  mutate(Z_D=as.numeric(Z_D))

data_trials_2$CP_output= paste0("hue", rowwise(data_trials_2[,1]), ":", round(rowMeans(data_trials_2[,c(4,7,10)], na.rm=TRUE)) , "-",    round(rowMeans(data_trials_2[,c(5,8,11)], na.rm=TRUE)))

正确解决方案 #

可以通过批量拆分CP_B/CP_C/CP_D列，提取Y和Z值后计算均值，再结合条件判断生成output。这里使用tidyr::extract更高效，避免多次拆分：

library(dplyr)
library(tidyr)

data_trial %>%
  # 批量提取CP_B、CP_C、CP_D中的Y和Z值，自动转换为数值类型
  mutate(across(c(CP_B, CP_C, CP_D), ~extract(
    tibble(col = .x),
    col,
    into = c(NA, "Y", "Z"),
    regex = "hue\\d+:(\\d+)-(\\d+)",
    remove = FALSE,
    convert = TRUE
  ) %>% select(Y, Z), .names = "{.col}_{.value}")) %>%
  rowwise() %>%
  mutate(
    # 计算Y的均值（忽略NA）
    mean_Y = mean(c(CP_B_Y, CP_C_Y, CP_D_Y), na.rm = TRUE),
    # 计算Z的均值（忽略NA）
    mean_Z = mean(c(CP_B_Z, CP_C_Z, CP_D_Z), na.rm = TRUE),
    # 按条件生成output，均值取整对齐期望输出
    output = if_else(
      TCK_A == "Yes",
      paste0("hue", hue, ":", round(mean_Y), "-", round(mean_Z)),
      CP_A
    )
  ) %>%
  # 移除中间生成的辅助列，保留原始列和output
  select(-starts_with("CP_B_"), -starts_with("CP_C_"), -starts_with("CP_D_"), -mean_Y, -mean_Z)

代码说明 #

1. 使用across批量处理CP_B/CP_C/CP_D列，通过extract正则匹配提取Y和Z值，同时自动转换为数值类型；
2. rowwise()确保每行独立计算均值，避免向量运算导致的错误；
3. if_else按条件生成output，round用于和期望输出的整数均值对齐；
4. 最后通过select移除中间辅助列，保留原始数据列和目标变量output。

运行上述代码后，即可得到与期望一致的结果。

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