Python Complete Formula & Function List

Built-in basics se lekar Pandas data cleaning, aggregation, statistics, business metrics aur visualization tak — har function ka syntax, example aur real output. Complete data cleaning pipeline template ke saath. Data Analyst interview + job ready cheat sheet.

📑 Is Article Mein Kya Hai:

• 📚 PART 1 — Python Built-in Basics (chote level)
• 📊 PART 2 — Import & Data Loading
• 🧹 PART 3 — DATA CLEANING (sabse important part)
• 🔍 PART 4 — Filtering & Selection (SQL ka WHERE)
• ➕ PART 5 — New Column Banana
• 📈 PART 6 — Aggregation (SQL ka GROUP BY)
• 🧮 PART 7 — Statistics
• ⏱️ PART 8 — Time Series / Rolling (SQL window function ka Python)
• 🔗 PART 9 — Join / Merge (SQL ka JOIN)
• 📦 PART 10 — NumPy Essentials
• 🚨 PART 11 — Outlier Detection (Analytics ka core)
• 🎯 PART 12 — Business Metrics (Interview gold)
• 📊 PART 13 — Visualization (Matplotlib + Seaborn)
• 🔮 PART 14 — Basic ML (scikit-learn) — bonus for interviews
• ⚙️ PART 15 — Performance & Pro Tips
• 🧹 PART 16 — Complete Data Cleaning Pipeline (copy-paste template)
• 🔁 PART 17 — SQL → Pandas Mapping (tumhare liye sabse useful)
• 🎤 PART 18 — Interview One-Liners (Python)
• ✅ Revision Priority (Interview se 1 raat pehle)

1.1 Print & Type

print("Hello Jatin")                  # output dikhane ke liye
type(x)                               # data type batata hai
isinstance(x, (int, float))           # True/False — type check

1.2 Type Conversion (Type Casting) ⭐

int("25")          # 25      → string se number
float("25.5")      # 25.5
str(25)            # "25"    → number se text
bool(0)            # False   (0, "", None, [] = False)
list("abc")        # ['a','b','c']

1.3 Math Built-ins ⭐

1.4 String Methods ⭐

s = "  Jatin Kumar  "

s.strip()              # "Jatin Kumar"     → extra space hatana
s.lower()              # "  jatin kumar  "
s.upper()              # "  JATIN KUMAR  "
s.title()              # "  Jatin Kumar  "
s.replace("Kumar","K") # "  Jatin K  "
s.split(" ")           # ['', '', 'Jatin', 'Kumar', '', '']
"jatin".capitalize()   # "Jatin"
"abc123".isdigit()     # False
"123".isdigit()        # True
"a,b,c".split(",")     # ['a','b','c']
"-".join(["a","b"])    # "a-b"
"Jatin"[0:3]           # "Jat"  (slicing)
len(s)                 # 16

1.5 f-string (formatted output) ⭐

name, sales = "Jatin", 15150.5678
print(f"Name: {name}, Sales: {sales:,.2f}")
# Name: Jatin, Sales: 15,150.57

print(f"Growth: {0.183:.1%}")     # Growth: 18.3%
print(f"{'SKU':<8}{'Qty':>6}")    # padding — report format

1.6 List Comprehension ⭐ (loop ka smart tarika)

nums = [1, 2, 3, 4, 5]

[x * 2 for x in nums]                      # [2, 4, 6, 8, 10]
[x for x in nums if x > 2]                 # [3, 4, 5]
["Even" if x % 2 == 0 else "Odd" for x in nums]
                                           # ['Odd','Even','Odd','Even','Odd']

1.7 Dictionary & Zip ⭐

d = {"W1": "North", "W2": "South"}
d["W1"]                  # "North"
d.get("W9", "Unknown")   # "Unknown" — error nahi dega (BEST practice)
d.keys(); d.values(); d.items()

wh = ["W1","W2"]; city = ["Gurugram","Bengaluru"]
dict(zip(wh, city))      # {'W1': 'Gurugram', 'W2': 'Bengaluru'}

1.8 Conditions & Loops

# if / elif / else
if rate > 15:
    print("Critical")
elif rate > 10:
    print("Warning")
else:
    print("Safe")

# for loop
for row in data:
    print(row)

# while loop
while stock <= reorder_point:
    stock += 100

# try / except — error handling ⭐
try:
    value = int(user_input)
except ValueError:
    value = 0            # bad data se crash nahi hoga

1.9 Functions ⭐

def otd_rate(on_time, total):
    """On-Time Delivery percentage calculate karta hai."""
    if total == 0:
        return 0
    return round(on_time / total * 100, 2)

otd_rate(82, 100)        # 82.0

# lambda — one-line function
square = lambda x: x ** 2
square(5)                # 25

2.1 Standard Imports (har notebook me yahi likhoge) ⭐

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

