如何识别指定列缺失行并完成缺失ID基线统计等R语言实现

首先加载原始数据：

ID <- c(1,1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3)
X1<-c(1.1,0.2,0.4,0.8,1.3,2.3,1.1,3.2,NA,0.8,2.1,NA,1.1,0.2,0.4,0.8,NA,0.6)
X2<-c(0.8,NA,1.2,0.3,NA,NA,0.8,NA,1.5,2.7,2.2,NA,0.8,3.1,1.7,0.3,1.1,2.4)
X3<-c(0.1,0.3,1.1,2.2,0,NA,0.1,3.3,1.4,2.3,0,NA,NA,0.3,2.8,2.3,0,NA)
Time<-c("baseline","week1","week2","week3","week4","week5","baseline","week1","week2","week3","week4","week5","baseline","week1","week2","week3","week4","week5")
data<-data.frame(ID,X1,X2,X3,Time)

需求1：统计缺失值数量并计算对应ID的baseline均值±标准差 #

统计各列缺失值数量 #

# 统计X1、X2、X3的缺失值数量
na_counts <- colSums(is.na(data[, c("X1", "X2", "X3")]))
print(na_counts)

运行后会输出每列的缺失值个数，例如X1有3个缺失、X2有4个缺失、X3有4个缺失。

计算存在缺失的ID的baseline均值±标准差 #

先定位所有存在缺失值的ID，再筛选这些ID的baseline数据进行统计：

# 若未安装dplyr包，先运行 install.packages("dplyr")
library(dplyr)

# 找出所有在X1/X2/X3中存在缺失的ID
missing_ids <- unique(data$ID[apply(is.na(data[, c("X1", "X2", "X3")]), 1, any)])

# 筛选baseline数据并计算均值、标准差，整理为均值±标准差格式
baseline_stats <- data %>%
  filter(ID %in% missing_ids, Time == "baseline") %>%
  summarise(
    across(
      X1:X3,
      list(
        mean = ~round(mean(., na.rm = TRUE), 2),
        sd = ~round(sd(., na.rm = TRUE), 2)
      )
    )
  ) %>%
  mutate(
    X1 = paste0(X1_mean, " ± ", X1_sd),
    X2 = paste0(X2_mean, " ± ", X2_sd),
    X3 = paste0(X3_mean, " ± ", X3_sd)
  ) %>%
  select(X1, X2, X3)

print(baseline_stats)

需求2：找出X2、X3首次出现缺失值的时间点 #

按ID分组，定位每个ID下X2、X3第一次出现缺失的时间点：

first_na_time <- data %>%
  group_by(ID) %>%
  summarise(
    first_na_X2 = ifelse(any(is.na(X2)), Time[which(is.na(X2))[1]], "无缺失"),
    first_na_X3 = ifelse(any(is.na(X3)), Time[which(is.na(X3))[1]], "无缺失")
  )

print(first_na_time)

需求3：找出各列对应的存在缺失的ID #

分别提取X1、X2、X3中存在缺失值的ID：

# X1存在缺失的ID
X1_missing_ids <- unique(data$ID[is.na(data$X1)])
# X2存在缺失的ID
X2_missing_ids <- unique(data$ID[is.na(data$X2)])
# X3存在缺失的ID
X3_missing_ids <- unique(data$ID[is.na(data$X3)])

# 输出结果
cat("X1存在缺失的ID：", paste(X1_missing_ids, collapse = ", "), "\n")
cat("X2存在缺失的ID：", paste(X2_missing_ids, collapse = ", "), "\n")
cat("X3存在缺失的ID：", paste(X3_missing_ids, collapse = ", "), "\n")

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