如何用data.table的fread导入含过多分隔符的错误数据并修正？

问题场景 #

需要导入一组大型数据，但原始文件存在数据填充错误：部分行的字段被错误拆分，导致分隔符数量过多，手动修改工作量极大。希望通过data.table的fread工具或其他方法在导入时自动修复问题。

错误数据示例 #

"Test File1"|"1"|"2"|"3"|"4"|"5"|"6"|"2"|"File1"|2|3|8|"Baseline1"
"File test2"|"1"|"2"|"3"|"4"|""|"5"|"6"|"2"|"File"|"o2"|2|3|8 |"Baseline2"
"Test File3"|"1"|"5"|"3"|"4"|"5"|"6"|"2"|"File3"|2|3|8|"Baseline3"
"Test File4"|"1"|"5"|"3"|"4"|"5"|"6"|"2"|"File4"|2|3|8|"Baseline"|"4 "
"Test File5"|"1"|"2"|"3"|"4"|"5"|"6"|"2"|"File5"|2|3|8|"Baseline5"
"Test File5"|"1"|"2"|"3"|"4"|"5"|"6"|"2"|"File5"|2|3|8|"Baseline5"
"File test6"|10|8|3|10|1|"6"|13|"File6"|2|10|13|"Baseline6"
"Test File7"|14|12|3|12|1|"6"|8|"File7"|2|7|6|"Baseline7"
"Test File8"|7|7|3|10|1|"6"|9|"File8"|2|11|14|"Baseline8"
"Test File9"|7|10|3|9|1|"6"|14|"File9"|2|11|14|"Baseline9"
"Test File10"|11|14|3|7|1|"6"|"11"|"File10"|2|6|11|"Baseline10"
"Test File11"|13|9|3|10|1|"6"|"10"|"File11"|2|10|13|"Baseline11"
"Test File12"|10|9|3|11|1|"6"|"11"|"File12"|2|6|8|"Baseline12"
"Test File13"|9|12|3|11|1|"6"|"11"|"File13"|2|11|6|"Baseline13"
"Test file14"|6|14|3|13|1|"6"|"8"|"File14"|2|11|6|"Baseline14"
"Test File15"|12|7|3|14|1|"6"|"6"|"File15"|2|6|12|"Baseline15"
"Trent" | "file16" | "1" |"5"| "3" |"4"| "5"| "6" | "2" | "Arch" | "" | "ivo16" |2|3|8| "Baseline" | "16"
"File test17"|"1"|"2"|"3"|"4"|"5"|"6"|"2"|"File17"|2|3|8|"Ba"|"seline17 "
"Test File18"|"1"|"2"|"3"|"4"|""|"5"|"6"|"2"|"File18"|2|3|8|"Baseline18"

期望正确结构 #

"Test File1"|"1"|"2"|"3"|"4"|"5"|"6"|"2"|"File1"|2|3|8|"Baseline1"
"File test2"|"1"|"2"|"3"|"4"|"5"|"6"|"2"|"File2"|2|3|8|"Baseline2"
"Test File3"|"1"|"5"|"3"|"4"|"5"|"6"|"2"|"File3"|2|3|8|"Baseline3"
"Test File4"|"1"|"5"|"3"|"4"|"5"|"6"|"2"|"File4"|2|3|8|"Baseline4"
"Test File5"|"1"|"2"|"3"|"4"|"5"|"6"|"2"|"File5"|2|3|8|"Baseline5"
"Test File5"|"1"|"2"|"3"|"4"|"5"|"6"|"2"|"File5"|2|3|8|"Baseline5"
"File test6"|"10"|"8"|"3"|"10"|"1"|"6"|"13"|"File6"|2|10|13|"Baseline6"
"Test File7"|"14"|"12"|"3"|"12"|"1"|"6"|"8"|"File7"|2|7|6|"Baseline7"
"Test File8"|"7"|"7"|"3"|"10"|"1"|"6"|"9"|"File8"|2|11|14|"Baseline8"
"Test File9"|"7"|"10"|"3"|"9"|"1"|"6"|"14"|"File9"|2|11|14|"Baseline9"
"Test File10"|"11"|"14"|"3"|"7"|"1"|"6"|"11"|"File10"|2|6|11|"Baseline10"
"Test File11"|"13"|"9"|"3"|"10"|"1"|"6"|"10"|"File11"|2|10|13|"Baseline11"
"Test File12"|"10"|"9"|"3"|"11"|"1"|"6"|"11"|"File12"|2|6|8|"Baseline12"
"Test file13"|"9"|"12"|"3"|"11"|"1"|"6"|"11"|"File13"|2|11|6|"Baseline13"
"Test File14"|"6"|"14"|"3"|"13"|"1"|"6"|"8"|"File14"|2|11|6|"Baseline14"
"Test File15"|"12"|"7"|"3"|"14"|"1"|"6"|"6"|"File15"|2|6|12|"Baseline15"
"Trent file16"|"1"|"5"|"3"|"4"|"5"|"6"|"2"|"File16"|2|3|8|"Baseline16"
"File test17"|"1"|"2"|"3"|"4"|"5"|"6"|"2"|"File17"|2|3|8|"Baseline17"
"Test file18"|"1"|"2"|"3"|"4"|"5"|"6"|"2"|"File18"|2|3|8|"Baseline18"

