How to sort a Pandas DataFrame by date, in this specific way?

Question

I am trying to sort a Pandas DataFrame by dates, but I don't want them purely ascending or descending so df.sort_values(by=['Date'], ascending=False) doesn't produce the result I am looking for.

My dataframe looks like:

df = pd.DataFrame({'Date':[date(2017,1,1),date(2019,2,1),date(2018,4,1),date(2017,3,1),date(2018,3,1)],'Count':[6,8,4,14,9],'Percentage':[0.02,0.62,0.41,0.36,0.39]})

    Date        Count   Percentage
0   2017-01-01  6       0.02
1   2019-02-01  8       0.62
2   2018-04-01  4       0.41
3   2017-03-01  14      0.36
4   2018-03-01  9       0.39

I want to sort the DataFrame by date so that each month is in ascending order, while the years themselves are in descending order. Tricky to phrase in words, but the desired output hopefully explains better.

Desired output:

    Date        Count   Percentage
0   2019-02-01  8       0.62
1   2018-03-01  9       0.39
2   2018-04-01  4       0.41
3   2017-01-01  6       0.02
4   2017-03-01  14      0.36

As you can see my desired output isn't strictly ascending or descending. There must exists a relatively efficient solution, which I need, as my actual DataFrame is too large for brute force.

did you try to google it? this post stackoverflow.com/questions/28161356/… seems pretty similar, using the sort method on the dataframe — Itai Ben Amram
– Itai Ben Amram, Commented Jun 21, 2020 at 22:01

BENY · Accepted Answer · 2020-06-21 22:02:30Z

3

What you need is sort_values by year and Month , but ascending for Year and decending for Month

df['Month']=df.Date.dt.month
df['Year']=df.Date.dt.year
df=df.sort_values(['Year','Month'],ascending=[False,True])
df=df.drop(['Year','Month'], axis=1)
df
        Date  Count  Percentage
1 2019-02-01      8        0.62
4 2018-03-01      9        0.39
2 2018-04-01      4        0.41
0 2017-01-01      6        0.02
3 2017-03-01     14        0.36

answered Jun 21, 2020 at 22:02

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tozCSS · Accepted Answer · 2020-06-21 22:10:13Z

2

In a single line:

df.assign(yr=df.Date.dt.year,mo=df.Date.dt.month).sort_values(['yr','mo'],ascending=[False,True]).drop(['yr','mo'],axis=1)

    Date        Count   Percentage
1   2019-02-01  8       0.62
4   2018-03-01  9       0.39
2   2018-04-01  4       0.41
0   2017-01-01  6       0.02
3   2017-03-01  14      0.36

edited Jun 21, 2020 at 22:10

answered Jun 21, 2020 at 22:04

tozCSS

6,2443 gold badges37 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to sort a Pandas DataFrame by date, in this specific way?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related