使用Pandas concat合并Excel文件时列数不符的问题排查

问题场景 #

示例数据 #

f1.xlsx数据：

id na ln 
a1 b1 c1
a2 b2 c2 
k1 k2 k3

f2.xlsx数据：

jd kd rd md nd pd  
a1 b1 c1 d1 e1 f1
a2 b2 c2 d2 e2 f2
a3 b3 c3 d3 e3 f3

执行代码 #

尝试通过以下代码合并两文件数据：

import pandas as pd
df1 = pd.read_excel('f1.xlsx', usecols=[0,1,2], header=None)
df2 = pd.read_excel('f2.xlsx', usecols=[0,4,5], header=None)
merge_df = pd.concat([df1 ,df2])

异常结果 #

合并后得到5列（列名0、1、2、4、5），而非期望的3列，输出如下：

0    1    2    4    5
0  id   na   ln  NaN  NaN
1  a1   b1   c1  NaN  NaN
2  a2   b2   c2  NaN  NaN
3  k1   k2   k3  NaN  NaN
0  jd  NaN  NaN   nd   pd
1  a1  NaN  NaN   e1   f1
2  a2  NaN  NaN   e2   f2
3  a3  NaN  NaN   e3   f3

但将f2另存为3列后用usecols=[0,1,2]读取，合并就能得到3列。

问题原因 #

核心在于**usecols参数会保留原Excel的列索引**：

• 读取df1时，usecols=[0,1,2]对应原Excel前3列，DataFrame列名被设为0、1、2
• 读取df2时，usecols=[0,4,5]对应原Excel第1、5、6列，DataFrame会保留原列索引0、4、5，不会重新从0开始编号

pd.concat()合并时会按列名对齐：df1的列0、1、2和df2的列0、4、5合并后，就会生成5列，缺失数据的位置填充NaN。

而将f2另存为3列后，usecols=[0,1,2]读取的列索引就是0、1、2，和df1列名完全一致，合并时自然对齐为3列。

解决方法 #

只需确保两个DataFrame的列名一致即可，以下两种常用方式：

方式1：读取后重命名df2的列 #

import pandas as pd
df1 = pd.read_excel('f1.xlsx', usecols=[0,1,2], header=None)
df2 = pd.read_excel('f2.xlsx', usecols=[0,4,5], header=None)
# 将df2列名改为和df1一致
df2.columns = [0, 1, 2]
merge_df = pd.concat([df1, df2])

方式2：读取时直接指定列名 #

import pandas as pd
# 读取时通过names参数统一列名
df1 = pd.read_excel('f1.xlsx', usecols=[0,1,2], header=None, names=[0,1,2])
df2 = pd.read_excel('f2.xlsx', usecols=[0,4,5], header=None, names=[0,1,2])
merge_df = pd.concat([df1, df2])

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