title: Pandas groupby sort within groups retaining multiple aggregates
body: <p>I want to apply a sort and a limit within the groups returned by <code>groupby</code>, as in <a href="https://stackoverflow.com/questions/27842613/pandas-groupby-sort-within-groups">this question</a>. However, I have multiple aggregates and I want all of the aggregates to be retained in the result. </p>
  
  <p>Here is a simple example:</p>
  
  <pre><code>products = ["A", "B", "C", "D"]
  stores = ["foo", "bar", "baz"]
  n = 30
  
  product_list = [products[i] for i in np.random.randint(0, len(products), n)]
  store_list = [stores[i] for i in np.random.randint(0, len(stores), n)]
  rating_list = np.random.random(n) * 5
  sales_list = np.random.random(n) * 10000
  
  df = pd.DataFrame(
      {'store': store_list, 
       'product': product_list, 
       'sales': sales_list, 
       'rating': rating_list})
  
  df = df[['store', 'product', 'sales', 'rating']]
  
  df[:5]
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/K4aqD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/K4aqD.png" alt="ungrouped dataframe"></a></p>
  
  <p>I want to group by <code>store</code> and <code>product</code>, and both <code>sum</code> and <code>count</code> the <code>sales</code>, while taking the <code>mean</code> of the <code>rating</code>. </p>
  
  <p>This is straightforward:</p>
  
  <pre><code>dfg = df.groupby(['store', 'product']).agg({'sales': ['sum', 'count'], 
                                              'rating': 'mean'})
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/XGvpd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XGvpd.png" alt="grouped"></a></p>
  
  <p>Now, I want to only keep the top two rated rows in each group. I can get this as follows (using a multi-level extension of the somewhat unintuitive [to me] incantations from <a href="https://stackoverflow.com/questions/27842613/pandas-groupby-sort-within-groups">1</a>):</p>
  
  <pre><code>g = dfg[('rating', 'mean')].groupby(
        level=0, group_keys=False).apply(
          lambda x: x.sort_values(ascending=False).head(2))
  g
  </code></pre>
  
  <p>This returns the following <code>Series</code>:</p>
  
  <pre><code>store  product
  bar    B          3.601135
         A          1.867449
  baz    B          2.984196
         D          2.780500
  foo    B          3.767912
         D          3.129346
  Name: (rating, mean), dtype: float64
  </code></pre>
  
  <p>But I've lost the <code>('sales', 'sum')</code> and <code>('sales', 'count')</code> columns.</p>
  
  <p>I suspect I need to extract <code>g.index</code> and use it in some way but have not been able to get this sorted (pun intended).</p>
  
  <p>EDIT: The answers below manage to give the groups that I'm looking for, but what I'd really like is a stable sort where not only do I get the top N mean ratings within each group, but the groups themselves are sorted so that the first group has the highest rating, etc. To some extent this is just icing on the cake as I have the values I want now and would just like the report to be prettier.</p>