How to get distinct count of keys along with other aggregations in pandas
<p>My data frame (DF) looks like this </p>

<pre><code>Customer_number Store_number   year   month   last_buying_date1  amount     
     1             20          2014    10      2015-10-07        100
     1             20          2014    10      2015-10-09        200
     2             20          2014    10      2015-10-20        100
     2             10          2014    10      2015-10-13        500
</code></pre>

<p>and I want to get an output like this </p>

<pre><code> year   month  sum_purchase count_purchases distinct customers 
 2014    10       900          4                  3
</code></pre>

<p>How do I get an output like this using Agg and group by . I am using a 2 step group by currently  but struggling to get the distinct customers . Here's my approach </p>

<pre><code>#### Step 1 - Aggregating everything at customer_number, store_number level
aggregations = {
    'amount': 'sum',       
    'last_buying_date1': 'count',
    }
grouped_at_Cust = DF.groupby(['customer_number','store_number','month','year']).agg(aggregations).reset_index()
grouped_at_Cust.columns =   ['customer_number','store_number','month','year','total_purchase','num_purchase']


#### Step2 - Aggregating at year month level 


aggregations = {
    'total_purchase': 'sum',       
    'num_purchase': 'sum',
     size
    }

Monthly_customers =       grouped_at_Cust.groupby(['year','month']).agg(aggregations).reset_index()
Monthly_customers.colums = ['year','month','sum_purchase','count_purchase','distinct_customers']
</code></pre>

<p>My struggle is in the 2nd step. How do i include size in the 2nd aggregation step ? </p>

<p>You could use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.DataFrameGroupBy.agg.html" rel="nofollow"><code>groupby.agg</code></a> and supplying the function <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.SeriesGroupBy.nunique.html" rel="nofollow"><code>nunique</code></a> to return number of unique Customer Ids in the group.</p>

<pre><code>df_grp = df.groupby(['year', 'month'], as_index=False)                                 \
           .agg({'purchase_amt':['sum','count'], 'Customer_number':['nunique']})

df_grp.columns = map('_'.join, df_grp.columns.values)

df_grp
</code></pre>

<p><a href="http://i.stack.imgur.com/TlazM.png" rel="nofollow"><img src="http://i.stack.imgur.com/TlazM.png" alt="Image"></a></p>

<hr>

<p>Incase, you are trying to group them differently (omitting certain column) when performing <code>groupby</code> operation:</p>

<pre><code>df_grp_1 = df.groupby(['year', 'month']).agg({'purchase_amt':['sum','count']})       

df_grp_2 = df.groupby(['Store_number', 'month', 'year'])['Customer_number'].agg('nunique')
</code></pre>

<p>Take the first level of the multiindex columns which contains the <code>agg</code> operation performed:</p>

<pre><code>df_grp_1.columns = df_grp_1.columns.get_level_values(1)
</code></pre>

<p>Merge them back on the intersection of the columns used to group them:</p>

<pre><code>df_grp = df_grp_1.reset_index().merge(df_grp_2.reset_index().drop(['Store_number'], 
                                      axis=1), on=['year', 'month'], how='outer')
</code></pre>

<p>Rename the columns to new ones:</p>

<pre><code>d = {'sum': 'sum_purchase', 'count': 'count_purchase', 'nunique': 'distinct_customers'}  

df_grp.columns = [d.get(x, x) for x in df_grp.columns]
df_grp
</code></pre>

<p><a href="http://i.stack.imgur.com/jjNSR.png" rel="nofollow"><img src="http://i.stack.imgur.com/jjNSR.png" alt="Image"></a></p>

python pandas group-by aggregate