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

78 practiced

Explain how to clean a free-text column of phone numbers using pandas string methods to produce a standardized E.164-like format (e.g., +1XXXXXXXXXX). Provide an approach that removes non-digits, handles leading zeros, and deals with missing country codes conservatively.

Sample Answer

Approach: use pandas string methods + regex to (1) strip non-digits, (2) normalize leading zeros conservatively, (3) detect if number already includes country code, and (4) only fill missing country codes when explicitly allowed (to avoid false assumptions). Return standardized "+<country><national_number>" or NaN for ambiguous cases.

python

import pandas as pd
import numpy as np
import re

def normalize_phones(series: pd.Series, default_country='1', assume_missing=False):
    """
    Normalize free-text phone numbers to an E.164-like format: +<country><number>
    - Removes non-digits
    - Removes leading "+" if present then treats digits
    - Removes leading zeros (conservative)
    - If country code present (length > 10 for NANP-like), keep it
    - If no country and assume_missing=True, prepend default_country for 10-digit numbers
    - Otherwise return NaN for ambiguous/malformed
    """
    s = series.astype(str).str.strip()
    # remove everything except digits
    digits = s.str.replace(r'\D+', '', regex=True)
    # drop empty results
    digits = digits.replace('', np.nan)
    
    def format_num(x):
        if pd.isna(x):
            return np.nan
        # remove leading '+' already eliminated; remove leading zeros conservatively
        x = re.sub(r'^0+', '', x)
        if not x:
            return np.nan
        # heuristic: if length >= 11 assume first digits are country code
        if len(x) > 10:
            return f"+{x}"
        # if exactly 10 digits, likely missing country (e.g., US). Only add default if allowed
        if len(x) == 10:
            return f"+{default_country}{x}" if assume_missing else np.nan
        # short numbers (7 digits etc.) -> ambiguous, return NaN
        return np.nan

    return digits.map(format_num)

Key points:- Use vectorized pandas.str.replace for fast cleaning; fallback to Python regex for per-value heuristics.- Conservative policy: don't assume country codes unless explicit or allow via assume_missing flag.- Leading zeros removed: many countries use trunk prefix '0' (e.g., 0XXXXXXXX) — dropping them makes country-level numbers consistent, but can be wrong for some formats; that's why this is conservative.

Time/space:- O(n * m) where n rows and m average length of string; memory O(n) for result.

Edge cases:- International numbers with trunk prefixes and variable national lengths (requires country-specific rules for perfect normalization).- Extensions ("ext", "x") are removed — consider capturing if required.- Numbers with formatting errors or too-short numbers returned as NaN for manual review.

Alternatives:- Use phonenumbers (libphonenumber) for robust parsing and validation by region.- Build country-specific rules if dataset is scoped to a known country to reduce ambiguity.

MediumTechnical

72 practiced

Write pandas code using groupby and agg to compute per-product: total_revenue, average_price, unique_customers, and first_order_date. Use named aggregation and show how to reset the index to a flat DataFrame. Include handling for products with no sales in the period (they should appear with zeros/NaN appropriately).

Sample Answer

Approach: group sales by product_id using pandas named aggregation to compute sum/mean/nunique/min, then left-join to a products table so products with no sales still appear. Fill zeros/NaN appropriately and reset index to a flat DataFrame.

python

import pandas as pd
import numpy as np

# Example inputs
# sales_df: columns -> product_id, revenue, price, customer_id, order_date
# products_df: columns -> product_id, product_name (catalog of all products)
sales_df['order_date'] = pd.to_datetime(sales_df['order_date'])

# Aggregate using named aggregation
prod_stats = sales_df.groupby('product_id').agg(
    total_revenue=('revenue', 'sum'),
    average_price=('price', 'mean'),
    unique_customers=('customer_id', 'nunique'),
    first_order_date=('order_date', 'min')
).reset_index()  # makes it flat already

# Ensure all products present by left-joining the catalog
result = products_df.merge(prod_stats, on='product_id', how='left')

# Fill missing values for products with no sales
result['total_revenue'] = result['total_revenue'].fillna(0.0)
result['unique_customers'] = result['unique_customers'].fillna(0).astype(int)
# average_price should remain NaN if no sales
result['first_order_date'] = pd.to_datetime(result['first_order_date'])  # keep dtype
# Optionally: sort or reorder columns
result = result[['product_id', 'product_name', 'total_revenue', 'average_price', 'unique_customers', 'first_order_date']]