2.2 Data Load Karna ⭐

# CSV
df = pd.read_csv("sales.csv")
df = pd.read_csv("sales.csv", encoding="latin-1")       # encoding error fix
df = pd.read_csv("sales.csv", parse_dates=["order_date"])  # date auto-convert ⭐
df = pd.read_csv("big.csv", nrows=1000)                 # sirf sample dekhna ho
df = pd.read_csv("big.csv", usecols=["order_id","units"])  # selected columns

# Excel
df = pd.read_excel("sales.xlsx", sheet_name="Sheet1")
df = pd.read_excel("sales.xlsx", sheet_name=None)       # saari sheets (dict milta hai)

# MySQL se directly ⭐ (interview me poochte hain)
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://user:password@localhost:3306/dbname")
df = pd.read_sql("SELECT * FROM orders LIMIT 1000", engine)

# JSON / Clipboard
df = pd.read_json("data.json")
df = pd.read_clipboard()          # Excel se copy-paste direct ⭐

# Save karna ⭐
df.to_csv("clean_sales.csv", index=False)               # index=False MUST ⭐
df.to_excel("output.xlsx", index=False, sheet_name="Clean")

2.3 Data ka Pehla Look ⭐

df.shape              # (8, 9) → rows, columns
df.head(5)            # pehli 5 rows
df.tail(5)            # last 5 rows
df.sample(5)          # random 5 rows
df.columns            # column names ki list
df.columns.tolist()   # list me
df.dtypes             # har column ka data type
df.info()             # full summary — rows, dtypes, non-null count ⭐
df.describe()         # numeric ka stats — mean, std, min, max, quartiles ⭐
df.describe(include="all")   # text columns bhi include
df.memory_usage(deep=True)   # memory kitni le raha hai
df["units"].unique()  # unique values
df["category"].nunique()     # unique values ka count

df.describe() ka real output (verified):

            units  unit_price  sales_amount
count    8.000000    8.000000      8.000000
mean    25.000000  891.25000  15150.000000
std     17.960274  493.57117   5394.706400
min      8.000000  180.00000  10000.000000
25%     11.500000  662.50000  11700.000000
50%     20.000000  875.00000  12750.000000
75%     32.500000 1262.50000  18050.000000
max     60.000000 1500.00000  25500.000000

3.1 Missing Values (NaN) — Detect ⭐

df.isnull().sum()                      # har column me kitne null ⭐
df.isnull().sum().sum()                # total null
df.isnull().mean() * 100               # % null (round karke dekho)
round(df.isnull().mean() * 100, 2)
df.isna().sum()                        # isnull() ka hi alias
df.notnull().sum()                     # non-null count
df[df["units"].isnull()]               # null wali rows dikhao
df.dropna().shape                      # null hata ke kitni rows bachi

3.2 Missing Values — Fill Karna ⭐

# Constant value se
df["units"].fillna(0, inplace=True)

# Mean se (numeric, normal distribution ho) ⭐
df["units"].fillna(df["units"].mean(), inplace=True)

# Median se (numeric, outliers ho) ⭐ BEST
df["units"].fillna(df["units"].median(), inplace=True)

# Mode se (categorical) ⭐
df["category"].fillna(df["category"].mode()[0], inplace=True)

# Forward / Backward fill (time series) ⭐
df["units"].ffill()        # pehla valid value aage bharo
df["units"].bfill()        # ulta

# Interpolate (linear gap fill) ⭐
df["units"].interpolate()

# Row hi drop karo
df.dropna(subset=["units"], inplace=True)        # sirf units null wali rows hatao
df.dropna(how="all", inplace=True)               # poori row null ho tabhi hatao
df.dropna(thresh=5, inplace=True)                # kam se kam 5 non-null chahiye

# Saare columns ek saath fill
df.fillna({"units": 0, "category": "Unknown"}, inplace=True)

# Column hi drop (zyada null ho)
df.drop(columns=["junk_col"], inplace=True)
df = df.loc[:, df.isnull().mean() < 0.4]         # 40%+ null wale columns auto-drop ⭐

3.3 Duplicates ⭐

df.duplicated().sum()                              # total duplicate rows
df.duplicated(subset=["order_id"]).sum()           # specific column pe check
df[df.duplicated(subset=["order_id"], keep=False)] # saari duplicate rows dikhao
df.drop_duplicates(inplace=True)                   # duplicates hatao
df.drop_duplicates(subset=["order_id"], keep="first", inplace=True)  ⭐
df.drop_duplicates(subset=["order_id"], keep="last", inplace=True)

3.4 Data Type Convert Karna ⭐

df["units"] = df["units"].astype(int)              # direct convert
df["order_date"] = pd.to_datetime(df["order_date"])            ⭐ date convert
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")  # bad dates → NaT ⭐
df["units"] = pd.to_numeric(df["units"], errors="coerce")      # bad numbers → NaN ⭐

