Data Cleaning With Pandas

1. Introduction: What Is Data Cleaning?

Data cleaning is the process of detecting, correcting, or removing inaccurate, incomplete, corrupted, inconsistent, and irrelevant data from a dataset.

In any data project—analytics, BI, ML, or AI—clean data contributes ~80% of the model’s success. Without clean data, even the best model will fail.

Common Data Problems

• Missing values
• Duplicate rows
• Outliers and impossible values
• Wrong data types
• Inconsistent formatting
• Noisy text (spaces, typos, casing issues)
• Categorical inconsistencies
• Mixed units (kg vs lbs)
• Irregular date formats

Pandas provides powerful tools to handle these problems.

---

2. Loading and Understanding Data With Pandas

import pandas as pd

df = pd.read_csv("sample.csv")

Preview Dataset

df.head()
df.tail()
df.sample(5)

Understand Structure

df.info()
df.describe(include='all')
df.shape
df.columns

Detect Data Types

df.dtypes

3. Handling Missing Data

Identify Missing Values

df.isna().sum()
df.isnull().mean() * 100   # percentage

Remove Missing Data

df_clean = df.dropna()
df_clean = df.dropna(subset=['age', 'salary'])
df_clean = df.dropna(thresh=3)  # rows with at least 3 non-null values

Fill Missing Values

Fill with Mean / Median / Mode

df['age'] = df['age'].fillna(df['age'].mean())
df['salary'] = df['salary'].fillna(df['salary'].median())
df['gender'] = df['gender'].fillna(df['gender'].mode()[0])

Forward & Backward Fill

df['sales'].fillna(method='ffill', inplace=True)
df['sales'].fillna(method='bfill', inplace=True)

Fill using interpolation

df['temperature'] = df['temperature'].interpolate()

4. Handling Duplicates

Check duplicates

df.duplicated().sum()

Remove duplicates

df = df.drop_duplicates()

Remove duplicates based on specific columns

df = df.drop_duplicates(subset=['name', 'date'])

5. Handling Inconsistent Data

Detect impossible values

df[df['age'] < 0]
df[df['salary'] < 0]
df[df['height'] > 250]

Fix values

df.loc[df['age'] < 0, 'age'] = df['age'].median()

9. Feature Engineering

Feature Engineering

Create new columns

df['total_price'] = df['quantity'] * df['unit_price']

Binning

df['age_group'] = pd.cut(df['age'], bins=[0,18,40,60,100],
                         labels=['Teen','Adult','Middle','Senior'])

Encoding categorical variables

df = pd.get_dummies(df, columns=['gender', 'city'])

Date features

df['year'] = df['date'].dt.year
df['month'] = df['date'].dt.month
df['weekday'] = df['date'].dt.day_name()

6. Renaming Columns

Best practice

df.columns = df.columns.str.lower().str.replace(' ', '_')

Manual renaming

df.rename(columns={'Sale Price': 'sale_price'}, inplace=True)

11. Scaling & Normalization

Min-Max Scaling

from sklearn.preprocessing import MinMaxScaler

scaler = MinMaxScaler()
df[['salary_scaled']] = scaler.fit_transform(df[['salary']])

Standard Scaling

from sklearn.preprocessing import StandardScaler

scaler = StandardScaler()
df[['salary_z']] = scaler.fit_transform(df[['salary']])

---

12. End-to-End Example (Complete Walkthrough)

Sample Dataset

data = {
    'name': ['Ram ', 'Shyam', 'Hari', None, 'SITA'],
    'age': ['25', '30', None, '40', '-5'],
    'salary': [20000, 25000, None, 40000, 15000],
    'city': ['ktm', 'KTM', 'pokhara', 'Biratnagar', None],
    'join_date': ['2020/01/01', '2021/05/03', None, '2019/12/15', '2021/01/20']
}

df = pd.DataFrame(data)

Step 1: Clean Text

df['name'] = df['name'].str.strip().str.title()

Step 2: Fix Data Types

df['age'] = pd.to_numeric(df['age'], errors='coerce')
df['join_date'] = pd.to_datetime(df['join_date'], errors='coerce')

Step 3: Handle Missing Values

df['age'] = df['age'].fillna(df['age'].median())
df['salary'] = df['salary'].fillna(df['salary'].median())
df['city'] = df['city'].fillna('Unknown')

Step 4: Correct Incorrect Values

df.loc[df['age'] < 0, 'age'] = df['age'].median()

Step 5: Fix Categorical Inconsistency

df['city'] = df['city'].str.title()
df['city'].replace({'Ktm': 'Kathmandu', 'Ktm ': 'Kathmandu', \
    'KTM': 'Kathmandu'}, inplace=True)

Step 6: Feature Engineering

df['year_joined'] = df['join_date'].dt.year
df['salary_lakh'] = df['salary'] / 100000

Result

A clean dataset ready for:

• EDA
• Visualization
• Machine learning
• Storage

---

7. Summary & Best Practices

Data Cleaning Checklist

• Understand data structure
• Handle missing values
• Fix data types
• Remove duplicates
• Address outliers
• Correct inconsistent values
• Clean text fields
• Standardize categories
• Engineer meaningful features

Best Practices

• Clean before modeling
• Never drop data blindly
• Use visualizations to detect issues
• Automate cleaning for repeated tasks
• Document every step