Q1: What is MultiIndex (Hierarchical Indexing) in Pandas?
Answer: MultiIndex creates multiple levels of index on rows or columns — enabling hierarchical data representation. Created using set_index() with multiple columns, or from groupby results. Access data using tuple indexing — df.loc[('IT','North')]. xs() method for cross-section selection. MultiIndex enables efficient querying of grouped data, represents hierarchical relationships (Country→State→City), and is the result of multi-level groupby operations.
🎯 Explain: MultiIndex = multiple levels ka index. df.set_index(['Dept','Region']) — Dept first level, Region second level. Ab df.loc['IT'] se saare IT employees, df.loc[('IT','North')] se IT+North employees directly access ho jaayenge. GroupBy ke result mein automatically MultiIndex banta hai. xs('North', level='Region') — sirf North wale sab departments. Hierarchical data representation ke liye perfect — Country→State→City structure. Interview mein "I use MultiIndex for hierarchical groupby results and efficient multi-level data querying."

# Create MultiIndex
df_multi = df.set_index(['Dept', 'Region'])

# Access by first level
print(df_multi.loc['IT'])          # All IT employees

# Access by both levels
print(df_multi.loc[('IT', 'North')])  # IT + North

# Cross-section — all depts in North
print(df_multi.xs('North', level='Region'))

# GroupBy creates MultiIndex
grouped = df.groupby(['Dept','Region'])['Salary'].mean()
print(grouped)                  # MultiIndex Series
print(grouped.reset_index())    # Back to flat DataFrame

Q2: What are Window Functions (Rolling, Expanding, EWM) in Pandas?
Answer: Window functions perform calculations across a sliding window of rows. Rolling(n) — fixed-size sliding window (e.g., 3-month moving average). Expanding() — cumulative window from start to current row (running total). EWM(span=n) — Exponentially Weighted Moving average (recent values weighted more). Key methods: mean(), sum(), std(), min(), max(). Window functions are essential for time-series analysis — trend smoothing, volatility calculation, cumulative metrics. Equivalent to SQL OVER(ROWS BETWEEN) window functions.
🎯 Explain: Rolling = sliding window — df['Salary'].rolling(3).mean() — har row pe last 3 values ka average. Expanding = cumulative — df['Sales'].expanding().sum() — running total. EWM = recent data ko zyada weight — stock prices mein use hota hai. Time series analysis mein bahut important — monthly sales trend smooth karo, volatility calculate karo, cumulative revenue track karo. SQL ke Window Functions jaisa concept hai. Interview mein "I use rolling for moving averages, expanding for cumulative metrics, and EWM for trend analysis with recency bias."

# Rolling — 3-period moving average
df['Rolling3Avg'] = df['Sales'].rolling(3).mean()

# Expanding — cumulative sum (running total)
df['CumSales'] = df['Sales'].expanding().sum()

# EWM — Exponentially Weighted Moving Average
df['EWM_Sales'] = df['Sales'].ewm(span=3).mean()

# Rolling with GroupBy — dept-wise moving avg
df['DeptRolling'] = df.groupby('Dept')['Sales'].transform(
    lambda x: x.rolling(2, min_periods=1).mean()
)

Q3: What is Method Chaining in Pandas?
Answer: Method Chaining is the practice of calling multiple Pandas methods sequentially on a DataFrame — each method returns a DataFrame, enabling the next method call. It creates readable, pipeline-style code without intermediate variables. Example: df.query('Salary > 60000').sort_values('Sales', ascending=False).head(5). Use assign() for adding columns in chains, query() for filtering, pipe() for custom functions. Method chaining makes data transformation pipelines clean, traceable, and maintainable.
🎯 Explain: Method Chaining = multiple operations ek line/block mein — bina intermediate variables ke. df.query('...').sort_values('...').head(5) — filter → sort → top 5 — ek chain mein. Purana tarika: df2 = df[df['Salary']>60000], df3 = df2.sort_values('Sales'), result = df3.head(5) — 3 variables. Chaining se: clean, readable, no extra memory. assign() se naye columns add karo chain mein. Interview mein "I write method chains for clean transformation pipelines — it makes code self-documenting."

# Method Chaining — clean pipeline
result = (
    df
    .query('Salary > 55000')
    .assign(
        Bonus=lambda x: x['Salary'] * 0.10,
        Total=lambda x: x['Salary'] + x['Sales']
    )
    .sort_values('Total', ascending=False)
    .head(5)
    [['Name', 'Dept', 'Salary', 'Bonus', 'Total']]
)
print(result)

Q4: What is the pipe() function in Pandas?
Answer: pipe() allows you to apply custom functions within a method chain — the DataFrame is passed as the first argument to the function. Syntax: df.pipe(function, args). It enables modular, reusable transformation functions that integrate cleanly into chains. Example: define clean_data(df), add_features(df), filter_outliers(df) — then chain: df.pipe(clean_data).pipe(add_features).pipe(filter_outliers). pipe() promotes functional programming style and makes complex pipelines modular and testable.
🎯 Explain: pipe() = custom functions ko method chain mein use karo. Ek function banao clean_data(df) jo DataFrame accept kare aur return kare — df.pipe(clean_data) se chain mein add ho jayega. Multiple steps: df.pipe(step1).pipe(step2).pipe(step3). Har step independently testable hai. ETL pipelines mein bahut useful — modular code. Interview mein "I use pipe() for modular data transformation pipelines — each step is a separate testable function."

# Define reusable pipeline functions
def clean_names(df):
    df.columns = df.columns.str.strip().str.lower()
    return df

def add_bonus(df, rate=0.10):
    df['bonus'] = df['salary'] * rate
    return df

def filter_high(df, threshold=60000):
    return df[df['salary'] > threshold]

# Chain with pipe
result = (df
    .pipe(clean_names)
    .pipe(add_bonus, rate=0.15)
    .pipe(filter_high, threshold=55000)
)

