For every row with the same name, find the 5 lowest number Pandas Python

user_17922809 0
Viewed 44

My data frame looks like this

Location  week  Number

Austria   1     154
Austria   2     140
Belgium   1     139
Bulgaria  2     110 
Bulgaria  1     164

the solution should look like this

Location  week  Number

Austria   3     100
Austria   2     101
Austria   1     102

Bulgaria  2     100
Bulgaria  3     101
Bulgaria  1     102

this means that I need to display

  1. Column 1 : I need to group the countries by name
  2. Column 2 : Week (every country has 53 weeks assigned to them)
  3. Column 3 : Show the numbers that occured in each of 53 weeks in an ascending order

I can not get my head around this

pandas dataframe python-3.x
2 Answers

Sort the rows in the order your like (here by Location and Number) and take the first 5 rows per group with groupby+head:

df.sort_values(by=['Location', 'Number']).groupby('Location').head(5)

output:

   Location  week  Number
0   Austria     3     100
1   Austria     2     101
2   Austria     1     102
3  Bulgaria     2     100
4  Bulgaria     3     101
5  Bulgaria     1     102
user_163434640

another way using .cumcount() and .loc

con = df.sort_values('Number',ascending=True).groupby('Location').cumcount()


df.loc[con.lt(5)]
user_93751020
Related