如何实现类文本对比工具的Pandas DataFrame行对齐？

需求详情 #

需要创建一个DataFrame，以文本对比工具的行对齐逻辑，对齐两个输入DataFrame的行：

• 输入的df_A和df_B均包含year、pos、score三级索引，索引存在交集但不保证完全重合
• 对齐逻辑类似文本对比工具：每行向下"移位"，直到找到下一个匹配的行，不匹配的行对应位置填充NaN或留空

现有方法的局限性 #

测试过Pandas标准方法（merge、join、concat、align）均无法得到预期结果：

• 例如join方法无法为pos=1或pos=8生成正确的空值
• 对pos=4的行重复次数过多，不符合文本对比的对齐逻辑

想确认Pandas是否有现成实现该对齐逻辑的方法，若没有则自行编写自定义函数。

测试代码 #

import pandas as pd

data_A = {"year":[2023]*7,"pos":[1,2,4,4,4,8,8],"score":[15,20,30,30,30,60,60],"value":["a","b","c","c","c","d","d"]}
df_A = pd.DataFrame(data_A)
df_A = df_A.set_index(["year","pos","score"])

data_B = {"year":[2023]*9,"pos":[1,1,1,3,3,4,4,8,10],"score":[15,15,15,25,25,30,30,60,80],"value":["v","v","v","w","w","x","x","y","z"]}
df_B = pd.DataFrame(data_B)
df_B = df_B.set_index(["year","pos","score"])

# 测试merge
df = pd.merge(df_A,df_B,on=["year","pos","score"],how="outer")
print(df)

# 测试join
df = df_A.join(df_B,how="outer",lsuffix='_left', rsuffix='_right')
print(df)

# 测试align
llll, rrrr = df_A.align(df_B)
print(llll)
print(rrrr)

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