Attribute tables are full of typed text, and typed text is messy. Bengaluru, bengaluru and BANGALORE are the same place to a person and three different values to a computer. Joins, groupings and filters fail on such differences, so text cleaning comes before analysis.
What is .str?
In Part 1 you called methods on one string, like name.strip(). On a pandas column, put .str in front and the method runs on every row:
import pandas as pd
# A Series is one column of values. None means a missing value.
places = pd.Series([" bengaluru ", "MYSURU", None, "Mangaluru "])
# .str lets you use text tools on every value at once.
# strip() removes spaces at both ends, title() writes Each Word With A Capital.
print(places.str.strip().str.title().tolist()) # the missing value stays missing
print(places.str.len().tolist()) # how many characters each value has
['Bengaluru', 'Mysuru', None, 'Mangaluru']
[12.0, 6.0, nan, 11.0]
Missing values stay missing instead of causing an error (shown as None or NaN, depending on the pandas version). Methods can be chained, one after another.
1. Basic cleaning
df = pd.DataFrame({
"place": [" bengaluru ", "MYSURU", "mangaluru ", "Hubli-Dharwad", None],
"road": ["NH-44", "nh 48", "SH-17", "State Highway 12", "ROAD-10"],
})
# Clean the place names into a NEW column, so the original stays for comparison
df["place_clean"] = df["place"].str.strip().str.title()
print(df[["place", "place_clean"]])
place place_clean
0 bengaluru Bengaluru
1 MYSURU Mysuru
2 mangaluru Mangaluru
3 Hubli-Dharwad Hubli-Dharwad
4 None None
strip() removes spaces at both ends. lower(), upper() and title() change letter case. Compare the original and cleaned columns side by side before trusting the result.
2. Replace
replace fixes known spelling variants and abbreviations.
addresses = pd.Series(["12 MG Rd.", "4 Church St.", "7 Brigade Rd"])
# Chain several replacements. Each line works on the result of the line above.
# regex=False means "treat the text literally", so the dot in "Rd." is just a dot.
fixed = (
addresses
.str.replace("Rd.", "Road", regex=False) # first the version with a full stop
.str.replace("Rd", "Road", regex=False) # then the version without (careful: see the note below)
.str.replace("St.", "Street", regex=False)
)
print(fixed.tolist())
['12 MG Road', '4 Church Street', '7 Brigade Road']
With regex=False the text is matched literally. That matters because in a pattern a dot means any character. Without it, "Rd." would also match "Rda". Replacing Rd. before Rd avoids leaving a stray dot.
3. Contains, startswith and endswith
These return True or False for each row, so you can use them as filters (Part 7).
roads = pd.Series(["National Highway 44", "NH-48", "State Highway 12", "SH-17", "Local Road", None])
# Each of these returns True or False for every value
print(roads.str.contains("Highway").tolist()) # capital H only; missing value gives NaN
print(roads.str.contains("highway", case=False, na=False).tolist()) # ignore capitals; treat missing as False
print(roads.str.startswith("NH").tolist()) # begins with NH?
print(roads.str.endswith("Road", na=False).tolist()) # ends with Road?
[True, False, True, False, False, None]
[True, False, True, False, False, False]
[False, True, False, False, False, None]
[False, False, False, False, True, False]
contains looks anywhere in the text. case=False ignores letter case. na=False treats missing values as no match, which is usually what a filter needs, because a missing value in a filter causes an error. To keep the matching rows, use the result inside brackets: roads[roads.str.contains("Highway", na=False)].
4. Regular expressions, just enough
A regular expression (regex) is a pattern that describes text. You only need a few pieces:
| Pattern | Meaning | Example match |
|---|---|---|
\d | one digit | 4 |
\d+ | one or more digits | 44 |
[A-Z] | one capital letter | N |
[A-Z]{2} | exactly two capital letters | NH |
^ | start of the text | |
$ | end of the text | |
NH|SH | NH or SH | |
(...) | a group to capture |
Write regex patterns as raw strings (r"...") so that backslashes survive.
5. match versus contains
match checks from the start of the text. contains looks anywhere.
codes = pd.Series(["NH-44", "Road NH-44", "Old NH-44 Road", "NH-44 Junction", "SH-12", "NH44"])
# contains looks anywhere inside the text; match checks from the very start
print(codes.str.contains("NH").tolist())
print(codes.str.match("NH").tolist())
# A pattern (regular expression) describes the shape of the text:
# NH- the letters NH and a dash
# \d+ one or more digits
# $ the text must end here
print(codes.str.match(r"NH-\d+$").tolist())
[True, True, True, True, False, True]
[True, False, False, True, False, True]
[True, False, False, False, False, False]
The first result is True for five codes because NH appears somewhere in them. match("NH") is true only for the codes that begin with NH. The third pattern is stricter: starts with NH-, then one or more digits, then the end. Only an exact code like NH-44 passes, and NH44 (missing dash) and NH-44 Junction fail. This is how you validate a column against a rule.
# ^ means "start of text". (NH|SH) means "NH or SH".
# So this pattern accepts only codes such as NH-44 or SH-12 and nothing else.
valid = codes[codes.str.match(r"^(NH|SH)-\d+$")]
# The ~ sign flips True and False, so this keeps the rows that did NOT match
invalid = codes[~codes.str.match(r"^(NH|SH)-\d+$")]
print(valid.tolist())
print(invalid.tolist())
['NH-44', 'SH-12']
['Road NH-44', 'Old NH-44 Road', 'NH-44 Junction', 'NH44']
~ flips True and False, so the second line selects the invalid codes. A list of invalid records is as valuable as the clean one.
6. Split
split cuts text at a separator. expand=True puts the pieces in separate columns.
loc = pd.Series(["Bengaluru, Karnataka", "Mysuru, Karnataka", "Chennai, Tamil Nadu"])
# Split at each comma. expand=True puts each piece in its own column.
parts = loc.str.split(",", expand=True)
parts.columns = ["city", "state"] # give the two new columns names
parts["state"] = parts["state"].str.strip() # remove the space left after the comma
print(parts)
# The same idea works with any separator
print(pd.Series(["NH-44", "SH-17"]).str.split("-", expand=True))
city state
0 Bengaluru Karnataka
1 Mysuru Karnataka
2 Chennai Tamil Nadu
0 1
0 NH 44
1 SH 17
Split on the separator and clean the pieces afterwards, as the spaces after the commas show. Use n=1 to split only at the first separator.
7. Extract
extract pulls values out of text using the groups, the parts of the pattern in brackets. Named groups, written (?P<name>...), become the column names.
messy = pd.Series([
"NH-44 near Hosur",
"sh 17 Mysuru road",
"Route NH 48, PIN 560001",
"Unknown road",
])
# extract pulls the matching pieces out into columns.
# Each (...) is one piece; ?P<name> gives that piece a column name.
# (NH|SH) the prefix [- ]? an optional dash or space (\d+) the number
# flags=2 means "ignore capitals", so "sh" matches too. No match gives an empty row.
found = messy.str.extract(r"(?P<prefix>NH|SH)[- ]?(?P<number>\d+)", flags=2)
print(found)
prefix number
0 NH 44
1 sh 17
2 NH 48
3 NaN NaN
The pattern reads: the letters NH or SH, an optional dash or space ([- ]?), then one or more digits. flags=re.IGNORECASE ignores letter case, so sh 17 is found too. Rows without a match give NaN, which tells you which texts need a manual look. PIN codes (six digits, an Indian postal code) can be pulled the same way:
pins = pd.Series(["MG Road, Bengaluru 560001", "Church St 560025", "no code here"])
# \d{6} means exactly six digits in a row, which is the shape of an Indian PIN code
print(pins.str.extract(r"(\d{6})")[0].tolist())
['560001', '560025', nan]
\d{6} means exactly six digits. Always check the result, since six digits may also appear in other numbers.
8. Find all matches
extract returns the first match. findall returns all of them as a list.
route = pd.Series(["NH-44 then SH-17 then NH-48", "SH-12 only", "none"])
# extract returns the first match only; findall returns ALL matches in a list
matches = route.str.findall(r"(?:NH|SH)-\d+")
print(matches.tolist())
print(matches.str.len().tolist()) # how many road codes were found in each value
[['NH-44', 'SH-17', 'NH-48'], ['SH-12'], []]
[3, 1, 0]
(?:...) is a group that is not captured, so the whole code is returned. str.len() on a column of lists counts the matches. Use findall when one cell holds several values, and extract when it holds one.
9. Combine columns with cat
parts = pd.DataFrame({"city": ["Bengaluru", "Mysuru", None], "state": ["Karnataka", "Karnataka", "Karnataka"]})
# cat glues two columns together with a separator. na_rep says what to write for a missing value.
label = parts["city"].str.cat(parts["state"], sep=", ", na_rep="unknown")
print(label.tolist())
# Plain + also joins text, but a missing value makes the whole result missing, so fill it first
print((parts["city"].fillna("?") + " (" + parts["state"] + ")").tolist())
['Bengaluru, Karnataka', 'Mysuru, Karnataka', 'unknown, Karnataka']
['Bengaluru (Karnataka)', 'Mysuru (Karnataka)', '? (Karnataka)']
cat joins columns with a separator and na_rep fills the missing values. Plain + also works and is easy to read, once missing values are filled.
10. A full cleaning example
Combine the methods to clean a small table and report problems.
# Everything together: a messy table, cleaned column by column
raw = pd.DataFrame({
"place": [" bengaluru", "MYSURU ", "Mangaluru", "hubballi"],
"code": ["NH-44", " nh-48", "SH 17", "R-5"],
"address": ["12 MG Rd., 560001", "4 Church St., 570001", "Hampankatta", "Station Rd. 580020"],
})
clean = raw.copy() # work on a copy so the raw data is kept
# Step 1: place names, trimmed and capitalised
clean["place"] = clean["place"].str.strip().str.title()
# Step 2: road codes, trimmed, in capitals, with spaces turned into dashes
clean["code"] = clean["code"].str.strip().str.upper().str.replace(" ", "-", regex=False)
# Step 3: spell out the abbreviations in the address
clean["address"] = (
clean["address"]
.str.replace("Rd.", "Road", regex=False)
.str.replace("St.", "Street", regex=False)
)
# Step 4: pull the six digit PIN code out of the address into its own column
clean["pin"] = clean["address"].str.extract(r"(\d{6})")[0]
# Step 5: mark which codes have a valid shape (True or False)
clean["code_ok"] = clean["code"].str.match(r"^(NH|SH)-\d+$")
# Step 6: show the result, list the bad codes, and save the clean table
print(clean[["place", "code", "pin", "code_ok"]])
print("Invalid codes:", clean.loc[~clean["code_ok"], "code"].tolist())
clean.to_csv("places_clean.csv", index=False)
place code pin code_ok
0 Bengaluru NH-44 560001 True
1 Mysuru NH-48 570001 True
2 Mangaluru SH-17 NaN True
3 Hubballi R-5 580020 False
Invalid codes: ['R-5']
Every step is one line. The invalid code R-5 shows up in the report. Cleaning in a copy keeps the raw column available to compare with.
Run this part as a notebook
The code on this page is also a Jupyter notebook. It has the theory in text cells, then the code, then guided practice, an independent assignment and hidden solutions, so you can try each task before you look. Download the Part 9 notebook, open it in JupyterLab and run the cells from top to bottom. Part 1 shows how to install and start Jupyter.
Practice
- Standardise a column of state names so that
karnataka,KARNATAKAandKarnatakabecome one value, then count the unique values before and after. - Mark the roads whose code matches
^MDR-\d+$(major district roads). - Extract the PIN code and the house number from
"42, 5th Cross, Indiranagar 560038". - Split
"12.9716,77.5946"into two numeric columns.
Common mistakes
- Forgetting
.strand calling a string method on the whole column. - Forgetting
na=Falsein a filter, which causes an error when the column has missing values. - Using
containswhen the rule needs the start or the whole text. Usematchwith^and$. - Leaving
regex=Truewhen you meant to replace a literal dot or bracket. - Not looking at the rows that did not match.
- Writing patterns without the raw string prefix
r.