# Text me "1,200" ya "₹1,200" ho toh ⭐
df["price"] = df["price"].str.replace(",", "", regex=False)
df["price"] = df["price"].str.replace("₹", "", regex=False)
df["price"] = pd.to_numeric(df["price"], errors="coerce")

df = df.convert_dtypes()          # auto best dtypes
df.info()                         # verify karo

3.5 Column Rename / Reorder / Drop ⭐

df.rename(columns={"qty": "units"}, inplace=True)
df.rename(columns=str.lower, inplace=True)                 # saare lowercase
df.columns = [c.strip().lower().replace(" ", "_") for c in df.columns]  # clean names ⭐

df = df[["order_id","units","sales_amount"]]               # reorder / select
df.drop(columns=["junk"], inplace=True)
df.drop(index=[0,1], inplace=True)                         # rows drop
df.reset_index(drop=True, inplace=True)                    # index reset ⭐
df.set_index("order_id", inplace=True)                     # index set

3.6 Text / String Cleaning (.str accessor) ⭐

s = pd.Series(["  Jatin Kumar ", "RAHUL-verma", "amit.sharma@GMAIL.com", None])

s.str.strip()                          # leading/trailing space hatana ⭐
s.str.lower()                          # lowercase
s.str.upper()                          # UPPERCASE
s.str.title()                          # Title Case
s.str.capitalize()
s.str.replace("-", " ", regex=False)   # replace ⭐
s.str.replace(r"\s+", " ", regex=True) # multiple spaces → 1 space ⭐
s.str.split("@", expand=True)          # split into 2 columns
s.str.extract(r"(\w+)@")               # regex se extract ⭐
s.str.contains("gmail", case=False, na=False)   # filter ⭐
s.str.startswith("121")                # pincode filter ⭐
s.str.endswith(".com")
s.str.len()                            # character count
s.str.slice(0, 3)                      # substring
s.str.zfill(6)                         # "121" → "000121" (pincode fix) ⭐
s.str.cat(sep="-")                     # sab jod do

3.7 Date Cleaning ⭐

df["order_date"] = pd.to_datetime(df["order_date"], format="%d-%m-%Y", errors="coerce")

df["order_date"].dt.year                 # 2024
df["order_date"].dt.month                # 1
df["order_date"].dt.day                  # 5
df["order_date"].dt.day_name()           # "Friday"
df["order_date"].dt.quarter              # 1
df["order_date"].dt.week                 # week number
df["order_date"].dt.to_period("M")       # 2024-01  (month grouping) ⭐
df["order_date"].dt.to_period("Q")       # 2024Q1
df["order_date"].dt.strftime("%b-%Y")    # "Jan-2024"
df["order_date"].dt.date                 # date only
df["order_date"].dt.normalize()          # time ko 00:00 kar do

# Date difference ⭐
(df["actual_delivery_date"] - df["promised_delivery_date"]).dt.days
(pd.Timestamp.today() - df["order_date"]).dt.days

# Date filter (sargable style) ⭐
df[(df["order_date"] >= "2024-01-01") & (df["order_date"] < "2025-01-01")]

# Month start / end
df["order_date"].dt.to_period("M").dt.start_time

3.8 Categorical Cleanup ⭐

# Case/spacing inconsistency: "Electronics ", "electronics", "ELECTRONICS"
df["category"] = df["category"].str.strip().str.title()

# Value mapping
mapping = {"M": "Male", "F": "Female", "male": "Male"}
df["gender"] = df["gender"].map(mapping)

# Category dtype (memory + speed) ⭐
df["category"] = df["category"].astype("category")

# Value counts se validate
df["category"].value_counts()

3.9 Value Cap / Binning ⭐

df["sales_amount"].clip(lower=0, upper=100000)     # outlier cap karna ⭐

pd.cut(df["units"], bins=[0, 10, 30, 100],
       labels=["Low", "Mid", "High"])               # custom bins ⭐

pd.qcut(df["sales_amount"], q=4,
        labels=["Q1","Q2","Q3","Q4"])               # quartile bins ⭐

# Single condition ⭐
df[df["delivery_status"] == "Delayed"]

# Multiple conditions — & (AND), | (OR), ~ (NOT) ⭐
df[(df["units"] > 10) & (df["category"] == "Electronics")]
df[(df["category"] == "Fashion") | (df["category"] == "Grocery")]
df[~(df["delivery_status"] == "On-Time")]

# IN clause
df[df["category"].isin(["Fashion","Grocery"])]

# NOT IN
df[~df["category"].isin(["Grocery"])]

# BETWEEN
df[df["units"].between(10, 30)]

# NULL / NOT NULL
df[df["units"].isnull()]
df[df["units"].notnull()]

