Here is a new article based on the information in the provided post.
—
## A Practical Guide to Cleaning Messy CSV Files with Python and Pandas
When you start working with real-world data, one of the most frequent tasks is cleaning messy CSV files. While data cleaning is a fundamental skill, it is also one of the most time-consuming parts of a data science workflow. Even experienced professionals spend a significant portion of their time preparing data rather than building models or deriving insights.
In this guide, we will walk through a complete, practical workflow for cleaning a messy customer CSV file using Python and the pandas library. The process covers everything from loading the data to saving the final clean version, with a focus on common issues you will encounter in real projects.
### 1. Loading the CSV File
The first step is to load the dataset into a pandas DataFrame. It is also a good idea to prevent pandas from automatically converting values to `NaN` during loading, so we can handle missing values explicitly later.
“`python
import pandas as pd
df = pd.read_csv(“messy_customers.csv”, keep_default_na=False)
df
“`
### 2. Inspecting the Data Before Cleaning
Before making any changes, it is important to understand the structure and quality of your data.
“`python
print(“Shape:”, df.shape)
print(“Column names:”, df.columns.tolist())
print(“Data types:n”, df.dtypes)
print(“Duplicate rows:”, df.duplicated().sum())
“`
This revealed:
– 10 rows and 8 columns
– Inconsistent column names with extra spaces
– All columns initially read as `object` (text) type
– One exact duplicate row
### 3. Cleaning Column Names
Messy column names can cause errors and make code harder to read. We standardize them by stripping whitespace, converting to lowercase, and replacing spaces with underscores.
“`python
df.columns = (
df.columns
.str.strip()
.str.lower()
.str.replace(r”s+”, “_”, regex=True)
)
“`
The cleaned column names are now:
`[‘customer_id’, ‘full_name’, ‘age’, ’email_address’, ‘join_date’, ‘city’, ‘membership’, ‘total_spend’]`
### 4. Replacing Blank Strings and Placeholders
Real datasets often use various representations for missing data. We convert these into proper pandas missing values.
“`python
df = df.replace(r”^s*$”, pd.NA, regex=True)
df = df.replace([“N/A”, “n/a”, “NA”, “unknown”, “not a date”], pd.NA)
“`
After this step, missing values are consistently identified across columns such as `age`, `city`, and `membership`.
### 5. Removing Duplicate Rows
Duplicate rows can skew analysis and aggregations.
“`python
print(“Rows before:”, len(df))
df = df.drop_duplicates().copy()
print(“Rows after:”, len(df))
“`
This reduced the dataset from 10 rows to 9 by removing one exact duplicate.
### 6. Cleaning Text Columns
Text fields often suffer from inconsistent spacing, casing, and formatting.
“`python
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()
“`
The result is consistent formatting, for example, names in title case and emails in lowercase.
### 7. Standardizing Categories
It is important to ensure that categorical columns contain only expected values.
“`python
allowed_memberships = {“bronze”, “silver”, “gold”}
df.loc[~df[“membership”].isin(allowed_memberships), “membership”] = pd.NA
“`
Values like “platinum” that are not part of the allowed set are replaced with missing values.
### 8. Converting Age to a Number
The age column was originally stored as text and may contain invalid entries.
“`python
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”)
“`
Invalid ages such as negatives or extreme values are turned into missing values.
### 9. Standardizing Date Formats
Dates in CSV files often appear in different formats.
“`python
df[“join_date”] = pd.to_datetime(
df[“join_date”],
format=”mixed”,
dayfirst=True,
errors=”coerce”
)
“`
Using `”mixed”` allows pandas to parse multiple date formats, with unparseable entries converted to missing values.
### 10. Cleaning Currency Values
The total spend column may include symbols and text that prevent numeric operations.
“`python
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”)
“`
After cleaning, the column is numeric and ready for calculations like sums and averages.
### 11. Validating Email Addresses
Invalid email formats can cause issues in communication and analysis.
“`python
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
“`
Emails that do not match a basic pattern are flagged as missing.
### 12. Handling Missing Values
At this stage, we decide how to handle remaining missing data.
“`python
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)
“`
Key decisions:
– Rows with missing `customer_id` are removed
– Other missing values are filled with sensible defaults like median age or “Unknown”
### 13. Validating the Cleaned Data
Before finalizing, we run checks to ensure the dataset meets basic quality standards.
“`python
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.”)
“`
These assertions confirm that IDs are valid, ages are reasonable, spend is non-negative, and membership values are controlled.
### 14. Reviewing the Final Result
A quick review shows a clean and consistent dataset. Column names are normalized, text is standardized, numeric columns are properly formatted, and dates are parsed correctly.
### 15. Saving the Clean CSV
Finally, we save the cleaned data to a new file, preserving the original messy version for reference.
“`python
df.to_csv(
“clean_customers.csv”,
index=False,
date_format=”%Y-%m-%d”,
)
print(“Saved clean file to clean_customers.csv”)
“`
### Final Thoughts
Cleaning data is not just about handling missing values or fixing column names. It involves validating data types, removing duplicates, standardizing categories, and verifying that the final dataset is trustworthy.
Following a structured workflow ensures that your analyses are based on high-quality data. This process can be reused across projects, even when the data sources change. Remember: inspect, clean, validate, and save.
—
**Source:** Adapted from *How to Clean Messy CSV Files with Python: A Beginner’s Guide* on KDnuggets. Original article available at [https://www.kdnuggets.com/](https://www.kdnuggets.com/wp-content/uploads/awan_clean_messy_csv_files_python_beginners_guide_1.png).
