Merging two pandas dataframes on multiple columns

Question

I have two dataframes:

>>> df1
[Output]: col1   col2   col3   col4
           a     abc     10    str1
           b     abc     20    str2
           c     def     20    str2
           d     abc     30    str2

>>> df2
[Output]: col1   col2   col3   col5   col6
           d     abc     30    str6    47
           b     abc     20    str5    66
           c     def     20    str7    53
           a     abc     10    str5    21

Below is what I want to generate:

>>> df_merged
[Output]: col1   col2   col5
           a     abc    str5
           b     abc    str5 
           c     def    str7
           d     abc    str6

I don't want to generate more than 4 rows and that is usually what happens when I try to merge the dataframes. Thanks for the tips!

I dont quite understand what your trying to merge on. Just col1? col1 and 2? In your example it actually wouldn't matter. — Ben Pap
– Ben Pap, Commented Jul 23, 2019 at 23:32
Why merge? I see only a sorted df2 with subsetted columns? — Parfait
– Parfait, Commented Jul 23, 2019 at 23:44

Erfan · Accepted Answer · 2019-07-23 23:36:03Z

1

Use .merge by subselecting the correct columns and using col1 & col2 as key columns:

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

  col1 col2  col5
0    a  abc  str5
1    b  abc  str5
2    c  def  str7
3    d  abc  str6

answered Jul 23, 2019 at 23:36

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

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

Comments

bobrien · Accepted Answer · 2019-07-23 23:15:10Z

1

df_merged = pd.DataFrame()
df_merged['col1'] = df1['col1'][0:3]
df_merged['col2'] = df1['col2'][0:3]
df_merged['col5'] = df2['col5'][0:3]

Does that help with what you're looking for?

answered Jul 23, 2019 at 23:15

bobrien

6810 bronze badges

1 Comment

realkes Over a year ago

My actual dataframes are much bigger that's why I wanted to use ` merge()`

Collectives™ on Stack Overflow

Merging two pandas dataframes on multiple columns

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related