Q5: What is pd.crosstab() and how is it different from pivot_table()?
Answer: pd.crosstab() computes frequency tables (cross-tabulation) by default — counting occurrences of combinations. Syntax: pd.crosstab(df['Dept'], df['Region']). It can also compute aggregations with values and aggfunc parameters. Differences from pivot_table(): crosstab counts frequencies by default, pivot_table requires explicit aggfunc. crosstab takes Series as input, pivot_table takes DataFrame. crosstab supports margins (totals) and normalize (percentages). crosstab is best for frequency analysis, pivot_table for value aggregation.
🎯 Explain: crosstab = frequency table — Dept vs Region mein kitne employees hain. pd.crosstab(df['Dept'], df['Region']) — ek matrix ban jayegi count ke saath. normalize='index' se row percentages. margins=True se totals. pivot_table values aggregate karta hai (sum, mean), crosstab default mein count karta hai. Interview mein "crosstab for frequency analysis and proportions, pivot_table for value aggregation — both create matrix-style summaries."

# Frequency crosstab
print(pd.crosstab(df['Dept'], df['Region'], margins=True))

# Percentage crosstab
print(pd.crosstab(df['Dept'], df['Region'], normalize='index'))

# Crosstab with values (like pivot_table)
print(pd.crosstab(df['Dept'], df['Region'],
                  values=df['Salary'], aggfunc='mean'))

Q6: What is the query() method and how does it compare with boolean indexing?
Answer: query() filters DataFrame rows using a string expression — df.query('Salary > 60000 and Dept == "IT"'). Compared to boolean indexing — df[(df['Salary']>60000) & (df['Dept']=="IT")] — query is more readable, requires less typing, no need for parentheses around each condition, and supports @ prefix for external variables. Boolean indexing is more flexible — supports complex expressions and function calls. query() is preferred for simple readable filters, boolean indexing for complex logic.
🎯 Explain: query() = SQL WHERE jaisa — string mein condition likho. df.query('Salary > 60000 and Dept == "IT"') — clean aur readable. Boolean indexing: df[(df['Salary']>60000) & (df['Dept']=="IT")] — zyada parentheses, verbose. External variable: threshold = 60000, df.query('Salary > @threshold') — @ prefix se variable use karo. query() method chaining mein perfect fit karta hai. Interview mein "I use query() for readable filters in method chains and boolean indexing for complex conditional logic."

# Boolean indexing (verbose)
filtered = df[(df['Salary'] > 60000) & (df['Dept'] == 'IT')]

# query() — cleaner
filtered = df.query('Salary > 60000 and Dept == "IT"')

# query with external variable
min_sal = 60000
filtered = df.query('Salary > @min_sal')

# query in method chain
result = df.query('Sales > 70000').sort_values('Salary').head(3)

Q7: How do you work with DateTime in Pandas?
Answer: pd.to_datetime() converts strings to datetime objects. The .dt accessor provides date components — dt.year, dt.month, dt.day, dt.dayofweek, dt.quarter, dt.day_name(). Date arithmetic: subtract dates for timedelta, add pd.DateOffset() for shifting. pd.date_range() generates sequences. Resample() groups time-series by frequency. DatetimeIndex enables time-based slicing — df['2023-01':'2023-06']. DateTime handling is critical for time-series analysis, cohort analysis, and trend reporting.
🎯 Explain: pd.to_datetime(df['Date']) — string ko datetime banao. .dt accessor se components nikalo — df['JoinDate'].dt.year → 2021, .dt.month → 1, .dt.day_name() → "Friday". Date math: (pd.Timestamp.now() - df['JoinDate']).dt.days — tenure in days. resample('M').sum() — monthly aggregate. Date range: pd.date_range('2023-01-01', periods=12, freq='M'). Interview mein "I use .dt accessor for extracting date components and resample for time-series aggregation."

# Extract date components
df['JoinYear'] = df['JoinDate'].dt.year
df['JoinMonth'] = df['JoinDate'].dt.month
df['JoinDay'] = df['JoinDate'].dt.day_name()
df['JoinQtr'] = df['JoinDate'].dt.quarter

# Tenure in days
df['TenureDays'] = (pd.Timestamp.now() - df['JoinDate']).dt.days

# Filter by date range
recent = df[df['JoinDate'] >= '2021-01-01']

# Group by year
print(df.groupby(df['JoinDate'].dt.year)['EmpID'].count())

Q8: What is pd.Timedelta and pd.DateOffset?
Answer: pd.Timedelta represents a duration — difference between two dates. Created from subtraction (date2 - date1) or explicitly pd.Timedelta(days=30). Supports arithmetic — add/subtract from dates. pd.DateOffset represents calendar-aware date shifts — DateOffset(months=3) shifts by 3 calendar months (handles month-end). Key difference: Timedelta is fixed duration (30 days is always 30 days), DateOffset is calendar-aware (1 month from Jan 31 = Feb 28). DateOffset is preferred for business date calculations.
🎯 Explain: Timedelta = fixed duration — pd.Timedelta(days=30) hamesha 30 din. DateOffset = calendar-aware — pd.DateOffset(months=1) January se February le jayega correctly (28/29/30/31 handle karega). Timedelta: df['JoinDate'] + pd.Timedelta(days=90) — 90 din baad ki date. DateOffset: df['JoinDate'] + pd.DateOffset(months=6) — 6 months baad. Business mein DateOffset zyada accurate hai — "3 months probation" calculate karna ho toh DateOffset use karo. Interview mein "Timedelta for fixed durations, DateOffset for calendar-aware month/year shifts."

# Timedelta — fixed duration
df['After90Days'] = df['JoinDate'] + pd.Timedelta(days=90)

# DateOffset — calendar-aware
df['After6Months'] = df['JoinDate'] + pd.DateOffset(months=6)

