Python for Data Analysis Questions

Covers the practical use of Python and its data libraries to perform data ingestion, cleaning, transformation, analysis, and aggregation. Candidates should be able to manipulate data frames, perform complex grouping and aggregation operations, merge and join multiple data sources, and implement efficient vectorized operations using libraries such as Pandas and NumPy. Expect to write clear, idiomatic Python with appropriate error handling, input validation, and small tests or assertions. At more senior levels, discuss performance trade offs and scalability strategies such as choosing NumPy vectorization versus Pandas, and when to adopt alternative tools like Polars or Dask for very large datasets, as well as techniques for memory management, profiling, and incremental or streaming processing. Also cover reproducibility, serialization formats, and integrating analysis into pipelines.

MediumSystem Design

55 practiced

Design a pandas-based pipeline to read all CSV files in a directory, concatenate them into a single DataFrame, coerce consistent dtypes using an explicit dtype mapping, and write partitioned Parquet files by calendar year. Describe potential pitfalls and memory-saving techniques in your design.

Sample Answer

Requirements & constraints:- Read all CSVs from a directory, coerce columns to explicit dtypes, concatenate, and write partitioned Parquet by calendar year.- Non-functional: handle large data that may not fit memory, preserve schema, be reproducible.

High-level pipeline:1. Discover CSV files (glob).2. Define explicit dtype mapping (dict: column -> pandas dtype or converter).3. Stream/ingest files in chunks to avoid memory spikes.4. For each chunk: apply dtype coercion, extract year column, write to partitioned Parquet (append mode).5. Validate final schema and optionally read small sample.

Example implementation (chunked write to year-partitioned parquet files using pyarrow/pandas):

python

import pathlib
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq

DATA_DIR = pathlib.Path("/data/csvs")
CSV_FILES = list(DATA_DIR.glob("*.csv"))
DTYPES = {"id": "Int64", "amount": "float64", "category": "category", "date": "datetime64[ns]"}
OUTPUT_DIR = "/data/parquet_by_year"

for csv_path in CSV_FILES:
    for chunk in pd.read_csv(csv_path, parse_dates=["date"], dtype={"category": "string"}, chunksize=100_000):
        # Coerce dtypes explicitly with safe conversions
        chunk = chunk.astype({"id": "Int64", "amount": "float64"})
        chunk["category"] = chunk["category"].astype("category")
        chunk["date"] = pd.to_datetime(chunk["date"], errors="coerce")
        chunk = chunk.astype({"date": "datetime64[ns]"})

        # Extract year for partitioning
        chunk["year"] = chunk["date"].dt.year.fillna(-1).astype("Int64")

        # Write per-year parquet files (append)
        for year, part in chunk.groupby("year"):
            year = int(year) if pd.notna(year) and year != -1 else "unknown"
            out_path = pathlib.Path(OUTPUT_DIR) / f"year={year}"
            out_path.mkdir(parents=True, exist_ok=True)
            table = pa.Table.from_pandas(part.drop(columns=["year"]), preserve_index=False)
            pq.write_table(table, out_path / f"{csv_path.stem}_{year}.parquet", compression="snappy")

Key design choices & reasoning:- Chunked CSV read prevents OOM on large inputs.- Explicit dtype mapping avoids mixed types and ensures consistent downstream analytics.- Using pyarrow Parquet gives columnar storage, compression, and schema preservation.- Partitioning by year at write-time enables efficient query pruning in downstream tools (e.g., Athena, Spark).

Pitfalls and how to mitigate:- Mixed/dirty types in CSV (e.g., numeric columns with stray text): use safe parsing (errors="coerce"), then validate and report rows where coercion failed.- Missing or malformed date values: parse with errors="coerce", assign to "unknown" partition, and log counts.- Schema drift across files: enforce master DTYPES and cast; fail fast or log mismatches for manual review.- Small-file problem: writing one parquet file per chunk/year can create many small files; mitigate by buffering per-partition and writing larger files (e.g., accumulate ~100MB then flush) or compact later with a merge job.- Index loss: avoid relying on DataFrame index; write a stable primary key column if needed.

Memory-saving techniques:- Use chunksize and process files sequentially.- Read expensive columns (large text) as string/object only when needed; convert to categorical when cardinality is low.- Drop unused columns early.- Use appropriate numeric dtypes (Int64 vs int64) and category for repeated strings.- Use pyarrow for zero-copy conversions where possible and write compressed Parquet (snappy).- If still large, use a distributed engine (Dask, Spark) or cloud-native staged transforms.

Validation & observability:- At end, run schema check (compare parquet schema to expected).- Emit metrics: rows processed, rows failed coercion, bytes written, files created.- Keep logs of sample bad rows for manual cleaning.

This pipeline balances correctness (explicit dtypes) with scalability (chunking, partitioned Parquet) and includes safeguards for common data-quality issues.

EasyTechnical

49 practiced

Explain the difference between a pandas Series and a pandas DataFrame. Provide short Python examples to create each and describe two practical scenarios in data analysis when you'd prefer a Series versus a DataFrame.

Sample Answer

A pandas Series is a 1D labeled array (like a single column), while a DataFrame is a 2D labeled table (rows × columns). Series has an index and a single dtype; DataFrame has row index and multiple named columns (each column can have its own dtype).

Example — create a Series:

python

import pandas as pd

s = pd.Series([10, 20, 30], index=['a', 'b', 'c'])
print(s)
# a    10
# b    20
# c    30

Example — create a DataFrame:

python

df = pd.DataFrame({
    'sales': [100, 150, 200],
    'region': ['N', 'S', 'E']
}, index=['Jan', 'Feb', 'Mar'])
print(df)

When to prefer a Series:- Quick vector operations on a single metric (e.g., apply smoothing or compute rolling average of daily active users). Series methods are concise and memory-efficient.- As an indexable lookup or mapping (e.g., store a mapping of product_id → category) where a 1D structure is simpler.

When to prefer a DataFrame:- Multi-feature analysis (e.g., build a cohort table with columns: user_id, signup_date, revenue) where you need joins, groupbys, or pivoting across columns.- Preparing data for modeling or dashboards where multiple related columns must be kept together, cleaned, and transformed in coordinated steps.

Key difference summary:- Dimensionality: Series = 1D, DataFrame = 2D- Use-case: Series for single-variable tasks; DataFrame for multi-variable, relational tasks.

HardTechnical

61 practiced

Provide a code sketch in Python for a memory-efficient out-of-core groupby aggregation over a 100M-row CSV: process in chunks, write partial aggregates partitioned by a hash of the group key to disk, then merge partitions to produce final aggregates. Explain intermediate disk layout, complexity, and failure recovery considerations.

Sample Answer

To process a 100M-row CSV memory-efficiently: read in chunks, hash-partition each chunk's group keys to N files (partial aggregates per partition), then for each partition read its partials, aggregate (fits memory), and write final results. Intermediate disk layout: directory with partition files like partitions/part_{i}.csv (or binary parquet/pickle) containing partial aggregates (group_key, agg1, agg2...). Use atomic rename for outputs.

Approach:1. Chunk read with pandas.read_csv(chunksize=...).2. For each chunk, compute groupby and map groups to partition id = hash(key) % N.3. Append partial aggregates to partition files.4. After all chunks, for each partition load all partials, merge by key, compute final aggregates, write final file.5. Optionally concat final partition files.

Code sketch:

python

import pandas as pd
import os
import hashlib

CSV = "big.csv"
PART_DIR = "partitions"
N = 64  # choose so each partition fits memory

os.makedirs(PART_DIR, exist_ok=True)

def part_id(key):
    return int(hashlib.md5(str(key).encode()).hexdigest(),16) % N

chunksize = 200_000
for df in pd.read_csv(CSV, chunksize=chunksize):
    # compute partial aggregates for this chunk
    agg = df.groupby('group_key').agg({'value':'sum','id':'count'}).reset_index()
    agg['part'] = agg['group_key'].apply(part_id)
    for i, g in agg.groupby('part'):
        out = os.path.join(PART_DIR, f"part_{i}.csv")
        # append; write header only if file absent
        g.drop(columns='part').to_csv(out, mode='a', header=not os.path.exists(out), index=False)
# merge partitions
FINAL_DIR = "final"
os.makedirs(FINAL_DIR, exist_ok=True)
for i in range(N):
    path = os.path.join(PART_DIR, f"part_{i}.csv")
    if not os.path.exists(path): continue
    p = pd.read_csv(path)
    final = p.groupby('group_key').agg({'value':'sum','id':'sum'}).reset_index()
    tmp = os.path.join(FINAL_DIR, f"part_{i}.csv.tmp")
    final.to_csv(tmp, index=False)
    os.replace(tmp, os.path.join(FINAL_DIR, f"part_{i}.csv"))  # atomic
# optionally concatenate FINAL_DIR files for final output

Key concepts:- Partitioning ensures per-partition memory fits. Use robust hash to evenly distribute.- Write partials incrementally to avoid keeping them in memory.- Use atomic replace/rename to avoid partial writes.

Complexity:- Time: O(N_rows log G_chunk?) dominated by disk I/O and grouping per chunk; overall O(rows).- Space: O(max_partition_size) on disk; memory O(max(groups_in_partition)).

Failure recovery:- Write partials per-chunk; upon crash, restart processing skipping chunks already contributing (track processed byte offsets or create marker files per chunk/partition).- Use atomic writes (tmp + rename) for final outputs.- Idempotency: include chunk identifiers in partial filenames or maintain a manifest to avoid double-appends.- Consider compression/parquet for faster IO and smaller disk footprint.

Trade-offs / notes:- Choose N based on RAM and cardinality.- Use binary formats (parquet/pickle) for speed and schema fidelity.- For multiple-group columns, hash tuple keys.

MediumTechnical

54 practiced

You have a table of events with columns ['user_id', 'event_time', 'event_type', 'value'] and multiple events per user. Write efficient pandas code that computes the time difference in seconds from each event to the previous event for the same user, ensuring the events are correctly ordered per user and handling missing or duplicate timestamps.

Sample Answer

Approach: convert event_time to datetime, sort events per user (and resolve duplicate timestamps deterministically), use groupby + shift to get previous timestamp per user, compute difference in seconds, and handle missing/duplicate timestamps explicitly.

python

import pandas as pd
# assume df has columns: user_id, event_time, event_type, value

# 1. Ensure datetime
df = df.copy()
df['event_time'] = pd.to_datetime(df['event_time'], errors='coerce')  # NaT if invalid

# 2. Define deterministic order for duplicates (e.g., by event_type then value)
df = df.sort_values(['user_id', 'event_time', 'event_type', 'value'], ascending=[True, True, True, True])

# 3. For strict ordering within same-timestamp events, optionally create a tie-breaker index
df['tie_breaker'] = df.groupby(['user_id','event_time']).cumcount()
df = df.sort_values(['user_id','event_time','tie_breaker'])

# 4. Compute previous timestamp per user
df['prev_time'] = df.groupby('user_id')['event_time'].shift(1)

# 5. Compute difference in seconds, handle NaT
df['sec_since_prev'] = (df['event_time'] - df['prev_time']).dt.total_seconds()

# 6. Optional: fill or flag negative/zero/NaN intervals
# e.g., flag same-timestamp duplicates:
df['same_timestamp'] = df['sec_since_prev'].fillna(0) == 0

