Pandas过滤特定值的常用方法与实战技巧

import pandas as pd
import numpy as np
 
# 创建示例数据
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
    'age': [25, 30, 35, 28, 32],
    'salary': [50000, 60000, 70000, 55000, 65000],
    'department': ['IT', 'HR', 'IT', 'Finance', 'HR']
})
 
# 单条件过滤
adults = df[df['age'] >= 30]
print("年龄30岁及以上的员工：")
print(adults)
 
# 多条件过滤（使用&和|）
it_high_earners = df[(df['department'] == 'IT') & (df['salary'] > 55000)]
print("\nIT部门高薪员工：")
print(it_high_earners)

# 使用between进行范围过滤
age_range = df[df['age'].between(28, 35)]
print("年龄在28-35岁之间的员工：")
print(age_range)
 
# 使用isin过滤多个值
target_departments = df[df['department'].isin(['IT', 'HR'])]
print("\nIT和HR部门员工：")
print(target_departments)
 
# 使用isnull和notnull处理缺失值
# 假设有些数据缺失
df.loc[2, 'salary'] = np.nan
complete_data = df[df['salary'].notnull()]
print("\n薪资信息完整的员工：")
print(complete_data)

# 使用query进行简单过滤
high_earners = df.query('salary > 60000')
print("高薪员工：")
print(high_earners)
 
# 多条件查询
experienced_it = df.query('department == "IT" and age >= 30')
print("\n经验丰富的IT员工：")
print(experienced_it)
 
# 使用变量
min_salary = 55000
dynamic_query = df.query(f'salary >= {min_salary}')
print(f"\n薪资不低于{min_salary}的员工：")
print(dynamic_query)

# 使用in操作符
departments = ['IT', 'Finance']
dept_query = df.query('department in @departments')
print("指定部门员工：")
print(dept_query)
 
# 使用字符串方法
name_pattern = df.query('name.str.startswith("A")', engine='python')
print("\n名字以A开头的员工：")
print(name_pattern)
 
# 使用数学函数
age_stats = df.query('age > age.mean()', engine='python')
print("年龄高于平均水平的员工：")
print(age_stats)

# 创建包含文本数据的DataFrame
text_df = pd.DataFrame({
    'email': ['alice@company.com', 'bob@gmail.com', 'charlie@company.com', 'david@yahoo.com'],
    'description': ['Senior Software Engineer', 'HR Manager', 'Data Scientist', 'Financial Analyst'],
    'status': ['active', 'inactive', 'active', 'pending']
})
 
# 使用str.contains进行模糊匹配
company_emails = text_df[text_df['email'].str.contains('@company.com')]
print("公司邮箱用户：")
print(company_emails)
 
# 使用正则表达式
manager_roles = text_df[text_df['description'].str.contains('Manager|Engineer', case=False, na=False)]
print("\n管理或工程职位：")
print(manager_roles)
 
# 多个关键词匹配
keywords = ['Software', 'Data']
pattern = '|'.join(keywords)
tech_roles = text_df[text_df['description'].str.contains(pattern, case=False)]
print("\n技术相关职位：")
print(tech_roles)

# 精确匹配
active_users = text_df[text_df['status'] == 'active']
print("活跃用户：")
print(active_users)
 
# 忽略大小写的精确匹配
case_insensitive = text_df[text_df['status'].str.lower() == 'active']
print("\n活跃用户（忽略大小写）：")
print(case_insensitive)

# 创建销售数据
sales_df = pd.DataFrame({
    'product': ['A', 'B', 'C', 'D', 'E', 'F'],
    'quantity': [100, 150, 80, 200, 120, 90],
    'price': [25.5, 30.0, 15.75, 45.0, 35.25, 20.0],
    'revenue': [2550, 4500, 1260, 9000, 4230, 1800]
})
 
# 使用比较运算符
high_quantity = sales_df[sales_df['quantity'] > 100]
print("高销量产品：")
print(high_quantity)
 
# 使用between进行范围过滤
mid_price = sales_df[sales_df['price'].between(20, 35)]
print("\n中等价位产品：")
print(mid_price)
 
