Update values in pandas columns based on values from another DataFrame-CodePudding

Two DataFrames df1 and df2 both has Weight in lb columns if Weight in lb matches I need to update the corresponding BMI in df1 from df2.

DataFrame df1=

Index	First Name	Age	Gender	Weight in lb
0	James	21	Male	167
1	John	25	Male	175
2	Patricia	23	Female	132
4	Kevin	22	Male	169
5	Alex	27	Male	169

DataFrame df2=

I tried

df1['BMI'] = df2.loc[df2['Weight in lb'].isin(df1['Weight in lb'],'BMI']

But it is not working, I cannot do merge operation because the original dataframe is too complex for that

Desired Output df1=

Index	First Name	Age	Gender	Weight in lb	BMI
0	James	21	Male	167	17.3
1	John	25	Male	175	19.6
2	Patricia	23	Female	132
4	Kevin	22	Male	169	17.9
5	Alex	27	Male	169	17.9

CodePudding user response：

You can use df2 to make a mapping from weight to BMI and then use it on df1

weight_to_bmi = dict(df2.values)
df1['BMI'] = df1['Weight in lb'].map(weight_to_bmi)

CodePudding user response：

Building off of mitoRibo's Answer

weight_to_bmi = pd.Series(df2['Weight in lb].values,index=df2['BMI']).to_dict()
df1['BMI'] = df1['Weight in lb'].map(weight_to_bmi)