EDA Process & Data Profiling

Issues Identified:

• Extreme outlier: Max ($500K) vs 75th percentile ($80K) - 6x gap
• Right skew: Mean ($65K) > Median ($60K)
• Suspicious minimum: $5K seems too low for full-time

# Investigate outliers using IQR
Q1, Q3 = df['salary'].quantile([0.25, 0.75])
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

print(f"IQR: ${IQR:,.0f}")
print(f"Lower bound: ${lower_bound:,.0f}")
print(f"Upper bound: ${upper_bound:,.0f}")

# Find outliers
outliers = df[(df['salary'] < lower_bound) | (df['salary'] > upper_bound)]
print(f"\nOutliers: {len(outliers)} ({len(outliers)/len(df)*100:.1f}%)")

# Visualize
import matplotlib.pyplot as plt
fig, axes = plt.subplots(1, 2, figsize=(12, 4))
df['salary'].hist(bins=50, ax=axes[0])
axes[0].set_title('Salary Distribution')
df.boxplot(column='salary', ax=axes[1])
axes[1].set_title('Salary Boxplot')
plt.show()

Issues Identified:

• Potential outliers: Max (500,000) is far from 75th percentile (80,000) - a gap of 420,000
• Right skew: Mean (65,000) > Median/50% (60,000) indicates positive skew
• Suspicious minimum: $5,000 salary seems very low - could be part-time, error, or different unit

# Investigate outliers
print(df[df['salary'] > 200000])  # High earners
print(df[df['salary'] < 20000])   # Unusually low

# Check for IQR-based outliers
Q1 = df['salary'].quantile(0.25)
Q3 = df['salary'].quantile(0.75)
IQR = Q3 - Q1
outliers = df[(df['salary'] < Q1 - 1.5*IQR) | (df['salary'] > Q3 + 1.5*IQR)]
print(f"Potential outliers: {len(outliers)}")

# Visualize distribution
import matplotlib.pyplot as plt
fig, axes = plt.subplots(1, 2, figsize=(12, 4))
df['salary'].hist(bins=50, ax=axes[0])
axes[0].set_title('Salary Distribution')
df.boxplot(column='salary', ax=axes[1])
axes[1].set_title('Salary Boxplot')
plt.show()

# Convert date string to datetime
df['order_date'] = pd.to_datetime(df['order_date'])

# Convert zip_code to string, preserving leading zeros
df['zip_code'] = df['zip_code'].astype(str).str.zfill(5)

# Convert to boolean
df['is_active'] = df['is_active'].astype(bool)

# Convert to category for memory efficiency
df['product_category'] = df['product_category'].astype('category')

# Verify changes
print(df.dtypes)
print(f"\nMemory usage: {df.memory_usage(deep=True).sum() / 1024**2:.2f} MB")

# For multiple conversions at once
df = df.astype({
    'order_date': 'datetime64[ns]',
    'is_active': 'bool',
    'product_category': 'category'
})

def first_look(df):
    """
    Perform comprehensive first look analysis on a DataFrame.
    """
    print("=" * 60)
    print("DATASET OVERVIEW")
    print("=" * 60)
    
    # Basic shape and memory
    print(f"\n📊 Shape: {df.shape[0]:,} rows × {df.shape[1]} columns")
    memory_mb = df.memory_usage(deep=True).sum() / 1024**2
    print(f"💾 Memory Usage: {memory_mb:.2f} MB")
    
    # Column types summary
    print(f"\n📋 Column Types:")
    for dtype, count in df.dtypes.value_counts().items():
        print(f"   {dtype}: {count} columns")
    
    # Missing values
    print("\n" + "=" * 60)
    print("MISSING VALUES")
    print("=" * 60)
    missing = df.isna().sum()
    missing_pct = (missing / len(df) * 100).round(2)
    missing_df = pd.DataFrame({
        'Missing': missing,
        'Percent': missing_pct
    }).query('Missing > 0').sort_values('Missing', ascending=False)
    
    if len(missing_df) > 0:
        print(missing_df)
    else:
        print("✅ No missing values found!")
    
    # Numeric columns
    numeric_cols = df.select_dtypes(include=['number']).columns
    if len(numeric_cols) > 0:
        print("\n" + "=" * 60)
        print("NUMERIC COLUMN SUMMARY")
        print("=" * 60)
        print(df[numeric_cols].describe().T)
    
    # Categorical columns (top values)
    cat_cols = df.select_dtypes(include=['object', 'category']).columns
    if len(cat_cols) > 0:
        print("\n" + "=" * 60)
        print("CATEGORICAL COLUMNS (Top 5 values)")
        print("=" * 60)
        for col in cat_cols:
            print(f"\n{col} ({df[col].nunique()} unique):")
            print(df[col].value_counts().head())
    
    print("\n" + "=" * 60)

