join two pandas dataframe using a specific column

Question

I am new with pandas and I am trying to join two dataframes based on the equality of one specific column. For example suppose that I have the followings:

df1
A    B    C
1    2    3
2    2    2

df2
A    B    C
5    6    7
2    8    9

Both dataframes have the same columns and the value of only one column (say A) might be equal. What I want as output is this:

df3
A    B    C   B    C
2    8    9   2    2

The values for column 'A' are unique in both dataframes.

Thanks

vk1011 · Accepted Answer · 2015-06-02 00:52:18Z

43

pd.concat([df1.set_index('A'),df2.set_index('A')], axis=1, join='inner')

If you wish to maintain column A as a non-index, then:

pd.concat([df1.set_index('A'),df2.set_index('A')], axis=1, join='inner').reset_index()

edited Jun 2, 2015 at 0:52

answered Jun 1, 2015 at 22:53

vk1011

7,2297 gold badges29 silver badges42 bronze badges

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

3 Comments

J Richard Snape Over a year ago

With this good method - A becomes the index. This may be what the OP wants, but you might also offer use of reset_index to turn it into a column if that's what is wanted.

vk1011 Over a year ago

Sure, the index can be reset by adding .reset_index() at the end. pd.concat([df1.set_index('A'),df2.set_index('A')], axis=1, join='inner').reset_index()

J Richard Snape Over a year ago

yup - that's what I was getting at :)

Paul H · Accepted Answer · 2015-06-01 22:59:20Z

37

Alternatively, you could just do:

df3 = df1.merge(df2, on='A', how='inner', suffixes=('_1', '_2'))

And then you can keep track of each value's origin

answered Jun 1, 2015 at 22:59

Paul H

68.7k23 gold badges165 silver badges139 bronze badges

4 Comments

ahajib Over a year ago

What does suffixes do?

Paul H Over a year ago

addes a suffix to each column name so that you're not left with e.g. two columns called "B". When you have duplicate column names, you get very unexpected behavior with say, df3['B'].apply(lambda x: ...) since now df['B'] is a DataFrame and not a Series.

ahajib Over a year ago

Awesome. That actually helps alot

Sahar Millis Over a year ago

Works great. tnx :)

Collectives™ on Stack Overflow

join two pandas dataframe using a specific column

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related