Python Pandas - Query the columns of a DataFrame

To query the columns of a Pandas DataFrame, use the query() method. This method allows you to filter records using a string expression, making it easier to write complex conditions in a readable format.

Creating a DataFrame

Let's start by creating a sample DataFrame with product inventory data ?

import pandas as pd

dataFrame = pd.DataFrame({
    "Product": ["SmartTV", "PenDrive", "Speaker", "Earphone"],
    "Opening_Stock": [300, 700, 1200, 1500],
    "Closing_Stock": [200, 500, 1000, 900]
})

print("DataFrame...")
print(dataFrame)

DataFrame...
   Product  Opening_Stock  Closing_Stock
0  SmartTV            300            200
1  PenDrive           700            500
2  Speaker           1200           1000
3  Earphone          1500            900

Using query() with Multiple Conditions

The query() method accepts a string expression with conditions. You can combine multiple conditions using & (AND) and | (OR) operators ?

import pandas as pd

dataFrame = pd.DataFrame({
    "Product": ["SmartTV", "PenDrive", "Speaker", "Earphone"],
    "Opening_Stock": [300, 700, 1200, 1500],
    "Closing_Stock": [200, 500, 1000, 900]
})

# Query with multiple conditions
result = dataFrame.query('Opening_Stock >= 500 & Closing_Stock < 1000 & Product.str.startswith("P")')
print("Querying columns to filter records...")
print(result)

Querying columns to filter records...
   Product  Opening_Stock  Closing_Stock
1  PenDrive            700            500

Common Query Operations

Here are some additional examples of using query() with different conditions ?

import pandas as pd

dataFrame = pd.DataFrame({
    "Product": ["SmartTV", "PenDrive", "Speaker", "Earphone"],
    "Opening_Stock": [300, 700, 1200, 1500],
    "Closing_Stock": [200, 500, 1000, 900]
})

# Simple numeric condition
print("Products with Opening_Stock > 800:")
print(dataFrame.query('Opening_Stock > 800'))

print("\nProducts with exact Closing_Stock value:")
print(dataFrame.query('Closing_Stock == 1000'))

print("\nProducts containing 'ar' in name:")
print(dataFrame.query('Product.str.contains("ar")'))

Products with Opening_Stock > 800:
   Product  Opening_Stock  Closing_Stock
2   Speaker           1200           1000
3  Earphone           1500            900

Products with exact Closing_Stock value:
  Product  Opening_Stock  Closing_Stock
2  Speaker           1200           1000

Products containing 'ar' in name:
   Product  Opening_Stock  Closing_Stock
2   Speaker           1200           1000
3  Earphone           1500            900

Conclusion

The query() method provides a readable way to filter DataFrame rows using string expressions. It supports numeric comparisons, string operations, and complex conditions with logical operators.