Sorting a dataset by month with month column as an object

Question

I'm trying to sort my data by "Month" (which is an object and I can't convert it to datetime for some reason), but nothing happens when using .sort_values. No error is printed either. I have three months in my current dataset, "Jul", "Aug", and "Sep" (other datasets might have other months)

I have tried the following snippet of code, which I would have expected to work:

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

Test_LM_Cboe.sort_values(by=['Month'], key=lambda x : pd.Categorical(x, categories=List_Of_Months, ordered=True))

I would have expected Jul to be shown first, then Aug and lastly Sep. However, nothing has changed in the dataset, it's the exact same as before:

I ultimately want to sort the month column so as to make a pivot table where the months comes in the right order.

Answer 1

I think here is what you looking for ...

List_Of_Months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]#your list

keys_of_months = [i for i in range(1,13)] #keys for sorting

dict_of_months = dict(zip(List_Of_Months,keys_of_months)) #list to dict

Test_LM_Cboe.sort_values(by=['Month'], key=lambda x: x.map(dict_of_months)) #applying on the dataframe