Cleaning Text Columns
Three branches, but the export's branch column claims seven different values. Clerks typed " Pune", "pune" and "Pune ", and to a computer those are three different cities. A groupby on this column would split Pune's sales into three piles. Text needs cleaning before it can be a key.
The rule
.strapplies a string method to every value at once.
Here is the messy column from a new export, messy:
messy["branch"].tolist()
# [' Pune', 'delhi', 'Pune ', 'CHENNAI', 'Delhi', 'chennai', 'pune', 'Delhi']
messy["branch"].nunique() # 7Every Python string method you know has a column-wide twin behind .str:
messy["branch"] = messy["branch"].str.strip().str.title()
messy["branch"].nunique() # 3: Pune, Delhi, Chennaistrip() removes spaces from both ends; title() capitalises each word. Chained, they fold all seven spellings into three. lower() and upper() work too; pick one style and use it everywhere.
Other everyday cleaners:
# price arrives as text like "₹250"
messy["price"] = messy["price"].str.replace("₹", "").astype(int)
# genre arrives as "fiction/novel"; keep the part before the slash
messy["genre"] = messy["genre"].str.split("/").str[0]
# find rows by text
messy[messy["genre"].str.contains("sci")].str.split() gives a list per row, and .str[0] then picks the first item of each list.
For patterns, .str methods accept regular expressions (Python's re language for describing text shapes). To pull the number out of labels like "Order #1042":
orders["code"].str.extract(r"(\d+)") # a column holding '1042'\d+ means "one or more digits", and the parentheses mark the part to keep.
The shortcut
Before grouping or merging on a text column, run value_counts() and read the list. Near-duplicates jump out: Pune next to pune next to Pune . Clean, then run it again until each real category appears exactly once.
Look
messy["branch"].value_counts()lists 7 entries, several of them Pune in disguise.
The trap is invisible whitespace. "Pune " prints just like "Pune", so a filter == "Pune" silently misses it. When a filter returns fewer rows than you expect, suspect spaces first.
Drills
Answer fast — trust the rule.
What does pd.Series([" delhi "]).str.strip().str.upper() contain?
What does pd.Series(["fiction/novel"]).str.split("/").str[1] contain?
A filter df[df["city"] == "Pune"] misses rows you can see saying Pune. Most likely cause?
What does pd.Series(["Order #88"]).str.extract(r"(\d+)") give?
Remember
.strapplies a string method to every value at once.- Run
value_counts()before grouping or merging on text; clean until each category appears once. - Hidden spaces and mixed case are the usual culprits.