DataFrame.query() function: How to query pandas DataFrame?

How To Query A Pandas DataFrame

In this Python tutorial, We are going to discuss how we can use the DataFrame.query() function to query pandas DataFrames. So, let’s get started with our discussion.


Syntax of the DataFrame.query() function in pandas

pandas.DataFrame.query(expr, inplace=False, **kwargs)

expr = It is a string that contains the logical expression according to which the rows of the pandas DataFrame is selected (when the value of expr=True).
inplace = It is a boolean value (either ‘True‘ or ‘False‘) that will decide if the DataFrame is modified inplace or a new copy of the modified DataFrame is returned.
**kwargs = It refers to the other keyword arguments if any.

When to use the DataFrame.query() function?

Pandas provide us so many ways/methods to select or filter the rows from a pandas DataFrame object. And the DataFrame.query() function in pandas is one of the robust methods to filter the rows of a pandas DataFrame object.

And it is preferable to use the DataFrame.query() function to select or filter the rows of the pandas DataFrame object instead of the traditional and the commonly used indexing method. This DataFrame.query() function can also be used with other pandas methods to make the data manipulation smooth and straightforward.

Examples of the DataFrame.query() function

Let’s create a sample pandas DataFrame object to work with and try to understand the functioning/working of the DataFrame.query() function with the help of few examples.

Create a sample pandas DataFrame object

# Import pandas Python module
import pandas as pd

# Create a pandas DataFrame object
df = pd.DataFrame({'Dept': ['ECE', 'ICE', 'IT', 'CSE', 'CHE', 'EE', 'TE', 'ME', 'CSE', 'IPE', 'ECE'],
                    'GPA': [8.85, 9.03, 7.85, 8.85, 9.45, 7.45, 6.85, 9.35, 6.53,8.85, 7.83],
                    'Name': ['Mohan', 'Gautam', 'Tanya', 'Rashmi', 'Kirti', 'Ravi', 'Sanjay', 'Naveen', 'Gaurav', 'Ram', 'Tom'],
                    'RegNo': [111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121],
                    'City': ['Biharsharif','Ranchi','Patna','Patiala','Rajgir','Patna','Patna','Mysore','Patna','Mumbai','Patna']})

# Print the created pandas DataFrame
print('Sample pandas DataFrame:\n')
print(df)

Output:

Sample pandas DataFrame:

   Dept   GPA    Name  RegNo         City
0   ECE  8.85   Mohan    111  Biharsharif
1   ICE  9.03  Gautam    112       Ranchi
2    IT  7.85   Tanya    113        Patna
3   CSE  8.85  Rashmi    114      Patiala
4   CHE  9.45   Kirti    115       Rajgir
5    EE  7.45    Ravi    116        Patna
6    TE  6.85  Sanjay    117        Patna
7    ME  9.35  Naveen    118       Mysore
8   CSE  6.53  Gaurav    119        Patna
9   IPE  8.85     Ram    120       Mumbai
10  ECE  7.83     Tom    121        Patna

Example #1

Select the rows of the sample DataFrame where (City = “Patna”).

# Filter the rows of the sample DataFrame which has City = 'Patna'
# Using the DataFrame.query() function
df2 = df.query('City=="Patna"')

# Print the filtered sample pandas DataFrame
print('Filtered sample pandas DataFrame:\n')
print(df2)

Output:

Filtered sample pandas DataFrame:

   Dept   GPA    Name  RegNo   City
2    IT  7.85   Tanya    113  Patna
5    EE  7.45    Ravi    116  Patna
6    TE  6.85  Sanjay    117  Patna
8   CSE  6.53  Gaurav    119  Patna
10  ECE  7.83     Tom    121  Patna

Example #2

Select the rows of the sample DataFrame where (GPA < 8).

# Filter the rows of the sample DataFrame which has GPA < 8
# Using the DataFrame.query() function
df2 = df.query('GPA < 8' & City == "Patna")

# Print the filtered sample pandas DataFrame
print('Filtered sample pandas DataFrame:\n')
print(df2)

Output:

Filtered sample pandas DataFrame:

   Dept   GPA    Name  RegNo   City
2    IT  7.85   Tanya    113  Patna
5    EE  7.45    Ravi    116  Patna
6    TE  6.85  Sanjay    117  Patna
8   CSE  6.53  Gaurav    119  Patna
10  ECE  7.83     Tom    121  Patna

Example #3

Select the rows of the sample DataFrame where (GPA < 7 and City = ‘Patna’).

# Filter the rows of the sample DataFrame which has GPA < 7 & City = 'Patna'
# Using the DataFrame.query() function
df2 = df.query('GPA < 7 & City == "Patna"')

# Print the filtered sample pandas DataFrame
print('Filtered sample pandas DataFrame:\n')
print(df2)

Output:

Filtered sample pandas DataFrame:

  Dept   GPA    Name  RegNo   City
6   TE  6.85  Sanjay    117  Patna
8  CSE  6.53  Gaurav    119  Patna

Example #4

Select the rows of the sample DataFrame which has Dept in [ECE, CSE, IT].

# Filter the rows of the sample DataFrame which has Dept in (ECE, CSE, IT)
# Using the DataFrame.query() function
df2 = df.query("Dept in ['CSE','ECE','IT']")

# Print the filtered sample pandas DataFrame
print('Filtered sample pandas DataFrame:\n')
print(df2)

Output:

Filtered sample pandas DataFrame:

   Dept   GPA    Name  RegNo         City
0   ECE  8.85   Mohan    111  Biharsharif
2    IT  7.85   Tanya    113        Patna
3   CSE  8.85  Rashmi    114      Patiala
8   CSE  6.53  Gaurav    119        Patna
10  ECE  7.83     Tom    121        Patna

Example #5

Select the rows of the sample DataFrame where (RegNo < 115 and GPA > 7).

# Filter the rows of the sample DataFrame which has (RegNo < 115 & GPA > 7)
# Using the DataFrame.query() function
df2 = df.query("RegNo < 115 & GPA > 7")

# Print the filtered sample pandas DataFrame
print('Filtered sample pandas DataFrame:\n')
print(df2)

Output:

Filtered sample pandas DataFrame:

  Dept   GPA    Name  RegNo         City
0  ECE  8.85   Mohan    111  Biharsharif
1  ICE  9.03  Gautam    112       Ranchi
2   IT  7.85   Tanya    113        Patna
3  CSE  8.85  Rashmi    114      Patiala

Summing-up

In this Python tutorial, we have learned how we can use the DataFrame.query() function in Pandas to query our pandas DataFrame object. Hope you have understood the concepts and examples discussed above and are ready to use them to query your own pandas DataFrame. Thanks for reading! Stay tuned with us for more amazing learning content on Python programming.