PANDAS - Resample monthly time series to hourly

Question

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?

score 1 · Accepted Answer · answered Jun 09 '22 at 17:59

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]

PANDAS - Resample monthly time series to hourly

1 Answers1