Practice: Cleaning Text Columns
A complete text-cleaning project on car names and IPL player data: normalise case and spaces, fix brand typos, split brand/model, extract numbers and categories.
This topic puts every .str method together, the way you would in a real project. The first half follows Munna's Car Pricing notebooks (videos/notebooks 04 and 05): the car name column mixes brand, model, engine size and variant in one messy string, and the brand is the most important feature for price analysis. The second half cleans a messy IPL squad sheet.
Definition
Text cleaning is turning free-form, inconsistent text into consistent, structured values — fixing case, spaces and spelling, then splitting or extracting the parts you need into their own typed columns, so they can be grouped, counted, joined and used in models.
- Consistent — one spelling per real-world thing (
"maruti","Maruti ","maruthi"→"Maruti"). - Structured — one fact per column (
brand,model,engine_l). - Typed — numbers extracted from text are converted to numeric dtypes.
The text-cleaning recipe
text
raw text ──► strip + collapse spaces ──► fix case ──► fix spellings (replace)
──► split / extract pieces ──► convert types ──► check with value_counts()Step 1 — the messy car data
python
cars = pd.DataFrame({
"CarName": ["maruti swift dzire VDI", "Maruti Alto 800 LXI ", " hyundai Verna 1.6 SX",
"toyouta corolla altis", "vw Polo 1.2 GT", "Maruthi Wagon R LXI",
"Mahindra XUV500 W8", "Honda City 1.5 V (Petrol)"],
"price_lakh": [5.5, 2.2, 7.5, 9.0, 6.0, 3.0, 12.0, 8.5],
})
print(cars)
print(cars["CarName"].str.split().str[0].value_counts())text
CarName price_lakh
0 maruti swift dzire VDI 5.5
1 Maruti Alto 800 LXI 2.2
2 hyundai Verna 1.6 SX 7.5
3 toyouta corolla altis 9.0
4 vw Polo 1.2 GT 6.0
5 Maruthi Wagon R LXI 3.0
6 Mahindra XUV500 W8 12.0
7 Honda City 1.5 V (Petrol) 8.5
CarName
maruti 1
Maruti 1
hyundai 1
toyouta 1
vw 1
Maruthi 1
Mahindra 1
Honda 1
Name: count, dtype: int64The brand counts are a mess: maruti, Maruti and Maruthi are one brand; toyouta is a typo of Toyota and vw is short for Volkswagen.
Step 2 — normalise spaces and case
python
cars["name"] = (cars["CarName"]
.str.replace(r"\s+", " ", regex=True)
.str.strip()
.str.title())
print(cars["name"].tolist())text
['Maruti Swift Dzire Vdi', 'Maruti Alto 800 Lxi', 'Hyundai Verna 1.6 Sx', 'Toyouta Corolla Altis', 'Vw Polo 1.2 Gt', 'Maruthi Wagon R Lxi', 'Mahindra Xuv500 W8', 'Honda City 1.5 V (Petrol)']\s+ → one space fixes "Maruthi Wagon", strip() removes the edge spaces, and title() gives every word one style. Notice title case also turns codes like VDI into Vdi — harmless for grouping; if you need the original codes, keep the raw CarName column (we did) and clean a copy.
Step 3 — split brand and model
python
cars[["brand", "model"]] = cars["name"].str.split(" ", n=1, expand=True)
print(cars[["brand", "model"]])text
brand model
0 Maruti Swift Dzire Vdi
1 Maruti Alto 800 Lxi
2 Hyundai Verna 1.6 Sx
3 Toyouta Corolla Altis
4 Vw Polo 1.2 Gt
5 Maruthi Wagon R Lxi
6 Mahindra Xuv500 W8
7 Honda City 1.5 V (Petrol)How it works: n=1 cuts at the first space only: the brand is the first word and everything else is the model. This is the same idea as Munna's df['name'].apply(lambda x: x.split(' ')[0].upper()), but done with .str so it is NaN-safe and shorter.
Step 4 — fix brand spellings
python
brand_fix = {"Maruthi": "Maruti", "Toyouta": "Toyota", "Vw": "Volkswagen"}
cars["brand"] = cars["brand"].replace(brand_fix)
print(cars["brand"].value_counts())text
brand
Maruti 3
Hyundai 1
Toyota 1
Volkswagen 1
Mahindra 1
Honda 1
Name: count, dtype: int64How it works: replace with a dict only changes the listed typos and keeps every correct brand untouched (with map the correct brands would turn into NaN).
Step 5 — extract engine size and fuel
python
cars["engine_l"] = cars["name"].str.extract(r"(\d\.\d)", expand=False).astype(float)
cars["fuel"] = cars["name"].str.extract(r"\((\w+)\)", expand=False).fillna("Unknown")
print(cars[["name", "engine_l", "fuel"]])text
name engine_l fuel
0 Maruti Swift Dzire Vdi NaN Unknown
1 Maruti Alto 800 Lxi NaN Unknown
2 Hyundai Verna 1.6 Sx 1.6 Unknown
3 Toyouta Corolla Altis NaN Unknown
4 Vw Polo 1.2 Gt 1.2 Unknown
5 Maruthi Wagon R Lxi NaN Unknown
6 Mahindra Xuv500 W8 NaN Unknown
7 Honda City 1.5 V (Petrol) 1.5 PetrolHow it works: (\d\.\d) captures a digit, a literal dot and a digit (1.6). \((\w+)\) captures the word inside brackets. Rows without a match get NaN, which we keep for engine size and label "Unknown" for fuel.
Step 6 — use the clean columns
python
summary = cars.groupby("brand")["price_lakh"].agg(["count", "mean"]).round(2)
print(summary.sort_values("mean", ascending=False))text
count mean
brand
Mahindra 1 12.00
Toyota 1 9.00
Honda 1 8.50
Hyundai 1 7.50
Volkswagen 1 6.00
Maruti 3 3.57Before cleaning, this table would have split Maruti across three rows and shown nonsense brands like Toyouta.
Part 2 — an IPL squad sheet
python
sheet = pd.DataFrame({
"player": [" virat KOHLI", "Rohit sharma (c)", "jasprit bumrah", "Rishabh Pant (wk)", "SURYAKUMAR yadav"],
"team_role": ["RCB-Batter", "MI-Batter", "MI-Bowler", "DC-WK Batter", "MI-Batter"],
"contact": ["virat@rcb.in", "rohit@mi.com", "bumrah@mi.com", "pant@dc.in", None],
})
sheet["captain"] = sheet["player"].str.contains("(c)", regex=False)
sheet["player"] = (sheet["player"]
.str.replace(r"\s*\(.*\)", "", regex=True)
.str.replace(r"\s+", " ", regex=True)
.str.strip()
.str.title())
sheet[["team", "role"]] = sheet["team_role"].str.split("-", n=1, expand=True)
sheet["domain"] = sheet["contact"].str.extract(r"@([\w.]+)", expand=False)
print(sheet.drop(columns=["team_role", "contact"]))
print(sheet["team"].value_counts())text
player captain team role domain
0 Virat Kohli False RCB Batter rcb.in
1 Rohit Sharma True MI Batter mi.com
2 Jasprit Bumrah False MI Bowler mi.com
3 Rishabh Pant False DC WK Batter dc.in
4 Suryakumar Yadav False MI Batter NaN
team
MI 3
RCB 1
DC 1
Name: count, dtype: int64How it works: we record the captain flag before removing the (c) tag, clean the names (remove tags, collapse spaces, strip, title case), split team_role at the first -, and extract the email domain. The missing contact just gives NaN.
Common mistakes
- ❌ Splitting before cleaning spaces —
" hyundai"splits into an empty first piece. ✅ Strip and collapse spaces first. - ❌ Fixing typos with
map→ correct values becomeNaN. ✅replacewith a dict. - ❌ Losing information while cleaning (removing
(c)before saving the captain flag). ✅ Extract flags first, then clean. - ❌ Leaving extracted numbers as text. ✅
astype(float)/pd.to_numeric. - ❌ Not checking the result. ✅ Finish with
value_counts()/unique()on each cleaned column.
Checklist
| Step | Code |
|---|---|
| spaces | .str.replace(r"\s+", " ", regex=True).str.strip() |
| case | .str.title() / .str.lower() / .str.upper() |
| typos | .replace({"Toyouta": "Toyota"}) |
| split | .str.split(" ", n=1, expand=True) |
| extract | .str.extract(r"(\d\.\d)", expand=False) |
| flags | .str.contains("(c)", regex=False) |
| verify | .value_counts(), .unique() |
Interview tip
Q: The brand column shows 30 unique values but you expect 25. What do you do?
A: Print value_counts() (or unique()), normalise case/spaces with .str.strip().str.title(), then fix the remaining spelling variants with a replace dict, and re-check nunique().
Key points to remember
- Clean in order: spaces → case → spellings → split/extract → convert types → verify.
- Collapse inner spaces with str.replace(r'\s+', ' ', regex=True) before splitting.
- str.split(' ', n=1, expand=True) separates a brand (first word) from the model (rest).
- Fix typos and abbreviations with replace({...}) so correct values stay unchanged.
- str.extract with a group pulls out numbers like engine size; convert them with astype(float).
- Save flags (like a captain '(c)' tag) before removing them from the text.
- Always verify each cleaned column with value_counts() or unique().
Try it yourself
Car names → brand and model
Extract engine size and average price per brand
Practice: clean an IPL squad sheet
Revision Flashcards
Tap a card to flip it and see the answer.