CODEWITHSUNDEEP
pythonpandas
Xenilo
Xenilo

Reputation: 25

sort months in pandas DataFrame

I have a pandas DataFrame, on which I've called pivot_table as follows.

df_pivot = pd.pivot_table(
    df, 
    values='Profit', 
    index=['Year'], 
    columns=['Month'], 
    aggfunc=np.sum
).fillna(0)

This produces the following table, when printed.

Month   April  August  December  February  January    July   March     May  \
Year                                                                         
2018   230.89  143.39    265.33    118.64   374.28  269.03  170.18  180.34   
2019   122.34  302.68    421.52     98.09    72.09    0.00  218.78  143.39   
2020   412.42    0.00      0.00    230.89   323.73    0.00  170.18  252.43

How do I get this to print out the months in chronological, rather than alphabetical order? So, instead of April, August, December,... I want it to print the columns as January, February, March,...

Upvotes: 1

Views: 577

Answers (2)

Eric Truett
Eric Truett

Reputation: 3010

Sort columns in a pandas dataframe to be chronological order rather than alphabetical

import pandas as pd  
import numpy as np  
months = ['February', 'January', 'April', 'May', 'June', 'March', 'July',
          'September', 'October', 'November', 'December', 'August' ] 
#create a new empty pandas dataframe with the months columns out of order: 
df = pd.DataFrame(columns=months) 
 
#Populate two rows with test data 
df.loc[0] = [1, 2, 3, 4, 9, 10, 11, 12, 13, 14, 15, 16] 
df.loc[1] = [4, 9, 10, 11, 12, 13, 14, 15, 16, 1, 2, 3]  
 
print(df) 
 
sorted_months = ['January', 'February', 'March', 'April', 'May', 'June', 
                 'July', 'August', 'September', 'October', 'November',
                 'December'] 
#create a new pandas dataframe that consists of all the columns of the
#original dataframe but in the specified order 
df_sorted = df.loc[:, sorted_months] 
print(df_sorted)

Which prints:

    February January April May June March July September October November December August
0          1       2     3   4    9    10   11        12      13       14       15     16
1          4       9    10  11   12    13   14        15      16        1        2      3
     January February March April May June July August September October November December
0          2        1    10     3   4    9   11     16        12      13       14       15
1          9        4    13    10  11   12   14      3        15      16        1        2

Upvotes: 1

Cameron Riddell
Cameron Riddell

Reputation: 13407

You'll need to grab a sorted list of the month names and reorder your dataframe based on that. Thankfully python has a built-in list of chronological months names in the calendar library:

import calendar

all_months = calendar.month_name[1:]
df_pivot = df_pivot.reindex(columns=all_months)

This will also create null columns for months that are not present in your data. If you do not want the null columns you can use dropna afterwards.

Upvotes: 1

Related Questions