Merging two datetime-indexed pandas.dataframe objects

Question

I have two datetime-indexed pandas.dataframe objects:

object1:

 DateTime                     Bid.ESU6  Ask.ESU6
 2016-06-28 08:30:00          207000    207025   
 2016-06-28 08:30:11          206975    207000   
 2016-06-28 08:30:21          207000    207050

object2:

 DateTime                     Bid.YMU6  Ask.YMU6
 2016-06-28 08:30:00          12793     12795
 2016-06-28 08:30:02          12793     12795
 2016-06-28 08:30:05          12794     12796
 2016-06-28 08:30:08          12793     12794
 2016-06-28 08:30:10          12792     12794
 2016-06-28 08:30:15          12792     12795
 2016-06-28 08:30:22          12794     12797

I want to merge these two dataframe objects, but only incorporate the dates from object1. I want to make new columns in object1 that correspond to the closest datetime from object2. In the example above, I would expect to output the following:

 DateTime                     Bid.ESU6  Ask.ESU6  Bid.YMU6  Ask.YMU6
 2016-06-28 08:30:00          207000    207025    12793     12795
 2016-06-28 08:30:11          206975    207000    12792     12794
 2016-06-28 08:30:21          207000    207050    12794     12797

Is there an easy way to do this without having to make a for-loop going through each row from object1?

Thank you in advance for any help!

object1.merge(object2, left_index=True, right_index=True, how='left') should work — EdChum
– EdChum, Commented Jul 5, 2016 at 15:08
I was thinking of doing something similar: object1.join(object2, how='left'), but this gave NaN unless the DateTime was exactly equal in the two objects. I want it to return the elements with the nearest datetimes, rather than the exact datetime. — sos
– sos, Commented Jul 5, 2016 at 15:14

root · Accepted Answer · 2016-07-05 16:15:49Z

3

You can use reindex on object2 with method='nearest' prior to doing the join:

object1 = object1.join(object2.reindex(object1.index, method='nearest'))

The resulting output:

                     Bid.ESU6  Ask.ESU6  Bid.YMU6  Ask.YMU6
DateTime                                                   
2016-06-28 08:30:00    207000    207025     12793     12795
2016-06-28 08:30:11    206975    207000     12792     12794
2016-06-28 08:30:21    207000    207050     12794     12797

answered Jul 5, 2016 at 16:15

root

34.1k6 gold badges77 silver badges89 bronze badges

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

Collectives™ on Stack Overflow

Merging two datetime-indexed pandas.dataframe objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related