Group by and Sort with Pandas Python

Question

I have performed a group by function, and I would like to sort it by month in chronological order, how can I do so? Currently the function is sorting month on alphabetical order:

func = {'Predictions':['count','mean','median']}

table1 = df.groupby(['FLAG','MONTH']).agg(func)

table1

         Predictions
                        count        mean      median
FLAG       MONTH                                     
0          Apr          49812  106.458209   75.325309
           Aug          44514   93.718901   74.485782
           Feb          51583   98.921119   74.199794
           Jan          54837  100.381814   74.682187
           Jul          49873  100.621877   73.233328
           Jun          47950  103.688532   74.150171
           Mar          52816  106.094774   75.104832
           May          49404  106.847784   75.844241
           Oct          41828   94.744952   76.178077
           Sep          41074   96.430351   75.335261
1          Apr          83377  285.631679  144.582569
           Aug          66285  217.619038  127.087037
           Feb          79693  310.919925  180.507922
           Jan          64730  290.113451  137.291571
           Jul         105213  298.337893  146.956319
           Jun          90305  312.484185  136.222903
           Mar          97274  308.013477  165.752471
           May          87927  310.162600  142.350688
           Oct          47064  258.213619   85.445310
           Sep          47337  240.361602   84.597842

Thanks for your help!

jezrael · Accepted Answer · 2017-11-29 11:53:42Z

4

You can use reindex:

#rewrite code for remove MultiIndex in columns
table1 = df.groupby(['FLAG','MONTH'])['Predictions'].agg(['count','mean','median'])

months = ['Jan', 'Feb', 'Mar', 'Apr','May','Jun', 'Jul', 'Aug','Sep', 'Oct', 'Nov', 'Dec']

df = table1.reindex(months, level=1)
print (df)
             count        mean      median
FLAG MONTH                                
0    Jan     54837  100.381814   74.682187
     Feb     51583   98.921119   74.199794
     Mar     52816  106.094774   75.104832
     Apr     49812  106.458209   75.325309
     May     49404  106.847784   75.844241
     Jun     47950  103.688532   74.150171
     Jul     49873  100.621877   73.233328
     Aug     44514   93.718901   74.485782
     Sep     41074   96.430351   75.335261
     Oct     41828   94.744952   76.178077
1    Jan     64730  290.113451  137.291571
     Feb     79693  310.919925  180.507922
     Mar     97274  308.013477  165.752471
     Apr     83377  285.631679  144.582569
     May     87927  310.162600  142.350688
     Jun     90305  312.484185  136.222903
     Jul    105213  298.337893  146.956319
     Aug     66285  217.619038  127.087037
     Sep     47337  240.361602   84.597842
     Oct     47064  258.213619   85.445310

edited Nov 29, 2017 at 11:53

answered Nov 29, 2017 at 11:51

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bharath M Shetty Over a year ago

That was fast. I could'nt copy data properly I was preparping df till now

Charles Over a year ago

Cheers! I'm trying to apply it also to a copy of the same table but without the FLAG variable, and this error comes up:

Charles Over a year ago

NotImplementedError: argument level is not implemented for CategoricalIndex.reindex

Charles Over a year ago

oh ok, found it! Thanks again @jezrael!

thebeancounter · Accepted Answer · 2017-11-29 20:08:29Z

0

As mentioned in this question you can use the following code to get the month to index mapping

import calendar
map = {v: k for k,v in enumerate(calendar.month_abbr)}

than you can use the index mapping to create a new column of the month index with

#create the new index
df["index"] = df["MONTH"].map(lambda x: map[x])
#groupby the new index
table1 = df.groupby(['FLAG','index']).agg(func).reset_index()
#drop the multi index
table1 = table1.reset_index()
#sort by month
table1.sort_values("index", inplace = True)

answered Nov 29, 2017 at 20:08

thebeancounter

4,85911 gold badges72 silver badges121 bronze badges

Collectives™ on Stack Overflow

Group by and Sort with Pandas Python

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related