Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Medium
A data engineering team is using a Spark Notebook in Microsoft Fabric to clean and transform a large dataset of customer reviews. The dataset contains a column named 'review_text' which often includes leading/trailing whitespace, multiple internal spaces, and special characters that need to be removed or replaced. Which PySpark function is most efficient for performing these string cleaning operations across the entire DataFrame column?
- Adf.select(col('review_text').substr(0, 10))
- Bdf.filter(F.col('review_text').contains('bad_word'))
- Cdf.withColumn('cleaned_text', F.regexp_replace(F.trim(F.col('review_text')), '\s+', ' '))
- Ddf.groupBy('review_text').count()
Show answer & explanationAnswer & explanation
Correct answer: C. df.withColumn('cleaned_text', F.regexp_replace(F.trim(F.col('review_text')), '\s+', ' '))
The combination of `F.trim()` to remove leading/trailing whitespace and `F.regexp_replace()` with the regular expression '\s+' to replace multiple internal spaces with a single space, applied within `withColumn`, is the most efficient and comprehensive PySpark method for the described string cleaning.
Why the other options are wrong
- A. This only extracts a substring and does not perform any cleaning of whitespace or special characters.
- B. This is used for filtering rows based on a substring presence, not for cleaning the string content itself.
- D. This performs an aggregation to count occurrences of each review text, not a string cleaning operation.
PySpark String Cleaning
PySpark's `functions` module provides efficient methods like `trim`, `regexp_replace`, and `lower` for cleaning string columns in DataFrames.
- Use `F.trim()` for leading/trailing whitespace.
- Use `F.regexp_replace()` for complex pattern-based cleaning (e.g., multiple spaces, special characters).
- Operations are vectorized for performance on large datasets.
Memory trick: Clean strings like a chef preps ingredients: trim, replace, standardize.