如何用group_by配合条件语句统计DataFrame分组的NA与有效值标识

我有一个名为XYdata的DataFrame，需要按**参与者（ppt）和项目（item）**分组，统计每组的Time列是否存在有效值。如果该列无有效值，说明分组内所有行的Time值都是NA。最终要生成包含N_NAs和N_Values列的表格，期望输出如下：

我已经能通过以下代码按分组统计NA的数量：

XYdata2 <- XYdata %>% group_by(ppt, item) %>% summarise(sum_na_Time = sum(is.na(Time)))

但需要对比分组总行数才能判断，我更倾向于用if或条件语句实现需求。

示例数据 #

XYdata <- setNames(data.frame(matrix(ncol = 3, nrow = 40)), c("item","Time", "ppt")) 
XYdata$item <- c("1","1","1","1","1","1","1","1","1","1","2","2","2","2","2","2","2","2","2","2","3","3","3","3","3","3","3","3","3","3","4","4","4","4","4","4","4","4","4","4") 
XYdata$Time <- c("","","","","1.456","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","3.722","","","","","","")
XYdata$ppt <- c(" ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1", " ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1"," ppt1", " ppt1")

解决方案 #

可以在分组汇总时直接用条件语句判断每组是否存在非NA的Time值，生成目标列：

library(dplyr)

# 先将Time列的空字符串转换为NA（示例中空值用""表示，需转为标准NA）
XYdata_clean <- XYdata %>%
  mutate(Time = ifelse(Time == "", NA, Time))

# 分组统计结果
result <- XYdata_clean %>%
  group_by(ppt, item) %>%
  summarise(
    # 全为NA则N_NAs=1，否则0
    N_NAs = ifelse(all(is.na(Time)), 1, 0),
    # 存在有效值则N_Values=1，否则0
    N_Values = 1 - N_NAs,
    .groups = "drop"
  )

print(result)

关键说明 #

• 示例数据中Time列的空值是字符串""，而非R的标准NA，所以第一步需要做转换，否则is.na()无法识别；
• 用all(is.na(Time))判断分组内是否所有Time值都是NA，结合ifelse直接生成N_NAs；
• N_Values是N_NAs的反向值，直接用1 - N_NAs即可，无需额外判断。

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