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

59 practiced

At scale, merge performance becomes a bottleneck. Discuss merge strategies in pandas for very large DataFrames (tens to hundreds of millions of rows): hash-based merge, sorting and merge-asof, chunked join, pre-aggregation, and offloading to databases or distributed frameworks. Provide recommendations and a sample chunked-merge code pattern.

Sample Answer

Approach summary:At tens-to-hundreds of millions of rows, in-memory pandas merges become I/O-, CPU-, and memory-bound. Choose a strategy based on join cardinality, key distribution, memory, and whether joins are equijoins or ordered/nearest.

Merge strategies (when & why):- Hash-based merge (pandas.merge): Good for moderate sizes if both sides fit memory and keys are evenly distributed. Complexity ~O(n+m) but high memory for hash tables.- Sorting + merge_asof / sort-merge: Use for time-series / nearest-key joins where ordering matters. Sorting cost O(n log n) but can be streaming-friendly (external sort) and uses lower peak memory if you can sort on disk.- Chunked join: Stream one table in chunks and probe against an indexed smaller table (or pre-partition both). Best when one table fits memory. Reduces peak memory.- Pre-aggregation / bloom filters / filtering: Reduce data before join — aggregate keys, apply predicates, or pre-filter using a bloom filter to drop non-matching rows.- Offload to DB / distributed frameworks: Use SQL databases (Postgres with indexes), Spark, Dask, or distributed key-value stores when both tables are large and joins require shuffling — these systems handle partitioned, out-of-core joins and parallelism.

Recommendations:- If one side << memory: load smaller side, set index, stream larger in chunks and join.- If both huge and cluster available: use Spark/Dask or a database with proper partitioning and indexes.- Always ensure dtypes are compact (categorical, int32 vs int64), prune unused columns, and consider hashing keys to integers.- For time-aware joins, sort and use merge_asof; avoid full cross-hash.

Sample chunked-merge pattern (pandas):

python

import pandas as pd

# small_df fits in memory; large file is CSV too big to load
small_df = pd.read_parquet("small.parquet")
# optimize dtypes
small_df['key'] = small_df['key'].astype('int32')
small_df = small_df.set_index('key')  # index for fast lookups

chunksize = 5_000_000
results = []

for chunk in pd.read_csv("large.csv", chunksize=chunksize, dtype={'key':'int32'}):
    # keep only needed cols to reduce memory
    chunk = chunk[['key','val1','val2']]
    chunk = chunk.set_index('key')

    # join using index to avoid creating huge temporary hash if possible
    joined = chunk.join(small_df, how='left', rsuffix='_s')
    # optional post-filter/aggregation to shrink result early
    agg = joined.groupby(level=0).agg({'val1':'sum','small_col':'first'})
    results.append(agg.reset_index())

# concatenate results incrementally (or write to disk)
out = pd.concat(results, ignore_index=True)
out.to_parquet("joined_output.parquet")

Edge considerations:- Monitor memory; use explicit gc and del large objects.- If join keys are skewed, partition by hash(key) into N files and join partition-wise in parallel.- For many-to-many high-cardinality joins, push to distributed systems or DBs.

Final guidance:If you can’t make it fit memory with chunking, move to Spark/Dask or a proper RDBMS with indexed joins and let the engine handle partitioning and spill-to-disk.

EasyTechnical

55 practiced

You get a dataset in a long format: columns ['date','store','product','sales']. Write pandas code to (a) pivot to wide format with stores as rows and products as columns (sales as values), (b) melt it back to long format, and (c) perform a pivot_table with aggregation (sum) and margins. Explain index/columns/values and how to handle duplicates during pivot.

Sample Answer

Approach: use pandas.DataFrame.pivot for strict reshapes (no duplicates), DataFrame.melt to go back to long, and DataFrame.pivot_table when duplicates exist or aggregation is needed.

python

import pandas as pd

# sample df
# df = pd.DataFrame({'date': [...], 'store': [...], 'product': [...], 'sales': [...]})

# (a) pivot to wide: stores as rows (index), products as columns, sales as values
wide = df.pivot(index='store', columns='product', values='sales')
# if you want to keep date too, use index=['date','store']

# (b) melt back to long
long = wide.reset_index().melt(id_vars='store', var_name='product', value_name='sales')
# if date was in index: reset_index(['date','store']).melt(id_vars=['date','store'], ...)

