Python Data Manipulation with Pandas Questions

Skills and concepts for extracting, transforming, and preparing tabular and array data in Python using libraries such as pandas and NumPy. Candidates should be comfortable reading data from common formats, working with pandas DataFrame and Series objects, selecting and filtering rows and columns, boolean indexing and query methods, groupby aggregations, sorting, merging and joining dataframes, reshaping data with pivot and melt, handling missing values, and converting and validating data types. Understand NumPy arrays and vectorized operations for efficient numeric computation, when to prefer vectorized approaches over Python loops, and how to write readable, reusable data processing functions. At higher levels, expect questions on memory efficiency, profiling and optimizing slow pandas operations, processing data that does not fit in memory, and designing robust pipelines that handle edge cases and mixed data types.

MediumTechnical

83 practiced

A pandas preprocessing step that used to run in seconds is now taking minutes as the input data has grown. Walk through how you'd figure out whether the bottleneck is CPU, memory, or I/O, and what you'd actually change in the code once you've found it.

Sample Answer

Start by measuring, not guessing, since CPU-bound, memory-bound, and I/O-bound problems look similar ("it's slow") but need completely different fixes.

1) Split the timing to localize the category- Time the raw data load by itself: %timeit pd.read_csv(path) (or however the data arrives). If this alone accounts for most of the runtime, you're likely I/O-bound (or CPU-bound on parsing, see below).- Time your transform/processing function on data that's already loaded into memory, separately from the load step. If this dominates, you're CPU- or memory-bound, not I/O.

2) Check for memory pressure- df.memory_usage(deep=True).sum() tells you how much RAM the DataFrame itself occupies. deep=True matters for object/string columns; without it pandas hugely underestimates their size.- Watch actual process memory (Activity Monitor, top, or memory_profiler) while the step runs. If memory keeps climbing toward the machine's limit and the OS starts swapping, the process will look CPU-slow but is actually memory-bound; swapping to disk is often the real cause of a sudden multi-minute stall.- Common memory culprits as data grows: object dtype columns (each string is a separate Python object), unnecessary df.copy() calls, or a merge/join that fans out rows (e.g. a many-to-many join) and blows up row count.

3) Check for I/O bottlenecks- If the file is coming from a network location (S3, a remote DB), separately time just the download/read. If that's most of the wall-clock time, the fix is on the I/O side: read fewer columns with usecols, switch to a columnar format like Parquet (much faster to read than CSV, especially with column pruning), or fetch in parallel/prefetch the next file while processing the current one.

4) Check for CPU bottlenecks- Use line_profiler on your processing function to see which lines actually consume the time:

python

# pip install line_profiler
%load_ext line_profiler
%lprun -f process_fn process_fn(df)

- The classic CPU culprit as data grows is anything that loops row-by-row in Python: .apply(axis=1), iterrows(), or a Python for-loop building up a list. These scale linearly in the number of rows but with a large per-row Python overhead, so they get painfully slow well before pandas' own vectorized operations would.

5) What you actually change once you know the category- CPU-bound (slow Python-level loop): rewrite as vectorized pandas/numpy operations (arithmetic on whole columns, np.where, groupby.transform), or use pandas' built-in string/datetime accessors instead of per-row Python string ops.- Memory-bound (too much RAM used): downcast dtypes (float64 to float32, int64 to a smaller int type, object columns with few unique values to category), drop columns you don't need before doing heavy operations, and avoid chained calls that create extra intermediate copies.- I/O-bound (loading/writing dominates): reduce what you read (usecols, dtype), switch to a faster format (Parquet/Feather over CSV), or process in chunks (read_csv(chunksize=...)) so you're not waiting on one giant read before any processing can start.- If, after fixing the above, the workload is CPU-bound but embarrassingly parallel (each row/group is independent), only then consider parallelizing with multiprocessing, Dask, or moving a numeric inner loop to numba. Parallelization adds real complexity and overhead, so it's a last resort, not a first move.

The overall approach: measure end-to-end, split load vs transform to localize the category, profile within that category (line_profiler for CPU, memory_usage/RSS for memory, timing the read for I/O), then apply the narrowest fix that addresses what you actually found.

HardTechnical

67 practiced

An overnight pandas ETL job failed because a vendor changed delimiter and quoting in a CSV file. Describe step-by-step how you would triage the incident, repair the pipeline for that run, and implement preventative measures so similar changes are detected and handled automatically in future runs.

