whytheq whytheq - 2 months ago 7
Python Question

Complex pivot and resample

I'm not sure where to start with this so apologies for my lack of an attempt.

This is the initial shape of my data:

df = pd.DataFrame({
'Year-Mth': ['1900-01'
,'1901-02'
,'1903-02'
,'1903-03'
,'1903-04'
,'1911-08'
,'1911-09'],
'Category': ['A','A','B','B','B','B','B'],
'SubCategory': ['X','Y','Y','Y','Z','Q','Y'],
'counter': [1,1,1,1,1,1,1]
})

df


This is the result I'd like to get to - the Mth-Year in the below has been resampled to 4 year buckets:

enter image description here

If possible I'd like to do this via a process that makes 'Year-Mth' resamplable - so I can easily switch to different buckets.

Answer
cols = [df.SubCategory, pd.to_datetime(df['Year-Mth']), df.Category]
df1 = df.set_index(cols).counter

df1.unstack('Year-Mth').T.resample('60M', how='sum').stack(0).swaplevel(0, 1).sort_index().fillna('')

enter image description here