Wide to long with quarter-over-quarter growth

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

Reshape quarterly columns into rows and compute growth versus the previous quarter per product. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Reports often have 1 column for each period, like q1, q2, q3 and q4. This is called a wide table. To calculate changes over time, it is easier to have 1 row for each period (a long table). df has 1 row for each product and four revenue columns q1 to q4. Reshape it to 1 row for each product and quarter with the columns product, quarter and revenue. Then add growth_pct: the percentage change compared with the previous quarter of the same product, rounded to 1 decimal. The first quarter of each product has no previous quarter, so its growth is NaN. Sort by product, then quarter, and reset the index. The editor builds df and ends with result = df. Change the last line. Use melt to make the table long and groupby(...).pct_change() for the growth.

Requirements

• Use melt to change the table from wide to long, with product, quarter, revenue.
• Sort by product and quarter before you calculate the change.
• Add growth_pct as pct_change() * 100, rounded to 1 decimal, calculated inside each product.
• Reset the index and store the DataFrame in result.

Constraints

• The result has 8 rows: 2 products times 4 quarters.
• Calculate the change within each product, with groupby("product").
• The first quarter of each product has growth_pct NaN.

Examples

Input: df product | q1 | q2 | q3 | q4 A | 100 | 120 | 90 | 135 B | 50 | 40 | 60 | 66 Output: product | quarter | revenue | growth_pct A | q1 | 100 | NULL A | q2 | 120 | 20 A | q3 | 90 | -25 A | q4 | 135 | 50 B | q1 | 50 | NULL B | q2 | 40 | -20 B | q3 | 60 | 50 B | q4 | 66 | 10 Product A grows 20% to q2, falls 25% in q3, then grows 50% in q4. Product B's q1 has no growth even though it follows A's q4.

Topics: lakebench, pandas, melt, pct_change, wide to long.

More interview problems · All interview problems · Learn data engineering