Adding values from another dataframe

Question

I have a df1

    Date         Open         Expiry    Entry Strike
0   2020-01-03  12261.10    2020-01-09  12200.0
1   2020-01-10  12271.00    2020-01-16  12200.0
2   2020-01-17  12328.40    2020-01-23  12300.0
3   2020-01-24  12174.55    2020-01-30  12100.0
4   2020-01-31  12100.40    2020-02-06  12100.0

i want to add values from df2

    Date         Expiry    Type  Strike Price   Open    Close
0   2020-01-03  2020-01-09  CE     13100         0.0    65.85
1   2020-01-03  2020-01-09  CE     13150         0.0    59.40
2   2020-01-03  2020-01-09  CE     13200         0.0    53.55
3   2020-01-03  2020-01-09  CE     13250         0.0    48.15
4   2020-01-03  2020-01-09  CE     13300         0.0    43.25

i want to compare elements of column Date , Expiry and Entry Price with Date ,Expiry and Strike Price of df2 and add corresponding Open column element to df1 if the condition matches. when i directly compare columns i get errors like .

ValueError: Can only compare identically-labeled Series objects

thanks for the help

can you share your code so we can see what has been done. also can you share expected results so we know what you are looking for please? — Joe Ferndz
– Joe Ferndz, Commented Sep 27, 2020 at 4:08
df1["Entry CE"] = df2["Open"] if (df1['Date'] == df2['Date']) & (df1['Expiry'] == df2['Expiry'])& (df1['Entry Strike'] == df2['Strike Price']) else " " i tried this . i dont know if its the correct way. i am new to pandas . — Vipul Sanjay Charthal
– Vipul Sanjay Charthal, Commented Sep 27, 2020 at 4:30

Joe Ferndz · Accepted Answer · 2020-09-27 04:48:51Z

2

Did you try to do a simple merge and see if it solves what you want.

Do something like this:

pd.merge(df1,df2,how='left', left_on=['Date','Expiry','Entry Strike'], right_on=['Date','Expiry','Strike Price'])

Your output will be as shown below. For this, I modified the first row to match. Otherwise, the data has no matching records.

         Date    Open_x      Expiry  ...  Strike Price Open_y  Close
0  2020-01-03  12261.10  2020-01-09  ...       12200.0    0.0  43.25
1  2020-01-10  12271.00  2020-01-16  ...           NaN    NaN    NaN
2  2020-01-17  12328.40  2020-01-23  ...           NaN    NaN    NaN
3  2020-01-24  12174.55  2020-01-30  ...           NaN    NaN    NaN
4  2020-01-31  12100.40  2020-02-06  ...           NaN    NaN    NaN

You can then delete all columns that you don't want.

answered Sep 27, 2020 at 4:48

Joe Ferndz

8,5282 gold badges15 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vipul Sanjay Charthal Over a year ago

wow.. it worked . thanks man . it worked so quick that i have doubt now and checking the output manually .

Srikar Manthatti · Accepted Answer · 2020-09-27 05:22:29Z

You can try with apply function aswell

def check(row):
    dt = row['Date']
    ex = row["Expiry"]
    sp = row["Entry Strike"]
    return df2[(df2['Date']==dt) & (df2['Expiry']==ex) & (df2["Strike Price"]==sp)]['Open']

df1['new_col'] = df1.apply(lambda x:  check(x), axis = 1)

this will also work, check the output below I have changed one of the value to match one row.

df1

      Date      Open        Expiry   Entry Strike   new_col
0   2020-01-03  12261.10    2020-01-09  13100.0      0.0
1   2020-01-10  12271.00    2020-01-16  12200.0      NaN
2   2020-01-17  12328.40    2020-01-23  12300.0      NaN
3   2020-01-24  12174.55    2020-01-30  12100.0      NaN
4   2020-01-31  12100.40    2020-02-06  12100.0      NaN

Collectives™ on Stack Overflow

Adding values from another dataframe

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related