Sorting the grouped data as per group size in Pandas

Question

I have two columns in my dataset, col1 and col2. I want group the data as per col1 and then sort the data as per the size of each group. That is, I want to display groups in ascending order of their size.

I have written the code for grouping and displaying the data as follows:

grouped_data = df.groupby('col1')
"""code for sorting comes here"""
for name,group in grouped_data:
          print (name)
          print (group)

Before displaying the data, I need to sort it as per group size, which I am not able to do.

score 67 · Accepted Answer · edited Oct 29 '18 at 10:45

67

For Pandas 0.17+, use sort_values:

df.groupby('col1').size().sort_values(ascending=False)

For pre-0.17, you can use size().order():

df.groupby('col1').size().order(ascending=False)

edited Oct 29 '18 at 10:45

jpp

159,742
34
281
339

answered Jun 12 '15 at 14:07

Victor Yan

3,339
2
28
28

I am doing df.groupby('A')['B'].mean(). Is there any way to get the means of the groups sorted using size? Thanks – Neo Jun 03 '19 at 18:29
@Neo Do you mean: df.groupby('A')['B'].mean().sort_values(ascending=False) ? – Victor Yan Jun 04 '19 at 17:58
1

I think this will sort mean() values. I want to first sort groups(using their size) and then find mean of each of those groups. – Neo Jun 04 '19 at 18:27
1

@Neo I think this will do: df.groupby("A")['B'].agg(['size', 'mean']).sort_values(by='size', ascending=False) – Victor Yan Jun 06 '19 at 03:33

Andy Hayden · Answer 2 · 2014-03-10T07:21:48.837

16

You can use python's sorted:

In [11]: df = pd.DataFrame([[1, 2], [1, 4], [5, 6]], index=['a', 'b', 'c'], columns=['A', 'B'])

In [12]: g = df.groupby('A')

In [13]: sorted(g,  # iterates pairs of (key, corresponding subDataFrame)
                key=lambda x: len(x[1]),  # sort by number of rows (len of subDataFrame)
                reverse=True)  # reverse the sort i.e. largest first
Out[13]: 
[(1,    A  B
     a  1  2
     b  1  4),
 (5,    A  B
     c  5  6)]

Note: as an iterator g, iterates over pairs of the key and the corresponding subframe:

In [14]: list(g)  # happens to be the same as the above...
Out[14]:
[(1,    A  B
     a  1  2
     b  1  4,
 (5,    A  B
     c  5  6)]

edited Mar 10 '14 at 07:21

answered Mar 10 '14 at 03:29

Andy Hayden

359,921
101
625
535

sorted function works good but it returns a list. Each element of the list is a pandas dataframe. So I try to access a column using the following code: sorted_data = sorted (...) print sorted_data[0]['col2'] But this doesn't work. How do I access each column of the dataframe in the sorted list? – krackoder Mar 10 '14 at 07:39
1

It's a list of tuples, so you can iterate it with: `for name, group in sorted(..)`, then the column is `group['col2']`. Or you can do `sorted_data[0][1]['col2']`... – Andy Hayden Mar 10 '14 at 07:45
Oh Yes. I missed to to notice that it is a list of tuples. Thanks. – krackoder Mar 10 '14 at 07:47

score 0 · Answer 3 · edited Jan 15 '22 at 08:21

df = pandas.DataFrame([[5, 5], [9, 7], [1, 8], [1, 7], [7, 8],
                       [9, 5], [5, 6], [1, 2], [1, 4], [5, 6]],
                      columns=['A', 'B'])

A B
0 5 5
1 9 7
2 1 8
3 1 7
4 7 8
5 9 5
6 5 6
7 1 2
8 1 4
9 5 6

group = df.groupby('A')

count = group.size()

count  
A

1 4
5 3
7 1
9 2
dtype: int64

grp_len = count[count.index.isin(count.nlargest(2).index)]

grp_len   
A

1 4
5 3
dtype: int64

Sorting the grouped data as per group size in Pandas

3 Answers3