Re-order DataFrame based on month (not alphabet)

Question

For a DataFrame, let's call it weather1:

        Temp
Month   
Apr     61
Jan     32
Jul     69
Oct     43

I would like to sort Month based on the actual calendar month order, and not alphabetically.

I can do the following:

ordered= ['Jan','Apr','Jul','Oct']
df.reindex(ordered)

However I am looking for a more dynamic way to do this, perhaps using "date part" functions.

Thank you in advance.

you can refer to this answer https://stackoverflow.com/a/39078261/4662041 — Sheshnath, Nov 28 '17 at 03:32
@Sheshnath I don't seem to be able to figure out how to use that example to apply to mine, with only having %b from date in the index. Also I get an error 'Index' object has no attribute 'strftime' — user8834780, Nov 28 '17 at 03:48

jlandercy · Accepted Answer · 2017-11-28T15:09:04.937

Having formatted your data in order to make a MCVE:

import pandas as pd
data = {
    'smonth': ['Apr', 'Jan', 'Jul', 'Oct']
   ,'temp': [61, 32, 69, 43]
}
df = pd.DataFrame(data)

We have the following DataFrame:

  smonth  temp
0    Apr    61
1    Jan    32
2    Jul    69
3    Oct    43

Now we convert your String Month (using %b formatter, see list) column into a Date, then into an Index and we finally extract Month field:

df['month'] = pd.DatetimeIndex(pd.to_datetime(df['smonth'], format='%b')).month

Then reindex and sort:

df = df.set_index('month').sort_index()

It is done:

      smonth  temp
month             
1        Jan    32
4        Apr    61
7        Jul    69
10       Oct    43

Update

If I explicitly reproduce your input, using:

df2 = pd.DataFrame(
    data['temp']
   ,index=pd.Index(data['smonth'], name='Month')
   ,columns=['Temp']
)

Then it reduces to:

df2.index = pd.to_datetime(df2.index, format='%b').month
df2.sort_index(inplace=True)

And returns:

       Temp
Month      
1        32
4        61
7        69
10       43

1 Answers1