# 使用clip处理异常值
clipped_revenue = sales_df['revenue'].clip(lower=1000, upper=5000)
print("\n收入数据（去除异常值）：")
print(clipped_revenue)

# 基于统计信息的过滤
mean_revenue = sales_df['revenue'].mean()
above_average = sales_df[sales_df['revenue'] > mean_revenue]
print(f"收入高于平均值({mean_revenue:.2f})的产品：")
print(above_average)
 
# 使用分位数
high_performers = sales_df[sales_df['revenue'] > sales_df['revenue'].quantile(0.75)]
print("\n收入前25%的产品：")
print(high_performers)
 
# 标准差过滤
std_threshold = sales_df['quantity'].mean() + 2 * sales_df['quantity'].std()
outliers = sales_df[sales_df['quantity'] > std_threshold]
print("\n销量异常高的产品：")
print(outliers)

# 创建时间序列数据
date_df = pd.DataFrame({
    'date': pd.date_range('2024-01-01', periods=10, freq='D'),
    'sales': [100, 120, 80, 150, 200, 90, 110, 130, 170, 140],
    'category': ['A', 'B', 'A', 'C', 'B', 'A', 'C', 'B', 'A', 'C']
})
 
# 设置日期为索引
date_df = date_df.set_index('date')
 
# 按日期范围过滤
january_data = date_df['2024-01-01':'2024-01-05']
print("1月上旬销售数据：")
print(january_data)
 
# 按星期过滤
weekend_data = date_df[date_df.index.dayofweek.isin([5, 6])]
print("\n周末销售数据：")
print(weekend_data)
 
# 按月份过滤
specific_month = date_df[date_df.index.month == 1]
print("\n1月份销售数据：")
print(specific_month)

# 创建综合数据集
complex_df = pd.DataFrame({
    'customer_id': range(1, 11),
    'age': [25, 35, 45, 30, 28, 52, 38, 42, 31, 29],
    'income': [50000, 75000, 100000, 60000, 55000, 120000, 80000, 95000, 65000, 58000],
    'credit_score': [700, 750, 800, 720, 680, 820, 760, 790, 710, 690],
    'loan_amount': [200000, 300000, 400000, 250000, 180000, 500000, 350000, 450000, 280000, 220000],
    'employment_years': [2, 8, 15, 5, 3, 20, 10, 12, 6, 4]
})
 
# 复合条件过滤
good_customers = complex_df[
    (complex_df['credit_score'] >= 750) & 
    (complex_df['income'] >= 70000) & 
    (complex_df['employment_years'] >= 5)
]
print("优质客户（高信用评分、高收入、工作稳定）：")
print(good_customers)
 
# 使用query进行复合查询
loan_candidates = complex_df.query(
    'credit_score >= 700 and income >= 60000 and age <= 45 and employment_years >= 3'
)
print("\n贷款候选人：")
print(loan_candidates)
 
# 使用条件组合
conditions = [
    complex_df['credit_score'] >= 750,
    complex_df['income'] >= 80000,
    complex_df['employment_years'] >= 10
]
premium_customers = complex_df[np.all(conditions, axis=0)]
print("\n高端客户：")
print(premium_customers)

import time
 
# 创建大型数据集进行性能测试
large_df = pd.DataFrame({
    'id': range(1000000),
    'value': np.random.randn(1000000),
    'category': np.random.choice(['A', 'B', 'C', 'D'], 1000000)
})
 
# 方法1：布尔索引
def method1():
    return large_df[large_df['value'] > 0]
 
# 方法2：query方法
def method2():
    return large_df.query('value > 0')
 
# 性能比较
print("性能比较：")
for method_name, method in [('布尔索引', method1), ('query方法', method2)]:
    start_time = time.time()
    result = method()
    end_time = time.time()
    print(f"{method_name}: {end_time - start_time:.4f}秒")

# 优化示例
optimized_df = large_df.copy()
optimized_df['category'] = optimized_df['category'].astype('category')
 
# 使用索引优化
indexed_df = optimized_df.set_index('category')
category_a = indexed_df.loc['A']  # 更快的过滤
 
print("优化后的数据类型：")
print(optimized_df.dtypes)
print(f"\n内存使用量：{optimized_df.memory_usage(deep=True).sum() / 1024**2:.2f} MB")

