Pandas Fundamentals

The interview approach

1. Restate the problem. Ask about input shape (DataFrame vs list of dicts), expected output shape, edge cases (empty input, NaNs, ties).
2. State your approach: "I'm going to groupby A, aggregate B, then merge back." Get a nod.
3. Write the vectorized version first. Reach for .apply() only with a comment about why it's the right call.
4. Mention the alternative: "Could also push to SQL or use DuckDB on this DataFrame at scale." This is the senior tell.
5. Trace 2 example rows. Catches off-by-ones.

Groupby patterns

Basic aggregates

import pandas as pd

# Multiple aggregations per group, named outputs
result = df.groupby('customer_id').agg(
    n_sessions=('session_id', 'count'),
    total_revenue=('revenue', 'sum'),
    first_session=('started_at', 'min'),
    last_session=('started_at', 'max'),
)

# Aggregate different columns differently in one call
result = df.groupby('tier').agg({
    'revenue': 'sum',
    'session_id': 'nunique',
    'started_at': ['min', 'max'],
})

Filter groups (HAVING-equivalent)

# Keep customers with at least 5 sessions
result = df.groupby('customer_id').filter(lambda g: len(g) >= 5)

# Equivalent, faster — avoids the Python lambda
counts = df.groupby('customer_id').size()
keep = counts[counts >= 5].index
result = df[df['customer_id'].isin(keep)]

Transform: add a group-level value back to every row

# Each row's revenue as a fraction of the customer's total
df['share_of_total'] = df['revenue'] / df.groupby('customer_id')['revenue'].transform('sum')

Pivot

wide = df.pivot_table(
    index='customer_id',
    columns='tier',
    values='gpu_hours',
    aggfunc='sum',
    fill_value=0,
)

Merge gotchas

• Default how='inner' — always specify how explicitly. 'left' is what you usually want for "enrich the left table."
• Row duplication: if your join key isn't unique on the right side, you'll get more rows than you started with. Always check row count before and after.
• Column-name collisions: when both sides have a column not in the join key, pandas appends _x and _y. Pre-rename or use suffixes.
• validate argument: df.merge(other, on='key', how='left', validate='m:1') raises if the assumption is wrong. Use it.

enriched = sessions.merge(
    customers[['customer_id', 'plan', 'country']],
    on='customer_id',
    how='left',
    validate='m:1',  # raises if customer_id isn't unique in customers
)
assert len(enriched) == len(sessions)  # row count sanity

The asof merge

For "what was the value of X at the time of event Y" — point-in-time joins.

sessions = sessions.sort_values('started_at')
price_history = price_history.sort_values('changed_at')

result = pd.merge_asof(
    sessions, price_history,
    left_on='started_at', right_on='changed_at',
    by='tier',                  # match within the same tier
    direction='backward',       # most-recent price at or before started_at
    tolerance=pd.Timedelta('30 days'),  # don't match older than 30 days
)

Window-function equivalents

Time-series ops

Resample

For "aggregate by week" / "fill missing days" / "downsample to hourly":

df['started_at'] = pd.to_datetime(df['started_at'])
weekly = (df.set_index('started_at')
            .groupby('customer_id')
            .resample('W-MON')           # ISO weeks
            .agg({'revenue': 'sum', 'session_id': 'count'})
            .reset_index())

Fill missing dates

daily = df.set_index('day').reindex(
    pd.date_range(df['day'].min(), df['day'].max(), freq='D'),
    fill_value=0
)

Date math

• (df['ended_at'] - df['started_at']).dt.total_seconds() / 3600 — gpu-hours.
• df['day'] = df['started_at'].dt.normalize() — strip time component to midnight.
• df['week'] = df['started_at'].dt.to_period('W').dt.start_time — week truncation.

Vectorization

Avoid .apply when possible

Pandas' .apply with a Python function is 10–100× slower than vectorized ops on big DataFrames. Reach for vectorization first.

# ✗ Slow
df['tier_label'] = df.apply(
    lambda r: 'high' if r['gpu_hours'] > 100 else 'low', axis=1)

# ✓ Vectorized
df['tier_label'] = np.where(df['gpu_hours'] > 100, 'high', 'low')

# Multi-branch: np.select
import numpy as np
df['tier_label'] = np.select(
    [df['gpu_hours'] > 1000, df['gpu_hours'] > 100, df['gpu_hours'] > 0],
    ['enterprise', 'pro', 'free'],
    default='inactive'
)

When .apply is the right call

• The function is genuinely complex (multi-line logic, branching, lookups against external data).
• The DataFrame is small (<100k rows) and clarity matters more than speed.
• You're applying along axis=0 (column-wise) to a small number of columns.

Mention DuckDB on big data

For "at scale" follow-ups: DuckDB can query a pandas DataFrame directly with SQL, often faster than pure pandas for groupby/join-heavy work:

import duckdb
result = duckdb.sql("""
    SELECT customer_id, COUNT(*) AS sessions, SUM(revenue) AS total
    FROM df
    WHERE started_at >= '2026-04-01'
    GROUP BY customer_id
""").to_df()

Mentioning this unprompted reads senior — it shows you know when pandas isn't the right tool.

SQL ↔ pandas translation

Edge cases & traps

The SettingWithCopyWarning

df[df['x'] > 0]['y'] = 99 may or may not modify the original DataFrame — pandas warns because the behavior depends on internal optimizations. The fix: df.loc[df['x'] > 0, 'y'] = 99.

NaN comparisons

NaN == NaN is False in pandas (and SQL). Use .isna() for tests; never == NaN.

Integer columns with NaN become floats

If you have a numeric column with any NaN, pandas casts the whole column to float. Use pd.Int64Dtype() (nullable integer) if you need to preserve integer semantics.

Object-dtype Series are slow

Strings stored as object are slow for many operations. pd.StringDtype() or pyarrow-backed strings are faster.

Sorting before groupby

Pandas' groupby sorts groups by default. If you've already sorted, pass sort=False to skip the redundant sort and save time.