Using SQL Syntax to Query Pandas DataFrames with the query Method

Goal : Learn how to use SQL‑style queries on a Pandas DataFrame.

Learning Content : The Pandas query method and how to write SQL‑like expressions for DataFrame filtering.

Code Example :

import pandas as pd

# Create example dataset
data = {
    '姓名': ['张三', '李四', '王五', '赵六', '孙七'],
    '部门': ['销售部', '市场部', '技术部', '财务部', '人力资源部'],
    '销售额': [120, 150, 130, 140, 160],
    '成本': [80, 90, 100, 110, 120]
}

df = pd.DataFrame(data)
print(f"示例数据集: \n{df}")

# Basic query
# 查询销售额大于 130 的记录
df_query = df.query('销售额 > 130')
print(f"查询销售额大于 130 的记录: \n{df_query}")

# Multiple conditions
# 查询销售额大于 130 且成本小于 110 的记录
df_query = df.query('销售额 > 130 and 成本 < 110')
print(f"查询销售额大于 130 且成本小于 110 的记录: \n{df_query}")

# OR condition
# 查询销售额大于 130 或成本小于 100 的记录
df_query = df.query('销售额 > 130 or 成本 < 100')
print(f"查询销售额大于 130 或成本小于 100 的记录: \n{df_query}")

# IN condition
# 查询部门为 '销售部' 或 '市场部' 的记录
df_query = df.query("部门 in ('销售部', '市场部')")
print(f"查询部门为 '销售部' 或 '市场部' 的记录: \n{df_query}")

# NOT IN condition
# 查询部门不是 '销售部' 或 '市场部' 的记录
df_query = df.query("部门 not in ('销售部', '市场部')")
print(f"查询部门不是 '销售部' 或 '市场部' 的记录: \n{df_query}")

# Using a variable
min_sales = 130
df_query = df.query('销售额 > @min_sales')
print(f"查询销售额大于 {min_sales} 的记录: \n{df_query}")

# Using isin method
departments = ['销售部', '市场部']
df_query = df[df['部门'].isin(departments)]
print(f"查询部门为 {departments} 的记录: \n{df_query}")

# Using not in method
departments = ['销售部', '市场部']
df_query = df[~df['部门'].isin(departments)]
print(f"查询部门不是 {departments} 的记录: \n{df_query}")

Practice : Apply the same SQL‑style queries on the example DataFrame to reinforce learning.

Summary : After completing the exercises, you should be able to use Pandas' query method to perform SQL‑like filtering with various logical operators and conditions, enabling concise and powerful data selection in Python projects.