I have a DatetimeIndex in my pandas dataframe in quarterly frequency, formatted to the beginning of quarters (so 1 jan, 1 Apr and so on). I want to reformat it to the end of quarters, (31 Mar, 30 Jun etc).
I figure the easiest method would be to shift the entire index by 3 months and subtract 1 day. The subtraction part is OK, e.g. I can do this to shift the entire index back a day
import datetime
from dateutil.relativedelta import *
df.index - datetime.timedelta(days=1)
The problem is how to shift ahead 3 months. timedelta works in days and weeks, so is not appropriate. I read that relativedelta does just this, but it only works for datetime object, not datetimeindex. For instance, the following works
df.index[0] + relativedelta(months=+3)
but not
df.index + relativedelta(months=+3)
I guess I can loop over each element of df.index but surely there is an elegant solution that eludes me?
