Update dataframe column based on another dataframe column without for loop

user_5181947 0
Viewed 81

I have two dataframes df1 and df2.

df1:

id val
1  25
2  40
3  78

df2:

id val
2  8
1  5

Now I want to do something like df1['val'] = df1['val']/df2['val'] for matching id. I can do that by iterating over all df2 rows as df2 is a subset of df1 so it may be missing some values, which I want to keep unchanged. This is what I have right now:

for row in df2.iterrows():
    df1.loc[df1['id']==row[1]['id'], 'val'] /= row[1]['val']

df1:

id val
1  5
2  5
3  78

How can I achieve the same without using for loop to improve speed?

pandas dataframe python
1 Answers

Use Series.map with Series.div:

df1['val'] = df1['val'].div(df1['id'].map(df2.set_index('id')['val']), fill_value=1)
print (df1)
   id   val
0   1   5.0
1   2   5.0
2   3  78.0

Solution with merge with left join:

df1['val'] = df1['val'].div(df1.merge(df2, on='id', how='left')['val_y'], fill_value=1)
          
print (df1)
   id   val
0   1   5.0
1   2   5.0
2   3  78.0
user_29010020
Related