# Business Day Offset
df['Next10BDays'] = df['JoinDate'] + pd.offsets.BDay(10)

Q9: What are Regular Expressions (Regex) and how are they used in Pandas?
Answer: Regular Expressions are pattern-matching strings used for searching, extracting, and replacing text. In Pandas, regex works through the .str accessor — str.contains(pattern), str.extract(pattern), str.replace(pattern, replacement), str.findall(pattern). Common patterns: \d+ (digits), [A-Za-z]+ (letters), \s (whitespace), ^ (start), $ (end), . (any char), * (0+), + (1+). The re module provides compile(), search(), match(), findall(). Regex is essential for parsing unstructured text data.
🎯 Explain: Regex = text patterns se data extract/validate karo. Email validate: r'^[\w.]+@[\w.]+\.\w+$'. Phone number extract: r'\d{10}'. Pandas mein: df['Name'].str.contains(r'^A') — A se start hone wale names. df['Phone'].str.extract(r'(\d{3})-(\d{7})') — area code aur number alag nikalo. Data cleaning mein bahut powerful — unstructured text parse karo, patterns dhundho, replace karo. Interview mein "I use regex with Pandas .str accessor for text extraction and validation — like extracting domains from emails or cleaning phone numbers."

import re

# Pandas str with regex
names_with_a = df[df['Name'].str.contains(r'^A', regex=True)]
# Names starting with 'A' — Aarav, Arjun

# Extract pattern
emails = pd.Series(['aarav@gmail.com', 'ishita@yahoo.in'])
domains = emails.str.extract(r'@([\w.]+)')
print(domains)  # gmail.com, yahoo.in

# Replace with regex
phones = pd.Series(['91-9876-543210', '91-1234-567890'])
clean = phones.str.replace(r'-', '', regex=True)
print(clean)   # 919876543210

# Python re module
text = "Order ID: ORD-2024-001, Amount: 55000"
order_id = re.search(r'ORD-\d{4}-\d{3}', text).group()
print(order_id)  # ORD-2024-001

Q10: How do you create date-based features for analysis?
Answer: Date-based feature engineering includes: (1) Extract components — year, month, day, quarter, weekday, week number. (2) Create flags — is_weekend, is_month_end, is_quarter_end. (3) Calculate duration — tenure days, age, days_since_last_purchase. (4) Time-based grouping — group by month/quarter for trend analysis. (5) Lag features — previous period values using shift(). (6) Cyclical encoding — sin/cos for month/hour to capture cyclic patterns. These features are critical for time-series models and cohort analysis.
🎯 Explain: Date features = datetime se useful columns banao analysis ke liye. Year, Month, Quarter — grouping ke liye. DayOfWeek — weekday vs weekend pattern. Tenure — employee retention analysis. is_weekend flag — sales pattern analysis. shift(1) — previous month value for MoM comparison. Cyclical encoding — January aur December close hain (1 aur 12 but actually neighbors) — sin/cos encoding se capture hota hai. Interview mein "I create date features like tenure, quarter, weekday flags, and lag values for time-series analysis."

# Date feature engineering
df['Year'] = df['JoinDate'].dt.year
df['Quarter'] = df['JoinDate'].dt.quarter
df['DayOfWeek'] = df['JoinDate'].dt.dayofweek
df['IsWeekend'] = df['DayOfWeek'].isin([5,6])

# Tenure calculation
df['TenureYears'] = ((pd.Timestamp.now() - df['JoinDate']).dt.days / 365.25).round(1)

# Lag feature — previous row's sales
df['PrevSales'] = df['Sales'].shift(1)
df['SalesChange'] = df['Sales'] - df['PrevSales']

Q11: What are common regex patterns used in data cleaning?
Answer: Common regex patterns: \d — digit [0-9]. \D — non-digit. \w — word character [a-zA-Z0-9_]. \s — whitespace. + — one or more. * — zero or more. ^ — start of string. $ — end of string. [] — character set. () — capture group. | — OR. Practical patterns: r'\d{10}' — 10-digit phone. r'^[A-Z]{5}\d{4}[A-Z]$' — PAN card format. r'[\w.]+@[\w.]+\.\w+' — email. r'\d{1,3}(,\d{3})*(\.\d+)?' — formatted numbers. r'[^\w\s]' — special characters for removal.
🎯 Explain: Regex patterns yaad rakho — interview mein practical use case pucha jaata hai. Phone number validation: r'^\d{10}$'. PAN card: r'^[A-Z]{5}\d{4}[A-Z]$'. Email: r'[\w.]+@[\w.]+\.\w+'. Special characters remove: df['Name'].str.replace(r'[^\w\s]', '', regex=True). Numbers extract: df['Text'].str.extract(r'(\d+)'). Interview mein 3-4 common patterns yaad rakho aur practical use case do.

Q12: How do you use pd.Grouper for time-based grouping?
Answer: pd.Grouper enables frequency-based grouping on datetime columns without setting them as index. Syntax: df.groupby(pd.Grouper(key='DateCol', freq='M')). Frequencies: 'D' daily, 'W' weekly, 'M' monthly, 'Q' quarterly, 'Y' yearly, 'B' business day. Can combine with other group columns: df.groupby(['Dept', pd.Grouper(key='JoinDate', freq='Y')]). This is essential for time-series aggregation — monthly sales, quarterly headcount, yearly revenue without manually extracting date parts.
🎯 Explain: pd.Grouper = datetime column pe directly frequency-based grouping. df.groupby(pd.Grouper(key='JoinDate', freq='Y'))['EmpID'].count() — yearly joiners count. 'M' se monthly, 'Q' se quarterly. Manually df['Year'] = df['JoinDate'].dt.year karne se better — cleaner aur flexible. Multiple group keys ke saath: ['Dept', pd.Grouper(key='Date', freq='Q')] — department + quarterly. Interview mein "I use pd.Grouper for time-series aggregation — cleaner than manually extracting date components."

