Sorting values within groups in Pandas

Pandas

chevron_right

Cookbooks

chevron_right

DataFrame Cookbooks

chevron_right

Data Aggregation Cookbook

schedule Jul 1, 2022

Last updated

local_offer Python●Pandas

Grouping by single column

Consider the following DataFrame:


        
        
            
                
                
                    df = pd.DataFrame({"A":[6,3,5,4],"B":[3,4,8,2],"group":["a","b","a","b"]})
df
                
            
               A  B  group
0  6  3    a
1  3  4    b
2  5  8    a
3  4  2    b

Using sort_values method

To get the row with the smallest value for A in each group:


        
        
            
                
                
                    df.sort_values("A").groupby("group").head(1)
                
            
               A  B  group
1  3  4    b
2  5  8    a

If you want the largest value instead, set ascending=False for sort_values(~).

Explanation

Here, we are first sorting df by column A using sort_values(~):


        
        
            
                
                
                    df.sort_values("A")   # ascending=True
                
            
               A  B  group
1  3  4    b
3  4  2    b
2  5  8    a
0  6  3    a

We then group by column group, and fetch the first row of each group using head(1) (you could also use first()). This is guaranteed to work because groupby(~) preserves the ordering of the rows, that is, the order of the rows computed by sort_values(~) will be respected.

Pandas DataFrame | sort_values method

Sorts the source DataFrame either by column or row values.

chevron_right