CODEWITHSUNDEEP
pythonpandasdataframemerge
dnrhead
dnrhead

Reputation: 84

Merge two dataframes on DateTimeIndex ignoring the year

I have two dataframes, one is a series of measurements,

A = ID                 Value
2020-01-01 00:00:00    0.2
2020-01-01 01:00:00    0.2
                      ...
2020-12-31 22:00:00    0.6
2020-12-31 23:00:00    0.5
2021-01-01 00:00:00    0.4
2021-01-01 01:00:00    0.3
                      ...
2021-12-31 22:00:00    0.3
2021-12-31 23:00:00    0.2

The other one is a scaling value, which is the same for every year, but available with a DateTimeIndex of a specific year.

B = ID                 Value
2020-01-01 00:00:00    2
2020-01-01 01:00:00    3
                      ...
2020-12-31 22:00:00    10
2020-12-31 23:00:00    11

Is there a way to merge the two dataset, negelecting the year of the second dataset to get a dataframe like this:

A = ID                 ValueA  ValueB
2020-01-01 00:00:00    0.2     2
2020-01-01 01:00:00    0.2     3
                      ...
2020-12-31 22:00:00    0.6     10
2020-12-31 23:00:00    0.5     11
2021-01-01 00:00:00    0.4     2
2021-01-01 01:00:00    0.3     3
                      ...
2021-12-31 22:00:00    0.3     10
2021-12-31 23:00:00    0.2     11

Upvotes: 2

Views: 307

Answers (1)

jezrael
jezrael

Reputation: 862681

Use DataFrame.merge with left join and helper column defined by DatetimeIndex.strftime:

df = (df1.assign(time=df.index.strftime('%m-%d %H:%M:%S'))
         .merge(df2.assign(time=df2.index.strftime('%m-%d %H:%M:%S')), 
                    on='time', how='left', suffixes=('A','B'))
         .drop('time', axis=1))

Upvotes: 4

Related Questions