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
What I want to do is to resample the data on a daily basis such that the monthly values show up for every day in the month:
0 1 ... 98 99
...
2022-03-01 0.322032 0.205307 ... 0.975128 0.673460
2022-03-02 0.322032 0.205307 ... 0.975128 0.673460
2022-03-03 0.322032 0.205307 ... 0.975128 0.673460
...
2022-06-29 0.221407 0.555711 ... 0.745158 0.648246
2022-06-30 0.221407 0.555711 ... 0.745158 0.648246
I tried to retrieve the month from the first index
df.index.get_level_values('month_begin'),
but then I don't know how to tell that it should resample it on a daily basis. Does anybody know how to do this? I would very much appreciate your help.
Use:
Output for first 3 columns: