Data Cleaning Handbook - Category Wise

1. isnull() / isna()

Kab Use: Jab check karna ho ki employees dataset mein kaunse cells null/NaN hain

employees.isnull()
employees["Salary"].isnull()
employees[employees["Salary"].isnull()]

2. isnull().sum()

Kab Use: Jab har column mein total kitni null values hain ye count karna ho

employees.isnull().sum()
employees[["Salary", "Age", "Department"]].isnull().sum()

3. isnull().mean() * 100

Kab Use: Jab har column mein missing values ka percentage check karna ho

employees.isnull().mean() * 100
employees[["Salary", "Age", "City"]].isnull().mean() * 100

4. notnull() / notna()

Kab Use: Jab sirf valid (non-null) values wale employees filter karne ho

employees[employees["Salary"].notnull()]
employees[employees["Department"].notna()]

5. dropna()

Kab Use: Jab null values wali rows ya columns ko delete/drop karna ho

employees.dropna()
employees.dropna(subset=["Salary"])
employees.dropna(subset=["Name", "Salary"])
employees.dropna(how="all")
employees.dropna(thresh=4)

6. dropna(axis=1)

Kab Use: Jab poora column hi hatana ho agar usme null value ho

employees.dropna(axis=1)

7. fillna() - Static Value

Kab Use: Jab missing values ki jagah ek fixed default value daalni ho

employees["City"] = employees["City"].fillna("Delhi")
employees["Salary"] = employees["Salary"].fillna(0)

8. fillna() with mean()

Kab Use: Jab Salary ya Age numeric column mein null ko average value se bharna ho

employees["Salary"] = employees["Salary"].fillna(employees["Salary"].mean())
employees["Age"] = employees["Age"].fillna(employees["Age"].mean())

9. fillna() with median()

Kab Use: Jab Salary column mein outliers ho aur median center value se fill karna ho

employees["Salary"] = employees["Salary"].fillna(employees["Salary"].median())

10. fillna() with mode()

Kab Use: Jab Department ya City categorical column mein sabse common value se fill karna ho

employees["Department"] = employees["Department"].fillna(employees["Department"].mode()[0])
employees["City"] = employees["City"].fillna(employees["City"].mode()[0])

11. ffill() - Forward Fill

Kab Use: Jab missing value ko pichli (upar wali) row ki value se fill karna ho

employees["Salary"] = employees["Salary"].ffill()
employees["Department"] = employees["Department"].ffill()

12. bfill() - Backward Fill

Kab Use: Jab missing value ko agli (niche wali) row ki value se fill karna ho

employees["Salary"] = employees["Salary"].bfill()
employees["Department"] = employees["Department"].bfill()

13. interpolate()

Kab Use: Jab missing numbers ko mathematical trend se fill karna ho (e.g. 10, NaN, 30 -> 20)

employees["Salary"] = employees["Salary"].interpolate()
employees["Age"] = employees["Age"].interpolate(method="linear")

14. isna().any() / isna().all()

Kab Use: Jab check karna ho ki kya column mein AT LEAST EK null hai (.any) ya SABHI null hain (.all)

employees.isna().any()
employees["Salary"].isna().any()
employees.isna().all()

15. duplicated()

Kab Use: Jab dataset mein duplicate rows trace/filter karni ho

employees.duplicated()
employees[employees.duplicated()]

16. duplicated(subset=[])

Kab Use: Jab specific columns ke combination (jaise Name + Department) par duplicate check karna ho

employees.duplicated(subset=["Name", "Department"])

17. duplicated().sum()

Kab Use: Jab dataset mein total kitni duplicate rows hain unka count nikalna ho

employees.duplicated().sum()
employees.duplicated(subset=["Name"]).sum()

18. drop_duplicates()

Kab Use: Jab duplicate rows ko permanent dataset se delete karna ho

employees = employees.drop_duplicates()
employees.drop_duplicates(inplace=True)

19. drop_duplicates(keep='first')