Key points:- Named aggregation keeps column names explicit and readable.- reset_index() after groupby (or .reset_index() on the merged result) yields a flat DataFrame.- Left-join with products_df ensures items with no sales appear; fillna sets zeros where appropriate while leaving average_price/first_order_date as NaN/NaT to indicate no observations.

MediumTechnical

71 practiced

Describe an approach to deduplicate and canonicalize customer records when you have multiple identifiers and partial matches (e.g., same email, slightly different names). Which pandas techniques and external libraries might you use, and how would you validate deduplication accuracy?

Sample Answer

Situation: We have multiple customer feeds with overlapping but inconsistent identifiers (email, phone, name variants) and need a clean canonical customer table for reporting.

Approach (high-level):- Normalize fields, block on reliable keys, then perform fuzzy pairwise comparisons and cluster matching into canonical records. Validate with a labeled sample and metrics.

Steps & pandas techniques:1. Normalize: lower()/str.strip(), remove punctuation, standardize phone with regex, parse names with .str.split().2. Create blocking keys to reduce comparisons (e.g., email domain, phone last 7 digits, soundex/metaphone of last name). Use pandas groupby on those keys to produce candidate pairs.3. Pairwise comparison: within blocks, compute similarity scores (name: token_set_ratio, email exact or levenshtein, address: Jaro-Winkler).4. Cluster matches into entities using connected components (networkx) or hierarchical clustering.

Example code snippets:

python

# normalize
df['email'] = df['email'].str.lower().str.strip()
df['phone'] = df['phone'].str.replace(r'\D','',regex=True).str[-10:]
df['name_norm'] = df['name'].str.lower().str.replace(r'[^a-z ]','',regex=True)
# blocking key
df['block'] = df['email'].str.split('@').str[1].fillna('')  # domain block

External libraries:- recordlinkage for blocking, compare API and indexing- rapidfuzz or thefuzz for fast fuzzy string scores- python-Levenshtein for speed- dedupe (active learning) for complex probabilistic matching- networkx or scipy.cluster for clustering matched pairs

Canonicalization:- For each cluster, choose canonical values by recency, frequency, or completeness (non-null > null, most frequent email, newest timestamp).

Validation:- Create a stratified labeled sample of candidate pairs and label true/false matches.- Compute precision, recall, F1 at chosen threshold(s). Plot precision-recall curve to pick operating point.- Review false positives/negatives manually to adjust blocking/weights.- Monitor post-deployment via downstream metrics (duplicate rate over time, number of merges per week) and periodic re-evaluation.

Why this works:- Blocking keeps cost manageable; fuzzy comparisons capture partial/typo matches; clustering consolidates transitive matches. Validation with labeled data ensures measurable accuracy and supports iteration.

MediumTechnical

64 practiced

You need to create a reusable pandas data processing function that processes multiple input file formats (CSV, JSON, parquet). Design a small API for a function process_file(path, file_type=None, **options) that returns a cleaned DataFrame. Show example implementation outline and explain how you'd unit test and document supported options.

Sample Answer

Approach: Provide a single, well-documented function that detects or accepts file type, reads into pandas, applies configurable cleaning steps (schema coercion, drop/rename, fill/convert types, dedupe), and returns a cleaned DataFrame. Make behavior deterministic and testable via options.

python

import pandas as pd
from typing import Optional, Dict