# 创建电商用户行为数据
np.random.seed(42)
dates = pd.date_range('2024-01-01', '2024-01-31', freq='H')
ecommerce_df = pd.DataFrame({
    'user_id': np.random.choice(range(1000, 2000), len(dates)),
    'timestamp': dates,
    'product_category': np.random.choice(['Electronics', 'Clothing', 'Books', 'Home', 'Sports'], len(dates)),
    'price': np.random.uniform(10, 500, len(dates)),
    'quantity': np.random.randint(1, 5, len(dates)),
    'user_age': np.random.randint(18, 65, len(dates)),
    'device_type': np.random.choice(['Mobile', 'Desktop', 'Tablet'], len(dates))
})
 
# 计算总收入
ecommerce_df['total_revenue'] = ecommerce_df['price'] * ecommerce_df['quantity']
 
print("电商数据概览：")
print(ecommerce_df.head())
print(f"\n数据集大小：{ecommerce_df.shape}")
 
# 分析1：高价值用户（购买金额超过1000元）
high_value_users = ecommerce_df.groupby('user_id')['total_revenue'].sum()
high_value_user_ids = high_value_users[high_value_users > 1000].index
 
high_value_transactions = ecommerce_df[ecommerce_df['user_id'].isin(high_value_user_ids)]
print(f"\n高价值用户交易记录：{len(high_value_transactions)}条")
print(high_value_transactions.groupby('product_category')['total_revenue'].sum().sort_values(ascending=False))
 
# 分析2：周末购买行为
weekend_data = ecommerce_df[ecommerce_df['timestamp'].dt.dayofweek.isin([5, 6])]
print(f"\n周末购买行为分析：")
print(f"周末总交易额：{weekend_data['total_revenue'].sum():.2f}")
print(f"周末平均订单价值：{weekend_data['total_revenue'].mean():.2f}")
 
# 分析3：移动端vs桌面端表现
mobile_data = ecommerce_df[ecommerce_df['device_type'] == 'Mobile']
desktop_data = ecommerce_df[ecommerce_df['device_type'] == 'Desktop']
 
print(f"\n设备类型对比：")
print(f"移动端平均订单价值：{mobile_data['total_revenue'].mean():.2f}")
print(f"桌面端平均订单价值：{desktop_data['total_revenue'].mean():.2f}")
 
# 分析4：年轻用户偏好（18-30岁）
young_users = ecommerce_df[ecommerce_df['user_age'].between(18, 30)]
young_user_categories = young_users.groupby('product_category')['total_revenue'].sum().sort_values(ascending=False)
print(f"\n年轻用户类别偏好：")
print(young_user_categories)
 
# 分析5：综合过滤 - 找出最有价值的用户群体
valuable_users = ecommerce_df[
    (ecommerce_df['user_age'].between(25, 45)) &
    (ecommerce_df['device_type'] == 'Mobile') &
    (ecommerce_df['timestamp'].dt.hour.between(18, 22)) &  # 晚上6点到10点
    (ecommerce_df['total_revenue'] > 100)
]
 
print(f"\n高价值用户群体分析：")
print(f"符合条件的交易数：{len(valuable_users)}")
print(f"平均交易金额：{valuable_users['total_revenue'].mean():.2f}")
print(f"热门产品类别：{valuable_users['product_category'].value_counts().head()}")

# 错误做法 - 会触发警告
filtered_df = df[df['age'] > 30]
filtered_df['new_column'] = 'value'  # 触发警告
 
# 正确做法1 - 使用copy()
filtered_df = df[df['age'] > 30].copy()
filtered_df['new_column'] = 'value'
 
# 正确做法2 - 使用loc
df.loc[df['age'] > 30, 'new_column'] = 'value'

# 错误做法
 df[df['age'] > 30]['salary'] = 50000  # 不会生效
 
# 正确做法
df.loc[df['age'] > 30, 'salary'] = 50000

# 确保数据类型一致
df['age'] = pd.to_numeric(df['age'], errors='coerce')
df['salary'] = pd.to_numeric(df['salary'], errors='coerce')
 
# 过滤前先检查数据类型
print(df.dtypes)