Pandas Cleaning Wrong Format

Data stored as the wrong type — like a date saved as plain text — will silently break sorting, filtering, and arithmetic until you convert it to the correct dtype.

Recognizing Wrong Format Data

A column can look fine when you print it and still be the wrong format underneath. The clearest tell is dtype: object where you expect datetime64 or a numeric type. Mixed date styles in the same column ('2021-03-01' next to '03/02/2021') are a strong sign the column was never actually parsed as a date.

Example

import pandas as pd

data = {
    'OrderID': [101, 102, 103, 104],
    'OrderDate': ['2021-03-01', '03/02/2021', '2021.03.03', 'Unknown']
}
df = pd.DataFrame(data)

print(df.dtypes)
# OrderID       int64
# OrderDate    object   <- dates stored as plain text

Converting Dates with to_datetime()

pd.to_datetime() parses a column of date-like strings into a real datetime64 dtype, which unlocks date arithmetic, correct chronological sorting, and .dt accessor methods like .dt.year or .dt.day_name(). Values it cannot parse, like 'Unknown', become NaT (Not a Time) instead of crashing the whole conversion.

Example

import pandas as pd

data = {
    'OrderID': [101, 102, 103, 104],
    'OrderDate': ['2021-03-01', '03/02/2021', '2021.03.03', 'Unknown']
}
df = pd.DataFrame(data)

df['OrderDate'] = pd.to_datetime(df['OrderDate'], errors='coerce')
print(df.dtypes)
print(df)
# The 'Unknown' row becomes NaT instead of raising an error

Note: errors='coerce' is what makes to_datetime() safe to use on messy real-world data: instead of stopping at the first unparseable value, it converts that value to NaT so you can find and handle it afterward with isnull().

Converting Other Data Types with astype()

Dates aren't the only format problem. Numbers imported from a CSV can arrive as text (object) if they contain currency symbols or thousands separators, and repeated text values are often better stored as category to save memory. astype() converts a column to a specified dtype in one call.

Example

import pandas as pd

data = {
    'OrderID': [101, 102, 103, 104],
    'Priority': ['High', 'Low', 'Low', 'High']
}
df = pd.DataFrame(data)

df['OrderID'] = df['OrderID'].astype(str)
df['Priority'] = df['Priority'].astype('category')
print(df.dtypes)
# OrderID       object
# Priority    category

object -> datetime64[ns] with pd.to_datetime() for date and time columns
object -> float64 or int64 with pd.to_numeric() for numbers stored as text
object -> category for columns with a small, repeated set of text values
int64 -> str with astype(str) when a numeric ID should be treated as text, not a quantity

Before	After	Conversion used
'2021-03-01' (object)	Timestamp (datetime64[ns])	pd.to_datetime()
'1,250' (object)	1250.0 (float64)	pd.to_numeric(errors='coerce')
'High' (object)	High (category)	astype('category')

Note: astype() raises a ValueError the instant it hits a value it cannot convert, which can crash a whole pipeline on one bad row. For numeric cleanup, prefer pd.to_numeric(df['col'], errors='coerce'), which behaves like to_datetime() and turns bad values into NaN instead of failing.

Keep Going

Pandas Interview QuestionsPractice real questions asked for Pandas Data Scientist SalarySee what Data Scientist roles pay in the market Auto Apply for JobsApply to open roles related to Pandas and tweak your resume