如何通过Pandas按id正确合并两个CSV文件

问题场景 #

有两个CSV格式的文本文件，需要按id字段进行左合并。文件部分内容如下：

文件1内容 #

>NODE_1080_length_25086_cov_1.756193_10 301022  fa_10   PF00128-Alpha-amylase-catalytic-domain
>NODE_84_length_160839_cov_0.924412_104 301022  fa_11   PF00128-Alpha-amylase-catalytic-domain
>NODE_793_length_32677_cov_0.985420_20  301022  fa_11   PF00128-Alpha-amylase-catalytic-domain

文件2内容 #

>NODE_1080_length_25086_cov_1.756193_10 gb|AVX77188.1|  67.177  724 719 236 0   0.0 1017
>NODE_102_length_146227_cov_1.193242_34 gb|AGD14055.1|;gb|QCA67811.1|;gb|QCA68885.1|;gb|QCA71791.1|;gb|AUF68592.1|; 49.512;40.618;40.047;41.734;38.038; 454;454;454;454;454; 410;421;422;369;418; 207;221;224;199;231; 0;10;10;8;8; 3.71e-159;1.64e-77;7.99e-76;4.89e-74;6.11e-70; 461;252;248;243;232;
>NODE_1045_length_25717_cov_0.952104_13 gb|AEI19864.1|;gb|QHZ14374.1|;gb|QFN56428.1|;gb|AYH98249.1|;gb|AYH98253.1|; 56.442;53.420;53.420;53.420;53.094; 372;372;372;372;372; 326;307;307;307;307; 140;142;142;142;143; 1;1;1;1;1;
>NODE_84_length_160839_cov_0.924412_104 gb|AGX66494.1|;gb|ADS15792.1|;gb|AKW57899.1|;gb|ACH26137.1|;gb|AGX66492.1|; 85.078;54.852;45.781;44.040;43.114; 518;518;518;518;518; 516;474;474;495;501; 77;204;231;246;258; 0;4;9;11;9; 0.0;0.0;1.47e-139;5.50e-138;1.69e-137; 928;557;416;413;411;

原尝试的代码：

import pandas as pd

df1 = pd.read_csv('file1', sep='\t')
df2 = pd.read_csv('file2', sep='\t')

df1.columns = ['id', 'sample', 'bin', 'profile']
df2.columns = ['id', 'sub id', 'identity', 'q length', 'alignment length', 'mismatches', 'gap opens', 'evalue', 'bit score'] 

# 第一种方法
df1.set_index(['id'])
df2.set_index(['id'])
newdf = df1.merge(df2, how='left', on='id')
newdf.to_csv('out.txt', index=False)  

# 第二种方法
df1[['sub id', 'identity', 'q length', 'alignment length', 'mismatches', 'gap opens', 'evalue', 'bit score']] = df2[['sub id', 'identity', 'q length', 'alignment length', 'mismatches', 'gap opens', 'evalue', 'bit score']]
df1.to_csv('out2.txt', index=False)  

执行后出现两个问题：

• 文件1的原始顺序被打乱
• 文件2对应的列全部显示为空值

问题原因 #

1. 列值为空的核心原因：

   • 文件实际是多空格分隔，而非严格制表符（\t），用sep='\t'读取会把整行内容解析成单个列，后续设置列名后，id字段无法正确匹配，导致merge时找不到对应行，列值为空。
   • 第一种方法中set_index没有赋值给原DataFrame，操作完全无效；第二种方法直接赋值列的逻辑错误，两个DataFrame的行未按id关联，无法匹配对应数据。

2. 顺序错乱的原因：
   Pandas的merge方法默认会对合并键（id）进行排序，导致左表（文件1）的原始顺序被改变。

解决方案 #

步骤1：正确读取文件 #

使用sep='\s+'匹配任意数量的空白字符（空格、制表符都兼容），确保每一列被正确解析：

import pandas as pd

# 读取文件，用\s+匹配任意空白分隔符，header=None表示文件无表头
df1 = pd.read_csv('file1', sep='\s+', header=None, skip_blank_lines=True)
df2 = pd.read_csv('file2', sep='\s+', header=None, skip_blank_lines=True)

# 设置列名
df1.columns = ['id', 'sample', 'bin', 'profile']
df2.columns = ['id', 'sub id', 'identity', 'q length', 'alignment length', 'mismatches', 'gap opens', 'evalue', 'bit score']

步骤2：按id左合并并保留原始顺序 #

使用merge的sort=False参数，关闭自动排序，保留左表（文件1）的原始顺序：

# 左合并，on指定合并键，sort=False保留df1的原始顺序
merged_df = df1.merge(df2, how='left', on='id', sort=False)

# 输出结果，用\t作为分隔符保持格式一致
merged_df.to_csv('merged_result.txt', index=False, sep='\t')

关键说明 #

• sep='\s+'解决了多空格分隔的解析问题，确保id字段能在两个DataFrame中正确匹配。
• sort=False是保留原始顺序的核心参数，避免merge操作自动排序。
• 无需手动设置索引，直接通过on='id'指定合并键即可完成关联，逻辑更清晰。

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