基于特定条件按行统计数值个数的R语言实现需求

示例数据 #

df <- data.frame(New=c("X2", "k 5, N 8", "N30","k 6, N 3", "K5", "S12", "K5", "k 1, N 18"),
                 K_10=c(NA, NA, 3, 4,0,2,NA, NA), 
                 K_11=c(NA, NA, NA, 4,0,3, NA, NA), 
                 K_12=c(NA, 2, NA, NA,0,NA,NA,0), 
                 K_13=c(0, 3, 5, NA,0,5,NA,NA), 
                 K_14=c(NA, 3, 1, 2,10,10,NA,NA),
                 K_15=c(NA, 2, 3, 5,15,10,NA,2),
                 K_16=c(NA, 10, 1, 6,43,10,NA,56),
                 K_17=c(NA, 5, 1, 3,1,10,NA,23),
                 K_18=c(NA, 6, 4, 2,0,10,NA,12),
                 K_19=c(NA, 3, 8, NA,3,10,NA,90),
                 K_20=c(NA, 3, 19, 2,6,10,NA,59),
                 K_21=c(NA, 3, 10, 2,8,10,NA,11),
                 K_22=c(NA, 3, NA, 2,9,10,NA,10),
                 K_23=c(NA, 3, NA, 2,90,10,NA,9))

需求说明 #

需要为数据框新增4列：

• At_least_6：仅当New列格式为k 数字, N 数字（如"k 5, N 8"）时，检查该行K_开头的列中是否至少有6个非NA数值；非目标格式行标记为"Not Applicable"
• Count_at_least_6：统计目标格式行中K_开头列的非NA数值个数；非目标格式行标记为"Not Applicable"
• At_most_5：仅当New列格式为k 数字, N 数字时，检查该行K_开头的列中所有非NA数值是否都属于{1,2,3,4,5}；非目标格式行标记为"Not Applicable"
• Count_at_most_5：统计目标格式行中K_开头列里数值在1-5之间的非NA个数；非目标格式行填0

期望结果 #

df_N <- data.frame(New=c("X2", "k 5, N 8", "N30","k 6, N 3", "K5", "S12", "K5", "k 1, N 18"),
                   K_10=c(NA, NA, 3, 4,0,2,NA, NA), 
                   K_11=c(NA, NA, NA, 4,0,3, NA, NA), 
                   K_12=c(NA, 2, NA, NA,0,NA,NA,0), 
                   K_13=c(0, 3, 5, NA,0,5,NA,NA), 
                   K_14=c(NA, 3, 1, 2,10,10,NA,NA),
                   K_15=c(NA, 2, 3, 5,15,10,NA,2),
                   K_16=c(NA, 10, 1, 6,43,10,NA,56),
                   K_17=c(NA, 5, 1, 3,1,10,NA,23),
                   K_18=c(NA, 6, 4, 2,0,10,NA,12),
                   K_19=c(NA, 3, 8, NA,3,10,NA,90),
                   K_20=c(NA, 3, 19, 2,6,10,NA,59),
                   K_21=c(NA, 3, 10, 2,8,10,NA,11),
                   K_22=c(NA, 3, NA, 2,9,10,NA,10),
                   K_23=c(NA, 3, NA, 2,90,10,NA,9),
                   At_least_6=c("Not Applicable","TRUE", "Not Applicable","TRUE", "Not Applicable", "Not Applicable", "Not Applicable","TRUE"),
                   Count_at_least_6=c("Not Applicable",8, "Not Applicable",6, "Not Applicable", "Not Applicable", "Not Applicable",5),
                   At_most_5=c("Not Applicable","FALSE", "Not Applicable","FALSE", "Not Applicable", "Not Applicable", "Not Applicable","FALSE"),
                   Count_at_most_5=c(0,0,0,0,0,0,0,0))

print(df_N)

解决方案（使用dplyr） #

library(dplyr)

df_processed <- df %>%
  # 标记是否为符合格式的目标行
  mutate(is_target = grepl("^k \\d+, N \\d+$", New)) %>%
  # 计算每行K_开头列的非NA数值总数
  mutate(total_non_na = rowSums(!is.na(select(., starts_with("K_"))))) %>%
  # 计算每行K_开头列中数值在1-5之间的非NA数量
  mutate(count_1_5 = rowSums(between(select(., starts_with("K_")), 1, 5) & !is.na(select(., starts_with("K_"))))) %>%
  # 生成At_least_6列
  mutate(At_least_6 = case_when(
    is_target ~ as.character(total_non_na >= 6),
    TRUE ~ "Not Applicable"
  )) %>%
  # 生成Count_at_least_6列
  mutate(Count_at_least_6 = case_when(
    is_target ~ as.character(total_non_na),
    TRUE ~ "Not Applicable"
  )) %>%
  # 生成At_most_5列：判断所有非NA数值是否都在1-5范围内
  mutate(At_most_5 = case_when(
    is_target ~ as.character(total_non_na == count_1_5 & total_non_na > 0),
    TRUE ~ "Not Applicable"
  )) %>%
  # 生成Count_at_most_5列
  mutate(Count_at_most_5 = ifelse(is_target, count_1_5, 0)) %>%
  # 移除中间辅助列
  select(-is_target, -total_non_na, -count_1_5)

print(df_processed)

代码说明 #

1. 正则匹配目标行：用grepl("^k \\d+, N \\d+$", New)精准识别New列符合k 数字, N 数字格式的行
2. 高效统计数值：通过rowSums结合列选择，快速计算每行的非NA总数、指定范围数值数量，适合处理数千行的大数据集
3. 条件赋值：用case_when和ifelse根据是否为目标行，分别返回对应统计结果或标记值，逻辑清晰易维护

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