Suppose I have a multi-index Pandas data frame with two index levels: month_begin and month_end
import pandas as pd
multi_index = pd.MultiIndex.from_tuples([("2022-03-01", "2022-03-31"),
("2022-04-01", "2022-04-30"),
("2022-05-01", "2022-05-31"),
("2022-06-01", "2022-06-30")])
multi_index.names = ['month_begin', 'month_end']
df = pd.DataFrame(np.random.rand(4,100), index=multi_index)
df
0 1 ... 98 99
month_begin month_end ...
2022-03-01 2022-03-31 0.322032 0.205307 ... 0.975128 0.673460
2022-04-01 2022-04-30 0.113813 0.278981 ... 0.951049 0.090765
2022-05-01 2022-05-31 0.777918 0.842734 ... 0.667831 0.274189
2022-06-01 2022-06-30 0.221407 0.555711 ... 0.745158 0.648246
I would like to resample the data to have the value in a month at every hour in the respective month:
0 1 ... 98 99
...
2022-03-01 00:00 0.322032 0.205307 ... 0.975128 0.673460
2022-03-01 01:00 0.322032 0.205307 ... 0.975128 0.673460
2022-03-01 02:00 0.322032 0.205307 ... 0.975128 0.673460
...
2022-06-30 22:00 0.221407 0.555711 ... 0.745158 0.648246
2022-06-30 23:00 0.221407 0.555711 ... 0.745158 0.648246
I know I can use resample(), but I am struggeling with how to do this. Does anybody have a clue?
IIUC, try this using list_comprehension and explode with pd.date_range:
df['Date'] = [pd.date_range(s, e, freq='H') for s, e in df.index]
df_out = df.explode('Date').set_index('Date')
Output:
0 1 ... 98 99
Date ...
2022-03-01 00:00:00 0.396311 0.138263 ... 0.637640 0.106366
2022-03-01 01:00:00 0.396311 0.138263 ... 0.637640 0.106366
2022-03-01 02:00:00 0.396311 0.138263 ... 0.637640 0.106366
2022-03-01 03:00:00 0.396311 0.138263 ... 0.637640 0.106366
2022-03-01 04:00:00 0.396311 0.138263 ... 0.637640 0.106366
... ... ... ... ... ...
2022-06-29 20:00:00 0.129921 0.654878 ... 0.619212 0.142297
2022-06-29 21:00:00 0.129921 0.654878 ... 0.619212 0.142297
2022-06-29 22:00:00 0.129921 0.654878 ... 0.619212 0.142297
2022-06-29 23:00:00 0.129921 0.654878 ... 0.619212 0.142297
2022-06-30 00:00:00 0.129921 0.654878 ... 0.619212 0.142297
[2836 rows x 100 columns]