Data Cleaning with Python

import pandas as pd

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

# Overview
print(f"Shape: {df.shape}")
print(f"\nData types:\n{df.dtypes}")
print(f"\nMissing values:\n{df.isnull().sum()}")
print(f"\nDuplicate rows: {df.duplicated().sum()}")

# Statistical summary
print(df.describe())

# Unique values in categorical columns
for col in ['status', 'country', 'category']:
    if col in df.columns:
        print(f"\n{col} unique values: {df[col].unique()}")
        print(f"{col} value counts:\n{df[col].value_counts()}")

# Check for duplicates
print(f"Duplicate rows: {df.duplicated().sum()}")

# View the duplicate rows
df[df.duplicated()]

# Drop duplicates (keep first occurrence)
df = df.drop_duplicates()

# Drop duplicates based on specific columns
df = df.drop_duplicates(subset=['order_id'])

# Drop duplicates keeping the last occurrence
df = df.drop_duplicates(subset=['order_id'], keep='last')

# Count missing values
df.isnull().sum()

# Percentage missing
(df.isnull().sum() / len(df) * 100).round(2)

# Drop rows where specific columns have missing values
df = df.dropna(subset=['customer_id', 'order_date'])

# Fill missing values
df['region'].fillna('Unknown', inplace=True)
df['discount'].fillna(0, inplace=True)
df['age'].fillna(df['age'].median(), inplace=True)  # fill with median

# Forward fill (carry last known value forward -- useful for time series)
df['status'] = df['status'].ffill()

# Convert to numeric (errors='coerce' converts non-numeric to NaN)
df['amount'] = pd.to_numeric(df['amount'], errors='coerce')

# Convert to datetime
df['order_date'] = pd.to_datetime(df['order_date'])
df['order_date'] = pd.to_datetime(df['order_date'], format='%d/%m/%Y')  # specify format

# Convert to string
df['product_id'] = df['product_id'].astype(str)

# Convert to categorical (saves memory for low-cardinality columns)
df['status'] = df['status'].astype('category')

# Check after conversion
print(df.dtypes)

# Strip whitespace
df['name'] = df['name'].str.strip()

# Change case
df['country'] = df['country'].str.title()   # Title Case
df['email'] = df['email'].str.lower()       # lowercase
df['code'] = df['code'].str.upper()         # UPPERCASE

# Replace text
df['phone'] = df['phone'].str.replace('-', '').str.replace(' ', '')

# Remove special characters
df['name'] = df['name'].str.replace(r'[^a-zA-Zs]', '', regex=True)

# Standardise values (map inconsistent values to canonical form)
status_mapping = {
    'complete': 'completed',
    'COMPLETED': 'completed',
    'done': 'completed',
    'cancelled': 'cancelled',
    'CANCELLED': 'cancelled',
    'cancel': 'cancelled'
}
df['status'] = df['status'].str.lower().map(status_mapping).fillna(df['status'])

# Check unique values after cleaning
df['status'].value_counts()

# Summary statistics to spot outliers
print(df['amount'].describe())

# Flag rows outside expected ranges
df['amount_flag'] = (df['amount'] < 0) | (df['amount'] > 100000)
print(f"Out-of-range amounts: {df['amount_flag'].sum()}")

# View flagged rows
df[df['amount_flag']]

# Remove obvious data errors
df = df[df['amount'] >= 0]  # remove negative amounts
df = df[df['age'].between(16, 100)]  # keep only valid ages

# Check future dates in historical data
df['date_flag'] = df['order_date'] > pd.Timestamp.today()
print(f"Future dates: {df['date_flag'].sum()}")

# Extract date parts
df['year'] = df['order_date'].dt.year
df['month'] = df['order_date'].dt.month
df['month_name'] = df['order_date'].dt.strftime('%B')
df['day_of_week'] = df['order_date'].dt.day_name()
df['quarter'] = df['order_date'].dt.quarter

# Extract from strings
df['domain'] = df['email'].str.extract(r'@(.+)')
df['first_name'] = df['full_name'].str.split().str[0]
df['last_name'] = df['full_name'].str.split().str[-1]

# Split a column into multiple columns
split_df = df['full_name'].str.split(' ', expand=True)
df['first_name'] = split_df[0]
df['last_name'] = split_df[1]

def clean_sales_data(filepath):
    """Load and clean sales data. Returns a clean DataFrame."""
    # Load
    df = pd.read_csv(filepath)

    # Remove duplicates
    df = df.drop_duplicates(subset=['order_id'])

    # Fix types
    df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')
    df['amount'] = pd.to_numeric(df['amount'], errors='coerce')

    # Fill missing values
    df['region'].fillna('Unknown', inplace=True)
    df['discount'].fillna(0, inplace=True)

    # Clean strings
    df['country'] = df['country'].str.strip().str.title()
    df['status'] = df['status'].str.lower().str.strip()

    # Remove invalid records
    df = df.dropna(subset=['order_id', 'order_date', 'amount'])
    df = df[df['amount'] >= 0]

    print(f"Cleaned dataset: {len(df)} rows, {df.isnull().sum().sum()} missing values")
    return df

# Use the function
df_clean = clean_sales_data('sales_data.csv')

import pandas as pd

# Create a messy dataset to practice cleaning
data = {
    'order_id': [1, 2, 2, 3, 4, 5],  # contains a duplicate
    'customer': ['Alice', '  bob  ', 'bob', 'CAROL', None, 'Dave'],
    'amount': ['120.50', 'abc', '340.00', '-50', '780.00', '90.00'],  # mixed types
    'country': ['nigeria', 'Ghana', 'NIGERIA', 'kenya', 'ghana', 'Nigeria'],
    'order_date': ['2024-01-15', '2024-02-20', '2024-02-20', 'invalid', '2024-03-10', '2026-01-01']
}
df = pd.DataFrame(data)

# Your tasks:
# 1. Remove duplicate order_ids
# 2. Clean 'customer' column: strip spaces, title case, fill missing
# 3. Convert 'amount' to numeric, flag and remove invalid values
# 4. Standardise 'country' to title case
# 5. Convert 'order_date' to datetime, flag future dates
# 6. Print a summary of how many rows remain and how many were removed