Getting the n largest values for groups

Question

I am looking to isolate the top 2 values per group for the following data.

Brand | Product | Rank
  A   |    P1   |   1000
      |    P2   |   1210
      |    P3   |   2000
      |    P4   |   600
      |    P5   |   756
      |    P6   |   867
  B   |    P1   |   549
      |    P2   |   1572
      |    P3   |   3490
      |    P4   |   2341
      |    P5   |   431
      |    P6   |   321
  C   |    P1   |   421
      |    P2   |   121
      |    P3   |   805
      |    P4   |   1202
      |    P5   |   4032
      |    P6   |   432

I want to be able to the top 2 values for each group (A, B, C).

Top_Products = df.nlargest(2, 'Rank')

This however only isolates the top 2 products.

Is there a way to get the top 2 products per Brand.

Desired Output:

Brand | Product | Rank
  A   |    P3   |   2000
      |    P2   |   1210
  B   |    P3   |   3490
      |    P4   |   2341
  C   |    P5   |   4032
      |    P4   |   1202

Thanks!

https://stackoverflow.com/questions/49983957/not-getting-top5-values-for-each-month-using-grouper-and-groupby-in-pandas/49984025#comment86988818_49984025 — BENY, Apr 25 '18 at 18:10

score 12 · Accepted Answer · answered Apr 25 '18 at 18:09

12

This should do the trick:

df.groupby('Brand').apply(lambda x: x.nlargest(2, 'Rank')).reset_index(drop=True)  

  Brand Product  Rank
0     A      P3  2000
1     A      P2  1210
2     B      P3  3490
3     B      P4  2341
4     C      P5  4032
5     C      P4  1202

answered Apr 25 '18 at 18:09

sacuL

49,704
8
81
106

If you think your solution is unique enough or more *up-to-date*, you should add it to the duplicate linked to in the question. – wwii Apr 25 '18 at 18:13

Getting the n largest values for groups

1 Answers1