Pandas Groupby Sort within Groups - Spark By {Examples} (2024)

You can find out the sorting within each group of Pandas DataFrame by using DataFrame.Sort_values() and the apply() function along with the lambda function. In this article, I will explain how to sort the data within each group using sort_values() and apply() functions and also explain how to get the count of each group and sort by count column.

1. Quick Examples of Sort within Groups of Pandas DataFrame

If you are in a hurry below are some quick examples of doing groupby and performing sort within groups of pandas DataFrame.

# Below are some quick examples.# Example 1 - Using groupby to sort_values of Pandas DataFrame.df2=df.sort_values(['Courses','Fee'],ascending=False).groupby('Courses').head(3)# Example 2 df2 = df.groupby(['Courses','Duration']).agg({'Fee':sum})# First three elements using groupby with lambda and DataFrame.apply() method.df2 = df.apply(lambda x: x.sort_values(ascending=False).head(3))# Example 3 - Using groupby with DataFrame.nlargest().df2=df.groupby(["Courses"])["Fee"].nlargest(3)# Example 4 - Sort values in descending order with groupby.df2=df.groupby(['Courses'])['Fee'].sum().sort_values(ascending=False).head(2)# Example 5 - Sort values of groupby using DataFrame.drop() method.df2=df.groupby(['Fee']).apply(lambda x: x.sort_values(['Courses'], ascending=False).head(3).drop('Fee', axis=1))

Let’s create a pandas DataFrame with a few rows and columns, execute these examples, and validate the results. Our DataFrame contains column namesCourses,Fee and Duration.

# Create a Pandas DataFrame.import pandas as pdimport numpy as nptechnologies= { 'Courses':["Spark","PySpark","Spark","Python","PySpark"], 'Fee' :[22000,25000,23000,24000,26000], 'Duration':['30days','50days','30days','60days','35days'] }df = pd.DataFrame(technologies)print("Create DataFrame:\n", df)

Yields below output.

See Also

PIVOT TABLES IN PANDASR Join (Merge) on Multiple Columns - Spark By {Examples}How to merge rows in Excel without losing datadplyr Tutorial : Data Manipulation (50 Examples)

2. Sort within Each Group of Pandas DataFrame

By using DataFrame.sort_values(), you can sort DataFrame in ascending or descending order, before going to sort the grouped data, we need to group the DataFrame rows by using DataFrame.groupby() method.

Note that groupby preserves the order of rows within each group.

# Using groupby & sort_values to sort.df2=df.sort_values(['Courses','Fee'], ascending=False).groupby('Courses').head(3)print("After sorting the data within each group:\n", df2)

Yields below output. head() method or similar should be used to get the result of the DataFrame. Here, head() method returns 3 rows for each group.

3. Another Example of Sorting within group

First, let’s group the rows using the groupby() function and then perform sorting for each group.

# Groupby using DataFrame.agg() Method.df2 = df.groupby(['Courses','Duration']).agg({'Fee':sum})print("After sorting the data within each group:\n", df2)

Yields below output.

# Output:# After sorting the data within each group: FeeCourses Duration PySpark 35days 26000 50days 25000Python 60days 24000Spark 30days 45000

Now, We group by the first level of the index:

# Groupby the first level of index.df2 = df.agg['Fee'].groupby('Courses', group_keys=False)print(df2)

Then, If you want to sort each group first, take the first three elements by using lambda along with pandas.DataFrame.apply() functions.

# First three elements using groupby with lambda and DataFrame.apply() method.df2 = df.apply(lambda x: x.sort_values(ascending=False).head(3))print(df2)

Yields below output.

# Output: Courses Fee Duration0 Spark NaN NaN1 NaN 25000.0 50days2 Spark NaN NaN3 Python 24000.0 60days4 NaN 26000.0 35days

4. Using Groupby with DataFrame.nlargest()

The DataFrame.nlargest() function is used to get the first n rows ordered by columns in descending order. The columns that are not specified are returned as well, but not used for ordering.

# Using groupby with DataFrame.nlargest().df2=df.groupby(["Courses"])["Fee"].nlargest(3)print(df2)

Yields below output.

# Output:Courses PySpark 4 26000 1 25000Python 3 24000Spark 2 23000 0 22000Name: Fee, dtype: int64

5. Sort Values in Descending Order with Groupby

You can sort values in descending order by using the ascending=False param to sort_values() method. The head() function is used to get the first n rows. It is useful for quickly testing if your object has the right type of data in it.

# Sort values in descending order with groupby.df2=df.groupby(['Courses'])['Fee'].sum().sort_values(ascending=False).head(2)print(df2)

Yields below output.

# Output:CoursesPySpark 51000Spark 45000Name: Fee, dtype: int64

6. Sort Values using apply()

Now let’s see how to sort groupby results using the apply() method. Here we apply a lambda function with the sort_values() function to sort data.

# Sort values of groupby using DataFrame.drop() method.df2=df.groupby(['Fee']).apply(lambda x: x.sort_values(['Courses'], ascending=False).head(3).drop('Fee', axis=1))print(df2)

Yields below output.

# Output:Fee 22000 0 Spark 30days23000 2 Spark 30days24000 3 Python 60days25000 1 PySpark 50days26000 4 PySpark 35days

7. Complete Examples of Sort within Groups

# Create a Pandas DataFrame.import pandas as pdimport numpy as nptechnologies= { 'Courses':["Spark","PySpark","Spark","Python","PySpark"], 'Fee' :[22000,25000,23000,24000,26000], 'Duration':['30days','50days','30days','60days','35days'] }df = pd.DataFrame(technologies)print(df)# Using groupby to sort_values of Pandas DataFrame.df2=df.sort_values(['Courses','Fee'],ascending=False).groupby('Courses').head(3)print(df2)# Groupby using DataFrame.agg() Method.df2 = df.groupby(['Courses','Duration']).agg({'Fee':sum})print(df2)# First three elements using groupby with lambda and DataFrame.apply() method.df2 = df.apply(lambda x: x.sort_values(ascending=False).head(3))print(df2)# Using groupby with DataFrame.nlargest().df2=df.groupby(["Courses"])["Fee"].nlargest(3)print(df2)# Sort values in descending order with groupby.df2=df.groupby(['Courses'])['Fee'].sum().sort_values(ascending=False).head(2)print(df2)# Sort values of groupby using DataFrame.drop() method.df2=df.groupby(['Fee']).apply(lambda x: x.sort_values(['Courses'], ascending=False).head(3).drop('Fee', axis=1))print(df2)

Conclusion

In this article, You have learned how to sort values within each group after groupby using Pandas DataFrame.groupby(), DataFrame.Sort_values(), and apply() with lambda functions with multiple examples.

Related Articles

• Pandas Find Unique Values From Columns
• Convert Row to Column Header in Pandas DataFrame
• Retrieve Number of Rows From Pandas DataFrame
• Replace Column value in Pandas DataFrame
• Rename Specific Columns in Pandas
• How to GroupBy Index in Pandas?
• Pandas groupby() and count() with Examples
• Pandas groupby() and sum() With Examples
• Pandas Sort by Column Values DataFrame
• Sort Pandas DataFrame by Date (Datetime)
• How to Sort Multiple Columns in Pandas DataFrame
• Pandas Series.sort_values() With Examples
• Pandas Groupby Transform
• Pandas Groupby Aggregate Explained
• Pandas Percentage Total With Groupby
• Convert groupby() output from series to DatatFrame
• Pandas Group Rows into List Using groupby()
• Pandas DataFrame count() Function

References

• https://pandas.pydata.org/pandas-docs/version/0.17.1/groupby.html