# CREATE SAMPLE DATAFRAME — Simulate 100K Sales Records
np.random.seed(42)
n = 100_000
df = pd.DataFrame({
'transaction_id': range(1, n + 1),
'product_category': np.random.choice(
['Electronics', 'Clothing', 'Home & Kitchen', 'Books', 'Sports'],
size=n,
p=[0.3, 0.25, 0.2, 0.15, 0.1] # Uneven probabilities
),
'region': np.random.choice(
['North', 'South', 'East', 'West'],
size=n
),
'price': np.round(np.random.uniform(10.0, 500.0, size=n), 2),
'quantity': np.random.randint(1, 6, size=n),
'discount_pct': np.random.choice([0.0, 0.05, 0.1, 0.15, 0.2], size=n, p=[0.5, 0.2, 0.15, 0.1, 0.05])
})
# Create a flag column
df['is_high_value'] = (df['price'] > 300).astype(int)
print(" Original DataFrame Info:")
print(df.info())
print(f"\nMemory usage: {df.memory_usage(deep=True).sum() / 1024**2:.2f} MB")
print("\n" + "="*70 + "\n")
# 1. Use eval() for complex expressions (avoids intermediate copies)
# Calculate total revenue and net revenue in one efficient step
df = df.eval('''
total_revenue = price * quantity
discount_amount = total_revenue * discount_pct
net_revenue = total_revenue - discount_amount
''')
print(" After using .eval() to create 3 new columns:")
print(df[['price', 'quantity', 'discount_pct', 'total_revenue', 'net_revenue']].head(3))
print("\n" + "="*70 + "\n")
# 🔍 2. Use query() for filtering (cleaner syntax + often faster)
# Filter: High-value items in Electronics or Books, with discount, in East or West
df_filtered = df.query(
"is_high_value == 1 and "
"product_category in ['Electronics', 'Books'] and "
"discount_pct > 0 and "
"region in ['East', 'West']"
)
print(f" After .query(): {len(df_filtered):,} rows match criteria")
print(df_filtered[['product_category', 'region', 'price', 'discount_pct']].head())
print("\n" + "="*70 + "\n")
# 3. Convert to category dtype for low-cardinality string columns
# Check unique values first
print(" Unique values before conversion:")
for col in ['product_category', 'region']:
print(f" {col}: {df[col].nunique()} unique values → {list(df[col].unique())}")
# Convert to category
df['product_category'] = df['product_category'].astype('category')
df['region'] = df['region'].astype('category')
df['is_high_value'] = df['is_high_value'].astype('category') # Also good for binary flags
print("\n After converting to 'category' dtype:")
print(df.dtypes)
print(f"\nNew memory usage: {df.memory_usage(deep=True).sum() / 1024**2:.2f} MB")
print(f"Memory saved: {df.memory_usage(deep=True).sum() / 1024**2:.2f} MB vs original")
# Show memory usage per column
print("\n Memory usage per column (after optimization):")
mem_usage = df.memory_usage(deep=True) / 1024
for col, mem_kb in mem_usage.items():
print(f" {col}: {mem_kb:.1f} KB")
print("\n" + "="*70 + "\n")
# Combine all optimizations in a realistic workflow
print(" REALISTIC WORKFLOW: Filter + Compute + GroupBy")
result = (
df
.query("net_revenue > 1000 and product_category != 'Books'")
.eval('profit_margin = (net_revenue - 50) / net_revenue') # Assume $50 fixed cost
.groupby(['product_category', 'region'], observed=True) # observed=True for categories
.agg(
avg_net_revenue=('net_revenue', 'mean'),
total_transactions=('transaction_id', 'count'),
avg_profit_margin=('profit_margin', 'mean')
)
.round(2)
.reset_index()
)
print("Final Aggregated Result:")
print(result)
