Data Cleaning Techniques for Data Analysts

Missing Values (NULLs)

import pandas as pd
import numpy as np

df = pd.read_csv('orders.csv')

# --- Step 1: Find and profile missing values ---
null_report = pd.DataFrame({
    'null_count': df.isnull().sum(),
    'null_pct':   (df.isnull().sum() / len(df) * 100).round(1),
    'dtype':      df.dtypes
})
print(null_report[null_report['null_count'] > 0])

# --- Step 2: Understand WHY each column has nulls ---
# delivery_date null → check if these are undelivered orders
print(df[df['delivery_date'].isnull()]['status'].value_counts())
# → All are "processing" or "shipped" — expected. Fill with "Pending".

# rating null → check if any delivered orders have null rating
delivered_null_rating = df[(df['status']=='delivered') & df['rating'].isnull()]
print(f"Delivered orders missing rating: {len(delivered_null_rating)}")
# → 8,200 delivered orders with no rating — customer chose not to rate.

# --- Step 3: Apply the right strategy for each column ---

# Strategy A: Fill with a meaningful string (semantic null)
df['delivery_date'] = df['delivery_date'].fillna('Pending')

# Strategy B: Fill numeric with median (robust to outliers)
df['rating'] = df['rating'].fillna(df['rating'].median())

# Strategy C: Fill with group-specific median (better for skewed data)
df['delivery_days'] = df.groupby('city')['delivery_days'].transform(
    lambda x: x.fillna(x.median())
)

# Strategy D: Forward-fill for time-series data
df = df.sort_values('order_date')
df['stock_level'] = df['stock_level'].ffill()

# Strategy E: Drop rows where key identifier is null
before = len(df)
df = df.dropna(subset=['order_id', 'customer_id', 'amount_inr'])
print(f"Dropped {before - len(df)} rows with null key columns")

# Strategy F: Drop columns with >50% null (not enough data to be useful)
threshold = 0.5
cols_to_drop = null_report[null_report['null_pct'] > 50].index.tolist()
df = df.drop(columns=cols_to_drop)
print(f"Dropped columns: {cols_to_drop}")

-- Find nulls in each column
SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN delivery_date IS NULL THEN 1 ELSE 0 END) AS null_delivery,
    SUM(CASE WHEN rating IS NULL THEN 1 ELSE 0 END) AS null_rating,
    SUM(CASE WHEN amount_inr IS NULL THEN 1 ELSE 0 END) AS null_amount
FROM orders;

-- Fill null delivery_date with 'Pending'
UPDATE orders
SET delivery_date = 'Pending'
WHERE delivery_date IS NULL;

-- Use COALESCE in queries to handle nulls without modifying data
SELECT
    order_id,
    COALESCE(rating, 3.0) AS rating,          -- treat missing rating as neutral
    COALESCE(delivery_days, 5) AS delivery_days
FROM orders;

Duplicate Records

# --- Find duplicates ---
print(f"Exact duplicate rows: {df.duplicated().sum()}")
print(f"Duplicate order_ids:  {df['order_id'].duplicated().sum()}")

# Preview duplicates
dupes = df[df['order_id'].duplicated(keep=False)].sort_values('order_id')
print(dupes.head(20))

# --- Understand why duplicates exist ---
# Are the rows identical, or different (e.g., same order_id but different status)?
dupes_grouped = dupes.groupby('order_id').agg(
    row_count       = ('order_id',   'count'),
    unique_statuses = ('status',     'nunique'),
    unique_amounts  = ('amount_inr', 'nunique'),
)
print(dupes_grouped[dupes_grouped['row_count'] > 1])

# --- Remove duplicates: different strategies ---

# Keep first occurrence (by whatever row order the data came in)
df_clean = df.drop_duplicates(subset=['order_id'], keep='first')

# Keep the most recent record (keep last by date)
df = df.sort_values('updated_at', ascending=True)
df_clean = df.drop_duplicates(subset=['order_id'], keep='last')

# Keep the row with the most complete data (fewest nulls)
df['null_count'] = df.isnull().sum(axis=1)
df = df.sort_values('null_count')   # fewest nulls first
df_clean = df.drop_duplicates(subset=['order_id'], keep='first')
df_clean = df_clean.drop(columns=['null_count'])

print(f"Before: {len(df):,} rows  →  After: {len(df_clean):,} rows")

