Pandas merge DataFrames based on index/column combination

Question

I have two DataFrames that I want to merge. I have read about merging on multiple columns, and preserving the index when merging. My problem needs to cater for both, and I am having difficulty figuring out the best way to do this.

The first DataFrame looks like this

and the second looks like this

I want to merge these based on the Date and the ID. In the first DataFrame the Date is the index and the ID is a column; in the second DataFrame both Date and ID are part of a MultiIndex.

Essentially, as a result I want a DataFrame that looks like DataFrame 2 with an additional column for the Events from DataFrame 1.

Mind posting the text for the DataFrames so we don't have to recreate them by hand? — Alex, Jul 19 '16 at 15:22
They are created by reading from MongoDB, and I can't share that data unfortunately — Philip O'Brien, Jul 19 '16 at 18:56

score 1 · Answer 1 · answered Jul 19 '16 at 15:12

1

I'd suggest reseting the index (reset_index) and then merging the DataFrame, as you've read. Then you can set the index (set_index) to reproduce your desired MultiIndex.

answered Jul 19 '16 at 15:12

DrTRD

1,641
1
13
18

Pandas merge DataFrames based on index/column combination

1 Answers1