# Usage
first_look(df)

from ydata_profiling import ProfileReport

# For large datasets, use minimal mode
profile = ProfileReport(
    sales_df,
    title="Sales Data Profile Report",
    minimal=True,  # Faster for large datasets
    explorative=True
)

# Save to HTML file
profile.to_file("sales_profile_report.html")

# Or display in Jupyter Notebook
profile.to_notebook_iframe()

# For very large datasets (>100K rows), sample first
if len(sales_df) > 100000:
    sample_df = sales_df.sample(n=10000, random_state=42)
    profile = ProfileReport(sample_df, title="Sales Sample Profile")
    profile.to_file("sales_sample_report.html")

from ydata_profiling import ProfileReport

# Generate profiles for both datasets
train_profile = ProfileReport(train_df, title="Training Data")
test_profile = ProfileReport(test_df, title="Test Data")

# Generate comparison report
comparison = train_profile.compare(test_profile)
comparison.to_file("train_test_comparison.html")

# Alternative: Custom comparison function
def compare_datasets(df1, df2, name1="Dataset 1", name2="Dataset 2"):
    """Compare two DataFrames for consistency."""
    print(f"Shape Comparison:")
    print(f"  {name1}: {df1.shape}")
    print(f"  {name2}: {df2.shape}")
    
    print(f"\nColumn Comparison:")
    cols1 = set(df1.columns)
    cols2 = set(df2.columns)
    print(f"  Only in {name1}: {cols1 - cols2}")
    print(f"  Only in {name2}: {cols2 - cols1}")
    
    print(f"\nNumeric Column Statistics:")
    common_numeric = df1.select_dtypes(include=['number']).columns.intersection(
        df2.select_dtypes(include=['number']).columns
    )
    for col in common_numeric:
        print(f"\n  {col}:")
        print(f"    {name1} - Mean: {df1[col].mean():.2f}, Std: {df1[col].std():.2f}")
        print(f"    {name2} - Mean: {df2[col].mean():.2f}, Std: {df2[col].std():.2f}")

compare_datasets(train_df, test_df, "Train", "Test")

def enhanced_profile(df, target_col=None):
    """
    Generate an enhanced data profile with quality checks.
    
    Parameters:
    -----------
    df : pd.DataFrame
        The DataFrame to profile
    target_col : str, optional
        Target column for correlation analysis
    """
    print("=" * 70)
    print("ENHANCED DATA PROFILE")
    print("=" * 70)
    
    # Basic info
    print(f"\n📊 Shape: {df.shape[0]:,} rows × {df.shape[1]} columns")
    
    issues = []
    
    # Analyze each column
    for col in df.columns:
        col_issues = []
        
        # Missing values check
        missing_pct = df[col].isna().mean() * 100
        if missing_pct > 10:
            col_issues.append(f"⚠️ High missing: {missing_pct:.1f}%")
        
        # Numeric column analysis
        if df[col].dtype in ['int64', 'float64']:
            Q1 = df[col].quantile(0.25)
            Q3 = df[col].quantile(0.75)
            IQR = Q3 - Q1
            lower = Q1 - 1.5 * IQR
            upper = Q3 + 1.5 * IQR
            outlier_count = ((df[col] < lower) | (df[col] > upper)).sum()
            
            if outlier_count > 0:
                outlier_pct = outlier_count / len(df) * 100
                col_issues.append(f"⚠️ Outliers: {outlier_count} ({outlier_pct:.1f}%)")
        
        # Categorical column analysis
        if df[col].dtype == 'object':
            cardinality = df[col].nunique()
            if cardinality > 50:
                col_issues.append(f"⚠️ High cardinality: {cardinality} unique")
            if cardinality == len(df):
                col_issues.append("⚠️ Possible ID column (all unique)")
        
        # Store issues
        if col_issues:
            issues.append((col, col_issues))
    
    # Print issues summary
    if issues:
        print("\n" + "=" * 70)
        print("🚨 DATA QUALITY ISSUES")
        print("=" * 70)
        for col, col_issues in issues:
            print(f"\n{col}:")
            for issue in col_issues:
                print(f"  {issue}")
    else:
        print("\n✅ No major data quality issues detected!")
    
    # Correlation with target
    if target_col and target_col in df.columns:
        print("\n" + "=" * 70)
        print(f"📈 CORRELATION WITH TARGET: {target_col}")
        print("=" * 70)
        numeric_cols = df.select_dtypes(include=['number']).columns
        correlations = df[numeric_cols].corrwith(df[target_col]).sort_values(ascending=False)
        print(correlations)
    
    print("\n" + "=" * 70)

