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.

MediumTechnical

53 practiced

Show an example of composing a pandas ETL pipeline using method chaining and .pipe for readability and testability. Include steps: read from CSV, filter rows, impute missing values, create features, and write out partitioned parquet. Explain how you would unit-test each independent function used in the pipeline.

Sample Answer

Pattern: build small testable functions and compose with method-chaining and .pipe. Each function is unit-testable.

python

import pandas as pd

def read_csv(path):
    return pd.read_csv(path, parse_dates=['event_time'])

def filter_rows(df):
    return df[df['event_time'].notna() & (df['revenue']>0)]

def impute(df):
    df['country'] = df['country'].fillna('unknown')
    df['revenue'] = df['revenue'].fillna(0)
    return df

def create_features(df):
    df['month'] = df['event_time'].dt.to_period('M')
    df['rev_per_item'] = df['revenue']/df['items'].replace(0,1)
    return df

(df
 .pipe(read_csv, 'input.csv')
 .pipe(filter_rows)
 .pipe(impute)
 .pipe(create_features)
 .to_parquet('out/', partition_cols=['month','country'], engine='pyarrow')
)

Unit testing: test each function with small DataFrames asserting expected columns/values (e.g., impute fills nulls, create_features computes rev_per_item). Use pytest fixtures and parametrized cases for edge conditions. Benefits: functions are isolated, easy to mock IO, and maintainable.

EasyTechnical

68 practiced

As a data engineer, how would you estimate a DataFrame's memory footprint? Show pandas code using df.info(memory_usage='deep') and df.memory_usage(deep=True), and demonstrate converting object columns to 'category' with an example before-and-after memory comparison. When are categoricals not appropriate?

Sample Answer

Estimate memory footprint using df.info and memory_usage(deep=True). Convert heavy object cols to category and compare before/after.

python

# before
df.info(memory_usage='deep')
print(df.memory_usage(deep=True))

# example conversion
obj_cols = [c for c in df.select_dtypes(include='object').columns]
df2 = df.copy()
for c in obj_cols:
    df2[c] = df2[c].astype('category')

# after
df2.info(memory_usage='deep')
print(df2.memory_usage(deep=True))

Explanation: df.info(memory_usage='deep') gives total deep memory including object internals. df.memory_usage(deep=True) returns per-column bytes. Converting object columns to category replaces repeated Python objects with integer codes + small category index, drastically reducing memory when cardinality << row count.

When categoricals are not appropriate:- High-cardinality columns (unique per row) — no savings and overhead of categories- When you need frequent new unseen values streaming in (categories require re-categorization)- When string values require heavy vectorized string ops where categories complicate pipeline

Measure and test before applying globally.

EasyTechnical

74 practiced

Given a DataFrame df with columns ['user_id','age','country','signup_date'], write pandas code to filter rows where country == 'US' and age is between 18 and 35. Provide two approaches: (a) boolean indexing and (b) using DataFrame.query(). Show how to bind external variables into query and mention pros/cons of each approach for readability and performance.

Sample Answer

**Goal**: filter country == 'US' and 18 <= age <= 35. Show boolean indexing and query with external variables.

**(a) Boolean indexing**

python

filtered = df[(df['country']=='US') & (df['age'] >= 18) & (df['age'] <= 35)]

**(b) DataFrame.query()**

python

min_age, max_age = 18, 35
country = 'US'
filtered = df.query('country == @country and age >= @min_age and age <= @max_age')

**Pros/cons**: Boolean indexing is explicit and easy to debug; it's slightly faster in simple cases and plays well with complex combined masks. query is concise and readable for many filters and allows binding Python variables via @ — helpful in parameterized ETL code. However query strings can be harder to lint/trace and may fail with column names containing spaces or conflicting names; boolean masks are safer in robust pipelines. Performance differences are minor; prefer readability and consistency across the codebase.

HardTechnical

63 practiced

