whytheq whytheq - 7 months ago 31
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