pandas apply subtractions on columns function when indexes are not equal, based on alignment in another columns

Question

I have two dataframes:

df1 =  
    C0   C1. C2.  
4   AB. 1.  2
5   AC. 7   8
6   AD. 9.  9
7   AE. 2.  6
8   AG  8.  9

df2 = 
   C0    C1. C2
8  AB    0. 1
9  AE.   6. 3
10 AD.   1. 2

I want to apply a subtraction between these two dataframes, such that when the value of the columns C0 is the same - I will get the subsraction, and when is not - a bool column will have the value False. notice that current indeics are not aligned. So new df1 should be:

df1 =  
    C0   C1. C2. diff_C1 match  
4   AB.  1.  2.    1.    True
5   AC.  7   8.    0.    False
6   AD.  9.  9.    8.    True
7   AE.  2.  6.    -4.   True
8   AG   8.  9.    0    False

What is the best way to do it?

Answer 1

A possible solution, based on pandas.DataFrame.merge:

(df1.merge(df2.iloc[:,:-1], on='C0', suffixes=['', 'y'], how='left')
 .rename({'C1.y': 'diff_C1'}, axis=1)
 .assign(diff_C1 = lambda x: x['C1.'].sub(x['diff_C1']))
 .assign(match = lambda x: x['diff_C1'].notna())
 .fillna(0))

Output:

    C0  C1.  C2.  diff_C1  match
0  AB.  1.0    2      1.0   True
1  AC.  7.0    8      0.0  False
2  AD.  9.0    9      8.0   True
3  AE.  2.0    6     -4.0   True
4  AG.  8.0    9      0.0  False

Answer 2

You can try merging the columns using pandas.DataFrame.merge on column C0 and how as left as shown below

df1.merge(df2, how='left', on='C0')
   .assign(match=lambda x: x['C1_y'].notna())
   .fillna(0)

Output:

then subtract the C1 columns i.e. C1_x and C1_y

df['C1_diff'] = df['C1_x'] - df['C1_y']