R语言：特殊格式CSV转标准数据框及代码修复

问题背景 #

正在编写R脚本，需用到treatment、subject、body mass、date变量，目标是将数据转换为每行对应subject与date的唯一测量值（核心建模变量为body_mass_g）。但待转换的CSV为嵌套列格式：日期作为大列分组，下方包含body_mass_g等子列，用read.csv导入后格式异常。此前用pivot_wider处理后，输出数据框仅返回值类型而非实际数值，需修复该问题。

目标数据格式 #

exp cohort    sbj treatment     date time body_mass_g food_weight_g blood_glucose_mgdl
1  dtx   tx03 dtx001      A    1/7/2020   NA       30.70            NA                 NA
...（省略后续行）

待转换数据格式 #

|   1/1/2023, Monday Day 0  |   1/2/2023, Tuesday Day 1  
1 Tag  sbj.  Treatment |  body_mass_g   BG  Hopper |  body_mass_g   BG  Hopper
2 10   1     A         |  22            1   6      |  22            1   6
...（省略后续行）

原异常代码 #

df2 <- read_excel(file_name, sheet = 1, skip = 1)
# Extract the first row and manipulate it to combine it with the
# column names.
col_name_groups <- 
  read_excel(file_name, sheet = 1, col_names = FALSE) |>
  slice(1) |>
  pivot_longer(everything()) |>
  fill(value) |>
  replace_na(list(value = "")) |>
  pull(value)

# Clean up column names
col_names <- 
  names(df2) |>
  str_remove("\.\.\.[0-9]")

# Combine first and second rows of column names
col_names <- 
  paste(col_name_groups, col_names, sep = "#") |>
  str_remove("^#")

# Apply combined column names, pivot data and extract the date column
output <- capture.output ({
df2 |>
  set_names(col_names) |>
  rename_with(~str_remove(., "[0-9]+$")) |>
  pivot_longer(-c(Eartag, Mouse, Inj.),
               names_to = c("group", "var"),
               names_sep = "#") |>
  pivot_wider(names_from = var,
              values_from = value,
              values_fn = unlist) |>
  mutate(date = str_extract(group, "(.*)(,)", group = 1)) |> 
  select(Mouse, date, BW, BG, Hopper)
})

修复方案及代码 #

原代码核心问题：

1. 用capture.output()包裹处理流程，导致输出被捕获为文本而非数据框
2. pivot_wider的values_fn = unlist引发类型转换异常，且列名映射不一致

修复后的代码：

library(tidyverse)
library(readxl)

# 读取完整数据（包含表头行）
full_data <- read_excel(file_name, sheet = 1, col_names = FALSE)

# 提取第一行作为日期分组名，第二行作为子列名
date_groups <- full_data |> slice(1) |> pivot_longer(everything()) |> fill(value) |> pull(value)
sub_cols <- full_data |> slice(2) |> pivot_longer(everything()) |> pull(value)

# 合并分组名和子列名，生成完整列名
full_colnames <- ifelse(date_groups == "", sub_cols, paste(date_groups, sub_cols, sep = "#"))

# 读取实际数据行（从第3行开始）
df <- full_data |> slice(-c(1,2)) |> set_names(full_colnames)

# 转换为长格式并整理
output <- df |>
  # 保留标识列，将其他列转为长格式
  pivot_longer(-c(Tag, sbj., Treatment),
               names_to = c("date_group", "var"),
               names_sep = "#") |>
  # 转回宽格式，强制转换为数值类型
  pivot_wider(names_from = var, values_from = value, values_fn = as.numeric) |>
  # 提取纯日期部分
  mutate(date = str_extract(date_group, "^[^,]+")) |>
  # 重命名列匹配目标格式
  rename(subject = sbj., treatment = Treatment) |>
  # 选择目标列
  select(subject, treatment, date, body_mass_g, BG, Hopper)

关键修复点 #

• 移除capture.output()，直接赋值处理结果，确保得到数据框
• 统一列名映射，匹配目标格式中的变量命名
• 在pivot_wider中用values_fn = as.numeric强制将测量值转为数值类型，避免返回类型标识
• 调整数据读取逻辑，从完整数据中拆分表头和实际数据，避免两次读取的不一致

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