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

CodePudding user response：

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

CodePudding user response：

another way using .cumcount() and .loc

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


df.loc[con.lt(5)]