Pandas Data Manipulation and Analysis Questions

Data manipulation and analysis using the Pandas library: reading data from CSV or SQL sources, selecting and filtering rows and columns, boolean indexing, iloc and loc usage, groupby aggregations, merging and concatenating DataFrames, handling missing values with dropna and fillna, applying transformations via apply and vectorized operations, reshaping with pivot and melt, and performance considerations for large DataFrames. Includes converting SQL style logic into Pandas workflows for exploratory data analysis and feature engineering.

HardTechnical

58 practiced

You need to enrich a 50M-row transactions DataFrame with a small categories lookup of 1000 rows. Provide strategies to perform this enrichment efficiently in pandas while minimizing memory and time, such as mapping via a dict, setting the lookup as index and joining, converting join key to categorical, or using chunked processing. Provide code examples and explain runtime and memory trade-offs.

Sample Answer

Approach summary: with a 50M-row transactions DataFrame and a tiny 1k-row categories lookup, prefer operations that avoid materializing large temporary DataFrames and minimize Python-level per-row work. Common fast options: dict mapping, index-based join, categorical keys, and chunked processing if memory constrained.

1) Dict mapping (fast, low memory)- Best when lookup is simple (one column -> value). Uses O(50M) scan but minimal intermediate memory.

python

# assume transactions has 'cat_id', lookup has 'cat_id' and 'category_name'
lookup_map = dict(zip(lookup['cat_id'], lookup['category_name']))
# vectorized map (C-optimized)
transactions['category_name'] = transactions['cat_id'].map(lookup_map)

Trade-offs: Very memory-light (only lookup dict ~1k entries). map is fast (C-level) and doesn't create large join DataFrame. If mapping many columns, repeated maps add cost.

2) Left-join with lookup set as index (pandas join is efficient if lookup is indexed)- Good when merging multiple columns from lookup.

python

lookup_idx = lookup.set_index('cat_id')
transactions = transactions.join(lookup_idx, on='cat_id')  # avoids merging two big frames

Trade-offs: join with on and small right table keeps memory overhead lower than merge because right is indexed. Still may allocate new columns but avoids full Cartesian temp.

3) Convert join key to categorical (reduces memory & speeds groupby-like ops)- When cat_id has low cardinality or repeating many times.

python

transactions['cat_id'] = transactions['cat_id'].astype('category')
lookup['cat_id'] = lookup['cat_id'].astype(transactions['cat_id'].dtype)
# then map or join as above
transactions['category_name'] = transactions['cat_id'].cat.codes.map(dict(zip(lookup['cat_id'].cat.codes, lookup['category_name'])))

Trade-offs: categorical reduces memory for the key and speeds operations that use equality; conversion cost up front but amortized.

4) Chunked processing (when RAM insufficient)- Read/process in chunks, write out or append to disk/DB.

python

import pandas as pd
chunks = pd.read_csv('transactions.csv', chunksize=5_000_000)
out_chunks = []
for chunk in chunks:
    chunk['category_name'] = chunk['cat_id'].map(lookup_map)
    out_chunks.append(chunk)          # or write to parquet/csv/db to avoid accumulating
# better: write each chunk to parquet/file/db to avoid building out_chunks

Trade-offs: Minimal peak memory, linear time; overhead from IO and repeated Python loop. Best when you cannot hold 50M rows in memory.

Runtime & memory summary:- Dict map: O(N) time, O(lookup) memory — fastest and lean if single column needed.- join with indexed lookup: roughly O(N) time, slightly higher memory for result columns; good for multiple columns.- Categorical conversion: extra O(N) convert time, lowers memory footprint and can speed downstream ops.- Chunking: O(N) time with extra IO; peak memory bounded by chunk size.

Practical tips:- Profile with .memory_usage(deep=True), test on a representative subset.- If operations repeated, persist enriched result (parquet, DB).- Use pyarrow-backed parquet or Feather for efficient storage.- Avoid pandas.merge of two large frames—here right table is small, but prefer join(on=...) or map to minimize temporaries.

MediumTechnical

62 practiced

You need to create a new column 'customer_avg' containing each order's customer's average order amount. Show two implementations: one using groupby + transform and one using groupby + apply (or agg + merge). Explain differences in alignment, speed, and memory usage between the two approaches.

Sample Answer

To compute each order's customer's historical average order amount you can use groupby + transform (vectorized, aligned to original index) or groupby + apply / agg + merge (compute summary then join). Both produce the same result; differences are in alignment, speed, and memory.

Approach 1 — groupby + transform (recommended)

python

import pandas as pd

# sample dataframe
orders = pd.DataFrame({
    "order_id": [1,2,3,4],
    "customer_id": ["A","A","B","A"],
    "amount": [10, 20, 15, 30]
})

# compute per-order customer average (aligned to rows)
orders["customer_avg"] = orders.groupby("customer_id")["amount"].transform("mean")

Key points:- transform returns an array aligned to original index (one value per row).- Highly vectorized, implemented in C where possible, so it's fast and memory-efficient for typical use.- Handles NaNs per group naturally (mean excludes NaNs by default).

Approach 2 — groupby + agg then merge (or apply)

python

# compute aggregated customer averages
cust_avg = orders.groupby("customer_id", as_index=True)["amount"].agg(customer_avg="mean").reset_index()

# join back to original orders
orders = orders.merge(cust_avg, on="customer_id", how="left")

Or using apply (less common here):

python

cust_avg = orders.groupby("customer_id")["amount"].apply(lambda s: s.mean()).reset_index(name="customer_avg")
orders = orders.merge(cust_avg, on="customer_id", how="left")

Differences — alignment, speed, memory:- Alignment: transform returns values aligned to original rows automatically; agg+merge requires an explicit join but gives more control (e.g., keep only groups meeting criteria).- Speed: transform is usually faster because it avoids creating an intermediate DataFrame and leverages internal optimizations. apply with Python lambdas is slowest (Python-level loop per group).- Memory: transform writes result directly; agg+merge creates a smaller aggregated table then a join. For extremely high-cardinality grouping, agg+merge can be slightly more memory-friendly because the intermediate aggregated object is small, but transform still tends to be efficient since it produces only one extra column. apply that builds Python objects per group uses most memory.- Use-case guidance: use transform for simple per-row group stats; use agg+merge when you need multiple aggregations, complex group-level logic, or want to filter groups before joining.

MediumTechnical

70 practiced

You are asked to filter a transaction DataFrame df_transactions to find high-value active transactions: amount > 1000, status in ['confirmed','shipped'], and country not in ['US','CA']. Write pandas code using boolean masks and using df.query to accomplish the same filter. Also explain how to handle column names that include spaces or special characters in df.query.

Sample Answer

Approach: build a boolean mask with elementwise conditions (clear and explicit for BI workflows), then show equivalent using df.query (concise, readable). Note: use .isin() for multi-value checks and ~ for "not in". Handle spaces/special chars in query by surrounding column names with backticks (`column name`) or using df["col"] expressions.

python

# Boolean masks approach
mask = (
    (df_transactions["amount"] > 1000) &
    (df_transactions["status"].isin(["confirmed", "shipped"])) &
    (~df_transactions["country"].isin(["US", "CA"]))
)
high_value = df_transactions[mask]

python

# df.query approach (no spaces/special chars)
high_value_q = df_transactions.query(
    "amount > 1000 and status in ['confirmed','shipped'] and country not in ['US','CA']"
)

# If column names contain spaces/special chars, use backticks:
# e.g., column "order amount" and "ship/country"
high_value_q2 = df_transactions.query(
    "`order amount` > 1000 and `status` in ['confirmed','shipped'] and `ship/country` not in ['US','CA']"
)

Key notes:- .isin() is vectorized and efficient; query parses strings and can be more readable in complex filters.- Handle NaNs explicitly if desired: add df_transactions["amount"].notna() in the mask or include "and not amount != amount" isn't recommended — use notnull().- If types mismatch (amount as string), convert before filtering: df_transactions["amount"] = pd.to_numeric(...).

MediumTechnical

74 practiced

As a BI analyst you have three DataFrames: customers (customer_id, name), orders (order_id, customer_id, amount), and refunds (refund_id, order_id, refunded_amount). Explain when to use pd.concat versus pd.merge and provide pandas code to: (a) vertically concatenate monthly sales DataFrames, (b) left-join orders to customers to attach names, and (c) join refunds to orders and compute net_revenue per order.

Sample Answer

Use pd.concat when you need to stack DataFrames (same columns, add more rows) — e.g., combining monthly exports into a time series. Use pd.merge when you need to combine relational data by matching keys (SQL-style joins) — e.g., attach customer names to orders or match refunds to orders.

(a) Vertically concatenate monthly sales DataFramesAssume monthly_dfs = [jan_df, feb_df, mar_df] with same schema (order_id, customer_id, amount, date).

python

import pandas as pd

monthly_dfs = [jan_df, feb_df, mar_df]  # list of DataFrames with identical columns
all_sales = pd.concat(monthly_dfs, axis=0, ignore_index=True)  # stack rows, reset index
# Optionally drop duplicates or sort by date
all_sales = all_sales.drop_duplicates().sort_values("date").reset_index(drop=True)

(b) Left-join orders to customers to attach namesWe want every order with customer info; if a customer is missing, keep the order (left join).

python

orders_with_names = orders.merge(customers, how="left", on="customer_id")
# If you want to rename or prefer explicit columns:
# orders_with_names = orders.merge(customers[['customer_id','name']], how='left', on='customer_id')

(c) Join refunds to orders and compute net_revenue per orderRefunds may be zero, missing, or multiple refunds per order. Aggregate refunds first, then join.

python

# Aggregate refunds per order
refunds_agg = refunds.groupby("order_id", as_index=False)["refunded_amount"].sum().rename(columns={"refunded_amount":"total_refunded"})

# Join aggregated refunds to orders (keep all orders)
orders_refunds = orders.merge(refunds_agg, how="left", on="order_id")

# Treat missing refunds as 0 and compute net revenue
orders_refunds["total_refunded"] = orders_refunds["total_refunded"].fillna(0)
orders_refunds["net_revenue"] = orders_refunds["amount"] - orders_refunds["total_refunded"]

Key notes:- concat preserves column alignment; mismatched columns produce NaNs.- merge supports many join types (left, right, inner, outer) and control over suffixes for overlapping column names.- Always handle nulls and duplicates when aggregating joins (as shown for refunds).

HardSystem Design

63 practiced

Design an automated ETL pipeline using Python/pandas that runs daily to refresh dashboards. Include components for extraction (SQL/CSV), transformation (pandas scripts), loading (data warehouse or parquet store), scheduling/orchestration (Airflow or Prefect), incremental loads, idempotency, monitoring, and alerting. Provide high-level pseudo-code for orchestration and explain how you'd handle failures and retries.

Sample Answer

Requirements & constraints:- Daily run, refresh dashboards within 2 hours of day boundary- Sources: SQL DB (OLTP) + CSV lake- Target: Parquet partitioned on date in S3 (or Snowflake/Redshift as alternative)- Must support incremental loads, idempotent runs, retries, and alerts

High-level architecture:1. Extraction: small SQL queries (CDC/timestamp or max(updated_at)), CSV read from landing S3 path2. Transformation: pandas for cleaning, joins, aggregations, KPI calculations; use vectorized ops, type enforcement, schema validation (pandera)3. Load: write partitioned parquet to S3 (pyarrow), or bulk upsert to warehouse via COPY/merge4. Orchestration: Airflow (or Prefect) DAG scheduled daily, with task-level retries and SLAs5. Monitoring/Alerting: Task status in Airflow + log aggregation (CloudWatch/Datadog) + alerts to Slack/email on failures or data-quality checks6. Idempotency: write output to temp path then atomically move/rename; use dedup keys and upsert logic in warehouse7. Incremental: maintain watermark table (last_loaded_timestamp) in metadata store

Orchestration pseudo-code (Airflow-like):from datetime import datetimeDAG daily_etl DAG schedule=0 2 * * *task extract_sql -> task extract_csv -> task transform -> task quality_checks -> task load -> task publish_metrics

Pseudo-task bodies:- extract_sql: latest = read_metadata('last_ts'); query = SELECT * FROM orders WHERE updated_at > latest save to /tmp/orders_{ds}.parquet- transform: df1 = pd.read_parquet(...) df1 = clean_types(df1) df_agg = df1.groupby([...]).agg(...) save /tmp/transformed_{ds}.parquet- quality_checks: assert df_agg['count'].sum() > 0 check schema via pandera- load: write to s3://bucket/data/date=YYYY-MM-DD/_tmp/ atomic rename to s3://bucket/data/date=YYYY-MM-DD/ update_metadata('last_ts', max(updated_at))

Failure handling & retries:- Configure task retries (3 retries, exponential backoff) at DAG/task level- Transient failures (DB timeout, S3 throttling) will retry automatically- Non-transient/data-quality failures: mark DAG failed, alert owners with diagnostic payload (sample rows, checks failed)- Use idempotency: tasks safe to re-run by reading watermark and using temp paths; loads are atomic/replace partition- For long-running failures, implement manual "resume" operator that can re-run from last successful task or backfill specific date

Observability & alerts:- Export metrics: rows processed, latency, failures to Datadog/Prometheus- Send Slack notifications on SLA miss or DAG failure with run_id and logs- Daily summary report of row counts and anomaly detection (e.g., >20% drop)

Operational best practices:- Keep transformations small, testable functions; unit tests with pytest- Use CI to validate schema/pandera checks before deploy- Document watermark logic and retention policies- Provide a lightweight dashboard for ETL health for stakeholders

This design delivers reliable, incremental, idempotent daily refreshes suitable for downstream dashboards.

Unlock Full Question Bank

Get access to hundreds of Pandas Data Manipulation and Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.