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.

MediumTechnical

62 practiced

A categorical column 'city' has 10k unique values across 10M rows. Describe the memory and performance benefits of converting this column to pandas.Categorical, show code to do it safely, and list pitfalls (e.g., high-cardinality categories, sorting, category reordering). When would you avoid using Categorical?

Sample Answer

Converting a high-cardinality string column to pandas.Categorical stores the column as integer codes + a categories array. Memory: instead of storing 10M Python strings (heavy), you store 10M int32 (or smaller) codes plus ~10k unique category strings — often reducing memory by an order of magnitude. Performance: faster comparisons, groupby/joins on the column use integer operations, and reduced GC/serialization overhead.

Safe conversion (preserve order, avoid surprises):

python

import pandas as pd

# df['city'] is object (strings); use copy to be safe if in-place risk
df = df.copy()

# Create categorical with categories inferred (default) or specified list to control ordering
df['city_cat'] = pd.Categorical(df['city'], categories=None, ordered=False)

# Optionally free original column
df.drop(columns=['city'], inplace=True)

If you want to ensure stable mapping across datasets (train/serve), fix categories with a known list:

python

known_cats = sorted(unique_city_list)  # from training data
df['city_cat'] = pd.Categorical(df['city'], categories=known_cats)
# unseen cities become NaN — handle with fillna or a '<UNK>' category

Pitfalls:- High-cardinality: when unique values approach row count, overhead of codes + categories may not help; categorical indexing overhead and memory for category array can still be large.- Sorting: sorting by categorical uses category order, not lexicographic, unless categories are set accordingly.- Category reordering: operations like .astype('category') on partitions can yield different code mappings; always unify categories before combining data.- Unseen/new categories: fixed categories will produce NaN — must decide mapping strategy for unknowns.- Serialization: some formats may not preserve categorical dtype; convert to codes or strings as needed.- dtype choices: choose smallest integer subtype for codes if custom storing.

When to avoid:- If cardinality is extremely high (near number of rows) so savings are negligible.- When categories change constantly in production and maintaining a stable mapping is infeasible.- When you need fast vectorized string operations (e.g., substring) — categorical stores codes and requires mapping back to strings.- When interoperating with systems that don't understand pandas Categorical (export/ML pipeline steps) unless you explicitly handle codes/categories.

EasyTechnical

62 practiced

Given a DataFrame `df` with columns ['user_id', 'feature1'] where feature1 contains NaNs, write pandas code to replace NaNs with the median of feature1 for that user's group; if a user's group median is not available (all NaNs), use the global median. Ensure the dtype remains numeric. Provide a short explanation of your approach.

Sample Answer

Approach: compute per-user median via groupby.transform, compute global median, use group median to fill NaNs and fall back to global median, and ensure dtype stays numeric.

python

import pandas as pd
import numpy as np

# example df
# df = pd.DataFrame({'user_id': [1,1,2,2,3], 'feature1': [10, np.nan, 5, np.nan, np.nan]})

# ensure feature is numeric
df['feature1'] = pd.to_numeric(df['feature1'], errors='coerce')

# per-user median (will be NaN for users with all NaNs)
user_median = df.groupby('user_id')['feature1'].transform('median')

# global median over all non-NaN values
global_median = df['feature1'].median()

# replace NaNs with user median, then with global median if user median is NaN
df['feature1'] = df['feature1'].fillna(user_median).fillna(global_median)

# dtype remains numeric (float if medians are non-integers)
df['feature1'] = pd.to_numeric(df['feature1'], errors='coerce')

Key points:- groupby.transform('median') aligns per-row medians.- fillna twice ensures users with all-NaNs get the global median.- to_numeric keeps dtype numeric; medians typically produce float dtype.- Complexity: O(n) time, O(n) extra for the transform. Edge cases: empty column (global_median NaN) — handle by providing a default value if needed.

HardSystem Design

48 practiced

You need to join a 100M-row feature table with a 1M-row training example list to produce training data without blowing memory. Describe efficient strategies in Python: partitioned (sharded) joins by key, hash-based streaming join, bloom-filter pre-filtering, using on-disk keyed stores, or pushing joins into a database. For the partitioned approach provide a code sketch showing how to partition, join, and reduce memory usage.

Sample Answer

Requirements & constraints:- Join 100M-row feature table (F) with 1M-row training keys (T) by key, memory-limited.- Need correctness, reasonable speed, and reproducibility.

High-level strategies (when to use which)- Partitioned (sharded) joins by key: safest, simple, parallelizable. Split both datasets into N shards by hash(key) so each shard fits memory; then join shard-by-shard and stream out results.- Hash-based streaming join: load smaller table (T or a subset) into an in-memory hash map and stream through F; good when smaller side fits memory.- Bloom-filter pre-filtering: build a Bloom filter from T to cheaply filter F; reduces IO/compute before heavier joins.- On-disk keyed stores: use LMDB/RocksDB/SQLite keyed DB for random lookups when lookup pattern is sparse and repeated.- Push joins into DB/warehouse: if F in a scalable DB (BigQuery, Redshift, Snowflake) push join there to leverage distributed resources and avoid transfer; use when cost/latency trade-off acceptable.

Partitioned (sharded) approach — why and how- Hash partitioning ensures that all rows with same key land in same shard; shards can be sized to fit memory.- Workflow: (1) choose N based on memory/row-size; (2) stream-partition both datasets into N files; (3) for i in 0..N-1 load shard_i of T and F, do in-memory join, write output; (4) optionally compress/merge outputs.- Benefits: simple, reproducible, easily parallelizable, works with arbitrarily large datasets.

Code sketch (partition, join, reduce memory):

python

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

def shard_id(key, N):
    # stable hash to bucket
    return int(hashlib.md5(str(key).encode()).hexdigest(), 16) % N

def partition_to_parquet(input_path, key_col, out_dir, N, chunksize=100_000):
    os.makedirs(out_dir, exist_ok=True)
    writers = {}
    for i in range(N):
        writers[i] = None
    for chunk in pd.read_csv(input_path, chunksize=chunksize):
        chunk['_shard'] = chunk[key_col].apply(lambda k: shard_id(k, N))
        for sid, sub in chunk.groupby('_shard'):
            path = os.path.join(out_dir, f'shard-{sid}.parquet')
            if not os.path.exists(path):
                sub.drop(columns='_shard').to_parquet(path, index=False)
            else:
                # append via pyarrow
                table = pq.read_table(path)
                new = pa.Table.from_pandas(sub.drop(columns='_shard'))
                pq.write_table(pa.concat_tables([table, new]), path)

def process_shards(feature_dir, train_dir, out_dir, key_col, N):
    os.makedirs(out_dir, exist_ok=True)
    for i in range(N):
        fpath = os.path.join(feature_dir, f'shard-{i}.parquet')
        tpath = os.path.join(train_dir, f'shard-{i}.parquet')
        if not os.path.exists(tpath):
            continue
        # load smaller shard first (training shard typically smaller)
        train = pd.read_parquet(tpath)
        features = pd.read_parquet(fpath)
        # in-memory join (inner join); choose merge strategy that avoids copies
        joined = train.merge(features, on=key_col, how='left')
        out_path = os.path.join(out_dir, f'joined-{i}.parquet')
        joined.to_parquet(out_path, index=False)
        # free memory
        del train, features, joined

# Example usage:
# 1) partition both tables into N shards (choose N so each shard fits memory)
# partition_to_parquet('features.csv', 'id', 'feature_shards', N=128)
# partition_to_parquet('train.csv', 'id', 'train_shards', N=128)
# 2) join per shard
# process_shards('feature_shards', 'train_shards', 'joined_shards', 'id', N=128)

Practical tips & trade-offs- Choose N so shard size < available memory / safety factor (e.g., 3x).- Use compression (parquet/snappy) to reduce disk; use fast columnar formats (parquet, feather).- Use Bloom filter: build from T and filter F in streaming pass to avoid writing many unnecessary feature rows.- Parallelism: run per-shard joins in parallel workers; watch I/O contention.- If feature table lives in data warehouse, push the join to the warehouse (cheaper and faster if compute available)—export only result if privacy/cost constraints allow.- For repeated lookups or feature store patterns, consider RocksDB/LMDB to serve features on-demand.- Monitor skew: heavy-key skew can break memory assumptions; handle by detecting hot keys and treating them separately (e.g., single-key streaming, sub-sharding).

This approach is robust for ML pipelines: predictable memory use, easy to parallelize, and compatible with feature engineering steps per-shard.

MediumTechnical

47 practiced

For each user, resample their event counts to a daily frequency, fill missing days with zero, compute a 7-day trailing rolling mean (aligned to the right), and return a DataFrame with columns ['user_id', 'date', 'daily_count', 'rolling_7d_mean']. Provide memory- and performance-conscious pandas code that avoids Python loops and explain potential bottlenecks when there are millions of users.

Sample Answer

Approach: put data into a MultiIndex (user_id, date), use groupby(level=0).resample('D') to fill missing days with zeros, then compute a right-aligned 7-day rolling mean per user via groupby+rolling. This avoids Python-level loops and keeps operations vectorized.

python

import pandas as pd

def daily_with_rolling(df, date_col='date', user_col='user_id', count_col='count'):
    # assume df has columns [user_id, date, count]; date may be datetime or string
    df = df[[user_col, date_col, count_col]].copy()
    df[date_col] = pd.to_datetime(df[date_col])
    # ensure sorted for groupby/resample/rolling efficiency
    df = df.sort_values([user_col, date_col])

    # set MultiIndex so we can resample per user (level=0)
    df = df.set_index([user_col, date_col])

    # resample daily per user and sum counts for days that have multiple events
    daily = df.groupby(level=0).resample('D', level=1).sum()

    # fill missing days with 0 (resample creates days but sum on empty groups gives NaN)
    daily[count_col] = daily[count_col].fillna(0)

    # compute 7-day trailing mean aligned to right (default center=False -> right-aligned)
    rolling = daily.groupby(level=0)[count_col].rolling(window=7, min_periods=1).mean()

    # rolling has a MultiIndex (user_id, date); align and assign
    daily['rolling_7d_mean'] = rolling.values

    # reset index to desired columns
    result = daily.reset_index().rename(columns={count_col: 'daily_count'})
    return result[[user_col, date_col, 'daily_count', 'rolling_7d_mean']]

