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

Shubham Sharma · Accepted Answer · 2021-02-27 16:16:54Z

4

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

answered Feb 27, 2021 at 16:16

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

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

Comments

data.dude · Accepted Answer · 2021-02-27 17:37:39Z

Just to put the previous answer into perspective. Shubham Sharma's 'magic' line worked for me in the following context:

Convert the Date column to pandas.Timestamp object using the appropriate input format, so as to be able to apply the normalize() function to it - if it is not in this format, an exception will be raised.
Set the Date column as index.
Use Shubham Sharma's 'magic' line from the previous answer.

The code might look something like this:

import pandas as pd

df1 = pd.DataFrame([['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]], columns=['Date', 'A', 'B'])

df1['Date'] = pd.to_datetime(df1['Date'].astype(str), format='%Y-%m-%d %H:%M:%S')
df1.set_index('Date', inplace=True)

df2 = pd.DataFrame([['2019-08-25', 'X'], ['2019-08-26', 'Y'], ['2019-08-27', 'Z']], columns=['Date', 'A'])
df2['Date'] = pd.to_datetime(df2['Date'].astype(str), format='%Y-%m-%d %H:%M:%S')
df2.set_index('Date', inplace=True)

# Now comes Shubham Sharma's magic line
df3 = df1.assign(key=df1.index.normalize()).merge(df2, left_on='key', right_index=True, how='left').drop('key', 1)

# Set column names (except the index) to A, B and C
df3.columns = ['A', 'B', 'C']

Collectives™ on Stack Overflow

Merge two dataframes with different Date Time Indexes

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related