Resample with categories in pandas, keep non-numerical columns

Question

I have hourly data, of variable x for 3 types, and Category column, and ds is set as index.

> df

ds                   Category   X
2010-01-01 01:00:00     A       32
2010-01-01 01:00:00     B       13
2010-01-01 01:00:00     C       09
2010-01-01 02:00:00     A       12
2010-01-01 02:00:00     B       62
2010-01-01 02:00:00     C       12

I want to resample it to Week. But if I use df2 = df.resample('W').mean(), it simply drops 'Category' Column.

Possible duplicate of [Pandas: resample timeseries with groupby](https://stackoverflow.com/questions/32012012/pandas-resample-timeseries-with-groupby) — Georgy, May 06 '19 at 09:55

score 8 · Accepted Answer · answered May 06 '19 at 09:03

If need resample per Category column per weeks add groupby, so is using DataFrameGroupBy.resample:

Notice:
For correct working is necessary DatetimeIndex.

df2 = df.groupby('Category').resample('W').mean()
print (df2)
                        X
Category ds              
A        2010-01-03  22.0
B        2010-01-03  37.5
C        2010-01-03  10.5

score 4 · Answer 2 · answered Oct 24 '19 at 17:16

To complete the answer by jezrael, I found it useful to put the content back as a DataFrame instead of a DataFrameGroup, as explained here. So, the answer will be:

df2 = df.groupby('Category').resample('W').mean()

# the inverse of groupby, reset_index
df2 = df2.reset_index()

# set again the timestamp as index
df2 = df2.set_index("ds")

Resample with categories in pandas, keep non-numerical columns

2 Answers2