-- Find duplicate order_ids
SELECT order_id, COUNT(*) AS cnt
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

-- Remove duplicates: keep the row with the latest created_at
DELETE FROM orders
WHERE id NOT IN (
    SELECT MIN(id)
    FROM orders
    GROUP BY order_id
);

-- Or use ROW_NUMBER to keep the most recent per order_id
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY created_at DESC
        ) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

Wrong Data Types

# Common type problems and fixes

# --- Amount stored as text with ₹ and commas ---
# "₹1,250.00" → 1250.0
df['amount_inr'] = (
    df['amount_inr']
    .astype(str)
    .str.replace('₹', '', regex=False)
    .str.replace(',', '', regex=False)
    .str.strip()
)
df['amount_inr'] = pd.to_numeric(df['amount_inr'], errors='coerce')
# errors='coerce' → turns anything that still can't convert into NaN

# --- Date stored as various string formats ---
# "01/08/2026" or "2026-08-01" or "1 August 2026"
df['order_date'] = pd.to_datetime(df['order_date'], dayfirst=True, errors='coerce')
# dayfirst=True handles DD/MM/YYYY (common in Indian data)

# --- Pincode stored as number (loses leading zeros) ---
# 110001 should be "110001" not 110001
df['pincode'] = df['pincode'].astype(str).str.zfill(6)

# --- Boolean stored as "Yes"/"No" strings ---
df['is_returned'] = df['is_returned'].map({'Yes': True, 'No': False, 'yes': True, 'no': False})

# --- Category stored as int codes instead of labels ---
category_map = {1: 'Electronics', 2: 'Clothing', 3: 'FMCG', 4: 'Home', 5: 'Sports'}
df['category'] = df['category_code'].map(category_map)

# --- Verify types after fixing ---
print(df.dtypes)
print(df[['amount_inr', 'order_date', 'pincode']].head())

-- Cast types in a SELECT query (non-destructive)
SELECT
    order_id,
    CAST(amount_raw AS DECIMAL(10, 2)) AS amount_inr,
    STR_TO_DATE(order_date_raw, '%d/%m/%Y') AS order_date,  -- MySQL
    LPAD(CAST(pincode AS CHAR), 6, '0')    AS pincode
FROM orders_raw;

-- Alter column type permanently (destructive — backup first)
ALTER TABLE orders MODIFY COLUMN order_date DATE;
ALTER TABLE orders MODIFY COLUMN amount_inr DECIMAL(10, 2);

Inconsistent Text & Category Names

# --- Step 1: Profile the mess ---
print(df['city'].value_counts().head(30))
print(df['category'].value_counts())

# --- Step 2: Standardise case and whitespace (catches most problems) ---
for col in ['city', 'category', 'status', 'payment_method']:
    df[col] = df[col].str.strip().str.title()
# str.strip() removes leading/trailing spaces
# str.title() makes "DELHI" and "delhi" both become "Delhi"

# --- Step 3: Replace known aliases ---
city_map = {
    'New Delhi': 'Delhi',
    'Delhi Ncr': 'Delhi',
    'Bengaluru': 'Bangalore',
    'Blr': 'Bangalore',
    'Bombay': 'Mumbai',
    'Pune City': 'Pune',
    'Gurugram': 'Gurgaon',
}
df['city'] = df['city'].replace(city_map)

category_map = {
    'Electronics & Gadgets': 'Electronics',
    'Electronic': 'Electronics',
    'Clothes': 'Clothing',
    'Apparel': 'Clothing',
    'Fmcg': 'FMCG',
    'Fast Moving Consumer Goods': 'FMCG',
}
df['category'] = df['category'].replace(category_map)

# --- Step 4: Fuzzy matching for systematic typos (fuzzywuzzy/rapidfuzz) ---
from rapidfuzz import process, fuzz

canonical_cities = ['Delhi','Mumbai','Bangalore','Hyderabad','Noida','Pune','Chennai','Kolkata']

def standardise_city(city_name, choices=canonical_cities, threshold=85):
    if pd.isna(city_name): return city_name
    match, score, _ = process.extractOne(str(city_name), choices, scorer=fuzz.ratio)
    return match if score >= threshold else city_name

df['city'] = df['city'].apply(standardise_city)

# --- Step 5: Verify ---
print(df['city'].value_counts())

