Pandas query() Method

Introduction 

You can query the DataFrame using the pandas query() method. A pandas query expression is passed as a string parameter to the pandasquery() method, and it must return True or False. The DataFrame when the result is True by the query statement, is returned.

Data analysis calls for numerous filtering techniques. There are numerous ways to filter a Data frame and Dataframe using Pandas. One of them is the query() method.

• Apply a boolean expression to a DataFrame's columns. By default, the pandas query() method uses a slightly altered Python syntax. In contrast to their boolean relatives and or, the bitwise operators & and | have precedence. Although the syntax of this Python sentence is correct, the meanings are not.
• Example:

Output:

How to Use Pandas .query() Method

Only when there are no blank spaces in the column name does the Dataframe.query() method operates. Therefore, spaces in column names are replaced with '_' prior to implementing the procedure using the pandas query.

The result of evaluating this expression is first sent to DataFrame.loc, and if that fails due to a multidimensional key (such as a DataFrame), the result is then passed to DataFrame.__getitem__ (). You can treat both the index and columns of the frame as a column in the frame because the DataFrame.index and DataFrame.columns attributes of the DataFrame instance are put in the query namespace by default.

The frame index is represented by the identifier index. However, the index name can also be used to identify it in a query. Please be aware that you cannot use Python keywords as identifiers.

Syntax 

DataFrame.query(_expr_, _*_, _inplace=False_, _**kwargs_)

Parameters

• expr: A string-based expression for data filtering. The search term to analyze. Environment variables can be referred to by prefixing them with a "@" symbol, such as @a + b. Backticks can be used to denote column names that aren't legitimate Python variable names. Therefore, column names that begin with digits, contain spaces, or other punctuation (other than underscores) must be enclosed in backticks.
• inplace: If True, make modifications to the initial data frame.
• kwargs: Other keyword arguments

Return Value 

DataFrame produced by the specified query expression or None if none was returned.

Examples

• Single-Condition Filtering

  For this illustration, only one criterion is used to filter the data. The spaces in column names have been replaced with '_' before using the pandasquery() method.

Example:

Output:

• Multiple condition filtering

  In this illustration, a dataframe has been filtered using several criteria. The spaces in column names have been replaced with '_' before using the pandasquery() method. Example:

Output:

• Subset a pandas dataframe based on a numeric variable

  Here, we're going to subset the data based on a salary-related numeric variable. The rows in the DataFrame with sales larger than 20,000 will be retrieved. Simply putting a logical phrase in the parenthesis, "Salary > 20000" accomplishes this. Example:

Output:

You'll see that in this straightforward example, we filtered the sales variable using the greater-than sign. While testing for equivalence (==), greater-than-or-equal, least-than, etc. is another option, we could also test for these other conditions as well. It is possible to compare using almost any operator.

• Select rows based on a categorical variable

  We're going to use "dot notation" to use the pandas.query() method. We only type df, the abbreviation for our DataFrame, followed by the word.query(), which stands for the method. We have a logical expression that reads "Company == "Google"" inside the query method's parentheses.

Example:

Output:

• Subset a DataFrame by index

  Here, we're going to make use of the DataFrame's index to subset the rows according to that index. On the left side of each row, an integer, numbered from zero, is connected with it. The index is this set of numbers. Each row will initially receive a numeric index value similar to this when we establish a DataFrame (although, there are ways to change the index to something else).

  Additionally, you can include mathematical operations inside of your expressions, which is a helpful trick for subsetting your data in unique ways. Example:

Output:

• Subset a pandas dataframe by comparing two columns

  We can compare Countries and companies using this query. This query method is to compare two columns as vectors just like numpy vector operations. Example:

Output:

• Select rows based on multiple conditions

  Here, the DataFrame will be a subset based on a challenging logical expression. The and operator is used to join the two smaller phrases that make up the expression. Example:

Output:

• Reference local variables inside of the query

  We have been referring to variables that are located inside the DataFrame up to this point. We have been referring to the column names, such as Salary.

  We will now refer to a variable that is not contained in the dataframe. Here, we're going to compute the Salary variable's mean and save the result in another variable that isn't part of our data frame. We're going to use the '@' character in front of the variable to do this. Example:

Output:

• Modify a DataFrame in Place

  We will alter a DataFrame "in place". This indicates that rather than creating a new DataFrame, we'll make direct changes to the one we're working with.

  Now keep in mind that because we will be overwriting our data, altering a DataFrame in place can be dangerous. In light of this, I'm going to start by making a second DataFrame called Salary_copy. In this manner, when we change the data, we'll replace the duplicate and preserve the integrity of our original.

Example:

Output: