Business Analytics 101: Data Management and Preparation Data Cleaning Missing Values Outliers Duplicates Inconsistent Formatting Standardization

What This Is

Data cleaning is the process of detecting, correcting, and preventing errors in data. It's crucial in business analytics as it ensures that data is accurate, complete, and consistent, which is essential for making informed business decisions. For instance, a retail company wants to forecast sales for the upcoming quarter. If the data is not cleaned, it may contain missing values, outliers, or inconsistent formatting, leading to inaccurate forecasts and poor business decisions.

Key Formulas & Metrics

• Mean Absolute Error (MAE) = (1/n) Σ|yᵢ − ŷᵢ|: average absolute forecast error, where yᵢ is the actual value and ŷᵢ is the predicted value.
• R² = 1 − (SS_res / SS_tot): proportion of variance explained by the regression model, where SS_res is the sum of squared residuals and SS_tot is the total sum of squares.
• Coefficient of Variation (CV) = σ / μ: measure of relative variability, where σ is the standard deviation and μ is the mean.
• Z-score = (X - μ) / σ: measure of how many standard deviations an observation is away from the mean.
• Correlation Coefficient (ρ) = Σ[(Xᵢ - μᵢ)(Yᵢ - μᵢ)] / √[Σ(Xᵢ - μᵢ)² * Σ(Yᵢ - μᵢ)²]: measure of linear relationship between two variables.
• Standard Deviation (σ) = √[Σ(Xᵢ - μ)² / (n - 1)]: measure of spread or dispersion of a dataset.
• Median Absolute Deviation (MAD) = Median|Xᵢ - Median(X)|: measure of spread or dispersion of a dataset, similar to standard deviation but more robust.
• Interquartile Range (IQR) = Q₃ - Q₁: measure of spread or dispersion of a dataset, where Q₃ is the third quartile and Q₁ is the first quartile.
• Data Quality Metrics (DQM): metrics such as accuracy, completeness, consistency, and timeliness to measure data quality.

Step-by-Step Procedure

1. Detect missing values: use techniques such as mean/median imputation, regression imputation, or multiple imputation to replace missing values.
2. Identify and handle outliers: use techniques such as Z-score, IQR, or density plots to detect outliers and decide whether to remove or transform them.
3. Remove duplicates: use techniques such as data profiling or data visualization to identify and remove duplicate records.
4. Standardize formatting: use techniques such as data normalization or data transformation to ensure consistent formatting across the dataset.
5. Verify data quality: use data quality metrics (DQM) to measure and verify the quality of the cleaned data.

Common Mistakes

• Mistake: Confusing correlation with causation.
• Correction: Correlation does not imply causation. Use techniques such as regression analysis or experimentation to establish causality.
• Mistake: Misinterpreting p-values.
• Correction: p-values represent the probability of observing the data (or more extreme) if the null hypothesis is true. Use techniques such as confidence intervals or effect sizes to interpret p-values correctly.
• Mistake: Using the wrong error metric for a business problem.
• Correction: Use metrics such as MAE or R² that are relevant to the business problem. For example, use MAE for forecasting problems and R² for regression problems.

Software / Tool Tips

• Python with pandas/scikit-learn: use functions such as pandas.DataFrame.dropna() to detect and remove missing values, scikit-learn.preprocessing.StandardScaler to standardize formatting, and pandas.DataFrame.duplicated() to remove duplicates.
• R: use functions such as dplyr::drop_na() to detect and remove missing values, stats::scale() to standardize formatting, and dplyr::distinct() to remove duplicates.
• Excel: use functions such as IFERROR() to detect and remove missing values, AVERAGE() to calculate mean, and FILTER() to remove duplicates.

Quick Practice Problem

• Problem: Compute the moving average forecast for period 4 given data: 100, 110, 105, 120.
• Answer: (100 + 110 + 105 + 120) / 4 = 110.
• Explanation: The moving average forecast is the average of the last four periods.

Last-Minute Cram Sheet

• Data cleaning is the process of detecting, correcting, and preventing errors in data.
• Mean Absolute Error (MAE) = (1/n) Σ|yᵢ − ŷᵢ|: average absolute forecast error.
• R² = 1 − (SS_res / SS_tot): proportion of variance explained by the regression model.
• Coefficient of Variation (CV) = σ / μ: measure of relative variability.
• Z-score = (X - μ) / σ: measure of how many standard deviations an observation is away from the mean.
• Correlation Coefficient (ρ) = Σ[(Xᵢ - μᵢ)(Yᵢ - μᵢ)] / √[Σ(Xᵢ - μᵢ)² * Σ(Yᵢ - μᵢ)²]: measure of linear relationship between two variables.
• Standard Deviation (σ) = √[Σ(Xᵢ - μ)² / (n - 1)]: measure of spread or dispersion of a dataset.
• Median Absolute Deviation (MAD) = Median|Xᵢ - Median(X)|: measure of spread or dispersion of a dataset.
• Interquartile Range (IQR) = Q₃ - Q₁: measure of spread or dispersion of a dataset.
• Data Quality Metrics (DQM): metrics such as accuracy, completeness, consistency, and timeliness to measure data quality.
• ⚠️ p-value is NOT the probability that H₀ is true – it’s the probability of observing the data (or more extreme) if H₀ is true.