Pandas .pivot_table() reorder the index in chronological order

user_8408050 0
Viewed 470

Im trying to create a pivot table out of the seaborn flights dataset. When I come to make a pivot table for a heat map the index column is ordered alphabetically when I want to order it chronologically from Jan to Dec. Does anyone know how to do this? I have deleted the values in the pivot table to make it look tidier when I copied and pasted from Jupyter notebooks.

import pandas as pd
import seaborn as sns

#Loading dataset
flights = sns.load_dataset('flights')

#Calling dataset
flights

year    month   passengers
0   1949    January 112
1   1949    February    118
2   1949    March   132
3   1949    April   129
4   1949    May 121
... ... ... ...
139 1960    August  606
140 1960    September   508
141 1960    October 461
142 1960    November    390
143 1960    December    432

flights.pivot_table(values='passengers',index='month',columns='year')

year 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960
month                                               
April   Values.....
August  
December    
February    
January 
July    
June    
March   
May 
November    
October 
September
pandas seaborn pivot-table dataframe python
2 Answers

One way would be just reindex and provide the index list as follows:

Code :

import  calendar

month_names = [calendar.month_name[i] for i in range(1,13)]
# ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']

newdf = df.reindex(month_names)
user_21239970

I am on seaborn '0.10.1' and if you look at the data, it is categorical and sorted in the correct order:

flights.month.dtype

CategoricalDtype(categories=['January', 'February', 'March', 'April', 'May', 'June',
                  'July', 'August', 'September', 'October', 'November',
                  'December'],
                 ordered=False)

If you do pivot, it should be sorted correctly. Else you set it as a category:

flights['month'] = pd.Categorical(flights['month'], categories=flights.month.unique())
user_122584590
Related