Resampling pandas columns datetime

Question

(I think) I have a dataset with the columns representing datetime intervals

Columns were transformed in datetime with:

    for col in df.columns:
        df.rename({col: pd.to_datetime(col, infer_datetime_format=True)}, inplace=True)

Then, I need to resample the columns (year and month '2001-01') into quarters using mean

I tried

df = df.resample('1q', how='mean', axis=1)

The DataFrame also has a multindex set ['RegionName', 'County']

But I get the error:

Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'

Is the problem in the to_datetime function or in the wrong sampling?

Answer 1

(I think) you are renaming each column head rather than making the entire columns object a DatetimeIndex

Try this instead:

df.columns = pd.to_datetime(df.columns)

Then run your resample

---

note:
I'd do it with period after converting to DatetimeIndex. That way, you get the period in your column header rather than an end date of the quarter.

df.groupby(df.columns.to_period('Q'), axis=1).mean()

demo

df = pd.DataFrame(np.arange(12).reshape(2, -1),
                  columns=['2011-01-31', '2011-02-28', '2011-03-31',
                           '2011-04-30', '2011-05-31', '2011-06-30'])

df.columns = pd.to_datetime(df.columns)

print(df.groupby(df.columns.to_period('Q'), axis=1).mean())

   2011Q1  2011Q2
0       1       4
1       7      10