How to Clean Messy CSV Files with Python: A Beginner’s Guide

# Introduction

 
When you are just starting out with data analysis, one of the first things you learn is how to clean a dataset. It sounds basic, but it is one of the most important skills you will use again and again.

The funny part is that even as a professional, you will still spend a lot of your time cleaning data instead of analyzing it, building models, or evaluating results. Why? Because raw data is rarely clean. It can have missing values, wrong formats, duplicate rows, messy strings, invalid dates, strange categories, and noisy entries.

Before you can understand what the data is telling you, you need to fix these issues.

In this guide, we will clean a messy customer CSV file using Python and pandas. We will start by loading and inspecting the data, then clean column names, handle missing values, remove duplicates, standardize text, convert data types, validate emails, and save the final clean CSV file.

# 1. Loading the CSV

 
The first step is to load the messy dataset into pandas.

import pandas as pd
df = pd.read_csv("messy_customers.csv", keep_default_na=False)
df

We are using a customer CSV file that has common data quality issues. As you can already see, the file includes messy column names, inconsistent text formatting, mixed date formats, missing values, duplicate rows, and numbers stored as text.

# 2. Inspecting Before Cleaning

 
Before we start cleaning, we need to understand what is actually inside the dataset.

print("Shape:", df.shape)

print("\nColumn names:")
print(df.columns.tolist())

print("\nData types:")
print(df.dtypes)

print("\nExact duplicate rows:", df.duplicated().sum())

Output:

Shape: (10, 8)

Column names:
[' Customer ID ', ' Full Name ', 'AGE', ' Email Address ', 'Join Date', 'City', 'Membership', 'Total Spend']

Data types:
 Customer ID       object
 Full Name         object
AGE                object
 Email Address     object
Join Date          object
City               object
Membership         object
Total Spend        object
dtype: object

Exact duplicate rows: 1

This gives us a quick overview of the dataset before making any changes.

We can see that the dataset has 10 rows and 8 columns. The column names are messy because some of them have extra spaces and inconsistent casing. We can also see that every column is stored as an object, which usually means pandas is treating them as text.

The duplicate check also shows that there is 1 exact duplicate row. This is useful to know early because duplicate records can affect the final analysis.

# 3. Cleaning the Column Names

 
Now that we know the column names are messy, we will clean them first.

df.columns = (
    df.columns
    .str.strip()
    .str.lower()
    .str.replace(r"\s+", "_", regex=True)
)

df.columns.tolist()

Output:

['customer_id',
 'full_name',
 'age',
 'email_address',
 'join_date',
 'city',
 'membership',
 'total_spend']

This step removes extra spaces from the column names, converts everything to lowercase, and replaces spaces with underscores.

Now the column names are much easier to work with. Instead of writing names with spaces like Email Address, we can simply use email_address. This makes the code cleaner and helps avoid small mistakes later.

# 4. Replacing Blank Strings and Placeholders

 
Next, we will replace blank values and common placeholders with proper missing values.

df = df.replace(r"^\s*$", pd.NA, regex=True)

df = df.replace(
    ["N/A", "n/a", "NA", "unknown", "not a date"],
    pd.NA,
)

df.isna().sum()

Output:

customer_id      1
full_name        1
age              1
email_address    0
join_date        1
city              2
membership        1
total_spend       1
dtype: int64

Real-world CSV files often show missing data in different ways. Some cells are blank, some use N/A, and some use values like unknown or not a date.

We convert all of these into proper missing values so pandas can detect them correctly. After this step, it becomes easier to count, fill, or remove missing values later.

# 5. Removing Duplicate Rows

 
Now we will remove exact duplicate rows from the dataset.

print("Rows before:", len(df))

df = df.drop_duplicates().copy()

print("Rows after:", len(df))

Output:

Rows before: 10
Rows after: 9

Duplicate rows can create problems in your analysis because the same record may be counted more than once.

Here, we had 10 rows before removing duplicates and 9 rows after. This means one exact duplicate row was removed from the dataset.

# 6. Cleaning Text Columns

 
Now we will clean the text-based columns so the values are more consistent.

text_columns = ["customer_id", "full_name", "email_address", "city", "membership"]

for column in text_columns:
    df[column] = df[column].astype("string").str.strip()

df["full_name"] = (
    df["full_name"]
    .str.replace(r"\s+", " ", regex=True)
    .str.title()
)

df["city"] = df["city"].str.title()
df["membership"] = df["membership"].str.lower()
df["email_address"] = df["email_address"].str.lower()

df[text_columns]

Text columns usually need extra cleaning because people write the same type of information in different ways.

In this step, we remove extra spaces from customer_id, full_name, email_address, city, and membership. Then we clean the formatting so names and cities use title case, while emails and membership values use lowercase.

This makes the dataset easier to read and also helps us avoid category issues later. For example, Gold, GOLD, and gold should all be treated as the same membership value.

# 7. Standardizing Categories

 
Now we will clean the membership column so it only contains valid categories.

allowed_memberships = {"bronze", "silver", "gold"}

df.loc[~df["membership"].isin(allowed_memberships), "membership"] = pd.NA

df["membership"].value_counts(dropna=False)

Output:

membership
gold      4
silver    2
      2
bronze    1
Name: count, dtype: Int64

This step makes sure that the membership column only contains the values we expect.

In this dataset, the valid membership types are bronze, silver, and gold. Any value outside these categories, such as platinum, is replaced with a missing value so we can handle it later.

# 8. Converting Age to a Number

 
Next, we will convert the age column from text to numbers.

df["age"] = pd.to_numeric(df["age"], errors="coerce")

df.loc[~df["age"].between(0, 120), "age"] = pd.NA

