No history yet

Complex Data Wrangling

Beyond Tidy Data

Real-world data is messy. It rarely arrives in a perfectly structured format, ready for analysis. While you've likely handled basic issues like missing values with a simple mean or median fill, professional-grade analysis, especially in sectors like finance and e-commerce, demands more sophisticated techniques.

Business datasets are often riddled with structural inconsistencies, complex patterns of missing information, and skewed distributions. Simple cleaning methods can distort the underlying patterns in the data, leading to flawed models and incorrect conclusions. We need to move beyond basic cleaning and into the realm of data wrangling, where we strategically reshape and transform data to reveal its true story.

Taming Wild Datasets

Imagine an e-commerce sales dataset. One of the most common challenges is dealing with user-generated or inconsistent categorical data. A column like product_category could have thousands of unique entries for what are essentially a few dozen distinct categories. This is known as a high-cardinality categorical variable

For example, 'T-Shirt', 'tshirt', 'Tee', and 'T-Shirt-Men' might all refer to the same item. A simple value_counts() would treat them as separate, hiding their collective importance. We need to consolidate these.

import pandas as pd

# Sample DataFrame with inconsistent categories
data = {'category': ['T-Shirt', 'tshirt', 'Jeans', 'Tee', 'jeans-blue', 'Jacket']}
sales_df = pd.DataFrame(data)

# Clean and consolidate using string methods
sales_df['category_clean'] = sales_df['category'].str.lower().str.strip()

# Use a mapping for consolidation
category_map = {
    't-shirt': 'Apparel', 
    'tee': 'Apparel',
    'jeans': 'Apparel',
    'jeans-blue': 'Apparel',
    'jacket': 'Outerwear'
}

# Map all variations to a higher-level category
sales_df['category_grouped'] = sales_df['category_clean'].map(category_map).fillna('Other')

print(sales_df)

Another common issue in sales data is skewed distributions. A handful of products might sell millions of units, while most sell only a few. This heavy skew can violate assumptions of many statistical models. A common and effective way to handle this is a log transformation, which compresses the range of the data and makes the distribution more symmetrical.

import numpy as np

# Assume 'sales_units' is a skewed column in our DataFrame
sales_data = {'sales_units': [10, 15, 12, 5, 8000, 2, 9500, 25]}
sales_df = pd.DataFrame(sales_data)

# Apply a log transformation to handle skew
# We add 1 to handle cases where sales_units could be 0
sales_df['sales_log'] = np.log1p(sales_df['sales_units'])

print(sales_df[['sales_units', 'sales_log']])

Outliers are another feature of real datasets. While it's tempting to delete them, they can sometimes represent your most valuable customers or fraudulent transactions. Deleting them blindly means throwing away information. A better approach is to first identify them, and then decide on a strategy. The Interquartile Range (IQR) method is a standard technique for flagging potential outliers.

Once identified, you can cap outliers (a technique called winsorizing), transform the feature (like with the log transformation we just saw), or treat them as a separate analytical segment. The right choice depends on the business context.

Filling the Gaps Intelligently

Filling missing data with a column's mean or median is fast, but it ignores relationships between variables. If a customer's age is missing, but you know their purchase_total and last_login_date, you can probably make a much better guess than the overall average age. This is the idea behind model-based imputation.

One powerful technique is Multivariate Imputation by Chained Equations (MICE). Instead of a simple fill, MICE builds a model to predict missing values for each column based on all the other columns. It iterates through this process, refining its predictions until the imputed values stabilise. This approach preserves the data's natural variance and correlations far better than single imputation methods.

from sklearn.experimental import enable_iterative_imputer
from sklearn.impute import IterativeImputer
import numpy as np

# Sample DataFrame with missing values
df = pd.DataFrame({
    'age': [25, 34, np.nan, 45, 22, 54],
    'income': [50000, 75000, 60000, 120000, np.nan, 200000],
    'purchase_total': [500, 1200, 800, 2500, 300, 4000]
})

# Create the imputer object
imputer = IterativeImputer(max_iter=10, random_state=0)

# Fit and transform the data
df_imputed = pd.DataFrame(imputer.fit_transform(df), columns=df.columns)

print("Original Data:")
print(df)
print("\nImputed Data:")
print(df_imputed)

Using an IterativeImputer from scikit-learn implements the MICE algorithm. Notice how the imputed NaN values are plausible given the other data points in their respective rows. This creates a much more robust dataset for any subsequent modelling.

Data cleaning and transformation: Addressing data quality issues, such as handling missing values, outliers, and inconsistencies.

These advanced wrangling techniques—consolidating categories, transforming skewed features, strategically handling outliers, and intelligently imputing missing data—are fundamental for preparing real-world data for reliable analysis.

Quiz Questions 1/5

In an e-commerce dataset, the 'product_category' column contains entries like 'T-Shirt', 'tshirt', and 'Tee-Shirt'. This is an example of what common data cleaning challenge?

Quiz Questions 2/5

Why is simply filling missing values with the column's mean or median often insufficient for complex, real-world datasets?