Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting the n largest values for groups [duplicate]

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!

like image 376
Dys_Lexi_A Avatar asked Apr 25 '18 18:04

Dys_Lexi_A


1 Answers

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
like image 78
sacuL Avatar answered Oct 22 '22 03:10

sacuL