groupby iterate with sorting

Question

Below is the code and console output.

import pandas as pd

#data
df= pd.DataFrame([{'col1':'a', 'is_open':0}, {'col1':'b', 'is_open':1}])

#1
df = df.sort_values('is_open',ascending=False).reset_index(drop=True)
# print(df)

#2
for i, d in df.groupby(['col1', 'is_open']):
    print(d)

  col1  is_open
1    a        0
  col1  is_open
0    b        1

I want is_open=1 to be printed out first like below.

  col1  is_open
0    b        1
  col1  is_open
1    a        0

Sorting dataframe before grouping did not work.

Any help would be appreciated.

hmm, it should be printed first - I assume your numbers are actually strings? — Umar.H, Jun 23 '22 at 01:50

Poiuy · Answer 1 · 2022-06-23T03:37:36.723

1

You may want something like the snippet below. Pandas groupBy function defaults to sorting by group keys which are in this case the first column, followed by the second column. Once the values have been grouped and aggregated you can sort the result and iterate through the data frame.

df = df.groupby(['col1', 'is_open']).apply(lambda x: x).sort_values('is_open',ascending=False)

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html

edited Jun 23 '22 at 03:37

answered Jun 23 '22 at 02:05

Poiuy

331
2
6

Does that work for you? AFAIK the result of `groupby` does not have `.sort_values` – Amadan Jun 23 '22 at 03:06
hmm you're right I assumed that the groupBy would return a normal data frame object instead of a DataFrameGroupBy object. Editing the answer to convert back to a dataframe using an identity function. – Poiuy Jun 23 '22 at 03:34

score 1 · Accepted Answer · answered Jun 23 '22 at 03:10

1

As Poiuy noted, groupby will sort by the grouping key... but it can be turned off.

df.groupby(['col1', 'is_open'], sort=False)

answered Jun 23 '22 at 03:10

Amadan

191,408
23
240
301

This helped me. I inserted the answer above. – tompal18 Jun 23 '22 at 04:20

score 1 · Answer 3 · answered Jun 23 '22 at 04:21

Below worked for me. Thank you all.

import pandas as pd

#data
df= pd.DataFrame([{'col1':'a', 'is_open':0}, {'col1':'b', 'is_open':1}])

#1
# df = df.sort_values('is_open',ascending=False).reset_index(drop=True)
df = df.sort_values('is_open',ascending=True)
# print(df)

#2
for i, d in df.groupby(['col1', 'is_open'], sort=False):
    print(d)

groupby iterate with sorting

3 Answers3