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

26 practiced

Write Python code (using pandas and seaborn/matplotlib) to compute a correlation matrix for numeric columns in DataFrame df, generate a heatmap with annotated correlation coefficients, and mask the upper triangle so the plot is easier to read. Note whether you use Pearson or Spearman and why.

Sample Answer

Approach: select numeric columns, compute correlation (I’ll use Spearman if variables are non-linear or have outliers; Pearson for linear relationships). Create an upper-triangle mask, plot annotated seaborn heatmap with a diverging colormap and proper figure sizing.

python

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

# Example: df is given
# 1. Select numeric columns
num_df = df.select_dtypes(include=[np.number]).copy()

# 2. Choose correlation method: 'pearson' or 'spearman'
# Use 'spearman' if relationships may be monotonic but non-linear or if outliers/skewness present.
method = 'spearman'  # or 'pearson'
corr = num_df.corr(method=method)

# 3. Create mask for upper triangle
mask = np.triu(np.ones_like(corr, dtype=bool))

# 4. Plot heatmap
plt.figure(figsize=(10, 8))
sns.heatmap(
    corr,
    mask=mask,
    annot=True,
    fmt=".2f",
    cmap='RdBu_r',
    vmin=-1, vmax=1,
    linewidths=0.5,
    cbar_kws={"shrink": .75}
)
plt.title(f'{method.capitalize()} Correlation Matrix (masked)')
plt.tight_layout()
plt.show()

Key points:- Spearman is robust to outliers and captures monotonic relations; Pearson captures linear correlation.- Masking reduces redundancy and improves readability.Edge cases:- If num_df is empty, handle with a guard. If many columns, increase figure size or cluster/order columns for readability.

HardTechnical

26 practiced

Compare PCA, t-SNE, and UMAP for dimensionality reduction in EDA and before modeling. Discuss assumptions, interpretability, computational cost, choice of parameters (n_components, perplexity, n_neighbors), and how to validate that reduced-dimension representations are meaningful for clustering or visualization.

Sample Answer

Overview: PCA, t-SNE, and UMAP serve different purposes in EDA and pre-modeling. PCA is a linear, orthogonal projection that preserves global variance; t-SNE and UMAP are non-linear manifold methods that preserve local structure (t-SNE emphasizes local pairwise similarities; UMAP balances local and some global structure and is faster).

Assumptions:- PCA: data relationships are linear; principal axes capture meaningful variance; features scaled.- t-SNE: assumes meaningful local neighborhoods; distances converted to probabilities (perplexity controls effective neighborhood size).- UMAP: assumes data lies on a Riemannian manifold with a fuzzy topological representation; preserves topological structure via n_neighbors and min_dist.

Interpretability:- PCA: components are linear combinations of original features — loadings provide direct interpretability and can be inspected, used in reporting, and for feature reduction.- t-SNE/UMAP: embeddings are not linear transforms; axes have no direct meaning. Useful for visualization and cluster discovery but not for feature engineering without caution.

Computational cost:- PCA: O(min(n_features^2 n_samples, n_samples^2 n_features)) for full SVD; scalable with randomized SVD for large data.- t-SNE: O(n^2) naive; Barnes-Hut and FFT approximations reduce cost but still heavy for >50k points.- UMAP: more scalable (approx. O(n log n)) using nearest-neighbor graphs and optimized implementations.

Parameter choices:- n_components: PCA commonly 2–10 depending on explained variance; choose via scree plot or cumulative variance threshold (e.g., 90%). For visualization use 2–3.- perplexity (t-SNE): controls neighborhood size; typical 5–50. Try several (10,30,50); small values emphasize very local structure; large values more global grouping. Set learning rate and iterations too.- n_neighbors (UMAP): controls local vs. global balance; small (5–15) = fine local clusters; larger (50+) = more global structure. min_dist controls tightness of clusters.

Validation of meaningfulness:- Reproducibility: run with different seeds/params to check stability.- Quantitative metrics: compute cluster-quality metrics on embeddings (silhouette score, Davies–Bouldin) using known labels or clustering on the embedding. For PCA, measure retained variance.- Neighborhood preservation: compute k-NN overlap between original space and embedding (trustworthiness, continuity).- Downstream task performance: use embeddings as features for classification/clustering and compare metrics to baseline.- Sanity checks: overlay known metadata (labels, time, segment) on plots to see if expected separations appear; check for artifacts due to scaling or outliers.- Avoid over-interpretation: for modeling, prefer PCA or a supervised dimensionality-reduction (e.g., PLS, supervised UMAP) when you need stable, interpretable features.

Practical recommendation for a data analyst:- Use PCA first (quick, interpretable) to detect linear structure and outliers.- Use UMAP for exploratory visualization when you expect manifold structure and need speed.- Use t-SNE for high-quality local visual separation when dataset size is moderate and you tune perplexity.- Always standardize/scale features, document parameter choices, and validate embeddings quantitatively before drawing business conclusions.

EasyTechnical

28 practiced

After loading a dataset into pandas/SQL/Excel, what immediate checks and commands would you run to inspect shape and data types, and produce a short one-page summary for stakeholders? Provide concrete commands/examples for pandas and SQL and specify what metrics (row/column counts, null counts, unique counts, sample rows) you would include in the summary.

Sample Answer

Immediate checks I run after loading data: confirm shape, data types, nulls, uniques, ranges, and view sample rows. Then produce a one-page summary with row/column counts, missingness, cardinality, basic stats, and 3–5 example rows.

Pandas commands:

python

import pandas as pd
df = pd.read_csv("data.csv")

# Shape & dtypes
df.shape            # (rows, cols)
df.dtypes

# Quick peek
df.head(5)
df.sample(3)

# Missingness and uniques
df.isnull().sum().sort_values(ascending=False)
df.nunique().sort_values()

# Descriptive stats
df.describe(include='all').T  # count, mean, std, min, 25/50/75, max, top, freq

# Small profile summary df
summary = pd.DataFrame({
  'rows': [len(df)],
  'cols': [df.shape[1]],
})
col_stats = pd.DataFrame({
  'col': df.columns,
  'dtype': df.dtypes.astype(str),
  'nulls': df.isnull().sum().values,
  'null_pct': df.isnull().mean().round(3).values,
  'unique': df.nunique().values,
})

SQL queries (adjust schema/table):

sql

-- counts
SELECT COUNT(*) AS row_count, COUNT(DISTINCT id) AS distinct_ids FROM schema.table;

-- columns & types (Postgres)
SELECT column_name, data_type FROM information_schema.columns
 WHERE table_schema='schema' AND table_name='table';

-- nulls and uniques per column (example for col1)
SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN col1 IS NULL THEN 1 ELSE 0 END) AS nulls_col1,
  COUNT(DISTINCT col1) AS unique_col1
FROM schema.table;

-- sample rows
SELECT * FROM schema.table LIMIT 5;

Excel quick checks:- Use Data → Get & Transform or open CSV.- Status bar shows count of selected cells.- Use =COUNTA(range), =COUNTBLANK(range), =UNIQUE(range) (Excel 365), and pivot tables for distinct counts.- Show sample rows, sort/filter outliers, and use conditional formatting for nulls/outliers.

One-page stakeholder summary should include:- Row count, column count- Column list with data types- Top 10 columns by missing % and by cardinality- Key metrics per numeric column: mean, median, std, min, max- Key metrics per categorical: top value + frequency, unique count- Notable data quality issues and next steps (e.g., impute, drop, validate)

EasyTechnical

30 practiced

Write a concise Python (pandas) code snippet to load '/data/transactions.csv' into a DataFrame while optimizing memory: read only columns ['id','user_id','amount','transaction_date','status'], set user_id as int32, amount as float32, currency as category (if present), parse transaction_date as datetime, and avoid low-memory pitfalls. Include one-line comments explaining choices.

Sample Answer

