CODEWITHSUNDEEP
pythonpandasdataframemerge
Vasilis Iak
Vasilis Iak

Reputation: 109

Merge two dataframes with different Date Time Indexes

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

Upvotes: 3

Views: 1849

Answers (2)

data.dude
data.dude

Reputation: 646

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

  1. 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.
  2. Set the Date column as index.
  3. 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']

Upvotes: 3

Shubham Sharma
Shubham Sharma

Reputation: 71689

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

Upvotes: 4

Related Questions