导出Pandas DataFrame至CSV时内容错乱问题求助

问题描述 #

我开发了一个调用Uniprot API的处理流程，其中某个查询出现异常：获取的DataFrame（df）结构符合预期（1行15列），但使用pd.to_csv导出为CSV文件后，在Excel中打开显示错乱——原本的1行被拆分为2行，第二行起始于'Sequence'列的中间位置，拆分点位于序列片段RLLANAECQEGQSVCFEIRVSGIPPPTLKWEKDG与PLSLGPNIEIIHEGLDYYALHIRDTLPEDTGYY之间，原序列中此处显示为小写字母q。其余99个查询均无此问题，怀疑是pd.to_csv调用存在问题，求分析原因。

测试代码 #

import requests 
import pandas as pd
import io 

def queries_to_table(base, query, organism_id):
    rest_url = base + f'query=(({query})AND(organism_id:{organism_id}))'
    response = requests.get(rest_url)
    if response.status_code == 200:
        return pd.read_csv(io.StringIO(response.text), 
                           sep = '\t')
    else:
        raise ValueError(f'The uniprot API returned a status code of {response.status_code}.  '\
                         'This was not 200 as expected, which may reflect an issue '\
                         f'with your query:  {query}.\n\nSee here for more '\
                         'information: https://www.uniprot.org/help/rest-api-headers.  '\
                         f'Full url: {rest_url}')

size = 500
fields = 'accession,id,protein_name,gene_names,organism_name,'\
          'length,sequence,go_p,go_c,go,go_f,ft_topo_dom,'\
          'ft_transmem,cc_subcellular_location,ft_intramem'
url_base = f'https://rest.uniprot.org/uniprotkb/search?size={size}&'\
           f'fields={fields}&format=tsv&'
query = '(id:TITIN_HUMAN)'
organism_id = 9606

df = queries_to_table(url_base, query, organism_id)
# -> df looks fine - one row and 15 columns

pd.concat([df]).to_csv('test2_error.csv')
# -> opening in excel this is broken - it splits df['Sequence'] into two rows at 
#the junction between 'RLLANAECQEGQSVCFEIRVSGIPPPTLKWEKDG' and 
#'PLSLGPNIEIIHEGLDYYALHIRDTLPEDTGYY'. In df['Sequence'], this sequence is joined 
#by a 'q' (the below string covers the junction, and has the previously quoted substrings in capitals):
#tdstlrpmfkRLLANAECQEGQSVCFEIRVSGIPPPTLKWEKDGqPLSLGPNIEIIHEGLDYYALHIRDTLPEDTGYYrvtatntags

原因分析与解决方案 #

问题根源 #

这个问题并非pd.to_csv的问题，核心原因是Uniprot返回的序列中隐藏了换行符（你看到的小写q大概率是显示bug，实际对应位置是\n换行符）。Excel读取CSV文件时，会将单元格内的换行符识别为行分隔符，从而导致行被错误拆分。

解决方案 #

• 清理序列中的换行符：在导出CSV前，对Sequence列做清洗，移除所有换行符：

  df['Sequence'] = df['Sequence'].str.replace('\n', '', regex=False)
  df.to_csv('test2_fixed.csv')
  

• 强制指定行终止符（可选）：如果仍存在其他行分隔符问题，可以在导出时明确指定行终止符：

  df.to_csv('test2_fixed.csv', line_terminator='\r\n')
  

• 验证原始响应数据：可以打印Uniprot返回的原始文本，确认是否存在换行符：

  response = requests.get(rest_url)
  print(repr(response.text))  # 用repr查看原始字符，能显示转义字符
  

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