CODEWITHSUNDEEP
pythonpandas
misantroop
misantroop

Reputation: 2554

Trouble upsampling daily data to intraday df

I have a multiindex data set where I'm creating a column on daily data that that is based on intraday data. This works fine and I have the daily results. However when I want to add the result to the original df, the column is NaN.

As I understand this is because the original has DateTimeIndex as date and time and the daily has a DateTimeIndex with just the date, so they never overlap which means I'm unable to merge. Upsampling is also not an option (ValueError: Upsampling from level= or on= selection is not supported, use .set_index(...) to explicitly set index to datetime-like). How would I go about this by using set_index or correspond each DateTime from daily with the intraday date.

Example of daily:

                              Col1
DateTime         Name
2019-12-12       ABC          1
2019-12-12       DDD          5
2019-12-13       ABC          2
2019-12-13       DDD          6
2019-12-13       WWW          0

Desired result in intraday:

                                       Col1
DateTime                  Name
2019-12-12 09:10:00       DDD          5
2019-12-12 09:15:00       DDD          5
2019-12-13 09:10:00       DDD          6
2019-12-13 09:15:00       DDD          6
2019-12-13 09:20:00       DDD          6

Upvotes: 0

Views: 207

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150735

IIUC:

idx = pd.MultiIndex.from_arrays(
          [df2.index.get_level_values(level=0).normalize(),
           df2.index.get_level_values(level=1)
          ])

df2['Col1_'] = df1.loc[idx, 'Col1'].values

Output:

                          Col1  Col1_
DateTime            Name             
2019-12-12 09:10:00 DDD      5      5
2019-12-12 09:15:00 DDD      5      5
2019-12-13 09:10:00 DDD      6      6
2019-12-13 09:15:00 DDD      6      6
2019-12-13 09:20:00 DDD      6      6

Upvotes: 1

Related Questions