A data engineer is working with a large Spark Delta table `product_reviews` in Microsoft Fabric. Each review has a `review_text` column. They need to create a new derived column `sentiment_category` based on whether the `review_text` contains specific keywords like 'excellent', 'good', 'bad', or 'poor'. If 'excellent' or 'good' is present, it should be 'Positive'; if 'bad' or 'poor', it should be 'Negative'; otherwise, 'Neutral'. Which PySpark function is most suitable for this conditional logic and string matching?
- Adf.withColumn('sentiment_category', F.when(F.col('review_text').like('%excellent%') or F.col('review_text').like('%good%'), 'Positive').otherwise(F.when(F.col('review_text').like('%bad%') or F.col('review_text').like('%poor%'), 'Negative').otherwise('Neutral')))
- Bdf.withColumn('sentiment_category', when(col('review_text').contains('excellent') | col('review_text').contains('good'), 'Positive').when(col('review_text').contains('bad') | col('review_text').contains('poor'), 'Negative').otherwise('Neutral'))
- Cdf.withColumn('sentiment_category', regexp_extract(col('review_text'), '(excellent|good|bad|poor)', 0))
- Ddf.withColumn('sentiment_category', array_contains(split(col('review_text'), ' '), 'excellent') ? 'Positive' : 'Neutral')
Show answer & explanationAnswer & explanation
Correct answer: B. df.withColumn('sentiment_category', when(col('review_text').contains('excellent') | col('review_text').contains('good'), 'Positive').when(col('review_text').contains('bad') | col('review_text').contains('poor'), 'Negative').otherwise('Neutral'))
The `when().otherwise()` construct in PySpark's `pyspark.sql.functions` module is specifically designed for implementing conditional logic (CASE WHEN statements) to create new columns. Combining it with `col().contains()` is the most direct and readable way to check for substring presence and assign categories.
Why the other options are wrong
- A. While this also uses `when().otherwise()`, the `like()` operator with wildcards is generally less efficient than `contains()` for simple substring checks, and the Python `or` operator would evaluate on the driver, not within Spark's execution plan. The correct way to combine conditions in Spark expressions is with `|` (bitwise OR) or `&` (bitwise AND).
- C. regexp_extract() extracts a matching string, it doesn't assign categories based on multiple conditions and defaults.
- D. This only handles a single condition and doesn't cover all cases. `array_contains` is for arrays, not direct string matching.
PySpark when().otherwise()
A PySpark SQL function that implements conditional logic similar to SQL's CASE WHEN statement, allowing different values to be assigned to a column based on specified conditions.
- Used with `pyspark.sql.functions.when`.
- Allows chaining multiple `when()` conditions.
- Ends with `otherwise()` for a default value.
- Excellent for creating derived columns with complex logic.
Memory trick: When this is true, then that; Otherwise, default to the rest.