In Pandas : How can i subtract two dataframes values based on other two dataframe same Column which contain same Values

user_17231996 0
Viewed 1367

I have Two Dataframe - First Dataset is -

enter image description here

Second Dataset is -

enter image description here

In both Dataset there is the same column, but I want to subtract values based on the Strike Price column, if both datasets Strick_price match then subtract those Close values.

Example - 
Dataset1   dataset2      Close
29500      29500         Close - Close
30000      Not Match     Nan
30300      30300         Close - Close
30400      30400         Close - Close
30500      Not Match     Nan

I don't understand, How can I do this? please help me to solve this problem.

pandas dataframe
1 Answers

We can start by merging both df_1 and df_2 on matching values of df_1 on strike_price using a left merge :

>>> df = pd.merge(df_1[['strike_price', 'close']],
...               df_2[['strike_price', 'close']],
...               how='left',
...               left_on=['strike_price'],
...               right_on=['strike_price'],
...               suffixes=['_df_1',
...                         '_df_2'])
>>> df
    strike_price    close_df_1  close_df_2
0   30000           3131.20     3000.0
1   30300           2836.30     NaN
2   30400           2736.95     2744.0
3   30500           2630.00     2800.0
4   30600           2530.60     2650.6

Then, we can build a column diff subtracting columns close_df_1 and close_df_2 to get the expected result :

>>> df['diff'] = df['close_df_1'] - df['close_df_2']
>>> df
    strike_price    close_df_1  close_df_2  diff
0   30000           3131.20     3000.0      131.20
1   30300           2836.30     NaN         NaN
2   30400           2736.95     2744.0      -7.05
3   30500           2630.00     2800.0      -170.00
4   30600           2530.60     2650.6      -120.00
user_84793870
Related