Python - Filter Rows Based on Column Values with query function in Pandas?

To filter rows based on column values, we can use the query() function. In the function, set the condition through which you want to filter records. At first, import the required library −

import pandas as pd

Following is our data with Team Records −

Team = [['India', 1, 100],['Australia', 2, 85],['England', 3, 75],['New Zealand', 4 , 65],['South Africa', 5, 50],['Bangladesh', 6, 40]]

Create a DataFrame from above and add columns as well −

dataFrame = pd.DataFrame(Team, columns=['Country', 'Rank', 'Points'])

Use query() to filter records with “Rank” equal to 5 −

dataFrame.query("Rank == 5"))

Example

Following is the complete code −

import pandas as pd

# data in the form of list of team rankings
Team = [['India', 1, 100],['Australia', 2, 85],['England', 3, 75],['New Zealand', 4 , 65],['South Africa', 5, 50],['Bangladesh', 6, 40]]

# Creating a DataFrame and add columns
dataFrame = pd.DataFrame(Team, columns=['Country', 'Rank', 'Points'])

print"DataFrame...\n",dataFrame

# using query to filter rows
print"\nFetch Team with Rank 5..\n",dataFrame.query("Rank == 5")

Output

This will produce the following output −

DataFrame...
        Country   Rank   Points
0         India      1      100
1     Australia      2       85
2       England      3       75
3   New Zealand      4       65
4  South Africa      5       50
5    Bangladesh      6       40

Fetch Team with Rank 5..
        Country   Rank   Points
4  South Africa      5       50

Example

Let us see another example. Here, we have a different condition to filter rows −

import pandas as pd

# data in the form of list of team rankings
Team = [['India', 1, 100],['Australia', 2, 85],['England', 3, 75],['New Zealand', 4 , 65],['South Africa', 5, 50],['Bangladesh', 6, 40]]

# Creating a DataFrame and add columns
dataFrame = pd.DataFrame(Team, columns=['Country', 'Rank', 'Points'])

print"DataFrame...\n",dataFrame

# using query to filter rows
print"\nFetch Team with points above 70 and Rank less than 3..\n"
print(dataFrame.query("Points > 70 and Rank <3"))

Output

This will produce the following output −

DataFrame...
        Country   Rank   Points
0         India      1      100
1     Australia      2       85
2       England      3       75
3   New Zealand      4       65
4  South Africa      5       50
5    Bangladesh      6       40

Fetch Team with points above 70 and Rank less than 3..

     Country   Rank   Points
0      India      1      100
1  Australia      2       85

AmitDiwan

e

Updated on: 21-Sep-2021

515 Views

Kickstart Your Career

Get certified by completing the course

Get Started