pandas nlargest is returning more than n rows

Question

I have a DataFrame that looks like this:

            name      value 
 date   
 2016-05-01 kelly      20  
 2016-05-05 john       12  
 2016-05-05 sarah      25  
 2016-05-05 george     3  
 2016-05-05 tom        40  
 2016-05-07 kara       24  
 2016-05-07 jane       90  
 2016-05-07 sally      39  
 2016-05-07 sam        28

I want to get the top 3 rows (according to value) preferably per date. I'm expecting something like this:

            name      value 
 date   
 2016-05-01 kelly      20  
 2016-05-05 john       12  
 2016-05-05 sarah      25  
 2016-05-05 tom        40  
 2016-05-07 jane       90  
 2016-05-07 sally      39  
 2016-05-07 sam        28

but I'm ok also with this:

            name      value 
 date   
 2016-05-05 tom        40  
 2016-05-07 jane       90  
 2016-05-07 sally      39

I tried df.nlargest(3, 'value') but I get this weird result:

         name        value 
 date       
 2016-05-01 kelly      20  
 2016-05-01 kelly      20  
 2016-05-01 kelly      20  
 2016-05-05 tom        40  
 2016-05-05 tom        40  
 2016-05-05 tom        40  
 2016-05-05 sarah      25  
 2016-05-05 sarah      25  
 2016-05-05 sarah      25  
 2016-05-07 kara       24  
 2016-05-07 kara       24  
 ...
 2016-05-07 sally      39  
 2016-05-07 sally      39  
 2016-05-07 jane       90  
 2016-05-07 jane       90  
 2016-05-07 jane       90

I tried running it day by day:
[df.ix[day].nlargest(3, 'value') for day in df.index.unique()]

but I got the same problem (each name is duplicated 3 times)

score 2 · Accepted Answer · answered Jun 03 '16 at 23:53

2

To start, this will get the job done:

df.sort_values('value', ascending=False).groupby(level=0).head(3).sort_index()

answered Jun 03 '16 at 23:53

piRSquared

285,575
57
475
624

Thanks! so groupby(level=0) + sorting got the results per date just as I needed. I guess nlargest isn't usable in this use case – codekitty Jun 06 '16 at 18:54

score 0 · Answer 2 · edited May 23 '17 at 11:53

Use `[:n]` slice of `sort_values()` result

Use sort_values() in descending mode and take the first n results in a slice, then use sort_index() to keep the days monotonically increasing.

import pandas as pd
import cStringIO

df  = pd.read_table(cStringIO.StringIO('''
 date   name      value 
 2016-05-01 kelly      20  
 2016-05-05 john       12  
 2016-05-05 sarah      25  
 2016-05-05 george     3  
 2016-05-05 tom        40  
 2016-05-07 kara       24  
 2016-05-07 jane       90  
 2016-05-07 sally      39  
 2016-05-07 sam        28 
'''), sep=' *', index_col=0, engine='python')

print 'Original DataFrame:'
print df
print

df_top3 = df.sort_values('value', ascending=False)[:3].sort_index()
print 'Top 3 Largest value DataFrame:'
print df_top3
print

Original DataFrame:
              name  value
date                     
2016-05-01   kelly     20
2016-05-05    john     12
2016-05-05   sarah     25
2016-05-05  george      3
2016-05-05     tom     40
2016-05-07    kara     24
2016-05-07    jane     90
2016-05-07   sally     39
2016-05-07     sam     28

Top 3 Largest value DataFrame:
             name  value
date                    
2016-05-05    tom     40
2016-05-07   jane     90
2016-05-07  sally     39

pandas nlargest is returning more than n rows

2 Answers2

Use [:n] slice of sort_values() result

Use `[:n]` slice of `sort_values()` result