Transform and aggregate messy event data
Company: Boston Consulting Group
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Take-home Project
Using pandas (vectorized; no loops), clean, combine, and aggregate the following to produce country/plan day-level metrics for 2025-08-31. DataFrames (ASCII): users user_id | signup_date | plan | monthly_fee | country 1 | 2025-08-30 | Pro | "$12.50" | US 2 | 2025-08-31 | Free | "$0" | US 3 | 2025-07-15 | Pro | " $15 " | CA 4 | 2025-08-31 | Pro | "$12.50" | US events_august user_id | event_time | event_type | amount 1 | 2025-08-31 09:12:00 | login | 1 | 2025-08-31 09:15:00 | purchase | "12.50" 2 | 2025-08-31 10:01:00 | login | 3 | 2025-08-30 23:58:00 | login | 4 | 2025-08-31 09:59:00 | purchase | "$12.50" events_august_extra (same fields, shuffled order): event_type | amount | user_id | event_time login | | 1 | 2025-08-31 10:10:00 purchase | "12.50" | 2 | 2025-08-31 10:30:00 Tasks: 1) Convert users.signup_date and all event_time to pandas datetime; strip currency/whitespace and coerce monthly_fee and amount to float (NaN on errors). 2) Column-align and concatenate events_august and events_august_extra into events_all. 3) Join users to events_all on user_id. 4) For date 2025-08-31 only, compute per (country, plan): active_users = count of distinct users with ≥1 login; purchasers = count of distinct users with ≥1 purchase; purchases_count = number of purchase events; revenue = sum(amount over purchase events). 5) Return a tidy DataFrame with columns [date, country, plan, active_users, purchasers, purchases_count, revenue] sorted by country asc, plan asc. State any assumptions about missing amounts and timezone handling.
Quick Answer: This question evaluates a candidate's competency in data wrangling with pandas, including datetime parsing and timezone considerations, currency/string cleaning and coercion to numeric types with handling of missing or malformed amounts, DataFrame column alignment and concatenation, joins, and group-by aggregations to produce per-country/plan day-level metrics. It is commonly asked to assess practical data-manipulation skills for producing analysis-ready metrics from messy event and user datasets, falls under the Data Manipulation (SQL/Python) domain, and targets practical application rather than purely conceptual understanding.