Pandas中处理内嵌双引号的制表符分隔文件：读写策略与最佳实践

"HEADER"    "ID"    "Part Reference"    "Value" "Part_Description"
"PARTOCC:17306" "17306" "M1"    "48SL-5S-50-C-LF"   "Series 48SL–5 WEDGE–LOK, 2-56UNC-2B, 5.00", chem film, lock washer and flat"
"PARTOCC:17310" "17310" "M2"    "48SL-5S-50-C-LF"   "Series 48SL–5 WEDGE–LOK, 2-56UNC-2B, 5.00", chem film, lock washer and flat"
"PARTOCC:65494" "65494" "J4E"   "311P822-MC-095-BS-D"   "GSFC CPCI J3 RA MALE 95 Position 0.123" tails"

import csv

def parse_tsv_with_manual_quoting(filepath: str) -> list[list[str]]:
    """
    解析一个制表符分隔文件，该文件的字段被双引号包裹，且内部可能含有双引号。
    通过忽略引用并手动切片来处理。
    """
    rows: list[list[str]] = []
    with open(filepath, newline="", encoding='utf-8') as f:
        # 使用 csv.QUOTE_NONE 忽略引用，并指定制表符为分隔符
        reader = csv.reader(f, delimiter="	", quoting=csv.QUOTE_NONE)

        # 处理标题行：去除外部双引号
        header = [x[1:-1] for x in next(reader)]
        rows.append(header) # 将处理后的标题也加入rows，或单独保存

        # 处理数据行：去除每个字段的外部双引号
        for row in reader:
            processed_row = [x[1:-1] for x in row]
            rows.append(processed_row)
    return rows[0], rows[1:] # 返回标题和数据

# 示例使用
exp_fn = "test.exp"
header, data_rows = parse_tsv_with_manual_quoting(exp_fn)
print("Header:", header)
print("Data Rows:")
for row in data_rows:
    print(row)

Header: ['HEADER', 'ID', 'Part Reference', 'Value', 'Part_Description']
Data Rows:
['PARTOCC:17306', '17306', 'M1', '48SL-5S-50-C-LF', 'Series 48SL–5 WEDGE–LOK, 2-56UNC-2B, 5.00", chem film, lock washer and flat']
['PARTOCC:17310', '17310', 'M2', '48SL-5S-50-C-LF', 'Series 48SL–5 WEDGE–LOK, 2-56UNC-2B, 5.00", chem film, lock washer and flat']
['PARTOCC:65494', '65494', 'J4E', '311P822-MC-095-BS-D', 'GSFC CPCI J3 RA MALE 95 Position 0.123" tails']

def write_tsv_with_manual_quoting(filepath: str, header: list[str], data_rows: list[list[str]]):
    """
    将解析后的数据写回制表符分隔文件，并保持原始的“坏格式”。
    """
    with open(filepath, "w", newline="", encoding='utf-8') as f:
        writer = csv.writer(f, delimiter="	", quoting=csv.QUOTE_NONE)
        # 手动为每个字段添加外部双引号
        writer.writerow([f'"{field}"' for field in header])
        for row in data_rows:
            writer.writerow([f'"{field}"' for field in row])

# 示例使用
output_fn = "check_manual.exp"
write_tsv_with_manual_quoting(output_fn, header, data_rows)
print(f"数据已写回至 {output_fn}，请检查其格式是否与原始文件一致。")

import sys

END = "
"
"""预期行结束符"""
DELIM = "	"
"""预期分隔符"""
QUOT = '"'
"""预期引用字符"""

Row = list[str]

def decode(line: str) -> Row:
    """
    解码一个“坏格式”的CSV/TSV行。
    确保行以END结尾，按DELIM分割行，并验证每个字段是否以QUOT开始和结束，然后去除这些外部QUOT。
    返回字段列表。
    """
    if not line.endswith(END):
        raise ValueError(f"行不以 {repr(END)} 结尾")

    line = line[:-len(END)] # 移除行结束符
    fields = line.split(DELIM)

    for i, field in enumerate(fields):
        if not (field.startswith(QUOT) and field.endswith(QUOT)):
            raise ValueError(f"字段 {i} 未被 {repr(QUOT)} 包裹: {repr(field)}")
        fields[i] = field[1:-1] # 移除外部双引号
    return fields

def encode(row: Row) -> str:
    """
    将行编码回其原始的“坏格式”。
    """
    # 为每个字段添加外部双引号，然后用制表符连接
    line = DELIM.join([f"{QUOT}{field}{QUOT}" for field in row])
    return line + END # 添加行结束符

def exit_err(msg: str):
    print(msg, file=sys.stderr)
    sys.exit(1)

# 读取并解码文件
input_filepath = "test.exp"
output_filepath = "check_custom.exp"

decoded_rows: list[Row] = []
with open(input_filepath, encoding='utf-8') as f:
    try:
        header = decode(next(f))
        decoded_rows.append(header)
    except ValueError as e:
        exit_err(f"无法解码标题行: {e}")

    for i, line in enumerate(f):
        try:
            decoded_rows.append(decode(line))
        except ValueError as e:
            exit_err(f"无法解码第 {i+2} 行: {e}") # i从0开始，所以是i+2行

# 编码并写回文件
with open(output_filepath, "w", encoding='utf-8') as f:
    for row in decoded_rows:
        f.write(encode(row))

print(f"数据已通过自定义编解码器处理并写回至 {output_filepath}。")

# 验证输入输出一致性
# 在命令行中运行：python your_script_name.py; diff test.exp check_custom.exp
# 如果没有任何输出，则表示文件内容完全一致。

import csv
import re
from io import StringIO
import pandas as pd

input_filepath = "test.exp"
output_filepath = "check_pandas_re.exp"

with open(input_filepath, encoding='utf-8') as f:
    text = f.read()

# 预处理：将形如 '数字.数字"' 的内部双引号转义为 '数字.数字"'
# 这样Pandas在读取时，可以通过 escapechar='\' 识别并正确处理
text = re.sub(r'(d+.d+)"', r'g<1>\"', text)

# 使用 StringIO 将预处理后的文本作为文件对象传递给 Pandas
df = pd.read_csv(StringIO(text), quotechar='"', sep="	", escapechar="\")

# 准备输出文件
out_f = StringIO()

# 写入CSV：
# sep="	"：制表符分隔
# quoting=csv.QUOTE_ALL：所有字段都用双引号包裹
# doublequote=False：不将内部双引号转义为双倍双引号
# escapechar="\"：使用反斜杠作为转义字符，对应我们之前的预处理
# index=False：不写入DataFrame的索引
df.to_csv(
    out_f,
    sep="	",
    quoting=csv.QUOTE_ALL,
    doublequote=False,
    escapechar="\",
    index=False,
)

# 获取Pandas写入后的字符串
output_text = out_f.getvalue()

# 后处理：将转义的反斜杠还原，以匹配原始的“坏格式”
output_text = output_text.replace(r'"', '"')

with open(output_filepath, "w", encoding='utf-8') as f:
    f.write(output_text)

print(f"数据已通过Pandas结合正则表达式处理并写回至 {output_filepath}。")
# 同样可以通过 diff 命令验证 input_filepath 和 output_filepath 的一致性