# Yearly grouping with Grouper
yearly = df.groupby(pd.Grouper(key='JoinDate', freq='Y'))['EmpID'].count()
print(yearly)

# Dept + Quarterly grouping
dept_qtr = df.groupby([
    'Dept',
    pd.Grouper(key='JoinDate', freq='Q')
])['Salary'].mean().reset_index()

Q13: How do you optimize Pandas performance for large datasets?
Answer: Optimization strategies: (1) Use vectorized operations instead of loops — np.where over apply+lambda. (2) Use appropriate dtypes — category for repeated strings, int32 over int64, float32 over float64. (3) Read only needed columns — pd.read_csv(usecols=['col1','col2']). (4) Use chunking for large files — pd.read_csv(chunksize=10000). (5) Avoid iterrows() — use vectorized or apply instead. (6) Use query() over boolean indexing for complex filters. (7) Use eval() for complex column expressions. (8) Consider Polars or Dask for very large data.
🎯 Explain: Performance optimization ka core rule: loops avoid karo, vectorized operations use karo. apply() bhi slow hai — np.where() ya np.select() 10x faster. dtypes optimize karo — category type repeated strings ke liye 90% memory save karta hai. usecols se sirf zaroorat ke columns load karo. 10GB file? chunksize se pieces mein process karo. iterrows() sabse slow — kabhi use mat karo large data pe. Interview mein "I follow vectorization-first approach — np.where over apply, category dtype for strings, and chunked reading for large files."

# ❌ SLOW — iterrows
for idx, row in df.iterrows():
    df.loc[idx, 'Bonus'] = row['Salary'] * 0.10

# ✅ FAST — vectorized
df['Bonus'] = df['Salary'] * 0.10

# ❌ SLOW — apply with lambda
df['Level'] = df['Salary'].apply(lambda x: 'High' if x>60000 else 'Low')

# ✅ FAST — np.where
df['Level'] = np.where(df['Salary'] > 60000, 'High', 'Low')

# Load only needed columns
df = pd.read_csv('big_file.csv', usecols=['Name','Salary','Dept'])

# Chunked reading for large files
for chunk in pd.read_csv('huge.csv', chunksize=100000):
    process(chunk)

Q14: How do you reduce memory usage in Pandas?
Answer: Memory reduction techniques: (1) Downcast numeric types — pd.to_numeric(df['col'], downcast='integer') converts int64 to int8/16/32 as appropriate. (2) Use category dtype — df['Dept'].astype('category') for repeated strings reduces memory by 90%+. (3) Drop unnecessary columns early — df.drop(columns=[...]). (4) Read specific columns — usecols parameter. (5) Use sparse arrays for mostly-zero data. (6) Monitor with df.memory_usage(deep=True). (7) Use appropriate dtypes at read time — dtype parameter in read_csv().
🎯 Explain: Memory optimization = large datasets handle karne ke liye zaroori. df.memory_usage(deep=True) se check karo kitni memory use ho rahi hai. Category type sabse effective — "IT","HR","Finance" baar baar repeat hota hai, category mein ek baar store hota hai. int64 → int32 ya int16 downcast karo — half memory. float64 → float32. read_csv mein dtype specify karo: dtype={'EmpID':'int32', 'Dept':'category'}. Interview mein "I always check memory_usage and downcast dtypes for large datasets — category alone saves 90% on string columns."

# Check memory usage
print(df.memory_usage(deep=True))

# Category dtype — massive savings for strings
df['Dept'] = df['Dept'].astype('category')
df['Region'] = df['Region'].astype('category')

# Downcast numeric
df['Salary'] = pd.to_numeric(df['Salary'], downcast='integer')

# Optimized read_csv
df = pd.read_csv('data.csv',
    dtype={'Dept': 'category', 'Region': 'category'},
    usecols=['Name','Dept','Salary','Region'],
    parse_dates=['JoinDate']
)

print(df.memory_usage(deep=True))  # Compare — significantly less!

Q15: What is the difference between apply(), vectorized operations, and iterrows()?
Answer: Speed hierarchy (fastest to slowest): (1) Vectorized NumPy/Pandas operations — operate on entire arrays, C-level speed. (2) apply() with simple functions — Python-level but optimized for Series. (3) List comprehension — Python loop but faster than iterrows. (4) itertuples() — faster than iterrows, returns namedtuples. (5) iterrows() — slowest, creates Series for each row, high overhead. Rule: always try vectorized first, then apply, list comprehension as last resort, never iterrows for computation. 1M rows: vectorized ~10ms, apply ~500ms, iterrows ~30,000ms.
🎯 Explain: Performance order: Vectorized > apply > list comprehension > itertuples > iterrows. Vectorized = df['Salary'] * 0.10 — C level, fastest. apply = df['Salary'].apply(lambda x: x*0.10) — Python function call per row, slower. iterrows = for idx, row in df.iterrows() — sabse slow, har row pe Series banata hai. 1 million rows pe: vectorized 10ms, apply 500ms, iterrows 30 seconds — 3000x difference! Interview mein "I never use iterrows for computation — vectorized operations are 3000x faster on large datasets."

📊 Performance Comparison:

Q16: What is eval() in Pandas and when should you use it?
Answer: eval() evaluates string expressions on DataFrame columns efficiently — df.eval('Bonus = Salary * 0.10'). It uses less memory than standard operations because it avoids creating intermediate arrays. Supports arithmetic, comparison, and boolean operations. Use inplace=True to modify the DataFrame directly. eval() is faster than regular operations for complex multi-column expressions on large DataFrames. Best for: column calculations, multi-step expressions, reducing memory during intermediate computations.
🎯 Explain: eval() = string expression se column operations — memory efficient. df.eval('Total = Salary + Sales') — intermediate array nahi banta — direct evaluate hota hai. Large DataFrames (1M+ rows) pe standard df['Total'] = df['Salary'] + df['Sales'] mein temporary array banta hai — memory use. eval() se skip hota hai. Complex expressions: df.eval('Ratio = (Sales - Salary) / Sales * 100'). Interview mein "I use eval for complex column expressions on large datasets — it reduces memory overhead by avoiding intermediate arrays."

