Pandas Filtering and Conditional Query

Data filtering is one of the most common operations in data analysis. Pandas provides rich conditional query functionality that lets you filter data based on various conditions. This section introduces various data filtering methods in detail.

Basic Conditional Filtering

Single Condition Filtering

Example

import pandas as pd

# Create sample data
df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu", "Zhao Liu", "Qian Qi"],
    "Age": [25, 30, 28, 35, 22],
    "City": ["Beijing", "Shanghai", "Guangzhou", "Beijing", "Shenzhen"],
    "Salary": [12000, 15000, 11000, 18000, 9000],
    "Department": ["Tech", "Sales", "Tech", "Operations", "Tech"]
})

print(Raw data:)
print(df)
print()

# Equality filtering
print("City equals Beijing:")
print(df[df["City"] == "Beijing"])
print()

# Inequality filtering
print("City not equal to Beijing:")
print(df[df["City"] != "Beijing"])
print()

# Greater than and less than
print("Salary greater than 12000:")
print(df[df["Salary"] > 12000])
print()

# String condition
print("Name contains 'San':")
print(df[df["Name"].str.contains("San")])

Combining Multiple Conditions

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu", "Zhao Liu", "Qian Qi"],
    "Age": [25, 30, 28, 35, 22],
    "City": ["Beijing", "Shanghai", "Guangzhou", "Beijing", "Shenzhen"],
    "Salary": [12000, 15000, 11000, 18000, 9000],
    "Department": ["Tech", "Sales", "Tech", "Operations", "Tech"]
})

# AND condition: use & operator
print("Beijing and Salary>10000:")
print(df[(df["City"] == "Beijing") & (df["Salary"] > 10000)])
print()

# OR condition: use | operator
print("Tech department or Salary>15000:")
print(df[(df["Department"] == "Tech") | (df["Salary"] > 15000)])
print()

# NOT condition: use ~ operator
print("Not Tech department:")
print(df[~(df["Department"] == "Tech")])
print()

# Complex combination
print("(Beijing and Age>25) or (Shanghai):")
print(df[((df["City"] == "Beijing") & (df["Age"] > 25)) | (df["City"] == "Shanghai")])

When combining multiple conditions, each condition must be enclosed in parentheses, and use&(AND),|(OR) to connect, and you cannot use Python'sand、orkeywords.

isin and where

isin Filtering

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu", "Zhao Liu", "Qian Qi"],
    "City": ["Beijing", "Shanghai", "Guangzhou", "Beijing", "Shenzhen"],
    "Department": ["Tech", "Sales", "Tech", "Operations", "Tech"]
})

# Filter within a list
print("City is Beijing or Shanghai:")
print(df[df["City"].isin(["Beijing", "Shanghai"])])
print()

# Not in the list
print("City is not Beijing or Shanghai:")
print(df[~df["City"].isin(["Beijing", "Shanghai"])])
print()

# Multi-column isin
df2 = pd.DataFrame({
    "City": ["Beijing", "Shanghai"],
    "Department": ["Tech", "Sales"]
})
print("Satisfies both city and department:")
print(df[df[["City", "Department"]].isin(df2).all(axis=1)])

where Condition

Example

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "A": [1, 5, 3, 7],
    "B": [4, 2, 8, 3]
})

# where: keep values that satisfy the condition, set others to NaN or a specified value
print("Values greater than 3 are kept, others set to 0:")
result = df.where(df > 3, 0)
print(result)
print()

# Use another DataFrame as the condition
other = pd.DataFrame({
    "A": [True, False, True, False],
    "B": [False, True, False, True]
})
print("Filter based on the condition DataFrame:")
print(df.where(other, -1))

String Filtering

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu", "Zhao Liu", "Sun Qi"],
    "City": ["Beijing", "Shanghai", "Guangzhou", "Shenzhen", "Hangzhou"],
    "Email": ["zhangsan@email.com", "lisi@email.com", "wangwu@email.com",
             "zhaoliu@email.com", "sunqi@email.com"]
})

# Contains
print("City contains 'Beijing':")
print(df[df["City"].str.contains("Beijing")])
print()

# Starts with / Ends with
print("Email starts with zhang:")
print(df[df["Email"].str.startswith("zhang")])
print()

# Regex match
print("Email username length greater than 4:")
print(df[df["Email"].str.extract(r"(\w+)@")[0].str.len() > 4])
print()

# Multiple pattern matching
print("City starts with Bei, Shang, Guang, or Shen:")
print(df[df["City"].str.contains("^Bei|^Shang|^Guang|^Shen")])

Numeric Range Filtering

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu", "Zhao Liu", "Qian Qi"],
    "Age": [25, 30, 28, 35, 22],
    "Salary": [12000, 15000, 11000, 18000, 9000]
})

# Range filtering
print("Age between 25 and 30 (inclusive):")
print(df[df["Age"].between(25, 30)])
print()

# Quantile filtering
q1 = df["Salary"].quantile(0.25)
q3 = df["Salary"].quantile(0.75)
print(f"Salary between 25% and 75% ({q1}-{q3}):")
print(df[df["Salary"].between(q1, q3)])
print()

# Top/Bottom N rows
print("Top 3 salaries:")
print(df.nlargest(3, "Salary"))
print()

print("Bottom 2 salaries:")
print(df.nsmallest(2, "Salary"))

Time Data Filtering

Example

import pandas as pd

# Create time series data
df = pd.DataFrame({
    "Date": pd.date_range("2024-01-01", periods=10, freq="D"),
    "Sales": [100, 120, 90, 150, 200, 180, 110, 130, 170, 190]
})
df = df.set_index("Date")

print("Time series data:")
print(df)
print()

# Filter by date
print("Data for 2024-01-05:")
print(df.loc["2024-01-05"])
print()

# Date range filtering
print("From 2024-01-03 to 2024-01-07:")
print(df.loc["2024-01-03":"2024-01-07"])
print()

# Use truncate (more efficient slicing)
print("truncate filtering:")
print(df.truncate(before="2024-01-03", after="2024-01-07"))

query Method

queryThe method provides a more concise SQL-style filtering approach.

Example

import pandas as pd

df = pd.DataFrame({
    "FullName": ["Zhang San", "Li Si", "Wang Wu", "Zhao Liu"],
    "Age": [25, 30, 28, 35],
    "City": ["Beijing", "Shanghai", "Guangzhou", "Beijing"],
    "Salary": [12000, 15000, 11000, 18000]
})

# Use the query method
print("Age>26 and City='Beijing':")
print(df.query("Age > 26 and City == 'Beijing'"))
print()

# Using variables
min_age = 26
city = "Beijing"
print(f"Age>{min_age} and City={city}:")
print(df.query("Age > @min_age and City == @city"))
print()

# When column names have spaces, use backticks
df2 = df.rename(columns={"FullName": "Full Name"})
print("When a column name has a space:")
print(df2.query("`Full Name` == 'Zhang San'"))

Common Issues

1. Error in combining conditions

When filtering with multiple conditions, use parentheses to clarify precedence, and use&、|rather thanand、or。

2. Strings contain null values

Usestr.containsBefore [using], null values need to be handled:df[df["列"].str.contains("x", na=False)]

3. Index filtering

If the DataFrame has a custom index, be aware oflocandilocthe difference.

For complex multi-condition queries,queryThe syntax of this method is closer to natural language, making the code more readable. When the data volume is large,locwhen combined with boolean arrays, it is usuallyqueryfaster.

Other extensions