-- Standardise city names in SQL
UPDATE orders
SET city = CASE
    WHEN LOWER(TRIM(city)) IN ('new delhi', 'delhi ncr', 'delhi-ncr') THEN 'Delhi'
    WHEN LOWER(TRIM(city)) IN ('bengaluru', 'blr')                    THEN 'Bangalore'
    WHEN LOWER(TRIM(city)) IN ('bombay')                               THEN 'Mumbai'
    WHEN LOWER(TRIM(city)) IN ('gurugram')                             THEN 'Gurgaon'
    ELSE INITCAP(TRIM(city))  -- standardise case for everything else
END;

-- Find remaining inconsistencies: cities with similar-looking names
SELECT city, COUNT(*) AS cnt
FROM orders
GROUP BY city
ORDER BY city;

Outliers — Detect, Investigate, Decide

# --- IQR method ---
def detect_outliers_iqr(series, multiplier=1.5):
    Q1  = series.quantile(0.25)
    Q3  = series.quantile(0.75)
    IQR = Q3 - Q1
    return (series < Q1 - multiplier * IQR) | (series > Q3 + multiplier * IQR)

df['is_outlier_amount'] = detect_outliers_iqr(df['amount_inr'])
outliers = df[df['is_outlier_amount']]
print(f"Outliers: {len(outliers):,}  ({len(outliers)/len(df)*100:.1f}%)")

# --- Investigate: what kind of orders are they? ---
print(outliers.groupby('category').agg(
    count    = ('order_id',   'count'),
    avg_val  = ('amount_inr', 'mean'),
    max_val  = ('amount_inr', 'max'),
))

# Check if outliers are errors or legitimate
print(outliers[['order_id','category','amount_inr','quantity','status']].sort_values('amount_inr', ascending=False).head(20))

# --- Decision framework ---
# 1. Negative amount_inr → DATA ERROR → remove
df = df[df['amount_inr'] > 0]

# 2. Extremely high amounts in Electronics → LEGITIMATE (corporate orders) → keep
# 3. Delivery days > 30 → investigate by carrier and city first
long_delivery = df[df['delivery_days'] > 30]
print(long_delivery.groupby(['city', 'carrier'])['delivery_days'].agg(['count','mean','max']))

# 4. Winsorise (cap) instead of remove — for modelling, not reporting
p99 = df['amount_inr'].quantile(0.99)
df['amount_capped'] = df['amount_inr'].clip(upper=p99)
# Original column kept for reference; capped column used in models

-- Find orders with amount more than 3 standard deviations from mean
SELECT *
FROM orders
WHERE ABS(amount_inr - (SELECT AVG(amount_inr) FROM orders))
    > 3 * (SELECT STDDEV(amount_inr) FROM orders)
ORDER BY amount_inr DESC;

-- Find delivery days outliers by city
SELECT city,
    COUNT(*) AS orders,
    AVG(delivery_days) AS avg_days,
    MAX(delivery_days) AS max_days,
    SUM(CASE WHEN delivery_days > 14 THEN 1 ELSE 0 END) AS over_14_days
FROM orders
GROUP BY city
ORDER BY over_14_days DESC;

Date & Time Cleaning

# --- Parse dates with explicit format (safest) ---
df['order_date'] = pd.to_datetime(df['order_date'], format='%d/%m/%Y', errors='coerce')
# errors='coerce' converts unparseable dates to NaT (Not a Time) rather than crashing

# Check how many failed to parse
print(f"Unparseable dates: {df['order_date'].isnull().sum()}")
# If this is > 0, look at the raw values that failed
failed_rows = df[df['order_date'].isnull()]
print(failed_rows['order_date_raw'].value_counts().head(10))

# --- Mixed formats: try multiple formats ---
def parse_flexible_date(s):
    for fmt in ['%d/%m/%Y', '%Y-%m-%d', '%d-%m-%Y', '%d %B %Y']:
        try:
            return pd.to_datetime(s, format=fmt)
        except:
            continue
    return pd.NaT

df['order_date'] = df['order_date_raw'].apply(parse_flexible_date)

# --- Extract time components for analysis ---
df['order_year']        = df['order_date'].dt.year
df['order_month']       = df['order_date'].dt.month
df['order_month_name']  = df['order_date'].dt.strftime('%B')
df['order_quarter']     = df['order_date'].dt.quarter
df['order_weekday']     = df['order_date'].dt.day_name()