df["age"] = df["age"].astype("Int64")

df[["full_name", "age"]]

The age column was stored as text, so we need to convert it into a numeric column before using it for analysis.

We also remove values that do not make sense, such as negative ages or ages above 120. Any invalid age is turned into a missing value, which we will fix later.

# 9. Converting Mixed Date Formats

 
Now we will clean the join_date column.

df["join_date"] = pd.to_datetime(
    df["join_date"],
    format="mixed",
    dayfirst=True,
    errors="coerce",
)

df[["full_name", "join_date"]]

Dates are often messy in CSV files because they can appear in different formats.

This step converts the join_date column into a proper datetime column. We use "mixed" because the dates in this file do not all follow the same format. Any invalid date is converted into a missing value.

# 10. Cleaning Currency Values

 
Next, we will clean the total_spend column.

df["total_spend"] = (
    df["total_spend"]
    .astype("string")
    .str.replace(r"[^0-9.-]", "", regex=True)
)

df["total_spend"] = pd.to_numeric(df["total_spend"], errors="coerce")

df[["full_name", "total_spend"]]

The total_spend column contains currency symbols, commas, and text values, so pandas cannot treat it as a number yet.

This step removes everything except numbers, decimal points, and minus signs. Then we convert the column into a numeric value so we can calculate totals, averages, and other useful metrics.

# 11. Validating Email Addresses

 
Now we will check whether the email addresses have a valid format.

email_pattern = r"^[^\s@]+@[^\s@]+\.[^\s@]+$"

valid_email = df["email_address"].str.match(email_pattern, na=False)

df.loc[~valid_email, "email_address"] = pd.NA

df[["full_name", "email_address"]]

This is a simple email validation step.

It checks whether each email has the basic structure of an email address. If an email is clearly invalid, we replace it with a missing value. This helps keep the email_address column cleaner and more reliable.

# 12. Handling Missing Values

 
Now we will decide what to do with the remaining missing values.

df = df.dropna(subset=["customer_id"]).copy()

df["full_name"] = df["full_name"].fillna("Unknown")
df["city"] = df["city"].fillna("Unknown")
df["membership"] = df["membership"].fillna("unassigned")

median_age = int(df["age"].median())
df["age"] = df["age"].fillna(median_age)

df["total_spend"] = df["total_spend"].fillna(0.0)

print("Median age used:", median_age)

df.isna().sum()

Output:

Median age used: 31

customer_id      0
full_name        0
age               0
email_address     1
join_date         1
city              0
membership        0
total_spend       0
dtype: int64

For this dataset, we remove rows where customer_id is missing because it is the main identifier for each customer.

For the other columns, we use sensible replacements. Missing names and cities become Unknown, missing membership values become unassigned, missing ages are filled with the median age, and missing spending values are filled with 0.0.

We still have missing values in email_address and join_date, and that is okay. Sometimes it is better to keep missing values instead of forcing a value that may not be correct.

# 13. Checking the Cleaned Data

 
Before saving the final file, we should check that the cleaned dataset follows the rules we expect.

final_memberships = {"bronze", "silver", "gold", "unassigned"}

assert df["customer_id"].notna().all()
assert df["customer_id"].is_unique
assert df["age"].between(0, 120).all()
assert df["total_spend"].ge(0).all()
assert df["membership"].isin(final_memberships).all()

print("All validation checks passed.")

Output:

All validation checks passed.

These checks help us confirm that the important cleaning steps worked.

We are checking that every customer has an ID, customer IDs are unique, ages are valid, total spend is not negative, and membership values are only from the final approved list. If all checks pass, we can feel more confident using this cleaned dataset.

# 14. Reviewing the Final Result

 
Now we can review the cleaned dataset and make sure everything looks correct.

df.head()

At this stage, the data is much cleaner than before.

The column names are consistent, the text values have been cleaned, the age column is now numeric, the join date is in a proper date format, and the total spend column is ready for calculations.

This final review is important because it gives us one last chance to quickly spot any obvious issue before saving the cleaned file.

# 15. Saving the Clean CSV

 
Finally, we will save the cleaned dataset as a new CSV file.

df.to_csv(
    "clean_customers.csv",
    index=False,
    date_format="%Y-%m-%d",
)

print("Saved clean file to clean_customers.csv")

Output:

Saved clean file to clean_customers.csv

We save the cleaned dataset as a separate file so the original messy CSV stays unchanged.

This is a good practice because you can always go back to the raw file if something goes wrong or if you want to apply a different cleaning approach later.

# Final Thoughts

 
Most people think they know how to clean a dataset, but the real challenge starts when you have to make sure the data is actually ready for analysis.

It is not just about removing missing values or fixing column names. You also need to check data types, handle invalid values, remove duplicates, standardize categories, validate important fields, and run final checks before trusting the dataset.

That is where many beginners make mistakes. They clean the data on the surface, but they do not validate whether the final dataset makes sense.

In this guide, we followed a simple but practical workflow for cleaning a messy CSV file with Python and pandas. We loaded the data, inspected it, cleaned the columns, handled missing values, fixed text, converted numbers and dates, validated emails, checked the final result, and saved a clean CSV file.

This is the kind of workflow you can reuse in almost any real-world data project. The dataset may change, but the process stays mostly the same: inspect, clean, validate, and save.
 
 

Abid Ali Awan (@1abidaliawan) is a certified data scientist professional who loves building machine learning models. Currently, he is focusing on content creation and writing technical blogs on machine learning and data science technologies. Abid holds a Master's degree in technology management and a bachelor's degree in telecommunication engineering. His vision is to build an AI product using a graph neural network for students struggling with mental illness.