Advanced Data Filtering

import pandas as pd
import numpy as np
 
# Load data
DATA_PATH = '/data/'
 
orders = pd.read_csv(DATA_PATH + 'src_orders.csv', parse_dates=['created_at'])
order_items = pd.read_csv(DATA_PATH + 'src_order_items.csv')
products = pd.read_csv(DATA_PATH + 'src_products.csv')
users = pd.read_csv(DATA_PATH + 'src_users.csv')
 
# Merge data for analysis
df = order_items.merge(products, on='product_id').merge(
    orders[['order_id', 'user_id', 'created_at', 'status']], on='order_id'
).merge(users[['user_id', 'age', 'gender', 'state', 'country']], on='user_id')
 
print(f"Data loaded: {len(df):,} rows")
print(f"\nColumn list: {df.columns.tolist()}")

import pandas as pd
 
# Traditional method - complex and long
result1 = df[(df['age'] > 40) & (df['sale_price'] > 50)]
print(f"Traditional method: {len(result1):,} rows")
 
# query() method - much cleaner!
result2 = df.query('age > 40 and sale_price > 50')
print(f"query() method: {len(result2):,} rows")

# String comparison
result = df.query('gender == "F" and country == "United States"')
print(f"Female + United States: {len(result):,} rows")
 
# Combining multiple conditions
result2 = df.query('gender == "M" and age > 50 and department == "Men"')
print(f"Male + Over 50 + Men department: {len(result2):,} rows")

# Reference external variables with @
min_price = 100
max_age = 35
target_country = 'United States'
 
result = df.query('sale_price >= @min_price and age < @max_age and country == @target_country')
print(f"Conditions: price>={min_price}, age<{max_age}, country={target_country}")
print(f"Result: {len(result):,} rows")

# One of multiple values
result = df.query('country in ["United States", "Germany", "France"]')
print(f"Filtering 3 countries: {len(result):,} rows")
 
# not in - exclude
result2 = df.query('department not in ["Women", "Men"]')
print(f"Excluding Women, Men (Kids only): {len(result2):,} rows")

# Select specific brands only
target_brands = ['Nike', 'Adidas', 'Calvin Klein']
result = df[df['brand'].isin(target_brands)]
 
print(f"Selected brands: {target_brands}")
print(f"Result: {len(result):,} rows")
print(f"\nCount by brand:")
print(result['brand'].value_counts())

# Specific categories AND specific countries
categories = ['Jeans', 'Sweaters', 'Accessories']
countries = ['United States', 'Germany']
 
result = df[
    df['category'].isin(categories) &
    df['country'].isin(countries)
]
print(f"Conditions: categories={categories}")
print(f"           countries={countries}")
print(f"Result: {len(result):,} rows")

# Use ~ operator for opposite condition
exclude_brands = ['Nike', 'Adidas']
result = df[~df['brand'].isin(exclude_brands)]
 
print(f"Excluded brands: {exclude_brands}")
print(f"Result: {len(result):,} rows")
print(f"Remaining brand types: {result['brand'].nunique()}")

# Traditional method - long and redundant
result1 = df[(df['age'] >= 25) & (df['age'] <= 35)]
print(f"Traditional method (25<=age<=35): {len(result1):,} rows")
 
# between() method - concise!
result2 = df[df['age'].between(25, 35)]
print(f"between() method (25<=age<=35): {len(result2):,} rows")

# Comparing inclusive options
print("=== Comparing inclusive options ===")
 
result1 = df[df['sale_price'].between(50, 100, inclusive='both')]
print(f"50 <= price <= 100: {len(result1):,} rows")
 
result2 = df[df['sale_price'].between(50, 100, inclusive='left')]
print(f"50 <= price < 100: {len(result2):,} rows")
 
result3 = df[df['sale_price'].between(50, 100, inclusive='right')]
print(f"50 < price <= 100: {len(result3):,} rows")
 
result4 = df[df['sale_price'].between(50, 100, inclusive='neither')]
print(f"50 < price < 100: {len(result4):,} rows")

# Age 20-40, price 30-150, female
result = df[
    df['age'].between(20, 40) &
    df['sale_price'].between(30, 150) &
    (df['gender'] == 'F')
]
 
print(f"Combined condition result: {len(result):,} rows")
print(f"Average age: {result['age'].mean():.1f} years")
print(f"Average price: ${result['sale_price'].mean():.2f}")

# Brand names containing 'Calvin'
result = df[df['brand'].str.contains('Calvin', na=False)]
print(f"Brands containing 'Calvin': {len(result):,} rows")
print(f"Matching brands: {result['brand'].unique()[:5]}")
 
# Multiple patterns (regex OR)
result2 = df[df['brand'].str.contains('Nike|Adidas', na=False)]
print(f"\n'Nike' or 'Adidas': {len(result2):,} rows")

# Categories starting with 'S'
result = df[df['category'].str.startswith('S', na=False)]
print(f"Categories starting with 'S': {len(result):,} rows")
print(f"Matching categories: {result['category'].unique()}")
 
# Categories ending with 's'
result2 = df[df['category'].str.endswith('s', na=False)]
print(f"\nCategories ending with 's': {len(result2):,} rows")
print(f"Matching categories: {result2['category'].unique()}")

# Brand names with 5 or fewer characters
result = df[df['brand'].str.len() <= 5]
print(f"Brand names with 5 or fewer characters: {len(result):,} rows")
print(f"Matching brands: {result['brand'].unique()}")
 
# Category names with 8 or more characters
result2 = df[df['category'].str.len() >= 8]
print(f"\nCategory names with 8 or more characters: {len(result2):,} rows")
print(f"Matching categories: {result2['category'].unique()}")