如何按字母顺序排序分组后的不良事件行（含关联关系行）

原始数据 #

df <- data.frame(
  adverse_event = c(
    "Haemorrhage", "related", "likely related",
    "Other", "related", "likely related", "Pain", "related", "likely related",
    "Subcapsular hematoma", "related", "likely related", "Ascites",
    "related", "likely related", "Hyperbilirubinemia", "related",
    "likely related", "Liver abscess", "related", "likely related",
    "Pleural effusion with drainage", "related", "likely related",
    "Pneumothorax", "related", "likely related", "Biliary leakage / occlusion / fistula",
    "related", "likely related", "Portal vein thrombosis", "related",
    "likely related", "Sepsis", "related", "likely related"
  ),
  grade_1 = c(
    4L, 4L, 0L, 3L, 6L, 1L, 8L, 4L, 5L, 3L, 1L, 3L, NA, NA, NA,
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
    NA, NA, NA, NA, NA
  ),
  grade_2 = c(
    2L, 3L, 0L, 11L, 3L, 7L, 2L, 4L, 2L, 1L, 2L, 0L, 1L, 1L, 0L,
    1L, 0L, 2L, 1L, 1L, 0L, 1L, 2L, 1L, 1L, 1L, 0L, NA, NA, NA, NA,
    NA, NA, NA, NA, NA
  ),
  grade_3 = c(
    1L, 4L, 1L, 5L, 3L, 2L, 2L, 5L, 1L, NA, NA, NA, NA, NA, NA,
    NA, NA, NA, 4L, 5L, 1L, NA, NA, NA, 1L, 1L, 0L, 1L, 2L, 0L, 1L,
    1L, 0L, 1L, 1L, 0L
  ),
  grade_4 = c(
    2L, 4L, 1L, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
    NA, NA, NA, NA, NA
  )
)

需求说明 #

需要将DataFrame中的不良事件按字母顺序排序，同时每个不良事件对应的所有含related的关联行（行数不固定，可能是2行、4行等）与该事件作为一个分组，整体参与排序。

已知可通过grep('related', df$adverse_event, invert = T)获取所有不良事件的行索引，但不清楚如何基于此实现分组排序。期望输出的adverse_event列开头如下：

expected_output_left_column <- data.frame(adverse_event = c( 
"Ascites", "related", "likely related", 
"Biliary leakage / occlusion / fistula", "related", "likely related" ) ) 

解决方案 #

方法1：Base R实现 #

# 1. 获取不良事件的行索引及名称
event_indices <- grep('related', df$adverse_event, invert = TRUE)
event_names <- df$adverse_event[event_indices]

# 2. 为每行分配所属分组名称
df$group <- NA
for (i in seq_along(event_indices)) {
  start <- event_indices[i]
  # 确定分组结束位置：下一个事件的前一行，或最后一行
  end <- if (i < length(event_indices)) event_indices[i+1] - 1 else nrow(df)
  df$group[start:end] <- event_names[i]
}

# 3. 按分组名称排序，删除临时分组列
sorted_df <- df[order(df$group), ]
sorted_df$group <- NULL

# 查看开头结果
head(sorted_df$adverse_event, 6)

方法2：dplyr（tidyverse）实现 #

library(dplyr)

sorted_df <- df %>%
  # 创建分组标识：将事件名称填充到后续的related行
  mutate(group = ifelse(!grepl('related', adverse_event), adverse_event, NA)) %>%
  fill(group, .direction = "down") %>%
  # 按分组名称排序，删除临时列
  arrange(group) %>%
  select(-group)

# 查看开头结果
head(sorted_df$adverse_event, 6)

两种方法均能得到符合要求的排序结果，dplyr方法更简洁直观。

内容的提问来源于stack exchange，提问作者d-math