Summary
Pinterest data scientist interview that was basically a pandas deep-dive. Four tasks, all coding, all interconnected, and the nested dict stuff at the end was where I started to feel the pressure.
Questions Asked(4)
The explode part I got pretty fast.
Suggested Approach
Use pandas' built-in vectorized operations — specifically pd.DataFrame.explode() to unnest the events list, followed by pd.json_normalize() to flatten both the event dicts and the nested attrs dict into columns. Chain these transformations together to avoid any row-level Python loops, keeping the solution performant and idiomatic.
Understand & Sketch the Schema
Verbally describe the input shape — a DataFrame with a list-of-dicts column (events) and a nested dict column (attrs) — and sketch the desired output schema with columns like event_type, event_ts, amount, and flattened attrs fields. This shows clarity before writing any code.
Explode the Events List
Use df.explode('events').reset_index(drop=True) to convert each element of the events list into its own row, preserving all other columns including attrs. Emphasize this is fully vectorized with no Python-level iteration.
Normalize the Event Dicts
Apply pd.json_normalize() on the exploded 'events' column to expand each event dict into flat columns (event_type, event_ts, amount, etc.), then pd.concat() the result back with the remaining columns from the exploded DataFrame.
Flatten the Nested attrs Dict
Similarly apply pd.json_normalize() on the 'attrs' column, using a separator like '_' to name nested keys (e.g., attrs.location.city → attrs_location_city), and join these columns into the working DataFrame.
Handle Edge Cases & Validate
Address nulls/missing keys (e.g., events lists that are None or empty, missing dict fields) using errors='ignore' in json_normalize and fillna strategies. Validate output row count and column dtypes, and discuss memory/performance trade-offs at scale.
Key Points to Mention
Named aggregations with groupby I use all the time so this felt okay.
Suggested Approach
Start by identifying which aggregations are straightforward (counts, sums, means) versus which require special handling for 'most recent' values (version, beta flag). Use pandas groupby with named aggregations (the dictionary-style agg syntax) to cleanly express multiple aggregation functions in a single pass. For recency-based fields, leverage a sort + last or idxmax pattern to capture the value associated with the most recent timestamp.
Sort by Timestamp
Before grouping, sort the exploded event table by user_id and event timestamp in ascending order. This ensures that when you use 'last' as an aggregation function, it correctly captures the most recent event's values.
Define Standard Aggregations
Identify columns that need simple aggregations (e.g., event_count=('event_id', 'count'), total_sessions=('session_id', 'nunique')) and list them using the named aggregation syntax: df.groupby('user_id').agg(feature_name=('column', 'func')).
Handle Recency-Based Fields
For fields like 'version' and 'beta_flag', use 'last' as the aggregation function after sorting by timestamp, e.g., version=('version', 'last'), beta_flag=('beta_flag', 'last'). This picks the value from the user's most recent event row.
Combine into a Single groupby Call
Consolidate all named aggregations into one groupby().agg() call to maximize efficiency and avoid multiple passes over the data. This produces a clean, flat per-user features DataFrame with descriptive column names.
Validate and Sanity Check
After aggregation, verify row count equals the number of unique users, check for nulls in recency fields, and spot-check a few users against the raw event table to confirm correctness of the 'most recent' logic.
Key Points to Mention
Pretty straightforward once the features table exists.
Suggested Approach
Start by identifying the grouping dimensions (version, beta_flag, dark_flag) and the key metrics needed (user count, purchaser count, conversion rate) from the per-user features table. Use a GROUP BY on all three segment dimensions and aggregate with COUNT and conditional SUM or COUNT DISTINCT to derive purchasers. Finally, compute conversion rate as the ratio of purchasers to total users per segment.
Understand the Table Schema
Identify the relevant columns: user_id (or equivalent unique identifier), version, beta_flag, dark_flag, and a column indicating purchase behavior (e.g., is_purchaser or a purchase event flag). Clarify what constitutes a 'purchaser' — a binary flag, a count of purchases, or a join to another table.
Define the Segmentation Logic
Group rows by the three segment dimensions — version, beta_flag, and dark_flag — to create one row per unique combination. Be explicit that each unique triplet (version, beta_flag, dark_flag) defines a distinct user segment.
Aggregate the Metrics
Use COUNT(user_id) for total user count per segment and SUM(is_purchaser) or COUNT(CASE WHEN is_purchaser = 1 THEN user_id END) for purchaser count. Ensure you are counting distinct users if the table could have duplicate user rows.
Compute Conversion Rate
Calculate conversion rate as ROUND(purchaser_count * 1.0 / user_count, 4) or as a percentage. Use 1.0 multiplication or CAST to avoid integer division, and handle potential division-by-zero with a NULLIF or CASE guard.
Validate and Interpret Results
Sanity-check that segment user counts sum to the total table population and that conversion rates fall within a plausible range (0–1). Briefly interpret which segments show notably higher or lower conversion rates, connecting findings to A/B testing or product decisions.
Key Points to Mention
This is where I slowed down the most.
Suggested Approach
Start by explaining the core challenge: safely navigating nested dictionary structures in a pandas Series/column without triggering SettingWithCopyWarning or breaking vectorization. Design a helper that uses `.apply()` with a robust inner function or leverages `pd.json_normalize` for true vectorization, explicitly handling None, missing keys, and type mismatches at each nesting level. Walk through the implementation with inline comments that justify each design decision.
Define the Problem & Constraints
Clarify that `attrs` is a column of dicts (possibly None or malformed), and the goal is to extract a value at an arbitrary key path like `['user', 'location', 'city']`. State upfront that you'll avoid chained indexing and operate on the original DataFrame to prevent SettingWithCopyWarning.
Write the Safe Key Extractor
Implement a pure Python helper `safe_get(d, *keys, default=None)` that iterates through the key path using `isinstance` checks and `.get()` at each level, returning the default on any None or missing key. This isolates the null-safety logic and keeps the pandas layer clean.
Apply Vectorized Extraction & Avoid SettingWithCopy
Use `df['new_col'] = df['attrs'].apply(lambda x: safe_get(x, 'key1', 'key2'))` directly on the original DataFrame (not a slice), and add a comment explaining that assigning to `df[col]` on the source object avoids the copy ambiguity that triggers the warning. Alternatively, show `df.assign()` for a fully immutable, copy-safe pattern.
Discuss True Vectorization Trade-offs
Acknowledge that `.apply()` is row-wise Python and not truly vectorized; explain when `pd.json_normalize(df['attrs'])` followed by column selection is preferable for flat or semi-structured dicts, and why it's faster at scale. Note the trade-off: `json_normalize` requires uniform schema assumptions.
Add Robustness & Testing Notes
Mention edge cases to unit-test: None values, empty dicts, wrong types at intermediate keys, and deeply nested missing paths. Briefly note that type annotations and a docstring on `safe_get` make the helper production-ready and reviewable by teammates.
Key Points to Mention
Discussion(4)
Sign in to join the discussion.
The interviewer pushing back on whether apply is vectorized is a genuinely good gotcha and you handled it correctly by admitting it isn't. Apply is row-wise Python iteration with nicer syntax, not numpy-level vectorization. The real answer to 'how do you vectorize nested dict extraction' is that you mostly can't in the numpy sense, because dicts are Python objects and pandas has no native dict-column dtype. What you can do is use a list comprehension over the Series values (faster than apply in practice due to lower overhead) or extract to a flat structure earlier in the pipeline so you're not doing repeated dict lookups at query time. On the SettingWithCopy angle: the issue arises when you assign to a column on a DataFrame that pandas internally treats as a view of another DataFrame, and the assignment may or may not propagate depending on whether it hits the original. The safe pattern is always .loc[row_indexer, col_indexer] for assignment, or just reassign the whole column on the original object. Adding a comment in the code about this is smart for a screen because it shows you understand the underlying copy-vs-view semantics, not just the error message. The fact that this turned into a back-and-forth conversation is probably a good sign, Pinterest DS screens at this level often care more about how you reason through the edges than whether you had the perfect answer ready.
Asking for clarification on the purchaser definition was the right move, full stop. Ambiguity like that in a phone screen is a trap if you silently assume, because you can build a perfectly correct answer to the wrong question. The mechanics here are pretty contained: groupby the three segment columns, then within that agg you want count of users and a conditional count for purchasers. One way is (features['purchase_count'] > 0).astype(int) as a column before the groupby, then sum it. Conversion rate is just purchaser_count divided by user_count, watch for zero-division if any segment has no users, which can happen if your earlier steps dropped rows.
Sorting before groupby is actually the right call here, not a smell. You sort by user_id and event_ts, then groupby user_id and call last() on the attrs columns. That's a well-known pattern and totally defensible. The alternative some people reach for is a transform or a separate idxmax merge, but that's genuinely more code for no real gain in this case. Named aggs with the pd.NamedAgg syntax are worth knowing cold for these screens since they make the intent explicit and avoid the dict-of-functions style that gets ambiguous with custom lambdas.
The apply-lambda-to-Series pattern you used is basically the standard move for this, clunky or not. What I'd push on is the concat step: if you're doing pd.concat([df, df['events'].apply(pd.Series)], axis=1) you're creating a full intermediate DataFrame for every column, and on a wide dict that gets messy fast. A slightly cleaner path is json_normalize on the exploded column directly, since it handles nested keys natively and gives you dot-notation flattening without manual extraction. For the nullable amount field, fillna after extraction is fine but you need to do it before any dtype cast or you'll get the TypeError you probably saw. On event_ts, the move is pd.to_datetime(..., errors='coerce') so bad or missing values become NaT instead of blowing up, then you can decide what to do with the NaTs afterward. The no-row-loops constraint is really just asking whether you know that apply still runs Python-level iteration under the hood, so the spirit of it is more about avoiding explicit for loops in your own code rather than achieving true vectorization. Pinterest phone screens at this level seem to care a lot about whether you know the idiomatic pandas path even if the output is the same.