Subtracting multiple columns between dataframes based on key-CodePudding

I have two dataframes, example:

Df1 -

A   B   C   D
x   j   5   2
y   k   7   3
z   l   9   4

Df2 -

A   B   C   D
z   o   1   1
x   p   2   1
y   q   3   1

I want to deduct columns C and D in Df2 from columns C and D in Df1 based on the key contained in column A.

I also want to ensure that column B remains untouched, example:

Df3 -

A   B   C   D
x   j   3   1
y   k   4   2
z   l   8   3

I found an almost perfect answer in the following thread: Subtracting columns based on key column in pandas dataframe

However what the answer does not explain is if there are other columns in the primary df (such as column B) that should not be involved as an index or with the operation.

Is somebody please able to advise?

I was originally performing a loop which find the value in the other df and deducts it however this takes too long for my code to run with the size of data I am working with.

CodePudding user response：

Idea is specify column(s) for maching and column(s) for subtract, convert all not cols columnsnames to MultiIndex, subtract:

match = ['A']
cols = ['C','D']
df1 = Df1.set_index(match   Df1.columns.difference(match   cols).tolist())

df = df1.sub(Df2.set_index(match)[cols], level=0).reset_index()
print (df)
   A  B  C  D
0  x  j  3  1
1  y  k  4  2
2  z  l  8  3

Or replace not matched values to original Df1:

match = ['A']
cols = ['C','D']
df1 = Df1.set_index(match)

df = df1.sub(Df2.set_index(match)[cols], level=0).reset_index().fillna(Df1)
print (df)
   A  B  C  D
0  x  j  3  1
1  y  k  4  2
2  z  l  8  3