Data Insights Masterclass — Part 5

Numeric Cleaning & Outlier Handling in Pandas: Complete Guide

Inconsistent floating decimals aur extreme outliers aapke statistical calculations ko kharab kar sakte hain. Sikhiye numerical columns ko clean aur normalize karne ki engineering jatinanalytics par.

📑 Is Masterclass Guide Mein Aap Kya Sikhenge:

Numerical data points ko standardize karne aur anomalies/outliers ko manage karne ke 6 powerful professional tools:

• Decimal Precision Control: round() for clean financial aggregates
• Sign Normalization: Removing negative anomalies using abs()
• Direct Outlier Capping: Restricting extreme value thresholds using clip()
• Robust Outlier Filtering: Interquartile Range (IQR) detection method
• Parametric Outlier Scanning: Standard deviation Z-Score method
• Winsorization / Percentile Capping: Quantile percentile caps (99th and 1st percentile)

🔍 Kya Hai: round() Pandas ka ek basic mathematical function hai jo numerical columns ke continuous floating-point values ko aapse specified decimal places tak round off kar deta hai.

🎯 Kyu Use Hota Hai: High-precision division calculations ya statistical mean operations ke baad float values me lambi decimal sequences ban jati hain (e.g., 54.33333333). Yeh extra precision reporting, dashboards, aur database engines ke liye space-heavy aur unreadable hoti hai.

💡 Kab Use Hota Hai: Feature engineering calculations ke baad, business reports generator steps me, aur final dashboards presentation load karne se pehle.

💻 Real-World Code Examples:

Example 1: Employees dataset me dynamic "Bonus" field calculation ke baad decimals ko 2 points tak limits karna.

import pandas as pd
employees = pd.read_csv("employees.csv")
employees["Bonus"] = (employees["Salary"] * 0.125).round(2)

Example 2: E-commerce order records me discount prices calculations ko single flat digit precision standardise karna.

ecommerce["Final_Price"] = (ecommerce["Price"] * (1 - ecommerce["Discount"])).round(1)

📊 Expected Output:

# Input
values:  [120.45612, 450.11119, 89.99999]
# After round(2): [120.46,    450.11,    90.0]     # Perfectly standard floating points!

✅ Best Practices — Data Insights Rulebook:

• Calculations ke *mid-way* (beech me) kabhi bhi rounding na apply karein, isse numerical precision leakage ho sakta hai jise "Rounding Error Accumulation" kehte hain. Hamesha final values par round apply karein.
• Currencies formats ke liye standard parameter value 2 places (`round(2)`) use karein.
• Verify floating data types before running round operations.

💬 Crack the Interview:

🔍 Kya Hai: abs() (Absolute value) function column ke sabhi numerical data points se unke negative sign (-) ko strips/remove karke unhe purely positive values (absolute magnitude) me normalize karta hai.

🎯 Kyu Use Hota Hai: ERP systems or financial ledgers me transaction direction (debit/credit) ko differentiate karne ke liye cashflows ko negative sign se register kiya jata hai (e.g. -$500). Distance matrices, loss metrics calculations, ya balance analyses me sign hatana zaroori ho jata hai.

💡 Kab Use Hota Hai: Transaction magnitudes analysis me, coordinate distance calculations me, ya error offsets metrics processing algorithms me.

💻 Real-World Code Examples:

Example 1: Bank credit debit logs me negative balances transactions ko standard positive levels me convert karna.

bank["TransactionAmount"] = bank["TransactionAmount"].abs()

Example 2: Forecast model evaluations me actual vs predicted errors variance offset check mapping.

ecommerce["Forecast_Error"] = (ecommerce["Sales"] - ecommerce["Predicted"]).abs()

📊 Expected Output:

# Before: [-250.0, -1450.0, 320.0]
# After:  [250.0,  1450.0,  320.0]  # signs normalized!

✅ Best Practices — Data Insights Rulebook:

• Signs clean out karne se pehle business logic check karein, debit/credit flags change hone se accounting reports disturb ho sakti hain. Flags ko alag column me register karein.
• Direct mathematical differences metrics chains par apply karein (like Example 2 where error variance magnitude check was needed).
• Verify metrics dtypes post execution.

