Data Wrangling with pandas

Data Wrangling with pandas

Why It Matters

Most project time goes into cleaning and reshaping. Good wrangling keeps logic transparent and reproducible.

Load & Inspect

import pandas as pd
df = pd.read_csv('raw/users.csv', dtype={'user_id':'int32'})
print(df.head())
print(df.info())

Selecting & Filtering

active = df[df.status == 'active']               # boolean mask
cols = df[['user_id','plan','signup_date']]      # column subset
recent = df.query('signup_date >= "2024-01-01"')

Avoid chained indexing like df[df.a>0].b = ... (may warn). Use .loc:

df.loc[df.a > 0, 'b'] = 1

Handling Missing Data

missing_ratio = df.isna().mean().sort_values(ascending=False)
# Strategy example
df['age'] = df['age'].fillna(df['age'].median())
df = df.drop(columns=['deprecated_flag'])

Guidelines: - Impute numerical with median when skewed, mean when roughly symmetric - Impute categorical with explicit token like "Unknown" - Document every irreversible choice

String & Datetime Operations

df['signup_date'] = pd.to_datetime(df['signup_date'])
df['signup_month'] = df['signup_date'].dt.to_period('M')
df['email_domain'] = df['email'].str.split('@').str[-1]

Combining Data

plans = pd.read_csv('raw/plans.csv')
merged = df.merge(plans, on='plan_id', how='left')  # left join

Choose join type deliberately: inner (intersection), left (keep primary), outer (union), anti (use boolean filtering snippet).

Aggregation & Grouping

engagement = (df
  .groupby('signup_month')
  .agg(users=('user_id','nunique'), avg_sessions=('sessions','mean'))
  .reset_index())

Feature Construction Example

from numpy import log1p
features = (df
  .assign(account_age_days=(pd.Timestamp('today') - df.signup_date).dt.days,
          sessions_per_day=lambda d: d.sessions / (d.account_age_days.clip(lower=1)),
          log_spend=lambda d: log1p(d.spend))
  [['user_id','account_age_days','sessions_per_day','log_spend']])

Efficient Patterns

• Vectorize operations; avoid row-wise Python loops
• Downcast numeric types (astype('float32')) for large frames
• Cache expensive intermediate results

flowchart LR
  A[Load Raw CSV] --> B[Validate Schema]
  B --> C[Clean & Impute]
  C --> D[Feature Engineering]
  D --> E[Aggregate / Join]
  E --> F[Export Features]
  F --> G[Model Training]

Performance Concern	Symptom	Tuning Lever
Excess memory	Process killed	Downcast dtypes, chunked read
Slow joins	Long wall time	Ensure join keys indexed / sorted
Chained ops overhead	Many intermediate frames	Use method chaining; assign once
Unnecessary object dtype	High memory + slow ops	Convert to categorical/int

Reproducibility Tips

1. Keep all wrangling in scripts, not ad-hoc notebook cells
2. Capture library versions (pip freeze > requirements.txt)
3. Save raw and processed snapshots (never overwrite raw)

Common Pitfalls

Pitfall	Fix
Silent type coercion	Explicit dtype on load
Chained indexing warnings	Use .loc for assignment
Mixing cleaning + modeling in one notebook	Separate 01_load_clean.py, 02_model.py
Forgetting time zones	Normalize to UTC early
Dropping rows too aggressively	Quantify impact (% removed)

Mini Exercise

1. Load two CSVs (users, events).
2. Join and compute sessions per user.
3. Create top 5 domains by active users.
4. Output a features CSV with 5 engineered columns.

Checklist

• Types explicit
• Missingness strategy documented
• Joins validated (row counts sane)
• Feature columns clearly named
• Raw data preserved

Resources

• pandas docs (Indexing, Merge, GroupBy)
• "Modern Pandas" by Tom Augspurger