Data Cleaning Error Scenarios

The Context

I'm working on a machine learning project for GlobalBev, a beverage company launching a new fruit juice flavor in India. The goal is to build a predictive model that recommends the optimal distribution channel (General Trade, Modern Trade, HoReCa, E-Commerce, or Q-Commerce) based on various factors including city characteristics, consumer behavior, and market conditions.

My first task was to explore the city dataset, which contains demographic and economic information about 20 Indian cities where GlobalBev operates.

Challenge #1: The Encoding Error

I started by loading the retail sales dataset, which worked fine:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

# This worked perfectly
df_sales = pd.read_csv('retailsalesdistribution.csv')
df_sales.head()

When I saw city codes like "CT001" in the sales data, I naturally wanted to understand which actual cities these represented. So I tried loading city.csv:

# This threw an error!
df_city = pd.read_csv('city.csv')

And hit my first roadblock: UnicodeDecodeError: 'utf-8' codec can't decode byte 0xb2 in position 57.

I opened the CSV in Excel and spotted a superscript ² symbol in the "Population_Density(persons/km²)" column header. That was the culprit.

The Learning: After discussing with my mentor, I learned that CSV files can have different character encodings. The default UTF-8 couldn't interpret the single-byte 0xB2 that represents ² in Windows-1252 (cp1252) encoding. The file metadata showed it was encoded as cp1252.

The Solution:

# Specify the correct encoding
df_city = pd.read_csv('city.csv', encoding='cp1252')

This worked! The file loaded successfully.

Challenge #2: The Phantom Columns

After successfully loading the file, I checked the structure:

# Check dataset shape and columns
print("Dataset shape:", df_city.shape)
print("\nAll columns:")
print(df_city.columns.tolist())

I was surprised to see the output showed 13 columns when Excel only displayed 5 columns (A through E):

Dataset shape: (20, 13)

All columns:
['City_ID', 'City_Name', 'City_tier', 'Population_Density(persons/km²)', 
 'Per_Capita_Income (INR)', 'Unnamed: 5', 'Unnamed: 6', 'Unnamed: 7', 
 'Unnamed: 8', 'Unnamed: 9', 'Unnamed: 10', 'Unnamed: 11', 'Unnamed: 12']

The Root Cause: I learned that the CSV file had trailing commas at the end of each row. Excel ignores these, but pandas interprets each comma as a column separator, creating empty "Unnamed" columns.

The Solution:

# Remove all columns starting with "Unnamed"
df_city = df_city.loc[:, ~df_city.columns.str.contains('^Unnamed')]

# Verify cleaning
print("Cleaned dataset shape:", df_city.shape)
print("Columns after cleaning:")
print(df_city.columns.tolist())

Output:

Cleaned dataset shape: (20, 5)
Columns after cleaning:
['City_ID', 'City_Name', 'City_tier', 'Population_Density(persons/km²)', 
 'Per_Capita_Income (INR)']

The Lesson: This dynamic approach is better than hardcoding usecols=range(5) because it adapts if the file structure changes - a more robust, reusable solution.

Challenge #3: The Object Type Problem

Now I could explore the actual city data. I wanted to see what CT001 represents:

# Find what CT001 represents
print(df_city[df_city['City_ID'] == 'CT001'])

# Get all city mappings
print("\nAll Cities:")
print(df_city[['City_ID', 'City_Name', 'City_tier']])

Perfect! CT001 is Delhi, a Tier 1 city. The dataset has 20 cities total - a mix of Tier 1 and Tier 2 cities.

Now I wanted to compare Tier 1 vs Tier 2 cities statistically. First, I checked the data types:

# Check data types
print("Current data types:")
print(df_city.dtypes)

Output showed that Per_Capita_Income (INR) was stored as object type. I learned that object dtype in pandas means the column is stored as string/text data, not as numbers.

I tried to run the analysis anyway:

# Compare Tier 1 vs Tier 2 characteristics
tier_comparison = df_city.groupby('City_tier').agg({
    'Population_Density(persons/km²)': ['mean', 'median', 'std', 'min', 'max'],
    'Per_Capita_Income (INR)': ['mean', 'median', 'std', 'min', 'max'],
    'City_ID': 'count'
})

Error: TypeError: Could not convert string to numeric

The Problem: Values like "4,61,910" with commas are treated as text, not numbers. You can't calculate statistics on text. This is why the column was stored as object type - pandas saw the commas and treated the entire column as strings.

The Solution:

# Remove commas and convert to float
df_city['Per_Capita_Income (INR)'] = (df_city['Per_Capita_Income (INR)']
                                      .astype(str)
                                      .str.replace(',', '')
                                      .astype(float))

# Verify the conversion
print("\nAfter conversion:")
print(df_city.dtypes)

Now it was converted to float64 and the analysis worked!

The Analysis: Tier 1 vs Tier 2 Cities

# Now this works!
tier_comparison = df_city.groupby('City_tier').agg({
    'Population_Density(persons/km²)': ['mean', 'median', 'std', 'min', 'max'],
    'Per_Capita_Income (INR)': ['mean', 'median', 'std', 'min', 'max'],
    'City_ID': 'count'
})

tier_comparison.columns = ['_'.join(col) for col in tier_comparison.columns]
print("\nTier-wise Statistics:")
print(tier_comparison)

The results showed:

Tier 1 cities are performing better across all metrics compared to Tier 2.

