含引号与反斜杠的CSV文件无法被Pandas正确读取的解决方法

我尝试将以下CSV内容读取到Pandas DataFrame中：

103028,"Kinokompaniya \"Ego Production\"",[ru],,K5251,K5251,ef6ba1a20ed58265766c35d9e2823f17
60985,"Studio \"Orlenok\", Central Television USSR",[ru],,S3645,S3645,909356683cb8bb5f9872a7f34242b81f
159429,TBWA\CHIAT\DAY,[us],,T123,T1232,e59c9a8f96296cf1418fd92777a5f543
82924,"\"I, of the Craft...\"",[us],,I1326,I1326,3e798b706075164fb6b21cbf20e472d2
130274,"Producentska grupa \"Most\", Zagreb",,,,P6325,,1e9cf3da625add311321a8cab69458df

遇到了字符串引号和反斜杠的处理问题：

• 添加quotechar='"'和escapechar='\\'参数后，TBWA\CHIAT\DAY中的反斜杠被意外移除，其余行读取正常：

id                                       name country_code  imdb_id name_pcode_nf name_pcode_sf                            md5sum
0  103028             Kinokompaniya "Ego Production"         [ru]      NaN         K5251         K5251  ef6ba1a20ed58265766c35d9e2823f17
1   60985  Studio "Orlenok", Central Television USSR         [ru]      NaN         S3645         S3645  909356683cb8bb5f9872a7f34242b81f
2  159429                               TBWACHIATDAY         [us]      NaN          T123         T1232  e59c9a8f96296cf1418fd92777a5f543
3   82924                       "I, of the Craft..."         [us]      NaN         I1326         I1326  3e798b706075164fb6b21cbf20e472d2
4  130274          Producentska grupa "Most", Zagreb          NaN      NaN         P6325           NaN  1e9cf3da625add311321a8cab69458df

• 不添加这两个参数时，触发错误：

pandas.errors.ParserError: Error tokenizing data. C error: Expected 7 fields in line 2, saw 8

当前测试代码：

import pandas as pd
from io import StringIO

data = '''103028,"Kinokompaniya \\"Ego Production\\"",[ru],,K5251,K5251,ef6ba1a20ed58265766c35d9e2823f17
60985,"Studio \\"Orlenok\\", Central Television USSR",[ru],,S3645,S3645,909356683cb8bb5f9872a7f34242b81f
159429,TBWA\\CHIAT\\DAY,[us],,T123,T1232,e59c9a8f96296cf1418fd92777a5f543
82924,"\\"I, of the Craft...\\"",[us],,I1326,I1326,3e798b706075164fb6b21cbf20e472d2
130274,"Producentska grupa \\"Most\\", Zagreb",,,,P6325,,1e9cf3da625add311321a8cab69458df
'''

print(data)

问题根源是CSV混合了两种转义逻辑：带引号字段用反斜杠转义内部引号，不带引号字段里的反斜杠是普通字符，但Pandas的escapechar会全局处理反斜杠，导致不带引号字段的反斜杠被误转义。以下是两种可行的解决方法：

方法1：用Python标准库csv.reader解析 #

利用csv.reader的解析逻辑，精准处理带引号字段的转义，同时保留不带引号字段的原始字符：

import pandas as pd
import csv
from io import StringIO

data = '''103028,"Kinokompaniya \\"Ego Production\\"",[ru],,K5251,K5251,ef6ba1a20ed58265766c35d9e2823f17
60985,"Studio \\"Orlenok\\", Central Television USSR",[ru],,S3645,S3645,909356683cb8bb5f9872a7f34242b81f
159429,TBWA\\CHIAT\\DAY,[us],,T123,T1232,e59c9a8f96296cf1418fd92777a5f543
82924,"\\"I, of the Craft...\\"",[us],,I1326,I1326,3e798b706075164fb6b21cbf20e472d2
130274,"Producentska grupa \\"Most\\", Zagreb",,,,P6325,,1e9cf3da625add311321a8cab69458df
'''

# 用csv.reader处理，指定引号和转义符
reader = csv.reader(StringIO(data), quotechar='"', escapechar='\\')
rows = list(reader)

# 转换为DataFrame并指定列名
df = pd.DataFrame(rows, columns=['id', 'name', 'country_code', 'imdb_id', 'name_pcode_nf', 'name_pcode_sf', 'md5sum'])
# 可选：将id列转为数值类型
df['id'] = df['id'].astype(int)

print(df)

运行后，TBWA\CHIAT\DAY的反斜杠会被正确保留，所有字段解析正常。

方法2：预处理CSV内容，区分引号内外的反斜杠 #

通过简单的状态机判断字符是否在引号内，将不带引号的单个反斜杠替换成双反斜杠，再用Pandas读取：

import pandas as pd
from io import StringIO

data = '''103028,"Kinokompaniya \\"Ego Production\\"",[ru],,K5251,K5251,ef6ba1a20ed58265766c35d9e2823f17
60985,"Studio \\"Orlenok\\", Central Television USSR",[ru],,S3645,S3645,909356683cb8bb5f9872a7f34242b81f
159429,TBWA\\CHIAT\\DAY,[us],,T123,T1232,e59c9a8f96296cf1418fd92777a5f543
82924,"\\"I, of the Craft...\\"",[us],,I1326,I1326,3e798b706075164fb6b21cbf20e472d2
130274,"Producentska grupa \\"Most\\", Zagreb",,,,P6325,,1e9cf3da625add311321a8cab69458df
'''

# 预处理：替换引号外的单个反斜杠为双反斜杠
processed_lines = []
in_quote = False
for line in data.split('\n'):
    new_line = []
    for char in line:
        if char == '"':
            in_quote = not in_quote
            new_line.append(char)
        elif char == '\\' and not in_quote:
            new_line.append('\\\\')
        else:
            new_line.append(char)
    processed_lines.append(''.join(new_line))

processed_data = '\n'.join(processed_lines)

# 用Pandas读取，指定引号和转义符
df = pd.read_csv(StringIO(processed_data), quotechar='"', escapechar='\\', dtype={'id': int})
print(df)

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