Python Pandas - Merge different sized DataFrames and replace value in row with matching columns

Question

I have two dataframes of a format similar to below:

df1:

0  fname    lname    note
1  abby     ross     note1
2  rob      william  note2
3  abby     ross     note3
4  john     doe      note4
5  bob      dole     note5

df2:

0  fname    lname    note
1  abby     ross     note6
2  rob      william  note4

I want to merge finding matches based on fname and lname and then update the note column in the first DataFrame with the note column in the second DataFrame

The result I am trying to achieve would be like this:

0  fname    lname    note
1  abby     ross     note6
2  rob      william  note4
3  abby     ross     note6
4  john     doe      note4
5  bob      dole     note5

This is the code I was working with so far:

pd.merge(df1, df2, on=['fname', 'lname'], how='left')

but it just creates a new column with _y appended to it. How can I get it to just update that column?

Any help would be greatly appreciate, thanks!

Hey, deleted the comment because yes, I saw the problem with what I suggested. — Sid
– Sid, Commented Apr 15, 2021 at 21:06

Bruno Mello · Accepted Answer · 2021-04-15 21:13:33Z

1

You can merge and then correct the values:

df_3 = pd.merge(df1, df2, on=['fname', 'lname'], how='outer') 

df_3['note'] = df_3['note_x']
df_3.loc[df_3['note'].isna(), 'note'] = df_3['note_y']
df_3 = df_3.drop(['note_x', 'note_y'], axis=1)

edited Apr 15, 2021 at 21:13

answered Apr 15, 2021 at 21:08

Bruno Mello

4,6781 gold badge16 silver badges46 bronze badges

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

Comments

Sid · Accepted Answer · 2021-04-15 21:16:44Z

0

Do what you are doing:

then:

# fill nan values in note_y
out_df['note_y'].fillna(out_df['note_x'])

# Keep cols you want
out_df = out_df[['fname', 'lname', 'note_y']]

# rename the columns
out_df.columns = ['fname', 'lname', 'note']

I don't like this approach a whole lot as it won't be very scalable or generalize able. Waiting for a stellar answer for this question.

answered Apr 15, 2021 at 21:16

Sid

4,0758 gold badges37 silver badges77 bronze badges

Comments

BENY · Accepted Answer · 2021-04-15 22:13:08Z

0

Try with update

df1=df1.set_index(['fname','lname'])
df1.update(df2.set_index(['fname','lname']))
df1=df1.reset_index()
df1
Out[55]: 
  fname    lname    0   note
0  abby     ross  1.0  note6
1   rob  william  2.0  note4
2  john      doe  3.0  note3
3   bob     dole  4.0  note4

answered Apr 15, 2021 at 22:13

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Python Pandas - Merge different sized DataFrames and replace value in row with matching columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related