Units per region with 1 column per quarter

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

Total units per region and quarter, laid out with 1 column per quarter and a zero for empty cells. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

df has 1 row per sale: region, quarter and units. A region can have several sales in the same quarter, and some regions have no sale in some quarters. Return 1 row per region with 1 column per quarter that appears in df (q1, q2, q3 in order), holding the units sold in that quarter. A quarter with no sale is 0. Add a total column with the sum of the quarters. Sort by region and reset the index. Assign the DataFrame to result.

Requirements

• Return region, then 1 column per quarter that appears in df (in order), then total.
• Empty cells are 0, not missing.
• Sort by region and reset the index.

Constraints

• units is a whole number.
• Quarters are written q1, q2, q3.

Examples

Input: df region | quarter | units East | q1 | 10 East | q2 | 12 West | q1 | 7 East | q1 | 5 West | q3 | 9 North | q2 | 4 Output: region | q1 | q2 | q3 | total East | 15 | 12 | 0 | 27 North | 0 | 4 | 0 | 4 West | 7 | 0 | 9 | 16 East q1 adds two sales (10 + 5 = 15). North only sold in q2 and West skipped q2, so their empty cells are 0.

Topics: lakebench, pandas, unstack, groupby, fill_value.

More interview problems · All interview problems · Learn data engineering