Visualization Strategy: Choosing the Right Chart

I wondered if I could visualize this better and asked if a box and whiskers plot would be a good choice. I was advised that box plots would work reasonably well, but with only 8 Tier 1 and 12 Tier 2 cities, I have limited data points.

I tried three different visualization approaches:

Visualization 1: Box Plot with Individual Points

fig, axes = plt.subplots(1, 2, figsize=(14, 6))

# Population Density comparison
sns.boxplot(data=df_city, x='City_tier', y='Population_Density(persons/km²)', ax=axes[0])
sns.swarmplot(data=df_city, x='City_tier', y='Population_Density(persons/km²)', 
              color='black', alpha=0.5, ax=axes[0])
axes[0].set_title('Population Density by City Tier')
axes[0].set_ylabel('Population Density (persons/km²)')

# Per Capita Income comparison
sns.boxplot(data=df_city, x='City_tier', y='Per_Capita_Income (INR)', ax=axes[1])
sns.swarmplot(data=df_city, x='City_tier', y='Per_Capita_Income (INR)', 
              color='black', alpha=0.5, ax=axes[1])
axes[1].set_title('Per Capita Income by City Tier')
axes[1].set_ylabel('Per Capita Income (INR)')

plt.tight_layout()
plt.show()

Visualization 2: Violin Plot

fig, axes = plt.subplots(1, 2, figsize=(14, 6))

sns.violinplot(data=df_city, x='City_tier', y='Population_Density(persons/km²)', ax=axes[0])
axes[0].set_title('Population Density Distribution by City Tier')

sns.violinplot(data=df_city, x='City_tier', y='Per_Capita_Income (INR)', ax=axes[1])
axes[1].set_title('Per Capita Income Distribution by City Tier')

plt.tight_layout()
plt.show()

Visualization 3: Bar Chart with Error Bars

fig, axes = plt.subplots(1, 2, figsize=(14, 6))

tier_stats = df_city.groupby('City_tier').agg({
    'Population_Density(persons/km²)': ['mean', 'std'],
    'Per_Capita_Income (INR)': ['mean', 'std']
})

# Population Density
axes[0].bar(tier_stats.index, 
           tier_stats[('Population_Density(persons/km²)', 'mean')],
           yerr=tier_stats[('Population_Density(persons/km²)', 'std')],
           capsize=5, alpha=0.7)
axes[0].set_title('Average Population Density by City Tier')
axes[0].set_ylabel('Population Density (persons/km²)')
axes[0].set_xlabel('City Tier')

# Per Capita Income
axes[1].bar(tier_stats.index,
           tier_stats[('Per_Capita_Income (INR)', 'mean')],
           yerr=tier_stats[('Per_Capita_Income (INR)', 'std')],
           capsize=5, alpha=0.7, color='green')
axes[1].set_title('Average Per Capita Income by City Tier')
axes[1].set_ylabel('Per Capita Income (INR)')
axes[1].set_xlabel('City Tier')

plt.tight_layout()
plt.show()

Key Business Insights from Visualization 1

Looking at the box plot with scatter points (Visual 1), I derived several business insights:

Population Density Analysis:

• Tier 1 cities are ~2.4x denser than Tier 2 (median: 13,720 vs 5,551 persons/km²)

• High variability in Tier 1 with one extreme outlier at ~24,252 persons/km² (likely Mumbai or Kolkata)

• Tier 2 is more homogeneous with tighter clustering

• Business Implication: Q-Commerce has better unit economics in dense Tier 1 cities due to more customers per delivery zone

Per Capita Income Analysis:

• Tier 1 has higher income but with extreme outliers - one very high at ~₹585,000 (likely Chennai) and one very low at ~₹171,000 (likely Kolkata)

• Tier 2 is more consistent with median ~₹241,000

• Income gap: Tier 1 earns ~38% more on average but has much more variation

• Business Implication: Can't treat all Tier 1 cities the same - high-income cities favor Modern Trade and E-Commerce while lower-income cities need General Trade strategies

Critical Insight - The Kolkata Paradox: A Tier 1 city with low income needs a Tier 2 strategy despite its tier classification.

Why Visualizations 2 and 3 Were Less Effective

Violin Plot Issues:

• Misleading with small sample sizes - the smooth curves imply more data than actually exists

• False precision from kernel density smoothing

• The "tails" extending below zero in per capita income are mathematically impossible but appear due to smoothing

• Harder for business stakeholders to interpret

Bar Chart Issues:

• Hides distribution shape and outliers

• Mean can be misleading with extreme values - it doesn't show the Chennai and Kolkata outliers

• Standard deviation assumes normal distribution, which our data isn't

• Too aggregated - can't identify which specific cities need different strategies

• Loses business nuance

Why Box Plot + Swarm Wins:

• Shows distribution through quartiles

• Displays every individual city as a point

• Immediately identifies outliers

• Intuitive for business audiences

• Enables city-specific insights, not just tier-level generalizations

What This Means for My ML Model

These insights suggest that my model should NOT just use "Tier" as a feature but should include:

• Population density as a continuous variable

• Per capita income as a continuous variable

• Possibly city-specific dummy variables for outliers

The heterogeneity within Tier 1 cities means tier classification alone will oversimplify the problem.

Key Takeaway: Data cleaning isn't just a technical step - it's where you start understanding your data's quirks, limitations, and hidden patterns. Every error message taught me something important about my dataset that will matter later in the analysis.