基于多字符与数值列创建条件列的R语言实现方案

现有如下R数据框：

df <- data.frame(old_farm=c("Yes", "Yes","Yes", "No", "No", "No",  NA ),
                 env_year=c(2011, 2020,2019,2010,2010,2010, NA),
                 global_M=c("Yes", "Yes", "No", "Yes","Yes", "No",NA ),
                 audit_year=c(2014, NA,NA,2010,NA,NA, NA))

输出的df如下：

old_farm env_year global_M audit_year
1      Yes     2011      Yes       2014
2      Yes     2020      Yes         NA
3      Yes     2019       No         NA
4       No     2010      Yes       2010
5       No     2010      Yes         NA
6       No     2010       No         NA
7     <NA>       NA     <NA>         NA

需要生成新数据框df_N，结构与原数据框一致，新增最后一列Situation，输出示例：

df_N <- data.frame(old_farm=c("Yes", "Yes","Yes", "No", "No", "No" , NA ),
                   env_year=c(2011, 2020,2019,2010,2010,2010, NA),
                   global_M=c("Yes", "Yes", "No", "Yes","Yes", "No", NA ),
                   audit_year=c(2014, NA,NA,2010,NA,NA,NA),
                   Situation=c("New costat 2011, Closed antex 2014",
                               "New costat 2020",
                               "New costat 2019",
                               "Closed antex 2010",
                               "missing entry",
                               "Full exit", "Not at all"))

输出的df_N如下：

old_farm env_year global_M audit_year                          Situation
1      Yes     2011      Yes       2014 New costat 2011, Closed antex 2014
2      Yes     2020      Yes         NA                    New costat 2020
3      Yes     2019       No         NA                    New costat 2019
4       No     2010      Yes       2010                  Closed antex 2010
5       No     2010      Yes         NA                      missing entry
6       No     2010       No         NA                          Full exit
7     <NA>       NA     <NA>         NA                         Not at all

生成规则 #

• 当old_farm="Yes"、global_M="Yes"且audit_year非NA时：Situation为"New costat {env_year}, Closed antex {audit_year}"
• 当old_farm="Yes"、global_M="Yes"且audit_year为NA时：Situation为"New costat {env_year}"
• 当old_farm="Yes"、global_M="No"且audit_year为NA时：Situation为"New costat {env_year}"
• 当old_farm="No"、global_M="Yes"且audit_year非NA时：Situation为"Closed antex {audit_year}"
• 当old_farm="No"、global_M="Yes"且audit_year为NA时：Situation为"missing entry"
• 当old_farm="No"、global_M="No"且audit_year为NA时：Situation为"Full exit"
• 所有列均为NA时：Situation为"Not at all"

需要高效的通用代码处理数千行数据，原猜测使用apply但不确定实现方式。

针对数千行的大数据量场景，推荐使用向量化操作替代逐行循环的apply，效率更高且代码可读性更强。

方法1：使用dplyr（推荐） #

dplyr的case_when可以批量处理条件判断，非常适合这类多规则场景：

library(dplyr)

df_N <- df %>%
  mutate(Situation = case_when(
    # 规则1
    old_farm == "Yes" & global_M == "Yes" & !is.na(audit_year) ~ 
      paste0("New costat ", env_year, ", Closed antex ", audit_year),
    # 规则2+3：合并相同结果的条件
    old_farm == "Yes" & is.na(audit_year) ~ 
      paste0("New costat ", env_year),
    # 规则4
    old_farm == "No" & global_M == "Yes" & !is.na(audit_year) ~ 
      paste0("Closed antex ", audit_year),
    # 规则5
    old_farm == "No" & global_M == "Yes" & is.na(audit_year) ~ 
      "missing entry",
    # 规则6
    old_farm == "No" & global_M == "No" & is.na(audit_year) ~ 
      "Full exit",
    # 规则7：所有列都是NA
    is.na(old_farm) & is.na(env_year) & is.na(global_M) & is.na(audit_year) ~ 
      "Not at all",
    # 兜底：未覆盖的情况返回NA（可选）
    TRUE ~ NA_character_
  ))

方法2：使用base R（无需额外包） #

如果不想加载第三方包，可以自定义向量化函数实现：

# 定义规则逻辑函数
get_situation <- function(old_farm, env_year, global_M, audit_year) {
  if (!is.na(old_farm) & !is.na(global_M)) {
    if (old_farm == "Yes") {
      if (global_M == "Yes" & !is.na(audit_year)) {
        return(paste0("New costat ", env_year, ", Closed antex ", audit_year))
      } else {
        return(paste0("New costat ", env_year))
      }
    } else if (old_farm == "No") {
      if (global_M == "Yes") {
        if (!is.na(audit_year)) {
          return(paste0("Closed antex ", audit_year))
        } else {
          return("missing entry")
        }
      } else if (global_M == "No" & is.na(audit_year)) {
        return("Full exit")
      }
    }
  } else if (all(is.na(c(old_farm, env_year, global_M, audit_year)))) {
    return("Not at all")
  }
  return(NA_character_)
}

# 向量化函数并应用
vec_get_situation <- Vectorize(get_situation)
df_N <- df
df_N$Situation <- vec_get_situation(df$old_farm, df$env_year, df$global_M, df$audit_year)

验证结果 #

运行上述任意一种代码后，df_N的输出将与需求完全一致，两种方法都能高效处理数千行数据，其中dplyr的方法在大数据量下性能更优。

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