0

I have the following Dataframes

DF1

Index Val
Index1 5
Index2 5
Index3 5
Index4 5
Index5 5
Index6 5
Index7 5

DF2

Index Val
Index1 2
Index2 2
Index3 2
Index4 2

When I do :

DF1['Val'].subtract(DF2['Val'])

I have the following output :

Index Val
Index1 3
Index2 3
Index3 3
Index4 3
Index5 NaN
Index6 NaN
Index7 NaN

And the excepted output would be :

Index Val
Index1 3
Index2 3
Index3 3
Index4 3
Index5 5
Index6 5
Index7 5

What am I missing ?


What to do when the index of DF2 is longer than DF1: We should have negative values, how to display them?

DF1

Index Val
Index1 5
Index2 5
Index3 5
Index4 5
Index5 5
Index6 5
Index7 5

DF2

Index Val
Index1 5
Index2 5
Index3 5
Index4 5
Index5 5
Index6 5
Index7 5
Index8 5

The output would be

Index Val
Index1 0
Index2 0
Index3 0
Index4 0
Index5 0
Index6 0
Index7 0
Index8 -5

More Complex One : DF1

Index Val
Index4 5
Index5 5

DF2

Index Val
Index5 5
Index6 5
Index7 5

Output

Index Val
Index4 5
Index5 0
Index6 -5
Index7 -5

1 Answer 1

2

Given:

DF1 = pd.DataFrame({'Val':[5, 5, 5, 5, 5, 5, 5]}, index=[0, 1, 2, 3, 4, 5, 6])

DF2 = pd.DataFrame({'Val':[2, 2, 2, 2]}, index=[0, 1, 2, 3])

DF3 = pd.DataFrame({'Val':[5, 5, 5, 5, 5, 5, 5, 5]}, index=[0, 1, 2, 3, 4, 5, 6, 7])

You can do:

DF1['Val'].subtract(DF2['Val'].reindex(DF1.index, fill_value=0))
0    3
1    3
2    3
3    3
4    5
5    5
6    5
Name: Val, dtype: int64

For your second question, it's the opposite:

DF1['Val'].reindex(DF2.index, fill_value=0).subtract(DF2['Val'])
0    0
1    0
2    0
3    0
4    0
5    0
6    0
7   -5
Name: Val, dtype: int64

EDIT: For the more complex case (where there is an index overlap but one is not a subset of the other):

DF4 = pd.DataFrame({'Val':[5, 5]}, index=[4, 5])

DF5 = pd.DataFrame({'Val':[5, 5, 5]}, index=[5, 6, 7])

common_index = DF4.index.union(DF5.index)
DF4['Val'].reindex(common_index, fill_value=0).subtract(DF5['Val'].reindex(common_index, fill_value=0))
4    5
5    0
6   -5
7   -5
Name: Val, dtype: int64
Sign up to request clarification or add additional context in comments.

4 Comments

Is it possible to define the index so we don't miss Index from DF1 and DF2 ? DF1 Index = a,b DF2 Index = b,c The Index Excepted would be a,b,c
Please see the edit, your answer will work 80% of the time
@TourEiffel edited with the solution
Thank you v Much for this really complete and detailed answer !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.