Sample Answer

Framework: triage (detect & contain), repair the failed run, then harden and automate detection/handling.

Triage (step-by-step)1. Gather evidence: check logs, stack trace, job start time, sample file from vendor (S3/GCS path), and last successful file.2. Reproduce locally: download the failing CSV and run the read step to see exact parsing error (pandas error, malformed rows, encoding).3. Contain impact: stop downstream consumers/notifications, mark downstream datasets as stale to avoid bad analyses.

Repair the pipeline for that run1. Inspect file header / first few lines and compare delimiters/quoting to expected.2. Quick fix: use pandas with flexible parsing options to ingest file safely, e.g.:

python

import pandas as pd
from csv import Sniffer

raw = open('vendor.csv', 'r', encoding='utf-8', errors='replace').read(2048)
dialect = Sniffer().sniff(raw)  # guesses delimiter/quotechar
df = pd.read_csv('vendor.csv', delimiter=dialect.delimiter,
                 quotechar=dialect.quotechar, engine='python', on_bad_lines='skip')

3. Validate schema: run existing row/field validators (types, not-null, ranges). If validation passes, mark repaired run as completed; if partially bad, isolate bad rows into quarantine and alert stakeholders.

Preventative measures (detect & handle automatically)1. Input contract and tests: - Define a manifest for expected delimiter, quotechar, header names, column types, row counts. - Add lightweight pre-commit/CI tests and a pre-ingest validation job that downloads the file and validates format and schema.2. Automated sniff + fallback: implement a robust ingestion wrapper that: - Attempts strict read with expected settings. - On failure, tries Sniffer-based parsing and engine='python' with fallback encodings. - If still failing, atomically moves file to quarantine and triggers alert.3. Monitoring & alerting: - Emit metrics (parse_success, parse_latency, row_count) to observability (Prometheus/NewRelic). - On deviations (delimiter/quote mismatch or row_count delta > threshold), create pager/Slack alert with sample diffs.4. Contract enforcement with vendor: - Share file schema and format spec; request checksum/signature or schema registry. - Add SLA and automated daily sample checks; if changed, notify vendor and pause automated downstream processing.5. CI and integration tests: - Add unit tests that simulate common delimiter/quote changes and ensure pipeline fallback logic behaves.6. Logging & lineage: - Log detected delimiter/quotechar and original file path in metadata store; track which fallback was used for audit.

Why this approach- Immediate repair minimizes business impact using best-effort parsing and validation.- Automated sniff+fallback balances robustness with safety: strict by default, tolerant only when validated.- Contracts, tests, and alerts prevent silent failures and speed detection of vendor-side breaking changes.

HardSystem Design

78 practiced

Design a robust ETL pipeline using pandas for ingesting raw CSVs from S3, validating schema, transforming and normalizing columns, and writing partitioned parquet files for downstream model training. Include considerations for retries, atomic writes, incremental processing, schema evolution, monitoring, and memory constraints in your design.

Sample Answer

The pandas-specific core of this design (the part most relevant to this topic, and the part worth being able to write code for) is: read each file in bounded chunks, validate and normalize as you go, and write out in a way that survives a crash mid-write. Everything else (schedulers, registries, table formats) is context around that core, useful to know exists, but not the focus here.

Core pipeline (the pandas part):

python

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

REQUIRED_COLUMNS = {'user_id', 'ts', 'amount'}
DTYPES = {'user_id': 'int64', 'amount': 'float64'}

def process_file(local_csv_path, out_dir, chunksize=100_000):
    for i, chunk in enumerate(pd.read_csv(local_csv_path, chunksize=chunksize)):
        # 1. validate schema before doing anything else
        missing = REQUIRED_COLUMNS - set(chunk.columns)
        if missing:
            raise ValueError(f'missing required columns: {missing}')

        # 2. normalize/transform: enforce types, quarantine clearly bad rows
        chunk['ts'] = pd.to_datetime(chunk['ts'], utc=True, errors='coerce')
        chunk['amount'] = pd.to_numeric(chunk['amount'], errors='coerce')
        bad_rows = chunk['ts'].isna() | chunk['amount'].isna()
        if bad_rows.any():
            chunk.loc[bad_rows].to_csv(os.path.join(out_dir, 'quarantine.csv'), mode='a', index=False)
            chunk = chunk[~bad_rows]
        chunk = chunk.astype(DTYPES, errors='ignore')

        # 3. write this chunk to a partition, atomically
        for date, part in chunk.groupby(chunk['ts'].dt.date):
            partition_dir = os.path.join(out_dir, f'date={date}')
            os.makedirs(partition_dir, exist_ok=True)
            final_path = os.path.join(partition_dir, f'part-{i}.parquet')
            tmp_path = final_path + '.tmp'
            pq.write_table(pa.Table.from_pandas(part, preserve_index=False), tmp_path)
            os.replace(tmp_path, final_path)  # atomic rename, no half-written files

Why each piece matters:- Chunked reading (chunksize=...) keeps memory bounded regardless of file size, the same technique as any other 'process a file bigger than RAM' pandas problem.- Validating required columns up front, before transforming, fails fast on a malformed file instead of partially processing it.- Writing to a .tmp path and then using os.replace (an atomic rename at the OS level) means the final path only ever contains a complete file. If the process crashes mid-write, you're left with a stray .tmp file, never a corrupted part-*.parquet that downstream readers would choke on.- Tracking which source files have already been fully written (for example, a small manifest file or table listing filename plus a content checksum) is what makes the pipeline incremental and idempotent: on the next run, or on retry after a crash, you skip files you've already processed instead of redoing (and possibly duplicating) their output.- Schema evolution, in pandas terms, means: new files sometimes have an extra column (keep it, mark it as new in your manifest), are missing an optional column (fill it with a null of the right dtype), or have a column whose values no longer cleanly cast to the expected dtype (route those specific rows to a quarantine file instead of crashing the whole run, as shown above).

Beyond pandas: the production system around this (context worth recognizing, not something a pandas-focused interview usually goes deep on):- An orchestrator like Airflow schedules the job on a timer, retries it with backoff if it fails, and alerts someone if it keeps failing.- A schema registry (for example AWS Glue's) is a shared, versioned place to store 'here is what columns and types we expect', so that if a vendor changes their format, multiple systems find out from one source instead of each guessing independently.- Table formats like Delta Lake or Apache Hudi add transactional (all-or-nothing) guarantees across many files at once. The tmp-then-rename trick above gives you that for a single file; you'd reach for one of these only once you need the same guarantee across an entire multi-file write as one atomic unit, a step beyond what plain pandas plus pyarrow give you.- Monitoring tools (Prometheus, CloudWatch, or similar) collect metrics like rows processed and error counts so a human finds out about a broken run without having to read logs by hand.

Memory constraints, summarized: chunked reads bound peak memory; downcasting dtypes (category for low-cardinality strings, smaller int/float types) and dropping unneeded columns before the heavy transform steps keep each chunk's memory footprint small; if a single file is still too large even chunked, the same chunk-then-write-partition pattern above works unchanged, you're never holding the whole file in memory at once.

Trade-off to flag explicitly: this design is a good fit for pandas at up to tens of millions of rows per file, processed one file at a time. If total data volume grows past what a single machine can process file-by-file in reasonable time, the pandas-per-file logic (validate, normalize) is exactly what you'd port into a Spark or Dask job; the reasoning doesn't change, only the execution engine does.

MediumTechnical

57 practiced

You inherit a loop that builds up a result DataFrame by appending a new small DataFrame onto it on every iteration, and it gets dramatically slower as the loop progresses. Walk through why that pattern degrades, and how you'd rewrite it to scale to tens of thousands of iterations.

Sample Answer

The pattern is: start with an (often empty) result DataFrame, and on every iteration reassign it to itself plus a new small chunk, e.g. result = pd.concat([result, new_chunk]) (older code sometimes wrote this as result = result.append(new_chunk), a method pandas removed in version 2.0, but the underlying problem is identical either way).

Why it degrades: a DataFrame is stored as one or more contiguous blocks of memory. pandas can't just tack a few new rows onto the end of that memory in place, so every concat/append call allocates a brand-new block big enough for the combined result and copies all of the old data into it plus the new chunk. On iteration 1 you copy a handful of rows; by iteration 10,000 you're copying nearly the entire accumulated result just to add a few more rows on top. Total work across the whole loop is proportional to 1 + 2 + 3 + ... + n, which is O(n^2), not O(n). That's why it looks fine for the first few hundred iterations and then falls off a cliff as it scales toward tens of thousands.

Fix: collect all the small DataFrames in a plain Python list (appending to a list is O(1) each time, no copying), and call pandas.concat exactly once at the end, after the loop.

python

import pandas as pd

def combine_fragments(fragment_iterable):
    parts = []
    for chunk in fragment_iterable:
        parts.append(chunk)              # O(1): just a list append, no DataFrame copy

    result = pd.concat(parts, ignore_index=True)  # ONE allocation for the final shape
    return result

Concretely: for 10,000 fragments of 100 rows each, the repeated-concat pattern does on the order of 10,000 x 5,000 (average accumulated size) row-copies in total; collect-then-concat-once does one pass that copies each of the 1,000,000 rows exactly once.

Why this is faster: pd.concat, called once on the full list, can figure out the total row count up front and allocate memory for the final DataFrame a single time, then copy each fragment into its slot exactly once. Time becomes O(total_rows) instead of roughly O(total_rows^2 / fragment_size).

If tens of thousands of iterations still doesn't fit comfortably in memory even with a single concat (e.g. each fragment is itself large, or there are millions of fragments):- Concat in batches: merge every N (e.g. 1,000) fragments into an intermediate DataFrame or write it to disk (Parquet/CSV), then do a final concat/read over the intermediates. This bounds peak memory instead of holding every fragment plus the final result at once.- Skip in-memory accumulation entirely: append each chunk directly to a Parquet dataset or database table as it's produced, and read it back with a single query/read when needed, rather than holding anything in a Python list at all.- For truly out-of-core workloads, consider dask.dataframe, which handles this partitioned-write/lazy-concat pattern for you.

Edge cases: if fragments have inconsistent columns, pd.concat aligns by column name and fills missing values with NaN by default; verify that's the behavior you want (versus silently dropping mismatched columns), and check dtypes after concatenation since mixed-dtype columns across fragments can get upcast (e.g. int64 becomes float64 if any fragment introduces a NaN).

EasyTechnical

65 practiced

A teammate's pandas code raises a KeyError after they filter a DataFrame and then try to grab row 3 with .loc[3]. Walk through what's likely going wrong, how you'd decide between .loc and .iloc for a given task, and how you'd fix the code so index-based lookups behave predictably after filtering.

Sample Answer

This is a classic mix-up between selecting by **label** and selecting by **position**.

`.loc` is label-based: you give it the actual index value (the label) you want, and it looks that label up in the DataFrame's index. `.iloc` is position-based: you give it an integer offset (0 = first row, 1 = second row, ...) regardless of what the labels are.

When you filter a DataFrame, pandas keeps the original index labels on the surviving rows; it does not renumber them. So if row label `3` got filtered out, `.loc[3]` raises `KeyError: 3` because that label genuinely isn't in the index anymore. And even if label `3` did survive, `.loc[3]` gives you "the row labeled 3", not "the 3rd row of what's left" -- those are usually different things after a filter.

python

import pandas as pd

df = pd.DataFrame({"score": [50, 90, 30, 85, 40, 95, 20, 88]})   # index is 0..7 by default
passing = df[df["score"] >= 80]
print(passing)
#    score
# 1     90
# 3     85
# 5     95
# 7     88

passing.loc[3]     # works, but returns the row that was ALWAYS labeled 3 (score 85)
passing.loc[2]     # KeyError: 2 -- label 2 (score 30) was filtered out entirely

If what the teammate actually wants is "the 3rd row of the filtered result" (a position), the fix is `.iloc[2]` (position 2 = the 3rd row, since positions are 0-based):

python

passing.iloc[2]    # 3rd row by position (label 5, score 95), whatever its label happens to be

If they want to keep using `.loc` but with clean, predictable labels after filtering (e.g. because downstream code assumes a 0..n-1 index), reset the index right after filtering:

python

passing = df[df["score"] >= 80].reset_index(drop=True)
passing.loc[1]     # now label 1 == position 1, since the index was rebuilt as 0,1,2,...

The general decision rule: use `.loc` when you care about a meaningful label (an ID, a boolean mask, a date in a DatetimeIndex) surviving operations like filtering or sorting. Use `.iloc` when you specifically mean "the Nth row/column", independent of what the labels are, which is exactly what you want right after a filter unless you've reset the index.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.