R语言：跨列比较生成FINALRACE新列的实现需求

原始数据框 #

df <- cbind.data.frame(
      ID = c("123", "604", "789", "193", "872"),
      r1 = c("HISPANIC", "WHITE", "ASIAN", "BLACK", "ASIAN"),
      r2 = c(NA, NA, "WHITE", "HISPANIC", "OTHER"),
      r3 = c(NA, NA, NA, "OTHER", "OTHER"))

数据结构：

ID       r1       r2    r3
1 123 HISPANIC     <NA>  <NA>
2 604    WHITE     <NA>  <NA>
3 789    ASIAN    WHITE  <NA>
4 193    BLACK HISPANIC OTHER
5 872    ASIAN    OTHER OTHER

需求说明 #

新增列FINALRACE，需遵循以下规则：

• 规则1：若该行任意列（r1/r2/r3）包含HISPANIC，则FINALRACE取值为HISPANIC
• 规则2：若r2和r3均为NA，则返回r1的值
• 规则3：其余情况返回OTHER

尝试过的错误代码 #

df$FINALRACE <- ifelse(df == 'HISPANIC', 'HISPANIC',
                         ifelse(df$r2 == '', as.character(r1), 'OTHER'))
    
df<- df %>% mutate(FINALRACE = if_else(df == 'HISPANIC', 'HISPANIC',
                                                 ifelse(df$r2 == '', as.character(r1),'OTHER')))

期望结果 #

ID       r1       r2    r3    FINALRACE
1 123 HISPANIC     <NA>  <NA>   HISPANIC
2 604    WHITE     <NA>  <NA>   WHITE
3 789    ASIAN    WHITE  <NA>   OTHER
4 193    BLACK HISPANIC OTHER   HISPANIC
5 872    ASIAN    OTHER OTHER   OTHER

正确实现方法 #

方法1：Base R实现 #

# 判断每行是否包含HISPANIC
has_hispanic <- apply(df[, c("r1", "r2", "r3")], 1, function(x) any(x == "HISPANIC", na.rm = TRUE))
# 判断r2和r3是否均为NA
r2r3_all_na <- is.na(df$r2) & is.na(df$r3)

# 按规则赋值
df$FINALRACE <- ifelse(has_hispanic, "HISPANIC",
                       ifelse(r2r3_all_na, as.character(df$r1), "OTHER"))

方法2：dplyr管道实现 #

library(dplyr)

df <- df %>%
  rowwise() %>%
  mutate(
    # 每行判断是否有HISPANIC
    has_hispanic = any(c(r1, r2, r3) == "HISPANIC", na.rm = TRUE),
    # 判断r2和r3是否都是NA
    r2r3_all_na = is.na(r2) & is.na(r3),
    # 按规则生成FINALRACE
    FINALRACE = case_when(
      has_hispanic ~ "HISPANIC",
      r2r3_all_na ~ r1,
      TRUE ~ "OTHER"
    )
  ) %>%
  # 删除临时生成的辅助列
  select(-has_hispanic, -r2r3_all_na)

错误代码问题分析 #

1. 直接使用df == 'HISPANIC'会返回整个数据框的逻辑矩阵，ifelse会逐元素处理，无法实现整行判断的需求
2. 用df$r2 == ''判断NA是错误的，NA不等于空字符串，正确判断NA的方式是is.na(df$r2)

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