How I Standardized and Normalized 10 Million+ Rows of Dirty Data Using Python (Without Losing My Mind)

My practical, no-fluff approach to cleaning and transforming massive datasets with pandas, NumPy, and custom logic.

Data is never clean. If you’ve ever opened a 2GB CSV from a client that swears their data is “pretty clean,” you know exactly what I mean.

In this deep dive, I’ll walk you through how I handled a real-world dataset with over 10 million messy records. The goal? Standardize and normalize everything for a machine learning pipeline that wouldn’t choke on inconsistencies.

1. Understanding the Dataset (and the Mess)

Before touching any code, I spent time identifying:

• What formats were inconsistent?
• Were there nulls? Duplicates?
• Were there logical inconsistencies?

Here’s the initial inspection step:

import pandas as pd

df = pd.read_csv("raw_data.csv")

# Quick look at the shape and types
print(df.shape)
print(df.dtypes)

# Preview
print(df.head())

# Check for missing values
print(df.isnull().sum())

Key Insight: About 15% of the fields were missing, and the data types were all over the place.

2. Converting Everything to the Right Data Types

Data types were a nightmare: dates stored as strings, numeric fields stored as objects, and inconsistent encodings.

# Convert date column
df['created_at'] = pd.to_datetime(df['created_at'], errors='coerce')

# Convert price to float
df['price'] = pd.to_numeric(df['price'], errors='coerce')

# Convert ID to string
df['user_id'] = df['user_id'].astype(str)

3. Handling Nulls — Smartly

You can’t just .dropna() a million rows and call it a day.

# Fill missing prices with median
df['price'].fillna(df['price'].median(), inplace=True)

# Drop rows where critical fields are null
df.dropna(subset=['user_id', 'created_at'], inplace=True)

# Fill missing city with mode
df['city'].fillna(df['city'].mode()[0], inplace=True)

4. Standardizing Text Fields (Case, Spaces, Encoding)

This is where the invisible bugs hide.

def clean_text(x):
    if isinstance(x, str):
        return x.strip().lower().replace('\xa0', ' ')
    return x

df['city'] = df['city'].apply(clean_text)
df['country'] = df['country'].apply(clean_text)

Also, remove duplicates after standardization:

df.drop_duplicates(inplace=True)

5. Normalizing Categorical Data (Mapping Variants to One Form)

People write “USA”, “U.S.A.”, “United States”, “us”, and “US”. They’re all the same country.

country_map = {
    'usa': 'united states',
    'u.s.a.': 'united states',
    'us': 'united states',
    'united states': 'united states',
    'uk': 'united kingdom',
    'england': 'united kingdom',
}

df['country'] = df['country'].map(lambda x: country_map.get(x, x))

This step reduced the number of unique countries from 42 → 14. That’s the power of normalization.

6. Binning Numerical Data (For Downstream ML)

Some models don’t like raw numerical values — especially with skewed distributions.

import numpy as np

# Binning price into 5 quantile-based buckets
df['price_bin'] = pd.qcut(df['price'], q=5, labels=False)

# Binning user activity
df['activity_score'] = df['actions_last_30d'].fillna(0)
df['activity_level'] = pd.cut(df['activity_score'], bins=[-1, 5, 20, 100, np.inf], labels=['low', 'medium', 'high', 'superuser'])

7. Scaling Numerical Features

To normalize the data (important for algorithms like KNN, SVM, etc.)

from sklearn.preprocessing import MinMaxScaler, StandardScaler

scaler = MinMaxScaler()
df[['price_scaled', 'actions_scaled']] = scaler.fit_transform(df[['price', 'actions_last_30d']])

For a normal distribution:

standard_scaler = StandardScaler()
df[['price_standard']] = standard_scaler.fit_transform(df[['price']])

8. Final Sanity Checks and Export

I never trust data until I re-inspect it after processing.

# Check again
print(df.info())
print(df.describe(include='all'))

# Final null check
print(df.isnull().sum())

# Export cleaned data
df.to_csv("cleaned_data.csv", index=False)

Also, run basic profiling:

# Optional but useful
import pandas_profiling

profile = pandas_profiling.ProfileReport(df)
profile.to_file("data_profile.html")

9. Lessons Learned & What I’d Do Differently

• Always inspect data types and NULLs first.
• Don’t rush standardization. A single dirty entry can break your ML model.
• Automate repetitive tasks with functions.
• Profile early. Profile often.

This process took me a couple of days, and I reused most of this logic across multiple projects. Once you get this flow down, you’ll be able to clean millions of rows in minutes — confidently.