# Standard way — creates intermediate arrays
df['Total'] = df['Salary'] + df['Sales']

# eval() — memory efficient
df.eval('Total = Salary + Sales', inplace=True)
df.eval('Bonus = Salary * 0.10', inplace=True)
df.eval('Ratio = (Sales - Salary) / Sales * 100', inplace=True)

# Multiple operations in one eval
df = df.eval('''
    Tax = Salary * 0.30
    NetSalary = Salary - Tax
    SalesRatio = Sales / Salary
''')

Q17: How do you profile Python code for performance?
Answer: Profiling tools: (1) %timeit / %%timeit — Jupyter magic for timing code execution. (2) %time — single execution timing. (3) cProfile module — detailed function-level profiling. (4) line_profiler — line-by-line execution time. (5) memory_profiler — memory usage per line. (6) df.memory_usage(deep=True) — Pandas memory check. Best practice: profile first, optimize bottlenecks, measure improvement. Do not optimize prematurely — profile identifies the actual slow parts.
🎯 Explain: %timeit — Jupyter mein sabse easy. %timeit df['Salary'].apply(lambda x: x*0.10) vs %timeit df['Salary'] * 0.10 — direct comparison. cProfile — function-level detail — kaunsa function kitna time le raha hai. memory_profiler — kaunsi line kitni memory use kar rahi hai. Rule: pehle profile karo, phir optimize karo — andha optimization mat karo. Interview mein "I profile with %timeit in Jupyter for quick comparisons and cProfile for detailed function-level analysis before optimizing."

# Jupyter %timeit
%timeit df['Salary'] * 0.10          # ~50 µs
%timeit df['Salary'].apply(lambda x: x*0.10)  # ~500 µs — 10x slower!

# cProfile
import cProfile
cProfile.run('df.groupby("Dept")["Salary"].mean()')

# Memory check
print(df.memory_usage(deep=True).sum() / 1024**2, 'MB')

Q18: What alternatives exist for very large datasets that don't fit in memory?
Answer: Alternatives for large data: (1) Chunked processing — pd.read_csv(chunksize=N), process each chunk, concatenate results. (2) Dask — parallel Pandas with lazy evaluation, scales to clusters. (3) Polars — Rust-based DataFrame library, 10-100x faster than Pandas. (4) SQL databases — SQLite, PostgreSQL for data that exceeds RAM. (5) PySpark — distributed computing for truly massive datasets. (6) Parquet/Feather file formats — columnar storage, faster reads, smaller files. (7) Vaex — out-of-core DataFrames for billion-row datasets. Choose based on data size and infrastructure.
🎯 Explain: Pandas limit — RAM mein fit hona chahiye. 10GB file 8GB RAM mein nahi aayega. Solutions: Chunking — pieces mein process karo. Dask — Pandas jaisa API but parallel + larger than memory. Polars — new, Rust mein built, 50x faster. SQL — database mein process karo, result lo. PySpark — cluster pe distributed processing. Parquet format — CSV se 10x chhota, 10x fast read. Interview mein "For data up to 10GB I optimize Pandas with chunking and dtype optimization. Beyond that, I use Dask or Polars. For truly large data, PySpark."

Q19: How do you perform Rank and Percentile calculations in Pandas?
Answer: rank() assigns rank values to elements. Parameters: method='min' (same rank for ties, gap after), 'dense' (same rank, no gap), 'average' (mean rank for ties), 'first' (order of appearance). ascending=True (lowest=rank 1). pct=True returns percentile rank (0-1). GroupBy + rank enables department-wise ranking. quantile() returns value at specific percentile. cut() bins continuous data into categories. qcut() creates equal-frequency bins. These are essential for creating leaderboards, percentile reports, and data segmentation.
🎯 Explain: rank() = values ko rank do. df['SalaryRank'] = df['Salary'].rank(ascending=False) — highest salary = rank 1. method='dense' — ties ke baad gap nahi hoga (1,2,2,3). pct=True — percentile rank (0 to 1). GroupBy + rank — department ke andar ranking. cut() — salary ko brackets mein divide karo — "0-50k","50k-75k","75k+". qcut() — equal number of employees in each bin. Interview mein "I use rank with method='dense' for leaderboards and qcut for equal-frequency segmentation."

# Ranking
df['SalaryRank'] = df['Salary'].rank(ascending=False, method='dense')

# Percentile rank (0-1)
df['SalaryPct'] = df['Salary'].rank(pct=True)

# Department-wise rank
df['DeptRank'] = df.groupby('Dept')['Salary'].rank(ascending=False)

# Binning with cut (custom bins)
bins = [0, 50000, 65000, 80000, 100000]
labels = ['Junior', 'Mid', 'Senior', 'Executive']
df['SalaryBand'] = pd.cut(df['Salary'], bins=bins, labels=labels)

# Equal-frequency bins with qcut
df['SalaryQuartile'] = pd.qcut(df['Salary'], q=4, labels=['Q1','Q2','Q3','Q4'])

Q20: How do you perform Cohort Analysis in Python?
Answer: Cohort Analysis groups users by their start date (cohort) and tracks their behavior over time. Steps: (1) Define cohort — first purchase/join month for each user. (2) Calculate period number — months since cohort. (3) Create a cohort matrix — pivot table with cohort as rows, period as columns, metric as values. (4) Calculate retention rate — users in period N / users in period 0. Used for customer retention analysis, subscription churn tracking, and user engagement measurement. Essential for SaaS and e-commerce analytics.
🎯 Explain: Cohort Analysis = users ko joining month ke basis pe group karo aur time ke saath track karo. January mein 100 users aaye — 2nd month mein 80 active, 3rd mein 60 — retention rate decrease ho raha hai. Steps: JoinMonth nikalo, months_since calculate karo, pivot table banao. Retention matrix dikhao — heatmap se visualize karo. E-commerce, SaaS companies mein mandatory analysis hai. Interview mein "I perform cohort analysis for retention tracking — grouping users by acquisition month and measuring period-over-period retention rates."

