Loading…
Loading…
Data cleaning is a critical step in the data analysis process. This lesson covers techniques for identifying and handling data quality issues.
Clean data is the foundation of reliable data analysis and decision-making. Poor data quality can lead to incorrect conclusions, flawed business strategies, and significant financial losses.
Business Consequences:
Technical Consequences:
Improved Analytics:
Business Advantages:
Outliers are data points that differ significantly from other observations. They can be legitimate extreme values or errors that need to be addressed.
Statistical Outliers:
Source-Based Outliers:
Statistical Methods:
Z-Score Method:
Z = (X - μ) / σ
Where:
- X = data point
- μ = mean
- σ = standard deviation
- Outliers typically have |Z| > 3
Interquartile Range (IQR) Method:
IQR = Q3 - Q1
Lower Bound = Q1 - 1.5 × IQR
Upper Bound = Q3 + 1.5 × IQR
Values outside bounds are outliers
Modified Z-Score:
Modified Z = 0.6745 × (X - median) / MAD
Where MAD = median absolute deviation
More robust to extreme values than standard Z-score
Visualization Methods:
Machine Learning Methods:
Removal Strategies:
Transformation Strategies:
Imputation Strategies:
Best Practices:
Missing data is a common problem in real-world datasets. Proper handling is crucial for maintaining data integrity and analysis validity.
Missing Completely at Random (MCAR):
Missing at Random (MAR):
Missing Not at Random (MNAR):
Visual Detection:
Statistical Detection:
Simple Imputation Methods:
Mean/Median/Mode Imputation:
Mean: For continuous, normally distributed data
Median: For continuous, skewed data
Mode: For categorical data
Advantages: Simple, fast, easy to implement Disadvantages: Reduces variance, ignores relationships
Forward/Backward Fill:
Constant Value Imputation:
Advanced Imputation Methods:
Regression Imputation:
Predict missing values using other variables
Steps:
1. Build regression model using complete cases
2. Predict missing values
3. Replace missing values with predictions
K-Nearest Neighbors (KNN) Imputation:
Find k most similar complete cases
Use weighted average of their values
Distance metrics: Euclidean, Manhattan, etc.
Multiple Imputation:
Create multiple complete datasets
Analyze each dataset separately
Combine results using Rubin's rules
Accounts for uncertainty in imputation
Expectation-Maximization (EM) Algorithm:
E-step: Estimate missing values given current parameters
M-step: Update parameters given estimated values
Iterate until convergence
Maximum likelihood approach
Considerations:
Decision Framework:
Data validation ensures that data meets quality standards and business rules before analysis.
Schema Validation:
Business Rule Validation:
Statistical Validation:
Automated Validation Rules:
Range and Boundary Checks:
# Example validation rules
age >= 0 and age <= 120
salary >= 0
rating between 1 and 5
Pattern Matching:
# Email validation
email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
# Phone number validation
phone_pattern = r'^\+?1?-?\.?\s?\(?(\d{3})\)?[\s.-]?(\d{3})[\s.-]?(\d{4})$'
Cross-Field Validation:
# Start date before end date
start_date < end_date
# Delivery date after order date
delivery_date > order_date
# Age consistent with birth date
age == current_year - birth_year
Statistical Validation:
# Check for normal distribution
shapiro_test(data) > 0.05
# Identify outliers using IQR
is_outlier = (data < Q1 - 1.5*IQR) | (data > Q3 + 1.5*IQR)
Completeness Metrics:
Accuracy Metrics:
Consistency Metrics:
Timeliness Metrics:
Validation Framework:
Tools and Technologies:
Data deduplication identifies and removes duplicate records to ensure data uniqueness and consistency.
Exact Duplicates:
Near Duplicates:
Partial Duplicates:
Exact Matching:
-- SQL example for exact duplicate removal
DELETE FROM table_name
WHERE id NOT IN (
SELECT MIN(id)
FROM table_name
GROUP BY all_columns
);
Fuzzy Matching:
Probabilistic Matching:
1. Data Preparation:
2. Blocking/Indexing:
3. Comparison:
4. Classification:
5. Resolution:
Prevention Strategies:
Quality Assurance:
1. Assessment Phase:
- Profile data to understand quality issues
- Identify missing values, outliers, duplicates
- Document data quality problems
- Prioritize issues based on impact
2. Planning Phase:
- Define cleaning objectives
- Select appropriate techniques
- Plan resource requirements
- Establish success criteria
3. Execution Phase:
- Implement cleaning procedures
- Apply validation rules
- Handle exceptions and edge cases
- Document all transformations
4. Validation Phase:
- Verify cleaning results
- Compare before/after quality metrics
- Test impact on downstream analyses
- Get stakeholder approval
Programming Languages:
Specialized Tools:
Challenge:
Solution:
Results:
In the next lesson, we'll explore data normalization and integration techniques to prepare cleaned data for advanced analysis.