CODEWITHSUNDEEP
pythonpython-3.xpandassortingdataframe
Jedrzej
Jedrzej

Reputation: 145

Sorting pandas dataframe by weekdays

How do i sort the DataFrame by weekday names? I cannot use pd.to_datetime() method because my dates aren't numbers.

    Date    Transactions
0   Friday  140.652174
1   Monday  114.000000
2   Saturday    208.826087
3   Sunday  140.565217
4   Thursday    118.217391
5   Tuesday     107.826087
6   Wednesday   105.608696

Upvotes: 12

Views: 11490

Answers (2)

jezrael
jezrael

Reputation: 863166

You can convert column values to ordered categoricals, so it is possible to use sort_values:

cats = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
df['Date'] = pd.Categorical(df['Date'], categories=cats, ordered=True)
df = df.sort_values('Date')
print (df)
        Date  Transactions
1     Monday    114.000000
5    Tuesday    107.826087
6  Wednesday    105.608696
4   Thursday    118.217391
0     Friday    140.652174
2   Saturday    208.826087
3     Sunday    140.565217

Or create an index from the Date column with set_index, then reindex and lastly reset_index:

Notice:
Solution only works if column values are unique

df = df.set_index('Date').reindex(cats).reset_index()
print (df)

        Date  Transactions
0     Monday    114.000000
1    Tuesday    107.826087
2  Wednesday    105.608696
3   Thursday    118.217391
4     Friday    140.652174
5   Saturday    208.826087
6     Sunday    140.565217

Upvotes: 15

jpp
jpp

Reputation: 164773

Use calendar.day_name with categorical data:

from calendar import day_name

df['Date'] = pd.Categorical(df['Date'], categories=day_name, ordered=True)

df = df.sort_values('Date')

print(df)

        Date  Transactions
1     Monday    114.000000
5    Tuesday    107.826087
6  Wednesday    105.608696
4   Thursday    118.217391
0     Friday    140.652174
2   Saturday    208.826087
3     Sunday    140.565217

If in your culture Monday is not considered the first day of the week, you can rotate your days of the week by n days. For example:

from collections import deque

days = deque(day_name)
days.rotate(1)

print(days)

deque(['Sunday', 'Monday', 'Tuesday', 'Wednesday',
       'Thursday', 'Friday', 'Saturday'])

Then feed categories=days as an argument to pd.Categorical.

Upvotes: 2

Related Questions