Calculate User Deviation from Team Average Messages
Company: Google
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
usage_stats
+---------+---------+---------------+------------+
| user_id | team_id | messages_sent | date |
+---------+---------+---------------+------------+
| 1 | 10 | 8 | 2024-05-01 |
| 2 | 10 | 3 | 2024-05-01 |
| 3 | 20 | 15 | 2024-05-02 |
| 4 | 20 | 9 | 2024-05-02 |
| 5 | 30 | 0 | 2024-05-03 |
+---------+---------+---------------+------------+
##### Scenario
Analyst needs each user’s deviation from their team’s average sent messages without renaming columns in pandas.
##### Question
Write Python code that returns a DataFrame with an extra column ‘delta_from_team_mean’ using transform, and explain why transform works better than groupby.mean here.
##### Hints
transform broadcasts team means to original index; avoids column aggregation and renaming.
Quick Answer: This question evaluates proficiency with pandas group-wise computations, broadcasting of group aggregates, and DataFrame manipulation without renaming columns.