Merge pandas dataframe with overwrite of columns

Question

What is the quickest way to merge to python data frames in this manner? I have two data frames with similar structures (both have a primary key id and some value columns). What I want to do is merge the two data frames based on id. Are there any ways do this based on pandas operations? How I've implemented it right now is as coded below:

import pandas as pd

a = pd.DataFrame({'id': [1,2,3], 'letter': ['a', 'b', 'c']})
b = pd.DataFrame({'id': [1,3,4], 'letter': ['A', 'C', 'D']})
a_dict =  {e[id]: e for e in a.to_dict('record')}
b_dict =  {e[id]: e for e in b.to_dict('record')}
c_dict = a_dict.copy()
c_dict.update(b_dict)
c = pd.DataFrame(list(c.values())

Here, c would be equivalent to

pd.DataFrame({'id': [1,2,3,4], 'letter':['A','b', 'C', 'D']})

   id letter
0   1      A
1   2      b
2   3      C
3   4      D

piRSquared · Accepted Answer · 2018-09-19 05:00:43Z

7

`combine_first`

If 'id' is your primary key, then use it as your index.

b.set_index('id').combine_first(a.set_index('id')).reset_index()

   id letter
0   1      A
1   2      b
2   3      C
3   4      D

`merge` with `groupby`

a.merge(b, 'outer', 'id').groupby(lambda x: x.split('_')[0], axis=1).last()

   id letter
0   1      A
1   2      b
2   3      C
3   4      D

edited Sep 19, 2018 at 5:00

answered Sep 19, 2018 at 3:44

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

1 Comment

Adrian Keister Over a year ago

Very nice solution, very elegant. Just what I needed!

niraj · Accepted Answer · 2018-09-19 04:55:52Z

1

One way may be as following:

append dataframe a to dataframe b
drop duplicates based on id
sort values on remaining by id
reset index and drop older index

You can try:

import pandas as pd

a = pd.DataFrame({'id': [1,2,3], 'letter': ['a', 'b', 'c']})
b = pd.DataFrame({'id': [1,3,4], 'letter': ['A', 'C', 'D']})

c = b.append(a).drop_duplicates(subset='id').sort_values('id').reset_index(drop=True)

print(c)

answered Sep 19, 2018 at 4:55

niraj

18.2k4 gold badges36 silver badges50 bronze badges

Comments

Khalil Al Hooti · Accepted Answer · 2018-09-19 04:24:50Z

0

Try this

c = pd.concat([a, b], axis=0).sort_values('letter').drop_duplicates('id', keep='first').sort_values('id')

c.reset_index(drop=True, inplace=True)

print(c)

    id  letter
0   1   A
1   2   b
2   3   C
3   4   D

edited Sep 19, 2018 at 4:24

answered Sep 19, 2018 at 4:17

Khalil Al Hooti

4,6065 gold badges28 silver badges51 bronze badges

Collectives™ on Stack Overflow

Merge pandas dataframe with overwrite of columns

3 Answers 3

`combine_first`

`merge` with `groupby`

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

combine_first

merge with groupby

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

`combine_first`

`merge` with `groupby`