python 47 lines · 9 steps

Cleaning a customer DataFrame with pandas

A single function normalizes messy customer records column by column into a consistent, deduplicated table.

Explained by highlit
pandas Intermediate data-cleaning regex normalization vectorized-strings deduplication
1import pandas as pd
2import numpy as np
3 
4 
5def clean_customers(df: pd.DataFrame) -> pd.DataFrame:
6 df = df.copy()
7 
8 df.columns = (
9 df.columns.str.strip()
10 .str.lower()
11 .str.replace(r"[^\w]+", "_", regex=True)
12 .str.strip("_")
13 )
14 
15 df["email"] = df["email"].str.strip().str.lower()
16 df.loc[~df["email"].str.contains(r"^[^@]+@[^@]+\.[^@]+$", na=False), "email"] = np.nan
17 
18 df["name"] = (
19 df["name"]
20 .str.strip()
21 .str.replace(r"\s+", " ", regex=True)
22 .str.title()
23 )
24 
25 df["phone"] = (
26 df["phone"]
27 .astype("string")
28 .str.replace(r"\D", "", regex=True)
29 .str.replace(r"^1(\d{10})$", r"\1", regex=True)
30 )
31 df.loc[df["phone"].str.len() != 10, "phone"] = pd.NA
32 
33 df["signup_date"] = pd.to_datetime(df["signup_date"], errors="coerce")
34 
35 df["country"] = (
36 df["country"].str.strip().str.upper().replace(
37 {"USA": "US", "U.S.": "US", "UNITED STATES": "US", "UK": "GB"}
38 )
39 )
40 
41 spend = df["total_spend"].astype("string").str.replace(r"[$,]", "", regex=True)
42 df["total_spend"] = pd.to_numeric(spend, errors="coerce").fillna(0.0)
43 
44 df = df.drop_duplicates(subset="email", keep="last")
45 df = df.dropna(subset=["email", "name"]).reset_index(drop=True)
46 
47 return df
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Copying the input up front keeps the cleaning function free of surprising side effects on the caller's data.
  2. 2pandas string accessors and regex let you normalize entire columns without explicit loops.
  3. 3Coercing bad values to NaN/NA first lets a final dropna enforce which fields are mandatory.

Related explainers

python 
from flask import Blueprint, jsonify
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
 

Building a health check endpoint in Flask

health-check blueprint error-handling
Intermediate 6 steps
java 
public final class EmailNormalizer {
 
    private static final Pattern EMAIL_PATTERN = Pattern.compile(
        "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$"

Normalizing email addresses in Java

validation regex normalization
Intermediate 8 steps
python 
from datetime import date, timedelta
from typing import Annotated
 
from fastapi import APIRouter, Depends, Query

Validating date ranges with FastAPI dependencies

dependency-injection validation pydantic
Intermediate 6 steps
python 
from collections.abc import MutableMapping
 
 
class CaseInsensitiveDict(MutableMapping):

Building a case-insensitive dict in Python

data structures abstract base classes dunder methods
Intermediate 8 steps
python 
from fastapi import FastAPI, Request, status
from fastapi.encoders import jsonable_encoder
from fastapi.exceptions import RequestValidationError
from fastapi.responses import JSONResponse

Redacting sensitive fields in FastAPI errors

validation error-handling security
Intermediate 7 steps
typescript 
type Masker = (value: string) => string;
 
const maskEmail: Masker = (value) => {
  const [local, domain] = value.split("@");

Recursively masking sensitive data for logs

recursion regex data-masking
Intermediate 9 steps

Share this explainer

Here's the card — post it anywhere.

Cleaning a customer DataFrame with pandas — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code