如何在R中将含重复列名的宽格式Excel表转为长格式

针对你的场景，核心是利用Excel导入后列名的字段名...数字格式，通过正则匹配实现批量分组转换，不需要依赖额外的单元标识列。以下是具体实现步骤：

1. 加载依赖包 #

先导入处理数据需要的工具包：

library(tidyverse)
library(readxl)

2. 导入Excel数据 #

直接读取文件，Excel的重复列名会自动被添加数字后缀（如Unit...2、Day...3），这正是我们可以利用的特征：

df <- read_excel("你的文件路径.xlsx")

3. 用pivot_longer实现转置 #

核心是通过正则表达式匹配列名结构，自动将每4列一组的字段映射为长格式的列：

df_long <- df %>%
  pivot_longer(
    cols = -Date,  # 固定Date列，转换其余所有列
    names_pattern = "(.*)\\.\\.(\\d+)",  # 匹配"原始字段名...数字"的列名格式
    names_to = c(".value", "group"),  # .value保留原始字段作为列，group记录分组编号
    values_drop_na = TRUE  # 可选：过滤无数据的空行
  )

参数说明： #

• names_pattern：正则表达式捕获两部分内容，第一部分是原始字段名（Unit/Day/Treatment/Value），第二部分是Excel自动添加的数字后缀；
• names_to = c(".value", "group")：.value是pivot_longer的特殊参数，会将捕获的第一部分作为新的列名，自动把同字段的数值归到对应列下；group用来区分不同的单元组（如果不需要可以后续删除）；
• values_drop_na：如果部分单元存在空值，可开启该参数过滤无效行。

4. （可选）清理分组标识列 #

如果不需要group列，直接删除即可：

df_long <- df_long %>% select(-group)

效果示例 #

假设导入后的数据集是这样的：

df <- tibble(
  Date = as.Date(c("2024-01-01", "2024-01-02")),
  Unit...2 = c("A", "A"),
  Day...3 = c(1, 2),
  Treatment...4 = c("T1", "T1"),
  Value...5 = c(10, 12),
  Unit...6 = c("B", "B"),
  Day...7 = c(1, 2),
  Treatment...8 = c("T2", "T2"),
  Value...9 = c(15, 18)
)

转换后会得到标准长格式：

这个方案完全适配你200+组单元的场景，不需要修改原始Excel文件，仅通过列名特征自动完成分组转换。

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