Can I subtract two pandas dataframes with different indexes in both axes?-CodePudding

I am trying to subtract two dataframes, but they might not always have the same indexes on the axis

df1

   A B C D E
ZA 1 2 4 0 1
ZB 1 5 1 3 1
ZC 1 5 0 1 0

And df2

   A B C D
ZA 4 6 5 0
ZB 5 1 5 9
ZD 0 9 5 3

Such that my result should be

df2.sub(df1)

   A  B  C  D  E
ZA  3  4  1  0  -1
ZB  4 -4  4  6  -1
ZC -1 -5  0 -1  0
ZD  0  9  5  3  0

The df.sub() function isn't quite giving me what I'm looking for.

Someone pointed out that I could split the indexes into those that intersect and those that don't. But what about if both axes can be different? For example product vs customer. If I'm comparing two date ranges there could be different customers and products during that time.

CodePudding user response：

Use DataFrame.align before subtracting by DataFrame.sub:

df2, df1 = df2.align(df1, fill_value=0)
df = df2.sub(df1)
print (df)
    A  B  C  D  E
ZA  3  4  1  0 -1
ZB  4 -4  4  6 -1
ZC -1 -5  0 -1  0
ZD  0  9  5  3  0

CodePudding user response：

You can use the fill_value option of sub and fillna:

df2.sub(df1, fill_value=0).fillna(0, downcast='infer')

output:

    A  B  C  D  E
ZA  3  4  1  0 -1
ZB  4 -4  4  6 -1
ZC -1 -5  0 -1  0
ZD  0  9  5  3  0