# (c) pivot_table with aggregation (sum) and margins (totals)
pt = df.pivot_table(index='store', columns='product', values='sales',
                    aggfunc='sum', margins=True, margins_name='All')

Key explanations:- index: row identifier(s) in wide form (here 'store').- columns: new column labels from a categorical variable ('product').- values: cell contents (here 'sales').- pivot requires a unique (index, columns) pair; if duplicates exist you get ValueError.- Use pivot_table with aggfunc (e.g., sum, mean) to handle duplicates: it aggregates all sales for each (index, column).- margins=True adds row/column totals; margins_name customizes label.

Edge cases / tips:- Missing combinations become NaN — use fillna(0) if appropriate.- To preserve order, use Categorical dtype for products.- For multi-index (date+store), include multiple columns in index/columns arguments.

MediumTechnical

59 practiced

Translate the following SQL logic into pandas: "SELECT user_id, COUNT(*) AS purchases, SUM(amount) AS total FROM transactions WHERE status='complete' AND created_at >= '2024-01-01' GROUP BY user_id HAVING COUNT(*) > 5 ORDER BY total DESC LIMIT 100". Provide the pandas equivalent code and explain any important differences in execution model and performance implications.

Sample Answer

Approach: filter rows, aggregate by user_id, apply HAVING (i.e., filter aggregated results), sort by total descending, and take top 100. Use vectorized pandas ops; for large data consider chunking or Dask.

python

import pandas as pd

# assume transactions is a DataFrame with columns: user_id, amount, status, created_at
# ensure created_at is datetime
transactions['created_at'] = pd.to_datetime(transactions['created_at'])

# filter
df = transactions[
    (transactions['status'] == 'complete') &
    (transactions['created_at'] >= pd.Timestamp('2024-01-01'))
]

# group and aggregate
agg = df.groupby('user_id', as_index=False).agg(
    purchases=('user_id', 'size'),        # count of rows per user
    total=('amount', 'sum')               # sum of amount per user
)

# HAVING: keep users with purchases > 5, sort and limit
result = agg[agg['purchases'] > 5].nlargest(100, 'total').reset_index(drop=True)

Key differences & performance implications:- Execution: SQL is declarative and often executed lazily / pushed to DB engine; pandas is eager/in-memory and imperative. pandas will load data into memory and perform operations there.- Memory: pandas requires dataset (or at least grouped intermediate) to fit in memory; for very large tables use chunked processing, Dask, or run the aggregation in the DB.- Performance tips: convert user_id/status to categorical to reduce memory and speed grouping; ensure amount is numeric and created_at is datetime; use .nlargest which is faster than sort_values(...).head(100).- Semantics: SQL COUNT(*) counts rows; pandas size aggregation used above matches that. Handle NULLs explicitly (e.g., missing amount will propagate to sum unless fillna(0)).

MediumTechnical

55 practiced

Explain how pandas MultiIndex works. Given a DataFrame df with MultiIndex on ['country','city'] and columns ['year','population'], show how to (a) select all rows for a country, (b) swaplevels and sort_index, (c) reset_index to flatten. Provide code examples and discuss when a MultiIndex is useful vs when a single-index flattened DataFrame is preferable.

Sample Answer

A pandas MultiIndex (hierarchical index) lets you index rows (or columns) by multiple levels, enabling compact representation of grouped data and powerful partial selection. For df indexed by ['country','city'] and columns ['year','population']:

Example setup:

python

import pandas as pd

tuples = [('US','NY'), ('US','SF'), ('CA','Toronto'), ('CA','Vancouver')]
idx = pd.MultiIndex.from_tuples(tuples, names=['country','city'])
df = pd.DataFrame({'year':[2020,2020,2020,2020], 'population':[8_336_000,884_000,2_930_000,631_000]}, index=idx)

a) Select all rows for a country (e.g., 'US'):

python

# label-based selection on level 0
df.loc['US']
# or explicitly on level:
df.xs('US', level='country')

df.loc['US'] returns a DataFrame indexed by city for that country.

b) Swap levels and sort index:

python

# swap levels so city becomes outermost, then sort for predictable ordering
df_swapped = df.swaplevel('country', 'city').sort_index()

swaplevel changes the order of levels; sort_index ensures lexicographic order which many operations expect.

c) Flatten the MultiIndex back to columns:

python

# reset_index moves index levels to columns
df_flat = df.reset_index()
# option: create a single concatenated index
df_flat['country_city'] = df_flat['country'] + '_' + df_flat['city']

When to use MultiIndex:- Useful for naturally hierarchical data (e.g., country→city→station), pivot tables, groupby aggregations, and when you want concise indexing without repeating values in columns.- Good for time-series with (entity, timestamp) keys or wide pivoted tables.

When to prefer flattened single-index DataFrame:- Simpler tooling and compatibility (many libraries expect flat columns), easier joins/merges, and clearer when exporting to CSV/SQL.- Prefer flattened when you frequently need vectorized column ops, simpler filtering, or when the hierarchy is not repeatedly used for selection/aggregation.

Key tips:- Use xs, loc with tuples (df.loc[('US','NY')]), get_level_values, and index.rename for clarity.- Always sort_index after rearranging levels to avoid unpredictable behavior.

MediumTechnical

62 practiced

You have a DataFrame with nested JSON in a column 'payload' (strings of JSON). Using pandas show how to expand the JSON into separate columns (flat) and normalize lists inside the payload into separate rows if needed. Provide examples using json_normalize and .explode, and discuss performance considerations.

Sample Answer

Approach: parse the JSON strings, use pandas.json_normalize to flatten nested dicts into columns, and .explode to turn list-valued fields into multiple rows. For performance, prefer vectorized parsing (or faster libraries like orjson/ujson) and avoid row-wise Python loops when possible.

Example:

python

import pandas as pd
import json
from pandas import json_normalize

# sample DF with nested JSON strings
df = pd.DataFrame({
    "id": [1, 2],
    "payload": [
        '{"user": {"id": 10, "name": "Alice"}, "events": [{"type": "click", "ts": 100}, {"type":"view","ts":110}], "meta": {"source":"web"}}',
        '{"user": {"id": 20, "name": "Bob"}, "events": [{"type": "view", "ts": 200}], "meta": {"source":"app"}}'
    ]
})

# parse JSON strings to dicts (use orjson.loads for speed in prod)
df['payload_parsed'] = df['payload'].apply(json.loads)

# Option A: normalize top-level and nested fields into columns (keeps lists as is)
flat = json_normalize(df['payload_parsed'])
df_flat = pd.concat([df.drop(columns=['payload','payload_parsed']), flat], axis=1)
print(df_flat)

Normalizing list fields into separate rows:

python

# explode the list of events so each event becomes its own row
df_exploded = df.copy()
df_exploded['payload_parsed'] = df_exploded['payload_parsed'].apply(json.loads)
df_exploded = df_exploded.explode(df_exploded['payload_parsed'].apply(lambda d: d.get('events')))
# Better pattern: extract events column, explode, then normalize
df_exploded = df.assign(parsed=df['payload'].apply(json.loads))
events = df_exploded['parsed'].apply(lambda d: d.get('events', []))
df_exploded = df_exploded.assign(event=events).explode('event').reset_index(drop=True)
# normalize event and other nested fields
events_flat = json_normalize(df_exploded['event'])
users_flat = json_normalize(df_exploded['parsed'].apply(lambda d: d.get('user', {})))
result = pd.concat([df_exploded[['id']].reset_index(drop=True), users_flat, events_flat], axis=1)
print(result)

Key points and reasoning:- json_normalize flattens nested dict keys into dot-separated column names (use record_path and meta arguments to directly extract nested lists into rows).- explode turns list-valued cells into multiple rows — combine explode + json_normalize to expand list-of-dicts.- Keep parsing separate from normalization to make debugging easier.

Performance considerations:- json.loads + apply is Python-level and can be slow for millions of rows. Use faster parsers (orjson/ujson) and vectorized methods where available.- If schema is consistent, precompute columns to avoid repeated normalization overhead.- For very large data, process in chunks, use Dask or PySpark to parallelize, or write a streaming parser.- Memory: json_normalize produces many columns; consider selecting only required keys to reduce memory.

Edge cases:- Missing keys, heterogeneous schemas, deeply nested lists, and nulls — handle with defaults or try/except during parsing.- Large or malformed JSON — validate before normalize.

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.