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

chi11ax 0
Viewed 22

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.

matrix dataframe pandas python
2 Answers

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
mozway0

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
jezrael0
Related