Cleaning and Transforming Data

Why clean data?

Real datasets commonly contain:

• Missing values – blank cells, NaN, None
• Duplicates – the same record entered twice
• Inconsistent labels – 'south', 'South ', 'SOUTH'
• Incorrect types – dates or numbers stored as text
• Invalid values – negative quantities, impossible dates

Cleaning makes analysis more dependable, but every change should follow a clear, documented rule, not a guess.

Example

# 1. Remove exact duplicate rows
df = df.drop_duplicates()
# 2. Standardise text labels
df['region'] = df['region'].str.strip().str.title()
# 3. Convert dates; invalid values become NaT (missing)
df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')
# 4. Convert currency text to numbers
df['amount'] = pd.to_numeric(
df['amount'].astype(str).str.replace('[₹,]', '', regex=True),
errors='coerce'
)

How it works

Missing values

Step 1 – Measure:

df.isna().sum() # missing count per column
df.isna().mean().round(3) * 100 # missing percentage per column

Step 2 – Look for patterns: Is missingness concentrated in one region, month, or device?

Step 3 – Choose a treatment:

Do not automatically replace every missing number with zero. A missing price is not a free product.

Common mistakes

• Overwriting the original data: keep the raw file unchanged; save a cleaned copy.
• Not documenting decisions: note what you changed and why.
• Not counting affected rows: compare len(df) before and after each step.
• Over-aggressive cleaning: a rule based on a wrong assumption can delete legitimate records.
• Chained assignment warnings: use df.loc[condition, 'col'] = value instead of df[condition]['col'] = value.

Try it: Inspect a dataset for missing values and duplicates. Standardise one category column and report how many values changed.