Pandas groupby datetime, getting the count and price
<p>I'm trying to use pandas to group subscribers by subscription type for a given day and get the average price of a subscription type on that day. The data I have resembles:</p>

<pre><code>Sub_Date             Sub_Type    Price 
2011-03-31 00:00:00  12 Month    331.00
2012-04-16 00:00:00  12 Month    334.70
2013-08-06 00:00:00  12 Month    344.34
2014-08-21 00:00:00  12 Month    362.53
2015-08-31 00:00:00  6 Month     289.47
2016-09-03 00:00:00  6 Month     245.57
2013-04-10 00:00:00  4 Month     148.79
2014-03-13 00:00:00  12 Month    348.46
2015-03-15 00:00:00  12 Month    316.86
2011-02-09 00:00:00  12 Month    333.25
2012-03-09 00:00:00  12 Month    333.88
...
2013-04-03 00:00:00  12 Month    318.34
2014-04-15 00:00:00  12 Month    350.73
2015-04-19 00:00:00  6 Month     291.63
2016-04-19 00:00:00  6 Month     247.35
2011-02-14 00:00:00  12 Month    333.25
2012-05-23 00:00:00  12 Month    317.77
2013-05-28 00:00:00  12 Month    328.16
2014-05-31 00:00:00  12 Month    360.02
2011-07-11 00:00:00  12 Month    335.00
...
</code></pre>

<p>I'm looking to get something that resembles:</p>

<pre><code>Sub_Date             Sub_type    Quantity  Price  
2011-03-31 00:00:00  3 Month     2         125.00
                     4 Month     0         0.00     # Promo not available this month
                     6 Month     1         250.78       
                     12 Month    2         334.70
2011-04-01 00:00:00  3 Month     2         125.00
                     4 Month     2         145.00     
                     6 Month     0         250.78       
                     12 Month    0         334.70
2013-04-02 00:00:00  3 Month     1         125.00
                     4 Month     3         145.00    
                     6 Month     0         250.78       
                     12 Month    1         334.70
...
2015-06-23 00:00:00  3 Month     4         135.12
                     4 Month     0         0.00     # Promo not available this month
                     6 Month     0         272.71       
                     12 Month    3         354.12
...
</code></pre>

<p>I'm only able to get the total number of <code>Sub_Type</code>s for a given date. </p>

<pre><code>df.Sub_Date.groupby([df.Sub_Date.values.astype('datetime64[D]')]).size()
</code></pre>

<p>This is somewhat of a good start, but not exactly what is needed. I've had a look at the <a href="http://pandas.pydata.org/pandas-docs/stable/groupby.html" rel="nofollow">groupby</a> documentation on the pandas site but I can't get the output I desire.</p>

<p>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.aggregate.html" rel="nofollow"><code>aggregate</code></a> by <code>mean</code> and <code>size</code> and then add missing values by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.unstack.html" rel="nofollow"><code>unstack</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.stack.html" rel="nofollow"><code>stack</code></a>.</p>

<p>Also if need change order of level <code>Sub_Type</code>, use <a href="http://pandas.pydata.org/pandas-docs/stable/categorical.html#sorting-and-order" rel="nofollow">ordered categorical</a>.</p>

<pre><code>#generating all months ('1 Month','2 Month'...'12 Month')
cat = [str(x) + ' Month' for x in range(1,13)]
df.Sub_Type = df.Sub_Type.astype('category', categories=cat, ordered=True)

df1 = df.Price.groupby([df.Sub_Date.values.astype('datetime64[D]'), df.Sub_Type])
        .agg(['mean', 'size'])
        .rename(columns={'size':'Quantity','mean':'Price'})
        .unstack(fill_value=0)
        .stack()

print (df1)
                      Price  Quantity
           Sub_Type                  
2011-02-09 4 Month     0.00         0
           6 Month     0.00         0
           12 Month  333.25         1
2011-02-14 4 Month     0.00         0
           6 Month     0.00         0
           12 Month  333.25         1
2011-03-31 4 Month     0.00         0
           6 Month     0.00         0
           12 Month  331.00         1
</code></pre>

python pandas