Kab Use: Jab duplicates mein se pehli entry ko safe rakhna ho aur baki duplicate entries hatani ho

employees.drop_duplicates(keep='first')

20. drop_duplicates(keep='last')

Kab Use: Jab duplicate entries mein se aakhri (latest) entry rakhni ho

employees.drop_duplicates(keep='last')

21. drop_duplicates(keep=False)

Kab Use: Jab sabhi repeating duplicate entries ko poori tarah delete kar dena ho

employees.drop_duplicates(keep=False)

22. str.strip()

Kab Use: Jab text ke aage aur peeche se extra space (whitespaces) hatana ho

employees["Name"] = employees["Name"].str.strip()

23. str.lstrip() / str.rstrip()

Kab Use: Jab sirf left (lstrip) ya right (rstrip) side ki extra spaces hatani ho

employees["Name"].str.lstrip()
employees["Name"].str.rstrip()

24. str.lower()

Kab Use: Jab saare text characters ko small letters (lowercase) mein convert karna ho

employees["Department"] = employees["Department"].str.lower()

25. str.upper()

Kab Use: Jab text ko Capital letters (uppercase) mein uniform banana ho

employees["Department"] = employees["Department"].str.upper()

26. str.title()

Kab Use: Jab har word ka pehla letter capital karna ho (e.g. rahul sharma -> Rahul Sharma)

employees["Name"] = employees["Name"].str.title()

27. str.capitalize()

Kab Use: Jab sirf poore sentence ka pehla letter capital rakhna ho

employees["City"] = employees["City"].str.capitalize()

28. str.replace()

Kab Use: Jab text mein koi specific character ya word change/replace karna ho

employees["Name"] = employees["Name"].str.replace("Mr.", "")
employees["Salary"] = employees["Salary"].str.replace(",", "")

29. str.replace(regex=True)

Kab Use: Jab RegEx pattern lagakar special characters ya numbers hatane ho

employees["Name"] = employees["Name"].str.replace(r'[^a-zA-Z\s]', '', regex=True)

30. str.contains()

Kab Use: Jab check karna ho ki text mein specific word hai ya nahi (filtering ke liye)

employees[employees["Department"].str.contains("Sales", na=False)]

31. str.startswith() / str.endswith()

Kab Use: Jab filter karna ho text jo specific word se shuru ya khatam hota ho

employees[employees["Name"].str.startswith("Ra")]
employees[employees["City"].str.endswith("pur")]

32. str.split()

Kab Use: Jab ek text column (e.g. Full Name) ko space/comma se todkar 2 columns me banana ho

employees[["First_Name", "Last_Name"]] = employees["Name"].str.split(" ", expand=True)

33. str.extract()

employees["Phone_Digits"] = employees["Phone"].str.extract(r'(\d+)')

34. str.len()

Kab Use: Jab text column ke character counts check karne ho

employees["Name_Length"] = employees["Name"].str.len()

35. astype()

Kab Use: Jab column ka data type manually change karna ho (e.g. float to int, object to category)

employees["Salary"] = employees["Salary"].astype(int)
employees["Department"] = employees["Department"].astype("category")

36. pd.to_numeric()

Kab Use: Jab text numbers ko numeric mein convert karna ho aur kachra data ko safe NaN banana ho

employees["Salary"] = pd.to_numeric(employees["Salary"], errors='coerce')

37. map()

Kab Use: Jab dictionary values ka use karke entries replace/swap karni ho

gender_map = {"M": "Male", "F": "Female"}
employees["Gender"] = employees["Gender"].map(gender_map)

38. replace()

Kab Use: Jab frame level par dictionary se key-value direct mass cleanup karna ho

employees["Department"] = employees["Department"].replace({"HR": "Human Resources"})
employees.replace("N/A", np.nan, inplace=True)

39. rename(columns={})

Kab Use: Jab system database column names ko clean aur short formats mein badalna ho

employees.rename(columns={"Emp_Sal": "Salary", "Emp_Name": "Name"}, inplace=True)

40. select_dtypes()

