How can I average ACROSS groups in python-pandas?
<p>I have a dataset like this:</p>

<pre><code>Participant   Type   Rating 
1              A       6
1              A       5
1              B       4
1              B       3
2              A       9
2              A       8
2              B       7
2              B       6
</code></pre>

<p>I want obtain this:</p>

<pre><code>Type   MeanRating
A        mean(6,9)
A        mean(5,8)
B        mean(4,7)
B        mean(3,6)
</code></pre>

<p>So, for each type, I want the mean of the higher value in each group, then the mean of the second higher value in each group, etc.</p>

<p>I can't think up a proper way to do this with python pandas, since the means seem to apply always within groups, but not across them.</p>

<p>First use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.DataFrameGroupBy.rank.html" rel="nofollow"><code>groupby.rank</code></a> to create a column that allows you to align the highest values, second highest values, etc.  Then perform another <code>groupby</code> using the newly created column to compute the means:</p>

<pre><code># Get the grouping column.
df['Grouper'] = df.groupby(['Type', 'Participant']).rank(method='first', ascending=False)

# Perform the groupby and format the result.
result = df.groupby(['Type', 'Grouper'])['Rating'].mean().rename('MeanRating')
result = result.reset_index(level=1, drop=True).reset_index()
</code></pre>

<p>The resulting output:</p>

<pre><code>  Type  MeanRating
0    A         7.5
1    A         6.5
2    B         5.5
3    B         4.5
</code></pre>

<p>I used the <code>method='first'</code> parameter of <code>groupby.rank</code> to handle the case of duplicate ratings within a <code>['Type', 'Participant']</code> group.  You can omit it if this is not a possibility within your dataset, but it won't change the output if you leave it and there are no duplicates.</p>

python pandas