Three Spellings, One Drink

The Daily Grind wants a count of oat lattes sold, and the order log has the drink name on every row. It should be a filter. It isn't, because three baristas typed the drink three ways, and to an engine a capital letter or a stray space makes a different string.

Watch it happen

Six rows of order_log:

from pyspark.sql import functions as F

order_log.filter(F.col("drink") == "oat latte").count()   # 0

Nought. Cleaning takes three steps: strip the ends, settle the case, and squash the double space in row 3004:

result = order_log.withColumn(
    "drink_clean",
    F.regexp_replace(F.lower(F.trim(F.col("drink"))), " +", " "),
)

Now drink_clean == "oat latte" finds three rows, and it will keep finding them however the next barista types it.

The three cleaners

F.trim removes spaces from both ends. F.lower and F.upper settle the case either way; F.initcap gives Oat Latte when the text is for reading rather than matching. They nest inside out: the innermost runs first.

F.length is how you catch a space you can't see. "oat latte " is 10 characters, not 9, and that one character is why the filter found nothing.

Rewrite with a pattern

F.regexp_replace(column, pattern, replacement) swaps every match of a regular expression. Two patterns do most of the work:

F.regexp_replace("drink", " +", " ")        # one or more spaces become one
F.regexp_replace("drink", "[^a-z ]", "")    # anything not a letter or space goes

Note the second row: deleting the #2 leaves the space that was in front of it. Clean in the right order, or trim again at the end.

Find text inside text

like takes % as "anything"; rlike takes a regular expression. Both are as exact as ==, so clean first or match on the cleaned column:

order_log.filter(F.col("drink").like("%latte%")).count()            # 1
order_log.filter(F.lower(F.col("drink")).like("%latte%")).count()   # 3

Two more that earn their place. F.concat_ws(" ", "size", "drink") joins columns with a separator, which is how a label like large oat latte gets built without a null in either column poisoning the result. F.split("drink", " ") breaks a string into an array of words, which is the shape the Long or Wide lesson takes apart.

Two problems, in the order they build:

1. Tidy the Names — trim and settle the case.
2. Clean Phone Digits — keep only what a pattern allows.