pythonpandas

How can I get Rows which have the max value of the group to which they belong?


I reword my question. I'm searching solution for the following problem:

I have a dataFrame like:

   Sp   Mt   Value  count
4  MM2  S4   bg     10
5  MM2  S4   dgd    1
6  MM4  S2   rd     2
7  MM4  S2   cb     8
8  MM4  S2   uyi    8

My objective is to get ALL the rows where count equal max in each group e.g. :

MM4  S4   bg     10
MM4  S2   cb     8
MM4  S2   uyi    8

I group by ['Sp','Mt']

Somebody knows how can I do it in pandas or in python?


Solution

  • >>> print d
         Sp  Mt Value  Count
    ID                      
    4   MM2  S4    bg     10
    5   MM2  S4   dgd      1
    6   MM4  S2    rd      2
    7   MM4  S2    cb      8
    8   MM4  S2   uyi      8
    
    >>> d.groupby('Sp').apply(lambda t: t[t.Count==t.Count.max()])
             Sp  Mt Value  Count
    Sp  ID                      
    MM2 4   MM2  S4    bg     10
    MM4 7   MM4  S2    cb      8
        8   MM4  S2   uyi      8