Why Data is Missing
Missing data happens for many reasons: form fields left blank, system errors, data not collected yet. Pandas represents missing values as NaN (Not a Number).
python
import pandas as pd
import numpy as np
df = pd.DataFrame({
"Name": ["Ahmed","Sara",None,"Layla","Karim"],
"Sales": [45000, np.nan, 38000, np.nan, 55000],
"Region": ["Cairo","Alex","Cairo",None,"Giza"],
"Years": [3, 5, np.nan, 7, 2],
})
# Overview of missing data
print("isnull():")
print(df.isnull())
print("\nMissing per column:")
print(df.isnull().sum())
print("\n% missing:")
print((df.isnull().sum() / len(df) * 100).round(1))
Output
Missing per column:
Name 1
Sales 2
Region 1
Years 1
dtype: int64
% missing:
Name 20.0
Sales 40.0
Region 20.0
Years 20.0
dtype: float64Visualising Missing Data
python
# Rows with ANY missing value
print(f"Rows with missing: {df.isnull().any(axis=1).sum()}")
# Show only rows that have missing values
print(df[df.isnull().any(axis=1)])
# Heatmap of missing (use missingno library for visuals)
# import missingno as msno
# msno.matrix(df)
💡 Tip: Always check missing values FIRST before any analysis. df.info() gives a quick count of non-null values per column.
🎯 TASKPractice: Finding Missing Values
Load a messy dataset, generate a full missing data report: count per column, percentage, and identify rows with more than 1 missing value.
Starter code:
python
import pandas as pd, numpy as np
np.random.seed(42)
n = 50
df = pd.DataFrame({
"ID": range(1, n+1),
"Name": [f"Customer_{i}" for i in range(n)],
"Age": np.random.randint(20,60,n).astype(float),
"Income": np.random.normal(8000,2000,n).round(0),
"City": np.random.choice(["Cairo","Alex","Giza"],n),
"Score": np.random.randint(300,800,n).astype(float),
})
# Introduce missing values
df.loc[np.random.choice(n,8,replace=False),"Age"] = np.nan
df.loc[np.random.choice(n,5,replace=False),"Income"] = np.nan
df.loc[np.random.choice(n,3,replace=False),"City"] = np.nan
df.loc[np.random.choice(n,6,replace=False),"Score"] = np.nan
print("=== Missing Data Report ===")
missing = df.isnull().sum()
pct = (missing / len(df) * 100).round(1)
report = pd.DataFrame({"Missing": missing, "Percent": pct})
print(report[report["Missing"] > 0])
print(f"\nTotal missing cells: {df.isnull().sum().sum()}")
multi_missing = df[df.isnull().sum(axis=1) > 1]
print(f"Rows with 2+ missing: {len(multi_missing)}")
Filling Missing Values
python
import pandas as pd, numpy as np
df = pd.DataFrame({
"Sales": [45000,np.nan,38000,np.nan,55000,42000],
"Region": ["Cairo",None,"Alex","Cairo",None,"Giza"],
"Month": [1,2,3,4,5,6],
})
# Fill with a fixed value
df2 = df.copy()
df2["Region"].fillna("Unknown", inplace=True)
# Fill numeric with mean / median
df2["Sales"].fillna(df["Sales"].mean(), inplace=True)
# Forward fill (use previous value)
df3 = df.copy()
df3["Sales"].fillna(method="ffill", inplace=True)
# Backward fill
df3["Region"].fillna(method="bfill", inplace=True)
# Fill with group mean
df["Sales"] = df.groupby("Region")["Sales"].transform(
lambda x: x.fillna(x.mean())
)
print(df)
Output
Sales Region Month
0 45000.0 Cairo 1
1 38000.0 Alex 2
2 38000.0 Alex 3
3 45000.0 Cairo 4
4 55000.0 Giza 5
5 42000.0 Giza 6Dropping Missing Values
python
# Drop rows with ANY missing
df.dropna(inplace=True)
# Drop rows where SPECIFIC column is missing
df.dropna(subset=["Sales"], inplace=True)
# Drop columns with too many missing (>50%)
threshold = len(df) * 0.5
df.dropna(thresh=threshold, axis=1, inplace=True)
# Drop if ALL values in row are NaN
df.dropna(how="all", inplace=True)
💡 Tip: Prefer filling over dropping when possible — dropping rows loses data. Use group-based filling for the most accurate imputation.
🎯 TASKPractice: Filling & Dropping Missing Data
Apply 3 different filling strategies to a sales dataset and compare results: mean fill, group mean fill, and forward fill. Show which strategy reduces error most.
Starter code:
python
import pandas as pd, numpy as np
np.random.seed(42)
# Create data with pattern
dates = pd.date_range("2024-01-01", periods=20, freq="D")
actual = 50000 + np.sin(np.arange(20)) * 5000 + np.random.normal(0, 1000, 20)
sales = actual.copy()
# Introduce 4 missing values
missing_idx = [3, 7, 12, 17]
sales[missing_idx] = np.nan
df = pd.DataFrame({"Date": dates, "Sales": sales, "Actual": actual})
# Strategy 1: Mean fill
s1 = df["Sales"].fillna(df["Sales"].mean())
# Strategy 2: Interpolation (linear)
s2 = df["Sales"].interpolate(method="linear")
# Strategy 3: Forward fill
s3 = df["Sales"].fillna(method="ffill")
# Compare errors at missing positions
for name, s in [("Mean", s1), ("Interpolate", s2), ("FFill", s3)]:
mae = abs(s.iloc[missing_idx] - actual[missing_idx]).mean()
print(f"{name:<12}: MAE = {mae:,.0f}")
Finding & Removing Duplicates
python
import pandas as pd
df = pd.DataFrame({
"Order_ID": [101, 102, 101, 103, 102, 104],
"Product": ["Laptop","Phone","Laptop","Tablet","Phone","Monitor"],
"Sales": [12000, 6500, 12000, 4200, 6500, 8000],
})
# Check duplicates
print(f"Duplicate rows: {df.duplicated().sum()}")
print(f"Duplicate Order_IDs: {df.duplicated('Order_ID').sum()}")
# View duplicates
print(df[df.duplicated(keep=False)])
# Remove duplicates
df_clean = df.drop_duplicates()
df_clean2 = df.drop_duplicates(subset=["Order_ID"], keep="last")
print(f"After clean: {len(df_clean)} rows")
Output
Duplicate rows: 2
Duplicate Order_IDs: 4
Order_ID Product Sales
0 101 Laptop 12000
1 102 Phone 6500
2 101 Laptop 12000
4 102 Phone 6500
After clean: 4 rowsFixing Data Types
python
import pandas as pd
df = pd.read_csv("sales.csv")
print(df.dtypes)
# Convert types
df["Sales"] = pd.to_numeric(df["Sales"], errors="coerce")
df["Date"] = pd.to_datetime(df["Date"])
df["Category"] = df["Category"].astype("category")
df["ID"] = df["ID"].astype(str).str.zfill(6)
df["Flag"] = df["Flag"].map({"yes":True,"no":False})
print(df.dtypes)
print(f"Category uses {df['Category'].memory_usage(deep=True):,} bytes")
💡 Tip: Always fix data types right after loading. Wrong types cause silent errors — groupby on a numeric-as-string column gives wrong results.
🎯 TASKPractice: Duplicates & Data Types
Clean a transaction log: remove exact duplicates, remove duplicates by transaction ID (keep latest), fix all column types, and report what was changed.
Starter code:
python
import pandas as pd, numpy as np
data = {
"TxID": [1001,1002,1003,1001,1004,1002,1005],
"Date": ["2024-01-15","2024-01-16","2024-01-16","2024-01-15","2024-01-17","2024-01-18","2024-01-17"],
"Amount": ["5000","bad","3200","5000","12000","6500","4800"],
"Type": ["sale","sale","refund","sale","sale","refund","sale"],
"Active": ["yes","yes","no","yes","yes","no","yes"],
}
df = pd.DataFrame(data)
original_rows = len(df)
# 1. Remove exact duplicates
df = df.drop_duplicates()
# 2. Remove duplicate TxIDs (keep last)
df = df.drop_duplicates(subset=["TxID"], keep="last")
# 3. Fix types
df["Date"] = pd.to_datetime(df["Date"])
df["Amount"] = pd.to_numeric(df["Amount"], errors="coerce")
df["Type"] = df["Type"].astype("category")
df["Active"] = df["Active"].map({"yes":True,"no":False})
print(f"Rows: {original_rows} → {len(df)}")
print(f"Missing Amount: {df['Amount'].isnull().sum()}")
print(df.dtypes)
print(df)
Common String Problems
python
import pandas as pd
df = pd.DataFrame({
"Name": [" ahmed HASSAN ","SARA ali","Omar Ibrahim","layla"],
"City": ["CAIRO","alex "," GIZA ","Port said"],
"Code": ["EMP-001","emp002","EMP_003","004"],
"Email": ["ahmed@corp.COM","SARA@tech.io","omar@BIZ.net",""],
})
# Standardize names and cities
df["Name"] = df["Name"].str.strip().str.title()
df["City"] = df["City"].str.strip().str.title()
# Standardize codes (remove separators, uppercase, add prefix)
df["Code"] = ("EMP-" + df["Code"].str.replace(r"[^0-9]","",regex=True).str.zfill(3))
# Lowercase emails, handle empty
df["Email"] = df["Email"].str.lower().str.strip()
df.loc[df["Email"]=="","Email"] = np.nan
print(df)
Output
Name City Code Email
0 Ahmed Hassan Cairo EMP-001 ahmed@corp.com
1 Sara Ali Alex EMP-002 sara@tech.io
2 Omar Ibrahim Giza EMP-003 omar@biz.net
3 Layla Port Said EMP-004 NaNReplace & Standardize Values
python
import pandas as pd
df["Region"] = df["Region"].replace({
"cairo": "Cairo", "CAIRO": "Cairo",
"alex": "Alexandria", "ALEX": "Alexandria",
"N/A": None, "NA": None, "-": None,
})
# Regex replace — remove unwanted characters
df["Phone"] = df["Phone"].str.replace(r"[^0-9]","",regex=True)
df["Amount"] = df["Amount"].str.replace(r"[,EGP$ ]","",regex=True)
df["Amount"] = pd.to_numeric(df["Amount"])
# Extract parts
df["FirstName"] = df["Name"].str.split().str[0]
df["Domain"] = df["Email"].str.split("@").str[-1]
🎯 TASKPractice: String Cleaning
Clean a messy product catalog: standardize names, fix prices (remove currency symbols), standardize categories, and extract brand from product name.
Starter code:
python
import pandas as pd, numpy as np
products = pd.DataFrame({
"raw_name": [" Samsung LAPTOP Pro ","apple iPhone 15","DELL Monitor 27"","hp keyboard wireless"],
"raw_price": ["EGP 12,000","$6,500","12000 EGP","450.00"],
"raw_cat": ["ELECTRONICS","electronics","Elec.","Electronics "],
"in_stock": ["yes","YES","No","YES"],
})
# 1. Clean product name
products["name"] = products["raw_name"].str.strip().str.title()
# 2. Extract brand (first word)
products["brand"] = products["name"].str.split().str[0]
# 3. Clean price — remove all non-numeric except dot
products["price"] = pd.to_numeric(
products["raw_price"].str.replace(r"[^0-9.]","",regex=True),
errors="coerce"
)
# 4. Standardize category
products["category"] = products["raw_cat"].str.strip().str.replace(r"[^a-zA-Z]","",regex=True).str.title()
# 5. Boolean in_stock
products["in_stock"] = products["in_stock"].str.lower() == "yes"
print(products[["name","brand","price","category","in_stock"]].to_string(index=False))
What is an Outlier?
An outlier is a value unusually far from the rest. It might be a data entry error, a system glitch, or a genuine extreme event — you need to investigate before removing.
Z-Score Method
python
import pandas as pd, numpy as np
sales = pd.Series([45000,52000,48000,61000,1200000,58000,31000,55000])
# Z-score: how many standard deviations from the mean
z_scores = (sales - sales.mean()) / sales.std()
print("Z-scores:")
print(z_scores.round(2))
print(f"\nOutliers (|z| > 3): {sales[abs(z_scores) > 3].tolist()}")
Output
Z-scores:
0 -0.64
1 -0.47
2 -0.56
3 -0.26
4 6.64
5 -0.38
6 -0.93
7 -0.41
dtype: float64
Outliers (|z| > 3): [1200000]IQR Method (More Robust)
python
import pandas as pd, numpy as np
sales = pd.Series([45000,52000,48000,61000,1200000,58000,31000,55000])
Q1 = sales.quantile(0.25)
Q3 = sales.quantile(0.75)
IQR = Q3 - Q1
lower_fence = Q1 - 1.5 * IQR
upper_fence = Q3 + 1.5 * IQR
outliers = sales[(sales < lower_fence) | (sales > upper_fence)]
clean = sales[(sales >= lower_fence) & (sales <= upper_fence)]
print(f"Q1={Q1:,.0f} Q3={Q3:,.0f} IQR={IQR:,.0f}")
print(f"Fences: [{lower_fence:,.0f}, {upper_fence:,.0f}]")
print(f"Outliers: {outliers.tolist()}")
print(f"Clean data: {len(clean)} of {len(sales)} rows")
Output
Q1=46,500 Q3=57,750 IQR=11,250
Fences: [29,625, 74,625]
Outliers: [1200000]
Clean data: 7 of 8 rows🎯 TASKPractice: Detecting Outliers
Detect outliers in a salary dataset using both Z-score and IQR methods. Compare results and investigate which are likely errors vs genuine cases.
Starter code:
python
import pandas as pd, numpy as np
np.random.seed(42)
salaries = pd.Series(
list(np.random.normal(9000, 1500, 50)) +
[150000, -500, 95000, 0] # Some outliers
).round(0)
# Z-score method
z = (salaries - salaries.mean()) / salaries.std()
outliers_z = salaries[abs(z) > 3]
# IQR method
Q1, Q3 = salaries.quantile(0.25), salaries.quantile(0.75)
IQR = Q3 - Q1
lo, hi = Q1 - 1.5*IQR, Q3 + 1.5*IQR
outliers_iqr = salaries[(salaries < lo) | (salaries > hi)]
print(f"Mean: {salaries.mean():,.0f}")
print(f"Median: {salaries.median():,.0f}")
print(f"\nZ-score outliers ({len(outliers_z)}):")
print(sorted(outliers_z.tolist()))
print(f"\nIQR outliers ({len(outliers_iqr)}):")
print(sorted(outliers_iqr.tolist()))
# Mark likely errors (negative or impossibly high)
errors = salaries[(salaries < 0) | (salaries > 100000)]
print(f"\nLikely data errors: {errors.tolist()}")
Strategies for Handling Outliers
python
import pandas as pd, numpy as np
df = pd.DataFrame({
"Sales": [45000,52000,48000,1500000,58000,31000,55000,-5000],
"Region": ["Cairo","Alex","Cairo","Cairo","Giza","Alex","Giza","Cairo"],
})
# Strategy 1: Remove (drop the rows)
Q1, Q3 = df["Sales"].quantile([0.25, 0.75])
IQR = Q3 - Q1
mask = (df["Sales"] >= Q1-1.5*IQR) & (df["Sales"] <= Q3+1.5*IQR)
df_removed = df[mask]
print(f"After removal: {len(df_removed)} rows")
# Strategy 2: Cap (Winsorize) — replace with fence values
lo, hi = Q1 - 1.5*IQR, Q3 + 1.5*IQR
df_capped = df.copy()
df_capped["Sales"] = df_capped["Sales"].clip(lower=lo, upper=hi)
print("After capping:")
print(df_capped["Sales"].describe().round(0))
# Strategy 3: Transform (log scale reduces impact)
df_valid = df[df["Sales"] > 0].copy()
df_valid["Sales_Log"] = np.log(df_valid["Sales"])
print("\nLog-transformed (no outlier effect):", df_valid["Sales_Log"].std().round(2))
💡 Tip: Capping (Winsorizing) is often better than removal — you keep all rows and reduce outlier influence without losing data.
🎯 TASKPractice: Removing Outliers
Apply all 3 outlier strategies to a dataset. Compare: row count, mean, std before and after each strategy.
Starter code:
python
import pandas as pd, numpy as np
np.random.seed(42)
data = pd.Series(
list(np.random.normal(50000, 8000, 95)) +
[500000, 800000, -10000, 0, 1000000]
).round(0)
Q1, Q3 = data.quantile(0.25), data.quantile(0.75)
IQR = Q3 - Q1
lo = Q1 - 1.5*IQR
hi = Q3 + 1.5*IQR
# Strategy 1: Remove
removed = data[(data >= lo) & (data <= hi)]
# Strategy 2: Cap
capped = data.clip(lower=lo, upper=hi)
# Strategy 3: Log transform (positive values only)
log_data = np.log(data[data > 0])
print(f"{'Strategy':<12} {'Count':>6} {'Mean':>10} {'Std':>10}")
print("-"*40)
for name, s in [("Original",data),("Removed",removed),("Capped",capped)]:
print(f"{name:<12} {len(s):>6} {s.mean():>10,.0f} {s.std():>10,.0f}")
What is Data Validation?
Validation checks that data meets expected rules and constraints — like age being 0-120, email having @, or sales being positive.
python
import pandas as pd, numpy as np
df = pd.DataFrame({
"Age": [25, -5, 150, 30, 28],
"Email": ["a@b.com","notanemail","c@d.io","","e@f.net"],
"Salary": [9500, 8000, 12000, -1000, 0],
"Score": [750, 300, 850, 1200, 600],
})
# Define validation rules
def validate(df):
issues = []
# Age: must be 18-100
bad_age = df[(df["Age"] < 18) | (df["Age"] > 100)]
if len(bad_age): issues.append(f"Invalid age: {len(bad_age)} rows")
# Email: must contain @
bad_email = df[~df["Email"].str.contains("@", na=False)]
if len(bad_email): issues.append(f"Invalid email: {len(bad_email)} rows")
# Salary: must be positive
bad_salary = df[df["Salary"] <= 0]
if len(bad_salary): issues.append(f"Non-positive salary: {len(bad_salary)} rows")
# Score: must be 300-850
bad_score = df[(df["Score"] < 300) | (df["Score"] > 850)]
if len(bad_score): issues.append(f"Invalid score: {len(bad_score)} rows")
return issues
for issue in validate(df):
print(f" ⚠️ {issue}")
Output
⚠️ Invalid age: 2 rows
⚠️ Invalid email: 2 rows
⚠️ Non-positive salary: 2 rows
⚠️ Invalid score: 1 rows🎯 TASKPractice: Data Validation
Build a complete validation system for a loan application dataset. Flag each row with specific issues and generate a validation report.
Starter code:
python
import pandas as pd, numpy as np, re
applications = pd.DataFrame({
"ID": [1,2,3,4,5],
"Age": [25, 17, 70, 35, -1],
"Income": [8000, 5000, 12000, -500, 9000],
"Score": [700, 550, 800, 650, 200],
"Amount": [50000, 30000, 200000, 40000, 25000],
"Phone": ["01012345678","0123456","01198765432","phone","01234567890"],
})
def validate_row(row):
errors = []
if not (21 <= row["Age"] <= 65): errors.append("age")
if row["Income"] < 3000: errors.append("income")
if row["Score"] < 600: errors.append("credit_score")
if row["Amount"] > row["Income"]*36: errors.append("amount_too_high")
if not re.match(r"^01[0125]\d{8}$", str(row["Phone"])): errors.append("phone")
return errors
applications["Issues"] = applications.apply(validate_row, axis=1)
applications["Valid"] = applications["Issues"].apply(len) == 0
print(applications[["ID","Age","Income","Score","Valid","Issues"]].to_string(index=False))
print(f"\nValid applications: {applications['Valid'].sum()} / {len(applications)}")
Why Encode Categories?
Machine learning models need numbers, not text. Encoding converts categorical columns to numeric representations.
python
import pandas as pd
df = pd.DataFrame({
"City": ["Cairo","Alex","Cairo","Giza","Alex","Cairo"],
"Level": ["Junior","Senior","Mid","Senior","Junior","Mid"],
"Score": [72, 95, 84, 91, 68, 79],
})
# 1. Label Encoding — assign integers
from sklearn.preprocessing import LabelEncoder
le = LabelEncoder()
df["City_Label"] = le.fit_transform(df["City"])
print("Label encoded:", dict(zip(le.classes_, range(len(le.classes_)))))
# 2. One-Hot Encoding — separate column per category
df_ohe = pd.get_dummies(df["City"], prefix="City")
print(df_ohe)
# 3. Ordinal Encoding — maintain order
level_order = {"Junior":1, "Mid":2, "Senior":3}
df["Level_Ord"] = df["Level"].map(level_order)
print(df[["Level","Level_Ord"]])
Output
Label encoded: {'Alex': 0, 'Cairo': 1, 'Giza': 2}
City_Alex City_Cairo City_Giza
0 0 1 0
1 1 0 0
...
Level Level_Ord
0 Junior 1
1 Senior 3
2 Mid 2💡 Tip: Use ordinal encoding for ordered categories (Junior < Mid < Senior). Use one-hot for unordered categories (City, Product). Avoid label encoding for unordered — it implies Cairo(1) < Giza(2) which is wrong.
🎯 TASKPractice: Encoding Categorical Data
Encode a customer dataset: ordinal encode satisfaction level, one-hot encode region, and frequency encode product category.
Starter code:
python
import pandas as pd
df = pd.DataFrame({
"Customer": [f"C{i}" for i in range(1,8)],
"Region": ["Cairo","Alex","Cairo","Giza","Cairo","Alex","Giza"],
"Product": ["Laptop","Phone","Laptop","Tablet","Phone","Laptop","Phone"],
"Satisfaction": ["Low","High","Medium","High","Low","Medium","High"],
})
# 1. Ordinal encode Satisfaction
sat_order = {"Low":1,"Medium":2,"High":3}
df["Sat_Score"] = df["Satisfaction"].map(sat_order)
# 2. One-Hot encode Region
ohe_region = pd.get_dummies(df["Region"], prefix="Region")
df = pd.concat([df, ohe_region], axis=1)
# 3. Frequency encode Product (replace with how often it appears)
freq = df["Product"].value_counts(normalize=True)
df["Product_Freq"] = df["Product"].map(freq)
cols = ["Customer","Sat_Score","Product_Freq"] + list(ohe_region.columns)
print(df[cols].to_string(index=False))
Converting Continuous to Categorical
python
import pandas as pd, numpy as np
ages = pd.Series([22, 35, 18, 45, 28, 55, 31, 67, 42, 19])
# Equal-width bins
age_groups = pd.cut(ages,
bins = [0, 25, 40, 60, 100],
labels = ["Young","Adult","Middle","Senior"]
)
print("Age groups:")
print(pd.DataFrame({"Age": ages, "Group": age_groups}))
Output
Age groups:
Age Group
0 22 Young
1 35 Adult
2 18 Young
3 45 Middle
4 28 Adult
5 55 Middle
6 31 Adult
7 67 Senior
8 42 Middle
9 19 YoungQuantile-Based Binning
python
import pandas as pd, numpy as np
np.random.seed(42)
sales = pd.Series(np.random.normal(50000,15000,100).round(0))
# qcut — equal-frequency bins (same number in each bin)
quartiles = pd.qcut(sales, q=4, labels=["Q1 Low","Q2","Q3","Q4 High"])
print("Distribution:")
print(quartiles.value_counts().sort_index())
# Custom bins for business logic
performance = pd.cut(sales,
bins = [-np.inf, 30000, 50000, 70000, np.inf],
labels = ["Poor","Average","Good","Excellent"]
)
print("\nPerformance:")
print(performance.value_counts())
💡 Tip: Use pd.cut() for fixed boundaries (e.g., age groups). Use pd.qcut() for equal-sized groups (percentile-based).
🎯 TASKPractice: Binning & Discretization
Bin employee salaries into pay grades, create age groups, and analyze the distribution of performance scores by salary band.
Starter code:
python
import pandas as pd, numpy as np
np.random.seed(42)
df = pd.DataFrame({
"Name": [f"Emp_{i}" for i in range(1,21)],
"Salary": np.random.randint(6000, 25000, 20),
"Age": np.random.randint(22, 60, 20),
"Perf": np.random.randint(60, 100, 20),
})
# Salary bands
df["Pay_Grade"] = pd.cut(df["Salary"],
bins = [0, 8000, 12000, 18000, np.inf],
labels = ["Grade C","Grade B","Grade A","Grade S"]
)
# Age groups
df["Age_Group"] = pd.cut(df["Age"],
bins = [0, 30, 45, 100],
labels = ["Junior","Mid","Senior"]
)
# Perf quartiles
df["Perf_Band"] = pd.qcut(df["Perf"], q=4,
labels=["Low 25%","Low-Mid","High-Mid","Top 25%"]
)
# Analysis
print("Average performance by pay grade:")
print(df.groupby("Pay_Grade", observed=True)["Perf"].mean().round(1))
print("\nCount by age group:")
print(df["Age_Group"].value_counts().sort_index())
The Complete Pipeline
python
import pandas as pd, numpy as np
def clean_sales_data(df):
"""
Complete data cleaning pipeline.
Returns cleaned DataFrame and a report dict.
"""
report = {"original_rows": len(df), "steps": []}
# Step 1: Standardize column names
df.columns = (df.columns.str.strip()
.str.lower()
.str.replace(r"[\s]+","_",regex=True))
report["steps"].append("Column names standardized")
# Step 2: Remove exact duplicates
before = len(df)
df = df.drop_duplicates()
report["steps"].append(f"Removed {before-len(df)} duplicate rows")
# Step 3: Fix data types
if "date" in df.columns:
df["date"] = pd.to_datetime(df["date"], errors="coerce")
if "sales" in df.columns:
df["sales"] = pd.to_numeric(df["sales"], errors="coerce")
# Step 4: Handle missing values
num_cols = df.select_dtypes(include="number").columns
str_cols = df.select_dtypes(include="object").columns
df[num_cols] = df[num_cols].fillna(df[num_cols].median())
df[str_cols] = df[str_cols].fillna("Unknown")
# Step 5: Remove outliers (IQR)
if "sales" in df.columns:
Q1,Q3 = df["sales"].quantile([0.25,0.75])
IQR = Q3-Q1
before = len(df)
df = df[(df["sales"] >= Q1-1.5*IQR) & (df["sales"] <= Q3+1.5*IQR)]
report["steps"].append(f"Removed {before-len(df)} outliers")
# Step 6: Clean strings
for col in str_cols:
df[col] = df[col].str.strip().str.title()
report["final_rows"] = len(df)
return df, report
💡 Tip: Always save original data before cleaning: df_original = df.copy(). Document every cleaning step for reproducibility.
🎯 TASKPractice: Full Cleaning Pipeline
Apply the full pipeline to a real messy dataset. Generate a before/after comparison report.
Starter code:
python
import pandas as pd, numpy as np
np.random.seed(42)
# Generate messy data
n = 100
raw = pd.DataFrame({
" Order ID ": range(1,n+1),
"Customer Name": [f"customer_{i}" for i in np.random.randint(1,20,n)],
" Sales Amount": np.random.normal(50000,20000,n).round(0),
"Region": np.random.choice(["cairo","CAIRO","Alex","ALEX","giza"],n),
"Order Date": pd.date_range("2024-01-01",periods=n,freq="D").astype(str),
})
# Add issues
raw.loc[np.random.choice(n,10,replace=False)," Sales Amount"] = np.nan
raw.loc[np.random.choice(n,5,replace=False),"Region"] = "N/A"
raw = pd.concat([raw, raw.iloc[:5]]) # Add duplicates
raw.iloc[0,2] = 5000000 # Outlier
print("=== BEFORE CLEANING ===")
print(f"Rows: {len(raw)}")
print(f"Missing: {raw.isnull().sum().sum()}")
print(f"Duplicates: {raw.duplicated().sum()}")
# Clean
df = raw.copy()
df.columns = df.columns.str.strip().str.lower().str.replace(" ","_")
df = df.drop_duplicates()
df["sales_amount"] = pd.to_numeric(df["sales_amount"],errors="coerce")
df["sales_amount"].fillna(df["sales_amount"].median(), inplace=True)
df["region"] = df["region"].str.strip().str.title().replace({"N/A":"Unknown"})
Q1,Q3 = df["sales_amount"].quantile([0.25,0.75]); IQR=Q3-Q1
df = df[(df["sales_amount"]>=Q1-1.5*IQR)&(df["sales_amount"]<=Q3+1.5*IQR)]
print("\n=== AFTER CLEANING ===")
print(f"Rows: {len(df)}")
print(f"Missing: {df.isnull().sum().sum()}")
print(f"Sales mean: {df['sales_amount'].mean():,.0f}")