19. Unpivot Wide Columns to Rows

Difficulty: Medium · Topics: Pivot, Unpivot & Rollup, Null Handling, Filtering & Selection

The budget table arrived in spreadsheet shape: one column per month (jan, feb, mar).

Turn it into a long table with columns dept, month (the column name, e.g. 'jan') and amount. Leave out months with no amount (null).

Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

budget

dept	jan	feb	mar
Sales	100	120	90
IT	300	null	310

Expected output

dept	month	amount
Sales	jan	100
Sales	feb	120
Sales	mar	90
IT	jan	300
IT	mar	310

Hints

Hint 1

Spark 3.4+ has df.unpivot(ids, values, variableColumnName, valueColumnName) (alias melt).

Hint 2

The ids stay as they are; each value column becomes one row per input row.

Hint 3

Unpivot keeps nulls, so filter them afterwards.

PySpark functions you'll practise

• unpivot
• isNotNull
• filter

Related problems

• Reconcile Two Sources (Full Outer Join) · Hard · Joins
• Build an SCD Type 2 History · Hard · Window Functions
• Monthly Retention by Cohort · Hard · Joins
• Subtotals with ROLLUP · Hard · Pivot, Unpivot & Rollup
• Pivot with Several Aggregations · Hard · Pivot, Unpivot & Rollup

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems