End To End Data Preprocessing & Exploration Questions

Follow a systematic, tool-agnostic data pipeline before deeper analysis: load the data, check shape and dtypes, identify missing values and duplicates, explore distributions, check for outliers, understand class or category balance where relevant, and summarize key statistics. Document findings and build visualizations that surface relationships in the data. This exploration is the foundation for whatever comes next: feature engineering and model selection for predictive/ML work, or clean aggregations and trustworthy KPIs for dashboards and reporting.

MediumTechnical

25 practiced

Write Python code to compute Variance Inflation Factor (VIF) for each numeric feature in a pandas DataFrame (exclude the target). Explain how to interpret VIF values and recommend actions if one or more features have VIF > 10.

Sample Answer

To compute VIF for numeric features (excluding the target), we regress each feature against all other features and calculate VIF = 1 / (1 - R^2). Here's a concise, robust implementation using statsmodels:

python

import pandas as pd
import numpy as np
from statsmodels.stats.outliers_influence import variance_inflation_factor
import statsmodels.api as sm

def compute_vif(df, target_col=None, thresh_drop_constant=True):
    """
    Compute VIF for numeric features in df excluding target_col.
    Returns DataFrame with feature and VIF.
    """
    # Select numeric columns and exclude target if provided
    X = df.select_dtypes(include=[np.number]).copy()
    if target_col is not None and target_col in X.columns:
        X = X.drop(columns=[target_col])
    # Drop constant columns (zero variance) because VIF undefined
    if thresh_drop_constant:
        const_cols = X.columns[X.std() == 0].tolist()
        X = X.drop(columns=const_cols)
    # Add intercept for statsmodels
    X_with_const = sm.add_constant(X)
    vif_data = []
    for i, col in enumerate(X.columns):
        try:
            vif = variance_inflation_factor(X_with_const.values, i + 1)  # +1 to skip const
        except Exception as e:
            vif = np.nan  # e.g., perfect multicollinearity
        vif_data.append((col, vif))
    return pd.DataFrame(vif_data, columns=["feature", "VIF"]).sort_values("VIF", ascending=False)

Key points:- Drop non-numeric and constant features; add constant term for VIF calculation.- Perfect multicollinearity can cause errors or infinite VIF (returned as very large or NaN).

Interpretation and recommended actions:- VIF ≈ 1: no multicollinearity. VIF 1–5: moderate. VIF > 10: high multicollinearity and concerning.- If VIF > 10: consider (in order) 1) inspect pairwise correlations and remove/reduce redundant features; 2) combine correlated features via domain-driven aggregation or feature engineering; 3) use regularized models (Ridge) which handle multicollinearity; 4) use PCA or SVD to create orthogonal components (loses interpretability); 5) if perfect multicollinearity, drop one of the perfectly collinear columns.Complexity: O(n_features^2 * n_samples) due to repeated regressions; memory O(n_features * n_samples).

MediumTechnical

32 practiced

Given transactions with columns (user_id, purchase_amount, product_category) where purchase_amount may be missing, write pandas code to impute missing purchase_amount with the median per product_category and fall back to the global median when a category median is missing. Show concise, efficient code that avoids explicit Python loops.

Sample Answer

Approach: compute per-category medians with groupby.transform (which broadcasts per-row without Python loops), fill missing purchase_amount from that, then fill any remaining NaNs with the global median.

python

import pandas as pd

# df has columns: user_id, purchase_amount, product_category

# 1) fill by category median (transform broadcasts median per-row)
df['purchase_amount'] = df['purchase_amount'].fillna(
    df.groupby('product_category')['purchase_amount'].transform('median')
)

# 2) fallback to global median for categories with no median (all-NaN)
df['purchase_amount'] = df['purchase_amount'].fillna(df['purchase_amount'].median())

Key points:- groupby(...).transform('median') computes the median per category and returns an aligned Series — no explicit loops.- fillna applies the per-row category median; any categories with only NaNs produce NaN medians and are handled by the second fillna with the global median.- Time complexity: O(n) for a single pass and group aggregation; memory overhead minimal (one extra Series).Edge cases:- If all purchase_amount values are NaN, global median is NaN — consider using a constant default or domain-specific fallback.- Missing product_category will result in NaN category median; those rows will receive global median.

EasyTechnical

31 practiced

Explain the difference between z-score standardization and min-max scaling. When would you prefer RobustScaler (median and IQR) over StandardScaler? Give concrete examples of model types and data distributions where each is appropriate.

EasyTechnical

34 practiced

Given a SQL table orders(order_id PK, user_id, amount, order_ts timestamp), write a PostgreSQL query to find possible duplicate orders defined as same user_id and amount within 1 minute. Return offending order_id pairs and explain how you'd flag or remove duplicates safely.

Sample Answer

