如何使用Pandas读取含特殊分隔符的CSV文件？

问题背景 #

待读取的CSV文件格式不符合标准规范，字段分隔符被额外双引号包裹，部分字段内部还包含逗号，示例内容如下：

"Sección;""Descripción Sección"";""Servicio"";""Descripción Servicio"";""Programa"";""Descripción Programa"";""Capítulo"";""Descripción Capítulo"";""Subconcepto"";""Descripción Subconcepto"";""Proyecto de Inversión"";""Nombre"";""CT"";""Isla"";""Importe""",,,,
"01;""Parlamento"";""0101"";""Servicios Generales"";""911A"";""Actuación Legislativa y de Control "";""1"";""GASTOS DE PERSONAL"";""10000"";""Retrib.básic. y otras ret. del Gob.y altos Cargos"";"""";"""";"""";"""";3.836.041",,,,
"01;""Parlamento"";""0101"";""Servicios Generales"";""911A"";""Actuación Legislativa y de Control "";""2"";""GASTOS CORRIENTES EN BIENES Y SERVICIOS"";""21900"";""Otro inmovilizado material"";"""";"""";"""";"""";1.500",,,,
"01;""Parlamento"";""0101"";""Servicios Generales"";""911A"";""Actuación Legislativa y de Control "";""2"";""GASTOS CORRIENTES EN BIENES Y SERVICIOS"";""22001"";""Prensa", revistas," libros y otras publicaciones"";"""";"""";"""";"""";111.000",,

已定义表头：

header = ["Sección", "Descripción Sección", "Servicio", "Descripción Servicio", "Programa", "Descripción Programa", "Capítulo", "Descripción Capítulo", "Subconcepto", "Descripción Subconcepto", "Proyecto de Inversión", "Nombre", "CT", "Isla", "Importe"] 

尝试正则分隔、固定宽度读取均未成功：

# 正则分隔失败
data = pd.read_csv("file.csv", engine='python', sep='("";"")|("";)|(;"")')

# 固定宽度读取失败
data = pd.read_fwf("file.csv")

期望输出格式：

Sección,Descripción Sección,Servicio,Descripción Servicio,Programa,Descripción Programa,Capítulo,Descripción Capítulo,Subconcepto,Descripción Subconcepto,Proyecto de Inversión,Nombre,CT,Isla,Importe
1,Parlamento,101,Servicios Generales,911A,Actuación Legislativa y de Control,1,GASTOS DE PERSONAL,10000,Retrib.básic. y otras ret. del Gob.y altos Cargos,,,,,"3,836,041"
1,Parlamento,101,Servicios Generales,911A,Actuación Legislativa y de Control,2,GASTOS CORRIENTES EN BIENES Y SERVICIOS,21900,Otro inmovilizado material,,,,,"1,500"
1,Parlamento,101,Servicios Generales,911A,Actuación Legislativa y de Control,2,GASTOS CORRIENTES EN BIENES Y SERVICIOS,22001,"Prensa, revistas, libros y otras publicaciones",,,,,"111,000"

可行解决方案 #

方法：预处理文件格式后再读取 #

原文件格式异常，先逐行清理格式，再用Pandas读取：

import pandas as pd
from io import StringIO

# 定义表头
header = ["Sección", "Descripción Sección", "Servicio", "Descripción Servicio", "Programa", "Descripción Programa", "Capítulo", "Descripción Capítulo", "Subconcepto", "Descripción Subconcepto", "Proyecto de Inversión", "Nombre", "CT", "Isla", "Importe"] 

processed_lines = []
with open("file.csv", "r", encoding="utf-8") as f:
    for line in f:
        # 去除首尾双引号
        cleaned_line = line.strip().strip('"')
        # 统一替换异常分隔符为临时分隔符|
        cleaned_line = cleaned_line.replace('"";""', '|').replace(';""', '|').replace('"";', '|')
        # 修复字段内部的异常引号
        cleaned_line = cleaned_line.replace('", ', ', ')
        # 去除行尾多余逗号
        cleaned_line = cleaned_line.rstrip(',')
        processed_lines.append(cleaned_line)

# 读取处理后的内容
df = pd.read_csv(StringIO('\n'.join(processed_lines)), sep='|', names=header, skiprows=1)

# 格式化Importe字段，匹配期望输出
df['Importe'] = df['Importe'].apply(lambda x: f'"{x.replace(".", ",")}"')

# 输出结果
print(df.to_csv(index=False, na_rep=''))

步骤说明 #

1. 格式清理：先去掉每行首尾的双引号，把各种异常的字段分隔符替换为临时分隔符|，避免和字段内的逗号冲突；同时修复数据行中字段内部的错误引号。
2. 读取数据：用StringIO把处理后的文本转为可读取对象，指定分隔符为|，使用自定义表头并跳过原文件的第一行。
3. 字段格式化：将Importe字段的点号替换为逗号，添加双引号，匹配期望的输出格式。

运行代码后即可得到符合要求的结果。

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