# Usage
enhanced_profile(df, target_col='sales')

import pandas as pd
import matplotlib.pyplot as plt

def analyze_missing_values(df, threshold=10):
    """Comprehensive missing value analysis."""
    # Calculate statistics
    missing_count = df.isna().sum()
    missing_pct = (missing_count / len(df) * 100).round(2)
    
    # Create summary DataFrame
    summary = pd.DataFrame({
        'Missing Count': missing_count,
        'Missing %': missing_pct,
        'Data Type': df.dtypes
    })
    
    # Filter and sort
    summary = summary[summary['Missing Count'] > 0]
    summary = summary.sort_values('Missing Count', ascending=False)
    
    print("MISSING VALUE REPORT")
    print("="*50)
    print(summary)
    
    # Flag high-missing columns
    high_missing = summary[summary['Missing %'] > threshold]
    if len(high_missing) > 0:
        print(f"\n⚠️ ATTENTION NEEDED (>{threshold}% missing):")
        print(list(high_missing.index))
    
    # Visualization
    if len(summary) > 0:
        plt.figure(figsize=(10, max(4, len(summary)*0.4)))
        colors = ['red' if x > threshold else 'steelblue' 
                  for x in summary['Missing %']]
        plt.barh(summary.index, summary['Missing %'], color=colors)
        plt.xlabel('Missing Percentage')
        plt.title('Missing Values by Column')
        plt.axvline(x=threshold, color='red', linestyle='--', 
                    label=f'{threshold}% threshold')
        plt.legend()
        plt.gca().invert_yaxis()
        plt.tight_layout()
        plt.show()
    
    return summary

# Usage
analyze_missing_values(df, threshold=10)

# 1. Exact duplicate rows
exact_dupes = df[df.duplicated(keep=False)]
print(f"Exact duplicates: {len(exact_dupes)}")

# 2. Duplicate order_ids
dupe_orders = df[df.duplicated(subset=['order_id'], keep=False)]
print(f"Duplicate order_ids: {len(dupe_orders)}")
print(dupe_orders.sort_values('order_id'))

# 3. Same customer and date (potential duplicates)
potential_dupes = df[df.duplicated(subset=['customer_id', 'order_date'], keep=False)]
print(f"Same customer/date: {len(potential_dupes)}")
print(potential_dupes.sort_values(['customer_id', 'order_date']))

# 4. Remove duplicates, keeping most recent
# First, ensure order_date is datetime
df['order_date'] = pd.to_datetime(df['order_date'])

# Sort by order_id and date (newest first), then keep first
df_deduped = df.sort_values(['order_id', 'order_date'], ascending=[True, False])
df_deduped = df_deduped.drop_duplicates(subset=['order_id'], keep='first')

print(f"Original rows: {len(df)}")
print(f"After deduplication: {len(df_deduped)}")
print(f"Removed: {len(df) - len(df_deduped)}")

import pandas as pd
from datetime import datetime

# Define valid departments
valid_departments = ['Sales', 'Engineering', 'HR', 'Marketing', 'Finance']

# Convert date column
df['start_date'] = pd.to_datetime(df['start_date'])
today = datetime.now()

# Create validation masks
invalid_age = (df['age'] < 18) | (df['age'] > 100)
invalid_salary = df['salary'] <= 0
invalid_date = df['start_date'] > today
invalid_dept = ~df['department'].isin(valid_departments)

# Find all violations
violations = pd.DataFrame({
    'Invalid Age': invalid_age,
    'Invalid Salary': invalid_salary,
    'Future Start Date': invalid_date,
    'Invalid Department': invalid_dept
})

# Any violation
any_violation = violations.any(axis=1)
print(f"Rows with violations: {any_violation.sum()}")

# Show detailed violations
violations_df = df[any_violation].copy()
violations_df['Violations'] = violations.apply(
    lambda row: ', '.join(violations.columns[row]), axis=1
)
print(violations_df[['employee_id', 'age', 'salary', 'start_date', 'department', 'Violations']])

