Python Pandas - GroupBy Filtration

---

---

Filtration in Pandas is a GroupBy operation that allows you to select specific parts of grouped data based on group conditions applied to each group. It can either filter entire groups, parts of groups, or customize which groups are included in the result.

To filter the GroupBy object in pandas you can use the methods such as head(), tail(), and nth(), or custom filters with the filter() method using user-defined functions (UDFs).

In this tutorial we will learn about various built-in filtration methods, such as head(), tail(), and nth(), as well as custom filtering with user-defined functions (UDFs) through the filter() method.

Built-In Filtration Methods for GroupBy

Pandas provides several built-in filtration methods for GroupBy objects, which allow quick data selections within each group.

• head(): Selects the top rows in each group.

• nth(): Selects the nth row/rows of each group.

• tail(): Selects the bottom rows in each group.

Let's explore each of these methods with examples.

Filter the Top Rows of Each Group with head()

The built-in head() method can be used to filter/select first few rows from each group.

Example

Below is a simple example demonstrating how to use the head() method to filter the top rows from each group.

import pandas as pd
import numpy as np

ipl_data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
   'kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
   'Rank': [1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2],
   'Year': [2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017],
   'Points':[876,789,863,673,741,812,756,788,694,701,804,690]}
df = pd.DataFrame(ipl_data)

# Display the Original DataFrame
print("Original DataFrame:")
print(df)

# Select the top rows from each group
print("\nTop rows of each group:")
print(df.groupby('Team').head(1))

Following is the output of the above code −

Original DataFrame:

	Team	Rank	Year	Points
0	Riders	1	2014	876
1	Riders	2	2015	789
2	Devils	2	2014	863
3	Devils	3	2015	673
4	Kings	3	2014	741
5	kings	4	2015	812
6	Kings	1	2016	756
7	Kings	1	2017	788
8	Riders	2	2016	694
9	Royals	4	2014	701
10	Royals	1	2015	804
11	Riders	2	2017	690

Top rows of each group:

	Team	Rank	Year	Points
0	Riders	1	2014	876
2	Devils	2	2014	863
4	Kings	3	2014	741
5	kings	4	2015	812
9	Royals	4	2014	701

Selecting Specific Rows Using nth()

The .nth() method allows you to select a specific row (or rows) within each group based on its position.

Example

The following example selects the second element within each group using the nth() method.

import pandas as pd
import numpy as np

data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
   'kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
   'Rank': [1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2],
   'Year': [2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017],
   'Points':[876,789,863,673,741,812,756,788,694,701,804,690]}
df = pd.DataFrame(data)

# Display the Original DataFrame
print("Original DataFrame:")
print(df)

# Select the nth row (1st index) from each group
print("\nSecond row in each group:")
print(df.groupby('Team').nth(1))

Following is the output of the above code −

Original DataFrame:

	Team	Rank	Year	Points
0	Riders	1	2014	876
1	Riders	2	2015	789
2	Devils	2	2014	863
3	Devils	3	2015	673
4	Kings	3	2014	741
5	kings	4	2015	812
6	Kings	1	2016	756
7	Kings	1	2017	788
8	Riders	2	2016	694
9	Royals	4	2014	701
10	Royals	1	2015	804
11	Riders	2	2017	690

Second row in each group:

	Team	Rank	Year	Points
1	Riders	2	2015	789
3	Devils	3	2015	673
6	Kings	1	2016	756
10	Royals	1	2015	804

Filtering Specific Columns with nth()

To filter specific rows from specific columns of each group you can use the nth() method along with the GroupBy object subset.

Example

Below example demonstrates filtering the nth row from specific columns using the nth() method.

import pandas as pd
import numpy as np

ipl_data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
   'kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
   'Rank': [1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2],
   'Year': [2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017],
   'Points':[876,789,863,673,741,812,756,788,694,701,804,690]}
df = pd.DataFrame(ipl_data)

# Select the nth row from specific columns
print("Selected columns (Rank, Points) for nth row:")
print(df.groupby('Team')[["Rank", "Points"]].nth(1))

Following is the output of the above code −

Selected columns (Rank, Points) for nth row:

	Rank	Points
1	2	789
3	3	673
6	1	756
10	1	804

Filtering Bottom Rows of Each Group with tail()

The tail() method can be used to filter the last few rows from each group.

Example

This example shows how you can select the bottom rows from each group using the tail() method.

import pandas as pd
import numpy as np

ipl_data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
   'kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
   'Rank': [1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2],
   'Year': [2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017],
   'Points':[876,789,863,673,741,812,756,788,694,701,804,690]}
df = pd.DataFrame(ipl_data)

# Select the bottom rows of each group
print("Bottom rows of each group:")
print(df.groupby('Team').tail(2))

Following is the output of the above code −

Bottom rows of each group:

	Team	Rank	Year	Points
2	Devils	2	2014	863
3	Devils	3	2015	673
5	kings	4	2015	812
6	Kings	1	2016	756
7	Kings	1	2017	788
8	Riders	2	2016	694
9	Royals	4	2014	701
10	Royals	1	2015	804
11	Riders	2	2017	690

Custom Group Filtration using the filter() Method

In addition to the built-in methods, Pandas allows you to define custom filter functions for more complex conditions using the .filter() method. With the Pandas DataFrameGroupBy.filter() method, you can apply more complex conditions to each group by passing a user defined function that returns True or False for each group.

Example

The following example filters out groups where the average Rank value is below 2 using the DataFrameGroupBy.filter() method.

import pandas as pd
import numpy as np

data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings', 'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
        'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
        'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
        'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]}
df = pd.DataFrame(data)

# Define a custom filter function
filtered_df = df.groupby('Team').filter(lambda x: x['Rank'].mean() > 2)

# Display the filtered DataFrame
print("Groups with an average Rank > 2:")
print(filtered_df)

On executing the above code you will get the following results −

Groups with an average Rank > 2:

	Team	Rank	Year	Points
2	Devils	2	2014	863
3	Devils	3	2015	673
4	Kings	3	2014	741
5	Kings	4	2015	812
6	Kings	1	2016	756
7	Kings	1	2017	788
9	Royals	4	2014	701
10	Royals	1	2015	804