[Python] Pandas Dataframe align/merge/join in similar way to text based file comparison

I want to create a dataframe that aligns the rows of two input dataframes in a similar manner to how a text comparison tool would align text when comparing two files.

For instance, consider the image below, where df_A and df_B are the two input dataframes. They both have triple index year, pos, and score. In this case there is an intersection between the two indexes, but that won't necessarily be the case.



I want to create a dataframe (or dataframes) that aligns the rows of df_A and df_B giving the output shown. The empty cells can contain NaN, or be empty. Note that this is not quite the same as the result that is generated by any of the standard Pandas merge, join, concat or align methods (as far as I can tell.)

For instance join (see image below) doesn't give the correct NaN/empty values for pos values 1 or 8. And it duplicates the values for pos 4 too many times. (Note that I understand what join is doing, and why it does it - it just isn't what I am needing to do here.)



The required output is more akin to what would be generated by a text comparison tool if each row of the dataframes was considered a line of text. That is, rows of each dataframe are "shuffled" down until the next matching line is found.

I can write a custom function to do this, but am wondering if I'm missing something that may be available in Pandas already.

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"])

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

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

llll, rrrr = df_A.align(df_B)
print(llll)
print(rrrr)

Continue reading...