R语言加载含新增列的Excel合并数据时set_names报错求助

有多个需合并的Excel文件，每个文件包含A至AF列（共32列），列名如下：

• t-ph
• Load
• HR
• BF
• V'E
• V'O2
• V'CO2
• d O2/dW
• RER
• EqO2
• EqCO2
• PETCO2
• VES (ml)
• VESi (ml/m²)
• FC (bpm)
• QC (l/min)
• IC (l/min/m²)
• PAS (mmHg)
• PAD (mmHg)
• PAM (mmHg)
• ICT
• TEV (ms)
• RPD (%)
• WCI (kg.m/m²)
• RVSi (dyn.s/cm5.m²)
• RVS (dyn.s/cm5)
• VTD est (ml)
• FE est (%)
• O2Hb
• HHb
• tHb
• HbDiff

新增AC列（HHb）至AF列（HbDiff）后，原R代码无法加载数据，报错如下：

Error in `set_names()`:
! The size of `nm` (23) must be compatible with the size of `x` (20).
Run `rlang::last_error()` to see where the error occurred.

原代码如下：

pacman::p_load(tidyverse, readxl, ggpubr)
library(dplyr)
library(ggplot2)

library(afex) ##statistic package

# load data and format
load_files <- function(files){
  temp <- read_excel(files) %>% 
    select(-(c(8:11, 14:15, 18:23))) %>%
    mutate(id = pull(.[4,1])) %>% ##ID
    mutate(body_mass = pull(.[7,3])) %>% ##body mass
    mutate(training = pull(.[4,2])) %>% ##training group
    set_names(c("time", "power", "hr", "fr", "VE", "absVO2", "VCO2", "PETCO2", "VES", "QC", "IC", "WCI", "RVSi", "RVS", "VTD", "FE", "O2Hb", "HHb", "tHb", "HbDiff", "id", "body_mass", "training")) %>% 
    slice(86:which(grepl("ration", VE))-1) %>% ##until recovery period
    mutate_at(vars(1:16), as.numeric) %>% 
    mutate_at(vars(18), as.numeric) %>% 
    mutate(time = format(as.POSIXct(Sys.Date() + time), "%H:%M", tz="UTC"),
           absVO2 = absVO2/1000, 
           VCO2 = VCO2/1000)
}

# apply function to all files
df <- map_df(file_list, load_files)


# remove those with who have less than four similar power
df <- df %>% 
  mutate(len_seq = rep(rle(power)$lengths, rle(power)$lengths)) %>% 
  filter(len_seq == 4) %>% 
  mutate(seq_id = rep(1:(n()/4), each = 4)) %>% 
  group_by(id) %>% 
  select(-seq_id)%>% 
  select(-(20))


# group data
df_sum <- df %>%
  type.convert(as.is = TRUE) %>%
  group_by(id, power, training) %>% 
  summarise_if(is.numeric, mean) %>%
  group_by(id) %>%
  mutate(percent_absVO2 = absVO2/max(absVO2)*100,
         percent_power = power/max(power)*100,
         percent_QC = QC/max(QC)*100,
         percent_SV = VES/max(VES)*100,
         percent_VCO2 = VCO2/max(VCO2)*100,
         percent_VE = VE/max(VE)*100) %>%
  mutate(VE_VO2 = VE/absVO2,
         VE_VCO2 = VE/VCO2) %>%
  mutate(RER = VCO2/absVO2, VT = VE/fr) %>%
  mutate(relVO2 = absVO2/body_mass*1000,
         percent_relVO2 = relVO2/max(relVO2)*100) %>%
  mutate(BF = VE/VT) %>%
  mutate(mech_perf = (power/(((0.003*power+0.1208)*1000*body_mass)/60))*100) %>%
  mutate(group = ifelse(grepl(".*-PRD-C", id), "CAD", "Healthy")) %>%
  mutate(temps = ifelse(grepl(".*-PRD-C1", id), "1", ifelse(grepl(".*-PRD-S1", id), "1", "2")))

核心问题 #

报错根源是硬编码列索引筛选导致列数不匹配：新增AC-AF列后，原代码中select(-(c(8:11, 14:15, 18:23)))的索引范围失效，筛选后剩余列数与set_names指定的23个列名数量不匹配，触发错误。

