Python and Pandas for Data Analysis Questions
Programmatic data manipulation and analysis in Python and R. Covers pandas transformations, joins and reshaping, aggregation, working with PySpark for larger data, and using R for statistical analysis. Emphasizes clean, reproducible analytical code.
A churn dataset has missing values in income, last_login, and plan_type, and the missingness seems to come from different sources rather than one system bug. How would you decide whether to impute, drop, or flag each field before modeling, and what would you check to make sure the choice is not biasing the model or hiding an important signal?
Sample Answer
Direct answer
Missing values are not one problem: they are a symptom, and the right handling depends on the mechanism behind each field's missingness, not on a single blanket rule for the dataset. I would look at income, last_login, and plan_type separately, because a numeric field missing due to a survey skip, a timestamp missing because the event never happened, and a category missing because of an integration gap all call for different treatments.
Structured elaboration
| Field | Likely missingness mechanism | Treatment | Why |
|---|---|---|---|
income | Plausibly random or weakly related to other fields (e.g. a form field some users skip) | Impute with the median; add an income_missing flag | A flag preserves the "was this observed" signal even after the value itself is filled in, so the model can use both |
last_login | Structural: missing very likely means "never logged in," not "unknown" | Do not impute a date; add a never_logged_in flag and a separate recency feature | Filling in a fake date would fabricate a "recent" or "average" user out of someone who may be a pure prospect or churned-before-activation user |
plan_type | Likely a categorical gap from an upstream integration or a free-tier user with no plan record | Fill with an explicit "Unknown" category rather than dropping the row | Dropping loses every other field's information for that user; an explicit category lets a tree-based model split on it if it turns out to matter |
The common thread: decide whether the fact that a value is missing carries information before deciding how to fill it in. Missingness that is itself predictive (an MNAR pattern, missing-not-at-random) should stay visible to the model as a flag; missingness that looks closer to random can be imputed more mechanically without much risk.
Worked example
Concretely, before finalizing any of the three choices above, I would run this check: compare the target churn rate for rows where last_login is missing against rows where it is present.
- If the two groups have similar churn rates, the missingness in
last_loginis probably closer to random and treating it lightly (e.g. simple imputation) carries lower risk. - If missing-
last_loginrows churn dramatically more (or less) than the rest, that gap is signal, and collapsing it into an imputed "typical" login date would erase the single most predictive fact in the dataset. In that case thenever_logged_inflag is not an optional nicety, it is likely one of the strongest features in the model.
I would run the same comparison for income and plan_type rather than assuming the table above holds for every dataset; the mechanism has to be checked, not guessed.
Trade-offs and pitfalls
- Imputing on the full dataset before splitting leaks information from validation/test rows into the training-time imputation statistic (e.g. a median computed across all rows). Fit any imputer on the training split only, then apply it unchanged to validation and test.
- Dropping rows is the easiest option and the most dangerous: if missingness correlates with the outcome (which is exactly the case that matters most for churn), dropping those rows biases the remaining dataset toward the "well-behaved" users and the model will underperform precisely on the segment it most needs to catch.
- A missingness flag with no real fill value change can still help even for fields you do impute: keeping
income_missingas a separate column after fillingincomewith the median lets a model use "the value" and "whether the value was actually observed" as two independent signals, instead of collapsing them into one filled number that looks identical to genuinely observed data. - Validate by segment, not just in aggregate: after choosing a treatment, compare model calibration and error rates across the missing-vs-present subgroups specifically, since an averaged validation metric can look fine even while the model is quietly wrong on exactly the rows that had missing data.
Your team stores a product tier column as plain strings (Bronze, Silver, Gold, Platinum), but a report needs to sort and compare tiers in business order rather than alphabetically. Walk through how you'd fix the dtype so sorting, comparisons, and groupby all respect that order, and what that buys you over leaving it as a plain string column.
Sample Answer
Direct answer
The root problem is that the tier column has dtype object/string, so pandas sorts and compares it lexicographically: Bronze, Gold, Platinum, Silver alphabetically, which has nothing to do with the real business ranking Bronze < Silver < Gold < Platinum. The fix is to cast the column to an ordered CategoricalDtype that encodes the actual business order, so sorting, comparisons, and groupby all use that order instead of the alphabet.
Structured elaboration
A pandas categorical dtype stores a column as a small, fixed list of allowed values (the categories) plus an integer code per row pointing into that list, instead of repeating the full string in every row. Setting ordered=True and listing the categories from lowest to highest tells pandas to use that sequence as the sort and comparison order, not the alphabet.
- Define the business order explicitly, lowest to highest, once:
CategoricalDtype(categories=['Bronze','Silver','Gold','Platinum'], ordered=True). - Cast the column with
.astype(that_dtype). - From that point on,
sort_values,>/<comparisons, andgroupbyoutput order all follow the category order, not string order.
Worked example
import pandas as pd
from pandas.api.types import CategoricalDtype
df = pd.DataFrame({
'account': ['A', 'B', 'C', 'D', 'E'],
'tier': ['Gold', 'Bronze', 'Platinum', 'Silver', 'Gold'],
'revenue': [500, 100, 900, 300, 450],
})
tier_order = CategoricalDtype(categories=['Bronze', 'Silver', 'Gold', 'Platinum'], ordered=True)
df['tier'] = df['tier'].astype(tier_order)
df.sort_values('tier')['tier'].tolist()
# ['Bronze', 'Silver', 'Gold', 'Gold', 'Platinum'] (would be Bronze, Gold, Gold, Platinum, Silver as plain strings)
df['tier'] > 'Silver'
# [True, False, True, False, True] (True only for the Gold and Platinum rows)
df.groupby('tier', observed=True)['revenue'].mean()
# Bronze 100.0
# Silver 300.0
# Gold 475.0
# Platinum 900.0
Verified: all three outputs above were reproduced exactly, including that df['tier'] > 'Silver' correctly picks out only the Gold and Platinum accounts, something a plain-string comparison would get wrong ('Gold' > 'Silver' is False alphabetically).
Trade-offs and pitfalls
- Before the fix,
df.sort_values('tier')on plain strings puts Platinum, the highest tier, ahead of Silver in the sorted output, misleading in any tier-ranked report. A filter meant to catch "above Silver" customers would silently miss the Gold accounts under plain-string comparison. - What the ordered categorical buys beyond correctness: memory, each row stores a small integer code plus one shared lookup table of the distinct strings, instead of a separate string object per row, which matters on a column with millions of rows and only a handful of distinct values; speed, sorting, comparisons, and
groupbyoperate on the integer codes internally rather than comparing strings row by row; a data-quality guardrail, a typo like'platinum'(wrong case) or a stray value becomesNaN(not-a-number, pandas' missing-value marker) on cast rather than silently becoming a fifth tier that an alphabetical sort would place in some arbitrary wrong position. - Complexity: the
.astype()cast itself isO(n)(one pass to map each string to its integer code) and allocates a new column, so it briefly costsO(n)extra memory during the cast, not an in-place mutation. After the cast, sort/compare/groupbyoperations run overO(k)integer codes forkdistinct categories instead of doingO(n)string comparisons each, so the ongoing cost of every later operation on that column drops, not just its one-time conversion cost. Memory at rest isO(k)for the category lookup table plusO(n)small integer codes, versusO(n)full string objects for the plain-string column. - Edge cases: any value not in the categories list turns into
NaNon cast (verified: casting'platinum'lowercase against the four defined categories producesNaN, not an error), so validate or clean upstream values before casting rather than losing rows silently; adding a new tier later should usedf['tier'].cat.add_categories(['Diamond'])rather than re-casting the whole column from scratch, since re-casting against a stale category list wouldNaNout every existing'Diamond'value that arrived before the category was added;groupby(..., observed=True)avoids materializing every possible category combination as a row even when a category wasn't actually observed in the data, which matters once the category list grows. - Categorical dtype is a net loss, not a win, on very high-cardinality columns (thousands-plus distinct values with few repeats), since the code-plus-lookup-table overhead can exceed plain string storage when there's little repetition to exploit.
You have two DataFrames: prices (timestamp, symbol, price) and trades (timestamp, symbol, quantity). For each trade, you need the most recent price for that symbol at or before the trade's own timestamp. Write pandas code to produce this, explain what ordering your approach depends on, and how you would restrict matches to the same symbol.
Sample Answer
Direct answer
Use pd.merge_asof with direction='backward' to attach each trade to the most recent price
at or before its own timestamp, passing by='symbol' to restrict matches to the same symbol.
The one hard requirement is that both DataFrames are sorted by the on timestamp column
itself, not by symbol first: merge_asof performs a single sorted-order scan, and by only
restricts which rows are eligible matches within that scan, it does not re-sort per group.
Structured elaboration
- Sort by the
onkey only. Sorting by['symbol', 'timestamp']groups rows by symbol
first, which breaks global timestamp order the moment two symbols' events interleave in
time, andmerge_asofraisesValueError: left keys must be sorted(or silently returns
wrong matches on older pandas that did not check). Sort by'timestamp'alone;by='symbol'
handles the per-symbol restriction internally. directioncontrols which side of "at or before" you want:'backward'(the default)
finds the last price at or before the trade;'forward'finds the next one at or after;
'nearest'finds whichever is closer in time.by=restricts matches to the same group, so an AAPL trade can never match an MSFT
price, without you having to loop over symbols yourself.
Worked example
import pandas as pd
prices = pd.DataFrame({
"timestamp": pd.to_datetime(["2025-01-01 09:30:00", "2025-01-01 09:31:00", "2025-01-01 09:30:00"]),
"symbol": ["AAPL", "AAPL", "MSFT"],
"price": [150.0, 150.5, 250.0],
})
trades = pd.DataFrame({
"timestamp": pd.to_datetime(["2025-01-01 09:30:30", "2025-01-01 09:31:10", "2025-01-01 09:29:00"]),
"symbol": ["AAPL", "AAPL", "MSFT"],
"quantity": [10, 5, 20],
})
# sort by the 'on' key alone; do NOT sort by symbol first (see pitfalls)
prices = prices.sort_values("timestamp")
trades = trades.sort_values("timestamp")
merged = pd.merge_asof(
trades,
prices,
on="timestamp",
by="symbol",
direction="backward",
suffixes=("", "_price"),
)
print(merged)
# timestamp symbol quantity price
# 0 2025-01-01 09:29:00 MSFT 20 NaN
# 1 2025-01-01 09:30:30 AAPL 10 150.0
# 2 2025-01-01 09:31:10 AAPL 5 150.5
The MSFT trade at 09:29 gets not-a-number (NaN) for price: its only MSFT price observation
is at 09:30, which is AFTER the trade, so direction='backward' correctly finds nothing.
Key points
by='symbol'restricts matches without a manual per-symbol loop.directionandtolerance(a max allowed time gap) both shape which price counts as "close
enough"; usetoleranceif a match hours old should be treated as no match at all.
Complexity
O(n log n) for the sort, which dominates; the merge_asof scan itself is O(n + m) given
sorted input (n trades, m prices).
Edge cases
A trade earlier than any price observation for its symbol produces NaN in the price columns
(decide: drop, forward-fill from a seed price, or treat as invalid upstream); a symbol present
in one side but not the other never matches, by design; and duplicate timestamps within a
symbol on the price side, where direction='backward' takes the last one in sort order, so add
an explicit tiebreaker column if that ordering needs to be deterministic beyond timestamp.
Trade-offs and pitfalls
The pitfall worth naming explicitly, because it is easy to get backwards: sorting by
['symbol', 'timestamp'] looks like the natural thing to do when you are about to pass
by='symbol', but it is wrong. merge_asof needs the on column globally sorted across the
whole DataFrame; grouping by symbol first breaks that the moment two symbols' timestamps
interleave (an MSFT trade at 09:29 sorted after an AAPL trade at 09:31 under a
symbol-then-timestamp sort). Sort by timestamp alone and let by do the grouping.
- If you want only exact-timestamp matches instead of "most recent at or before", use a plain
inner merge on bothtimestampandsymbolinstead ofmerge_asof. - Consider whether an unmatched trade (
NaNprice) should be dropped, flagged, or filled from
a known opening price, since silently keepingNaNin downstream arithmetic will propagate
it.
Using Python and pandas, transform a wide DataFrame 'df' with columns ['month', 'segment_A', 'segment_B', 'segment_C'] into tidy (long) format and compute month-over-month growth per segment. Provide the code outline and note edge cases you must handle (missing months, NaNs, zero denominators).
Sample Answer
Direct answer
Melt the wide table to long/tidy format with one row per (month, segment), reindex each segment to a complete, continuous monthly date range so missing months become explicit NaN (not-a-number) rather than silently absent rows, then compute month-over-month growth per segment with a grouped percent-change that guards zero and null denominators explicitly.
Structured elaboration
- Parse
monthas a real datetime and sort by it, so downstream date-range logic has something to work with. df.melt(id_vars='month', var_name='segment', value_name='value')turns the three wide segment columns into a longsegment/valuepair per month, which is the shape almost every later step (grouping, plotting, joining) wants.- Reindex each segment onto a complete
pd.date_range(..., freq='MS')(month-start) so a month that is missing from the source data becomes an explicitNaNrow instead of a silent gap that apct_changewould otherwise just skip over unnoticed. - Compute the month-over-month growth per segment.
pct_change()alone already returnsNaNwhen either the current or prior value is null, and returnsinf/-inf(not a cleanNaN) when the prior value is exactly zero, so wrap it to convert a zero-denominator case into an explicit sentinel (NaNor a flag column) rather than lettinginfleak into later aggregations, where it silently poisons a mean or sum.
Worked example
import pandas as pd
df = pd.DataFrame({
'month': ['2026-01', '2026-02', '2026-04'],
'segment_A': [100, 110, 0],
'segment_B': [200, 0, 210],
'segment_C': [50, 55, 60],
}) # note March is missing entirely, this is what the reindex step below must surface
df['month'] = pd.to_datetime(df['month'])
df = df.sort_values('month')
long = df.melt(id_vars='month', var_name='segment', value_name='value')
min_m, max_m = long['month'].min(), long['month'].max()
all_months = pd.date_range(min_m, max_m, freq='MS')
segments = long['segment'].unique()
index = pd.MultiIndex.from_product([segments, all_months], names=['segment', 'month'])
long = long.set_index(['segment', 'month']).reindex(index).reset_index()
def safe_pct_change(x):
prev = x.shift(1)
denom_zero = prev == 0
pct = (x - prev) / prev
pct[denom_zero & x.notna()] = pd.NA
return pct
long['mom_growth'] = long.groupby('segment')['value'].transform(safe_pct_change)
print(long)
Output, run against a 3-month fixture with a missing March row (verified by running this exact code):
segment month value mom_growth
0 segment_A 2026-01-01 100.0 NaN
1 segment_A 2026-02-01 110.0 0.1
2 segment_A 2026-03-01 NaN NaN
3 segment_A 2026-04-01 0.0 NaN
4 segment_B 2026-01-01 200.0 NaN
5 segment_B 2026-02-01 0.0 -1.0
6 segment_B 2026-03-01 NaN NaN
7 segment_B 2026-04-01 210.0 NaN
8 segment_C 2026-01-01 50.0 NaN
9 segment_C 2026-02-01 55.0 0.1
10 segment_C 2026-03-01 NaN NaN
11 segment_C 2026-04-01 60.0 NaN
Key points and a correction to the pattern above
Use .transform(safe_pct_change), not .apply(safe_pct_change), to assign the result back onto the long frame's mom_growth column. groupby(...).apply() prepends the group key to the result's index by default, so assigning its output straight into long['mom_growth'] raises TypeError: incompatible index of inserted column with frame index under pandas 3.0.3, because the apply result carries a (segment, original_position) MultiIndex that pandas can no longer silently reconcile against long's plain index. .transform() is built exactly for this "one output row per input row, same index" shape and assigns back cleanly; this was verified directly, .apply() throws on assignment, .transform() does not, and both compute the identical per-row numbers when tested in isolation.
Trade-offs and pitfalls
- Deciding what to do with an explicit missing month is a business call, not a technical one: leaving it
NaNis the honest default (an actual gap in the data), but if the business rule is "assume flat between reported months," use.ffill()after reindexing, never.fillna(method='ffill')(themethod=keyword was removed fromfillnain pandas 3.0 and now raisesTypeError). pct_change()on a rawSeriesreturnsinffor a zero-to-nonzero jump andNaNonly for a zero-to-zero step; if you skip the zero-guard shown above, aninfwill silently pass through a later.mean()or.sum()and corrupt it without raising anything.- Complexity:
O(n)in the number of (segment x month) rows after melting and reindexing; the reindex step also allocates a fullsegment x monthgrid, so if segments are very high-cardinality relative to the number of months, that grid can be much bigger than the original data.
Write Python (pandas) code to compute weekly active users (WAU) from an event log events(user_id, event_time timestamp). Your code should: 1) compute WAU per ISO week, 2) compute week-over-week percentage change, and 3) handle missing days and timezone-aware timestamps. Explain any assumptions.
Sample Answer
Direct answer
Localize event timestamps to a consistent timezone, bucket by ISO week (the ISO 8601 standard week-numbering scheme, weeks run Monday to Sunday and can span a year boundary), count distinct users per week for WAU (weekly active users), then fill any week with zero events before computing week-over-week percent change so a silent gap does not get skipped over.
Approach
- Parse
event_time; if naive, localize it to a chosen timezone (UTC unless you have a reason to use local time); if already timezone-aware, convert to that timezone. - Derive
(iso_year, iso_week)with.dt.isocalendar(), which correctly handles ISO week 52/53 and year-boundary weeks. - Count distinct
user_idper(iso_year, iso_week)for WAU. - Build the full continuous range of ISO weeks between the first and last observed week and left-fill any missing week with
wau = 0, so "no events that week" is visible rather than silently absent from the output. - Compute week-over-week percent change with a
shift(1), explicitly leaving it undefined (notinf) for the first week and for any week following a zero-WAU week.
Worked example
import numpy as np
import pandas as pd
df = pd.DataFrame({
"user_id": [1, 2, 1, 3, 1, 2, 4],
"event_time": [
"2024-01-02 10:00:00", "2024-01-03 11:00:00",
"2024-01-09 09:00:00", "2024-01-10 12:00:00",
"2024-01-25 08:00:00", "2024-01-26 09:30:00", "2024-01-27 14:00:00",
],
})
def compute_wau(df, ts_col="event_time", user_col="user_id", tz="UTC"):
df = df.copy()
df[ts_col] = pd.to_datetime(df[ts_col])
if df[ts_col].dt.tz is None:
df[ts_col] = df[ts_col].dt.tz_localize(tz)
else:
df[ts_col] = df[ts_col].dt.tz_convert(tz)
iso = df[ts_col].dt.isocalendar()
df["iso_year"], df["iso_week"] = iso["year"], iso["week"]
weekly = (df.groupby(["iso_year", "iso_week"])[user_col]
.nunique().rename("wau").reset_index())
start = pd.Timestamp.fromisocalendar(int(weekly["iso_year"].min()), int(weekly["iso_week"].min()), 1)
end = pd.Timestamp.fromisocalendar(int(weekly["iso_year"].max()), int(weekly["iso_week"].max()), 1)
week_starts = pd.date_range(start, end, freq="7D")
all_idx = [(d.isocalendar().year, d.isocalendar().week) for d in week_starts]
full = (pd.DataFrame(all_idx, columns=["iso_year", "iso_week"])
.merge(weekly, on=["iso_year", "iso_week"], how="left")
.fillna({"wau": 0})
.sort_values(["iso_year", "iso_week"])
.reset_index(drop=True))
full["wau_prev"] = full["wau"].shift(1)
with np.errstate(divide="ignore", invalid="ignore"):
full["wow_pct_change"] = ((full["wau"] - full["wau_prev"]) / full["wau_prev"]) * 100
# undefined for the first week and for any week following a zero-WAU
# week (division by zero would otherwise give an unusable inf)
full.loc[(full["wau_prev"].isna()) | (full["wau_prev"] == 0), "wow_pct_change"] = None
return full[["iso_year", "iso_week", "wau", "wow_pct_change"]]
print(compute_wau(df))
Output (verified against pandas 3.0.3):
iso_year iso_week wau wow_pct_change
0 2024 1 2.0 NaN
1 2024 2 2.0 0.0
2 2024 3 0.0 -100.0
3 2024 4 3.0 NaN
Week 3 has zero events (the gap), correctly reported as wau=0 and -100.0% change from week 2's 2. Week 4 follows a zero-WAU week, where a naive percent-change formula would divide by zero and report inf; that case is explicitly nulled out here instead, since "infinite percent growth" is not an interpretable business number.
Assumptions
- WAU is counted per ISO calendar week, not a rolling trailing-7-days window; these are two different (and both legitimate) definitions of "weekly," and the code above computes the calendar-bucket one because the question asks for week-over-week comparison, which implies fixed weeks.
- Naive timestamps are assumed to already be in the target timezone; if they are actually in a mix of timezones (e.g. server local time that changed when infrastructure moved regions), this code will silently mislabel some events' week.
Complexity
O(n) to parse and group the raw events, plus O(w) for the missing-week fill, where w is the number of distinct ISO weeks in range; w is normally tiny compared to n. Memory: the weekly aggregate is O(w), far smaller than the input, since the whole point of the aggregation is to collapse n events down to one row per week.
Edge cases
- Weeks with zero events: made explicit via the fill step rather than silently missing from the output, and handled specially in the percent-change formula (see above).
- ISO week 52/53 and year boundaries:
.dt.isocalendar()returns the ISO year, which can differ from the calendar year for dates near January 1st; grouping by(iso_year, iso_week)rather than(calendar_year, iso_week)is what keeps late-December and early-January weeks from colliding. - Duplicate events for the same user in the same week:
.nunique()onuser_idalready collapses these correctly, no separate deduplication step needed. - Bots or test accounts: not filtered here; if they are present in
user_id, WAU includes them, and a real implementation should exclude them before this step.
Trade-offs and pitfalls
- Silently reporting
inffor a percent change out of a zero base is a common and confusing mistake in growth-metric code; treat it the same way you would treat a first-period-of-history percent change, as undefined rather than a number.
Unlock Full Question Bank
Get access to all Python and Pandas for Data Analysis interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.