def process_file(path: str, file_type: Optional[str]=None, *,
                 dtype: Optional[Dict[str,str]]=None,
                 parse_dates: Optional[list]=None,
                 drop_columns: Optional[list]=None,
                 rename_columns: Optional[Dict[str,str]]=None,
                 fillna: Optional[Dict[str,object]]=None,
                 drop_duplicates: bool=True,
                 sample_n: Optional[int]=None,
                 read_opts: Optional[Dict]=None) -> pd.DataFrame:
    """
    Read file (csv/json/parquet) and apply standard cleaning.
    Supported options documented below.
    """
    # infer file_type
    if file_type is None:
        file_type = path.split('.')[-1].lower()
    read_opts = read_opts or {}

    if file_type in ('csv','txt'):
        df = pd.read_csv(path, dtype=dtype, parse_dates=parse_dates, **read_opts)
    elif file_type == 'json':
        df = pd.read_json(path, **read_opts)
    elif file_type == 'parquet':
        df = pd.read_parquet(path, **read_opts)
    else:
        raise ValueError(f"Unsupported file_type: {file_type}")

    if sample_n:
        df = df.sample(n=sample_n, random_state=0)

    if rename_columns:
        df = df.rename(columns=rename_columns)
    if drop_columns:
        df = df.drop(columns=[c for c in drop_columns if c in df.columns])
    if fillna:
        df = df.fillna(fillna)
    if dtype:
        df = df.astype(dtype, errors='ignore')
    if drop_duplicates:
        df = df.drop_duplicates()

    return df

Key points:- Options: dtype, parse_dates, drop_columns, rename_columns, fillna, drop_duplicates, sample_n, read_opts.- Keep read_opts to pass engine-specific args (e.g., compression).

Unit testing:- Use pytest + tmp_path. Create small CSV/JSON/parquet fixtures, call process_file with combinations; assert schema, nulls handled, duplicates removed, date types parsed.- Test unsupported file_type raises ValueError.- Test read_opts behavior (e.g., compression).- Use parametrized tests to cover formats and options.

Documentation:- Provide docstring with option descriptions and examples.- Maintain a short Markdown section in repo: supported formats, examples, recommended defaults, and examples for common transformations.- Note performance considerations (use chunksize for very large CSVs) and when to defer dtype coercion.

EasyTechnical

80 practiced

Using Python and pandas, write code to read a CSV file named 'sales.csv' with columns: order_id (int), order_date (YYYY-MM-DD), customer_id (int), amount (float). Show how you would: a) parse order_date as datetime, b) enforce dtypes for ids, c) handle malformed lines and custom NA tokens, and d) load the file safely if it contains mixed encodings. Explain the key read_csv parameters you chose.

Sample Answer

To robustly load sales.csv with pandas while parsing dates, enforcing dtypes, handling malformed lines and custom NA tokens, and tolerating mixed encodings:

1) Approach: use pd.read_csv with parse_dates, dtype hints, custom na_values, on_bad_lines to skip or warn, and try a safe encoding fallback; then coerce types with to_numeric/to_datetime to ensure enforcement.

python

import pandas as pd

file = "sales.csv"

# preferred read options
read_opts = dict(
    parse_dates=["order_date"],          # a) parse order_date as datetime
    dayfirst=False,                      # adjust if dates are D/M/Y
    na_values=["", "NA", "N/A", "null"], # c) custom NA tokens
    keep_default_na=True,
    on_bad_lines="skip",                 # c) skip malformed lines safely (or 'warn')
    low_memory=False                     # avoid dtype guessing across chunks
)

# Try utf-8 with BOM handling, fall back to latin-1 if needed (mixed encodings)
for enc in ("utf-8-sig", "utf-8", "latin-1"):
    try:
        df = pd.read_csv(file, encoding=enc, **read_opts)
        break
    except UnicodeDecodeError:
        continue

# b) enforce dtypes for ids and amount, coercing errors to NaN then handling
df["order_id"] = pd.to_numeric(df["order_id"], errors="coerce").astype("Int64")
df["customer_id"] = pd.to_numeric(df["customer_id"], errors="coerce").astype("Int64")
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")

# Ensure order_date parsed; coerce invalids
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce", format="%Y-%m-%d")

# Optionally drop or report rows with critical missing data
invalid = df[df[["order_id","order_date","customer_id","amount"]].isna().any(axis=1)]
# report or drop:
# df = df.dropna(subset=["order_id","order_date","customer_id","amount"])

Key read_csv params chosen:- parse_dates: auto-convert to datetime.- na_values/keep_default_na: recognize custom missing tokens.- on_bad_lines="skip": avoids exceptions on malformed lines (alternative: "warn" or custom handler).- low_memory=False: prevents mixed-type inference across chunks.- encoding: try utf-8-sig and fall back to latin-1 to handle mixed BOM/legacy encodings.- errors="coerce" with to_numeric/to_datetime: ensures invalid values become NaN so you can handle them explicitly.

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.