Key points:- pd.to_datetime(errors='coerce') handles invalid times.- groupby+shift is vectorized and efficient (O(n) time, O(n) memory for new columns).- Sorting per user ensures correct ordering; include deterministic tie-breakers to handle duplicate timestamps.- Edge cases: first event per user -> sec_since_prev is NaN; duplicate timestamps -> zero seconds (or flagged); invalid timestamps -> NaT and resulting NaN diffs.Alternative: if data is huge, sort by ['user_id','event_time'] on-disk (dask or database) before loading to pandas.

MediumTechnical

57 practiced

Outline the steps and tools you would use to profile a slow pandas job to determine whether the bottleneck is CPU, Python-level loops, memory pressure, or I/O. Mention specific profiling tools/libraries and what you would look for in their output.

Sample Answer

1) Reproduce & isolate- Run the job on a representative dataset and, if possible, a smaller sample to iterate quickly.- Ensure deterministic inputs and disable unrelated services.

2) Lightweight system-level checks (quick triage)- top/htop or Windows Task Manager: watch CPU% (per core), user vs system, load average. - CPU-bound: sustained high CPU across cores. - I/O-bound: low CPU, high %IOWAIT.- free / vmstat / dstat / iostat: - Memory pressure: low free RAM, swap activity, high page faults. - I/O pressure: high disk throughput or latency.

3) Python-level sampling profiler (fast & non-invasive)- pyinstrument or py-spy (sampling): shows call stacks and time spent per function; py-spy can produce flamegraphs. - Look for large fractions in Python-level functions (apply/iterrows) implying interpreter overhead or GIL contention.

4) Deterministic profiler for hotspots- cProfile (via snakeviz or pstats): precise timings per function, call counts. - Look for heavy time in pandas internals vs your loops. High time in pure-Python loops -> refactor vectorized ops.- line_profiler (@profile): line-by-line timings for hotspots. - Use on functions flagged by sampling profiler; long times on specific lines indicate candidates for optimization.

5) Memory allocation profiling- memory_profiler (mprof) for line-by-line memory usage over time: reveals where RAM grows or copies happen. - Look for large incremental increases per row/chunk or copies after operations like df.assign/join.- tracemalloc: track allocation sources (helpful to find peak allocations).

6) I/O and serialization checks- Time CSV/Parquet reads/writes; use pandas read_csv(..., usecols, dtype, chunksize) and pd.read_parquet as a faster option.- Check network DB queries: use database slow query logs, EXPLAIN for SQL; pg_stat_activity or query profiling tools.

7) Concurrency / GIL considerations- If multi-threading shows low CPU utilization across processes, consider that heavy Python loops are GIL-bound. Use multiprocessing or vectorized NumPy/pandas operations.

8) What to look for in outputs (how to interpret)- Sampling profiler: functions taking >50% of runtime are top targets.- cProfile: high cumulative time vs per-call time; many calls to small functions suggest overhead.- line_profiler: specific lines with long timings or high memory deltas.- memory_profiler/tracemalloc: peak memory, allocation stack traces; frequent reallocations/copies.- iostat/dstat: high await or tps suggests disk bottleneck.- py-spy flamegraph: wide towers in Python code vs narrow ones in C extensions (pandas/NumPy in C is fast).

9) Remediation steps (based on cause)- CPU/GIL: vectorize with pandas/NumPy, use numba, or process-based parallelism (multiprocessing, Dask).- Python-level loops: replace iterrows/apply with vectorized groupby/merge or map in C-backed ops.- Memory pressure: process in chunks, use categorical dtypes, downcast numeric types, use out-of-core tools (Dask, Vaex), write intermediate results to disk.- I/O: switch to Parquet, compress appropriately, increase read_csv chunksize, optimize database queries and indexes, use local SSD or faster network.

10) Validate improvements- Re-run profilers after changes and compare wall time, CPU, memory peaks, and profiler outputs to confirm the bottleneck is resolved.

Unlock Full Question Bank

Get access to hundreds of Python for Data Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.