Split comma values into rows

PySpark data engineering interview problem. Difficulty: beginner. Pattern: Filtering. About 10 minutes. Free to practice.

Turn a comma-separated string column into 1 row per trimmed value. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Many tables store several values in one text column, separated by commas, for example a, b,c. To work with them, you want 1 row for each value. The text may be messy: spaces around the values, empty pieces like x,,y, a blank text, or NULL. In the table tickets, the column tags holds comma-separated tags. Return 1 row for each ticket and each tag, with the spaces around the tag removed. Do not return rows for blank, empty or NULL tags. Return id and tag. The editor creates the table tickets and ends with result = tickets. Change the last line to build your answer.

Requirements

• Split tags on commas and put each piece on its own row (explode).
• Remove the spaces around each tag with trim.
• Remove rows where the tag is empty after trimming.
• Return id and tag in result.

Constraints

• A NULL tags value gives no rows, because explode drops NULL.
• Trim before you check for empty, because a tag of just spaces becomes empty only after trimming.
• Do not change the case of the tags.

Examples

Input: tickets id | tags 1 | a, b,c 2 | 3 | NULL 4 | red ,blue 5 | x,,y Output: id | tag 1 | a 1 | b 1 | c 4 | red 4 | blue 5 | x 5 | y Ticket 1 gives a, b and c. Tickets 2 (spaces only) and 3 (NULL) give nothing. Ticket 4 gives red and blue without spaces, and ticket 5 gives x and y.

Topics: lakebench, pyspark, split, explode, trim.

More PySpark interview questions · All interview problems · Learn data engineering