Assign values column based on a column in another dataframe

Question

I would like to assign values to a column based on the values in another dataframe.

For example, in the example below the values in column B of df2 should be equal to the df['B'] value in df1 where df1['A'] = df2['A']. The output should then be the same as df3.

> import pandas as pd  
> import numpy as np
> 
> df1 = pd.DataFrame({'A':[1,2,3],'B':["req 1", "req 2","req 3"]})
> df2 = pd.DataFrame({'A':[2,1,7],'B':[np.nan,np.nan,np.nan]})
> df3= pd.DataFrame({'A':[2,1,7],'B':["req 2", "req 1",np.nan]})

Could somebody help me with this?

sophocles · Accepted Answer · 2021-04-14 17:26:55Z

3

You can achieve that with fillna():

out = df2.set_index('A').fillna(df1.set_index('A')).reset_index()

>>> out

   A      B
0  2  req 2
1  1  req 1
2  7    NaN

>>> df3
 
   A      B
0  2  req 2
1  1  req 1
2  7    NaN

edited Apr 14, 2021 at 17:26

answered Apr 14, 2021 at 14:56

sophocles

13.9k3 gold badges18 silver badges37 bronze badges

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

Comments

Nk03 · Accepted Answer · 2021-04-14 14:58:55Z

3

You can do merge to achieve this -

df2 = df2.merge(df1, on='A', how='left').drop('B_x', axis=1).rename(columns={"B_y": 'B'})

output -


    A   B
0   2   req 2
1   1   req 1
2   7   NaN

answered Apr 14, 2021 at 14:58

Nk03

15k2 gold badges11 silver badges24 bronze badges

Collectives™ on Stack Overflow

Assign values column based on a column in another dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related