Kab Use: Jab filters lagakar sirf number ya sirf text columns ko clean-up ke liye select karna ho

employees.select_dtypes(include=["number"])
employees.select_dtypes(include=["object"])

41. round()

Kab Use: Jab floating numbers ko fixed decimal points (e.g. 2 decimal) tak round karna ho

employees["Salary"] = employees["Salary"].round(2)

42. abs()

Kab Use: Jab system error se negative me aayi values ko positive numbers mein badalna ho

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

43. clip()

Kab Use: Jab outliers ko drop kiye bina fixed lower/upper boundaries par lock (cap) karna ho

employees["Salary"] = employees["Salary"].clip(lower=10000, upper=200000)

44. pd.to_datetime()

Kab Use: Jab text format ki dates ko proper Pandas Datetime format mein badalna ho

employees["JoinDate"] = pd.to_datetime(employees["JoinDate"], format='mixed', errors='coerce')

45. dt.year / dt.month / dt.day

Kab Use: Jab date se saal, mahina ya din alag columns mein extract karna ho

employees["Year"] = employees["JoinDate"].dt.year
employees["Month"] = employees["JoinDate"].dt.month
employees["Day"] = employees["JoinDate"].dt.day

46. dt.weekday / dt.day_name()

Kab Use: Jab date se week ka din number (0-6) ya Name ("Monday") nikalna ho

employees["Day_Num"] = employees["JoinDate"].dt.weekday
employees["Day_Name"] = employees["JoinDate"].dt.day_name()

47. dt.strftime()

Kab Use: Jab date ko custom visual format (e.g. DD-MM-YYYY) mein display karna ho

employees["Formatted"] = employees["JoinDate"].dt.strftime("%d-%m-%Y")

48. Date Arithmetic

Kab Use: Jab do dates subtract karke tenure/experience days calculate karne ho

employees["Tenure_Days"] = (pd.Timestamp.now() - employees["JoinDate"]).dt.days

49. dt.quarter / dt.hour

Kab Use: Jab Business quarter (Q1-Q4) ya order execution hours extract karne ho

employees["Quarter"] = employees["JoinDate"].dt.quarter
ecommerce["Order_Hour"] = ecommerce["OrderDate"].dt.hour

50. IQR Method

Kab Use: Jab Interquartile range se lower & upper boundary nikal kar outliers filter karne ho

Q1 = employees["Salary"].quantile(0.25)
Q3 = employees["Salary"].quantile(0.75)
IQR = Q3 - Q1
employees = employees[(employees["Salary"] >= Q1 - 1.5*IQR) & (employees["Salary"] <= Q3 + 1.5*IQR)]

51. Z-Score Method

Kab Use: Jab Mean se 3 Standard Deviations door wali extreme values drop karni ho

from scipy import stats
z_scores = stats.zscore(employees["Salary"])
employees = employees[(z_scores > -3) & (z_scores < 3)]

52. Percentile Capping

Kab Use: Jab top 5% aur bottom 5% outliers ko exact boundaries par lock karna ho

lower = employees["Salary"].quantile(0.05)
upper = employees["Salary"].quantile(0.95)
employees["Salary"] = employees["Salary"].clip(lower, upper)

53. np.where()

Kab Use: Excel IF logic ki tarah single condition check karke values assign karni ho

import numpy as np
employees["Salary_Band"] = np.where(employees["Salary"] > 50000, "High", "Low")

54. np.select()

Kab Use: Multi-condition Nested IF logic ka upayog karke categories assign karni ho

conds = [employees["Salary"] < 30000, employees["Salary"].between(30000, 70000), employees["Salary"] > 70000]
choices = ["Low", "Medium", "High"]
employees["Tier"] = np.select(conds, choices, default="Unknown")

55. str.contains() + np.where()

Kab Use: Text keyword search pattern par dynamic 1/0 ya Yes/No flags create karne ho

employees["Is_Manager"] = np.where(employees["Name"].str.contains("Manager", na=False), "Yes", "No")

56. df.query()

