Setting The Datetime In A Multiindex To Last Day Of The Month

I have a Multiindex Dataframe and would like to change the date level, so that the date of the last value I have for each month is changed to the last day of the month. Any help is

Solution 1:

It may be easier to convert it to a Series first, change the values, and then replace the original index with the new one.

idx = df.index.levels[0]

ser = pd.Series(idx)
last_of_mon = ser.groupby(ser.dt.year * 100 + ser.dt.month).last()

ser = ser.apply(
    lambda x: x + pd.offsets.MonthBegin(1) - pd.offsets.Day(1)
        if x in last_of_mon.values
        else x
)

df.index.set_levels(ser, 0, inplace=True)

Notice that + pd.offsets.MonthBegin(1) - pd.offsets.Day(1) is used to change to the last of the month. If you use + pd.offsets.MonthEnd(1) on a date that is already the last of the month, it changes it to the last of the next month.