Python Data Cleaning Cheat Sheet

📌Inspect the Dataset

Understand the structure, data types, missing values, and basic statistics.

df.head()
df.info()
df.describe()

📌Check Missing Values

Count missing values across every column before deciding how to handle them.

df.isnull().sum()  # Total missing values
df.isnull().mean() * 100  # Percentage of missing values

📌Remove Missing Values

Delete rows or columns when missing data cannot be meaningfully recovered.

df.dropna()  # Remove rows with missing values
df.dropna(subset=["email"])  # Remove rows with missing values in the "email" column
df.dropna(axis=1)  # Remove columns with missing values

📌Fill Missing Values

Replace missing values using constants, averages, medians, or previous values.

df["age"].fillna(df["age"].median())  # Fill with median
df["city"].fillna("Unknown")  # Fill with a constant
df.ffill()  # Forward fill

📌Remove Duplicate Records

Identify and remove repeated rows that may distort analysis.

df.duplicated().sum()  # Count duplicates
df.drop_duplicates()  # Remove duplicates
df.drop_duplicates(subset=["customer_id"])  # Remove duplicates based on "customer_id"

📌Fix Data Types

Convert columns into the correct numerical, text, or datetime format.

df["price"] = pd.to_numeric(df["price"], errors="coerce")  # Convert to numeric
df["date"] = pd.to_datetime(df["date"])  # Convert to datetime
df["category"] = df["category"].astype("category")  # Convert to category

📌Clean Column Names

Standardize column names to make filtering and coding easier.

df.columns = df.columns.str.strip()  # Remove leading/trailing spaces
df.columns = df.columns.str.lower()  # Convert to lowercase
df.columns = df.columns.str.replace(" ", "_")  # Replace spaces with underscores

📌Clean Text Values

Remove spaces, normalize capitalization, and replace inconsistent labels.

df["name"] = df["name"].str.strip()  # Remove leading/trailing spaces
df["city"] = df["city"].str.title()  # Capitalize city names
df["status"] = df["status"].replace({"Yes": "Y", "No": "N"})  # Replace values

📌Detect Outliers

Find unusually high or low values using the interquartile range.

q1 = df["salary"].quantile(0.25)
q3 = df["salary"].quantile(0.75)
iqr = q3 - q1
outliers = (df["salary"] < q1 - 1.5 * iqr) | (df["salary"] > q3 + 1.5 * iqr)

📌Handle Outliers

Remove, cap, or transform extreme values based on business context.

lower = q1 - 1.5 * iqr
upper = q3 + 1.5 * iqr
df["salary"] = df["salary"].clip(lower, upper)  # Cap values

📌Standardize Categories

Fix inconsistent spelling, capitalization, and category labels.

df["department"] = df["department"].str.lower()  # Normalize capitalization
df["department"] = df["department"].replace({"human resources": "hr"})  # Replace labels

📌Validate Value Ranges

Check whether numerical values fall within realistic boundaries.

df = df[df["age"].between(18, 100)]  # Filter age between 18 and 100
df = df[df["quantity"] > 0]  # Ensure quantity is positive
df = df[df["rating"].between(1, 5)]  # Ensure ratings are between 1 and 5

📌Split and Extract Text

Separate combined fields or extract useful patterns from text.

df[["first_name", "last_name"]] = df["full_name"].str.split(" ", expand=True)  # Split names
df["email_domain"] = df["email"].str.extract(r"@(.+)$")  # Extract email domains

📌Rename and Reorder Columns

Create clearer labels and organize columns for easier analysis.

df.rename(columns={"amt": "amount"}, inplace=True)  # Rename columns
df = df[["customer_id", "date", "amount", "status"]]  # Reorder columns

📌Reset the Index

Create a clean sequential index after removing or filtering rows.

df = df.reset_index(drop=True)

📌Final Quality Check

Confirm the dataset is complete, consistent, and analysis-ready.

df.info()  # Check structure
df.isnull().sum()  # Check missing values
df.duplicated().sum()  # Check duplicates
df.describe(include="all")  # Check statistics

📌Recommended Cleaning Workflow

df.to_csv("cleaned_data.csv", index=False)

📌Real-Time Story: 🧹 Cleaning Data Like a Pro

Imagine you're a data analyst working for an e-commerce company. Your manager hands you a messy dataset containing customer orders. Here's how you clean it step by step:

By following these steps, you ensure the data is reliable, consistent, and ready for actionable insights!