AWS Builder Center

Builder Center

Clean Your Data in 6 Simple Steps with Python and pandas

Messy data gives wrong answers. This beginner guide shows six easy pandas steps to make any dataset ready for analysis.

prajwal S

Data Analyst | SQL, Python, Power BI & AWS | Turning data into clear business insights
Most of a data analyst's time goes into cleaning data. If the data is messy, your charts and reports will be wrong. The good news: a few simple steps fix most problems.
Here is a short checklist using Python and pandas. It works for any CSV file.

Step 0: Load the data

1
2
3
4
5
import pandas as pd

df = pd.read_csv("sales.csv")
print(df.shape)
df.head()

Step 1: Check the basics

See the column names, data types and missing values.
1
2
df.info()
df.isna().sum()

Step 2: Fix column names

Make names lowercase with no spaces. This makes code easier to write.
1
2
3
4
df.columns = (df.columns
.str.strip()
.str.lower()
.str.replace(" ", "_"))

Step 3: Remove duplicates

1
2
print("Duplicates:", df.duplicated().sum())
df = df.drop_duplicates()

Step 4: Handle missing values

Decide per column. Drop rows if a key field is missing. Fill others with a sensible value.
1
2
3
df = df.dropna(subset=["order_id"])
df["region"] = df["region"].fillna("Unknown")
df["amount"] = df["amount"].fillna(df["amount"].median())

Step 5: Fix data types

Dates and numbers are often read as text. Convert them.
1
2
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")

Step 6: Clean text values

"north", "North " and "NORTH" should be the same value.
1
2
df["region"] = df["region"].str.strip().str.title()
df["region"].value_counts()

Save the clean file

1
df.to_csv("sales_clean.csv", index=False)
Tip: On AWS, you can upload the clean file to Amazon S3 and query it with Amazon Athena or build a dashboard in Amazon QuickSight.

Quick recap

  • Look before you change anything.
  • Fix names, duplicates, missing values, types and text.
  • Keep the raw file. Save the clean data as a new file.
What is the messiest dataset you have cleaned? Share it in the comments!
# python# data-analytics
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article