Core Theory

• 1pandas groupby mechanics: groupby returns a DataFrameGroupBy object — a lazy computation that only executes when you call an aggregation (.sum(), .mean(), .agg(), .transform(), .apply()). Key distinction: .agg() reduces each group to a scalar (returns a smaller DataFrame); .transform() returns a DataFrame of the same shape as the input (each row gets the group-level statistic — essential for adding group features without a merge); .apply() applies a user function to each group and can return any shape. Performance: .agg({'col': 'sum'}) is fastest (dispatches to C); .transform(lambda x: x.fillna(x.mean())) is Cython-accelerated; .apply(custom_fn) is slowest (pure Python). For named aggregations in pandas ≥1.0: df.groupby('group').agg(total_spend=('spend', 'sum'), avg_spend=('spend', 'mean')).
• 2Merge and join types: pd.merge(left, right, on='key', how='inner/left/right/outer'). Inner join: only rows where key exists in both — equivalent to SQL INNER JOIN. Left join: all rows from left, matching rows from right, NaN where no match — equivalent to SQL LEFT JOIN. Cross join: pd.merge(left, right, how='cross') creates the Cartesian product — useful for enumerating all user-date combinations. Anti-join (rows in left with NO match in right): left_merge = left.merge(right, on='key', how='left', indicator=True); anti = left_merge[left_merge['_merge'] == 'left_only']. Merge on multiple columns: on=['user_id', 'date']. Merge on inequality: pd.merge_asof() for nearest-match on sorted keys (useful for point-in-time feature lookups). Index merges: df.join(other, on='key') uses the index of `other`.
• 3pivot_table and reshaping: pd.pivot_table(df, values='revenue', index='user_id', columns='product_category', aggfunc='sum', fill_value=0) creates a pivot table — equivalent to a spreadsheet pivot. df.melt(id_vars=['user_id', 'date'], value_vars=['feature_A', 'feature_B'], var_name='feature', value_name='value') unpivots from wide to long format (inverse of pivot). df.stack() pivots the innermost column level to the row index; df.unstack() does the reverse. df.explode('list_column') expands a column of lists into separate rows (one row per list element) — essential for processing JSON arrays from event logs.
• 4Window functions (rolling, expanding, ewm): df['rolling_7d'] = df.groupby('user_id')['revenue'].transform(lambda x: x.rolling(7, min_periods=1).mean()). Rolling computes over a fixed-width window. Expanding computes over all data up to the current row (expanding().mean() = cumulative mean). EWM (exponentially weighted moving average): df['ewma'] = df['value'].ewm(span=7, adjust=False).mean() — assigns exponentially decaying weights to older observations. Key parameter: min_periods — minimum non-NaN values required to return a result. For per-group rolling features: always sort by (user_id, timestamp) before applying rolling to ensure time order within each group. Use .shift(1) after rolling to prevent the current row from being included in its own feature (lag-1 feature).
• 5Time series — resampling and date arithmetic: pd.date_range('2023-01-01', periods=365, freq='D') generates daily timestamps. df.resample('W').sum() resamples to weekly aggregations (like a groupby on time windows). Supported frequencies: 'D' (day), 'W' (week-end), 'MS' (month start), 'QS' (quarter start), 'H' (hour), '15T' (15-minute). Date arithmetic: (df['end_date'] - df['start_date']).dt.days extracts integer day difference. pd.to_datetime() parses strings; df['date'].dt.dayofweek returns 0=Monday, 6=Sunday. Business day offset: pd.tseries.offsets.BusinessDay(n). Time zone handling: df['ts'].dt.tz_localize('UTC').dt.tz_convert('US/Eastern'). The .dt accessor unlocks datetime properties and methods on a Series.
• 6NumPy broadcasting: Broadcasting allows operations on arrays with different shapes without explicit loops. Rules: (1) If arrays have different numbers of dimensions, the shape of the smaller-dimensioned array is padded with 1s on the left. (2) Arrays with size 1 along a dimension are stretched to match the other array's size. Example: a.shape = (3,4), b.shape = (1,4) — b is broadcast to (3,4). a.shape = (3,1), b.shape = (3,4) — a is broadcast to (3,4). Common pattern: compute pairwise distances. X.shape = (n, d). distances = np.sqrt(((X[:, None, :] - X[None, :, :])**2).sum(axis=2)) — X[:, None, :] has shape (n, 1, d), X[None, :, :] has shape (1, n, d), difference is (n, n, d). Vectorization: avoid Python loops over rows. A loop over 1M rows at 1μs/iteration = 1 second; the equivalent NumPy operation = 10ms (100x speedup). Rule: if you're writing 'for i, row in df.iterrows()', think about whether a .apply(), .groupby(), or broadcasting can replace it.
• 7Efficient pandas — avoiding common performance pitfalls: (1) chained indexing anti-pattern: df['A'][mask] = value triggers a SettingWithCopyWarning and may silently fail on a copy. Always use df.loc[mask, 'A'] = value for in-place mutation. (2) .iterrows() is ~100x slower than .apply() which is ~10x slower than vectorized ops. (3) String columns: object dtype stores Python strings — use df['col'].astype('category') for low-cardinality string columns (reduces memory ~10x and speeds up groupby). (4) dtype optimization: use int32 instead of int64 where values fit, float32 instead of float64 for model features. df['col'] = pd.to_numeric(df['col'], downcast='integer'). (5) Avoiding copies: df.copy() creates a full copy; operations that return a view (slices, column selection) share memory — modifying a view can modify the original. Use df.copy() when you need an independent DataFrame. (6) Large data: use chunking (pd.read_csv(file, chunksize=100_000)) or switch to Polars/DuckDB for DataFrames that don't fit in RAM.
• 8SQL-to-pandas translation (the most common interview skill): SELECT + GROUP BY = df.groupby().agg(). WHERE = df[mask]. HAVING = df.groupby().filter(). ORDER BY = df.sort_values(). LIMIT = df.head(). JOIN = pd.merge(). Window function ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) = df.groupby('user_id').cumcount() + 1. RANK() = df.groupby('user_id')['value'].rank(method='min', ascending=False). LAG(col, 1) = df.groupby('user_id')['col'].shift(1). LEAD(col, 1) = df.groupby('user_id')['col'].shift(-1). SUM() OVER (PARTITION BY user_id ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) = df.groupby('user_id')['col'].cumsum(). NTILE(4) = df.groupby('user_id')['col'].transform(lambda x: pd.qcut(x, 4, labels=False)).
• 9Common interview coding patterns — sessionizing user events: A session is a contiguous sequence of events separated by gaps of at most T minutes (commonly 30 minutes). Algorithm: (1) sort by (user_id, timestamp); (2) compute time gap between consecutive events within each user: df['gap'] = df.groupby('user_id')['ts'].diff().dt.total_seconds() / 60; (3) flag new sessions where gap > 30 or it's the user's first event: df['is_new_session'] = (df['gap'] > 30) | df['gap'].isna(); (4) session_id = df.groupby('user_id')['is_new_session'].cumsum(). Now each row has a session identifier. Aggregate session features: df.groupby(['user_id', 'session_id']).agg(session_start=('ts', 'min'), session_end=('ts', 'max'), n_events=('event_type', 'count')).
• 10Common interview coding patterns — top-N per group: Find the top 3 products by revenue per country. Without SQL window functions, this requires careful pandas: method 1 — sort then groupby head: df.sort_values('revenue', ascending=False).groupby('country').head(3). Method 2 — rank then filter: df['rank'] = df.groupby('country')['revenue'].rank(method='first', ascending=False); df[df['rank'] <= 3]. Method 1 is concise; Method 2 is more flexible (can use dense rank, handle ties). For the 'median of medians' or 'Nth percentile per group': df.groupby('group')['value'].quantile(0.9). For deduplication keeping the latest record per user: df.sort_values('timestamp').drop_duplicates('user_id', keep='last').
• 11Handling large datasets — chunking, dtypes, memory profiling: df.info(memory_usage='deep') reports per-column memory. df.memory_usage(deep=True).sum() / 1e9 gives total GB. Dtype optimization can reduce memory 4-8x: float64→float32 (saves 50%), int64→int32 (saves 50%), object→category for strings with <100 unique values (saves ~70%). Chunked reading: for chunk in pd.read_csv('large.csv', chunksize=100_000): process(chunk). For datasets larger than RAM, switch to: DuckDB (SQL queries on Parquet files with minimal RAM), Polars (Rust-based DataFrame library, 5-10x faster than pandas on large data, lazy evaluation), Dask (parallelizes pandas across cores), or PySpark (distributed). In interviews, mentioning when you would switch from pandas to a more scalable tool signals production experience.