🧹 Finding Missing Values

Unit 6 — Real-world data is messy. Learn to find and fix every type of data quality problem.

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: float64

Visualising 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 & Dropping Missing Data

Unit 6 — Real-world data is messy. Learn to find and fix every type of data quality problem.

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 6

Dropping 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}")

🧹 Duplicates & Data Types

Unit 6 — Real-world data is messy. Learn to find and fix every type of data quality problem.

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 rows

Fixing 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)

🧹 String Cleaning

Unit 6 — Real-world data is messy. Learn to find and fix every type of data quality problem.

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": ["[email protected]","[email protected]","[email protected]",""], }) # 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 [email protected] 1 Sara Ali Alex EMP-002 [email protected] 2 Omar Ibrahim Giza EMP-003 [email protected] 3 Layla Port Said EMP-004 NaN

Replace & 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))

🧹 Detecting Outliers

Unit 6 — Real-world data is messy. Learn to find and fix every type of data quality problem.

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()}")

🧹 Removing Outliers

Unit 6 — Real-world data is messy. Learn to find and fix every type of data quality problem.

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}")

🧹 Data Validation

Unit 6 — Real-world data is messy. Learn to find and fix every type of data quality problem.

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": ["[email protected]","notanemail","[email protected]","","[email protected]"], "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)}")

🧹 Encoding Categorical Data

Unit 6 — Real-world data is messy. Learn to find and fix every type of data quality problem.

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))

🧹 Binning & Discretization

Unit 6 — Real-world data is messy. Learn to find and fix every type of data quality problem.

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 Young

Quantile-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())

🧹 Full Cleaning Pipeline

Unit 6 — Real-world data is messy. Learn to find and fix every type of data quality problem.

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}")