CODEWITHSUNDEEP
pythonpandasdataframesortingdate
Haukur Björnsson
Haukur Björnsson

Reputation: 77

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

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.

Upvotes: 0

Views: 229

Answers (2)

tozCSS
tozCSS

Reputation: 6114

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

Upvotes: 2

BENY
BENY

Reputation: 323226

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

Upvotes: 3

Related Questions