Is there some way in Pandas to aggregate rows by one column and sort by another?

user_19091397 0
Viewed 25

Disclaimer: it`s my first question, fell little akward :/

I have DataFrame that have two columns: title and val:

>>> df=pd.DataFrame(
...     {
...         "title": ["a", "a", "a", "b", "b", "b", "c", "c", "c"],
...         "val": [1, 4, 3, 4, 5, 6, 2, 4, 5],
...     }
... )
>>> df
  title  val
0     a    1
1     a    4
2     a    3
3     b    4
4     b    5
5     b    6
6     c    2
7     c    4
8     c    5

I would like to aggregate rows in clusters with same title and then sort that clusters by descending of maximum val that they have. If maximum val equals, then sort clusters by title alphabetically. The rows in the clusters must be sort descending by val.

I know, I can do it in long way, like:

>>> df.loc[:,'max_value']=df.groupby('title', as_index=False).transform(max) 
>>> df
  title  val  max_value
0     a    1          4
1     a    4          4
2     a    3          4
3     b    4          6
4     b    5          6
5     b    6          6
6     c    2          5
7     c    4          5
8     c    5          5
>>> df.sort_values(['max_value','title', 'val'], ascending=False, inplace=True)
>>> df.drop(columns='max_value', inplace=True)
>>> df
  title  val
5     b    6
4     b    5
3     b    4
8     c    5
7     c    4
6     c    2
1     a    4
2     a    3
0     a    1

But will some shortcut have place there?

pandas dataframe python
1 Answers

There is sorting by multiple columns, so created helper column new used for sorting:

df = df.loc[df.assign(a = df.groupby('title', as_index=False).transform(max))
              .sort_values(['a','title', 'val'], ascending=False).index]
print (df)
  title  val
5     b    6
4     b    5
3     b    4
8     c    5
7     c    4
6     c    2
1     a    4
2     a    3
0     a    1
user_29010020
Related