Text Columns

Harbor Coffee's staff write notes on every refund. Nobody ever reads them, because there are thousands. But hidden in that text are order numbers, amounts, and reasons. With a few lines of code, a messy notes column turns into a clean table.

• Staff notes on every refund
• Thousands, and nobody reads them
• Turn text into a table

Extracting with patterns

Here are four notes, written by different people, in different styles. The str accessor applies text operations to the entire column. Contains finds the refunds, ignoring upper and lower case. Extract uses a regular expression, with brackets around the part that we want. One grabs the digits after a hash sign. The other grabs a dollar amount, with optional cents. Run it. Clean columns, from messy notes, and the late delivery line stays empty, rather than breaking anything. And now we can add it all up.

import pandas as pd

notes = pd.Series(["Refund #1042 - $12.50", "refund #1077 ($9)", "Late delivery", "REFUND #1101 - $30"])

is_refund = notes.str.contains("refund", case=False)
ids = notes.str.extract(r"#(\d+)")[0]
amounts = notes.str.extract(r"\$(\d+(?:\.\d+)?)")[0].astype(float)

print(pd.DataFrame({"refund": is_refund, "id": ids, "amount": amounts}).to_string())
print("Refund total:", amounts.sum())

   refund    id  amount
0    True  1042    12.5
1    True  1077     9.0
2   False   NaN     NaN
3    True  1101    30.0
Refund total: 51.5

Text cleaning habits

A few habits will save you from pain. Always strip spaces and fix the case first. Check how many rows matched, because a pattern that silently misses half of your data looks just like a pattern that works. Look at the rows which didn't match. They're usually where the surprises are. And keep the original column, so you can always check your work.

Summary