修复步骤 #

1. 改用列名筛选，摆脱索引依赖：将按索引删除列的逻辑替换为按列名删除，无论列数如何变化，只要列名不变就不会出错。
2. 替换硬编码索引为列名：后续mutate_at中的列索引也改用列名，提升代码稳定性。

修改后的代码 #

pacman::p_load(tidyverse, readxl, ggpubr)
library(dplyr)
library(ggplot2)
library(afex)

# load data and format
load_files <- function(files){
  temp <- read_excel(files) %>% 
    # 按列名删除不需要的列，替代原索引筛选
    select(-c("d  O2/dW", "RER", "EqO2", "EqCO2", 
              "VESi (ml/m²)", "FC (bpm)", 
              "PAS (mmHg)", "PAD (mmHg)", "PAM (mmHg)", 
              "ICT", "TEV (ms)", "RPD (%)")) %>%
    mutate(id = pull(.[4,1])) %>% ##ID
    mutate(body_mass = pull(.[7,3])) %>% ##body mass
    mutate(training = pull(.[4,2])) %>% ##training group
    set_names(c("time", "power", "hr", "fr", "VE", "absVO2", "VCO2", "PETCO2", 
                "VES", "QC", "IC", "WCI", "RVSi", "RVS", "VTD", "FE", 
                "O2Hb", "HHb", "tHb", "HbDiff", 
                "id", "body_mass", "training")) %>% 
    slice(86:which(grepl("ration", VE))-1) %>% ##until recovery period
    # 改用列名指定需要转成numeric的列
    mutate_at(vars(time, power, hr, fr, VE, absVO2, VCO2, PETCO2, 
                   VES, QC, IC, WCI, RVSi, RVS, VTD, FE), as.numeric) %>% 
    mutate_at(vars(HHb), as.numeric) %>% 
    mutate(time = format(as.POSIXct(Sys.Date() + time), "%H:%M", tz="UTC"),
           absVO2 = absVO2/1000, 
           VCO2 = VCO2/1000)
}

# apply function to all files
df <- map_df(file_list, load_files)

# remove those with who have less than four similar power
df <- df %>% 
  mutate(len_seq = rep(rle(power)$lengths, rle(power)$lengths)) %>% 
  filter(len_seq == 4) %>% 
  mutate(seq_id = rep(1:(n()/4), each = 4)) %>% 
  group_by(id) %>% 
  select(-seq_id) %>% 
  # 替换硬编码索引为列名，避免列顺序变化导致误删
  select(-HbDiff)

# group data
df_sum <- df %>%
  type.convert(as.is = TRUE) %>%
  group_by(id, power, training) %>% 
  summarise_if(is.numeric, mean) %>%
  group_by(id) %>%
  mutate(percent_absVO2 = absVO2/max(absVO2)*100,
         percent_power = power/max(power)*100,
         percent_QC = QC/max(QC)*100,
         percent_SV = VES/max(VES)*100,
         percent_VCO2 = VCO2/max(VCO2)*100,
         percent_VE = VE/max(VE)*100) %>%
  mutate(VE_VO2 = VE/absVO2,
         VE_VCO2 = VE/VCO2) %>%
  mutate(RER = VCO2/absVO2, VT = VE/fr) %>%
  mutate(relVO2 = absVO2/body_mass*1000,
         percent_relVO2 = relVO2/max(relVO2)*100) %>%
  mutate(BF = VE/VT) %>%
  mutate(mech_perf = (power/(((0.003*power+0.1208)*1000*body_mass)/60))*100) %>%
  mutate(group = ifelse(grepl(".*-PRD-C", id), "CAD", "Healthy")) %>%
  mutate(temps = ifelse(grepl(".*-PRD-C1", id), "1", ifelse(grepl(".*-PRD-S1", id), "1", "2")))

额外说明 #

• 原代码中select(-(20))对应修改后的HbDiff列，改用select(-HbDiff)更直观，避免列顺序变化引发误删。
• 用列名进行筛选和操作是处理动态列数据的最优方案，能有效避免新增/删除列后代码失效的问题。

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