String collection in pandas best rated dataframe

Question

I have a pandas dataframe that looks like this:

Name Candidates Qualifier Score
AAA   AAA_1       Yes      0
AAA   AAA_2       Yes      10
AAA   AAA_3        No      20
BBB   BBB_1        No      1
BBB   BBB-2       Yes      10
BBB   BBB_3       Yes     50

I want to choose the best two candidates in each "name" with the highest score. How can i do this?

Answer 1

You can sort df by "Name" and "Score", and then groupbyby "Name" and call head(2)to get the first 2 lines for each group:

In [228]:
df.sort(['Name','Score'], ascending=False).groupby('Name').head(2)

Out[228]:
  Name Candidates Qualifier  Score
5  BBB      BBB_3       Yes     50
4  BBB      BBB-2       Yes     10
2  AAA      AAA_3        No     20
1  AAA      AAA_2       Yes     10

Answer 2

, , EdChum answer, , . , sort.

Sorted = df.sort(['Score'], ascending = False).groupby('Name').head(2)
print Sorted.sort(['Candidate'], ascending = True)

  Name   Cand  Score
1  AAA  AAA_2     10
2  AAA  AAA_3     20
4  BBB  BBB_2     10
5  BBB  BBB_3     50