1
$\begingroup$

I have dataframe named df1.

I want to sort data frame month column according to month (Jan, Feb, March..).

For that I used code:

sorted_df = df1.sort_values(by='month')
print(sorted_df)

but the output is sorted by alphabetic order of month column.

I think the reason for that in this month column data type is object so month column is sorted by alphabetic order.

The question is: how sort values in column month in correct order (according to order of months in year)?

My dataframe:

alphabetic

$\endgroup$
2
$\begingroup$

I suggest first separating the month column into day and month using str.split('-')

# create test data
df = pd.DataFrame(['20-Apr', '19-Mar', '4-Dec'], columns=['month'])
# create day column
df['day'] = 0 
split =  df['month'].str.split('-', expand=True)
df['day'], df['month'] = split[0], split[1]

Now that month is seperated, you can change it to categorical such that it can be custom sorted

df['month'] = pd.Categorical(df['month'], ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'])

Now you can sort

df.sort_values("month")

Hope this helps

| improve this answer | |
$\endgroup$
  • $\begingroup$ @A Kareem why pd.categorical function? from this function what we can do?? $\endgroup$ – randunu galhena May 3 at 16:53
  • 1
    $\begingroup$ Because then when you sort them, it relies on the ordering of the initial list passed to it, You should read the documentation here From the documentation: "Ordered Categoricals can be sorted according to the custom order of the categories and can have a min and max value." $\endgroup$ – A Kareem May 3 at 17:07
  • $\begingroup$ Finally I want to concatenate this month and day column. So I went to concatenate this month and day column but there had error,unsupported operand type(s) for +: 'Categorical' and 'str' so how I can concatenate this month and day column. df['final_date'] = df['month'] +' '+ df['day'] print(df) This code I have used above concatenation. $\endgroup$ – randunu galhena May 3 at 17:12
  • $\begingroup$ @A Kareem thanks a lot. I did a concatenation part successfully. I accepted your answer as correct one. $\endgroup$ – randunu galhena May 3 at 18:04
3
$\begingroup$

Your month column is really a partial date (i.e. the year is missing), but you could still just convert that column to a proper date format and then sort. You will have to specify a format, so the parser knows roughly what to do.

df["date"] = pd.to_datetime(["20-Apr"], format="%d-%b")
df.sort_values("date")   # ascending by default

Without any year information, it will add the year as 1900.


If you want to add e.g. this year, you could do the following, adding 12*20 months to the new date column:

df["fixed_date"] = df.date.apply(lambda x: pd.tseries.offsets.shift_month(x, 12*20))
| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.