Lesson 7 of 8 · 8 min · Cleaning data
Cleaning data: data types, dates and text
What you will learn
- Convert columns to the right data types with
astypeandto_numeric - Parse dates reliably with
to_datetime, including day-first formats - Clean text with the
.strmethods and regular expressions - Standardise categories with a mapping
- Use
categoryand nullable types where they help
In simple words
A computer cannot add "₹4,50,000" because it is text with a symbol and commas, not a number. It cannot sort "10/07/2025" as a date until it knows whether that means 10 July or 7 October. Cleaning types means turning these look-alike texts into real numbers and dates, and tidying names and cities so that "pune", "Pune " and "PUNE" become one value.
Words to know
- Data type: the kind of value a column holds, such as integer, float, text or datetime.
- Parsing: reading text and turning it into a number or date.
- Regular expression: a pattern language for finding and replacing text.
- Coercion: forcing a conversion and turning failures into missing values.
Check the types first
df.dtypes
df.info()
A numeric-looking column shown as str (or object in older pandas) means it has non-numeric content. Common causes: currency symbols, commas, spaces, "N/A", or a trailing %.
Numbers
astype fails loudly on bad values; to_numeric can turn failures into missing values:
df["salary"] = (df["salary"].astype("str")
.str.replace("[₹,\\s]", "", regex=True) # remove ₹, commas, spaces
.replace({"N/A": pd.NA, "nan": pd.NA}))
df["salary"] = pd.to_numeric(df["salary"], errors="coerce")
errors="coerce" makes anything unparseable NaN, so count what was lost (df["salary"].isna().sum()) and inspect those rows rather than trusting the result. The other option is "raise" (the default), which stops with an error on the first bad value. The old "ignore" option was deprecated and is gone in recent versions.
Integers cannot hold missing values in plain NumPy types, which is why a column with gaps turns into float. pandas offers nullable types: astype("Int64") (capital I) keeps whole numbers and <NA> together. Use "float64" for decimals, and "Float64" for nullable decimals.
Rounding and units: keep money as integers in paise or round to two decimals; avoid comparing floats with ==.
Dates
df["signup"] = pd.to_datetime(df["signup"], errors="coerce")
Dangers with dates:
- Day-first versus month-first.
10/07/2025is 10 July in India but 7 October in the US. Be explicit:pd.to_datetime(s, dayfirst=True)or, best, give the format:pd.to_datetime(s, format="%d/%m/%Y"). - Mixed formats in one column (as in the sample, one row is
2025-06-10and another10/07/2025). Parse each format separately and combine, or useformat="mixed"(with care, since each element is guessed):
iso = pd.to_datetime(df["signup"], format="%Y-%m-%d", errors="coerce")
dmy = pd.to_datetime(df["signup"], format="%d/%m/%Y", errors="coerce")
df["signup"] = iso.fillna(dmy)
- Unparseable text (
"not known") becomesNaT(not a time) witherrors="coerce". - Excel serial numbers (45658):
pd.to_datetime(45658, unit="D", origin="1899-12-30"). - Time zones: attach with
.dt.tz_localize("Asia/Kolkata"), convert with.dt.tz_convert(...). In pandas 3, time zones use the standardzoneinfomodule rather thanpytz.
Once parsed, use the .dt accessor:
df["signup"].dt.year
df["signup"].dt.month_name()
df["signup"].dt.day_name()
df["signup"].dt.to_period("M")
(pd.Timestamp("2025-12-31") - df["signup"]).dt.days # days since signup
Note: pandas 3 infers microsecond resolution (datetime64[us]) for parsed strings, where older versions always used nanoseconds. Avoid converting dates to raw integers with astype("int64"); use .dt properties.
Text cleaning with .str
The .str accessor applies a string method to every value and skips missing values:
df["name"] = df["name"].str.strip().str.title() # " asha " -> "Asha"
df["city"] = df["city"].str.strip().str.title() # "pune" -> "Pune"
df["name"].str.lower()
df["name"].str.len()
df["name"].str.contains("sha", case=False, na=False)
df["name"].str.startswith("A")
df["email"].str.split("@").str[1] # the domain
df["name"].str.replace(r"\s+", " ", regex=True) # collapse spaces
Regular expressions help with patterns: extract digits, remove punctuation, validate a format.
df["phone"] = df["phone"].str.replace(r"\D", "", regex=True) # keep digits only
df["phone"] = df["phone"].str[-10:] # last 10 digits
df["pin"] = df["address"].str.extract(r"(\d{6})") # a 6-digit pincode
valid = df["email"].str.match(r"^[^@\s]+@[^@\s]+\.[a-z]{2,}$", case=False, na=False)
In pandas 3, text columns use the dedicated str dtype, which holds only text or missing values; operations behave the same, and assigning a number into a text column raises an error instead of silently mixing types.
Standardise categories
Variants of the same category ruin groupings. Count them with value_counts(), then map:
city_map = {"Hyd": "Hyderabad", "Hyderbad": "Hyderabad", "Bangalore": "Bengaluru", "Bombay": "Mumbai"}
df["city"] = df["city"].replace(city_map)
For many variants, build a lookup table (a small CSV) and merge it in, so the mapping is reusable and reviewable. The rapidfuzz or difflib libraries can suggest close matches for typos, but a person should approve the mapping.
Convert repeated text to the category dtype to save memory and speed groupby: df["city"] = df["city"].astype("category").
Validate after cleaning
assert df["age"].between(0, 100).all() or df["age"].isna().any()
assert df["signup"].notna().sum() >= 4
assert df["salary"].dtype.kind in "fi"
Write small checks like these (or use pandera or Great Expectations for bigger projects) so that if the next data file breaks your assumptions, your script stops and tells you.
Common mistakes
- Using
astype(int)on a column with commas or missing values, causing an error. - Day and month swapped when parsing slash dates.
- Silent
coercehiding how many values were lost. - Cleaning only the visible variants, missing "Pune " with a trailing space.
- Forgetting
regex=True(orFalse) instr.replaceand getting unexpected matches for characters like..
Try it yourself
- Clean the
salarycolumn in the sample into numbers and count the unparseable values. - Parse the
signupcolumn with both formats; count theNaT. - Normalise
nameandcityand checkvalue_counts(). - Extract the domain from a list of emails and keep only valid addresses with a regex.
- Add three
assertchecks that would fail if a future file broke your assumptions.
Check yourself
What does pd.to_numeric(s, errors="coerce") do with bad values?
It converts values it cannot parse into missing values (NaN), so you should count them and inspect the source rows.
Why is 10/07/2025 dangerous?
It can mean 10 July or 7 October depending on the convention. State it with dayfirst=True or an explicit format.
How do you remove spaces and fix capitalisation in a text column?
df["col"].str.strip().str.title().
Why does a whole-number column with missing values become float?
Standard NumPy integers cannot represent missing values, so pandas uses floats with NaN; use the nullable Int64 type to keep integers.
Interview questions
How do you convert a column like "₹1,25,000" to a number?
Strip the symbol and commas with str.replace using a regular expression, then convert with pd.to_numeric(..., errors="coerce"), and check how many values failed.
How would you standardise inconsistent city names?
Normalise case and spaces, list the distinct values with value_counts, build a mapping (or lookup table) from variants to the standard name, apply it, and re-check the counts.
How do you handle dates in several formats?
Parse by explicit format in separate passes and combine, or use format="mixed" cautiously; set errors="coerce", review the NaT rows and document the day-first assumption.
Summary
- Check
dtypes; fix numbers withstr.replaceandto_numeric, dates withto_datetimeand an explicit format. - Use
.strmethods and regular expressions for text, and mappings for categories. coerceis useful but always count what it turned into missing values.- Validate the cleaned data with simple assertions.
Going deeper
- Working with text data: the
.straccessor and thestrdtype. - Time series and date functionality: parsing, time zones and
.dt. - pandas.to_datetime and pandas.to_numeric: all parameters.
- Regular expression HOWTO: the Python tutorial for patterns.
See how much of this you can explain
A free mock interview asks you new questions on Python for data analysis with pandas for Data Analyst and scores every answer, with what to fix.