# --- Indian financial year (April–March) ---
df['financial_year'] = df['order_date'].apply(
    lambda d: f"FY{d.year}-{str(d.year+1)[2:]}" if d.month >= 4
              else f"FY{d.year-1}-{str(d.year)[2:]}"
)

# --- Logical date checks ---
# delivery_date should not be before order_date
invalid = df[df['delivery_date'] < df['order_date']]
print(f"Orders where delivery precedes order: {len(invalid)}")
df = df[~(df['delivery_date'] < df['order_date'])]  # remove invalid

-- Parse date stored as DD/MM/YYYY text in MySQL
SELECT
    order_id,
    STR_TO_DATE(order_date_text, '%d/%m/%Y') AS order_date
FROM orders;

-- Indian financial year classification
SELECT
    order_id,
    order_date,
    CASE
        WHEN MONTH(order_date) >= 4
            THEN CONCAT('FY', YEAR(order_date), '-', RIGHT(YEAR(order_date)+1, 2))
        ELSE
            CONCAT('FY', YEAR(order_date)-1, '-', RIGHT(YEAR(order_date), 2))
    END AS financial_year
FROM orders;

How much time do data analysts spend on data cleaning?

Multiple industry surveys consistently find that data analysts spend 50–80% of their working time on data preparation and cleaning — not on analysis itself. The specific percentage depends on the organisation's data maturity. At companies with well-maintained data warehouses and automated pipelines, an analyst might spend 20–30% on cleaning. At companies that rely on manual exports, legacy systems, or data entered by field teams, the percentage can exceed 70%. In Indian companies, particularly in FMCG, manufacturing, and logistics, manual data entry errors and inconsistent formats are common — meaning higher cleaning time. This is why data cleaning is a core analyst skill, not an incidental task. An analyst who cleans data slowly or incorrectly cannot produce timely or accurate analysis.

What is the difference between dropping and imputing missing values?

Dropping (deletion) removes rows or columns with missing values from the dataset. It is appropriate when: the proportion of missing values is small (under 5%), the missing values appear to be random (not systematic), and the remaining data is sufficient for the analysis. Imputing means filling missing values with a substitute — the column mean, median, mode, a forward-fill from the previous row, or a value predicted by a model. Imputing is appropriate when: the proportion of missing is too high to drop without losing too much data, the missingness is systematic (e.g., a delivery_date is always null for undelivered orders — fill with "Pending"), or the missing values occur in a column that is important for the analysis. The worst approach is to do nothing — leaving NULLs in numeric columns causes incorrect aggregations (SUM, AVG) and broken joins. Always make an explicit, documented decision about each column with missing values.

What are the most common data quality problems in Indian company datasets?

Based on real analyst experience with Indian company data, the most common problems are: (1) City name inconsistencies — "Bengaluru" vs "Bangalore", "New Delhi" vs "Delhi", "Mumbai" vs "Bombay" — especially when data is entered by field teams across regions; (2) Date format inconsistencies — "01/08/2026", "2026-08-01", "1 August 2026" in the same column, often from Excel files created by different teams; (3) Trailing spaces in text fields — "Noida " and "Noida" look the same on screen but do not match in SQL or GROUP BY; (4) Mixed case — "ELECTRONICS", "Electronics", "electronics" in the same category column; (5) Duplicate records from system exports or ETL errors; (6) Amount columns stored as text (sometimes with "₹" or commas: "₹1,250") rather than numbers; (7) Null values in key identifier columns (customer_id, order_id) from incomplete form submissions.

Should data cleaning be done in Python, SQL, or Excel?

The right tool depends on where the data lives and how often cleaning must be done. Use SQL when the data is in a database and you need to clean at the source or create a cleaned view — SQL cleaning runs on the server and does not require downloading large files. Use Python (pandas) when the data is in files (CSV, Excel), requires complex transformations (regex, fuzzy matching, ML-based imputation), or when you want to automate a repeatable cleaning pipeline. Use Excel or Power Query when the audience is non-technical, the file is small, or the cleaning is a one-time task that needs to be shared as a spreadsheet. In most Indian analyst roles, you will use all three — SQL for source cleaning, pandas for file-based cleaning and complex transforms, Excel/Power Query for quick one-off fixes and sharing results.