# Introduction
When you're just getting started with data analysis, one of the first things you learn is how to clean a dataset. It sounds basic, but it's one of the most important skills you'll use over and over again.
The funny part is that even as a professional, you will still spend most 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, incorrect formats, duplicate lines, dirty strings, invalid dates, irregular paragraphs, and noisy entries.
Before you can understand what the data is telling you, you need to address these issues.
In this guide, we will clean up a dirty customer CSV file using Python again the pandas. We'll start by loading and checking the data, then cleaning up column names, handling missing values, removing duplicates, formatting text, changing data types, validating emails, and saving the final clean CSV file.
# 1. Loading CSV
The first step is to load the raw dataset into pandas.
import pandas as pd
df = pd.read_csv("messy_customers.csv", keep_default_na=False)
df
We use a CSV file for a customer with common data quality issues. As you can already see, the file includes dirty column names, inconsistent text formatting, mixed date formats, missing values, duplicate lines, and numbers stored as text.
# 2. Pre-Cleaning Inspection
Before we start cleaning, we need to understand what exactly is 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: 1This gives us a quick overview of the dataset before making any changes.
We see that the dataset has 10 rows and 8 columns. Column names are messy because some of them have extra spaces and inconsistent alphabets. We can also see that each column is stored as objectusually means pandas treat them like text.
A duplicate check also shows that there is exactly 1 duplicate row. This is useful to know in advance because duplicate records can affect the final analysis.
# 3. Cleaning Column Names
Now that we know the column names are dirty, we'll clean them up 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 column names, converts everything to lowercase, and replaces spaces with underscores.
Now column names are much easier to work with. Instead of writing words with spaces like an email address, we can simply use email_address. This makes the code cleaner and helps avoid minor mistakes later.
# 4. Replacing Blank Wires and Holders
Next, we'll replace empty values and common placeholders with appropriate null 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: int64Real-world CSV files often show missing data in different ways. Some cells are empty, some are active N/Aand others use similar values unknown or not a date.
We convert all of these into appropriate null values so that pandas can see them correctly. After this step, it is easy to calculate, fill in, or remove missing values later.
# 5. Removing Duplicate Rows
Now we will remove exactly 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: 9Duplicate rows can cause problems in your analysis because the same record can be counted more than once.
Here, we had 10 rows before removing duplicates and 9 rows after. This means that one duplicate row has been removed from the dataset.
# 6. Cleaning Text Columns
Now we'll clean up the text-based columns so that the values are equal.
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 often need more cleaning because people write the same type of information in different ways.
In this step, we remove extra spaces from them customer_id, full_name, email_address, cityagain membership. We then cleaned up the formatting so that 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 classification problems later. For example, Gold, GOLDagain gold all should be considered the same membership value.
# 7. Estimating Sections
Now we will clean up membership column therefore contains only valid fields.
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 ensures that the membership column contains only the values we expect.
For this dataset, the valid membership types are bronze, silveragain gold. Any value outside these categories, such as platinumit is replaced with a null value so that we can handle it later.
# 8. Converting Age to 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"]]
I age column was stored as text, so we need to convert it to a numeric column before using it for analysis.
We also remove values that do not make sense, such as negative years or more than 120 years. Any invalid age is returned as a null value, which we will fix later.
# 9. Converting Mixed Date Formats
Now we will clean up 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 changes the join_date column in the appropriate date column. We use "mixed" because the dates in this file do not all follow the same format. Any invalid date is converted to a null value.
# 10. Cleanup of 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"]]
I total_spend column contains capital signs, commas, and text values, so pandas can't treat it as a number yet.
This step removes everything except numbers, decimal points, and minus signs. We then convert the column to a numeric value so we can calculate sums, ratios, and other useful metrics.
# 11. Verifying Email Addresses
Now we will check if the email addresses are in 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 step to verify email.
It checks that each email has the basic structure of an email address. If the email is clearly invalid, we return it with a null value. This helps to keep the email_address column cleaner and more reliable.
# 12. Managing Missing Assets
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: int64In this dataset, we delete the rows there customer_id it does not exist because it is the main indicator of each customer.
For other columns, we use logical substitution. Missing names and cities have UnknownMembership fees are not available unassignedmissing years are filled in with average years, and missing consumption values are filled in 0.0.
We still have some missing values email_address again join_dateand that's fine. Sometimes it's better to keep missing values instead of forcing a value that might be wrong.
# 13. Clean Data Testing
Before saving the final file, we must check that the clean 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.This test helps us to make sure that the important cleaning steps have worked.
We check that each customer has an ID, the customer IDs are unique, the age is valid, the amount spent is correct, and the membership prices are only from the last approved list. If all the 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 right.
At this stage, the data is much cleaner than before.
Column names are consistent, text values have been cleaned up, the age column is now a number, the join date is in the correct date format, and the sum of the spend column is ready to be calculated.
This final review is important because it gives us one last chance to quickly spot any obvious problem before saving the cleaned file.
# 15. Saving 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.csvWe save the cleaned dataset as a separate file so that the original dirty CSV remains intact.
This is a good practice because you can always go back to the raw file if something goes wrong or if you want to use a different cleaning method 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 ready for analysis.
It's not just removing missing values or correcting column names. You also need to check data types, handle invalid values, remove duplicates, balance categories, validate important fields, and perform final checks before trusting the dataset.
This is where many beginners make mistakes. They clean the data in place, but they don't verify if the final dataset is meaningful.
In this guide, we have followed a simple but effective workflow for cleaning a dirty CSV file with Python and pandas. We loaded the data, checked it, cleaned columns, handled missing values, fixed text, changed numbers and dates, verified 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 remains largely the same: scan, clean, validate, and save.
Abid Ali Awan (@1abidiawan) is a data science expert with a passion for building machine learning models. Currently, he focuses on creating content 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 idea is to create an AI product using a graph neural network for students with mental illness.
