Share of group and above-group-mean

Pandas data engineering interview problem. Difficulty: intermediate. Pattern: GroupBy. About 14 minutes. Part of the Pro drill bank.

Add each order's share of its customer's total and a flag for being above the customer's mean, without collapsing rows. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Sometimes you want to compare each row with its group without collapsing the rows. For example, what share of a customer's spending does one order make up, and is it a bigger order than usual for that customer? df has 1 row per order: order_id, customer and amount. Keep every row and add 2 columns. share is the order's amount divided by the customer's total amount, rounded to 4 decimals. above_mean is True when the order's amount is strictly greater than the mean amount of that customer's orders. Return order_id, customer, amount, share and above_mean, sorted by order_id, with a clean index. The editor builds df and ends with result = df. Change the last line. groupby(...).transform(...) gives a group value on every row.

Requirements

• Compute each customer's total and mean with transform, so every order keeps its row.
• share is amount / total, rounded to 4 decimals.
• above_mean is amount > mean, with a strict comparison.
• Return the 5 columns, sorted by order_id, with a clean index, in result.

Constraints

• The result has the same number of rows as df: 6.
• Use groupby("customer")["amount"].transform("sum") and .transform("mean").
• A plain groupby().sum() would collapse the rows. Do not use it for this.

Examples

Input: df order_id | customer | amount 1 | a | 10 2 | a | 30 3 | a | 20 4 | b | 50 5 | b | 50 6 | c | 80 Output: order_id | customer | amount | share | above_mean 1 | a | 10 | 0.1667 | False 2 | a | 30 | 0.5 | True 3 | a | 20 | 0.3333 | False 4 | b | 50 | 0.5 | False 5 | b | 50 | 0.5 | False 6 | c | 80 | 1 | False Customer a's total is 60, so order 2 is half (0.5) and above the mean of 20. Customer b's two equal orders are not above their mean. Customer c has one order: share 1 and not above its own mean.

Topics: lakebench, pandas, transform, groupby, share.

More interview problems · All interview problems · Learn data engineering