Advanced Pandas – 20 Techniques Every Data Scientist Must Know

You know how to filter, group, and merge DataFrames. But advanced Pandas techniques can make your code 10× faster, more readable, and more memory-efficient. This guide covers 20 techniques that separate beginner Pandas users from power users.

1. Method Chaining

# Avoid: intermediate variables pollute namespace
df1 = df.dropna()
df2 = df1[df1['revenue'] > 0]
df3 = df2.assign(profit_margin = df2['profit'] / df2['revenue'])
result = df3.groupby('region')['profit_margin'].mean()

# Better: readable chain
result = (df
    .dropna()
    .query('revenue > 0')
    .assign(profit_margin = lambda d: d['profit'] / d['revenue'])
    .groupby('region')['profit_margin']
    .mean())

2. eval() and query() for Fast Filtering

# query() — readable filter syntax
result = df.query('age > 25 and income > 50000 and city == "Mumbai"')

# eval() — fast expression evaluation (uses numexpr under the hood)
df.eval('profit_margin = profit / revenue * 100', inplace=True)
df.eval('z_score = (value - value.mean()) / value.std()', inplace=True)

3. Categorical Dtype – Massive Memory Savings

# Before: object dtype for low-cardinality string column
print(df['status'].dtype)           # object
print(df['status'].memory_usage())  # 80 bytes per row

# After: categorical
df['status'] = df['status'].astype('category')
print(df['status'].memory_usage())  # ~8 bytes per row (10× savings!)

# Auto-convert all low-cardinality object columns
for col in df.select_dtypes('object'):
    if df[col].nunique() / len(df) < 0.05:  # < 5% unique values
        df[col] = df[col].astype('category')

4. Window Functions

# Rolling statistics
df['7d_avg_revenue']   = df['revenue'].rolling(window=7, min_periods=1).mean()
df['30d_std_revenue']  = df['revenue'].rolling(window=30).std()
df['cumulative_total'] = df['revenue'].cumsum()

# Expanding window (all rows up to current)
df['running_max'] = df['revenue'].expanding().max()

# Exponential weighted (recent values weighted more)
df['ema_revenue'] = df['revenue'].ewm(span=7).mean()

5. Efficient Apply with Vectorization

# SLOW: row-by-row apply
df['risk'] = df.apply(lambda row: 'high' if row['debt'] > row['income'] * 0.5 else 'low', axis=1)

# FAST: vectorized with np.where
import numpy as np
df['risk'] = np.where(df['debt'] > df['income'] * 0.5, 'high', 'low')

# Multiple conditions: np.select
conditions = [
    df['debt'] > df['income'] * 0.7,
    df['debt'] > df['income'] * 0.4,
]
df['risk'] = np.select(conditions, ['very_high', 'high'], default='low')

6. MultiIndex – Hierarchical Data

sales = df.groupby(['region', 'product'])['revenue'].sum()
print(sales['North'])           # all products in North
print(sales['North', 'Widget']) # specific combination

# Cross-section
sales.xs('Widget', level='product')  # Widget across all regions

# Reset index to flatten
sales.reset_index()

7. Merge Strategies

# Indicator column — see where rows came from
merged = pd.merge(df1, df2, on='customer_id', how='outer', indicator=True)
print(merged['_merge'].value_counts())
# left_only, right_only, both

# Fuzzy/approximate join (for slightly mismatched keys)
from rapidfuzz import process
# match each name in df1 to closest in df2

# Merge on nearest timestamp (asof merge)
pd.merge_asof(trades, quotes, on='timestamp', by='ticker', direction='backward')

8. String Methods (.str accessor)

df['email_domain'] = df['email'].str.split('@').str[1]
df['name_upper']   = df['name'].str.upper()
df['has_promo']    = df['description'].str.contains('SALE|PROMO', regex=True, case=False)
df['first_word']   = df['product'].str.extract(r'^(\w+)')

9. pd.cut and pd.qcut – Binning

# Equal-width bins
df['age_group'] = pd.cut(df['age'], bins=[0, 25, 35, 50, 65, 100],
                          labels=['18-25', '26-35', '36-50', '51-65', '65+'])

# Equal-frequency bins (quantile-based)
df['income_quartile'] = pd.qcut(df['income'], q=4, labels=['Q1', 'Q2', 'Q3', 'Q4'])

10. Pivot Tables and Crosstabs

pivot = df.pivot_table(
    values='revenue', index='region', columns='quarter',
    aggfunc={'revenue': ['sum', 'mean']},
    fill_value=0, margins=True)

# Crosstab — frequency of combinations
pd.crosstab(df['region'], df['product_category'], normalize='index')

Performance Tips

Use df.info(memory_usage='deep') to identify memory hogs. Downcast numeric types with pd.to_numeric(df['col'], downcast='integer'). Use chunksize in read_csv() for files larger than RAM. Prefer vectorized NumPy operations over apply() wherever possible. Consider Polars for datasets over 10 million rows — it's 5-10× faster than Pandas for many operations and uses lazy evaluation.

Conclusion

These techniques — method chaining, categorical dtype, vectorization over apply, and proper use of groupby/window functions — can easily make your Pandas code 5-10× faster and more readable simultaneously. Start with the categorical dtype conversion (immediately halves memory for string columns) and eliminate apply() in favour of np.where/np.select — those two changes alone will have the biggest impact on your day-to-day work.