Key points / reasoning:- groupby(level=0).resample('D', level=1) performs resampling per user in C/Cython-backed pandas routines—vectorized and avoids explicit Python loops.- rolling after groupby ensures the window is applied per user.

Complexity & bottlenecks:- Time: O(U * D) where U = #users and D = average days per user (linear in final row count). Groupby/resample/rolling are O(n) but with nontrivial constants.- Memory: the resampled result expands rows to include every user-day; if you have millions of users and long date ranges, this can blow up memory. Also groupby/resample creates intermediate objects and indices.- Potential bottlenecks: sorting and creating MultiIndex, temporary arrays for rolling, and large memory spikes.

Practical mitigations:- Filter date range to relevant window before resampling.- Process users in chunks (split user_id space) or use out-of-core tools: Dask DataFrame or Spark for distributed processing.- Use categorical user_id and appropriate dtypes to reduce memory; consider storing daily_count as int32/float32.- If many users are sparse (few events), consider computing rolling on sparse representations or only expanding windows around event clusters.

MediumTechnical

50 practiced

You have duplicated transaction records and want to deduplicate with tolerance: two records with the same user_id, amount equal, and timestamps within 2 seconds should be considered duplicates. Describe and implement a pandas approach to identify and drop such duplicates while keeping the earliest record. Explain complexity and edge cases.

Sample Answer

Approach (brief)- Convert timestamps to datetime, sort by user_id, amount, timestamp (ascending).- Within each (user_id, amount) group compute the time difference to the previous row.- Mark a row as duplicate if the previous row exists and the time difference <= 2 seconds.- Drop marked duplicates, keeping the earliest record in each close-time cluster.

Code implementation

python

import pandas as pd

def dedupe_tolerant(df, ts_col='timestamp', user_col='user_id', amt_col='amount', tol_seconds=2):
    # Ensure timestamp is datetime
    df = df.copy()
    df[ts_col] = pd.to_datetime(df[ts_col], errors='coerce')

    # Sort so earliest record comes first within groups
    df = df.sort_values([user_col, amt_col, ts_col]).reset_index(drop=True)

    # Compute difference to previous row within the same (user_id, amount) group
    grp = df.groupby([user_col, amt_col], sort=False)
    df['prev_ts'] = grp[ts_col].shift(1)
    df['dt_prev'] = df[ts_col] - df['prev_ts']

    # Mark duplicates where time diff <= tolerance and prev exists
    tol = pd.Timedelta(seconds=tol_seconds)
    df['is_dup'] = df['dt_prev'].notna() & (df['dt_prev'] <= tol)

    # Keep rows that are not marked duplicate (keeps earliest in each cluster)
    result = df.loc[~df['is_dup']].drop(columns=['prev_ts', 'dt_prev', 'is_dup'])
    return result

Key points / reasoning- Sorting ensures the earliest timestamp is kept when duplicates occur within tolerance.- Grouping by (user_id, amount) restricts comparisons to relevant records only.- Using shift compares each row to its immediate predecessor: this collapses sequential duplicates (A at t0, B at t0+1s, C at t0+1.5s) by keeping A and removing B and C because both are within tolerance from a kept earlier row.

Time & space complexity- Sorting: O(n log n) time. Group/shift and linear scans: O(n). Overall time O(n log n). Space O(n) for copies and extra columns.- If input is pre-sorted by keys + timestamp, complexity reduces to O(n).

Edge cases and considerations- Missing or malformed timestamps: to_datetime(..., errors='coerce') produces NaT; those rows won't be considered duplicates (you may want to drop or inspect them).- Timezones: normalize timezone-aware timestamps to a common timezone before comparing.- Chain duplicates: the shift approach removes later items in a chain, which matches "keep earliest".- Amount floating-point equality: if amount is float, consider rounding or using a tolerance for amount equality before grouping.- Very large datasets: consider processing by partitions (by user hash) or using Dask / Spark to avoid O(n log n) sort on single machine.- Duplicate definition symmetry: this approach only compares to the immediate previous row within group; if you need to consider a row within 2s of any earlier kept row in the group, the shift logic already handles that via iterative removal because earlier kept rows remain earliest in sorted order.

Alternatives- pd.merge_asof to join each row to the previous timestamp within tolerance (similar effect).- Using windowed/rolling operations if more complex clustering logic required.- SQL approach with window functions if operating in a database.

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.