# Cohort Analysis — simplified example

# Step 1: Define cohort (join month)
df['CohortMonth'] = df['JoinDate'].dt.to_period('M')

# Step 2: Calculate months since cohort
df['MonthsSince'] = (
    (pd.Timestamp.now().to_period('M') - df['CohortMonth'])
    .apply(lambda x: x.n)
)

# Step 3: Cohort summary
cohort = df.groupby('CohortMonth').agg(
    Headcount=('EmpID', 'count'),
    AvgSalary=('Salary', 'mean'),
    TotalSales=('Sales', 'sum')
)
print(cohort)

Q21: How do you perform Pareto Analysis (80/20 Rule) in Python?
Answer: Pareto Analysis identifies the vital few that contribute the most — typically 20% of items generating 80% of results. Steps: (1) Sort data by the metric descending. (2) Calculate cumulative sum. (3) Calculate cumulative percentage. (4) Identify the cutoff where cumulative reaches 80%. (5) Visualize with a combined bar + line chart (Pareto chart). Used in sales analysis (top products), customer analysis (top revenue customers), and issue prioritization (top complaint categories).
🎯 Explain: Pareto = 80/20 rule — 20% products 80% revenue generate karti hain. Steps: Sales descending sort karo, cumulative sum nikalo, cumulative percentage nikalo, 80% cutoff dhundho. Visualization: bar chart (individual values) + line chart (cumulative %) — dono ek chart mein. Top contributing items identify karo — focus resources yahan. Interview mein "I perform Pareto analysis to identify top 20% contributors — focusing business attention on the vital few that drive 80% of results."

# Pareto Analysis
pareto = (df
    .sort_values('Sales', ascending=False)
    .assign(
        CumSales=lambda x: x['Sales'].cumsum(),
        CumPct=lambda x: x['Sales'].cumsum() / x['Sales'].sum() * 100
    )
)
print(pareto[['Name','Sales','CumSales','CumPct']])

# Find 80% cutoff
top_contributors = pareto[pareto['CumPct'] <= 80]
print(f"Top {len(top_contributors)} employees contribute 80% of sales")

Q22: How do you perform RFM Analysis in Python?
Answer: RFM (Recency, Frequency, Monetary) segments customers based on purchase behavior. Recency — how recently they purchased. Frequency — how often they purchase. Monetary — how much they spend. Steps: (1) Calculate R, F, M for each customer. (2) Score each metric (1-5 using qcut). (3) Combine scores into RFM segment (e.g., "555" = best customer). (4) Create customer segments — Champions, Loyal, At Risk, Lost. RFM is the foundation of customer segmentation in marketing analytics and CRM.
🎯 Explain: RFM = customer segmentation ka gold standard. R = last purchase kitne din pehle (kam = better). F = kitni baar purchase kiya (zyada = better). M = total spend (zyada = better). qcut se 1-5 score do. "555" = Champion customer — recent, frequent, high spender. "111" = lost customer. E-commerce, retail, banking sab mein use hota hai. Interview mein "I implement RFM segmentation using qcut for scoring and create customer segments — Champions, Loyal, At Risk — for targeted marketing."

# RFM Analysis — simplified with employee data
rfm = df.assign(
    Recency=(pd.Timestamp.now() - df['JoinDate']).dt.days,
    Monetary=df['Sales']
)

# Score with qcut (1-4)
rfm['R_Score'] = pd.qcut(rfm['Recency'], q=4, labels=[4,3,2,1])  # Lower recency = higher score
rfm['M_Score'] = pd.qcut(rfm['Monetary'], q=4, labels=[1,2,3,4])

# Combine scores
rfm['RFM'] = rfm['R_Score'].astype(str) + rfm['M_Score'].astype(str)
print(rfm[['Name','Recency','Monetary','R_Score','M_Score','RFM']])

Q23: How do you handle JSON data in Python for analysis?
Answer: JSON handling: json module for basic parsing — json.loads() string to dict, json.dumps() dict to string. pd.read_json() loads JSON directly into DataFrame. pd.json_normalize() flattens nested JSON into a flat table — essential for API responses. For nested structures: pd.json_normalize(data, record_path='items', meta=['id','name']). requests library fetches JSON from APIs — response.json() returns Python dict. JSON is the standard format for API data, NoSQL databases, and web scraping results.
🎯 Explain: JSON = web APIs ka standard format. json.loads('{"name":"Aarav"}') → Python dict. pd.read_json('file.json') → DataFrame. Nested JSON tricky hota hai — pd.json_normalize() se flatten karo. API se data: import requests, r = requests.get(url), data = r.json(), df = pd.json_normalize(data). Real-world mein APIs se data fetch karke analysis karna common hai. Interview mein "I use pd.json_normalize for flattening nested API responses into analysis-ready DataFrames."

import json

# Parse JSON string
json_str = '[{"name":"Aarav","salary":55000},{"name":"Ishita","salary":72000}]'
data = json.loads(json_str)
df_json = pd.DataFrame(data)

# Nested JSON — flatten
nested = {
    "company": "DataCorp",
    "employees": [
        {"name": "Aarav", "dept": {"name": "IT", "floor": 3}},
        {"name": "Ishita", "dept": {"name": "HR", "floor": 2}}
    ]
}
flat_df = pd.json_normalize(nested['employees'])
print(flat_df)
# name  dept.name  dept.floor
# Aarav    IT          3