# Text filter
df[df["customer_pincode"].str.startswith("121")]
df[df["customer_pincode"].str.contains("121", na=False)]

# query() — SQL jaisa syntax ⭐
df.query("units > 10 and category == 'Electronics'")
df.query("delivery_status == 'Delayed' and units > 20")

# Column select
df["units"]                          # Series
df[["order_id","units"]]             # DataFrame ⭐
df.loc[0:5, ["order_id","units"]]    # label based ⭐
df.iloc[0:5, 0:3]                    # position based ⭐
df.loc[df["units"] > 20, "flag"] = "Bulk"    # condition pe naya column ⭐

# Simple math ⭐
df["sales_amount"] = df["units"] * df["unit_price"]

# np.where — if/else vectorized ⭐ (sabse zyada use hota hai)
df["status"] = np.where(df["units"] > 15, "Bulk", "Retail")

# Nested condition
df["grade"] = np.where(df["sales_amount"] > 20000, "A",
              np.where(df["sales_amount"] > 12000, "B", "C"))

# np.select — multiple conditions (cleaner than nested) ⭐
conditions = [df["sales_amount"] > 20000, df["sales_amount"] > 12000]
choices    = ["A", "B"]
df["grade"] = np.select(conditions, choices, default="C")

# apply — custom function ⭐
df["tier"] = df["sales_amount"].apply(lambda x: "High" if x > 15000 else "Low")

# map — dictionary se value ⭐
mapping = {"W1": "North", "W2": "South", "W3": "West"}
df["region"] = df["warehouse_id"].map(mapping)

# replace
df["delivery_status"].replace({"Delayed": "Late", "On-Time": "OT"})

# Row-wise apply (axis=1)
df["tat"] = df.apply(lambda r: (r["actual"] - r["promised"]).days, axis=1)

# Full custom function
def risk_flag(row):
    if row["days_of_stock"] <= row["lead_time_days"]:
        return "Critical"
    return "Safe"

df["flag"] = df.apply(risk_flag, axis=1)

# Single aggregate ⭐
df.groupby("warehouse_id")["sales_amount"].sum()

# Multiple aggregates ⭐
df.groupby("warehouse_id")["sales_amount"].agg(["sum","mean","count","max","min"])

# Custom names ⭐
df.groupby("warehouse_id")["sales_amount"].agg(
    total_sales = "sum",
    avg_sales   = "mean",
    orders      = "count",
    max_order   = "max"
).reset_index()

# Multiple columns
df.groupby("warehouse_id").agg(
    total_sales = ("sales_amount", "sum"),
    total_units = ("units", "sum"),
    avg_price   = ("unit_price", "mean")
).reset_index()

# Multi-level group ⭐
df.groupby(["category","warehouse_id"])["sales_amount"].sum().reset_index()

# groupby + custom function
df.groupby("warehouse_id")["delivery_status"].apply(
    lambda x: round((x == "Delayed").mean() * 100, 2)
)
# warehouse_id
# W1    66.67
# W2    33.33
# W3     0.00

# Pivot table ⭐ (Excel pivot ka Python version)
df.pivot_table(index="category", columns="warehouse_id",
               values="sales_amount", aggfunc="sum", fill_value=0)

df.pivot_table(index="category", values="sales_amount",
               aggfunc=["sum","mean","count"])

# Crosstab (frequency table)
pd.crosstab(df["category"], df["delivery_status"], margins=True)

# Top N per group ⭐
df.groupby("category")["sales_amount"].nlargest(1)
df.sort_values("sales_amount", ascending=False).groupby("category").head(2)

# Transform — group value ko har row pe ⭐ (window function jaisa)
df["wh_total"] = df.groupby("warehouse_id")["sales_amount"].transform("sum")
df["pct_of_wh"] = df["sales_amount"] / df["wh_total"] * 100

# Central tendency ⭐
df["units"].mean()          # average
df["units"].median()        # middle value
df["units"].mode()          # most frequent
df["units"].sum()
df["units"].count()         # non-null count
len(df)                     # total rows

# Spread ⭐
df["units"].std()           # standard deviation
df["units"].var()           # variance
df["units"].min(); df["units"].max()
df["units"].quantile(0.25)  # Q1
df["units"].quantile(0.75)  # Q3
df["units"].quantile([0.25, 0.5, 0.75])
df["units"].sem()           # standard error

# Correlation ⭐
df[["units","unit_price","sales_amount"]].corr()              # Pearson
df[["units","unit_price"]].corr(method="spearman")            # rank based

# Value counts ⭐
df["category"].value_counts()
df["category"].value_counts(normalize=True) * 100             # percentage
df["category"].value_counts(dropna=False)                     # null bhi count

