Lesson 7 of 8 · 8 min · Cleaning data

Cleaning data: data types, dates and text

Covers: pandas 3.x. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • Convert columns to the right data types with astype and to_numeric
  • Parse dates reliably with to_datetime, including day-first formats
  • Clean text with the .str methods and regular expressions
  • Standardise categories with a mapping
  • Use category and 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/2025 is 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-10 and another 10/07/2025). Parse each format separately and combine, or use format="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") becomes NaT (not a time) with errors="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 standard zoneinfo module rather than pytz.

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 coerce hiding how many values were lost.
  • Cleaning only the visible variants, missing "Pune " with a trailing space.
  • Forgetting regex=True (or False) in str.replace and getting unexpected matches for characters like ..

Try it yourself

  1. Clean the salary column in the sample into numbers and count the unparseable values.
  2. Parse the signup column with both formats; count the NaT.
  3. Normalise name and city and check value_counts().
  4. Extract the domain from a list of emails and keep only valid addresses with a regex.
  5. Add three assert checks 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 with str.replace and to_numeric, dates with to_datetime and an explicit format.
  • Use .str methods and regular expressions for text, and mappings for categories.
  • coerce is useful but always count what it turned into missing values.
  • Validate the cleaned data with simple assertions.

Going deeper

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.