Complex pivot and resample
<p>I'm not sure where to start with this so apologies for my lack of an attempt.</p>

<p>This is the initial shape of my data:</p>

<pre><code>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
</code></pre>

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

<p><a href="http://i.stack.imgur.com/sWkYR.png" rel="nofollow"><img src="http://i.stack.imgur.com/sWkYR.png" alt="enter image description here"></a></p>

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

<p>Here's my attempt:</p>

<pre><code>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
</code></pre>

<p>The year column is not parameterized as pandas (or numpy) does not offer a cut option with step size, as far as I know. But I think it can be done with a little arithmetic on minimums/maximums. Something like:</p>

<pre><code>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)
</code></pre>

<p>This wouldn't create nice bins like Excel does, though. </p>

python pandas