# API data fetch
# import requests
# r = requests.get('https://api.example.com/data')
# df = pd.json_normalize(r.json())

Q24: How do you export analysis results in Python?
Answer: Export methods: (1) CSV — df.to_csv('output.csv', index=False). (2) Excel — df.to_excel('output.xlsx', index=False, sheet_name='Report'). Multiple sheets: ExcelWriter context manager. (3) JSON — df.to_json('output.json', orient='records'). (4) SQL — df.to_sql('table', engine). (5) Parquet — df.to_parquet('output.parquet') — fast, compact. (6) Clipboard — df.to_clipboard() for quick paste. (7) HTML — df.to_html('table.html'). For formatted Excel reports, use openpyxl or xlsxwriter with conditional formatting, charts, and styling.
🎯 Explain: to_csv — sabse common, index=False zaroor lagao. to_excel — multiple sheets ke liye ExcelWriter use karo. to_parquet — large data ke liye fast aur compact — CSV se 10x chhota. to_sql — database mein directly write. to_clipboard — quick paste ke liye. Professional reports ke liye xlsxwriter engine use karo — formatting, charts sab add kar sakte ho Excel mein. Interview mein "I export to CSV for sharing, Parquet for storage efficiency, and formatted Excel with xlsxwriter for management reports."

# CSV export
df.to_csv('employees.csv', index=False)

# Excel — multiple sheets
with pd.ExcelWriter('report.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Data', index=False)
    summary.to_excel(writer, sheet_name='Summary', index=False)

# Parquet — fast & compact
df.to_parquet('data.parquet', index=False)

# Read back — much faster than CSV
df = pd.read_parquet('data.parquet')

Q25: How would you automate a daily/weekly report using Python?
Answer: Report automation pipeline: (1) Data extraction — pd.read_csv, pd.read_sql, API calls with requests. (2) Data transformation — cleaning, aggregation, feature creation with Pandas. (3) Report generation — formatted Excel with xlsxwriter, HTML reports, PDF with reportlab. (4) Scheduling — Windows Task Scheduler, cron jobs (Linux), or Python schedule library. (5) Email distribution — smtplib with MIME for attachments. (6) Logging — Python logging module for tracking execution. This eliminates hours of manual work and ensures consistency.
🎯 Explain: Report automation = manual report banana band karo, Python se automate karo. Steps: Data lo (CSV/SQL/API) → Clean karo (Pandas) → Summary banao (GroupBy, Pivot) → Excel mein formatted report banao (xlsxwriter) → Email se bhejo (smtplib) → Schedule karo (Task Scheduler). Logging add karo taaki errors track ho. Real-world mein bahut demand hai — "automate your MIS reports" common requirement. Interview mein "I automated a weekly MIS report that reduced 4 hours of manual work to a 5-minute scheduled script."

# Automated Report Pipeline — skeleton
import pandas as pd
import logging

logging.basicConfig(level=logging.INFO)

def generate_report():
    logging.info("Starting report generation...")
    
    # Step 1: Extract
    df = pd.read_csv('daily_data.csv')
    
    # Step 2: Transform
    summary = df.groupby('Dept').agg(
        Headcount=('EmpID','count'),
        AvgSalary=('Salary','mean'),
        TotalSales=('Sales','sum')
    ).reset_index()
    
    # Step 3: Export
    with pd.ExcelWriter('weekly_report.xlsx') as w:
        df.to_excel(w, sheet_name='Raw Data', index=False)
        summary.to_excel(w, sheet_name='Summary', index=False)
    
    logging.info("Report generated successfully!")

generate_report()

Q26: How do you handle multiple CSV files and combine them?
Answer: Use glob to find all files matching a pattern, read each with pd.read_csv, and combine with pd.concat. Steps: (1) import glob, os. (2) files = glob.glob('data/*.csv'). (3) Read all files into a list — [pd.read_csv(f) for f in files]. (4) Combine — pd.concat(dfs, ignore_index=True). For large files, use chunking or Dask. Add source file name as column — useful for tracking. Handle schema differences with error handling or schema validation before concat.
🎯 Explain: Monthly reports alag files mein hain — Jan.csv, Feb.csv, Mar.csv. glob.glob('data/*.csv') se sab files dhundho. List comprehension se sab read karo aur pd.concat se combine karo. Source filename add karo — pata rahe kaunsa data kaunsi file se aaya. Schema validation important hai — agar ek file mein extra column hai toh concat mein NaN aayega. Interview mein "I use glob + concat for combining monthly files with source tracking — and validate schema consistency before concatenation."

import glob
import os

# Find all CSV files
files = glob.glob('data/*.csv')
print(f"Found {len(files)} files")

# Read all and add source filename
dfs = []
for f in files:
    temp = pd.read_csv(f)
    temp['Source'] = os.path.basename(f)
    dfs.append(temp)

# Combine all
combined = pd.concat(dfs, ignore_index=True)
print(combined.shape)

# One-liner version
combined = pd.concat([pd.read_csv(f) for f in glob.glob('data/*.csv')], ignore_index=True)

Q27: How do you connect Python to SQL databases for analysis?
Answer: Python connects to SQL databases using: (1) sqlite3 — built-in module for SQLite. (2) sqlalchemy — universal database toolkit supporting MySQL, PostgreSQL, SQL Server. (3) pd.read_sql() — executes SQL query and returns DataFrame directly. (4) pd.read_sql_table() — reads entire table. (5) df.to_sql() — writes DataFrame to database table. Connection string format: 'dialect+driver://user:password@host/database'. For large queries, use chunksize parameter. SQLAlchemy create_engine() is the standard approach.
🎯 Explain: Python + SQL = powerful combination. sqlalchemy se engine banao → pd.read_sql('SELECT * FROM employees', engine) → DataFrame directly mil jayega. SQL query database pe execute hoga, result Python mein aayega — best of both worlds. df.to_sql('table', engine) se Python se database mein data push karo. ETL pipelines mein bahut use hota hai — SQL se extract, Python mein transform, SQL mein load. Interview mein "I use SQLAlchemy with pd.read_sql for database integration — SQL for extraction, Pandas for transformation."

