get the matching rows for multiple columns pandas dataframe

Question

Consider my first data frame df1

      col1 col2  col3
0    hello    q     1
1    world    q     2
2  welcome    r     3
3    hello    t     4

And second data frame df2

    col1 col2  col3
0  hello    q     2

Need output like

      col1 col2  col3
0    hello    q     2
1    world    q     2
2  welcome    r     3
3    hello    t     4

'col1' and 'col2' should be equal and if 'col3' differs get the output and replace the value in dataframe first

I tried to use merge

df1.merge(df2, on=['col1', 'col2'])

    col1 col2  col3_x  col3_y
0  hello    q       1       2

But I don't know what to do next.

first.loc[first['col1'].isin(second['col1']), 'col1'] can able to get the matched rows for one column — sakthivinoth
– sakthivinoth, Commented Apr 5, 2018 at 13:27

piRSquared · Accepted Answer · 2018-04-05 13:41:28Z

1

Option 1

merge, append, drop_duplicates

df1.drop('col3', 1).merge(df2).append(df1).drop_duplicates(['col1', 'col2'])

      col1 col2  col3
0    hello    q     2
1    world    q     2
2  welcome    r     3
3    hello    t     4

Option 2

set_index and combine_first

cols = ['col1', 'col2']
df2.set_index(cols).combine_first(
    df1.set_index(cols)
).reset_index().astype(df1.dtypes)

      col1 col2  col3
0    hello    q     2
1    hello    t     4
2  welcome    r     3
3    world    q     2

answered Apr 5, 2018 at 13:41

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

2 Comments

piRSquared Over a year ago

That will only work because the row to replace with happens to have the same index value. That seems coincidental for the chosen toy data.

Scott Boston Over a year ago

Agree OP did state they wanted to match on col1 and col2 not index. Thanks.

Collectives™ on Stack Overflow

get the matching rows for multiple columns pandas dataframe

1 Answer 1

Option 1

Option 2

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option 1

Option 2

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related