1

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 BMI
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=

Weight in lb BMI Height
165 16.5 180
166 17.0 180
167 17.3 180
168 17.4 180
169 17.9 180
170 18.4 180
171 18.7 180
172 18.9 180
173 19.2 180
174 19.3 180
175 19.6 180
176 19.9 180
177 20.0 180
178 20.2 180
179 21.6 180
180 21.9 180
181 22.1 180
182 22.3 180
183 22.5 180
184 22.8 180
185 22.9 180
186 23.0 180
187 22.1 180
188 22.1 180

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
Improve this question

2 Answers 2

Reset to default
1

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

you can also do dict(df2[['Weight in lb','BMI']])
0

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)
Improve this answer

1 Comment

The answer works but my original dataframe df2 has one more column height, which I didn't show here for simplicity I will edit the question to include that too. Then the solution would be 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)

Your Answer

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