Comprehensive rotation and re-selection

Question

I'm not sure where to start, so I apologize for not trying.

This is the original form 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 would like to get - the Mth-Year in the example below was re-mapped to 4-year-old buckets:

If possible, I would like to do this using a process that allows you to reuse "Year-Mth", so I can easily switch to different buckets.

Answer 1

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('')

Answer 2

Here is my attempt:

df['Year'] = pd.cut(df['Year-Mth'].str[:4].astype(int), 
                    bins=np.arange(1900, 1920, 5), right=False)
df.pivot_table(index=['SubCategory', 'Year'], columns='Category', 
               values='counter', aggfunc='sum').dropna(how='all').fillna(0)
Out: 
Category                    A    B
SubCategory Year                  
Q           [1910, 1915)  0.0  1.0
X           [1900, 1905)  1.0  0.0
Y           [1900, 1905)  1.0  2.0
            [1910, 1915)  0.0  1.0
Z           [1900, 1905)  0.0  1.0

, pandas ( numpy) , . , /. - :

df['Year'] = pd.to_datetime(df['Year-Mth']).dt.year
df['Year'] = pd.cut(df['Year'], bins=np.arange(df['Year'].min(), 
                    df['Year'].max() + 5, 5), right=False)

, Excel.