Kab Use: SQL style logic format mein strings ke base par multiple rows clean filter karni ho

employees.query('Salary > 50000 and Department == "Sales"')

57. df.isin()

Kab Use: SQL IN operator behavior ki tarah explicit list me match karke filtering karni ho

employees[employees["Department"].isin(["Sales", "HR", "IT"])]

58. ~ Operator + isin()

Kab Use: Bulk exclusion filter (SQL NOT IN behavior) lagana

employees[~employees["Department"].isin(["Admin", "Temp"])]

59. apply() + lambda

Kab Use: Jab custom user-defined function single column par execute karna ho

employees["Tax"] = employees["Salary"].apply(lambda x: x * 0.20 if x > 50000 else x * 0.05)

60. apply(axis=1)

Kab Use: Jab multiple columns ka data aapas mein combine karke row-by-row calculation karni ho

employees["Total_Comp"] = employees.apply(lambda r: r["Salary"] + r["Bonus"], axis=1)

61. applymap()

Kab Use: Frame level ke har single structural block par clean functions lagane ho

employees.select_dtypes("number").applymap(lambda x: round(x, 2))

62. for loop

Kab Use: Multiple columns par sequence mein iterate karke null checks ya operation run karne ho

for col in employees.columns:
    print(f"{col}: {employees[col].isnull().sum()} nulls")

63. while loop

Kab Use: Jab jab tak condition True hai tab tak repetitive batch transformations chalane ho

i = 0
while i < len(employees):
if employees.loc[i, "Salary"] < 0: employees.loc[i, "Salary"] = 0
i += 1

64. iterrows()

Kab Use: Small datasets ke har row index aur record series par loop lagana ho

for index, row in employees.iterrows():
    print(row["Name"], row["Salary"])

65. itertuples()

Kab Use: Iterrows se fast speed me high-performance tuple row iteration karni ho

for row in employees.itertuples():
    print(row.Name, row.Salary)

66. List Comprehension

Kab Use: Short single-line syntax looping se Quick pythonic feature transformation create karna ho

employees["Status"] = ["Senior" if age > 40 else "Junior" for age in employees["Age"]]

67. groupby()

Kab Use: Categories ke base par dataset ko group karke summary metrics calculate karne ho

employees.groupby("Department")["Salary"].mean()

68. groupby().agg()

Kab Use: Multiple columns aur multiple functions ko ek sath summarize karna ho

employees.groupby("Department").agg({"Salary": ["mean", "sum"], "Age": "mean"})

69. groupby().transform()

Kab Use: Group-level aggregation result ko har original row ke dimension me Broadcast map karna ho

employees["Dept_Avg_Sal"] = employees.groupby("Department")["Salary"].transform("mean")

70. groupby().size() / count()

Kab Use: Category wise count (size for rows, count for valid non-null elements) check karna ho

employees.groupby("Department").size()

71. groupby().filter()

Kab Use: Group level aggregations condition lagakar whole groups drop ya keep karne ho

employees.groupby("Department").filter(lambda x: len(x) >= 5)

72. pd.merge()

Kab Use: SQL JOINs ki tarah do DataFrames ko Common key column ke base par merge karna ho

merged = pd.merge(employees, departments, on="Department", how="left")

73. df.join()

Kab Use: Index headers ke basis par Do DataFrames ko horizontal join karna ho

employees.
join(departments, how="left")

74. pd.concat()

Kab Use: DataFrames ko vertical rows (axis=0) ya horizontal columns (axis=1) apend/stack karna ho

combined = pd.concat([df1, df2], axis=0, ignore_index=True)

75. pd.cut()

Kab Use: Continuous statistical ranges ko fixed numeric buckets/bins mein badalna (e.g. Age Groups)

employees["Age_Group"] = pd.cut(employees["Age"], bins=[18, 30, 45, 60], labels=["Young", "Mid", "Senior"])

76. pd.qcut()

Kab Use: Data ko equal frequency quantiles/percentiles bins mein split karna