# Ranking ⭐
df["sales_amount"].rank(ascending=False)
df["sales_amount"].rank(method="dense", ascending=False)      # DENSE_RANK
df["sales_amount"].rank(method="min", ascending=False)        # RANK

# Cumulative ⭐
df["sales_amount"].cumsum()
df["sales_amount"].cummax()
df["sales_amount"].cumprod()

# Top / Bottom N ⭐
df.nlargest(5, "sales_amount")
df.nsmallest(5, "sales_amount")

df = df.sort_values("order_date")            # pehle sort karna MUST ⭐

df["sales_amount"].rolling(3).mean()         # 3-row rolling average ⭐
df["sales_amount"].rolling(7).sum()          # rolling total
df["sales_amount"].rolling(3, min_periods=1).mean()   # kam data pe bhi chale
df["sales_amount"].rolling(3).median()       # outliers ho toh MEDIAN better ⭐

df["sales_amount"].expanding().sum()         # cumulative running total
df["sales_amount"].expanding().mean()        # running average

df["sales_amount"].shift(1)                  # previous row value ⭐
df["sales_amount"].diff()                    # current - previous ⭐
df["sales_amount"].pct_change()              # growth rate (decimal) ⭐
df["sales_amount"].pct_change() * 100        # growth %

# Month-over-month growth ⭐
monthly = df.set_index("order_date")["sales_amount"].resample("ME").sum()
monthly.pct_change() * 100

# Resample (daily → monthly / weekly / quarterly) ⭐
df.set_index("order_date")["sales_amount"].resample("ME").sum()    # month-end
df.set_index("order_date")["sales_amount"].resample("W").mean()    # week
df.set_index("order_date")["sales_amount"].resample("QE").sum()    # quarter-end

# Date range generate
pd.date_range("2024-01-01", periods=12, freq="ME")

# Merge ⭐
pd.merge(df, wh, on="warehouse_id", how="left")      # LEFT JOIN
pd.merge(df, wh, on="warehouse_id", how="inner")     # INNER JOIN
pd.merge(df, wh, on="warehouse_id", how="outer")     # FULL OUTER
pd.merge(df, wh, on="warehouse_id", how="right")     # RIGHT JOIN

# Different column names
pd.merge(df, wh, left_on="wh_id", right_on="warehouse_id", how="left")

# Method style
df.merge(wh, on="warehouse_id", how="left")

# Multiple keys
pd.merge(df, wh, on=["warehouse_id","city"], how="left")

# Column name conflict
pd.merge(df, wh, on="warehouse_id", how="left", suffixes=("_orders","_wh"))

# Validate (data quality check) ⭐
pd.merge(df, wh, on="warehouse_id", how="left", validate="m:1")

# Concat — stack karna ⭐
pd.concat([df1, df2], ignore_index=True)             # rows stack (UNION ALL)
pd.concat([df1, df2], axis=1)                        # columns side by side

# Set operations
pd.concat([df1, df2]).drop_duplicates()              # UNION
np.intersect1d(df1["order_id"], df2["order_id"])     # common values
np.setdiff1d(df1["order_id"], df2["order_id"])       # sirf df1 me

import numpy as np

arr = np.array([10, 25, 8, 40, 15])

# Creation
np.array([1,2,3])
np.zeros(5)                    # [0,0,0,0,0]
np.ones(5)                     # [1,1,1,1,1]
np.arange(0, 10, 2)            # [0,2,4,6,8]
np.linspace(0, 1, 5)           # 5 evenly spaced
np.random.randint(1, 100, 5)   # random numbers
np.random.normal(50, 10, 1000) # normal distribution ⭐

# Math (vectorized — no loop) ⭐
arr * 2          # [20, 50, 16, 80, 30]
arr + 5
arr ** 2
np.sqrt(arr)
np.log(arr)
np.exp(arr)

# Stats ⭐
np.mean(arr); np.median(arr); np.std(arr); np.var(arr)
np.min(arr); np.max(arr); np.sum(arr); np.cumsum(arr)
np.percentile(arr, 75)
np.round(arr, 2)
np.abs(arr)
np.sort(arr)
np.argsort(arr)          # sort hone ke baad ke positions

# Conditions ⭐
np.where(arr > 15, "High", "Low")     # if/else ⭐
np.select([arr > 30, arr > 15], ["A","B"], default="C")
arr[arr > 15]                          # filter
np.clip(arr, 10, 30)                   # cap values

# Missing ⭐
np.nan
np.isnan(arr)
np.nanmean(arr)          # NaN ignore karke mean ⭐
np.nanmedian(arr)
np.nanstd(arr)

# Set ops
np.unique(arr)
np.intersect1d([1,2,3], [2,3,4])   # [2,3]
np.setdiff1d([1,2,3], [2,3,4])     # [1]

