Calculate User Deviation from Team Average Messages

Quick Overview

This question evaluates proficiency with pandas group-wise computations, broadcasting of group aggregates, and DataFrame manipulation without renaming columns.

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.

|Home/Data Manipulation (SQL/Python)/Google
Google logo
Google
Aug 4, 2025, 10:55 AM
mediumData ScientistTechnical ScreenData Manipulation (SQL/Python)
100
0

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.

Loading comments...