如何在R中修复CSV字符串内逗号被误判为分隔符的问题

问题背景 #

从老旧数据库导出的CSV文件存在格式问题：Customer和Location列的字符串中包含逗号，被错误识别为列分隔符，导致部分行的列偏移至12、13甚至14列。文件共26009行、11列，仅能使用当前导出文件，且已有一份城市唯一列表可作为基准。

解决方案思路 #

利用已知的城市列表，从每行末尾倒推定位City列的正确位置，将偏移的字段合并回对应的Location（或Customer）列，最终重构为符合规范的11列数据。

实现代码 #

# 1. 读取原始CSV为文本行（避免直接read.csv导致列错位）
raw_lines <- readLines("your_file.csv")

# 2. 定义已知的城市唯一列表（替换为你实际的城市数据）
city_list <- c("Yorktown", "Sechelt", "Taliha", "Winnipeg", "Sandy", "Edmonton", 
               "Ray City", "Port Francis", "Halifax", "Kelvin")

# 3. 处理标题行
header <- strsplit(raw_lines[1], ", ")[[1]]
expected_cols <- length(header) # 固定为11列

# 4. 批量处理数据行
fixed_data <- lapply(raw_lines[-1], function(line) {
  fields <- strsplit(line, ", ")[[1]]
  num_fields <- length(fields)
  
  if (num_fields == expected_cols) {
    # 列数正常，直接返回
    return(fields)
  } else {
    # 从后往前找到第一个匹配城市的位置（即City列的正确位置）
    city_pos <- which(fields %in% city_list)[1]
    
    # 合并City列前的偏移字段到Location列
    merged_location <- paste(fields[4:(city_pos-1)], collapse = ", ")
    
    # 重构正确的字段序列
    fixed_fields <- c(
      fields[1:3],          # Number, Status, Customer
      merged_location,      # 修复后的Location
      fields[city_pos],     # City
      fields[(city_pos+1):(num_fields - (expected_cols - 6))], # 后续正常字段
      rep("", expected_cols - length(c(fields[1:3], merged_location, fields[city_pos:(num_fields - (expected_cols - 6))]))) # 补全空值
    )
    
    # 强制截断/补全为11列
    return(fixed_fields[1:expected_cols])
  }
})

# 5. 转换为数据框并设置正确列类型
fixed_df <- as.data.frame(do.call(rbind, fixed_data), stringsAsFactors = FALSE)
colnames(fixed_df) <- header

# 修正列类型
fixed_df$Number <- as.double(fixed_df$Number)
fixed_df$Total <- as.double(fixed_df$Total)
fixed_df$Accepted <- as.logical(fixed_df$Accepted)
fixed_df$Rejected <- as.logical(fixed_df$Rejected)

# 查看修复结果
head(fixed_df)

注意事项 #

• 若Customer列也存在含逗号的情况，可调整合并逻辑：先定位Customer的合理取值范围，再处理Location的字段合并。
• 若部分行的城市未出现在列表中，需手动补充到city_list，或添加兜底逻辑（例如按列数差异直接合并前面的偏移字段）。
• 处理前建议先抽取部分异常行测试代码，确保合并逻辑匹配实际数据规律。

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