# Matrix
np.array([[1,2],[3,4]]).T          # transpose
np.dot(a, b)                       # matrix multiply

11.1 IQR Method ⭐ (sabse common)

Q1 = df["sales_amount"].quantile(0.25)
Q3 = df["sales_amount"].quantile(0.75)
IQR = Q3 - Q1

lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR

outliers = df[(df["sales_amount"] < lower) | (df["sales_amount"] > upper)]
print(f"Outliers: {len(outliers)}")

# Cap karna (drop nahi) ⭐
df["sales_amount"] = df["sales_amount"].clip(lower, upper)

11.2 Z-Score Method ⭐

z = (df["sales_amount"] - df["sales_amount"].mean()) / df["sales_amount"].std()
outliers = df[abs(z) > 3]          # 3 sigma rule

11.3 Boxplot se visually ⭐

import matplotlib.pyplot as plt
df.boxplot(column="sales_amount", by="category")
plt.show()

import seaborn as sns
sns.boxplot(data=df, x="category", y="sales_amount")

12.1 On-Time Delivery % ⭐

total = len(df)
ontime = (df["delivery_status"] == "On-Time").sum()
otd_pct = round(ontime / total * 100, 2)
# 62.5

# Group-wise
df.groupby("warehouse_id")["delivery_status"].apply(
    lambda x: round((x == "On-Time").mean() * 100, 2)
)

12.2 Delayed / SLA Breach Rate ⭐

df["sla_breach"] = np.where(
    df["actual_delivery_date"] > df["promised_delivery_date"], 1, 0
)
breach_rate = round(df["sla_breach"].mean() * 100, 2)          # 37.5

# Hub-wise top 5 (minimum 500 shipments — warna chhote hub 100% breach dikha denge) ⭐
(df.groupby("hub_id")["sla_breach"]
   .agg(breach_rate="mean", shipments="count")
   .assign(breach_rate=lambda x: (x["breach_rate"] * 100).round(2))
   .query("shipments >= 500")
   .sort_values("breach_rate", ascending=False)
   .head(5))

Verified output (min filter 2 rakha demo ke liye):

        breach_rate  shipments
hub_id
H1            66.67          3
H2            33.33          3
H3             0.00          2

12.3 Return Rate / RTO % ⭐

df["is_returned"] = (df["returned_status"] == "Returned").astype(int)
return_rate = round(df["is_returned"].mean() * 100, 2)

# COD vs Prepaid split
df.groupby("payment_mode")["is_returned"].mean() * 100

12.4 Days of Stock / Reorder Point ⭐

df["days_of_stock"]  = df["current_stock"] / df["daily_avg_sales"]
df["reorder_point"]  = df["daily_avg_sales"] * df["lead_time_days"]

df["reorder_status"] = np.where(
    df["days_of_stock"] <= df["lead_time_days"],
    "Critical - Reorder Now", "Safe"
)

Verified output:

12.5 Safety Stock & EOQ ⭐

import numpy as np

# Safety Stock = Z × σ(demand) × √lead_time
z_score = 1.65                      # 95% service level
sigma_demand = df["units"].std()
lead_time = 7
safety_stock = z_score * sigma_demand * np.sqrt(lead_time)

# EOQ = sqrt(2 × D × S / H)
D = df["units"].sum()               # annual demand
S = 500                             # ordering cost per order
H = 50                              # holding cost per unit
eoq = np.sqrt((2 * D * S) / H)

12.6 Inventory Turnover / DOH ⭐

cogs = 1200000
avg_inventory = 300000
inventory_turnover = cogs / avg_inventory          # 4.0
days_of_inventory  = 365 / inventory_turnover      # 91.25 days

12.7 Fill Rate / OTIF ⭐

fill_rate = round((df["units_delivered"].sum() / df["units_ordered"].sum()) * 100, 2)
otif = round(((df["on_time"] == 1) & (df["complete"] == 1)).mean() * 100, 2)

12.8 ABC Analysis (Pareto) ⭐

abc = (df.groupby("sku_id")["sales_amount"].sum()
         .sort_values(ascending=False)
         .reset_index())
abc["cum_pct"] = abc["sales_amount"].cumsum() / abc["sales_amount"].sum() * 100
abc["class"] = np.where(abc["cum_pct"] <= 80, "A",
               np.where(abc["cum_pct"] <= 95, "B", "C"))

12.9 Growth & CAGR ⭐

# MoM growth
mom = (current - previous) / previous * 100

# CAGR
cagr = ((end_value / start_value) ** (1 / years) - 1) * 100

12.10 Fraud / Anomaly Flags ⭐

df = df.sort_values(["card_number","transaction_time"])

df["prev_txn_time"]   = df.groupby("card_number")["transaction_time"].shift(1)
df["mins_since_prev"] = (df["transaction_time"] - df["prev_txn_time"]).dt.total_seconds() / 60
df["avg_card_spend"]  = df.groupby("card_number")["transaction_amount"].transform("mean")

