Exploratory Data Analysis is the systematic process of investigating and validating a dataset to understand its structure, content, and quality before modelling or reporting. Core activities include examining schema and data types, computing descriptive statistics such as counts, means, medians, standard deviations and quartiles, and measuring class balance and unique value counts. It covers distribution analysis, outlier detection, correlation and relationship exploration, and trend or seasonality checks for time series. Data validation and quality checks include identifying missing values, anomalies, inconsistent encodings, duplicates, and other data integrity issues. Practical techniques span SQL profiling and aggregation queries using GROUP BY, COUNT and DISTINCT; interactive data exploration with pandas and similar libraries; and visualization with histograms, box plots, scatter plots, heatmaps and time series charts to reveal patterns and issues. The process also includes feature summary and basic metric computation, sampling strategies, forming and documenting hypotheses, and recommending cleaning or transformation steps. Good Exploratory Data Analysis produces a clear record of findings, assumptions to validate, and next steps for cleaning, feature engineering, or modelling.
HardTechnical
61 practiced
Outline algorithms and trade-offs for detecting multivariate anomalies in datasets with mixed data types (numeric and categorical) without labeled anomalies. Compare isolation forest, autoencoders, and statistical-distance based methods for use during EDA, and explain how you would validate and prioritize flagged anomalies for investigation.
Sample Answer
Approach summary- For EDA with unlabeled, mixed-type multivariate data, use a tiered strategy: fast, interpretable statistical-distance methods for broad screening; tree-based methods (Isolation Forest) for robust, scaleable detection on mixed types; and representation methods (autoencoders) for capturing complex joint patterns when enough data exists. Combine anomaly scores, explainability, and business-context validation to prioritize investigations.Algorithms and trade-offs1) Isolation Forest (tree‑based)- How: builds random partitioning trees; anomalies require fewer splits → higher anomaly score.- Pros: handles numeric + categorical (natively if categories hashed or by using categorical splits), scalable, few hyperparameters, robust to irrelevant features, fast for EDA.- Cons: less sensitive to subtle density changes or correlated multivariate shifts; limited interpretability on why a point is anomalous beyond path length.2) Autoencoders (neural)- How: learn compressed representation; high reconstruction error signals anomaly.- Pros: captures complex nonlinear interactions, good for high-dim numeric data.- Cons: needs numeric inputs (categoricals require embeddings/one-hot), sensitive to hyperparameters, risk of overfitting to anomalies if contamination high, less interpretable, slower to iterate—better for deeper analysis beyond EDA.3) Statistical-distance based (Mahalanobis, Gower, density/LOF)- How: Mahalanobis for Gaussian-like numeric data; Gower distance for mixed data; LOF for local density anomalies.- Pros: interpretable (distance contributions by feature), useful for small datasets and explainable EDA; Gower preserves mixed-type semantics.- Cons: Mahalanobis assumes unimodal Gaussian; LOF scales poorly and needs careful choice of neighborhood k; distance methods sensitive to scaling and correlated features.Handling mixed types- Prefer Gower distance or tree-based methods for mixed data in initial EDA.- If using models requiring numeric input, transform categoricals thoughtfully: target/impact encoding or learned embeddings (avoid high-cardinality one-hot blowup).- Preserve interpretability: keep original categorical labels alongside encoded features for explanation.Validation and prioritization workflow1) Score ensemble: run 2–3 complementary detectors (e.g., Isolation Forest + Gower-LOF + autoencoder) and compute a consensus score (rank aggregation) to reduce method-specific false positives.2) Explain: compute per-feature contributions (e.g., SHAP for tree models; reconstruction error by feature for autoencoders; Mahalanobis components) to surface likely root causes.3) Validate with proxies: - Temporal holdout: check if flagged points persist or cluster in time. - Business rules: cross-check against known events (deploys, billing changes). - Backfill outcomes: sample flagged vs. non-flagged and label via domain experts to estimate precision/TPR.4) Prioritize using a scoring rubric: - Business impact (revenue, compliance, user experience) - Novelty/severity (distance/consensus score) - Repeatability (isolated vs. recurring cluster) - Confidence (agreement across methods + explainability)5) Investigation steps: triage high-priority items with root-cause features, inspect raw records, check upstream ETL and external events, create tickets with reproducible queries and suggested mitigations.Best practices for EDA- Start simple: run Gower-based nearest-neighbor or Isolation Forest to surface candidates quickly.- Visualize: pairwise plots, PCA/UMAP colored by anomaly score, time-series overlays.- Iteratively refine: incorporate domain filters, remove obvious data issues (nulls, duplicates), retrain.- Build feedback loop: capture analyst labels to tune thresholds and calibrate models; track true/false positives to prioritize future detection.This balanced approach yields fast, interpretable EDA signals (Isolation Forest / Gower), with autoencoders reserved for complex patterns; validation relies on ensemble agreement, feature-level explanations, temporal checks and business-impact prioritization.
EasyTechnical
124 practiced
Describe step-by-step how to create a pivot table in Excel to show monthly revenue per product category from columns: date, product_category, revenue. Explain how to add a computed field to calculate month-over-month percentage change and how to use conditional formatting to highlight decreases greater than 10%. Mention any limitations to be aware of when using Excel for repeated EDA.
Sample Answer
Situation: You have columns date, product_category, revenue and need monthly revenue per category plus month-over-month (MoM) % change and highlighting for >10% decreases.Step-by-step:1. Convert data to a Table (select range → Insert → Table). This makes refresh easier.2. Insert PivotTable: Insert → PivotTable → choose Table and place on new sheet.3. Build the pivot: - Drag product_category to Rows. - Drag date to Columns. Right-click any date in the pivot → Group → select Months (and Years if multi-year). - Drag revenue to Values and ensure it’s set to Sum (Value Field Settings → Sum).4. Add MoM % change: - Click the Sum of revenue field in Values → Value Field Settings → Show Values As → choose "% Difference From". - For Base Field choose the grouped date field (Month or the Year-Month group) and Base Item choose "(Previous)". This shows month-over-month percent change. - Optionally, rename the field label to "MoM % Change".5. Conditional formatting to highlight decreases >10%: - Select the MoM % Change cells in the pivot (click first → SHIFT+click last). - Home → Conditional Formatting → New Rule → Use a formula: =CELL< -0.1 (Excel will record relative addresses; ensure rule applies to the selected range). - Set formatting (fill red, bold). Alternatively use Format only cells that contain → Cell Value → less than → -0.10 and choose format.6. Refresh workflow: When source updates, right-click pivot → Refresh. If table resized, pivot will include new rows automatically.Limitations and notes:- Pivot calculated fields cannot compute previous-row differences; use "Show Values As" for % Difference. For more complex measures use Power Pivot / DAX or Power Query.- Grouping by month requires true date types; text dates must be converted.- Pivot formatting and conditional rules can break or misalign when pivot layout changes—reapply or use rules scoped to the pivot table range.- Large datasets may be slow; consider Power Query / data model for repeated EDA and automation.- Pivot cache is static until refresh; schedule refresh or automate with macros/Power Automate for recurring reports.
HardSystem Design
57 practiced
Design an automated EDA pipeline that runs after each daily ETL job to produce an interactive HTML or dashboard-friendly profile. Describe components including data ingestion, sampling or streaming profiling, summary statistics computation, anomaly detection, visualization generation, storage of EDA artifacts, and notification. Discuss choices for orchestration, reproducibility, and how to surface explainable findings to business users.
Sample Answer
Requirements & constraints:- Run after daily ETL, produce interactive HTML/dashboard artifacts for analysts and business users within SLA (e.g., 1 hour).- Provide summary stats, distributions, data quality checks, anomaly detection, lineage, reproducibility, and easy drill-down/explanations.High-level architecture:1. Orchestration: Airflow DAG triggered on ETL completion (or event from ETL job). Tasks run in containers (Docker) for reproducibility.2. Ingestion: Read from the trusted analytics table (or read snapshot/exported parquet). For large tables use columnar sample or incremental partition read.3. Sampling / streaming profiling: - If table small (<10M rows): full-pass profiling. - If large: stratified sample (partition/time-based) + reservoir sampling for unbiased sampling. - For near-real-time streams, run windowed profiling (last 24h) with incremental metrics stores.4. Summary statistics & data quality: - Compute count, nulls, uniques, percentiles, cardinality, histograms, correlations, type inference, and freshness. - Run Great Expectations suites for schema, ranges, referential integrity.5. Anomaly detection: - Rule-based first (sudden null% spike, cardinality changes, KPI drift). - Statistical/time-series methods: z-score on aggregates, EWMA for mean/volume, and simple isolation-forest or CBT for multivariate drift. - Score anomalies and attach evidence (metric, historical trend, p-value/confidence).6. Visualization generation: - Generate interactive HTML report using a library (pandas-profiling / ydata-profiling) or custom dashboard fragments (Plotly + Dash / Vega-lite exports). - Produce summary JSON/CSV for BI ingestion (Tableau/PowerBI). - Include top-level summary, per-column cards, anomaly timeline, and sample rows showing offending records.7. Storage of artifacts: - Store HTML reports and JSON metadata in object storage (S3/GCS) with path: /reports/{table}/{date}/{run_id}/. - Persist metrics and anomalies to a metrics DB (TimescaleDB / BigQuery / Postgres) for trend queries and downstream alerting. - Record provenance: ETL job id, data snapshot hash, code version, DAG run id.8. Notification & consumption: - On critical anomalies, send alerts to Slack/email with summary and direct link to report. - For routine runs, publish a daily digest to stakeholders and update a central dashboard in BI that links to artifacts.9. Reproducibility & governance: - Containerized tasks, version-controlled code and config in Git, immutable DAGs. - Store sample snapshots or dataset hashes; keep schema and expectation versions. - Use infra-as-code for deployments; register reports in a catalog (Data Catalog / Amundsen) with lineage.10. Explainable findings for business users: - Convert technical anomalies into business language: e.g., "Orders table: daily order count down 18% vs 7-day avg — driven by region X (sample rows attached)." - Provide root-cause hints: correlated metric drops, changed schema, upstream null increase. - Attach confidence and suggested actions (check upstream feed, inspect transformation, contact owner). - Provide interactive drilldowns: filter by date, region, customer segment; sample records with before/after transforms.Trade-offs:- Full profiling gives completeness but higher cost; sampling + incremental metrics balances cost and timeliness.- Simple anomaly detectors are explainable; complex ML models improve detection but need monitoring and explanations.This pipeline enables daily, reproducible EDA artifacts with explainable insights tailored to both analysts and business stakeholders.
HardTechnical
68 practiced
Customer lifetime value (CLV) analysis is affected by truncated purchase histories because older transactions are missing. During EDA, propose methods to quantify bias introduced by truncation, estimate correction factors or bounds, and explain how you would communicate the resulting uncertainty and assumptions to product stakeholders.
Sample Answer
Approach summary — quantify bias, estimate corrections/bounds, and communicate uncertainty in EDA using survival/censoring methods, sensitivity checks, and clear visuals.1) Quantify bias- Measure truncation: compute fraction of customers with first/last observed purchase near dataset start/end (histogram of customer tenure and cohort survival).- Compare observed per-cohort average spend vs. later cohorts (cohort heatmap). Systematic declines near edges indicate truncation.- Use Kaplan–Meier survival estimator to estimate probability a customer would have made a purchase beyond the cutoff; compare observed recency distribution to expected from full-history cohorts.2) Estimate correction factors / bounds- Nonparametric bound: use KM to compute probability mass beyond cutoff and scale observed CLV by 1 / (1 - KM_tail) as an upper-bound adjustment.- IPCW (inverse probability of censoring weighting): weight uncensored customers by 1 / P(not censored | covariates) to reweight CLV estimates.- Parametric & semi-parametric models: fit Cox proportional hazards or parametric (Weibull/Lognormal) interpurchase-time model to earlier cohorts, predict missing future transactions, simulate lifetime revenue (bootstrap to get distribution).- Sensitivity analysis: present multiple scenarios (conservative, base, optimistic) by varying tail hazard rates ±20%.3) Practical EDA steps / snippets- Compute censor indicators and survival curves by cohort (SQL/py/pandas).- Fit models on uncensored portion; simulate future purchases to augment CLV.- Bootstrap resampling to produce CI for adjusted CLV.4) Communicate to stakeholders- Present a concise slide/dashboard: what is truncated, numeric impact (raw vs. adjusted CLV), and scenario range (95% CI + conservative/optimistic bounds).- Explicitly list assumptions (stationarity of behavior, no structural changes), limitations, and recommended data fixes (extend observation window, instrument event-store).- Recommend action: use corrected CLV for strategic planning with caveats; run sensitivity-driven decisions (e.g., prioritize experiments with outcomes robust across scenarios).This approach gives quantifiable bias estimates, principled corrections, and transparent uncertainty so product decisions reflect data limits.
EasyTechnical
62 practiced
You receive a numeric column in a dataset with values including sentinel codes and obvious errors, for example: [100, 102, NaN, 105, -999, 108]. Describe step-by-step how you would detect sentinel values and outliers during EDA, and list at least three actions you might take for sentinel or error values versus legitimate outliers. Which visualizations and summary statistics would you use to support your decisions?
Sample Answer
Step-by-step detection (EDA):1. Quick summary: compute count, mean, median, std, min, max, percentiles, and nulls (pandas .describe(), .isna().sum()) to spot impossible minima like -999.2. Frequency check: value_counts() to detect repeated sentinel codes (e.g., -999 appears many times).3. Domain rules: confirm allowed range from business/context (e.g., valid scores 0–200). Flag values outside as errors.4. Outlier statistics: compute IQR-based bounds (Q1−1.5*IQR, Q3+1.5*IQR) and z-scores (>3) to find extreme but plausible values.5. Visual checks: histogram, boxplot, and scatter (vs. time or another variable) to see distribution and context; time series plot if data is temporal.6. Cross-field validation: compare with correlated columns (e.g., age vs. score) to find inconsistencies.Actions (for sentinel / error values vs legitimate outliers) — at least three:- Mark and convert sentinels to NaN and add a flag column (e.g., is_sentinel) so downstream logic knows they were special.- Impute sentinels/NaNs where appropriate (median for skewed data, model-based imputation, forward-fill for time series) or leave as missing if analysis requires raw counts.- Remove or filter rows with impossible values when they clearly indicate bad records (e.g., negative ages).- For legitimate outliers: investigate cause (data entry vs true rare event), consider transformation (log), winsorize/cap extreme values, or use robust methods (median, quantile regression) rather than deleting.- Create separate analysis segments: run key metrics with and without outliers and report both.Visualizations & stats to support decisions:- Histogram + KDE: overall shape and multimodality.- Boxplot: quick outlier identification and IQR view.- Scatter/time-series plots: contextualize points (are spikes tied to events?).- Value counts / bar chart: show sentinel frequency.- Summary stats: mean vs median, skewness, IQR, z-score counts.Example pandas snippets:
