如何从含重复列名的CSV提取数据并批量合并添加soil_ref列

我有77个格式一致的CSV文件（命名为1_modrapport.csv至77_modrapport.csv），每个文件的原始数据存在重复列名（行交替为数据行和列名行），需要完成以下处理：

• 提取ansemis、mafruit、masec(n)等关键信息，保留有效数据行，剔除重复的列名行
• 处理masec(n)列的缺失情况
• 汇总所有文件的提取结果，新增soil_ref列，取值为文件名前缀的1-77数字

现有R代码无法实现上述需求，求调整方案。

原始数据结构 #

raw_data<-structure(list(P_usm = c("001_Pal_IRR1_N0", "P_usm", "001_Pal_IRR1_N0", 
                             "P_usm", "001_Pal_IRR1_N0", "P_usm", "001_Pal_IRR1_N0", "P_usm", 
                             "001_Pal_IRR1_N0"), wlieu = c("87_073_v3test", "wlieu", "87_073_v3test", 
                                                           "wlieu", "87_073_v3test", "wlieu", "87_073_v3test", "wlieu", 
                                                           "87_073_v3test"), ansemis = c("1980", "ansemis", "1981", "ansemis", 
                                                                                         "1982", "ansemis", "1983", "ansemis", "1984"), CNgrain = c("7.7690000000000001", 
                                                                                                                                                    "CNgrain", "6.4790000000000001", "CNgrain", "7.3739999999999997", 
                                                                                                                                                    "CNgrain", "6.5549999999999997", "CNgrain", "6.5449999999999999"
                                                                                         ), `masec(n)` = c("6.9470000000000001", "masec(n)", "7.7850000000000001", 
                                                                                                           "mafruit", "2.8279999999999998", "mafruit", "3.355", "mafruit", 
                                                                                                           "3.3410000000000002"), mafruit = c("2.3639999999999999", "mafruit", 
                                                                                                                                              "3.1230000000000002", NA, NA, NA, NA, NA, NA)), class = c("tbl_df", 
                                                                                                                                                                                                        "tbl", "data.frame"), row.names = c(NA, -9L))

现有代码 #

library(dplyr)
library(readr)

process_csv_file <- function(file_path) {
  raw_data <- read_csv(file_path, col_types = cols(.default = "c"))
  column_names <- names(raw_data)
  num_rows <- nrow(raw_data)
  data_list <- lapply(seq(1, num_rows, by = 2), function(i) {
    if (i + 1 <= num_rows) {

      data_row <- raw_data[i + 1, ]

      tibble(
        P_usm = data_row$P_usm,
        ansemis = data_row$ansemis,
        mafruit = data_row$mafruit
      )
    }
  })
  
  data_combined <- bind_rows(data_list)
  
  return(data_combined)
}

path <- "C:/MyJavaSTICS/01_grid/Output_Results/MGIPallador_Results"

csv_files <- list.files(path, pattern = "*.csv", full.names = TRUE)

all_data <- lapply(csv_files, process_csv_file)
final_data <- bind_rows(all_data)
final_data_cleaned <- final_data %>%
  mutate(across(everything(), as.character))

print(head(final_data_cleaned))

write_csv(final_data_cleaned, "C:/MyJavaSTICS/01_grid/Output_Results/MGIPallador_Results/combined_data.csv")

关键改动点 #

1. 修正数据行识别逻辑：原始数据中奇数行是有效数据行，偶数行是重复列名行，之前的代码搞反了提取对象
2. 新增soil_ref列：从文件名前缀提取数字，匹配1-77的编号
3. 保留masec(n)列并自动处理缺失值
4. 优化文件排序逻辑，避免文件名按字符串排序导致的顺序混乱（比如"10_"排在"2_"前）

完整调整代码 #

library(dplyr)
library(readr)
library(stringr)

process_csv_file <- function(file_path) {
  # 读取文件，所有列按字符类型加载
  raw_data <- read_csv(file_path, col_types = cols(.default = "c"))
  
  # 从文件名提取前缀数字作为soil_ref
  soil_ref <- str_extract(basename(file_path), "^\\d+") %>% as.integer()
  
  # 筛选奇数行作为有效数据行（原始数据中奇数行是数据，偶数行是列名重复行）
  data_rows <- raw_data[seq(1, nrow(raw_data), by = 2), ]
  
  # 提取目标列，添加soil_ref，可选转换数值类型
  cleaned_data <- data_rows %>%
    select(P_usm, ansemis, `masec(n)`, mafruit) %>%
    mutate(soil_ref = soil_ref) %>%
    # 按需转换数值列类型，不需要可删除此行
    mutate(across(c(ansemis, `masec(n)`, mafruit), as.numeric))
  
  return(cleaned_data)
}

# 文件路径
path <- "C:/MyJavaSTICS/01_grid/Output_Results/MGIPallador_Results"

# 精准匹配目标CSV文件，避免混入其他无关文件
csv_files <- list.files(path, pattern = "^\\d+_modrapport\\.csv$", full.names = TRUE)

# 按文件名前缀数字排序，确保处理顺序为1-77
csv_files <- csv_files[order(as.integer(str_extract(basename(csv_files), "^\\d+")))]

# 批量处理所有文件并合并
all_data <- lapply(csv_files, process_csv_file)
final_data <- bind_rows(all_data)

# 查看结果头部
print(head(final_data))

# 保存合并后的数据
write_csv(final_data, file.path(path, "combined_data.csv"))

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