Merge two dataframes with different Date Time Indexes

Question

I have two dataframes with time index but with different scale. The first has data every 30 mins every day and the other has just one data per day. I want to fill the first dataframe with the values of the second and keep the shape of the first dataframe.

first:

2019-08-26 13:00:00 a 1
2019-08-26 13:30:00 b 2
2019-08-26 14:00:00 c 3
2019-08-26 14:30:00 d 4
2019-08-26 15:00:00 e 5

second:

2019-08-25 X
2019-08-26 Y
2019-08-27 Z

wanted result:

2019-08-26 13:00:00 a 1 Y
2019-08-26 13:30:00 b 2 Y
2019-08-26 14:00:00 c 3 Y
2019-08-26 14:30:00 d 4 Y
2019-08-26 15:00:00 e 5 Y

Answer 1

You can create a temporary merge key in df1 by normalising the index of df1 then you should be able to merge df1 with the other dataframe df2 based on this merge key:

df1.assign(key=df1.index.normalize())\
   .merge(df2, left_on='key', right_index=True, how='left').drop('key', 1)

---

                     A  B  C
2019-08-26 13:00:00  a  1  Y
2019-08-26 13:30:00  b  2  Y
2019-08-26 14:00:00  c  3  Y
2019-08-26 14:30:00  d  4  Y
2019-08-26 15:00:00  e  5  Y