# Summary by violation type
print("\nViolation Summary:")
for col in violations.columns:
    count = violations[col].sum()
    if count > 0:
        print(f"  {col}: {count} rows")

def data_quality_report(df, id_columns=None, rules=None):
    """
    Generate a comprehensive data quality report.
    
    Parameters:
    -----------
    df : pd.DataFrame
    id_columns : list, optional - Columns that should be unique
    rules : dict, optional - Validation rules {column: function}
    
    Returns:
    --------
    dict : Quality scores and issues
    """
    report = {
        'scores': {},
        'issues': [],
        'summary': {}
    }
    
    # 1. COMPLETENESS
    completeness_scores = (1 - df.isna().mean()) * 100
    overall_completeness = completeness_scores.mean()
    report['scores']['completeness'] = overall_completeness
    
    for col in df.columns:
        if completeness_scores[col] < 95:
            report['issues'].append({
                'type': 'completeness',
                'column': col,
                'score': completeness_scores[col],
                'message': f'{col}: {100-completeness_scores[col]:.1f}% missing'
            })
    
    # 2. UNIQUENESS (for ID columns)
    if id_columns:
        uniqueness_scores = []
        for col in id_columns:
            unique_ratio = df[col].nunique() / len(df) * 100
            uniqueness_scores.append(unique_ratio)
            if unique_ratio < 100:
                dupes = len(df) - df[col].nunique()
                report['issues'].append({
                    'type': 'uniqueness',
                    'column': col,
                    'score': unique_ratio,
                    'message': f'{col}: {dupes} duplicate values'
                })
        report['scores']['uniqueness'] = sum(uniqueness_scores) / len(uniqueness_scores) if uniqueness_scores else 100
    else:
        report['scores']['uniqueness'] = 100
    
    # 3. VALIDITY (custom rules)
    if rules:
        validity_scores = []
        for col, rule_func in rules.items():
            if col in df.columns:
                valid = df[col].apply(rule_func)
                validity_score = valid.mean() * 100
                validity_scores.append(validity_score)
                if validity_score < 100:
                    invalid_count = (~valid).sum()
                    report['issues'].append({
                        'type': 'validity',
                        'column': col,
                        'score': validity_score,
                        'message': f'{col}: {invalid_count} invalid values'
                    })
        report['scores']['validity'] = sum(validity_scores) / len(validity_scores) if validity_scores else 100
    else:
        report['scores']['validity'] = 100
    
    # 4. CONSISTENCY (format checks for string columns)
    consistency_scores = []
    for col in df.select_dtypes(include=['object']).columns:
        # Check for mixed case
        if df[col].dropna().str.lower().nunique() < df[col].nunique():
            report['issues'].append({
                'type': 'consistency',
                'column': col,
                'message': f'{col}: Mixed case values detected'
            })
        # Check for leading/trailing spaces
        has_spaces = df[col].dropna().str.strip() != df[col].dropna()
        if has_spaces.any():
            report['issues'].append({
                'type': 'consistency',
                'column': col,
                'message': f'{col}: {has_spaces.sum()} values with extra spaces'
            })
    
    report['scores']['consistency'] = 100 - min(len([i for i in report['issues'] if i['type'] == 'consistency']) * 5, 50)
    
    # 5. OVERALL SCORE
    weights = {'completeness': 0.3, 'uniqueness': 0.2, 'validity': 0.3, 'consistency': 0.2}
    overall = sum(report['scores'][k] * weights[k] for k in weights)
    report['scores']['overall'] = overall
    
    # Print report
    print("=" * 60)
    print("DATA QUALITY REPORT")
    print("=" * 60)
    print(f"\n📊 Overall Quality Score: {overall:.1f}%")
    print(f"\n   Completeness: {report['scores']['completeness']:.1f}%")
    print(f"   Uniqueness:   {report['scores']['uniqueness']:.1f}%")
    print(f"   Validity:     {report['scores']['validity']:.1f}%")
    print(f"   Consistency:  {report['scores']['consistency']:.1f}%")
    
    if report['issues']:
        print(f"\n⚠️ Issues Found: {len(report['issues'])}")
        for issue in report['issues']:
            print(f"   - {issue['message']}")
    
    return report

# Usage example
rules = {
    'age': lambda x: pd.isna(x) or (18 <= x <= 100),
    'email': lambda x: pd.isna(x) or '@' in str(x),
    'salary': lambda x: pd.isna(x) or x > 0
}

report = data_quality_report(
    df,
    id_columns=['employee_id'],
    rules=rules
)