Wide quarterly sales to long rows

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

Turn 1 column per quarter into 1 row per region, product and quarter. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

df has 1 row per region and product, with 1 column per quarter: q1, q2 and q3. A quarter that has not been reported is empty. Return 1 row per region, product and quarter that has a figure, with the columns region, product, quarter and units. Leave out quarters with no figure. Sort by region, product, then quarter, and reset the index. Assign the DataFrame to result.

Requirements

• Return the columns region, product, quarter, units.
• Sort by region, product, quarter and reset the index.

Constraints

• Region and product together identify one input row.

Examples

Input: df region | product | q1 | q2 | q3 East | pen | 10 | 12 | 11 East | ink | 4 | 0 | 5 West | pen | 7 | 9 | NULL Output: region | product | quarter | units East | ink | q1 | 4 East | ink | q2 | 0 East | ink | q3 | 5 East | pen | q1 | 10 East | pen | q2 | 12 East | pen | q3 | 11 West | pen | q1 | 7 West | pen | q2 | 9 8 rows come back: nine cells minus West's empty q3. The 0 for East ink in q2 is a real figure and stays.

Topics: lakebench, pandas, melt, id_vars, dropna.

More interview problems · All interview problems · Learn data engineering