如何在R语言DataFrame中每9行后插入空白行以适配Excel可读性

数据准备 #

首先将你的DataFrame赋值到变量中：

df <- structure(list(Dispensary = c("A", "A", "A", "A", "A", "A", "A", 
"A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B", "C", "C", 
"C", "C", "C", "C", "C", "C", "C"), cohort = c(1L, 2L, 3L, 4L, 
5L, 6L, 7L, 8L, 9L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 1L, 2L, 
3L, 4L, 5L, 6L, 7L, 8L, 9L), t0 = c(100, 100, 100, 100, 100, 
100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 
100, 100, 100, 100, 100, 100, 100, 100, 100), t1 = c(46.1, 41.7, 
37.5, 36.2, 35.9, 34.4, 39.8, 38.3, 0, 34.6, 37.4, 31.4, 29.5, 
25.5, 33.2, 30.6, 30.8, 0, 28.6, 30.2, 28.1, 28.8, 30.7, 29.2, 
33.5, 30.3, 0), t2 = c(41.4, 34.6, 38.6, 27.9, 30, 32.8, 35.2, 
0, 0, 35.2, 31.4, 23.9, 24.2, 23.1, 30.1, 24, 0, 0, 26.9, 24.8, 
21, 25.3, 25.8, 25.9, 23.3, 0, 0), t3 = c(29.6, 32.3, 31.7, 25.8, 
29.5, 23.8, 0, 0, 0, 25.2, 28.9, 23, 23.8, 20.5, 22.6, 0, 0, 
0, 20.7, 24, 21.5, 24.9, 23, 23.7, 0, 0, 0), t4 = c(30.9, 28, 
30.4, 24.9, 29.5, 0, 0, 0, 0, 23.1, 23.1, 20.7, 21.8, 19.2, 0, 
0, 0, 0, 21, 20.8, 21.3, 23.7, 20.5, 0, 0, 0, 0), t5 = c(30.3, 
25.6, 23.5, 24.9, 0, 0, 0, 0, 0, 19.4, 22.1, 20, 19.5, 0, 0, 
0, 0, 0, 17.2, 18.5, 20.4, 14.7, 0, 0, 0, 0, 0), t6 = c(30.9, 
24, 23.2, 0, 0, 0, 0, 0, 0, 19.6, 20.3, 17.2, 0, 0, 0, 0, 0, 
0, 20.3, 17.7, 18.2, 0, 0, 0, 0, 0, 0), t7 = c(27.6, 18.5, 0, 
0, 0, 0, 0, 0, 0, 18, 16.1, 0, 0, 0, 0, 0, 0, 0, 14.2, 14.8, 
0, 0, 0, 0, 0, 0, 0), t8 = c(17.8, 0, 0, 0, 0, 0, 0, 0, 0, 17.2, 
0, 0, 0, 0, 0, 0, 0, 0, 11.4, 0, 0, 0, 0, 0, 0, 0, 0)), row.names = c(NA, 
-27L), class = c("tbl_df", "tbl", "data.frame"))

方法一：使用tidyverse工具（dplyr + tibble） #

适合已经使用tidyverse生态的场景，代码简洁易读：

library(dplyr)
library(tibble)

# 按Dispensary分组，每组后添加一行全NA的空白行，最后移除最后多余的空白行
df_with_blank <- df %>%
  group_by(Dispensary) %>%
  group_modify(~ bind_rows(.x, tibble(!!!set_names(rep(NA, ncol(df)), colnames(df))))) %>%
  ungroup() %>%
  slice(-n())

逻辑说明：

• group_by(Dispensary) 按药房分组，每组正好9行
• group_modify 对每个分组执行操作，用bind_rows追加一行和原数据列名一致的全NA行
• slice(-n()) 去掉最后一行空白（最后一组后不需要额外空白行）

方法二：使用base R #

无需加载额外包，适合轻量场景：

# 定位所有cohort=9的行（每组的最后一行）
group_ends <- which(df$cohort == 9)

# 创建空白行模板，列名与原数据一致
blank_row <- setNames(as.list(rep(NA, ncol(df))), colnames(df))

# 从后往前插入空白行，避免插入后位置偏移
result <- df
for (pos in rev(group_ends[-length(group_ends)])) {
  result <- rbind(result[1:pos, ], blank_row, result[(pos+1):nrow(result), ])
}

逻辑说明：

• group_ends[-length(group_ends)] 排除最后一组的结束行，避免在末尾插入空白
• 反向遍历插入位置，防止前面插入后导致后续位置偏移

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