Data analysts spend up to 80% of their time cleaning data — not building models or creating dashboards. Messy data is the reality of every real-world project: missing values, wrong data types, inconsistent text formats, duplicates, and outliers that silently corrupt your analysis.
This guide walks through the complete data cleaning workflow using Python Pandas — from loading a messy dataset to exporting a production-ready clean file. Every technique here is battle-tested on real client projects.
Prerequisites
Install the required libraries before starting: pip install pandas numpy openpyxl. This guide uses Pandas 2.x syntax which is compatible with Python 3.8 and above.
1. Why Data Cleaning Matters
Consider this scenario: your sales dashboard shows revenue of ₹42 lakhs, but the finance team's Excel report shows ₹38 lakhs. The difference? One system recorded "Mumbai" while another recorded "mumbai" and a third used "MUM". Three entries for the same city, counted as three separate regions. This is a data quality problem — and it is more common than most organisations admit.
Dirty data causes:
- Incorrect aggregations — SUMIFS and GROUP BY silently miss records
- False trends in visualisations — a date stored as text won't sort correctly
- Machine learning models that train on noise instead of signal
- Business decisions made on numbers that don't reflect reality
Pandas gives you a systematic, repeatable, and auditable way to fix all of this.
2. Setup and Loading the Dataset
Python
import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')
# Load from CSV
df = pd.read_csv("sales_data.csv")
# Load from Excel
df = pd.read_excel("sales_data.xlsx", sheet_name="Sheet1")
# Load from multiple sheets at once
sheets = pd.read_excel("sales_data.xlsx", sheet_name=None)
df = pd.concat(sheets.values(), ignore_index=True)
# Common read options
df = pd.read_csv(
"sales_data.csv",
encoding="utf-8", # handle special characters
parse_dates=["OrderDate"], # auto-parse date columns
dtype={"ProductID": str}, # force specific type on load
na_values=["N/A", "--", ""], # treat these as NaN
)3. Explore First — Understand Before You Clean
Never start cleaning before understanding the shape and health of your dataset. These commands give you a complete picture in under a minute:
Python — Dataset Exploration
# ── Basic shape ──────────────────────────────────
print(df.shape) # (rows, columns)
print(df.columns.tolist()) # all column names
print(df.dtypes) # data type of each column
# ── First look ──────────────────────────────────
df.head() # first 5 rows
df.tail(10) # last 10 rows
df.sample(5) # 5 random rows
# ── Missing value summary ───────────────────────
missing = df.isnull().sum()
missing_pct = (df.isnull().sum() / len(df) * 100).round(2)
missing_df = pd.DataFrame({
'Missing Count' : missing,
'Missing %' : missing_pct
}).sort_values('Missing %', ascending=False)
print(missing_df[missing_df['Missing Count'] > 0])
# ── Statistical summary ──────────────────────────
df.describe() # numeric columns
df.describe(include='object') # text columns
# ── Duplicate check ──────────────────────────────
print(f"Duplicate rows: {df.duplicated().sum()}")
# ── Unique values per column ─────────────────────
for col in df.select_dtypes(include='object').columns:
print(f"{col}: {df[col].nunique()} unique → {df[col].unique()[:5]}")
Save Your Exploration Output
Run df.info() and df.describe() before and after cleaning. Compare them side-by-side — this is your data quality audit trail. In professional projects, save both outputs to a log file as documentation.
4. Handling Missing Values
Missing values are not all the same. A missing salary is different from a missing phone number. The right strategy depends on what the column represents and how much data is missing.
Python — Missing Value Strategies
# ── Strategy 1: Drop rows with too many missing values ──
df = df.dropna(thresh=len(df.columns) * 0.5) # drop rows missing 50%+ columns
# ── Strategy 2: Drop columns with too many missing ──────
df = df.dropna(axis=1, thresh=int(len(df) * 0.5)) # need at least 50% filled
# ── Strategy 3: Fill with a fixed value ─────────────────
df['Status'].fillna('Unknown', inplace=True)
df['Discount'].fillna(0, inplace=True)
# ── Strategy 4: Fill with statistical values ────────────
df['Revenue'].fillna(df['Revenue'].mean(), inplace=True) # mean
df['Quantity'].fillna(df['Quantity'].median(), inplace=True) # median (better for skewed data)
df['Category'].fillna(df['Category'].mode()[0], inplace=True) # mode (most common)
# ── Strategy 5: Forward / backward fill (time series) ───
df['Price'] = df['Price'].ffill() # fill with previous value
df['Price'] = df['Price'].bfill() # fill with next value
# ── Strategy 6: Fill based on group average ─────────────
df['Revenue'] = df.groupby('Region')['Revenue'].transform(
lambda x: x.fillna(x.median())
)
# ── Verify no missing values remain ─────────────────────
print(f"Remaining nulls: {df.isnull().sum().sum()}")5. Fixing Data Types
Wrong data types are one of the most common and damaging data quality issues. A revenue column stored as text will sum to zero. A date stored as a string won't sort or filter correctly.
Python — Fixing Data Types
# ── Convert to numeric ──────────────────────────────────
df['Revenue'] = pd.to_numeric(df['Revenue'], errors='coerce')
df['Quantity'] = pd.to_numeric(df['Quantity'], errors='coerce')
# errors='coerce' → invalid values become NaN (not an error)
# ── Remove currency symbols before converting ────────────
df['Revenue'] = (
df['Revenue']
.astype(str)
.str.replace('₹', '')
.str.replace(',', '')
.str.strip()
)
df['Revenue'] = pd.to_numeric(df['Revenue'], errors='coerce')
# ── Convert to datetime ──────────────────────────────────
df['OrderDate'] = pd.to_datetime(df['OrderDate'], errors='coerce')
df['DeliveryDate']= pd.to_datetime(df['DeliveryDate'], errors='coerce')
# Custom date format
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'], format='%d-%m-%Y')
# ── Extract date parts ───────────────────────────────────
df['Year'] = df['OrderDate'].dt.year
df['Month'] = df['OrderDate'].dt.month
df['Quarter'] = df['OrderDate'].dt.quarter
df['Weekday'] = df['OrderDate'].dt.day_name()
# ── Convert to category (saves memory, faster groupby) ───
df['Region'] = df['Region'].astype('category')
df['Category'] = df['Category'].astype('category')
# ── Convert integers ─────────────────────────────────────
df['OrderID'] = df['OrderID'].astype('Int64') # Int64 (capital I) allows NaN
# ── Verify final types ───────────────────────────────────
print(df.dtypes)6. Removing Duplicates
Python — Removing Duplicates
# ── Check duplicates ─────────────────────────────────────
print(f"Total rows : {len(df)}")
print(f"Duplicate rows : {df.duplicated().sum()}")
# View duplicate rows
df[df.duplicated(keep=False)].sort_values('OrderID')
# ── Remove exact duplicates (all columns match) ──────────
df = df.drop_duplicates()
# ── Remove duplicates based on key columns only ──────────
df = df.drop_duplicates(subset=['OrderID'], keep='first')
# keep='first' → keep first occurrence
# keep='last' → keep last occurrence (useful if later records are more updated)
# keep=False → drop ALL duplicates
# ── Remove duplicates keeping the most recent record ─────
df = df.sort_values('UpdatedAt', ascending=False)
df = df.drop_duplicates(subset=['CustomerID'], keep='first')
# ── Reset index after dropping ───────────────────────────
df = df.reset_index(drop=True)
print(f"Rows after dedup: {len(df)}")7. Cleaning String and Text Data
Inconsistent text is one of the most overlooked data quality issues. "Mumbai", "mumbai", "MUMBAI", and "Mumbai " (trailing space) are four different values in Pandas — but represent the same city. The .str accessor gives you the full power of string operations on every row simultaneously.
Python — String Cleaning
# ── Standardise casing ──────────────────────────────────
df['Region'] = df['Region'].str.strip().str.title()
df['Category'] = df['Category'].str.strip().str.upper()
df['Email'] = df['Email'].str.strip().str.lower()
# ── Remove special characters ────────────────────────────
df['PhoneNo'] = df['PhoneNo'].str.replace(r'[^0-9]', '', regex=True)
df['Name'] = df['Name'].str.replace(r'[^a-zA-Z\s]', '', regex=True)
# ── Replace inconsistent values ──────────────────────────
region_map = {
'mum' : 'Mumbai',
'MUM' : 'Mumbai',
'bombay' : 'Mumbai',
'delhi' : 'Delhi',
'DEL' : 'Delhi',
'new delhi': 'Delhi',
}
df['Region'] = df['Region'].str.strip().str.lower().replace(region_map)
# ── Extract structured data from text ────────────────────
# Split "PROD-001-Electronics" into 3 parts
df[['Prefix', 'Code', 'Type']] = df['ProductCode'].str.split('-', expand=True)
# Extract numbers from text ("Order #1042 processed")
df['OrderNum'] = df['Notes'].str.extract(r'#(\d+)')
# ── Validate email format ────────────────────────────────
email_pattern = r'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$'
df['EmailValid'] = df['Email'].str.match(email_pattern)
# ── Check what invalid values remain ────────────────────
print(df['Region'].value_counts())8. Renaming and Restructuring Columns
Python — Column Cleanup
# ── Rename specific columns ──────────────────────────────
df = df.rename(columns={
'ord_id' : 'OrderID',
'cust_name': 'CustomerName',
'rev' : 'Revenue',
'qty' : 'Quantity',
})
# ── Standardise all column names at once ────────────────
df.columns = (
df.columns
.str.strip()
.str.lower()
.str.replace(' ', '_')
.str.replace(r'[^a-z0-9_]', '', regex=True)
)
# ── Drop unnecessary columns ─────────────────────────────
df = df.drop(columns=['Unnamed: 0', 'temp_col', 'notes'], errors='ignore')
# ── Reorder columns ──────────────────────────────────────
desired_order = ['OrderID', 'OrderDate', 'CustomerName',
'Region', 'Category', 'Product',
'Quantity', 'Revenue']
df = df[[c for c in desired_order if c in df.columns]]
# ── Add calculated columns ───────────────────────────────
df['UnitPrice'] = df['Revenue'] / df['Quantity']
df['YearMonth'] = df['OrderDate'].dt.to_period('M').astype(str)
df['IsHighValue'] = df['Revenue'] > 500009. Detecting and Handling Outliers
Outliers can be legitimate extreme values (a large enterprise order) or data errors (a revenue of ₹999999999 from a miskeyed entry). Always investigate before removing — context matters.
Python — Outlier Detection
# ── Method 1: IQR (Interquartile Range) ─────────────────
Q1 = df['Revenue'].quantile(0.25)
Q3 = df['Revenue'].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
outliers = df[(df['Revenue'] < lower_bound) | (df['Revenue'] > upper_bound)]
print(f"Outliers found: {len(outliers)}")
print(outliers[['OrderID', 'Revenue']])
# ── Option A: Remove outliers ────────────────────────────
df_clean = df[(df['Revenue'] >= lower_bound) & (df['Revenue'] <= upper_bound)]
# ── Option B: Cap outliers (Winsorizing) ─────────────────
df['Revenue'] = df['Revenue'].clip(lower=lower_bound, upper=upper_bound)
# ── Option C: Flag outliers (keep but mark) ──────────────
df['IsOutlier'] = (df['Revenue'] < lower_bound) | (df['Revenue'] > upper_bound)
# ── Method 2: Z-Score (for normally distributed data) ────
from scipy import stats
z_scores = np.abs(stats.zscore(df['Revenue'].dropna()))
df = df[(z_scores < 3)] # keep rows within 3 standard deviations
# ── Business rule validation ─────────────────────────────
df = df[df['Quantity'] > 0] # quantity must be positive
df = df[df['Revenue'] >= 0] # revenue cannot be negative
df = df[df['OrderDate'] <= pd.Timestamp.today()] # no future dates10. Standardising Categories
Python — Category Standardisation
# ── Label encoding (for ordinal categories) ──────────────
size_map = {'Small': 1, 'Medium': 2, 'Large': 3, 'Enterprise': 4}
df['SizeCode'] = df['CompanySize'].map(size_map)
# ── One-hot encoding (for nominal categories) ────────────
df = pd.get_dummies(df, columns=['Region'], prefix='Region')
# ── Group rare categories into 'Other' ───────────────────
top_categories = df['Category'].value_counts().nlargest(5).index
df['Category'] = df['Category'].where(
df['Category'].isin(top_categories), 'Other'
)
# ── Boolean flags from text ──────────────────────────────
df['IsPremium'] = df['Plan'].str.contains('Premium', case=False, na=False)11. Complete Reusable Cleaning Pipeline
The best practice is to wrap your cleaning steps into a function. This makes the process repeatable, testable, and easy to hand over to a colleague or schedule in a cron job.
Python — Production Cleaning Pipeline
"""
data_cleaner.py
Reusable data cleaning pipeline for sales data
Author : Ankit Kumar | etlguru.in
"""
import pandas as pd
import numpy as np
import logging
from datetime import datetime
logging.basicConfig(level=logging.INFO, format="%(asctime)s [%(levelname)s] %(message)s")
log = logging.getLogger(__name__)
def load_data(filepath: str) -> pd.DataFrame:
log.info(f"Loading: {filepath}")
if filepath.endswith('.csv'):
return pd.read_csv(filepath, encoding='utf-8', na_values=['N/A', '--', '', ' '])
return pd.read_excel(filepath, na_values=['N/A', '--', '', ' '])
def clean_data(df: pd.DataFrame) -> pd.DataFrame:
initial_rows = len(df)
log.info(f"Starting: {initial_rows} rows, {len(df.columns)} columns")
# ── 1. Standardise column names ──────────────────────────
df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')
# ── 2. Drop empty rows and columns ───────────────────────
df = df.dropna(how='all')
df = df.dropna(axis=1, how='all')
# ── 3. Remove duplicates ─────────────────────────────────
before = len(df)
df = df.drop_duplicates(subset=['order_id'], keep='first')
log.info(f"Removed {before - len(df)} duplicates")
# ── 4. Fix data types ────────────────────────────────────
df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')
df['revenue'] = pd.to_numeric(
df['revenue'].astype(str).str.replace(r'[^\d.]', '', regex=True),
errors='coerce'
)
df['quantity'] = pd.to_numeric(df['quantity'], errors='coerce')
# ── 5. Handle missing values ─────────────────────────────
df['revenue'].fillna(df['revenue'].median(), inplace=True)
df['quantity'].fillna(1, inplace=True)
df['region'].fillna('Unknown', inplace=True)
df = df.dropna(subset=['order_id', 'order_date']) # key columns must exist
# ── 6. Clean text columns ────────────────────────────────
df['region'] = df['region'].str.strip().str.title()
df['category'] = df['category'].str.strip().str.upper()
# ── 7. Business rule validation ──────────────────────────
before = len(df)
df = df[(df['revenue'] >= 0) & (df['quantity'] > 0)]
log.info(f"Removed {before - len(df)} invalid rows")
# ── 8. Add derived columns ───────────────────────────────
df['year'] = df['order_date'].dt.year
df['month'] = df['order_date'].dt.month
df['quarter'] = df['order_date'].dt.quarter
df['unit_price'] = (df['revenue'] / df['quantity']).round(2)
df['cleaned_at'] = datetime.now().strftime('%Y-%m-%d %H:%M')
df = df.reset_index(drop=True)
log.info(f"Done: {len(df)} clean rows ({initial_rows - len(df)} removed)")
return df
def save_data(df: pd.DataFrame, output: str) -> None:
if output.endswith('.csv'):
df.to_csv(output, index=False, encoding='utf-8-sig')
else:
df.to_excel(output, index=False, sheet_name='CleanData')
log.info(f"Saved: {output} ({len(df)} rows)")
if __name__ == '__main__':
raw = load_data("sales_raw.csv")
clean = clean_data(raw)
save_data(clean, "sales_clean.csv")
save_data(clean, "sales_clean.xlsx")12. Quick Reference — Most Used Commands
| Task | Pandas Command |
|---|---|
| Check missing values | df.isnull().sum() |
| Fill missing with value | df['col'].fillna(0) |
| Fill missing with median | df['col'].fillna(df['col'].median()) |
| Drop rows with nulls | df.dropna(subset=['col']) |
| Remove duplicates | df.drop_duplicates(subset=['id']) |
| Convert to number | pd.to_numeric(df['col'], errors='coerce') |
| Convert to date | pd.to_datetime(df['col'], errors='coerce') |
| Strip & lowercase text | df['col'].str.strip().str.lower() |
| Replace values | df['col'].replace({'old': 'new'}) |
| Remove special chars | df['col'].str.replace(r'[^a-z0-9]', '', regex=True) |
| Extract with regex | df['col'].str.extract(r'(\d+)') |
| Cap outliers | df['col'].clip(lower=lb, upper=ub) |
| Rename columns | df.rename(columns={'old': 'new'}) |
| Drop columns | df.drop(columns=['col'], errors='ignore') |
| Export to CSV | df.to_csv('file.csv', index=False) |
| Export to Excel | df.to_excel('file.xlsx', index=False) |
Always Keep the Raw Data Untouched
Never overwrite your source file. Work on a copy: df_clean = df.copy(). Save your original file in a separate /raw folder and output to a /clean folder. This way, if your cleaning logic has a bug, you can rerun on the original without re-downloading or re-exporting from the source system.