Pandas Data Cleaning & GroupBy
Real-world datasets are messy, frequently containing missing fields due to sensor dropouts, optional user inputs, or data corruption.
The 2 Primary Strategies for Missing Data:
- Deletion (
dropna()):df.dropna(how='any'): Drops any row containing at least oneNaN.df.dropna(subset=['critical_column']): Drops rows only if a specific mandatory column is null.
- Imputation (
fillna()): Replaces missing values with statistical measures to preserve sample size:- Mean / Median: For numerical distributions (median is robust against extreme outliers).
- Mode (Most Frequent): For categorical columns.
- Forward Fill (
ffill): For time-series data (propagates the last known valid value forward).
# Missing Data Cleaning Simulation:
raw_sensor_data = [
{"timestamp": "10:00", "temperature": 28.5, "humidity": 65},
{"timestamp": "10:05", "temperature": None, "humidity": 68}, # Missing temp
{"timestamp": "10:10", "temperature": 29.1, "humidity": None},# Missing humidity
{"timestamp": "10:15", "temperature": 28.8, "humidity": 64}
]
# Calculate mean temperature of valid records for imputation:
valid_temps = [r["temperature"] for r in raw_sensor_data if r["temperature"] is not None]
mean_temp = sum(valid_temps) / len(valid_temps)
# Impute missing values:
cleaned_data = []
for record in raw_sensor_data:
row = record.copy()
if row["temperature"] is None:
row["temperature"] = round(mean_temp, 1) # Mean Imputation!
if row["humidity"] is None:
row["humidity"] = 60 # Default fallback
cleaned_data.append(row)
print(f"--- 🧹 Cleaned Sensor Data (Imputed Mean Temp: {mean_temp:.1f}°C) ---")
for r in cleaned_data:
print(r)In real Pandas: df['temperature'] = df['temperature'].fillna(df['temperature'].mean()) performs this entire imputation in a single vectorized line.
The Split-Apply-Combine concept (formalized by Hadley Wickham) is the foundation of group analysis in data science:
- Split: Partition the dataset into groups based on keys (e.g. split sales by
Region). - Apply: Execute an aggregation function (such as
mean(),sum(),count(), orstd()) independently on each subgroup. - Combine: Merge the resulting scalar metrics back into a unified summary DataFrame.
# GroupBy Aggregation Simulation:
sales_transactions = [
{"region": "South", "rep": "Balaji", "sales": 450000},
{"region": "North", "rep": "Alex", "sales": 320000},
{"region": "South", "rep": "Chloe", "sales": 580000},
{"region": "North", "rep": "David", "sales": 290000},
{"region": "West", "rep": "Elena", "sales": 410000}
]
# Group sales by region and calculate totals:
regional_summary = {}
for item in sales_transactions:
reg = item["region"]
if reg not in regional_summary:
regional_summary[reg] = {"total_sales": 0, "deal_count": 0}
regional_summary[reg]["total_sales"] += item["sales"]
regional_summary[reg]["deal_count"] += 1
print("--- 📈 Regional Sales Aggregation Summary ---")
for reg, stats in regional_summary.items():
avg = stats["total_sales"] / stats["deal_count"]
print(f"• Region: {reg:6} | Total: ₹{stats['total_sales']:>10,} | Deals: {stats['deal_count']} | Avg Deal: ₹{avg:>8,}")In Pandas: df.groupby('region')['sales'].agg(['sum', 'mean', 'count']) produces this full statistical summary table in a single line!
Calculating mean/median across the entire dataset before train_test_split causes Data Leakage (information from the test set leaks into the training pipeline). Always calculate statistics strictly on the training set and apply them to the test set.
Simulate merging two datasets: match customer orders with their corresponding customer addresses using customer_id as the join key.
customers = {1: "Balaji (Hyderabad)", 2: "Alex (Bengaluru)"}
orders = [{"order_id": 101, "cust_id": 1, "amount": 4500}, {"order_id": 102, "cust_id": 2, "amount": 1200}]
# Equivalent to pd.merge(orders_df, customers_df, on="cust_id", how="inner")
merged = []
for ord in orders:
merged.append({
"order_id": ord["order_id"],
"customer": customers.get(ord["cust_id"], "Unknown"),
"amount": ord["amount"]
})
print("Merged Orders Table:")
for m in merged:
print(f"Order #{m['order_id']}: {m['customer']} - ₹{m['amount']}")Q What is the difference between pd.merge() and pd.concat()?
pd.merge() combines DataFrames horizontally based on matching key columns (relational SQL JOIN). pd.concat() stacks DataFrames either vertically (appending rows) or horizontally (aligning indices).
Q What are the 4 join types supported by pd.merge()?
how="inner" (keeps only keys present in both DataFrames), how="left" (keeps all rows from left DataFrame), how="right" (keeps all from right), and how="outer" (keeps all rows from both, filling unmatched values with NaN).
Q How do I export a Pandas DataFrame to Excel (.xlsx)?
Use df.to_excel("filename.xlsx", index=False) using the openpyxl engine.