如何判定数据库列值的冗余性并生成Multi?与Number列

针对数据库中指定的一组列，需生成两个新列multi?和Number，规则如下：

• multi?列规则：
  • 标记为0：该组列仅存在单一非NA、非"Unspecified"的值
  • 标记为1：该组列存在多个不同的非"Unspecified"值
  • 标记为"Unspecified"：该组列的唯一非NA值是"Unspecified"
  • 标记为NA：该组列所有值均为NA
• Number列规则：
  • 当multi?为1时，记录该组列中唯一非"Unspecified"值的数量
  • 当multi?为0或"Unspecified"时，记录为1
  • 当multi?为NA时，记录为NA
• 特殊处理：若"Unspecified"与其他非NA值共存，忽略"Unspecified"，仅评估其余值的冗余性

输入示例 #

headbleed_type_dx1 headbleed_type_dx2 headbleed_type_dx3 headbleed_type_dx4
1      Intracerebral      Intracerebral      Intracerebral               <NA>      
2      Intracerebral      Subarachnoid                <NA>           Subdural      
3        Unspecified      Intracerebral           Subdural      Intracerebral      
4        Unspecified               <NA>                <NA>               <NA>               
5               <NA>               <NA>                <NA>               <NA>               

输出示例 #

Multi?       Number
1 0            1
2 1            3
3 1            2
4 Unspecified  1
5 NA           NA 

实现方案（以R语言为例） #

library(dplyr)
library(tidyr)

# 构造输入数据集
input_df <- tibble(
  headbleed_type_dx1 = c("Intracerebral", "Intracerebral", "Unspecified", "Unspecified", NA),
  headbleed_type_dx2 = c("Intracerebral", "Subarachnoid", "Intracerebral", NA, NA),
  headbleed_type_dx3 = c("Intracerebral", NA, "Subdural", NA, NA),
  headbleed_type_dx4 = c(NA, "Subdural", "Intracerebral", NA, NA)
)

# 按规则处理生成目标列
output_df <- input_df %>%
  rowwise() %>%
  mutate(
    # 提取当前行所有目标列的值，过滤NA和Unspecified（仅保留有效评估值）
    valid_vals = list(na.omit(c_across(starts_with("headbleed_type_dx")))),
    valid_vals = list(valid_vals[valid_vals != "Unspecified"]),
    # 判断是否仅存在Unspecified
    only_unspec = all(na.omit(c_across(starts_with("headbleed_type_dx"))) == "Unspecified"),
    # 判断是否全为NA
    all_na = all(is.na(c_across(starts_with("headbleed_type_dx")))),
    # 生成Multi?列
    `Multi?` = case_when(
      all_na ~ NA_character_,
      only_unspec ~ "Unspecified",
      length(unique(valid_vals)) == 1 ~ "0",
      TRUE ~ "1"
    ),
    # 生成Number列
    Number = case_when(
      all_na ~ NA_integer_,
      only_unspec ~ 1L,
      length(unique(valid_vals)) == 1 ~ 1L,
      TRUE ~ length(unique(valid_vals))
    )
  ) %>%
  select(`Multi?`, Number)

# 查看结果
print(output_df, n = 5)

代码说明 #

• 使用rowwise()按行处理每一条数据
• 通过c_across()提取目标列的所有值，过滤掉NA和"Unspecified"得到有效评估值
• 依次判断全NA、仅Unspecified、单一有效值、多有效值四种场景，生成对应列
• 最后筛选出Multi?和Number列得到结果

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