Merging data frame with overlapping columns

Question

I have following DataFrames:

    stores = [['AA', 12, 'Red'], ['BB', 13, 'Red'], ['BB', 14, 'Red'], ['BB', 15, 'Red']]
    visits = [['BB', 13, 'Green'], ['BB', 14, 'Blue']]

    stores_df = pd.DataFrame(data=stores, columns=['retailer', 'store', 'color'])
    stores_df.set_index(['retailer', 'store'], inplace=True)

    visits_df = pd.DataFrame(data=visits, columns=['retailer', 'store', 'color'])
    visits_df.set_index(['retailer', 'store'], inplace=True)

                color
retailer store       
BB       13     Green
         14      Blue

               color
retailer store      
AA       12      Red
BB       13      Red
         14      Red
         15      Red

How I can merge them in order to get following result:

               color
retailer store      
AA       12      Red
BB       13      Green
         14      Blue
         15      Red

Colonel Beauvel · Accepted Answer · 2016-07-14 08:55:26Z

3

You can use update:

In [41]: stores_df.update(visits_df)

In [42]: stores_df
Out[42]:
                color
retailer store
AA       12       Red
BB       13     Green
         14      Blue
         15       Red

answered Jul 14, 2016 at 8:55

Colonel Beauvel

31.3k11 gold badges49 silver badges88 bronze badges

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

3 Comments

piRSquared Over a year ago

This is a great answer. update changes inplace and does not have a return value. These are features that can be useful. +1

Colonel Beauvel Over a year ago

I actually prefer much more combine_first. Changing inplace and no return is stabbing you in your back. I am far more keen on a functional paradigm where you do not modify the object and you have a return value: a copy of the object, modified.

piRSquared Over a year ago

True and I agree. But I wasn't aware of an update on dataframes. I'll keep it in mind for potential future use.

piRSquared · Accepted Answer · 2016-07-14 09:01:50Z

2

You want to use combine_first

visits_df.combine_first(stores_df)

answered Jul 14, 2016 at 9:01

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Collectives™ on Stack Overflow

Merging data frame with overlapping columns

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related