Write pandas code to join billing.csv and accounting.csv on ['invoice_id'] to produce a reconciliation report containing: matched rows, billing-only rows, accounting-only rows, and rows with amount discrepancies greater than $0.01. Use merge(..., indicator=True) and show how to compute summary metrics and export detailed discrepancies to partitioned Parquet for downstream review.

Sample Answer

**Approach**Load both CSVs into DataFrames, merge with indicator to separate matched, billing-only, accounting-only, then compute amount discrepancies (absolute diff > 0.01). Export detailed discrepancy rows partitioned by year/month or status to Parquet for review and compute summary metrics.

**Code**

python

import pandas as pd

bill = pd.read_csv('billing.csv')
acct = pd.read_csv('accounting.csv')

# standardize numeric
bill['amount'] = pd.to_numeric(bill['amount'], errors='coerce')
acct['amount'] = pd.to_numeric(acct['amount'], errors='coerce')

merged = bill.merge(acct, on='invoice_id', how='outer', suffixes=('_bill','_acct'), indicator=True)

matched = merged[merged['_merge']=='both']
billing_only = merged[merged['_merge']=='left_only']
accounting_only = merged[merged['_merge']=='right_only']

# amount discrepancies
matched['amt_diff'] = (matched['amount_bill'] - matched['amount_acct']).abs()
discrepancies = matched[matched['amt_diff'] > 0.01]

# summary metrics
summary = {
    'total_invoices': merged['invoice_id'].nunique(),
    'matched': len(matched),
    'billing_only': len(billing_only),
    'accounting_only': len(accounting_only),
    'discrepancies': len(discrepancies)
}
print(summary)

# export detailed discrepancies partitioned by status and year/month if invoice_date exists
if 'invoice_date' in merged.columns:
    merged['invoice_date'] = pd.to_datetime(merged['invoice_date'], errors='coerce')
    merged['year'] = merged['invoice_date'].dt.year
    merged['month'] = merged['invoice_date'].dt.month

# write parquet partitions
for df, name in [(matched,'matched'), (billing_only,'billing_only'), (accounting_only,'accounting_only'), (discrepancies,'discrepancies')]:
    path = f'out/{name}'
    df.to_parquet(path, index=False)

**Notes**- Validate invoice_id uniqueness or handle duplicates explicitly (aggregate sums).- Use decimal or scaled integers to avoid floating drift when money precision is critical.- For large data, write partitioned parquet by year/month and use fastparquet or pyarrow with partition_cols argument for efficient downstream reads.

EasyTechnical

72 practiced

Describe how to read a very large CSV file (tens of GB) that cannot fit into memory using pandas. Provide a code sketch using pd.read_csv with chunksize to compute a streaming aggregate, such as total sales per user_id, and then write the results incrementally to disk or S3 in partitioned Parquet format.

Sample Answer

Strategy: stream CSV with chunksize, aggregate per-chunk into an on-disk accumulator (or incremental Parquet per partition), then write partitions to disk/S3.

python

import pandas as pd
from collections import defaultdict
import pyarrow as pa

chunksize = 10_000_00
agg = defaultdict(float)
for chunk in pd.read_csv('large.csv', chunksize=chunksize, parse_dates=['event_time']):
    # compute per-chunk sum sales per user
    chunk_agg = chunk.groupby('user_id')['sales'].sum()
    for uid, val in chunk_agg.items():
        agg[uid] += float(val)

# turn into DataFrame and write partitioned parquet
out = pd.Series(agg).reset_index()
out.columns = ['user_id','total_sales']
out.to_parquet('s3://bucket/path/total_sales.parquet', partition_cols=['user_id'])

Better pattern: write intermediate per-chunk Parquet partitioned by hash(user_id % N) to S3, then run a final distributed aggregation (e.g., Dask, Spark or a second pass) to combine partitions. This avoids keeping entire aggregation in memory.

Notes: set dtype hints in read_csv, use compression, and consider using fastparquet/pyarrow. For S3, use boto3 credentials or fsspec for streaming writes.

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.