如何在R中转换含多行表头的Excel实验室数据为长格式数据框？

我有多个Excel工作表，每个表包含约120项实验室结果和150位患者的矩阵数据。实验室结果从第4行开始存储，患者列则在第1至3行有额外表头数据，希望将其读取并转换为长格式/规范化的数据框。

我尝试使用pivot_longer或melt处理，但左上角的空单元格带来问题：实验室结果的列表头从第4行开始，患者列从C列开始。患者列名固定从D列开始，但具体患者ID（如EXA_2665、EXA_2707等）无法预先知晓。

目前尝试的代码 #

library(tidyverse)
library(readxl)
library(dplyr)
library(tidyr)

##xls_data <- read_excel(path = "lab2_exafinal.xlsx", sheet = "Labata")
## TEST hardcoded data for testing purposes
xls_data2 <- read.table(text = "
...1,...2,Patient-Id,EXA_2665,EXA_2707,EXA_2971
,,Tube,87019054,87065976,87093024
,,Tube-Pos,C12,D01,D02
LabId,LabDescr,WeightFact,,,
9,HbA1c,1.00,1.784,0.419,1.045
37,HDL-Chol,1.00,0.684,2.172,1.954
38,LDL-Chol,1.00,2.438,1.995,0.806
41,Glucose,1.00,1.366,1.47,0.978
", header = TRUE, sep = ",", na.strings = "")

library(reshape)
df_melt <- melt(xls_data, id.vars=c("...1", "...2"))
# error: names do not match previous names

pivoted_data <- pivot_longer(
  xls_data,
  cols = names(xls_data[3,]),
  names_to = "patient",
  values_to = "value" 
  )
#no error but not quite the desired result

补充数据结构（dput输出） #

编辑补充：经建议使用dput分享read_excel读取后的准确数据结构，同时注意患者标签名可能包含-字符。

#xls_data <- read_excel(path = "lab2_exafinal.xlsx", sheet = "Labata")
#dput(xls_data)
xls_data <- structure(
  list(
    ...1 = c(NA, NA, "LabId", "9", "37", "38", "41"),
    ...2 = c(NA, NA, "LabDescr", "HbA1c", "HDL-Chol", "LDL-Chol", "Glucose"),
    ...3 = c("Tube", "Tube-Pos", "WeightFact", "1,00", "1,00", "1,00", "1,00"),
    EXA_2665 = c("87019054", "C12", NA, "1.784", "0.68400000000000005", "2.4380000000000002", "1.3660000000000001"),
    EXA_2707 = c("87065976", "D01", NA, "0.41899999999999998", "2.1720000000000002", "1.9950000000000001", "1.47"),
    EXA_2971 = c("87093024", "D02", NA, "1.0449999999999999", "1.954", "0.80600000000000005", "0.97799999999999998")
    ),
    class = c("tbl_df", "tbl", "data.frame"),
    row.names = c(NA, -7L)
  )

遇到的问题 #

使用readxl::read_excel读取Excel表时，前两列为...1、...2。尝试用melt时将它们设为id.vars出现错误；pivot_longer运行无错但结果不符合预期，问题在于存在额外表头行，不知如何处理或移除。

我想到两种思路但不知如何在RStudio中实现：

• 将患者数据（C1..F3）和实验室数值（A4..F8）分别读取为两个数据框，再合并。
• 先将患者表头名（如EXA_2665）复制到空的第4行，再重新读取A4..F8区域。

请问还有哪些可行的解决方法？

解决方案 #

方法一：拆分表头与数据，分别处理后合并 #

直接拆分患者元数据和实验室结果，整理后关联：

library(tidyverse)

# 1. 提取并整理患者元数据（Tube、Tube-Pos信息）
patient_meta <- xls_data %>%
  slice(1:2) %>%
  pivot_longer(cols = starts_with("EXA"), names_to = "patient_id", values_to = "value") %>%
  pivot_wider(names_from = ...3, values_from = value) %>%
  select(patient_id, Tube, `Tube-Pos`)

# 2. 提取并整理实验室结果数据
lab_data <- xls_data %>%
  slice(4:n()) %>%
  rename(
    LabId = ...1,
    LabDescr = ...2,
    WeightFact = ...3
  ) %>%
  mutate(WeightFact = as.numeric(str_replace(WeightFact, ",", ".")))

# 3. 转为长格式并关联患者元数据
final_long_data <- lab_data %>%
  pivot_longer(cols = starts_with("EXA"), names_to = "patient_id", values_to = "lab_result") %>%
  mutate(lab_result = as.numeric(lab_result)) %>%
  left_join(patient_meta, by = "patient_id")

# 查看最终结果
print(final_long_data)

方法二：重构表头后读取数据 #

先合并表头信息，再提取数据转为长格式：

# 重构患者列的表头（合并ID、Tube、Tube-Pos）
new_colnames <- c("LabId", "LabDescr", "WeightFact",
                  paste(names(xls_data)[4:ncol(xls_data)],
                        xls_data[1,4:ncol(xls_data)],
                        xls_data[2,4:ncol(xls_data)],
                        sep = "_"))

# 提取数据行并设置新表头
clean_lab_data <- xls_data %>%
  slice(4:n()) %>%
  set_names(new_colnames) %>%
  mutate(across(c(WeightFact, starts_with("EXA")), ~as.numeric(str_replace(., ",", "."))))

# 拆分表头转为长格式
final_long_data2 <- clean_lab_data %>%
  pivot_longer(cols = starts_with("EXA"), 
               names_to = c("patient_id", "Tube", "Tube-Pos"),
               names_sep = "_",
               values_to = "lab_result")

方法三：分区域读取Excel文件 #

直接指定Excel区域分别读取元数据和结果，避免混合表头问题：

# 读取患者元数据（C1到F3区域）
patient_meta_range <- read_excel("lab2_exafinal.xlsx", sheet = "Labata", range = "C1:F3", col_names = FALSE) %>%
  pivot_longer(cols = 2:ncol(.), names_to = "col_idx", values_to = "value") %>%
  pivot_wider(names_from = 1, values_from = value) %>%
  rename(patient_id = `...1`)

# 读取实验室数据（A4到F8区域）
lab_data_range <- read_excel("lab2_exafinal.xlsx", sheet = "Labata", range = "A4:F8")

# 转为长格式并合并
final_long_data3 <- lab_data_range %>%
  pivot_longer(cols = starts_with("EXA"), names_to = "patient_id", values_to = "lab_result") %>%
  left_join(patient_meta_range, by = "patient_id")

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