Pandas: GroupBy, Merge, Window

The Core Tools of Tabular Feature Engineering

In machine learning data preparation, tabular feature engineering relies heavily on 3 Pandas Patterns:

1. GroupBy: Aggregating data across categorical subgroups.
2. Merge: Joining distinct DataFrames on relational key columns.
3. Rolling Windows: Computing moving averages and temporal deltas.

Raw DataFrames ──► [ GroupBy / Merge / Rolling Windows ] ──► Machine Learning Feature Matrix

1. Pandas groupby() and transform()

df.groupby('category') splits data into groups, applies functions, and combines results.

Aggregate vs Transform

• .agg(): Collapses groups into a smaller summary DataFrame (like SQL GROUP BY).
• .transform(): Computes group statistics and broadcasts them back to the original DataFrame shape!

import pandas as pd

# Compute user average purchase amount and add as a new feature column
df['user_avg_amount'] = df.groupby('user_id')['amount'].transform('mean')

# Compute multiple aggregations simultaneously
user_features = df.groupby('user_id').agg(
    total_spend=('amount', 'sum'),
    avg_spend=('amount', 'mean'),
    purchase_count=('transaction_id', 'count'),
)

2. Relational Joins using pd.merge()

Combine user activity logs with user profile features:

# Inner, Left, Right, or Outer Joins
merged_df = pd.merge(
    left=transactions_df,
    right=user_profiles_df,
    on='user_id',
    how='left',  # Retain all transaction records!
)

• Left Join (how='left'): Standard in feature engineering to preserve all primary target event rows while enriching with metadata.

3. Rolling Time-Series Windows (.rolling())

Compute temporal features over continuous sliding windows:

# Sort by time first to ensure temporal order
df = df.sort_values(by=['user_id', 'transaction_time'])

# Compute 7-day rolling average spend per user
df['rolling_7d_avg'] = df.groupby('user_id')['amount'].transform(
    lambda x: x.rolling(window=7, min_periods=1).mean()
)

# Lag feature: Get amount of previous transaction
df['prev_amount'] = df.groupby('user_id')['amount'].shift(1)

Essential Best Practices

1. Always Sort Before Rolling or Shifting: Rolling and shift operations depend on exact row order. Sort by timestamp first!
2. Avoid apply() for Standard Aggregations: df.groupby().apply(custom_func) is slow because it executes Python loops. Use built in vector strings like 'mean', 'std', or 'count'.

Say this out loud

Pandas GroupBy, Merge, and Rolling Window functions construct tabular features. GroupBy transform broadcasts group statistics back to original DataFrame shapes. Merge enriches event logs using relational join keys. Rolling and shift functions compute temporal moving averages and lag features over sorted time series rows.

Followups to expect

1. What is the difference between pd.concat() and pd.merge()? pd.concat() stacks DataFrames vertically (axis=0) or side-by-side horizontally (axis=1) by index. pd.merge() joins DataFrames relationally based on column values.
2. What is pd.pivot_table()? Reshaping long format data into wide format matrices, aggregating values across categorical row and column index dimensions.