import sqlite3
from sqlalchemy import create_engine

# SQLite — simple local database
conn = sqlite3.connect('company.db')
df = pd.read_sql('SELECT * FROM employees WHERE salary > 60000', conn)

# SQLAlchemy — production databases
engine = create_engine('sqlite:///company.db')
# engine = create_engine('mysql://user:pass@host/db')  # MySQL
# engine = create_engine('postgresql://user:pass@host/db')  # PostgreSQL

df = pd.read_sql('SELECT * FROM employees', engine)

# Write DataFrame to SQL table
df.to_sql('employees_clean', engine, if_exists='replace', index=False)

Q28: What is your complete EDA (Exploratory Data Analysis) workflow?
Answer: My EDA workflow: (1) Load data — pd.read_csv with parse_dates. (2) First look — head(), shape, info(), describe(). (3) Missing values — isnull().sum(), percentage, visualize with heatmap. (4) Data types — check dtypes, convert as needed. (5) Duplicates — duplicated().sum(), investigate, handle. (6) Univariate analysis — histograms, value_counts for each column. (7) Bivariate analysis — scatter plots, correlation heatmap. (8) Outliers — box plots, IQR method. (9) Feature engineering — create derived columns. (10) Summary — key findings documented with charts.
🎯 Explain: EDA = data ko samajhna before modeling. Load → First Look → Missing → Types → Duplicates → Univariate → Bivariate → Outliers → Features → Summary. Har step pe visualization karo — patterns visually dikhte hain jo numbers mein miss ho sakte hain. 80% analysis time EDA mein jaata hai — achi EDA = accurate results. Interview mein yeh 10-step workflow confidently batao — structured approach dikhata hai.

# Complete EDA Template
def quick_eda(df):
    print("="*50)
    print(f"Shape: {df.shape}")
    print(f"\nData Types:\n{df.dtypes}")
    print(f"\nMissing Values:\n{df.isnull().sum()}")
    print(f"\nMissing %:\n{(df.isnull().sum()/len(df)*100).round(2)}")
    print(f"\nDuplicates: {df.duplicated().sum()}")
    print(f"\nNumeric Summary:\n{df.describe()}")
    print(f"\nMemory: {df.memory_usage(deep=True).sum()/1024**2:.2f} MB")
    print("="*50)

quick_eda(df)

Q29: How do you create a reusable data cleaning function?
Answer: A reusable cleaning function encapsulates common steps: clean column names, handle missing values, fix data types, remove duplicates, standardize text, handle outliers. Accept parameters for customization — threshold percentages, fill strategies. Return the cleaned DataFrame. Document with docstrings. Store in a utility module (.py file) and import across projects. This ensures consistency — every dataset goes through the same quality checks. Version control with git.
🎯 Explain: Reusable function banao — har project mein same cleaning steps repeat mat karo. clean_data(df) function mein sab steps encapsulate karo — column names clean, missing handle, types fix, duplicates remove. Parameters se customize karo — missing threshold, fill strategy. Ek utils.py file mein save karo — from utils import clean_data. Har project mein consistent cleaning. Interview mein "I maintain a reusable cleaning module with documented functions — ensures consistency across all my analysis projects."

# Reusable cleaning function
def clean_data(df, drop_threshold=0.5, num_strategy='median', cat_strategy='Unknown'):
    """Clean DataFrame with standard steps."""
    df = df.copy()
    
    # 1. Clean column names
    df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')
    
    # 2. Drop columns with >50% missing
    missing_pct = df.isnull().sum() / len(df)
    drop_cols = missing_pct[missing_pct > drop_threshold].index
    df = df.drop(columns=drop_cols)
    
    # 3. Fill missing — numeric with median, categorical with string
    for col in df.select_dtypes(include='number').columns:
        df[col].fillna(df[col].median(), inplace=True)
    for col in df.select_dtypes(include='object').columns:
        df[col].fillna(cat_strategy, inplace=True)
    
    # 4. Remove duplicates
    df = df.drop_duplicates()
    
    # 5. Standardize text columns
    for col in df.select_dtypes(include='object').columns:
        df[col] = df[col].str.strip()
    
    return df

# Usage
df_clean = clean_data(df, drop_threshold=0.4)

Q30: Describe a complex Python data analysis project you have worked on.
Answer: Use STAR method — Situation, Task, Action, Result. Example: Situation — "Company had sales data scattered across 24 monthly Excel files with inconsistent formats, no automated reporting, and management needed weekly insights." Task — "Build an automated analysis pipeline with interactive visualizations." Action — "I used glob to combine 24 files, Pandas for cleaning and transformation, created reusable cleaning functions, implemented RFM segmentation for customer analysis, built Pareto analysis for product focus, automated weekly Excel report generation with xlsxwriter, and created Seaborn dashboards for trend visualization." Result — "Reduced reporting time from 6 hours to 15 minutes, identified top 15% products driving 78% revenue, customer segments improved targeting by 40%."
🎯 Explain: STAR method use karo — structured answer do. (S) Problem kya tha — scattered data, manual reporting. (T) Role kya tha — automate aur analyze. (A) Kya kiya — glob+concat, cleaning pipeline, RFM, Pareto, xlsxwriter reports, Seaborn charts. (R) Result — time saved (6hr → 15min), revenue insight (15% products = 78% revenue), targeting improved (40%). Numbers do — "24 files", "15 minutes", "78% revenue". Bina numbers ke answer weak lagta hai. Agar real project nahi hai toh practice project ka scenario structured way mein batao — honest raho but prepared raho.