employees["Salary_Quartile"] = pd.qcut(employees["Salary"], q=4, labels=["Q1", "Q2", "Q3", "Q4"])

77. Label Encoding (via map)

Kab Use: Categorical text values ko numbers (0, 1, 2) me convert karna ML models ke liye

employees["Gender_Code"] = employees["Gender"].map({"Male": 0, "Female": 1})

78. One-Hot Encoding (pd.get_dummies)

Kab Use: Categorical text values ko separate 0 aur 1 ke binary columns mein badalna

dummies = pd.get_dummies(employees["Department"], prefix="Dept")
employees = pd.concat([employees, dummies], axis=1)

79. MinMax Scaling

Kab Use: Continuous numeric metrics ko 0 se 1 scale boundaries ke beech bounds me laana

from sklearn.preprocessing import MinMaxScaler
employees[["Scaled_Salary"]] = MinMaxScaler().fit_transform(employees[["Salary"]])

80. Standard Scaling

Kab Use: Numerical features ko Mean=0 aur Standard Deviation=1 scale standard transform karna

from sklearn.preprocessing import StandardScaler
employees[["Std_Salary"]] = StandardScaler().fit_transform(employees[["Salary"]])

81. Creating New Math Features

Kab Use: Existing attributes se domain specific math calculations karke naya feature banana

ecommerce["Total_Amount"] = ecommerce["Price"] * ecommerce["Quantity"]

82. head() / tail()

Kab Use: Dataset ki shuruat ki head rows ya last tail rows visual scan karni ho

employees.head(5)
employees.tail(5)

83. shape

Kab Use: Total Rows aur Total Columns ka exact count (matrix shape) nikalna ho

employees.shape

84. info()

Kab Use: Dataset memory consumption, data types aur non-null totals ka complete health check karna ho

employees.info()

85. dtypes

Kab Use: System Data types index array check karna features ka

employees.dtypes

86. columns

Kab Use: Dataset ke saare headers column names array fetch karni ho

employees.columns

87. index

Kab Use: Dataframe index boundaries range trace check karni ho

employees.index

88. sample()

Kab Use: Entire dataset mein se randomly N rows pull/sample karni ho

employees.sample(5)
employees.sample(frac=0.1)

89. describe()

Kab Use: Numeric columns ka mean, std, min, max, quartiles exact metrics scan karna ho

employees.describe()
employees.describe(include="object")

90. count()

Kab Use: Non-null population records total count exact number me dekhna ho

employees.count()

91. unique()

Kab Use: Categorical column me kaun kaun si distinct values exist karti hai unhe array dekhna ho

employees["Department"].unique()

92. nunique()

Kab Use: Column ki unique entries total metric count check karna ho

employees["Department"].nunique()

93. value_counts()

Kab Use: Categorical items Frequency Distribution (Kaun sa item kitni baar aaya) check karna ho

employees["Department"].value_counts()

94. value_counts(normalize=True) * 100

Kab Use: Categorical elements percentage share contribution metric check karna ho

employees["Department"].value_counts(normalize=True) * 100

95. mean() / median() / mode()

Kab Use: Central tendencies calculation (Average, Middle Value, Most Repeated Value) ke liye

employees["Salary"].mean()
employees["Salary"].median()
employees["Department"].mode()[0]

96. std() / var()

Kab Use: Mathematical Variance aur Standard Deviation metrics check karne numerical features ke

employees["Salary"].std()
employees["Salary"].var()

97. skew() / kurtosis()

Kab Use: Data curve distribution skewness (left/right asymmetry) aur kurtosis (sharp peak) measure karna ho

employees["Salary"].skew()
employees["Salary"].kurtosis()

98. corr()

Kab Use: Numerical features ka Linear Correlation Matrix (-1 to +1) evaluate karna ho

employees.corr(numeric_only=True)

99. cov()

Kab Use: Mathematical Covariance matrix calculate karne do numerical features ke

employees[["Salary", "Age"]].cov()

100. pd.crosstab()

Kab Use: Do categorical attributes ka Cross-tabulation count matrix generate karna ho