💬 Crack the Interview:

🔍 Kya Hai: clip() ek structural bounding constraint function hai jo kisi column ke extreme low and high parameters outliers values ko binary levels boundaries me lock (cap) kar deta hai.

🎯 Kyu Use Hota Hai: Jab statistical model building me extreme outliers exist karte hain jo predictions ko distort kar sakte hain, tab hum rows drop nahi karna chahte (taaki data size intact rahe). clip() un extreme values ko upper aur lower standard limits par force adjust kar deta hai.

💡 Kab Use Hota Hai: Outliers boundary containment steps me, financial credit balance limits setups me, or age ranges limits checks (e.g., keeping age bounded within 18 and 65).

💻 Real-World Code Examples:

Example 1: Restricting employee age column values within 18 and 65 years limits.

employees["Age"] = employees["Age"].clip(lower=18, upper=65)

Example 2: Capping extreme transaction sizes indicators within standard limits standard bank logs.

bank["TransactionAmount"] = bank["TransactionAmount"].clip(lower=100, upper=50000)

📊 Expected Output (Example 1):

# Input
values:  [15,  25,  45,  72]
# After clip:   [18,  25,  45,  65]  #
Values out of bounds forced to
limit points!

✅ Best Practices — Data Insights Rulebook:

• Capping parameters arbitrary values rakhne ke bajaye data attributes statistical analysis distributions indicators (jaise standard deviation bounds) par select karein.
• To restrict only one side of the distribution, we can pass only one boundary argument: df['col'].clip(upper=1000) (lower bounds will remain completely untouched).
• Verify if outliers density reduced.

💬 Crack the Interview:

🔍 Kya Hai: Interquartile Range (IQR) method outlier detection ka sabse robust statistical standard model hai. Isme hum dataset ko percentiles categories me divide karke, 25th percentile (Q1) aur 75th percentile (Q3) nikalte hain. IQR, Q3 aur Q1 ke beech ka standard span hota hai, aur outliers is range ke extreme limits coordinates par filter hote hain.

🎯 Kyu Use Hota Hai: Non-parametric data (jo perfect normal bell curve display nahi karta) me outlier values scan aur remove karne ke liye. IQR calculation normal parameters me outliers distribution skewness ko stabilize karta hai.

💡 Kab Use Hota Hai: Machine learning features preparation, credit loan approvals risk checks, dynamic pricing curves cleaning structures.

💻 Real-World Code Examples:

Example 1: Filtering out standard outliers from employees "Salary" column using IQR standard.

Q1 = employees["Salary"].quantile(0.25)
Q3 = employees["Salary"].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
# Filter out rows falling outside these bounds
clean_employees = employees[(employees["Salary"] >= lower_bound) & (employees["Salary"] <= upper_bound)]

Example 2: Isolate and display only the outlier transaction records from the banking dataset.

Q1_bal = bank["Balance"].quantile(0.25)
Q3_bal = bank["Balance"].quantile(0.75)
IQR_bal = Q3_bal - Q1_bal
outlier_mask = (bank["Balance"] < (Q1_bal - 1.5 * IQR_bal)) | (bank["Balance"] > (Q3_bal + 1.5 * IQR_bal))
only_outliers_df = bank[outlier_mask]

📊 Expected Output (Example 2):

# Isolated outliers records list displayed on target panel:
   AccountHolder   Balance       Branch
12  VIP_Client_A  850000.0   Delhi_Main  # Outlier identified (Extremely high balance)

✅ Best Practices — Data Insights Rulebook:

• IQR default factor coefficient is 1.5. If you want to detect extreme outliers only, you can change it to 3.0: upper = Q3 + 3 * IQR.
• Do not blindly drop all identified outliers. Isolate them into an audit table first to investigate if they represent fraud or system errors.
• Calculate IQR values on train sets separately during ML splits.

💬 Crack the Interview:

🔍 Kya Hai: Z-Score method measure karta hai ki aapka single raw numeric cell value overall mean (average) point se kitne standard deviation door hai. Z-score threshold beyond standard levels (generally > 3 or < -3) outliers mark map indicators represent karta hai.

