CODEWITHSUNDEEP
pythonpandasdataframe
codekitty
codekitty

Reputation: 1438

pandas nlargest is returning more than n rows

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)

Upvotes: 1

Views: 1649

Answers (2)

tmthydvnprt
tmthydvnprt

Reputation: 10748

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

Upvotes: 0

piRSquared
piRSquared

Reputation: 294318

To start, this will get the job done:

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

Upvotes: 2

Related Questions