pd.crosstab(employees["Department"], employees["Gender"])

101. pivot_table()

Kab Use: Excel jaisa dynamic multi-dimensional aggregations summary sheet build karna ho

employees.pivot_table(values="Salary", index="Department", columns="Gender", aggfunc="mean")

102. nunique() == len(df)

Kab Use: Check karna ki kya koi column Absolute Unique Identifier (Primary Key) ban sakta hai

employees["Emp_ID"].nunique() == len(employees)

103. melt()

Kab Use: Wide-format tables ko long-format me Unpivot shape transform karne ke liye

pd.melt(employees, id_vars=["Name"], value_vars=["Salary", "Age"])

104. sort_values()

Kab Use: Dataframe rows ko specific columns ke basis par Ascending/Descending arrange karna ho

employees.sort_values("Salary", ascending=False)

105. reset_index()

Kab Use: Filtering ya sorting modification steps ke baad index ko sequential reset karna ho

employees.reset_index(drop=True, inplace=True)

106. set_index()

Kab Use: Kisi column feature ko Dataframe row header index index assign karna ho

employees.set_index("Name", inplace=True)

107. between()

Kab Use: Numerical boundary limits filtering ke under aane wale records pull karne ke liye

employees[employees["Age"].between(25, 40)]

108. nlargest() / nsmallest()

Kab Use: Dataset se Top N largest ya Bottom N smallest rows quickly fetch karni ho

employees.nlargest(5, "Salary")
employees.nsmallest(3, "Age")

109. where() / mask()

Kab Use: Condition matching: where() retains True values, mask() retains False condition values

employees["Salary"].
where(employees["Salary"] > 50000)
employees["Salary"].mask(employees["Salary"] > 50000)

110. rank()

Kab Use: Values ko numeric ranking assign karni ho (1st, 2nd, 3rd place position)

employees["Salary_Rank"] = employees["Salary"].rank(ascending=False)

111. cumsum() / cummax()

Kab Use: Sequential running cumulative sum totals ya cumulative maximum tracking metrics nikalna ho

ecommerce["Running_Total"] = ecommerce["Price"].cumsum()
ecommerce["Max_Price_So_Far"] = ecommerce["Price"].cummax()

112. pct_change()

Kab Use: Row to row percentage growth/drop shift calculation karni ho

ecommerce["Price_Growth_%"] = ecommerce["Price"].pct_change() * 100

113. shift()

Kab Use: Previous (+1) ya Next (-1) row records ko current alignment height row par reference karna ho

ecommerce["Prev_Price"] = ecommerce["Price"].shift(1)
ecommerce["Next_Price"] = ecommerce["Price"].shift(-1)

114. memory_usage()

Kab Use: System memory audit: column-wise byte allocation memory usage test karna

employees.memory_usage(deep=True)

115. T (Transpose)

Kab Use: Matrix layout rows ko columns me aur columns ko rows me flip rotate karna ho

employees.describe().T

116. pipe()

Kab Use: Modular function steps ko chain karke cleaner pipeline transformation flow banana ho

def clean_pipeline(df):
    df["Name"] = df["Name"].str.strip().str.title()
    return df

employees = employees.pipe(clean_pipeline)

117. min() / max() / sum()

Kab Use: Feature boundaries calculations: Minimum value, Maximum value aur Total Sum metrics

employees["Salary"].min()
employees["Salary"].max()
employees["Salary"].sum()

118. quantile()

Kab Use: Percentile distribution limits (25th, 50th, 75th percentile) extract karne ke liye

employees["Salary"].quantile([0.25, 0.50, 0.75])

119. filter() - Column Name Filtering

Kab Use: Specific column name patterns ke आधार par dataframe subset extract karne ke liye

employees.filter(like="Date")
employees.filter(regex="^Emp_")

120. reset_index(drop=True)

Kab Use: Data cleaning workflows complete karne ke baad messy index structure reset aur Drop karne ke liye

employees.reset_index(drop=True, inplace=True)