Cleaning messy data

01The problem

Kolanut is moving to a new system, and the old one can only export customers as customer_list_raw.csv. Open it and you'll see why nobody trusts it:

• Names with stray spaces and random capitals: kayode distributors , ADA SUPERSTORE.
• Regions spelled 23 different ways: Lagos, LAGOS, Lagos , SW, South-West, south west…
• Credit limits as text: ₦2,050,000, 1,400,000.00, 850000, and a few blanks.
• Dates in three layouts: 2023-07-11, 22/10/2023, 30-Sep-2023.
• Phone numbers as 0803…, 0803 123 4567, +234 803… and 234803….
• Some customers exported twice.

Count Lagos customers in this file without cleaning it and you get the wrong answer twice over: spelling variants are missed, and duplicates are counted twice. Cleaning isn't tidying for its own sake. It decides whether your numbers are right.

02The concept

Load everything as text first

raw = pd.read_csv("https://academy.cloudtechanalytics.com/datasets/cleaning/customer_list_raw.csv", dtype=str)

dtype=str stops pandas guessing types on a messy file. Without it, a phone column of digits becomes numbers and loses its leading 0, and a column with one text value in it silently becomes text anyway. Load as text, then convert each column deliberately.

Text methods: .str

Every text column has a .str accessor with the cleaning tools you need:

Chain them: raw["Customer Name"].str.strip().str.title().

Standardise categories with a mapping

Bring every variant to one form (lower case, no hyphens), then map the abbreviations with a dictionary:

REGIONS = {"sw": "south west", "se": "south east", "ss": "south south", "nw": "north west", "nc": "north central"}
region = raw["Region"].str.strip().str.lower().str.replace("-", " ").replace(REGIONS).str.title()

Then check: region.value_counts() should show exactly the six regions you expect. Any extra value is a spelling you haven't handled.

Numbers stored as text

pd.to_numeric converts text to numbers. Remove the symbols first; blanks become NaN (Not a Number), pandas' marker for a missing value. Adding errors="coerce" turns anything else that won't convert into NaN too, instead of stopping with an error: useful, but check how many you lost.

Mixed date layouts

pd.to_datetime(raw["Date Joined"], format="mixed", dayfirst=True)

format="mixed" works the layout out for each value; dayfirst=True reads 03/01/2023 as 3 January, as Nigerian systems write it.

Missing values

• Find them: df.isna().sum() counts blanks per column.
• Decide what a blank means before you touch it. A missing credit limit isn't a zero limit: filling it with 0 would quietly understate every total and average. Usually you leave it as NaN (pandas skips it in sum and mean) and report it.
• fillna(value) fills blanks; dropna(subset=[...]) drops rows with blanks in the columns you name. Use them only when you can explain why.

Duplicates

• df.duplicated(subset=["customer_name"]).sum() counts repeats of a key.
• df.drop_duplicates(subset=["customer_name"]) keeps the first copy of each.
• Clean the key first. " ADA SUPERSTORE" and "Ada Superstore" are only duplicates once both are cleaned.

03Example

The whole cleaning script, one column per line, into a new, tidy DataFrame:

import pandas as pd

raw = pd.read_csv("https://academy.cloudtechanalytics.com/datasets/cleaning/customer_list_raw.csv", dtype=str)

REGIONS = {"sw": "south west", "se": "south east", "ss": "south south", "nw": "north west", "nc": "north central"}

clean = pd.DataFrame({
    "customer_name": raw["Customer Name"].str.strip().str.split().str.join(" ").str.title(),
    "region": raw["Region"].str.strip().str.lower().str.replace("-", " ").replace(REGIONS).str.title(),
    "city": raw["City"].str.strip().str.title(),
    "phone": raw["Phone"].str.replace(r"\D", "", regex=True).str.replace(r"^234", "0", regex=True),
    "joined_date": pd.to_datetime(raw["Date Joined"], format="mixed", dayfirst=True),
    "credit_limit": pd.to_numeric(raw["Credit Limit"].str.replace(r"[₦,]", "", regex=True)),
    "channel": raw["Channel"].str.strip(),
})

print(f"Rows before removing duplicates: {len(clean)}")
clean = clean.drop_duplicates(subset="customer_name")
print(f"Rows after: {len(clean)}")
clean["region"].value_counts()

Rows before removing duplicates: 102
Rows after: 90
region
Lagos            34
South East       14
North West       11
South South      11
South West       10
North Central    10
Name: count, dtype: int64

Six regions, no stray spellings, 90 customers. The phone line removes every non-digit, then turns a leading 234 into 0, so every number ends up as 11 digits starting with 0.

04Walkthrough

1. Load the raw file with dtype=str and run raw.head(10). Spot each problem from the list above.
2. Check the region mess before fixing it: raw["Region"].nunique() gives 23.
3. Build the clean DataFrame from the Example one line at a time, running clean.head() after each new column to see the result.
4. Check the phones: clean["phone"].str.len().value_counts() should show only 11. A different length is a number that needs a closer look.
5. Spot-check dates: compare raw["Date Joined"].head(10) with clean["joined_date"].head(10). 22/10/2023 must be 22 October.
6. Count blanks: clean.isna().sum(). Three credit limits are missing.
7. Count duplicates before dropping them: clean.duplicated(subset="customer_name").sum() gives 12.
8. Drop duplicates and re-check clean["region"].value_counts().
9. Save the result for later: clean.to_csv("customers_clean.csv", index=False). In Colab, the file appears in the Files panel to download.

05Practice

06Challenge

07Check your understanding