解决方案 #

方案1：导入后修复（适合中等规模文件） #

利用fread的fill=TRUE参数先导入所有行，再针对错误行合并拆分的字段：

library(data.table)

# 导入文件，允许填充缺失列，保留原始格式
dt <- fread("your_file.txt", sep="|", quote="\"", fill=TRUE, strip.white=FALSE)

# 获取正确列数（以第一行的列数为基准）
correct_ncol <- ncol(dt[1, ])
# 定位列数超标的错误行
error_rows <- which(dt[, .(n = length(unlist(.SD)))][, n] > correct_ncol)

# 批量修复错误行
for (i in error_rows) {
  row_data <- dt[i, ]
  
  # 修复第一列被拆分的情况（如Trent行）
  if (grepl("Trent", row_data[[1]]) && grepl("file16", row_data[[2]])) {
    row_data[[1]] <- paste0(trimws(row_data[[1]]), " ", trimws(row_data[[2]]))
    dt[i, ] <- row_data[, -2, with=FALSE]
  }
  # 修复中间字段拆分（如File|o2）
  else if (grepl("File", row_data[[9]]) && grepl("o2", row_data[[10]])) {
    row_data[[9]] <- paste0(row_data[[9]], row_data[[10]])
    dt[i, ] <- row_data[, -c(6, 10), with=FALSE]
  }
  # 修复末尾字段拆分（如Baseline|4、Ba|seline17）
  else if (grepl("Baseline", row_data[[13]]) && grepl("[0-9]", row_data[[14]])) {
    row_data[[13]] <- paste0(row_data[[13]], trimws(row_data[[14]]))
    dt[i, ] <- row_data[, -14, with=FALSE]
  } else if (grepl("Ba", row_data[[13]]) && grepl("seline", row_data[[14]])) {
    row_data[[13]] <- paste0(row_data[[13]], trimws(row_data[[14]]))
    dt[i, ] <- row_data[, -14, with=FALSE]
  }
}

# 统一列名（可选）
setnames(dt, paste0("V", 1:correct_ncol))

方案2：预处理文件后导入（适合超大型文件） #

先通过正则表达式修复文件内容，再用fread导入，避免内存过载：

# 读取所有行
lines <- readLines("your_file.txt")

# 批量修复错误模式
fixed_lines <- lapply(lines, function(line) {
  line <- gsub('"Trent" \\| "file16"', '"Trent file16"', line)
  line <- gsub('""\\|"5"', '"5"', line)
  line <- gsub('"File"\\|"o2"', '"File2"', line)
  line <- gsub('"Baseline"\\|"4 "', '"Baseline4"', line)
  line <- gsub('"Ba"\\|"seline17 "', '"Baseline17"', line)
  return(line)
})

# 写入修复后的临时文件
writeLines(fixed_lines, "fixed_file.txt")

# 导入修复后的文件
dt <- fread("fixed_file.txt", sep="|", quote="\"")

方案3：系统命令预处理（最高效） #

对于TB级超大型文件，直接用fread的cmd参数调用系统sed命令预处理，无需加载整个文件到内存：

dt <- fread(
  cmd = 'sed -e \'s/"Trent" | "file16"/"Trent file16"/g\' -e \'s/""/"5"/g\' -e \'s/"File"|"o2"/"File2"/g\' -e \'s/"Baseline"|"4 "/\"Baseline4"/g\' -e \'s/"Ba"|"seline17 "/\"Baseline17"/g\' your_file.txt',
  sep="|", quote="\""
)

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