Pandas GroupBy Two Text Columns And Return The Max Rows Based On Counts
<p>I'm trying to figure out the max <code>(First_Word, Group)</code> pairs </p>

<pre><code>import pandas as pd

df = pd.DataFrame({'First_Word': ['apple', 'apple', 'orange', 'apple', 'pear'],
           'Group': ['apple bins', 'apple trees', 'orange juice', 'apple trees', 'pear tree'],
           'Text': ['where to buy apple bins', 'i see an apple tree', 'i like orange juice',
                'apple fell out of the tree', 'partrige in a pear tree']},
          columns=['First_Word', 'Group', 'Text'])

  First_Word         Group                        Text
0      apple    apple bins     where to buy apple bins
1      apple   apple trees         i see an apple tree
2     orange  orange juice         i like orange juice
3      apple   apple trees  apple fell out of the tree
4       pear     pear tree     partrige in a pear tree
</code></pre>

<p>Then I do a <code>groupby</code>:</p>

<pre><code>grouped = df.groupby(['First_Word', 'Group']).count()
                         Text
First_Word Group             
apple      apple bins       1
           apple trees      2
orange     orange juice     1
pear       pear tree        1
</code></pre>

<p>And I now want to filter it down to only unique index rows that have the max <code>Text</code> counts. Below you'll notice <code>apple bins</code> was removed because <code>apple trees</code> has the max value.</p>

<pre><code>                         Text
First_Word Group             
apple      apple trees      2
orange     orange juice     1
pear       pear tree        1
</code></pre>

<p>This <a href="https://stackoverflow.com/questions/15707746/python-how-can-i-get-rows-which-have-the-max-value-of-the-group-to-which-they">max value of group</a> question is similar but when I try something like this:</p>

<pre><code>df.groupby(["First_Word", "Group"]).count().apply(lambda t: t[t['Text']==t['Text'].max()])
</code></pre>

<p>I get an error: <code>KeyError: ('Text', 'occurred at index Text')</code>. If I add <code>axis=1</code> to the <code>apply</code> I get <code>IndexError: ('index out of bounds', 'occurred at index (apple, apple bins)')</code></p>