🎯 Kyu Use Hota Hai: Parametric datasets (jo normal symmetric Gaussian distribution satisfy karte hain) me statistical precision ke sath anomalies/outliers dhoondhne ke liye.

💡 Kab Use Hota Hai: Standard normalized distributions features checks, quality check gates systems me.

💻 Real-World Code Examples:

Example 1: Filtering outliers beyond 3 standard deviations using scipy stats zscore.

from scipy import stats
z_scores = stats.zscore(employees["Salary"].dropna())
# Filter: keep only rows within -3 and +3 standard deviations
clean_employees = employees[abs(z_scores) < 3]

Example 2: Manual mathematical execution of Z-Score calculation without scipy.

mean_val = employees["Age"].mean()
std_val = employees["Age"].std()
employees["Age_ZScore"] = (employees["Age"] - mean_val) / std_val

📊 Expected Output (Example 2):

# Individual raw row value transformed to Z-Scores:
# Age: 25 -> Age_ZScore: -0.45 (Below average)
# Age: 65 -> Age_ZScore: +2.95 (Highly above average, near outlier threshold)

✅ Best Practices — Data Insights Rulebook:

• Remember that Z-Score assumes a normal distribution. If applied on highly skewed datasets, standard deviation and mean will be biased, giving misleading z-scores.
• For skewed data, prefer IQR method or use Modified Z-score (which utilizes Median Absolute Deviation - MAD).
• Check model outcomes variances curves carefully.

💬 Crack the Interview:

🔍 Kya Hai: Percentile Capping (Winsorization) outlier treatment ka ek standard method hai. Isme hum specific high percentile (jaise 99th percentile) aur low percentile (jaise 1st percentile) values nikalte hain, aur is boundary ke bahar jaane wale points ko inhi edge percentiles par overwrite (cap) kar dete hain.

🎯 Kyu Use Hota Hai: Real-world analytics me rows delete karne se information loss hota hai (jisase predictive models weak ho jate hain). Percentile Capping outliers ko eliminate bhi kar deta hai aur master database records size ko fully preserve rakhta hai.

💡 Kab Use Hota Hai: Pre-modeling scaling phases me, high-density transactional balance logs datasets cleanup setups configurations me.

💻 Real-World Code Examples:

Example 1: Implementing 1st and 99th percentile capping on employees "Salary" column.

lower_cap = employees["Salary"].quantile(0.01)
upper_cap = employees["Salary"].quantile(0.99)
# Cap the salary
values within bounds
employees["Salary"] = employees["Salary"].clip(lower_cap, upper_cap)

Example 2: Dynamic percentile capping on transactional balances inside banking databases.

low_bound = bank["Balance"].quantile(0.05)
high_bound = bank["Balance"].quantile(0.95)
bank["Balance"] = bank["Balance"].clip(low_bound, high_bound)

📊 Expected Output (Example 2):

# If 95th percentile balance is 4,50,000.0.
# Any client account balance higher than this (e.g. 12,00,000.0) is capped directly at 4,50,000.0.

✅ Best Practices — Data Insights Rulebook:

• Select percentile values based on standard guidelines. Typically, [0.01, 0.99] or [0.05, 0.95] are standard bounds depending on outlier frequency.
• Check how data density varies. Percentile capping does not reduce sample row count, but concentrates boundary points.
• Document dynamic limits values of quantiles.

💬 Crack the Interview:

Apne numerical cleaning aur outlier requirements ke basis par right statistical method chunye:

Scenario / Situation	Recommended Tool	Key Implication
Inconsistent floating decimals points	round()	Standardizes numeric representations for tables.
Negative transactional anomalies	abs()	Converts negative changes to pure magnitude.
Skewed datasets outlier detection	IQR Method	Utilizes robust percentiles, unaffected by extremes.
Normally distributed symmetric outliers	Z-Score Method	Identifies anomalies beyond standard deviations.
Preventing data loss from outlier drops	Percentile Capping (clip)	Locks outliers at edge quantiles, preserving size.

Next Post Preview: Masterclass Part 6

Next masterclass tutorial mein hum cover karenge: Date & Time Handling (pd.to_datetime, dt structures, and timedelta arithmetic) ko details layouts and practice questions ke sath jatinanalytics par.

Happy Coding & Stay Analytically Pure! 🚀