To optimize memory and avoid low-memory pitfalls, first inspect the header to decide if optional columns (like 'currency') exist, then pass explicit dtypes and parse_dates with low_memory=False.

python

import pandas as pd

# Read header to detect optional columns (cheap operation)
cols = pd.read_csv('/data/transactions.csv', nrows=0).columns.tolist()

# Define required cols and include optional 'currency' if present
usecols = ['id', 'user_id', 'amount', 'transaction_date', 'status']
if 'currency' in cols:
    usecols.append('currency')

# Specify dtypes: small integer and float types reduce memory; leave 'id' as default if large
dtype = {
    'user_id': 'int32',        # compact integer for user identifiers
    'amount': 'float32',       # float32 halves memory vs float64 for monetary amounts
    'status': 'category'       # categorical for low-cardinality text
}
# Only set currency to category if present
if 'currency' in usecols:
    dtype['currency'] = 'category'  # categorical for short repeated currency codes

# Parse dates and set low_memory=False to ensure proper dtype inference during chunking
df = pd.read_csv(
    '/data/transactions.csv',
    usecols=usecols,
    dtype=dtype,
    parse_dates=['transaction_date'],
    low_memory=False
)

# Optionally downcast 'id' if it fits in smaller integer type to save memory
df['id'] = pd.to_numeric(df['id'], downcast='integer', errors='ignore')  # safe downcast

EasyTechnical

29 practiced

Given a table churn(user_id, churn_flag INT 0/1, plan_type), write SQL to compute overall class balance (counts and percentages of churn=1 vs churn=0) and class balance broken down by plan_type. After computing, suggest quick corrective steps if the dataset is highly imbalanced for modeling.

Sample Answer

Approach: compute overall counts and percentages, then group by plan_type. Use conditional aggregation and window functions for percentages.

sql

-- Overall class balance
SELECT
  SUM(CASE WHEN churn_flag = 1 THEN 1 ELSE 0 END) AS churn_1_count,
  SUM(CASE WHEN churn_flag = 0 THEN 1 ELSE 0 END) AS churn_0_count,
  ROUND(100.0 * SUM(CASE WHEN churn_flag = 1 THEN 1.0 ELSE 0 END) / COUNT(*), 2) AS pct_churn_1,
  ROUND(100.0 * SUM(CASE WHEN churn_flag = 0 THEN 1.0 ELSE 0 END) / COUNT(*), 2) AS pct_churn_0,
  COUNT(*) AS total_records
FROM churn;

sql

-- Class balance by plan_type
SELECT
  plan_type,
  COUNT(*) AS total_by_plan,
  SUM(CASE WHEN churn_flag = 1 THEN 1 ELSE 0 END) AS churn_1_count,
  SUM(CASE WHEN churn_flag = 0 THEN 1 ELSE 0 END) AS churn_0_count,
  ROUND(100.0 * SUM(CASE WHEN churn_flag = 1 THEN 1.0 ELSE 0 END) / COUNT(*), 2) AS pct_churn_1,
  ROUND(100.0 * SUM(CASE WHEN churn_flag = 0 THEN 1.0 ELSE 0 END) / COUNT(*), 2) AS pct_churn_0
FROM churn
GROUP BY plan_type
ORDER BY total_by_plan DESC;

Key points:- Handles NULLs if churn_flag is NULL (treated as 0 in these CASEs; adjust if needed).- For very small plan_type groups consider filtering (e.g., HAVING COUNT(*) >= 30) to avoid noisy percentages.

Quick corrective steps for high imbalance (practical for a Data Analyst preparing modeling data):- Resample: downsample majority class or upsample minority (random or SMOTE for tabular features).- Use class weights in modeling (logistic regression, tree-based models accept weights).- Use appropriate metrics: precision/recall, F1, ROC-AUC, PR-AUC rather than accuracy.- Stratified train/test split to preserve class proportions.- Feature engineering to boost signal for minority class and aggregate rare plan_types if very small.

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.