Approach: join the table to itself per user and amount, and find pairs where timestamps differ by <= 60 seconds. Use LEAST/GREATEST or restrict order_id to avoid duplicates. Then describe safe flagging/removal (marking, de-dup keys, transaction, backing up).

Query (returns each offending pair once with earlier/later order):

sql

SELECT
  o1.order_id AS order_id_earlier,
  o2.order_id AS order_id_later,
  o1.user_id,
  o1.amount,
  o1.order_ts AS ts_earlier,
  o2.order_ts AS ts_later,
  EXTRACT(EPOCH FROM (o2.order_ts - o1.order_ts))::int AS seconds_between
FROM orders o1
JOIN orders o2
  ON o1.user_id = o2.user_id
  AND o1.amount = o2.amount
  AND o1.order_ts < o2.order_ts
  AND o2.order_ts <= o1.order_ts + INTERVAL '60 seconds';

Key points:- Using o1.order_ts < o2.order_ts avoids duplicate reversed pairs and self-join to same row.- If you want symmetric pairs include <= and filter order_id <>.

Safe flagging/removal strategy:1. Add a boolean column is_duplicate (default false) or a dedup_job_runs table to track runs.2. Mark candidates first (UPDATE ... WHERE order_id IN (subquery)) rather than deleting.3. For deterministic retention, pick rule: keep earliest or lowest order_id per duplicate cluster — e.g. use DISTINCT ON or window function:

sql

WITH dup_groups AS (
  SELECT order_id,
         user_id, amount, order_ts,
         ROW_NUMBER() OVER (PARTITION BY user_id, amount, grp ORDER BY order_ts) AS rn
  FROM (
    SELECT *,
      SUM(is_new_group)::bigint AS grp
    FROM (
      SELECT *,
        CASE WHEN lag(order_ts) OVER (PARTITION BY user_id, amount ORDER BY order_ts) IS NULL
                  OR order_ts - lag(order_ts) OVER (PARTITION BY user_id, amount ORDER BY order_ts) > INTERVAL '60 seconds'
             THEN 1 ELSE 0 END AS is_new_group
      FROM orders
    ) s
    WINDOW w AS ()
  ) t
)
UPDATE orders
SET is_duplicate = TRUE
FROM dup_groups d
WHERE orders.order_id = d.order_id AND d.rn > 1;

4. After verification, delete flagged rows inside a transaction and keep backups (COPY to file or move to archive table).5. Validate with counts and sample checks before and after.

EasyTechnical

26 practiced

For a numeric feature 'session_duration', list the summary statistics you would compute during EDA (e.g., mean, median, std, skew, kurtosis, IQR, percentiles). Explain what each statistic tells you about the data distribution and how specific values (e.g., mean >> median) would influence preprocessing decisions such as applying transformations or removing outliers.

Sample Answer

Key summary statistics to compute for session_duration and what they tell you — plus how their values affect preprocessing:

- Count: number of observations; shows missingness and sample size.- Mean: arithmetic average; sensitive to extreme values.- Median (50th percentile): central tendency robust to outliers.- Mode: most frequent value; useful if many identical sessions (e.g., zeros).- Standard deviation (std): average spread around mean; large std → high variability.- Variance: square of std; used in some modeling contexts.- Interquartile range (IQR = Q3−Q1): spread of middle 50%; robust measure of dispersion.- Percentiles (e.g., 1st, 5th, 25th, 75th, 95th, 99th): show tail behavior and quantile thresholds for winsorizing/capping.- Min / Max: raw bounds; identify extreme outliers.- Skewness: asymmetry of distribution. Positive skew (right tail) means many short sessions and few very long ones. Negative skew is the opposite.- Kurtosis: tail heaviness. High kurtosis indicates heavy tails / more extreme outliers than normal.- Number/proportion of zeros or negatives: domain checks (sessions shouldn't be negative).- Missing rate: fraction missing → imputation decisions.

How specific patterns guide preprocessing:- Mean >> Median and positive skew: right-skewed with long tail. Consider log(x+1), Box-Cox, or Yeo-Johnson to reduce skew for models sensitive to normality; or use robust models/transformations. Use percentiles (e.g., 99th) to cap extreme outliers or winsorize.- High kurtosis and extreme max vs 99th percentile: suggests few extreme outliers — investigate (bot traffic, logging errors). If errors, remove; if valid but rare, consider capping or separate modeling.- Large IQR or std relative to mean: high dispersion — consider scaling (robust scaler using median and IQR) especially for distance-based models.- Many zeros: treat separately (zero-inflated models), or feature-engineer indicator for zero.- Skew ≈ 0, kurtosis ≈ 3, mean ≈ median: distribution near-symmetric — standard scaling often fine.

Always combine statistics with visual checks (histogram, boxplot, QQ-plot) and domain knowledge before removing or transforming data.

Unlock Full Question Bank

Get access to hundreds of End To End Data Preprocessing & Exploration interview questions and detailed answers.

Join thousands of developers preparing for their dream job.