CODEWITHSUNDEEP
pythonpandasdataframegroup-bypandas-groupby
Neil
Neil

Reputation: 8247

Sort by descending order within each group

I have following dataframe in pandas

 code     date         time        tank    
 123      01-01-2018   08:00:00    1
 123      01-01-2018   11:00:00    1
 123      01-01-2018   12:00:00    1
 123      01-01-2018   13:00:00    1
 123      01-01-2018   07:00:00    1
 123      01-01-2018   09:00:00    1
 124      01-01-2018   08:00:00    2
 124      01-01-2018   11:00:00    2
 124      01-01-2018   12:00:00    2
 124      01-01-2018   13:00:00    2
 124      01-01-2018   07:00:00    2
 124      01-01-2018   09:00:00    2

I am grouping by and sorting it by 'time' 

df= df.groupby(['code', 'date', 'tank']).apply(lambda x: x.sort_values(['time'], ascending=True)).reset_index()

When I do reset_index() I am getting following error

ValueError: cannot insert tank, already exists

Upvotes: 1

Views: 146

Answers (1)

cs95
cs95

Reputation: 402493

How about sorting by every grouper key column, with "time" in descending?

df.sort_values(['code', 'date', 'tank', 'time'], ascending=[True]*3 + [False])

    code        date      time  tank
3    123  01-01-2018  13:00:00     1
2    123  01-01-2018  12:00:00     1
1    123  01-01-2018  11:00:00     1
5    123  01-01-2018  09:00:00     1
0    123  01-01-2018  08:00:00     1
4    123  01-01-2018  07:00:00     1
9    124  01-01-2018  13:00:00     2
8    124  01-01-2018  12:00:00     2
7    124  01-01-2018  11:00:00     2
11   124  01-01-2018  09:00:00     2
6    124  01-01-2018  08:00:00     2
10   124  01-01-2018  07:00:00     2

This will achieve the same effect, but without the groupby.

If groupby is needed, you will need two reset_index calls (to remove the last level):

(df.groupby(['code', 'date', 'tank'])
   .time.apply(lambda x: x.sort_values(ascending=False))
   .reset_index(level=-1, drop=True)
   .reset_index())

    code        date  tank      time
0    123  01-01-2018     1  13:00:00
1    123  01-01-2018     1  12:00:00
2    123  01-01-2018     1  11:00:00
3    123  01-01-2018     1  09:00:00
4    123  01-01-2018     1  08:00:00
5    123  01-01-2018     1  07:00:00
6    124  01-01-2018     2  13:00:00
7    124  01-01-2018     2  12:00:00
8    124  01-01-2018     2  11:00:00
9    124  01-01-2018     2  09:00:00
10   124  01-01-2018     2  08:00:00
11   124  01-01-2018     2  07:00:00

Upvotes: 2

Related Questions