df["risk_flag"] = np.where(
    (df["mins_since_prev"] <= 10) & (df["transaction_amount"] > 3 * df["avg_card_spend"]),
    "High Risk", "Normal"
)

Verified demo output (5 normal txns + 1 spike, sab 11 minute ke andar):

Card testing detection (baar-baar failed txns) — verified:

df = df.sort_values(["card_number","transaction_time"])

df["fail_count_roll"] = (df.groupby("card_number")["txn_status"]
                           .transform(lambda x: (x == "Failed").rolling(5).sum().values))

df["card_testing_flag"] = np.where(df["fail_count_roll"] >= 4, "Card Testing Suspect", "OK")

Off-hour anomaly (2 AM – 4 AM):

df["txn_hour"] = df["transaction_time"].dt.hour
df["off_hour_flag"] = np.where(df["txn_hour"].between(2, 4), "Off-Hour", "Normal")

import matplotlib.pyplot as plt
import seaborn as sns

# --- Matplotlib basics ---
plt.figure(figsize=(10, 6))                       # size set ⭐
plt.plot(df["order_date"], df["sales_amount"])    # line
plt.bar(df["category"], df["units"])              # bar
plt.hist(df["sales_amount"], bins=20)             # histogram
plt.scatter(df["units"], df["unit_price"])        # scatter
plt.boxplot(df["sales_amount"])                   # boxplot
plt.pie([60, 40], labels=["On-Time","Delayed"], autopct="%1.1f%%")

plt.title("Monthly Sales")                        # title ⭐
plt.xlabel("Month"); plt.ylabel("Sales (₹)")
plt.legend(["Sales"])
plt.xticks(rotation=45)
plt.grid(True)
plt.tight_layout()
plt.savefig("chart.png", dpi=150, bbox_inches="tight")   # save ⭐
plt.show()

# --- Seaborn (better looking, less code) ⭐
sns.set_theme(style="whitegrid")

sns.barplot(data=df, x="category", y="sales_amount")
sns.lineplot(data=df, x="order_date", y="sales_amount", hue="warehouse_id")
sns.boxplot(data=df, x="category", y="sales_amount")
sns.histplot(df["sales_amount"], kde=True)              # distribution
sns.scatterplot(data=df, x="units", y="unit_price", hue="category")
sns.heatmap(df[["units","unit_price","sales_amount"]].corr(), annot=True, cmap="coolwarm")
sns.countplot(data=df, x="delivery_status")
sns.pairplot(df[["units","unit_price","sales_amount"]])

# Subplots
fig, axes = plt.subplots(1, 2, figsize=(14, 5))
sns.barplot(data=df, x="category", y="sales_amount", ax=axes[0])
sns.boxplot(data=df, x="warehouse_id", y="units", ax=axes[1])
plt.show()

from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression, LogisticRegression
from sklearn.preprocessing import StandardScaler, LabelEncoder
from sklearn.metrics import (accuracy_score, confusion_matrix,
                             classification_report, mean_absolute_error, r2_score)

X = df[["units","unit_price"]]
y = df["sales_amount"]

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)

model = LinearRegression()
model.fit(X_train, y_train)
pred = model.predict(X_test)

print(r2_score(y_test, pred))
print(mean_absolute_error(y_test, pred))

# Fraud classification
le = LabelEncoder()
y_encoded = le.fit_transform(df["is_fraud"])

clf = LogisticRegression()
clf.fit(X_train, y_train)
pred = clf.predict(X_test)

print(confusion_matrix(y_test, pred))
print(classification_report(y_test, pred))

# Memory optimize ⭐
df["category"] = df["category"].astype("category")
df["units"] = df["units"].astype("int32")

# Chunk read (big file) ⭐
for chunk in pd.read_csv("huge.csv", chunksize=100000):
    process(chunk)

# Vectorization > loop ⭐  (yeh interview me bolna)
# ❌ Slow
for i in range(len(df)):
    df.loc[i, "total"] = df.loc[i, "units"] * df.loc[i, "price"]

# ✅ Fast (100x faster)
df["total"] = df["units"] * df["price"]

# inplace=True use karo ya reassign karo
df.dropna(inplace=True)         # ya
df = df.dropna()

# Options for display
pd.set_option("display.max_columns", None)
pd.set_option("display.max_rows", 100)
pd.set_option("display.float_format", "{:,.2f}".format)

# Settings pe warning ignore
import warnings
warnings.filterwarnings("ignore")

import pandas as pd
import numpy as np

