按列前缀汇总R数据框非缺失值生成updrs评分

问题说明 #

现有一个R语言DataFrame，其中缺失值以"Z"（含小写"z"）和NA表示，数值以字符型存储（如"0"、"1"）。需要生成updrs1、updrs2、updrs3三个评分字段，分别对列名前缀为NP1、NP2、NP3的列中的非缺失值求和，要求：

• 输出所有值均为字符型
• 不能将NA/Z/z当作0处理

示例数据 #

dummy_df <- data.frame(
  subject_id = seq(1,6,1), 
  OTHERV1 = c(1,1,0,0,1,1),
  NP1VAR1 = c("Z","0","Z","Z","Z","Z"),
  NP1VAR2 = c("Z","0","Z","Z","Z","Z"),
  NP1VAR3 = c("Z","3","Z","Z","Z","Z"),
  NP2VAR1 = c("Z","2","Z","Z","Z","Z"), 
  NP2VAR2 = c("Z","0","Z","Z","Z","Z"),
  NP2VAR3 = c("Z","0","Z","Z","Z","Z"),
  NP3VAR1 = c("Z","4","Z","Z","Z","Z"),
  NP3VAR2 = c("Z","0","Z","Z","z","Z"),
  NP3VAR3 = c("Z","0","Z","Z","Z",NA),
  OTHERV2 = c(NA,NA,NA,NA,NA,NA)
)

期望输出 #

可行解决方案 #

通过dplyr和tidyr的组合操作实现需求，代码如下：

library(dplyr)
library(tidyr)

desired_output <- select(dummy_df, c(subject_id, starts_with("NP"))) %>%
  mutate(across(all_of(everything()), ~ifelse(. %in% c("Z", "z"), NA, .))) %>%
  pivot_longer(cols = starts_with("NP"), names_to = c(".value", "np_var"), names_sep = "VAR") %>%
  group_by(subject_id) %>%
  summarize(updrs1 = sum(as.numeric(NP1), na.rm = FALSE),
            updrs2 = sum(as.numeric(NP2), na.rm = FALSE), 
            updrs3 = sum(as.numeric(NP3), na.rm = FALSE), .groups = "drop") %>%
  mutate(across(all_of(everything()), as.character)) %>%
  replace(is.na(.), "Z")

代码逻辑说明 #

1. 筛选目标列：只保留subject_id和所有以NP开头的列，排除无关列
2. 统一缺失值标记：将"Z"和"z"替换为NA，便于后续求和时识别缺失值
3. 重构数据格式：通过pivot_longer拆分列名，将同一前缀的列聚合到一起
4. 分组求和：按subject_id分组，对每个前缀对应的列求和，na.rm = FALSE确保只要存在缺失值，求和结果就为NA
5. 格式转换：将所有列转为字符型，再把NA替换回"Z"，符合输出要求

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