def clean_dataset(df):
    """Standard data cleaning pipeline — har project me use karo."""
    df = df.copy()

    # 1. Column names clean  ("Order ID" -> "order_id")
    df.columns = [str(c).strip().lower().replace(" ", "_") for c in df.columns]

    # 2. Duplicates hatao
    before = len(df)
    df = df.drop_duplicates()
    print(f"Duplicates removed: {before - len(df)}")

    # 3. Date columns ko datetime me convert
    date_cols = [c for c in df.columns if "date" in c or "time" in c]
    for col in date_cols:
        df[col] = pd.to_datetime(df[col], errors="coerce")

    # 4. Text me chhupe numbers ko numeric banao  ("1,200" -> 1200)
    for col in df.select_dtypes(include=["object"]).columns:
        sample = df[col].dropna().astype(str).str.strip()
        if len(sample) and sample.str.fullmatch(r"[\d,]+(\.\d+)?").all():
            df[col] = pd.to_numeric(sample.str.replace(",", "", regex=False), errors="coerce")
            print(f"Converted '{col}' to numeric")

    # 5. Text normalize + blank ko NaN banao
    for col in df.select_dtypes(include=["object"]).columns:
        df[col] = (df[col].astype(str).str.strip()
                     .replace({"nan": np.nan, "None": np.nan, "": np.nan}))

    # 6. Missing values handle
    for col in list(df.columns):
        null_pct = df[col].isnull().mean()
        if null_pct > 0.4:                                   # 40%+ null = column drop
            print(f"Dropped '{col}' ({null_pct:.0%} null)")
            df = df.drop(columns=[col])
        elif pd.api.types.is_numeric_dtype(df[col]):         # numeric -> median
            df[col] = df[col].fillna(df[col].median())
        else:                                                # text -> mode
            mode_val = df[col].mode()
            df[col] = df[col].fillna(mode_val[0] if len(mode_val) else "Unknown")

    # 7. Outliers cap (IQR method)
    for col in df.select_dtypes(include=["float64", "int64"]).columns:
        Q1, Q3 = df[col].quantile([0.25, 0.75])
        IQR = Q3 - Q1
        df[col] = df[col].clip(Q1 - 1.5 * IQR, Q3 + 1.5 * IQR)

    # 8. Index reset
    df = df.reset_index(drop=True)
    print(f"Clean shape: {df.shape}")
    return df

df = clean_dataset(pd.read_csv("sales.csv"))

Verified run (dirty data → clean):

--- input (ganda data) ---
 Order ID   Units  unit_price      city note
      101    10.0       1,200 Gurugram    ok
      101    10.0       1,200 Gurugram    ok     <- duplicate
      102     NaN         800     Delhi         <- missing + blank
      103     8.0       1,500                   <- missing

--- pipeline output ---
Duplicates removed: 1
Converted 'unit_price' to numeric
Dropped 'note' (67% null)
Clean shape: (3, 4)

 order_id  units  unit_price     city
      101   10.0        1200 Gurugram
      102    9.0         800    Delhi     <- median se fill hua
      103    8.0        1500    Delhi     <- mode se fill hua

order_id      int64
units       float64
unit_price    int64      <- pehle text tha, ab numeric
city         object

SQL aata hai toh pandas 2 din me pakad me aa jayega — bas yeh table yaad kar lo:

Pandas ka ek bada advantage jo SQL me nahi: df.info(), df.describe(), .corr(), aur plotting — data profiling SQL me bahut mehnat ka kaam hai, pandas me 2 line ka.

📝 Practice karne ke liye — runnable script

Is file ke snippets ko directly run karke dekhna ho toh yeh script use karo:

_verify/verify_all_snippets.py      # python3 _verify/verify_all_snippets.py

Isme sample sales / inventory / transactions data bana hua hai — copy karke apne Jupyter notebook me paste karo aur har function ka output khud dekho. Ratta mat maaro — run karke dekho, tabhi yaad rahega.

Tier 1 — MUST (yeh 25 aana hi chahiye): read_csv · head · info · describe · shape · isnull().sum() · fillna · drop_duplicates · astype · to_datetime · value_counts · groupby().agg() · merge · sort_values · np.where · rename · drop · loc/iloc · rolling · pct_change · quantile · corr · apply · to_csv(index=False) · pivot_table

Tier 2 — Strong impression: clip · qcut/cut · transform · nlargest · resample · str.extract · errors="coerce" · validate="m:1" · query() · np.select · ffill/interpolate

Tier 3 — Differentiator: ABC analysis · Safety Stock/EOQ · chunked read · category dtype · Polars/Dask mention

Har snippet Python 3.13 + pandas 2.2.3 pe run karke verify kiya gaya hai.

Python Analytics Cheat Sheet — Complete!

Python 3.13, pandas 2.2.3 aur numpy 2.3.5 pe har snippet actually run karke verify kiya gaya hai. Output real hai — andaza nahi.

Happy Learning & Keep Exploring! 🚀