Skip to content
Machine Learning & Data Science

Pandas interview questions and answers

45 questions, 45 with code. About 2 h 27 min to read them all.

By TestLab · Updated

Plan for an interview
45 questions

Track what you know and what to revise.

Sign in to track progress

You can open a few full answers without signing in. Sign in to read all

1

What is pandas and why is it used for data analysis?

pandas is the standard open-source Python library for working with tabular and time-series data: it provides the DataFrame (a table of named, typed columns with a row index) and the Series (one labelled column), plus fast, vectorized tools to load data from CSV, Excel, SQL, JSON and Parquet, clean it, filter and transform it, group and aggregate it, join tables, reshape them, handle dates and missing values, and export results. It is used for data analysis because it turns tasks that would take many loops in plain Python into one readable line (df.groupby("city")["sales"].sum()), builds on NumPy for speed, aligns data by labels automatically so mismatched rows don’t get silently mixed up, and plugs into the rest of the ecosystem: Matplotlib and seaborn for plots, scikit-learn for models, and Arrow, Polars or Dask when data gets larger. Its limits are that a DataFrame lives in memory on one machine, and very large data needs chunking or other tools.

Read full answer

Applies topandas 2.2+

What pandas Gives You

Need pandas tool
Load and save data read_csv, read_parquet, read_sql, to_csv, to_parquet
Select and filter .loc, .iloc, boolean masks, query
Clean isna, fillna, dropna, drop_duplicates, astype
Transform assign, vectorized arithmetic, .str, .dt
Summarize describe, groupby().agg(), pivot_table, value_counts
Combine merge, concat, join
Reshape melt, pivot, stack, unstack
Time series DatetimeIndex, resample, rolling, shift

Code Example

import pandas as pd

sales = pd.DataFrame({
    "date": pd.to_datetime(["2026-01-05", "2026-01-05", "2026-01-06", "2026-01-07"]),
    "city": ["Pune", "Delhi", "Pune", "Delhi"],
    "units": [3, 5, 2, 4],
    "price": [100.0, 80.0, 100.0, 80.0],
})
sales = sales.assign(revenue=sales["units"] * sales["price"])   # vectorized: no loop

print(sales.groupby("city")["revenue"].sum().to_dict())
# {'Delhi': 720.0, 'Pune': 500.0}

print(sales.loc[sales["revenue"] > 300, ["date", "city", "revenue"]])
#         date   city  revenue
# 1 2026-01-05  Delhi    400.0
# 3 2026-01-07  Delhi    320.0

print(sales["units"].describe()[["mean", "max"]].to_dict())
# {'mean': 3.5, 'max': 5.0}

Common Pitfalls

  • Writing Python loops (iterrows) where a vectorized expression would be much faster.

  • Chained assignment like df[df.a > 0]["b"] = 1, which may not modify df; use .loc.

  • Loading data that is larger than memory into one DataFrame.

Interview Tips

  • Define DataFrame and Series and list the main tasks pandas handles.

  • Mention vectorization, label alignment and the ecosystem.

2

What is the difference between a Series and a DataFrame?

A Series is a one-dimensional labelled array: a sequence of values of one dtype with an index giving each value a label, like a single column of a spreadsheet; a DataFrame is a two-dimensional labelled table made of several Series that share the same row index, one per column, and each column can have its own dtype. Selecting one column from a DataFrame with df["col"] returns a Series, while df[["col"]] (a list) returns a one-column DataFrame. Both align on labels in arithmetic, both support vectorized operations, and many methods exist on both (sum, mean, sort_values), but DataFrame methods usually take an axis argument to work down rows (axis=0, per column) or across columns (axis=1, per row).

Read full answer

Applies topandas 2.2+

Comparison

Series DataFrame
Dimensions 1-D 2-D
Labels Index Index (rows) + columns
dtype One One per column
Created from List, array, dict, scalar Dict of lists/Series, list of dicts, 2-D array
.shape (n,) (n_rows, n_cols)
Column selection — df["a"] → Series, df[["a"]] → DataFrame
Aggregation result Scalar Series (one value per column)

Code Example

import pandas as pd

prices = pd.Series([250.0, 120.5, 99.0], index=["pen", "book", "cup"], name="price")
print(prices["book"], prices.shape, prices.dtype)
# 120.5 (3,) float64

df = pd.DataFrame({
    "price": [250.0, 120.5, 99.0],
    "stock": [10, 0, 25],
}, index=["pen", "book", "cup"])
print(df.shape, df.dtypes.to_dict())
# (3, 2) {'price': dtype('float64'), 'stock': dtype('int64')}

col = df["stock"]
print(type(col).__name__, type(df[["stock"]]).__name__)
# Series DataFrame

print(df.sum().to_dict())          # one value per column
# {'price': 469.5, 'stock': 35.0}

row = df.loc["cup"]                # a row is also a Series (upcast to float here)
print(row.to_dict())
# {'price': 99.0, 'stock': 25.0}

df.sum() returns a Series whose index is the column names; because its two values share one dtype, 35 is shown as 35.0. Selecting the “cup” row also gives a float Series, since a row combines a float column with an int column.

Common Pitfalls

  • Expecting df["col"] to be a DataFrame and calling DataFrame-only methods on it.

  • Forgetting that taking a row of a mixed-dtype DataFrame upcasts values (or makes them object dtype).

  • Confusing axis=0 (down the rows, per column) with axis=1 (across columns, per row).

Interview Tips

  • Describe a DataFrame as a dict of Series sharing an index.

  • Mention dtypes per column and df["a"] vs df[["a"]].

3

How do you read and write CSV files with pandas?

Read a CSV with pd.read_csv(path) and write one with df.to_csv(path, index=False); the important part is the options that make the result correct and fast: usecols to load only needed columns, dtype to set column types (and avoid mixed-type columns), parse_dates for date columns, na_values for custom missing markers, sep, encoding and decimal for regional formats, nrows or chunksize for big files, and dtype_backend="pyarrow" or engine="pyarrow" for speed in pandas 2.x. When writing, pass index=False unless the index carries information, choose date_format and float_format if a downstream system needs them, and use compression (path.csv.gz) for large exports. For data that pandas will read again, Parquet is usually better than CSV because it keeps dtypes, is compressed and loads much faster.

Read full answer

Applies topandas 2.2+

Key Options

Option Purpose
usecols=["a", "b"] Load only some columns
dtype={"id": "string", "qty": "Int64"} Fix types, use nullable dtypes
parse_dates=["order_date"] Parse dates on read
na_values=["NA", "-", ""] Extra missing-value markers
sep=";", decimal="," European formats
encoding="utf-8" / "latin-1" Character encoding
chunksize=100_000 Iterate over large files
engine="pyarrow" Faster multi-threaded parsing
index=False (write) Don’t write the index as a column

Code Example

import io

import pandas as pd

raw = io.StringIO(
    "order_id;order_date;city;qty;price\n"
    "A1;2026-03-01;Pune;2;10,50\n"
    "A2;2026-03-02;Delhi;-;8,00\n"
    "A3;2026-03-02;Pune;1;12,25\n"
)
orders = pd.read_csv(
    raw,
    sep=";",
    decimal=",",
    na_values=["-"],
    parse_dates=["order_date"],
    dtype={"order_id": "string", "city": "category", "qty": "Int64"},
)
print(orders.dtypes.astype(str).to_dict())
# {'order_id': 'string', 'order_date': 'datetime64[ns]', 'city': 'category', 'qty': 'Int64', 'price': 'float64'}
print(orders["qty"].tolist())
# [2, <NA>, 1]

out = io.StringIO()
orders.to_csv(out, index=False, date_format="%d/%m/%Y")
print(out.getvalue().splitlines()[:2])
# ['order_id,order_date,city,qty,price', 'A1,01/03/2026,Pune,2,10.5']

orders.to_csv("orders.csv.gz", index=False)                    # compression inferred from the extension
back = pd.read_csv("orders.csv.gz", usecols=["order_id", "price"])
print(back.shape)
# (3, 2)

The nullable Int64 dtype keeps the missing quantity as <NA> while the column stays integer; plain int64 can’t hold missing values, so pandas would turn it into float.

Common Pitfalls

  • Writing the index by accident, then reading it back as an “Unnamed: 0” column.

  • Letting pandas guess dtypes on large files (mixed-type warnings, IDs with leading zeros lost); set dtype.

  • Wrong sep, decimal or encoding for files from other regions or Excel.

  • Using CSV for intermediate data that pandas will reread; Parquet keeps types.

Interview Tips

  • Name the main read_csv options and index=False.

  • Mention dtypes, chunks for big files and Parquet as a better format.

4

What are pandas indexes and how are they used?

An index is the set of labels attached to the rows of a Series or DataFrame (and the columns are an index too); it identifies each row, is what .loc uses for label-based selection, and is what pandas uses to align data automatically when you combine objects, so adding two Series matches values by label rather than by position. Indexes can be a default RangeIndex (0, 1, 2, …), meaningful labels such as product IDs, a DatetimeIndex that enables time-based selection and resampling, or a MultiIndex with several levels for hierarchical data. You create or change them with set_index, reset_index, reindex and rename; a unique, sorted index makes lookups and slicing fast. Because joins and arithmetic follow the index, an unexpected index is a common source of NaNs after operations.

Read full answer

Applies topandas 2.2+

Index Types

Type Example Typical use
RangeIndex 0, 1, 2, … Default
Index of labels “SKU1”, “SKU2” Lookups by key
DatetimeIndex 2026-01-01, … Time series, resample
CategoricalIndex Small fixed set of labels Memory savings
MultiIndex (region, year) Hierarchical data, group results

Code Example

import pandas as pd

df = pd.DataFrame({
    "sku": ["S1", "S2", "S3"],
    "stock": [5, 0, 12],
    "price": [10.0, 25.0, 4.0],
}).set_index("sku")
print(df.loc["S2", "price"])
# 25.0

restock = pd.Series({"S3": 3, "S1": 10, "S9": 7})        # different order and an extra label
print((df["stock"] + restock).to_dict())
# {'S1': 15.0, 'S2': nan, 'S3': 15.0, 'S9': nan}   alignment by label; missing labels give NaN
print(df["stock"].add(restock, fill_value=0).to_dict())
# {'S1': 15.0, 'S2': 0.0, 'S3': 15.0, 'S9': 7.0}

daily = pd.Series([3, 5, 2, 8], index=pd.date_range("2026-01-01", periods=4, freq="D"))
print(daily.loc["2026-01-02":"2026-01-03"].tolist())     # label slices include both ends
# [5, 2]

sales = pd.DataFrame({"region": ["N", "N", "S"], "year": [2025, 2026, 2026], "rev": [10, 12, 7]})
multi = sales.set_index(["region", "year"])
print(multi.loc[("N", 2026), "rev"], multi.loc["N"]["rev"].tolist())
# 12 [10, 12]

print(multi.reset_index().columns.tolist())
# ['region', 'year', 'rev']

Common Pitfalls

  • Unexpected NaNs from arithmetic between objects with different indexes.

  • Duplicate index labels, which make .loc return several rows and slow lookups.

  • Forgetting that .loc slices include the end label while .iloc slices exclude the end position.

  • Losing an index column in exports; use reset_index() or index=True deliberately.

Interview Tips

  • Explain label-based selection and automatic alignment.

  • Mention DatetimeIndex, MultiIndex and set_index/reset_index.

5

How do you handle missing data in a pandas DataFrame?

Start by finding and understanding the missing values: df.isna().sum() counts them per column, and you should ask why they are missing, because the right treatment depends on it; then choose per column between dropping (dropna, with subset or thresh), filling with a constant, statistic or group statistic (fillna, groupby().transform("median")), carrying values forward or back in time series (ffill, bfill), or interpolating (interpolate, including time-aware interpolation). Know how missing values behave: most aggregations skip NaN by default (sum, mean use skipna=True), comparisons with NaN are False, and a missing value in an integer column turns it into float unless you use nullable dtypes such as Int64, boolean and string, which use pd.NA. For modelling, impute inside a scikit-learn pipeline so test data doesn’t leak into the fill values, and add an “was missing” indicator when missingness may itself be informative.

Read full answer

Applies topandas 2.2+

Options

Method Use when
dropna() / dropna(subset=[...]) Few rows affected, or the column is essential
dropna(thresh=k) Keep rows with at least k non-missing values
fillna(value) Known default (0 items, “unknown”)
fillna(df.median()) Simple numeric imputation
groupby(...).transform("median") Fill using similar rows
ffill() / bfill() Time series where the last value persists
interpolate(method="time") Smooth numeric series over time
Nullable dtypes (Int64) Keep integers with missing values

Code Example

import numpy as np
import pandas as pd

df = pd.DataFrame({
    "city": ["Pune", "Pune", "Delhi", "Delhi", "Delhi"],
    "temp": [30.0, np.nan, 25.0, np.nan, 27.0],
    "visits": pd.array([10, None, 7, 8, None], dtype="Int64"),
})
print(df.isna().sum().to_dict())
# {'city': 0, 'temp': 2, 'visits': 2}

print(df["temp"].mean(), df["temp"].sum())               # NaN is skipped
# 27.333333333333332 82.0

filled = df.assign(
    temp=df["temp"].fillna(df.groupby("city")["temp"].transform("median")),
    visits_missing=df["visits"].isna(),
    visits=df["visits"].fillna(0),
)
print(filled["temp"].tolist(), filled["visits"].tolist())
# [30.0, 30.0, 25.0, 26.0, 27.0] [10, 0, 7, 8, 0]

ts = pd.Series([1.0, np.nan, np.nan, 4.0], index=pd.date_range("2026-01-01", periods=4, freq="D"))
print(ts.ffill().tolist(), ts.interpolate().tolist())
# [1.0, 1.0, 1.0, 4.0] [1.0, 2.0, 3.0, 4.0]

print(df.dropna(subset=["temp"]).shape, df.dropna(thresh=3).shape)
# (3, 3) (2, 3)

Delhi’s median temperature is the median of 25 and 27, which is 26, so row 3 gets 26.0. Only rows 0 and 2 have all three values, so dropna(thresh=3) keeps 2 rows.

Common Pitfalls

  • Filling with a statistic computed on the full dataset before a train/test split.

  • Dropping rows blindly, which can bias results when missingness depends on other values.

  • Integer columns silently turning into float because of NaN; use Int64.

  • Using fillna(method="ffill"), deprecated in pandas 2.x; use .ffill().

Interview Tips

  • Start with counting and understanding the cause, then choose per column.

  • Mention NaN in aggregations, nullable dtypes and leakage-free imputation.

6

How does groupby work in pandas? Give an example

groupby follows the split-apply-combine pattern: it splits the rows into groups by one or more keys, applies a function to each group, and combines the results into a new Series or DataFrame; the apply step is usually an aggregation (sum, mean, count, or several at once with agg and named aggregations), a transformation that returns a result the same shape as the input (transform, for example each row’s share of its group total), or a filter that keeps or drops whole groups (filter). Group keys become the index of the result unless you pass as_index=False or call reset_index(); categorical keys show every category unless you pass observed=True; and NaN keys are dropped unless dropna=False. Built-in aggregations are vectorized and fast, while apply with a Python function is flexible but slower, so prefer named aggregations and transform.

Read full answer

Applies topandas 2.2+

Operations

Operation Returns Example
Aggregation One row per group df.groupby("city")["sales"].sum()
Named aggregation One row per group, named columns agg(total=("sales", "sum"))
Transform Same length as the input transform("mean")
Filter Rows of qualifying groups filter(lambda g: len(g) > 1)
Apply Anything Custom per-group logic (slower)

Code Example

import pandas as pd

orders = pd.DataFrame({
    "city": ["Pune", "Delhi", "Pune", "Delhi", "Mumbai"],
    "channel": ["web", "web", "app", "app", "web"],
    "amount": [120, 80, 200, 50, 300],
})

summary = orders.groupby("city", as_index=False).agg(
    orders=("amount", "size"),
    total=("amount", "sum"),
    avg=("amount", "mean"),
)
print(summary)
#      city  orders  total    avg
# 0   Delhi       2    130   65.0
# 1  Mumbai       1    300  300.0
# 2    Pune       2    320  160.0

orders["share_of_city"] = orders["amount"] / orders.groupby("city")["amount"].transform("sum")
print(orders["share_of_city"].round(3).tolist())
# [0.375, 0.615, 0.625, 0.385, 1.0]

by_two = orders.groupby(["city", "channel"])["amount"].sum()
print(by_two.loc[("Pune", "app")], by_two.index.nlevels)
# 200 2

repeat_cities = orders.groupby("city").filter(lambda g: len(g) > 1)
print(sorted(repeat_cities["city"].unique()))
# ['Delhi', 'Pune']

Groups are sorted by key by default (Delhi, Mumbai, Pune); pass sort=False to keep first-appearance order and save time on large data.

Common Pitfalls

  • Using apply with Python loops where a built-in aggregation or transform would do.

  • Forgetting observed=True with categorical keys, which can create many empty groups.

  • Losing rows with NaN keys (default dropna=True).

  • Confusing size (counts rows, including NaN values) with count (non-missing values only).

Interview Tips

  • Explain split-apply-combine and aggregate vs transform vs filter.

  • Show named aggregation and mention as_index, observed and dropna.

7

Explain data alignment and broadcasting in pandas

Data alignment means that when pandas combines two Series or DataFrames (arithmetic, comparisons that allow it, assignment of a Series to a column), it matches values by their index and column labels, not by position: labels present in both are combined, and labels present in only one get NaN, so the result’s index is the union of the inputs’ indexes. Broadcasting means applying an operation between objects of different shapes by stretching the smaller one: a scalar applies to every element, and a Series applies across a DataFrame, matched against the columns by default (df - df.mean() subtracts each column’s mean), or against the rows when you use a method with axis=0 (df.sub(row_series, axis=0)). Alignment protects you from mixing up rows that are in a different order, but it also explains surprising NaNs; use fill_value= in add/sub/mul, reindex, or .to_numpy() when you really want positional behaviour.

Read full answer

Applies topandas 2.2+

Code Example

import pandas as pd

jan = pd.Series({"apples": 10, "pears": 4, "plums": 7})
feb = pd.Series({"pears": 6, "apples": 3, "kiwis": 5})        # different order, different labels
print((jan + feb).to_dict())
# {'apples': 13.0, 'kiwis': nan, 'pears': 10.0, 'plums': nan}
print(jan.add(feb, fill_value=0).to_dict())
# {'apples': 13.0, 'kiwis': 5.0, 'pears': 10.0, 'plums': 7.0}

df = pd.DataFrame({"math": [70, 80, 90], "art": [60, 75, 90]}, index=["ana", "ben", "cai"])
print((df - df.mean()).to_dict())                         # Series broadcast across columns
# {'math': {'ana': -10.0, 'ben': 0.0, 'cai': 10.0}, 'art': {'ana': -15.0, 'ben': 0.0, 'cai': 15.0}}

bonus = pd.Series({"cai": 5, "ana": 2, "ben": 0})        # one value per row label
print(df.add(bonus, axis=0).to_dict())                    # align on the row index instead
# {'math': {'ana': 72, 'ben': 80, 'cai': 95}, 'art': {'ana': 62, 'ben': 75, 'cai': 95}}

print((df * 1.1).round(1).loc["ana"].tolist())            # scalar broadcasting
# [77.0, 66.0]

new_col = pd.Series([1, 2, 3], index=["cai", "ben", "zed"])
df["rank"] = new_col                                      # column assignment aligns on the index
print(df["rank"].tolist())
# [nan, 2.0, 1.0]

The math column mean is 80 and the art mean is 75, which gives the centred values above. In the last assignment, “ana” has no match, so it gets NaN, and “zed” is ignored.

Rules of Thumb

Situation Behaviour
Series + Series Align on index (union)
DataFrame − Series Series aligned to columns
df.sub(s, axis=0) Series aligned to rows
DataFrame + DataFrame Align on both rows and columns
Scalar Applied to every element
Assign Series to a column Aligned to the DataFrame’s index

Common Pitfalls

  • Expecting positional matching and getting NaNs.

  • Subtracting a per-row Series with -, which aligns to the columns and fills everything with NaN.

  • Assigning a Series with a different index to a column.

Interview Tips

  • Show a two-Series example with NaN and fill_value.

  • Explain column-wise broadcasting and axis=0 for rows.

8

What is slicing in pandas and how does it differ from filtering?

Slicing selects a contiguous range of rows (or columns) by position or by label, such as the first ten rows with df.iloc[:10] or all dates from March to June with df.loc["2026-03":"2026-06"]; filtering selects rows by a condition on their values, such as df[df["price"] > 100] or df.query("city == 'Pune'"), which can pick any rows anywhere in the frame. The key rules are that .iloc slices are positional and exclude the end, like Python lists, while .loc slices use labels and include the end label (and need a sorted index for range slicing), and that filters build a boolean mask that must be combined with &, | and ~ with parentheses around each condition. Both return new objects; to change the original rows, use .loc[mask, "col"] = value rather than chained indexing.

Read full answer

Applies topandas 2.2+

Comparison

Slicing Filtering
Selects by Position or label range Condition on values
Rows chosen Contiguous block Any rows
Syntax iloc[a:b], loc["x":"y"] df[mask], loc[mask], query()
End point iloc excludes, loc includes —
Depends on order Yes No

Code Example

import pandas as pd

df = pd.DataFrame({
    "item": ["pen", "book", "cup", "lamp", "mug"],
    "price": [20, 350, 120, 900, 150],
    "stock": [100, 5, 40, 0, 25],
}, index=["a", "b", "c", "d", "e"])

print(df.iloc[1:3]["item"].tolist())          # positions 1 and 2 (end excluded)
# ['book', 'cup']
print(df.loc["b":"d", "item"].tolist())       # labels b to d (end included)
# ['book', 'cup', 'lamp']
print(df.iloc[:, :2].columns.tolist())        # column slice by position
# ['item', 'price']

mask = (df["price"] > 100) & (df["stock"] > 0)
print(df.loc[mask, "item"].tolist())
# ['book', 'cup', 'mug']
print(df.query("price > 100 and stock > 0").index.tolist())
# ['b', 'c', 'e']
print(df[df["item"].isin(["pen", "lamp"])].index.tolist())
# ['a', 'd']

df.loc[df["stock"] == 0, "status"] = "sold out"     # modify the original safely
print(df["status"].fillna("ok").tolist())
# ['ok', 'ok', 'ok', 'sold out', 'ok']

Common Pitfalls

  • Using and/or instead of &/|, or forgetting the parentheses around each condition.

  • Chained assignment (df[mask]["col"] = x), which may not change df; under copy-on-write it never does.

  • Label slicing on an unsorted index, which can raise an error or give unexpected rows.

  • Mixing up the end rules of loc and iloc.

Interview Tips

  • Define slicing as range-based and filtering as condition-based.

  • Mention end-inclusion rules and .loc[mask, col] = value.

9

How does joining and merging data work in pandas?

pd.merge (or df.merge) combines two DataFrames like a SQL join: it matches rows on key columns (on, or left_on/right_on when the names differ, or the index with left_index/right_index) and the how argument decides which rows survive: “inner” keeps keys in both, “left” keeps every left row, “right” every right row, “outer” all keys from both, and “cross” every combination. df.join is a shortcut that joins on the index, and pd.concat stacks frames vertically or side by side instead of matching keys. The main risks are duplicate keys, which multiply rows (a key appearing twice on each side produces four rows), and keys with different dtypes or spellings that silently don’t match, so use validate="one_to_one" or "many_to_one" to check the relationship, indicator=True to see where each row came from, and compare row counts before and after.

Read full answer

Applies topandas 2.2+

Join Types

how Keeps SQL
"inner" Keys in both INNER JOIN
"left" All left rows LEFT JOIN
"right" All right rows RIGHT JOIN
"outer" All keys FULL OUTER JOIN
"cross" Every pair CROSS JOIN

Code Example

import pandas as pd

customers = pd.DataFrame({"cust_id": [1, 2, 3], "name": ["Asha", "Ravi", "Meera"]})
orders = pd.DataFrame({"order_id": [10, 11, 12, 13], "cust_id": [1, 1, 3, 4], "amount": [250, 90, 400, 60]})

inner = customers.merge(orders, on="cust_id", how="inner", validate="one_to_many")
print(inner[["name", "order_id"]].values.tolist())
# [['Asha', 10], ['Asha', 11], ['Meera', 12]]

left = customers.merge(orders, on="cust_id", how="left")
print(left["amount"].tolist())          # Ravi has no orders -> NaN
# [250.0, 90.0, nan, 400.0]

outer = customers.merge(orders, on="cust_id", how="outer", indicator=True)
print(outer["_merge"].astype(str).tolist())
# ['both', 'both', 'left_only', 'both', 'right_only']

totals = orders.groupby("cust_id")["amount"].sum()
print(customers.join(totals, on="cust_id")["amount"].tolist())     # join a Series on the key column
# [340.0, nan, 400.0]

dupes = pd.DataFrame({"cust_id": [1, 1], "segment": ["gold", "silver"]})   # accidental duplicate key
try:
    customers.merge(dupes, on="cust_id", validate="one_to_one")
except pd.errors.MergeError as exc:
    print("caught:", type(exc).__name__)
# caught: MergeError

The outer join has 5 rows: Asha’s two orders, Ravi with no order, Meera’s order, and order 13 for customer 4, who isn’t in the customer table.

Common Pitfalls

  • Duplicate keys multiplying rows; check with validate and row counts.

  • Keys with different dtypes (int vs string) or whitespace, which don’t match.

  • Overlapping column names getting _x/_y suffixes; set suffixes=.

  • Using concat when a key-based merge was needed, or the reverse.

Interview Tips

  • Explain how options with a small example.

  • Mention validate, indicator and row-count checks.

10

How do you apply a function to every element of a DataFrame column?

The best way is usually not to call a function per element at all, but to use a vectorized operation on the whole column: arithmetic (df["price"] * 1.18), NumPy functions (np.log(df["x"])), the .str accessor for text (df["name"].str.title()), the .dt accessor for dates (df["date"].dt.month), and np.where, np.select, clip or pd.cut for conditional logic; these run in optimized C code and are often 10–100 times faster than Python loops. When you need a custom Python function, Series.map(func) applies it element by element (and map also accepts a dict for lookups), Series.apply(func) does the same with a few more options, and DataFrame.map(func) (named applymap before pandas 2.1) applies a function to every cell of a whole DataFrame. For a function that needs several columns from each row, prefer a vectorized expression over df.apply(func, axis=1), which loops row by row.

Read full answer

Applies topandas 2.2+

Options

Method Works on Speed Use for
Vectorized ops, NumPy Whole column Fastest Arithmetic, math
.str / .dt accessors Whole column Fast Text and dates
np.where / np.select Whole column Fast Conditional values
Series.map(dict) Elements Fast Lookups/recoding
Series.map(func) / apply(func) Elements Slow (Python) Custom logic
DataFrame.map(func) Every cell Slow Element-wise on all columns
df.apply(func, axis=1) Rows Slowest Last resort

Code Example

import numpy as np
import pandas as pd

df = pd.DataFrame({
    "name": ["  asha rao", "RAVI kumar ", "meera"],
    "price": [100.0, 250.0, 40.0],
    "size": ["S", "L", "M"],
    "joined": pd.to_datetime(["2024-01-15", "2025-06-01", "2026-02-20"]),
})

df["price_with_tax"] = df["price"] * 1.18                          # vectorized
df["name_clean"] = df["name"].str.strip().str.title()               # string accessor
df["join_year"] = df["joined"].dt.year                              # datetime accessor
df["band"] = np.where(df["price"] >= 100, "premium", "budget")      # conditional
df["size_num"] = df["size"].map({"S": 1, "M": 2, "L": 3})           # dict lookup

def shipping(price):                                                # custom Python function
    return 0.0 if price >= 200 else 49.0
df["shipping"] = df["price"].map(shipping)

print(df[["name_clean", "price_with_tax", "join_year", "band", "size_num", "shipping"]].to_dict("list"))
# {'name_clean': ['Asha Rao', 'Ravi Kumar', 'Meera'], 'price_with_tax': [118.0, 295.0, 47.199999999999996],
#  'join_year': [2024, 2025, 2026], 'band': ['premium', 'premium', 'budget'],
#  'size_num': [1, 3, 2], 'shipping': [49.0, 0.0, 49.0]}

40 × 1.18 prints as 47.199999999999996 because of binary floating point; round with .round(2) for display.

Common Pitfalls

  • df.apply(..., axis=1) or iterrows for logic that could be vectorized.

  • Using the old applymap name (deprecated in 2.1 in favour of DataFrame.map).

  • map with a dict that misses some keys, which produces NaN for them.

  • Functions that fail on NaN; handle missing values or use na_action="ignore" with map.

Interview Tips

  • Recommend vectorized operations and accessors first.

  • Explain map, apply and DataFrame.map, and why row-wise apply is slow.

11

How do you handle duplicate rows in a pandas DataFrame?

Find duplicates with df.duplicated(), which marks each row that repeats an earlier one (all columns by default, or only the columns in subset), count them with df.duplicated().sum(), inspect them with df[df.duplicated(keep=False)] to see every copy, and remove them with df.drop_duplicates(), choosing keep="first", "last" or False (drop all copies). Decide what “duplicate” means for the data: exact repeated rows from a double import, or the same business key (same order ID, same customer and timestamp) with slightly different values, where you should keep the latest version by sorting first (sort_values("updated_at").drop_duplicates("order_id", keep="last")) or aggregate the copies with groupby. Normalize keys before checking (trim whitespace, lowercase emails), because “a@x.com ” and “A@x.com” are not duplicates to pandas.

Read full answer

Applies topandas 2.2+

Options

Task Code
Flag repeats df.duplicated()
Only some columns df.duplicated(subset=["email"])
Show all copies df[df.duplicated(subset=[...], keep=False)]
Remove repeats df.drop_duplicates()
Keep the latest per key sort_values(...).drop_duplicates(key, keep="last")
Combine copies groupby(key).agg(...)

Code Example

import pandas as pd

users = pd.DataFrame({
    "email": ["a@x.com", "b@x.com", "a@x.com", "A@x.com ", "c@x.com"],
    "plan": ["free", "pro", "free", "pro", "free"],
    "updated": pd.to_datetime(["2026-01-01", "2026-01-02", "2026-01-01", "2026-02-01", "2026-01-03"]),
})

print(users.duplicated().tolist())                 # exact repeats of an earlier row
# [False, False, True, False, False]
print(len(users.drop_duplicates()))
# 4

users["email_norm"] = users["email"].str.strip().str.lower()
print(users.duplicated(subset=["email_norm"], keep=False).tolist())   # every copy of a repeated key
# [True, False, True, True, False]

latest = (users.sort_values("updated")
               .drop_duplicates(subset=["email_norm"], keep="last")
               .sort_index())
print(latest[["email_norm", "plan"]].values.tolist())
# [['b@x.com', 'pro'], ['a@x.com', 'pro'], ['c@x.com', 'free']]

After normalization, rows 0, 2 and 3 are the same user; keeping the latest by updated keeps row 3, the “pro” plan from February.

Common Pitfalls

  • Removing duplicates before normalizing keys, so near-duplicates survive.

  • Using keep="first" without sorting, which keeps an arbitrary version.

  • Dropping legitimate repeated events (two real purchases of the same item) because all columns match; include an ID or timestamp in the check.

Interview Tips

  • Show duplicated, drop_duplicates, subset and keep.

  • Mention normalization and keeping the latest record per key.

12

How do you convert categorical data to numeric format in pandas?

The right conversion depends on the kind of category and the model: for nominal categories with no order (city, colour), use one-hot encoding with pd.get_dummies(df, columns=[...]) or scikit-learn’s OneHotEncoder (which remembers the categories seen in training and can ignore unknown ones); for ordinal categories with a real order (size S < M < L, education level), map them to ordered integers with a dict or an ordered CategoricalDtype and .cat.codes; and for high-cardinality columns (thousands of product IDs), use frequency encoding, target encoding with cross-fitting, or hashing. The category dtype’s .cat.codes gives integer codes for any category column, but those codes are arbitrary for nominal data, so a linear model would read a fake order into them; tree models tolerate integer codes better. In production, do the encoding inside a scikit-learn pipeline (ColumnTransformer) so training and new data get identical columns.

Read full answer

Applies topandas 2.2+, scikit-learn 1.5+

Methods

Method For Code
One-hot Nominal, few levels pd.get_dummies, OneHotEncoder
Ordinal mapping Ordered levels map({...}), ordered CategoricalDtype
Category codes Trees, compact storage astype("category").cat.codes
Frequency encoding Many levels map(value_counts(normalize=True))
Target encoding Many levels, supervised TargetEncoder (cross-fitted)
Hashing Huge or open-ended sets FeatureHasher

Code Example

import pandas as pd

df = pd.DataFrame({
    "city": ["Pune", "Delhi", "Pune", "Chennai"],
    "size": ["M", "S", "L", "M"],
})

onehot = pd.get_dummies(df, columns=["city"], prefix="city", dtype=int)
print(onehot.columns.tolist())
# ['size', 'city_Chennai', 'city_Delhi', 'city_Pune']
print(onehot["city_Pune"].tolist())
# [1, 0, 1, 0]

size_type = pd.CategoricalDtype(categories=["S", "M", "L"], ordered=True)
df["size_code"] = df["size"].astype(size_type).cat.codes
print(df["size_code"].tolist())
# [1, 0, 2, 1]

df["city_freq"] = df["city"].map(df["city"].value_counts(normalize=True))
print(df["city_freq"].tolist())
# [0.5, 0.25, 0.5, 0.25]

codes = df["city"].astype("category").cat.codes      # alphabetical categories: Chennai=0, Delhi=1, Pune=2
print(codes.tolist())
# [2, 1, 2, 0]

Pipeline Version for Models

import pandas as pd
from sklearn.compose import ColumnTransformer
from sklearn.preprocessing import OneHotEncoder, OrdinalEncoder

train = pd.DataFrame({"city": ["Pune", "Delhi"], "size": ["S", "L"]})
new = pd.DataFrame({"city": ["Goa"], "size": ["M"]})          # unseen city

encoder = ColumnTransformer([
    ("city", OneHotEncoder(handle_unknown="ignore"), ["city"]),
    ("size", OrdinalEncoder(categories=[["S", "M", "L"]]), ["size"]),
]).fit(train)
print(encoder.transform(new).tolist())
# [[0.0, 0.0, 1.0]]   unknown city -> all zeros; M -> 1

OneHotEncoder returns a sparse matrix when the output is mostly zeros; here the output is small, so ColumnTransformer returns a dense array.

Common Pitfalls

  • Label-encoding nominal categories for linear models, which invents an order.

  • get_dummies on train and test separately, giving different columns; use OneHotEncoder.

  • Target encoding without cross-fitting, which leaks the target.

  • One-hot encoding thousands of levels into a huge, sparse table.

Interview Tips

  • Distinguish nominal from ordinal and match the method.

  • Mention pipelines, unseen categories and high-cardinality options.

13

How can you pivot data in a pandas DataFrame?

Pivoting turns long data (one row per observation, such as date, product, sales) into a wide table where the values of one column become new column headers: df.pivot(index="date", columns="product", values="sales") does this when each index/column pair occurs only once, and df.pivot_table(index=..., columns=..., values=..., aggfunc="sum") does it while aggregating duplicates, with options for fill_value, multiple aggregation functions and grand totals (margins=True). The reverse operation, wide to long, is melt, and stack/unstack pivot between the index and columns of an existing MultiIndex. Use pivot when the data is guaranteed unique (it raises an error on duplicates, which is a useful check) and pivot_table for reporting and summaries.

Read full answer

Applies topandas 2.2+

pivot vs pivot_table

pivot pivot_table
Duplicates Raises ValueError Aggregated with aggfunc
Aggregation None mean (default), sum, count, several
Missing cells NaN NaN or fill_value
Totals No margins=True

Code Example

import pandas as pd

sales = pd.DataFrame({
    "month": ["Jan", "Jan", "Jan", "Feb", "Feb"],
    "region": ["North", "South", "North", "North", "South"],
    "product": ["tea", "tea", "coffee", "tea", "coffee"],
    "units": [10, 4, 6, 8, 5],
})

table = sales.pivot_table(index="month", columns="product", values="units",
                          aggfunc="sum", fill_value=0, margins=True, margins_name="Total")
print(table)
# product  coffee  tea  Total
# month
# Feb           5    8     13
# Jan           6   14     20
# Total        11   22     33

by_region = sales.pivot_table(index="region", columns="month", values="units", aggfunc=["sum", "count"])
print(by_region[("sum", "Jan")].to_dict(), by_region[("count", "Feb")].to_dict())
# {'North': 16, 'South': 4} {'North': 1, 'South': 1}

unique_rows = sales[sales["region"] == "North"]                   # month+product unique for North
wide = unique_rows.pivot(index="month", columns="product", values="units")
print(wide.to_dict())
# {'coffee': {'Feb': nan, 'Jan': 6.0}, 'tea': {'Feb': 8.0, 'Jan': 10.0}}

try:
    sales.pivot(index="month", columns="product", values="units")   # Jan/tea appears twice
except ValueError as exc:
    print("pivot refused duplicates:", "duplicate" in str(exc))
# pivot refused duplicates: True

long_again = wide.reset_index().melt(id_vars="month", value_name="units").dropna()
print(len(long_again))
# 3

Rows and columns are sorted alphabetically (Feb before Jan); reorder with reindex(["Jan", "Feb"]) or use an ordered categorical for months.

Common Pitfalls

  • Using pivot on data with duplicates (error) or pivot_table without noticing it averaged duplicates by default.

  • Forgetting that the default aggfunc is the mean, not the sum.

  • Alphabetical order of months or other ordered labels.

  • Wide tables with MultiIndex columns that are awkward to export; flatten them.

Interview Tips

  • Contrast pivot and pivot_table and their duplicate handling.

  • Mention melt as the inverse, fill_value and margins.

14

How do you use where() to apply conditional logic to columns?

Series.where(cond, other) keeps each value where the condition is True and replaces it with other where the condition is False (NaN if other is not given), and Series.mask(cond, other) is the opposite, replacing values where the condition is True; both are vectorized, keep the original index and shape, and work on whole DataFrames too. They are ideal for “keep or replace” logic such as capping outliers, blanking invalid readings or substituting defaults. For choosing between two new values, NumPy’s np.where(cond, a, b) is the usual tool (note that its argument meaning differs: it returns a where True and b where False), and for many conditions use np.select(conditions, choices, default) or pd.cut for numeric bins. All of these beat row-wise apply with if/else.

Read full answer

Applies topandas 2.2+, NumPy 2.x

Comparison

Tool Behaviour
s.where(cond, other) Keep where True, replace where False
s.mask(cond, other) Replace where True, keep where False
np.where(cond, a, b) a where True, b where False (new array)
np.select([c1, c2], [v1, v2], default) First matching condition wins
s.clip(lower, upper) Cap values to a range
pd.cut(s, bins, labels) Numeric ranges to labels

Code Example

import numpy as np
import pandas as pd

df = pd.DataFrame({
    "sensor": ["a", "b", "c", "d", "e"],
    "temp": [21.5, -999.0, 35.0, 18.0, 52.0],
    "score": [45, 82, 67, 91, 30],
})

df["temp_clean"] = df["temp"].where(df["temp"] > -100)            # invalid readings -> NaN
print(df["temp_clean"].tolist())
# [21.5, nan, 35.0, 18.0, 52.0]

df["temp_capped"] = df["temp_clean"].mask(df["temp_clean"] > 40, 40.0)
print(df["temp_capped"].tolist())
# [21.5, nan, 35.0, 18.0, 40.0]

df["result"] = np.where(df["score"] >= 50, "pass", "fail")
print(df["result"].tolist())
# ['fail', 'pass', 'pass', 'pass', 'fail']

conditions = [df["score"] >= 85, df["score"] >= 65, df["score"] >= 50]
df["grade"] = np.select(conditions, ["A", "B", "C"], default="F")
print(df["grade"].tolist())
# ['F', 'B', 'B', 'A', 'F']

numbers = df[["temp", "score"]]
print(numbers.where(numbers > 0, 0).to_dict("list"))              # DataFrame-wide
# {'temp': [21.5, 0.0, 35.0, 18.0, 52.0], 'score': [45, 82, 67, 91, 30]}

Common Pitfalls

  • Mixing up the meaning of where (keep where True) and np.where (choose between two values).

  • Forgetting that NaN in a condition counts as False in where.

  • Using apply with if/else per row for something these tools do in one line.

  • np.select conditions in the wrong order (the first match wins).

Interview Tips

  • Explain where/mask as keep-or-replace and np.where/np.select for choices.

  • Give a practical example such as cleaning sensor readings.

15

What is the purpose of apply() in pandas?

apply() runs a function along an axis of a DataFrame or over the values of a Series: Series.apply(func) calls func on each element, DataFrame.apply(func) calls it on each column (axis=0, the default) and DataFrame.apply(func, axis=1) on each row, and groupby(...).apply(func) on each group; the results are combined into a Series or DataFrame. It is the flexible fallback for logic that has no vectorized equivalent, such as calling an external parser, combining several columns with complex rules, or a custom per-group calculation. The cost is speed: apply calls a Python function once per element, row or group, so it is often 10–100 times slower than vectorized arithmetic, .str and .dt methods, np.where/np.select, map with a dict, or built-in groupby aggregations, which should be the first choice. Use apply with axis=1 only when you can’t express the logic column-wise.

Read full answer

Applies topandas 2.2+

Forms

Call Function receives Typical use
s.apply(f) Each element Custom element logic
df.apply(f) Each column (Series) Column-wise statistics
df.apply(f, axis=1) Each row (Series) Multi-column logic (slow)
df.apply(f, result_type="expand") Row → several values Split into columns
df.groupby(k).apply(f) Each group (DataFrame) Custom per-group results

Code Example

import numpy as np
import pandas as pd

df = pd.DataFrame({
    "item": ["pen", "lamp", "cup"],
    "price": [20.0, 900.0, 120.0],
    "qty": [10, 1, 3],
    "category": ["office", "home", "home"],
})

print(df[["price", "qty"]].apply(lambda col: col.max() - col.min()).to_dict())   # per column
# {'price': 880.0, 'qty': 9.0}

def discount(row):                                 # row-wise rule (works, but slow on big data)
    if row["category"] == "home" and row["price"] > 500:
        return row["price"] * 0.9
    return row["price"]

df["final_apply"] = df.apply(discount, axis=1)
df["final_vector"] = np.where((df["category"] == "home") & (df["price"] > 500), df["price"] * 0.9, df["price"])
print(df["final_apply"].tolist(), df["final_apply"].equals(df["final_vector"]))
# [20.0, 810.0, 120.0] True

print(df.groupby("category")[["price", "qty"]].apply(lambda g: (g["price"] * g["qty"]).sum()).to_dict())
# {'home': 1260.0, 'office': 200.0}

The per-column result shows qty as 9.0 because both results share one Series, which is float. The home revenue is 900·1 + 120·3 = 1260.

Common Pitfalls

  • Using apply(axis=1) or iterrows for logic that vectorizes easily.

  • Assuming apply is parallel; it runs in a single Python loop.

  • Returning inconsistent types from the function, which gives object dtype results.

  • groupby().apply including grouping columns in the passed frame (select columns explicitly).

Interview Tips

  • Describe element, column, row and group forms.

  • Stress that vectorized alternatives come first and why apply is slow.

16

How do you reshape a DataFrame with stack and unstack?

stack() moves the innermost column level into the row index, turning a wide DataFrame into a longer Series (or DataFrame) with a MultiIndex, and unstack() does the reverse, moving the innermost row index level (or a level you name) up into the columns to make the data wider; they are inverse operations and the basis of pivot and melt. They are most useful with MultiIndex data, such as the result of groupby(["region", "year"]).sum(), where unstack("year") gives a region × year table ready for plotting or reporting. unstack fills missing combinations with NaN unless you pass fill_value, and in pandas 2.1+ stack(future_stack=True) uses the new implementation that keeps NaN rows and will be the default in pandas 3.0.

Read full answer

Applies topandas 2.2+

Summary

Method Moves Shape change
stack() Columns → innermost index level Wider → longer
unstack() Innermost index level → columns Longer → wider
unstack(level="name") A chosen level → columns —
unstack(fill_value=0) Same, filling gaps —

Code Example

import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "North", "South", "South", "East"],
    "year": [2025, 2026, 2025, 2026, 2026],
    "revenue": [100, 120, 80, 95, 40],
})
grouped = sales.groupby(["region", "year"])["revenue"].sum()      # Series with a 2-level index
print(grouped.index.nlevels, len(grouped))
# 2 5

wide = grouped.unstack("year", fill_value=0)
print(wide)
# year    2025  2026
# region
# East       0    40
# North    100   120
# South     80    95

long = wide.stack(future_stack=True)
print(long.loc[("North", 2026)], len(long))
# 120 6

by_region = grouped.unstack(level=0)                              # regions become columns
print(by_region.columns.tolist(), by_region.loc[2025].tolist())
# ['East', 'North', 'South'] [nan, 100.0, 80.0]

back = wide.stack(future_stack=True).rename("revenue").reset_index()
print(back.columns.tolist())
# ['region', 'year', 'revenue']

After fill_value=0, East has a row for 2025, so stacking back gives 6 rows, one more than the original 5.

Common Pitfalls

  • Duplicate index entries, which make unstack raise a ValueError; aggregate first.

  • Losing zeros vs missing distinctions when filling NaN.

  • Forgetting which level moves (the innermost by default).

  • The deprecated default stack() behaviour; pass future_stack=True in pandas 2.x.

Interview Tips

  • Explain stack as columns → rows and unstack as rows → columns.

  • Show the groupby + unstack reporting pattern.

17

What are the differences between astype, to_numeric and to_datetime?

astype(dtype) converts a Series or DataFrame to a specific dtype and raises an error if any value can’t be converted, so it suits clean data (“int64”, “float64”, “string”, “category”, “Int64”); pd.to_numeric(s, errors=...) parses messy strings into numbers, with errors="coerce" turning invalid values into NaN and downcast picking the smallest numeric type; and pd.to_datetime(s, format=..., errors=...) parses dates and times into datetime64, with format for speed and correctness, dayfirst for day-month order, utc=True for timezone handling and errors="coerce" for bad values. In short, use astype when you know the data is valid, and the to_* functions when parsing text that may contain errors; after coercing, always count how many values became NaN or NaT so bad data doesn’t vanish silently.

Read full answer

Applies topandas 2.2+

Comparison

astype pd.to_numeric pd.to_datetime
Purpose Any dtype conversion Text/objects → numbers Text/numbers → datetime
Invalid values Raises errors="coerce" → NaN errors="coerce" → NaT
Extra options errors="ignore" deprecated downcast="integer"/"float" format, dayfirst, utc, unit
Typical input Clean columns “1,200”, “n/a” “03/04/2026”, epoch seconds

Code Example

import pandas as pd

df = pd.DataFrame({
    "qty": ["3", "5", "7"],
    "price": ["10.5", "n/a", "12"],
    "when": ["03/04/2026", "15/04/2026", "not a date"],
    "epoch": [1767225600, 1767312000, 1767398400],
})

df["qty"] = df["qty"].astype("int64")                          # clean strings -> int
print(df["qty"].sum())
# 15

try:
    df["price"].astype(float)
except ValueError:
    print("astype failed on 'n/a'")
# astype failed on 'n/a'

df["price_num"] = pd.to_numeric(df["price"], errors="coerce")
print(df["price_num"].tolist(), int(df["price_num"].isna().sum()))
# [10.5, nan, 12.0] 1

df["when_dt"] = pd.to_datetime(df["when"], format="%d/%m/%Y", errors="coerce")
print(df["when_dt"].dt.strftime("%Y-%m-%d").tolist())
# ['2026-04-03', '2026-04-15', nan]

df["epoch_dt"] = pd.to_datetime(df["epoch"], unit="s", utc=True)
print(df["epoch_dt"].dt.strftime("%Y-%m-%d %H:%M %Z").tolist())
# ['2026-01-01 00:00 UTC', '2026-01-02 00:00 UTC', '2026-01-03 00:00 UTC']

print(pd.to_numeric(pd.Series(["1", "2", "300"]), downcast="integer").dtype)
# int16

The day-first format matters: without it, “03/04/2026” would be read as 4 March in month-first order. 1767225600 seconds after the Unix epoch is 1 January 2026 at midnight UTC.

Common Pitfalls

  • astype(int) on a column with NaN, which fails; use "Int64" or fill first.

  • Coercing errors and never checking how many values were lost.

  • Ambiguous dates without format or dayfirst.

  • Mixing timezone-aware and naive datetimes; standardize with utc=True and tz_convert.

Interview Tips

  • Contrast strict astype with the error-tolerant parsers.

  • Mention errors="coerce", format, downcast and checking NaN counts.

18

How do you work with time series data in pandas?

Working with time series in pandas starts with real datetimes: parse them with pd.to_datetime (or parse_dates when reading), set a sorted DatetimeIndex, and handle timezones explicitly (tz_localize, tz_convert); from there pandas gives date-based selection (df.loc["2026-03"]), resampling to other frequencies (resample("W").sum() to aggregate, asfreq plus ffill or interpolate to fill), rolling and expanding windows (rolling(7).mean(), rolling("7D") for time-based windows), lags and differences (shift, diff, pct_change), date parts through .dt (dayofweek, month), and time-aware joins with merge_asof. Watch for irregular or missing timestamps (reindex to a full date_range), daylight-saving gaps in local time, and leakage in models: features must use only past values, which shift before rolling ensures.

Read full answer

Applies topandas 2.2+

Toolkit

Task Code
Parse dates pd.to_datetime, read_csv(parse_dates=[...])
Index df.set_index("ts").sort_index()
Select a period df.loc["2026-03"], df.loc["2026-03-01":"2026-03-15"]
Change frequency resample("D").sum(), asfreq("h")
Moving statistics rolling(7).mean(), rolling("7D").sum()
Lags and changes shift(1), diff(), pct_change()
Calendar features .dt.dayofweek, .dt.month, .dt.is_month_end
Timezones tz_localize("Asia/Kolkata"), tz_convert("UTC")
Nearest-time join pd.merge_asof

Code Example

import pandas as pd

idx = pd.date_range("2026-03-01", periods=10, freq="D")
sales = pd.DataFrame({"units": [5, 7, 6, 9, 12, 4, 8, 10, 11, 7]}, index=idx)

weekly = sales["units"].resample("W-SUN").sum()               # weeks ending Sunday
print(weekly.to_dict())
# {Timestamp('2026-03-01 00:00:00'): 5, Timestamp('2026-03-08 00:00:00'): 56, Timestamp('2026-03-15 00:00:00'): 18}

sales["avg_3d"] = sales["units"].rolling(3).mean()
sales["prev_day"] = sales["units"].shift(1)
sales["change"] = sales["units"].diff()
print(sales.loc["2026-03-03", ["avg_3d", "prev_day", "change"]].tolist())
# [6.0, 7.0, -1.0]

sales["past_avg_3d"] = sales["units"].shift(1).rolling(3).mean()   # leakage-free feature
print(sales["past_avg_3d"].iloc[3])
# 6.0

sales["weekday"] = sales.index.day_name()
print(sales.loc["2026-03-07":"2026-03-08", "weekday"].tolist())
# ['Saturday', 'Sunday']

gappy = sales["units"].drop(pd.to_datetime(["2026-03-04", "2026-03-05"]))
full = gappy.reindex(pd.date_range(gappy.index.min(), gappy.index.max(), freq="D"))
print(int(full.isna().sum()), round(float(full.interpolate().loc["2026-03-04"]), 2))
# 2 5.33

local = sales.index.tz_localize("Asia/Kolkata")
print(local[0].tz_convert("UTC").strftime("%Y-%m-%d %H:%M"))
# 2026-02-28 18:30

1 March 2026 is a Sunday, so the first weekly bin holds only that day (5 units) and the next week (2–8 March) holds 7 + 6 + 9 + 12 + 4 + 8 + 10 = 56. Interpolating across the gap from 6 on 3 March to 4 on 6 March gives 5.33 on 4 March.

Common Pitfalls

  • Dates stored as strings, which sort and compare incorrectly.

  • An unsorted index, which breaks slicing and rolling.

  • Rolling features that include the current value when predicting it (leakage).

  • Mixing naive and timezone-aware timestamps.

Interview Tips

  • Mention DatetimeIndex, resample, rolling, shift and timezones.

  • Explain leakage-free lag features.

19

How can you perform statistical aggregation on DataFrame groups?

Group the rows with groupby and aggregate with built-in, vectorized statistics: a single one (df.groupby("team")["score"].mean()), several at once with agg(["mean", "median", "std", "count"]), or, most readably, named aggregations that set the output column names, agg(avg=("score", "mean"), p90=("score", lambda s: s.quantile(0.9))). pandas also offers describe() per group, quantile, nunique, size vs count, weighted statistics through a small helper, and transform to attach a group statistic to every row (for z-scores within groups). Use as_index=False or reset_index() for a flat result, observed=True with categorical keys, and remember that std uses ddof = 1 and that NaN values are skipped by default.

Read full answer

Applies topandas 2.2+

Common Aggregations

Statistic Function
Count rows / non-missing size / count
Central tendency mean, median
Spread std, var, min, max, quantiles
Distinct values nunique
First/last first, last
Custom Lambda or function in agg

Code Example

import numpy as np
import pandas as pd

scores = pd.DataFrame({
    "team": ["A", "A", "A", "B", "B", "C"],
    "player": ["p1", "p2", "p3", "p4", "p5", "p6"],
    "score": [70, 80, 90, 60, np.nan, 85],
    "minutes": [30, 20, 50, 40, 10, 60],
})

stats = scores.groupby("team").agg(
    players=("player", "size"),
    scored=("score", "count"),
    avg=("score", "mean"),
    median=("score", "median"),
    sd=("score", "std"),
    best=("score", "max"),
)
print(stats.round(2).to_dict("index"))
# {'A': {'players': 3, 'scored': 3, 'avg': 80.0, 'median': 80.0, 'sd': 10.0, 'best': 90.0},
#  'B': {'players': 2, 'scored': 1, 'avg': 60.0, 'median': 60.0, 'sd': nan, 'best': 60.0},
#  'C': {'players': 1, 'scored': 1, 'avg': 85.0, 'median': 85.0, 'sd': nan, 'best': 85.0}}

def weighted_mean(g):
    valid = g["score"].notna()
    return np.average(g.loc[valid, "score"], weights=g.loc[valid, "minutes"])

print(scores.groupby("team")[["score", "minutes"]].apply(weighted_mean).round(2).to_dict())
# {'A': 81.0, 'B': 60.0, 'C': 85.0}

scores["z_in_team"] = scores.groupby("team")["score"].transform(lambda s: (s - s.mean()) / s.std())
print(scores.loc[scores["team"] == "A", "z_in_team"].tolist())
# [-1.0, 0.0, 1.0]

Team A’s weighted mean is (70·30 + 80·20 + 90·50) / 100 = 81. The standard deviation is NaN for groups with a single score because ddof = 1 needs at least two values.

Common Pitfalls

  • Confusing size (rows) and count (non-missing values).

  • Forgetting that std is NaN for one-element groups.

  • Using lambdas for statistics that have built-in fast versions.

  • Weighted averages computed as the mean of ratios instead of ratio of sums.

Interview Tips

  • Show named aggregation and transform.

  • Mention NaN handling, ddof and weighted statistics.

20

What ranking methods does pandas offer?

Series.rank() (and DataFrame.rank(), also available on groups) assigns each value its position in sorted order, and the method argument decides how ties are handled: “average” (the default) gives tied values the mean of their positions, “min” gives them all the lowest position (competition ranking, 1, 2, 2, 4), “max” the highest, “first” breaks ties by order of appearance, and “dense” gives ties the same rank without gaps (1, 2, 2, 3). Other options are ascending=False for “highest is rank 1”, pct=True for percentile ranks between 0 and 1, and na_option (“keep”, “top” or “bottom”) for missing values. Ranking within groups (groupby("class")["score"].rank(...)) is common for leaderboards and top-n per group, where nlargest or a rank filter picks the winners.

Read full answer

Applies topandas 2.2+

Tie-Handling Methods (values 90, 85, 85, 70, descending)

Method Ranks Typical use
average 1, 2.5, 2.5, 4 Statistics (rank tests)
min 1, 2, 2, 4 Sports/competitions
max 1, 3, 3, 4 Conservative ranking
first 1, 2, 3, 4 Unique positions by order
dense 1, 2, 2, 3 Levels without gaps

Code Example

import numpy as np
import pandas as pd

s = pd.Series([90, 85, 85, 70, np.nan], index=["ana", "ben", "cai", "dev", "eva"])

for method in ["average", "min", "max", "first", "dense"]:
    print(method, s.rank(ascending=False, method=method).tolist())
# average [1.0, 2.5, 2.5, 4.0, nan]
# min [1.0, 2.0, 2.0, 4.0, nan]
# max [1.0, 3.0, 3.0, 4.0, nan]
# first [1.0, 2.0, 3.0, 4.0, nan]
# dense [1.0, 2.0, 2.0, 3.0, nan]

print(s.rank(pct=True).tolist())               # percentile rank, ascending
# [1.0, 0.625, 0.625, 0.25, nan]
print(s.rank(ascending=False, na_option="bottom").tolist())
# [1.0, 2.5, 2.5, 4.0, 5.0]

df = pd.DataFrame({
    "class": ["X", "X", "X", "Y", "Y"],
    "student": ["a", "b", "c", "d", "e"],
    "score": [88, 92, 88, 75, 81],
})
df["class_rank"] = df.groupby("class")["score"].rank(ascending=False, method="min").astype(int)
print(df["class_rank"].tolist())
# [2, 1, 2, 2, 1]
print(df[df["class_rank"] == 1]["student"].tolist())
# ['b', 'e']

With pct=True, each rank is divided by the 4 non-missing values: 70 is rank 1 (0.25), the tied 85s share rank 2.5 (0.625), and 90 is rank 4 (1.0).

Common Pitfalls

  • Forgetting ascending=False when the highest value should be rank 1.

  • Not choosing a tie method deliberately (the default “average” gives fractional ranks).

  • Ranks returned as floats; convert with astype(int) once NaNs are handled.

Interview Tips

  • List the five tie methods with a small example.

  • Mention pct, na_option and group-wise ranking.

21

How do you use window functions in pandas for running calculations?

pandas has three families of window functions: rolling windows (rolling(3).mean(), or time-based rolling("7D").sum()) compute a statistic over a fixed-size moving window, expanding windows (expanding().max(), or shortcuts like cumsum, cummax) use everything from the start up to each row for running totals and records, and exponentially weighted windows (ewm(span=10).mean(), or alpha/halflife) give recent values more weight for smoothing. Key options are min_periods (how many values are needed before a result appears instead of NaN), center=True for centred windows, closed for time-based windows, and win_type for weighted windows. Combine them with groupby for per-group running calculations (groupby("store")["sales"].rolling(7).mean() or cumsum()), and with shift(1) first when building model features so the current value isn’t included. Built-in methods are fast; rolling().apply(func, raw=True) handles custom statistics, more slowly.

Read full answer

Applies topandas 2.2+

Window Types

Type Example Use
Rolling, count-based s.rolling(3).mean() Moving average over 3 rows
Rolling, time-based s.rolling("7D").sum() Last 7 days, irregular timestamps
Expanding s.expanding().mean(), cumsum() Running totals, records
Exponentially weighted s.ewm(span=3, adjust=False).mean() Smoothing, recency weighting
Grouped df.groupby(k)[c].cumsum() Per-customer running totals

Code Example

import pandas as pd

s = pd.Series([10, 20, 30, 40, 50], index=pd.date_range("2026-05-01", periods=5, freq="D"))

print(s.rolling(3).mean().tolist())
# [nan, nan, 20.0, 30.0, 40.0]
print(s.rolling(3, min_periods=1).mean().tolist())
# [10.0, 15.0, 20.0, 30.0, 40.0]
print(s.expanding().sum().tolist(), s.cummax().tolist())
# [10.0, 30.0, 60.0, 100.0, 150.0] [10, 20, 30, 40, 50]
print(s.ewm(alpha=0.5, adjust=False).mean().tolist())
# [10.0, 15.0, 22.5, 31.25, 40.625]

irregular = pd.Series([1, 2, 3, 4], index=pd.to_datetime(["2026-05-01", "2026-05-02", "2026-05-05", "2026-05-06"]))
print(irregular.rolling("3D").sum().tolist())     # window covers the 3 days ending at each timestamp
# [1.0, 3.0, 3.0, 7.0]

orders = pd.DataFrame({
    "customer": ["a", "b", "a", "a", "b"],
    "amount": [100, 50, 30, 70, 20],
})
orders["running_total"] = orders.groupby("customer")["amount"].cumsum()
orders["order_number"] = orders.groupby("customer").cumcount() + 1
orders["prev_avg"] = (orders.groupby("customer")["amount"]
                            .transform(lambda x: x.shift(1).expanding().mean()))
print(orders.to_dict("list"))
# {'customer': ['a', 'b', 'a', 'a', 'b'], 'amount': [100, 50, 30, 70, 20],
#  'running_total': [100, 50, 130, 200, 70], 'order_number': [1, 1, 2, 3, 2],
#  'prev_avg': [nan, nan, 100.0, 65.0, 50.0]}

For the exponentially weighted mean with α = 0.5 and adjust=False, each value is 0.5·new + 0.5·previous: 15, then 22.5, then 31.25, then 40.625. In the time-based window, 5 May only covers 3–5 May, so it holds just the value 3.

Common Pitfalls

  • Time-based windows on an unsorted index (raises an error) or a non-datetime index.

  • Including the current row in features meant to predict it; shift first.

  • Expecting rolling(3) to produce values for the first two rows (use min_periods).

  • rolling().apply with Python functions on large data, which is slow.

Interview Tips

  • Name rolling, expanding and EWM windows with examples.

  • Mention min_periods, time-based windows, groupby and shift for leakage-free features.

22

What is a crosstab in pandas and when would you use it?

pd.crosstab(index, columns) builds a contingency table: it counts how often each combination of two (or more) categorical variables occurs, such as how many customers of each plan churned or stayed, and it can also aggregate a third variable with values= and aggfunc=, add row and column totals with margins=True, and turn counts into proportions with normalize=True, "index" (row shares) or "columns" (column shares). Use it to explore relationships between categorical variables, check class balance across segments, build a confusion matrix from true and predicted labels, or prepare the table for a chi-squared test of independence (scipy.stats.chi2_contingency). It is a convenient shortcut for groupby plus size plus unstack, or for pivot_table(..., aggfunc="count"), and it accepts arrays as well as DataFrame columns.

Read full answer

Applies topandas 2.2+, SciPy 1.13+

Code Example

import pandas as pd
from scipy.stats import chi2_contingency

df = pd.DataFrame({
    "plan": ["free", "free", "pro", "pro", "free", "pro", "free", "pro"],
    "churned": ["yes", "no", "no", "no", "yes", "yes", "yes", "no"],
    "revenue": [0, 0, 20, 25, 0, 20, 0, 30],
})

counts = pd.crosstab(df["plan"], df["churned"], margins=True, margins_name="Total")
print(counts)
# churned  no  yes  Total
# plan
# free      1    3      4
# pro       3    1      4
# Total     4    4      8

rates = pd.crosstab(df["plan"], df["churned"], normalize="index")
print(rates["yes"].to_dict())
# {'free': 0.75, 'pro': 0.25}

avg_rev = pd.crosstab(df["plan"], df["churned"], values=df["revenue"], aggfunc="mean")
print(avg_rev.loc["pro"].to_dict())
# {'no': 25.0, 'yes': 20.0}

chi2, p, dof, expected = chi2_contingency(pd.crosstab(df["plan"], df["churned"]))
print(round(float(chi2), 3), dof)
# 0.5 1

y_true = ["cat", "dog", "cat", "dog", "cat"]
y_pred = ["cat", "cat", "cat", "dog", "dog"]
print(pd.crosstab(pd.Series(y_true, name="actual"), pd.Series(y_pred, name="predicted")).to_dict())
# {'cat': {'cat': 2, 'dog': 1}, 'dog': {'cat': 1, 'dog': 1}}

The chi-squared value uses Yates’ continuity correction, which SciPy applies by default for 2 × 2 tables: each cell is 1 away from its expected count of 2, so χ² = 4 · (|1| − 0.5)² / 2 = 0.5. With only 8 rows the test is far from significant; it is here to show the workflow.

crosstab vs Alternatives

Tool Best for
pd.crosstab Counts/proportions of category combinations, arrays as input
pivot_table Aggregating numeric values from a DataFrame
groupby().size().unstack() Same counts with more control
value_counts(normalize=True) One variable

Common Pitfalls

  • Choosing the wrong normalize axis (row vs column shares answer different questions).

  • Reading patterns from tiny counts; check totals with margins=True.

  • Missing values are dropped by default, which can hide a category; fill them first if they matter.

Interview Tips

  • Describe crosstab as a contingency table with counts, shares and totals.

  • Mention confusion matrices and chi-squared tests as uses.

23

How do you query a DataFrame with a MultiIndex?

With a MultiIndex (hierarchical index such as region → store → date), you select rows by passing tuples to .loc, from the outer level inward: df.loc["North"] returns all rows for that region, df.loc[("North", "S1")] a single store, and lists or slices select several; for conditions on inner levels, use pd.IndexSlice (df.loc[idx[:, "S1"], :]), the cross-section method df.xs("S1", level="store"), or a boolean mask built from df.index.get_level_values("store"). df.query("store == 'S1' and year >= 2025") also works with level names. Slicing ranges on a MultiIndex requires a sorted index (sort_index()), otherwise pandas raises an UnsortedIndexError or warns about performance, and reset_index() turns the levels back into ordinary columns when a flat table is easier.

Read full answer

Applies topandas 2.2+

Selection Methods

Need Code
Outer level df.loc["North"]
Exact combination df.loc[("North", "S1")]
Several values df.loc[(["North", "South"], "S1"), :]
Inner level, all outer values df.loc[pd.IndexSlice[:, "S1"], :]
Cross-section df.xs("S1", level="store")
Condition on a level df[df.index.get_level_values("year") >= 2025]
Query by level name df.query("store == 'S1'")

Code Example

import pandas as pd

data = pd.DataFrame({
    "region": ["North", "North", "North", "South", "South", "South"],
    "store": ["S1", "S1", "S2", "S1", "S3", "S3"],
    "year": [2025, 2026, 2026, 2026, 2025, 2026],
    "revenue": [100, 120, 90, 70, 60, 65],
}).set_index(["region", "store", "year"]).sort_index()

print(data.loc["North", "revenue"].tolist())
# [100, 120, 90]
print(data.loc[("North", "S1", 2026), "revenue"])
# 120

idx = pd.IndexSlice
print(data.loc[idx[:, "S1", :], "revenue"].tolist())          # store S1 in every region
# [100, 120, 70]
print(data.xs(2026, level="year")["revenue"].to_dict())
# {('North', 'S1'): 120, ('North', 'S2'): 90, ('South', 'S1'): 70, ('South', 'S3'): 65}

recent = data[data.index.get_level_values("year") >= 2026]
print(len(recent), int(recent["revenue"].sum()))
# 4 345

print(data.query("store == 'S3' and year == 2025")["revenue"].tolist())
# [60]

print(data.groupby(level="region")["revenue"].sum().to_dict())
# {'North': 310, 'South': 195}

Common Pitfalls

  • Range slicing on an unsorted MultiIndex; call sort_index() first.

  • Passing a tuple without : for columns in loc, which is ambiguous; use df.loc[(...), :].

  • Forgetting that selecting the outer level drops it from the result’s index.

  • Building deep MultiIndexes when a flat table with columns would be simpler.

Interview Tips

  • Show tuple selection, IndexSlice, xs and get_level_values.

  • Mention sorting and reset_index.

24

How do you normalize data in a DataFrame column?

Normalization rescales a numeric column so values are on a comparable scale, and the common methods are each one vectorized line in pandas: min-max scaling to [0, 1] with (x − min) / (max − min), z-score standardization to mean 0 and standard deviation 1 with (x − mean) / std, robust scaling with (x − median) / IQR, which resists outliers, and a log transform (np.log1p) for skewed, non-negative values such as income or counts. You can apply them to several columns at once, or within groups with groupby().transform (for example z-scores per store). For machine learning, use scikit-learn’s MinMaxScaler, StandardScaler or RobustScaler inside a pipeline instead, because the scaling parameters must be learned from the training data only and reused on new data. Note that pandas’ std() uses ddof = 1 while scikit-learn’s StandardScaler uses ddof = 0, so results differ slightly.

Read full answer

Applies topandas 2.2+, scikit-learn 1.5+

Methods

Method Formula Range Outlier sensitivity
Min-max (x − min) / (max − min) [0, 1] High
Z-score (x − mean) / std Unbounded, mean 0 Medium
Robust (x − median) / IQR Unbounded Low
Log log(1 + x) Compresses large values Reduces skew
Unit vector (rows) x / ‖x‖ Norm 1 —

Code Example

import numpy as np
import pandas as pd

df = pd.DataFrame({
    "store": ["A", "A", "B", "B"],
    "sales": [10.0, 20.0, 30.0, 40.0],
    "income": [20_000, 35_000, 50_000, 1_000_000],
})

s = df["sales"]
df["sales_minmax"] = (s - s.min()) / (s.max() - s.min())
df["sales_z"] = (s - s.mean()) / s.std()
print(df["sales_minmax"].round(3).tolist(), df["sales_z"].round(3).tolist())
# [0.0, 0.333, 0.667, 1.0] [-1.162, -0.387, 0.387, 1.162]

q1, q3 = df["income"].quantile([0.25, 0.75])
df["income_robust"] = (df["income"] - df["income"].median()) / (q3 - q1)
df["income_log"] = np.log1p(df["income"])
print(df["income_robust"].round(3).tolist())
# [-0.088, -0.029, 0.029, 3.737]
print(df["income_log"].round(2).tolist())
# [9.9, 10.46, 10.82, 13.82]

df["sales_z_in_store"] = df.groupby("store")["sales"].transform(lambda x: (x - x.mean()) / x.std())
print(df["sales_z_in_store"].round(3).tolist())
# [-0.707, 0.707, -0.707, 0.707]

cols = ["sales", "income"]
print(((df[cols] - df[cols].min()) / (df[cols].max() - df[cols].min())).round(3).to_dict("list"))
# {'sales': [0.0, 0.333, 0.667, 1.0], 'income': [0.0, 0.015, 0.031, 1.0]}

The sales standard deviation (ddof = 1) is √(500/3) ≈ 12.91, so 10 becomes (10 − 25) / 12.91 ≈ −1.162. For income, the quartiles are 31,250 and 287,500 (IQR 256,250) and the median is 42,500, so the huge value gets a robust score of about 3.74 while min-max squeezes the other three near 0.

Common Pitfalls

  • Computing scaling statistics on the full dataset before splitting (leakage).

  • Min-max scaling with outliers, which squashes everything else together.

  • Dividing by zero when a column (or group) is constant.

  • Mixing ddof conventions between pandas and scikit-learn.

Interview Tips

  • Give min-max, z-score and robust formulas and when each fits.

  • Mention per-group scaling and scaler fitting inside pipelines.

25

How do you create simple plots with pandas' plotting tools?

pandas has a built-in .plot accessor on Series and DataFrames that wraps Matplotlib, so one call gives a quick chart: df.plot() draws a line per numeric column against the index, and kind= (or methods like df.plot.bar()) selects bar, barh, hist, box, kde, area, pie, scatter and hexbin charts, with options for x, y, title, xlabel, ylabel, figsize, subplots=True, stacked=True, logy and more. Because each call returns a Matplotlib Axes, you can keep customizing it (titles, annotations, saving with fig.savefig) or pass ax= to draw several pandas plots in one figure. It is ideal for exploratory analysis; for publication-quality or statistical plots, seaborn and Plotly build on the same data. Plot aggregated data (for example groupby results) rather than millions of raw rows.

Read full answer

Applies topandas 2.2+, Matplotlib 3.8+

Plot Kinds

kind Shows Typical data
line Trends Time series
bar / barh Comparisons Group totals
hist Distribution One numeric column
box Spread and outliers Numeric columns
kde Smoothed distribution Numeric column
scatter Relationship Two numeric columns
area Stacked totals over time Shares
pie Parts of a whole Few categories

Code Example

import matplotlib

matplotlib.use("Agg")
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
days = pd.date_range("2026-01-01", periods=60, freq="D")
df = pd.DataFrame({
    "web": 100 + np.cumsum(rng.normal(0, 5, 60)),
    "app": 80 + np.cumsum(rng.normal(1, 5, 60)),
}, index=days)
df["region"] = rng.choice(["North", "South", "East"], 60)

fig, axes = plt.subplots(2, 2, figsize=(11, 8))
df[["web", "app"]].plot(ax=axes[0, 0], title="Daily orders", ylabel="orders")
df.groupby("region")[["web", "app"]].sum().plot.bar(ax=axes[0, 1], stacked=True, title="Orders by region", rot=0)
df["web"].plot.hist(ax=axes[1, 0], bins=15, alpha=0.7, title="Distribution of web orders")
df.plot.scatter(x="web", y="app", ax=axes[1, 1], alpha=0.6, title="Web vs app")
fig.tight_layout()
fig.savefig("pandas_plots.png", dpi=120)

ax = df[["web", "app"]].resample("W").mean().plot(marker="o", title="Weekly average")
ax.figure.savefig("weekly.png", dpi=120)
print(type(ax).__name__)
# Axes

The matplotlib.use("Agg") line renders without a display, for scripts and servers; in a notebook you can omit it.

Common Pitfalls

  • Plotting raw high-frequency data instead of resampled or aggregated data.

  • Pie charts with many slices; bar charts read better.

  • Forgetting that df.plot() uses the index as the x-axis; set it or pass x=.

  • Overlapping labels on bar charts; use rot= or barh.

Interview Tips

  • Mention the .plot accessor, kind= and the returned Axes.

  • Show combining plots with ax= and saving the figure.

26

How does pandas integrate with Matplotlib and seaborn?

pandas plotting is built on Matplotlib: df.plot(...) creates Matplotlib figures and returns an Axes object, so you can mix pandas’ quick plots with Matplotlib’s full control (subplots via ax=, annotations, styles, saving), and seaborn is designed to take pandas DataFrames directly, using column names for x, y, hue, col and row, which makes statistical plots like grouped box plots, regression lines, distributions and heatmaps one line each. Seaborn works best with long (“tidy”) data, one row per observation, so melt is often the step that connects a wide DataFrame to seaborn, while pandas aggregations (groupby, pivot_table, corr) prepare the summaries that Matplotlib or sns.heatmap then draw. A typical workflow: clean and aggregate in pandas, plot quickly with .plot during exploration, then build polished figures with seaborn on top of Matplotlib Axes.

Read full answer

Applies topandas 2.2+, Matplotlib 3.8+, seaborn 0.13+

Roles

Library Role
pandas Prepare data; quick .plot charts that return Axes
Matplotlib Figure, Axes, layout, fine control, saving
seaborn Statistical plots from DataFrame columns (hue, facets)

Code Example

import matplotlib

matplotlib.use("Agg")
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import seaborn as sns

rng = np.random.default_rng(42)
n = 300
df = pd.DataFrame({
    "plan": rng.choice(["free", "pro", "team"], n, p=[0.5, 0.3, 0.2]),
    "tenure_months": rng.integers(1, 37, n),
})
df["monthly_spend"] = (df["plan"].map({"free": 0, "pro": 20, "team": 60})
                       + 0.5 * df["tenure_months"] + rng.normal(0, 4, n)).clip(lower=0)

fig, axes = plt.subplots(1, 3, figsize=(15, 4.5))
df.groupby("plan")["monthly_spend"].mean().plot.bar(ax=axes[0], rot=0, title="Mean spend (pandas)")
sns.boxplot(data=df, x="plan", y="monthly_spend", order=["free", "pro", "team"], ax=axes[1])
axes[1].set_title("Spend by plan (seaborn)")
sns.scatterplot(data=df, x="tenure_months", y="monthly_spend", hue="plan", alpha=0.6, ax=axes[2])
axes[2].set_title("Tenure vs spend")
fig.tight_layout()
fig.savefig("pandas_seaborn.png", dpi=120)

wide = pd.DataFrame({"Q1": [10, 12], "Q2": [14, 11]}, index=["north", "south"]).rename_axis("region")
tidy = wide.reset_index().melt(id_vars="region", var_name="quarter", value_name="sales")
print(tidy.values.tolist())
# [['north', 'Q1', 10], ['south', 'Q1', 12], ['north', 'Q2', 14], ['south', 'Q2', 11]]
grid = sns.catplot(data=tidy, x="quarter", y="sales", hue="region", kind="bar", height=3)
grid.savefig("tidy_bars.png", dpi=120)

corr = df[["tenure_months", "monthly_spend"]].corr()
ax = sns.heatmap(corr, annot=True, fmt=".2f", cmap="RdBu_r", vmin=-1, vmax=1)
ax.figure.savefig("corr.png", dpi=120)

Common Pitfalls

  • Passing wide data to seaborn functions that expect long data; melt first.

  • Mixing plt. state-machine calls with explicit Axes, which draws on the wrong subplot.

  • Plotting before aggregating large data, which is slow and cluttered.

  • Category order left to chance; set order= or use an ordered categorical.

Interview Tips

  • Explain pandas → Matplotlib Axes and seaborn’s column-based API.

  • Mention tidy data and melt as the bridge.

27

How would you export a DataFrame to different file formats for reports?

pandas has a to_* method for each common format: to_csv for universal plain text, to_excel for business users (multiple sheets with ExcelWriter, formatting through openpyxl or XlsxWriter), to_parquet for compact, typed, fast files used by data tools, to_json for APIs and web apps (orient="records"), to_html and to_markdown for emails, wikis and dashboards, to_sql for databases, and to_latex for papers; the Styler (df.style) adds number formats, highlighting and colour scales to HTML and Excel exports. For reports, prepare a presentation copy first: round and format numbers, rename columns to readable labels, sort meaningfully, reset or name the index, and keep raw data in Parquet or CSV beside the formatted file. Mind encodings for Excel-bound CSVs (encoding="utf-8-sig"), timezone-aware datetimes (Excel can’t store them; convert or strip timezones), and the optional engines each format needs (openpyxl, pyarrow, tabulate).

Read full answer

Applies topandas 2.2+ (openpyxl, pyarrow, tabulate and Jinja2 as optional dependencies)

Formats

Format Method Good for Notes
CSV to_csv(index=False) Any tool encoding="utf-8-sig" for Excel
Excel to_excel, ExcelWriter Business reports Several sheets; needs openpyxl/XlsxWriter
Parquet to_parquet Analytics pipelines Keeps dtypes; needs pyarrow
JSON to_json(orient="records") APIs, web date_format="iso"
HTML to_html, style.to_html() Emails, dashboards Styler for formatting
Markdown to_markdown Docs, wikis Needs tabulate
SQL to_sql Databases SQLAlchemy engine

Code Example

import pandas as pd

report = pd.DataFrame({
    "region": ["North", "South", "East"],
    "revenue": [125430.5, 98010.25, 45200.0],
    "growth": [0.124, -0.031, 0.087],
})
pretty = (report.sort_values("revenue", ascending=False)
                .rename(columns={"region": "Region", "revenue": "Revenue (INR)", "growth": "Growth"}))

pretty.to_csv("report.csv", index=False, encoding="utf-8-sig")
pretty.to_parquet("report.parquet", index=False)
print(report.to_json(orient="records"))
# [{"region":"North","revenue":125430.5,"growth":0.124},{"region":"South","revenue":98010.25,"growth":-0.031},{"region":"East","revenue":45200.0,"growth":0.087}]

with pd.ExcelWriter("report.xlsx") as writer:
    pretty.to_excel(writer, sheet_name="Summary", index=False)
    report.to_excel(writer, sheet_name="Raw data", index=False)

styled = (pretty.style
          .format({"Revenue (INR)": "{:,.0f}", "Growth": "{:+.1%}"})
          .hide(axis="index"))
html = styled.to_html()
print("12.4%" in html, "125,430" in html)
# True True

print(pretty.round(1).to_markdown(index=False))   # a pipe table for wikis and READMEs

The Markdown table’s column widths and number display come from the tabulate package; format numbers yourself (with round or string formatting) when the exact text matters.

Common Pitfalls

  • Writing the index by accident (an extra unnamed column).

  • Timezone-aware datetimes in Excel exports, which raise an error.

  • CSV files that show garbled characters in Excel because of encoding.

  • Exporting raw floats with many decimals to a human-facing report.

Interview Tips

  • Match formats to audiences: Excel/HTML for people, Parquet/CSV/JSON for systems.

  • Mention Styler formatting, ExcelWriter sheets and encoding issues.

28

What techniques improve the performance of pandas operations?

The biggest gains come from doing less work in Python: replace loops, iterrows and row-wise apply with vectorized column operations, NumPy functions, .str/.dt accessors, np.where/np.select, map with dicts, and built-in groupby aggregations; then reduce the data and memory: load only needed columns (usecols) and rows, use compact dtypes (category for repeated strings, downcast numbers, string[pyarrow] or dtype_backend="pyarrow"), and store intermediate data in Parquet instead of CSV. Further techniques are sorting and setting an index for repeated lookups and merges, using merge instead of looping lookups, query/eval with numexpr for large expressions, avoiding repeated concat inside loops (collect pieces in a list and concat once), observed=True and sort=False in groupby, and chunked processing for files bigger than memory. When pandas itself is the bottleneck, Polars, DuckDB, Dask or Spark take over. Always profile first (%timeit, memory_usage(deep=True)) to fix the real bottleneck.

Read full answer

Applies topandas 2.2+

Techniques

Technique Typical speedup
Vectorize instead of iterrows/apply(axis=1) 10–100×
category dtype for repeated strings Large memory and groupby savings
usecols, compact dtypes on read Less memory and I/O
Parquet instead of CSV Several times faster reads
Concat once, not in a loop Avoids quadratic copying
merge instead of per-row lookups Large
groupby(..., observed=True, sort=False) Moderate
eval/query (numexpr) Moderate on big frames
Polars/DuckDB/Dask When data or CPU needs outgrow pandas

Code Example

import time

import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
n = 200_000
df = pd.DataFrame({
    "city": rng.choice(["Pune", "Delhi", "Mumbai", "Chennai"], n),
    "qty": rng.integers(1, 10, n),
    "price": rng.uniform(10, 500, n).round(2),
})

def timed(label, func):
    start = time.perf_counter()
    result = func()
    print(f"{label:28s} {time.perf_counter() - start:.4f}s")
    return result

loop = timed("apply(axis=1)", lambda: df.apply(lambda r: r["qty"] * r["price"], axis=1))
vector = timed("vectorized", lambda: df["qty"] * df["price"])
print("same result:", np.allclose(loop, vector))
# same result: True

before = df.memory_usage(deep=True).sum()
df["city"] = df["city"].astype("category")
df["qty"] = pd.to_numeric(df["qty"], downcast="integer")
after = df.memory_usage(deep=True).sum()
print(f"memory {before / 1e6:.1f} MB -> {after / 1e6:.1f} MB")
# the city column shrinks the most: from Python strings to 1-byte codes

timed("groupby observed", lambda: df.groupby("city", observed=True)["price"].mean())

pieces = [df.iloc[i:i + 10_000] for i in range(0, n, 10_000)]
timed("concat once", lambda: pd.concat(pieces, ignore_index=True))

The vectorized multiplication is typically about a thousand times faster than the row-wise apply on 200,000 rows; exact timings depend on the machine.

Common Pitfalls

  • Optimizing without profiling.

  • df = pd.concat([df, row]) inside a loop.

  • Using object dtype strings for low-cardinality columns.

  • Chained operations that create many large temporary copies; select columns early.

Interview Tips

  • Lead with vectorization and dtypes, then I/O formats and chunking.

  • Mention profiling and when to switch to Polars, DuckDB or Dask.

29

How does memory usage compare for category vs object dtype?

An object-dtype column stores a pointer to a separate Python string object for every row, so a column of one million city names costs roughly 8 bytes per pointer plus about 50–60 bytes per string object (repeated identical strings may share objects, but often don’t after reading from files); a category column stores each distinct value once in a small “categories” array and keeps one integer code per row, which uses 1 byte per row for up to 128 categories (int8), 2 bytes for up to about 32,000, and so on. For low-cardinality columns this cuts memory by 90% or more and makes groupby, sorting, comparisons and value_counts faster because they work on integer codes. The benefit disappears when most values are unique (IDs, free text), where category adds overhead for little gain; for those, pandas’ string[pyarrow] dtype is usually the most memory-efficient. Use memory_usage(deep=True) to measure, because the default reading ignores the string objects.

Read full answer

Applies topandas 2.2+ (pyarrow for `string[pyarrow]`)

Comparison

object category string[pyarrow]
Storage Pointer + Python str per row Codes + unique values once Arrow buffers (contiguous bytes)
Low cardinality Large Very small Medium
High cardinality Large Large (+ overhead) Smallest
groupby / sort speed Slow Fast Fast
Adding new values Free Must add categories Free
Order semantics Lexical Optional ordered categories Lexical

Code Example

import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
n = 1_000_000
cities = rng.choice(["Pune", "Delhi", "Mumbai", "Chennai", "Kolkata"], n)
ids = np.char.add("user_", rng.integers(0, 10**9, n).astype(str))   # nearly all unique

low = pd.Series(cities, dtype="object")
high = pd.Series(ids, dtype="object")

def mb(s):
    return round(s.memory_usage(deep=True) / 1e6, 1)

print("low cardinality :", mb(low), "MB object ->", mb(low.astype("category")), "MB category ->",
      mb(low.astype("string[pyarrow]")), "MB string[pyarrow]")
print("high cardinality:", mb(high), "MB object ->", mb(high.astype("category")), "MB category ->",
      mb(high.astype("string[pyarrow]")), "MB string[pyarrow]")
# Low cardinality: category is about 1 MB (1 byte per row) vs tens of MB as object.
# High cardinality: category saves little or nothing; string[pyarrow] is clearly smallest.

cat = low.astype("category")
print(cat.cat.codes.dtype, len(cat.cat.categories))
# int8 5
print(low.memory_usage() / 1e6, "MB without deep=True (pointers only)")
# 8.000132 MB without deep=True (pointers only)

The shallow memory_usage() counts only the 8-byte pointers plus the index (8,000,000 + 132 bytes for a RangeIndex), which hides the real cost of the strings.

Common Pitfalls

  • Measuring without deep=True and concluding object columns are cheap.

  • Converting near-unique columns to category.

  • Combining or concatenating categoricals with different categories, which silently converts back to object; use union_categoricals or set the same categories.

  • Assigning a value that isn’t in the categories, which raises an error.

Interview Tips

  • Explain codes plus categories vs pointers to Python strings.

  • Mention when category helps, when it doesn’t, and string[pyarrow].

30

How do you use Dask or Modin for larger-than-memory pandas data?

Dask DataFrame splits a large dataset into many pandas DataFrames (partitions) and builds a lazy task graph of pandas-like operations, which runs in parallel and out of core when you call .compute(), so you can process data bigger than RAM on a laptop or scale to a cluster with mostly familiar code (dd.read_parquet, groupby, merge, resample); Modin keeps the exact pandas API (import modin.pandas as pd) and parallelizes operations across all CPU cores using Ray or Dask as the engine, which speeds up existing code with almost no changes but is mainly about using more cores, with limited spilling to disk. Choose Dask for truly larger-than-memory data and pipelines, Modin to accelerate pandas code that already roughly fits in memory. Both work best with columnar Parquet files, partitions of about 100 MB, filtering and column selection early, and avoiding operations that need all data in one place (full sorts, iloc by position, row-wise apply). Alternatives worth considering are Polars (lazy, streaming engine) and DuckDB (SQL on Parquet), which often handle “too big for pandas” data on one machine faster.

Read full answer

Applies toDask 2024+, Modin 0.30+, pandas 2.2+

Comparison

Dask DataFrame Modin
API pandas-like, lazy (.compute()) Drop-in pandas (eager)
Larger than memory Yes (partitions, spilling) Limited
Scale out Cluster via dask.distributed Ray/Dask clusters
Code changes Moderate Usually one import
API coverage Common operations Most of pandas
Best for Big pipelines, ETL Speeding up existing scripts

Code Example (Dask)

import dask.dataframe as dd
import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
for part in range(4):                                     # create sample files; real data would already exist
    pd.DataFrame({
        "date": pd.date_range("2026-01-01", periods=50_000, freq="min") + pd.Timedelta(days=40 * part),
        "store": rng.choice(["S1", "S2", "S3"], 50_000),
        "amount": rng.uniform(1, 100, 50_000).round(2),
    }).to_parquet(f"sales_part_{part}.parquet", index=False)

ddf = dd.read_parquet("sales_part_*.parquet", columns=["date", "store", "amount"])
print(ddf.npartitions)
# 4

big_sales = ddf[ddf["amount"] > 50]                        # lazy: nothing computed yet
per_store = big_sales.groupby("store")["amount"].agg(["count", "sum", "mean"])
result = per_store.compute()                                # runs in parallel, returns pandas
print(sorted(result.index.tolist()))
# ['S1', 'S2', 'S3']

daily = ddf.set_index("date").resample("D")["amount"].sum()
print(type(daily.compute()).__name__)
# Series

Modin Usage

import modin.pandas as mpd   # requires modin[ray] or modin[dask]

df = mpd.read_csv("big_file.csv")                          # same API as pandas, parallel under the hood
summary = df.groupby("store")["amount"].mean()
print(summary.head())

Common Pitfalls

  • Calling .compute() on a huge result, which pulls everything into memory.

  • Reading many small files or one giant CSV; prefer Parquet partitions of about 100 MB.

  • Operations that need a global shuffle (set_index on unsorted data, full sorts) repeated often.

  • Expecting Modin to handle data far larger than RAM.

Interview Tips

  • Explain partitions and lazy graphs (Dask) vs drop-in parallel pandas (Modin).

  • Mention Parquet, early filtering, and Polars/DuckDB as alternatives.

31

Filter DataFrame rows above a column's percentile in pandas

Compute the threshold with Series.quantile(q), where q is the percentile divided by 100 (0.9 for the 90th percentile), then keep the rows whose value is above it with a boolean mask: df[df["col"] > df["col"].quantile(0.9)]. A reusable function should validate the percentile, choose between strictly greater and greater-or-equal (ties at the threshold matter on small or discrete data), say which interpolation method is used (pandas defaults to “linear”, so the threshold may fall between two observed values), ignore NaN (quantile skips them, and NaN comparisons are False), and optionally work within groups using groupby().transform("quantile"), for example the top 10% of orders in each city. For a fixed top count instead of a percentile, nlargest(n, col) is simpler.

Read full answer

Applies topandas 2.2+

Code Example

import numpy as np
import pandas as pd

def filter_above_percentile(df, column, percentile, inclusive=False, by=None):
    """Rows where `column` is above its `percentile` (0-100), overall or within groups `by`."""
    if not 0 <= percentile <= 100:
        raise ValueError("percentile must be between 0 and 100")
    q = percentile / 100
    if by is None:
        threshold = df[column].quantile(q)
    else:
        threshold = df.groupby(by)[column].transform(lambda s: s.quantile(q))
    mask = df[column] >= threshold if inclusive else df[column] > threshold
    return df[mask]

orders = pd.DataFrame({
    "order_id": range(1, 11),
    "city": ["Pune", "Pune", "Pune", "Pune", "Pune", "Delhi", "Delhi", "Delhi", "Delhi", "Delhi"],
    "amount": [120, 340, 90, 560, 210, 75, 980, 430, 150, np.nan],
})

print(orders["amount"].quantile(0.75))
# 430.0
print(filter_above_percentile(orders, "amount", 75)["order_id"].tolist())
# [4, 7]
print(filter_above_percentile(orders, "amount", 75, inclusive=True)["order_id"].tolist())
# [4, 7, 8]
print(filter_above_percentile(orders, "amount", 50, by="city")["order_id"].tolist())
# [2, 4, 7, 8]
print(orders.nlargest(2, "amount")["order_id"].tolist())
# [7, 4]

The 9 non-missing amounts sorted are 75, 90, 120, 150, 210, 340, 430, 560, 980; the 75th percentile with linear interpolation sits at position 0.75 · 8 = 6, which is 430 exactly. Within Pune the median is 210, so orders 2 (340) and 4 (560) pass; within Delhi (75, 980, 430, 150) the median is 290, so orders 7 and 8 pass. The missing amount is never selected.

Common Pitfalls

  • Passing 90 instead of 0.9 to quantile (it raises an error for values above 1).

  • Forgetting ties: > drops rows equal to the threshold.

  • Computing the threshold on the full data and then filtering only a subset, or the reverse.

  • Expecting NaN rows to appear in either result.

Interview Tips

  • Show quantile + boolean mask and a group-wise version.

  • Mention interpolation, ties and nlargest.

32

Concatenate two DataFrames and handle overlapping indices

pd.concat([a, b]) stacks DataFrames but keeps their original index labels, so when both use a default 0..n index the result has duplicate labels, which later breaks .loc lookups, alignment and some joins; handle it according to what the index means. If the index is just a row number, pass ignore_index=True for a fresh RangeIndex; if you need to remember the source, pass keys=["jan", "feb"] for a MultiIndex (or add a source column); if the index is a real key such as an order ID and the same key can appear in both frames, decide which version wins, for example keep the newer one with concat then ~index.duplicated(keep="last"), or use combine_first to fill gaps in one frame from the other. verify_integrity=True makes concat raise an error on overlaps, which is a good safety check, and columns that exist in only one frame become NaN unless you pass join="inner".

Read full answer

Applies topandas 2.2+

Code Example

import pandas as pd

def concat_handling_overlap(first, second, strategy="renumber"):
    """
    strategy:
      "renumber"   - discard old labels (ignore_index=True)
      "keys"       - keep labels, add a source level
      "prefer_new" - same label in both: keep the row from `second`
      "strict"     - raise if any label repeats
    """
    if strategy == "renumber":
        return pd.concat([first, second], ignore_index=True)
    if strategy == "keys":
        return pd.concat([first, second], keys=["first", "second"])
    if strategy == "prefer_new":
        combined = pd.concat([first, second])
        return combined[~combined.index.duplicated(keep="last")].sort_index()
    if strategy == "strict":
        return pd.concat([first, second], verify_integrity=True)
    raise ValueError(f"unknown strategy {strategy!r}")

jan = pd.DataFrame({"order": ["A", "B"], "amount": [100, 200]})
feb = pd.DataFrame({"order": ["C", "D"], "amount": [150, 50]})
print(pd.concat([jan, feb]).index.tolist())
# [0, 1, 0, 1]   duplicate labels
print(concat_handling_overlap(jan, feb).index.tolist())
# [0, 1, 2, 3]
print(concat_handling_overlap(jan, feb, "keys").loc["second", "order"].tolist())
# ['C', 'D']

old = pd.DataFrame({"status": ["open", "open", "closed"]}, index=["o1", "o2", "o3"])
new = pd.DataFrame({"status": ["shipped", "open"]}, index=["o2", "o4"])
print(concat_handling_overlap(old, new, "prefer_new")["status"].to_dict())
# {'o1': 'open', 'o2': 'shipped', 'o3': 'closed', 'o4': 'open'}

try:
    concat_handling_overlap(old, new, "strict")
except ValueError as exc:
    print("strict mode:", "overlapping" in str(exc))
# strict mode: True

partial = pd.DataFrame({"status": [None], "carrier": ["DHL"]}, index=["o3"])
print(partial.combine_first(old).loc["o3"].to_dict())       # fill gaps from `old`
# {'carrier': 'DHL', 'status': 'closed'}

combine_first returns the union of rows and columns, sorted, so the o3 row has carrier from partial and status filled from old.

Common Pitfalls

  • Leaving duplicate index labels after concat, then getting several rows from df.loc[label].

  • Columns that differ between frames producing NaN columns unnoticed.

  • Calling concat in a loop; collect frames in a list and concatenate once.

  • Using DataFrame.append, which was removed in pandas 2.0.

Interview Tips

  • Name ignore_index, keys, verify_integrity and de-duplication by label.

  • Ask what the index means before choosing a strategy.

33

Write a cleaning function that drops mostly-empty columns and mean-fills the rest

The function needs three steps: measure the share of missing values per column with df.isna().mean(), drop the columns where that share is above 0.5, and fill the remaining missing values with each column’s mean, which only makes sense for numeric columns, so non-numeric columns need their own rule (for example the most frequent value, or leave them for a later step). A reliable version returns a copy instead of changing the input, makes the threshold a parameter, reports what it dropped and the fill values it used, handles the edge case of a numeric column that is entirely missing (already dropped by the rule), and, for machine learning, learns the means from training data only and reuses them on test data, which is exactly what scikit-learn’s SimpleImputer(strategy="mean") does inside a pipeline.

Read full answer

Applies topandas 2.2+

Code Example

import numpy as np
import pandas as pd

def clean_missing(df, max_missing=0.5, fitted=None):
    """
    Drop columns with more than `max_missing` share of NaN, then fill numeric NaN with the column mean
    and non-numeric NaN with the most frequent value. Pass `fitted` (the report from a training run)
    to drop the same columns and reuse the same fill values. Returns (clean_df, report).
    """
    out = df.copy()
    missing_share = out.isna().mean()
    if fitted is None:
        dropped = missing_share[missing_share > max_missing].index.tolist()
        fill_values = None
    else:
        dropped, fill_values = fitted["dropped"], fitted["fill_values"]
    out = out.drop(columns=[c for c in dropped if c in out.columns])

    if fill_values is None:
        numeric = out.select_dtypes(include="number").columns
        other = out.columns.difference(numeric)
        fill_values = {c: out[c].mean() for c in numeric}
        fill_values.update({c: out[c].mode().iloc[0] for c in other if out[c].notna().any()})
    out = out.fillna(value={c: v for c, v in fill_values.items() if c in out.columns})
    report = {"dropped": dropped, "fill_values": fill_values,
              "missing_share": missing_share.round(2).to_dict()}
    return out, report

raw = pd.DataFrame({
    "age": [25, np.nan, 35, 40],
    "income": [50_000, 60_000, np.nan, 80_000],
    "notes": [np.nan, np.nan, np.nan, "vip"],
    "city": ["Pune", None, "Pune", "Delhi"],
})
clean, report = clean_missing(raw)
print(report["dropped"])
# ['notes']
print(clean.to_dict("list"))
# {'age': [25.0, 33.333333333333336, 35.0, 40.0], 'income': [50000.0, 60000.0, 63333.333333333336, 80000.0],
#  'city': ['Pune', 'Pune', 'Pune', 'Delhi']}

test = pd.DataFrame({"age": [np.nan], "income": [np.nan], "notes": ["x"], "city": [None]})
test_clean, _ = clean_missing(test, fitted=report)          # same columns dropped, training means reused
print(test_clean.round(1).to_dict("list"))
# {'age': [33.3], 'income': [63333.3], 'city': ['Pune']}

“notes” is 75% missing, so it is dropped; “age” and “income” are 25% missing and filled with their means, (25 + 35 + 40) / 3 and (50,000 + 60,000 + 80,000) / 3. In the test run every column of the single row is missing except notes, so recomputing the 50% rule there would drop the wrong columns; reusing the training report drops notes and fills the rest with the training statistics.

Common Pitfalls

  • Filling text columns with a mean (it raises an error or does nothing).

  • Computing means on the full dataset before splitting into train and test.

  • Mean imputation on skewed data (income), where the median is often better.

  • Silent changes; return a report of what was dropped and filled.

Interview Tips

  • Show isna().mean(), the drop rule and fillna with a dict.

  • Mention non-numeric columns, reusing training statistics and SimpleImputer.

34

Build a pandas pipeline that ingests, processes and summarizes time-series CSV data

A clean pipeline splits the work into small, testable functions chained with DataFrame.pipe: ingest (read the CSV with explicit usecols, dtype and parse_dates, then sort and set a DatetimeIndex), validate (check required columns, drop duplicate timestamps, count and log bad rows), process (convert timezones, reindex to a regular frequency, fill short gaps with interpolation while leaving long gaps missing, and add features such as rolling means), and summarize (resample to daily or weekly totals, compute statistics per group, and save the result to Parquet or CSV). Each step takes and returns a DataFrame, so the whole pipeline reads top to bottom and individual steps can be unit-tested; for large files, read in chunks or move to Polars or DuckDB.

Read full answer

Applies topandas 2.2+

Code Example

import io

import pandas as pd

CSV = io.StringIO(
    "timestamp,sensor,value\n"
    "2026-06-01 00:00,s1,10\n"
    "2026-06-01 01:00,s1,12\n"
    "2026-06-01 01:00,s1,12\n"        # duplicate row
    "2026-06-01 03:00,s1,16\n"        # 02:00 missing
    "2026-06-01 00:00,s2,5\n"
    "2026-06-01 01:00,s2,bad\n"       # invalid value
    "2026-06-01 02:00,s2,7\n"
    "2026-06-02 00:00,s1,20\n"
)

def ingest(source):
    df = pd.read_csv(source, usecols=["timestamp", "sensor", "value"], dtype={"sensor": "category"},
                     parse_dates=["timestamp"])
    df["value"] = pd.to_numeric(df["value"], errors="coerce")
    return df.sort_values(["sensor", "timestamp"])

def validate(df):
    bad = int(df["value"].isna().sum())
    dupes = int(df.duplicated(["sensor", "timestamp"]).sum())
    print(f"validate: {bad} invalid values, {dupes} duplicate rows")
    return df.drop_duplicates(["sensor", "timestamp"], keep="last")

def regularize(df, freq="h", max_gap=2):
    parts = []
    for sensor, g in df.groupby("sensor", observed=True):
        g = g.set_index("timestamp")["value"]
        full = g.reindex(pd.date_range(g.index.min(), g.index.max(), freq=freq))
        parts.append(full.interpolate(limit=max_gap, limit_area="inside").rename_axis("timestamp")
                         .to_frame("value").assign(sensor=sensor))
    return pd.concat(parts).reset_index()

def summarize(df):
    daily = (df.set_index("timestamp")
               .groupby("sensor")["value"]
               .resample("D")
               .agg(["count", "mean", "max"]))
    return daily.round(2)

summary = ingest(CSV).pipe(validate).pipe(regularize).pipe(summarize)
# validate: 1 invalid values, 1 duplicate rows
print(summary.reset_index().values.tolist())
# [['s1', Timestamp('2026-06-01 00:00:00'), 6, 14.1, 16.38], ['s1', Timestamp('2026-06-02 00:00:00'), 1, 20.0, 20.0], ...]

Sensor s1 runs hourly from 1 June 00:00 to 2 June 00:00. After reindexing, 02:00 is filled with 14 (between 12 and 16), and the 20 empty slots from 04:00 to 23:00 form a long gap, so limit=2 fills only 04:00 and 05:00 (16.19 and 16.38, on the line from 16 to 20) and leaves the rest missing. 1 June therefore has 6 values with mean 14.1 and maximum 16.38, and the count column shows how much of the day is real data.

Pipeline Practices

Practice Why
Small functions + pipe Readable and testable
Explicit dtypes and dates on read Fast, correct parsing
Validation with counts Bad data doesn’t vanish silently
Limit interpolation (limit) Don’t invent long stretches of data
Per-group reindexing Each sensor has its own range
Save as Parquet Typed, fast for the next step

Common Pitfalls

  • Interpolating across long outages, which fabricates data.

  • Resampling with mixed timezones or unsorted timestamps.

  • Dropping invalid rows without logging how many.

  • One giant function that can’t be tested.

Interview Tips

  • Describe ingest → validate → process → summarize with pipe.

  • Mention gap limits, per-group handling and logging.

35

Write a function that computes a correlation matrix and plots a seaborn heatmap

The function should select the numeric columns (df.select_dtypes("number")), compute the correlation matrix with DataFrame.corr(method=...), offering “pearson” for linear relationships and “spearman” or “kendall” for monotonic or ranked ones, and draw it with sns.heatmap using a diverging colour map centred at zero (cmap="RdBu_r", vmin=-1, vmax=1), annotations with two decimals, and a mask that hides the redundant upper triangle. Return the matrix (and the Axes) so callers can use the numbers, list the strongest pairs, and handle edge cases: constant columns give NaN correlations, few rows make estimates noisy, and min_periods controls how many overlapping values a pair needs. Correlation shows linear association only and is sensitive to outliers, which is why Spearman is a useful second look.

Read full answer

Applies topandas 2.2+, seaborn 0.13+, Matplotlib 3.8+

Code Example

import matplotlib

matplotlib.use("Agg")
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import seaborn as sns

def correlation_heatmap(df, method="pearson", min_periods=3, title=None, path=None, figsize=(7, 6)):
    """Compute the correlation matrix of numeric columns and draw a lower-triangle heatmap."""
    numeric = df.select_dtypes(include="number")
    constant = numeric.columns[numeric.nunique(dropna=True) <= 1]
    numeric = numeric.drop(columns=constant)
    corr = numeric.corr(method=method, min_periods=min_periods)

    mask = np.triu(np.ones_like(corr, dtype=bool), k=1)
    fig, ax = plt.subplots(figsize=figsize)
    sns.heatmap(corr, mask=mask, annot=True, fmt=".2f", cmap="RdBu_r", vmin=-1, vmax=1,
                center=0, square=True, linewidths=0.5, cbar_kws={"shrink": 0.8}, ax=ax)
    ax.set_title(title or f"{method.title()} correlation")
    fig.tight_layout()
    if path:
        fig.savefig(path, dpi=120)
    return corr, ax

def top_pairs(corr, n=3):
    pairs = corr.where(np.triu(np.ones_like(corr, dtype=bool), k=1)).stack()
    return pairs.reindex(pairs.abs().sort_values(ascending=False).index).head(n)

rng = np.random.default_rng(42)
n = 200
ads = rng.uniform(0, 100, n)
df = pd.DataFrame({
    "ad_spend": ads,
    "visits": 3 * ads + rng.normal(0, 30, n),
    "sales": 0.5 * ads + rng.normal(0, 15, n),
    "returns": rng.normal(5, 2, n),
    "region": rng.choice(["N", "S"], n),          # non-numeric: ignored
    "version": 1,                                  # constant: dropped
})
corr, ax = correlation_heatmap(df, path="corr_heatmap.png")
print(corr.columns.tolist())
# ['ad_spend', 'visits', 'sales', 'returns']
print(top_pairs(corr).round(2))
# ad_spend-visits is the strongest pair (about 0.95), then the pairs involving sales; returns is near 0
spearman, _ = correlation_heatmap(df, method="spearman", path="spearman_heatmap.png")

Choosing the Method

Method Measures Robust to outliers
Pearson Linear association No
Spearman Monotonic association (ranks) Yes
Kendall Rank agreement, small samples Yes

Common Pitfalls

  • Including ID or code columns that are numeric but meaningless.

  • Sequential colour maps that hide the sign of correlations.

  • Reading causation from correlation.

  • Huge heatmaps with hundreds of variables; show the top pairs or cluster them (sns.clustermap).

Interview Tips

  • Show numeric selection, corr, a masked diverging heatmap and returning the matrix.

  • Mention Pearson vs Spearman and constant columns.

36

How do you combine several datetime columns into the earliest datetime?

Convert every candidate column to a real datetime first (pd.to_datetime(..., errors="coerce"), with the same timezone handling for all), then take the row-wise minimum across them with df[cols].min(axis=1), which skips missing values by default (skipna=True), so a row with some empty dates still gets the earliest of the dates it has and a row with none gets NaT. To also record which column supplied the earliest date, use idxmin(axis=1) on rows that have at least one date. Watch for mixed timezones (normalize everything to UTC with utc=True or tz_convert before comparing, because pandas refuses to compare naive and aware timestamps), for placeholder dates such as 1900-01-01 that should be treated as missing, and for string columns, which would otherwise be compared alphabetically.

Read full answer

Applies topandas 2.2+

Code Example

import pandas as pd

df = pd.DataFrame({
    "order_id": [1, 2, 3, 4],
    "created": ["2026-03-05 10:00", "2026-03-07 09:30", None, "1900-01-01"],
    "paid": ["2026-03-05 09:45", None, None, "2026-03-09 12:00"],
    "shipped": ["2026-03-06 08:00", "2026-03-06 18:00", None, "2026-03-10 07:00"],
})
date_cols = ["created", "paid", "shipped"]

for col in date_cols:
    df[col] = pd.to_datetime(df[col], errors="coerce")
    df[col] = df[col].mask(df[col] < "2000-01-01")          # treat placeholder dates as missing

df["earliest"] = df[date_cols].min(axis=1)
has_any = df[date_cols].notna().any(axis=1)
df["earliest_source"] = df.loc[has_any, date_cols].idxmin(axis=1)
print(df[["order_id", "earliest", "earliest_source"]].astype(str).values.tolist())
# [['1', '2026-03-05 09:45:00', 'paid'], ['2', '2026-03-06 18:00:00', 'shipped'],
#  ['3', 'NaT', 'nan'], ['4', '2026-03-09 12:00:00', 'paid']]

aware = pd.DataFrame({
    "local_event": pd.to_datetime(["2026-03-05 10:00"]).tz_localize("Asia/Kolkata"),
    "utc_event": pd.to_datetime(["2026-03-05 05:00"]).tz_localize("UTC"),
})
as_utc = aware.apply(lambda col: col.dt.tz_convert("UTC"))
print(as_utc.min(axis=1).iloc[0], as_utc.idxmin(axis=1).iloc[0])
# 2026-03-05 04:30:00+00:00 local_event

10:00 in India (UTC+5:30) is 04:30 UTC, so the “local” event is earlier than the 05:00 UTC event even though its clock time looks later. Order 4’s 1900-01-01 placeholder is masked, so its earliest real date is the payment.

Common Pitfalls

  • Comparing string columns, which sorts “10/03” before “9/03”.

  • Mixing naive and timezone-aware columns.

  • Placeholder dates (1900-01-01, 1970-01-01) winning the minimum.

  • idxmin on rows that are all NaT, which is deprecated behaviour; filter them first.

Interview Tips

  • Convert, normalize timezones, then min(axis=1).

  • Mention skipna, idxmin for the source column and placeholders.

37

How would you use pandas to preprocess data for a machine learning model?

Use pandas for the steps that shape the dataset and scikit-learn pipelines for the steps that learn parameters: in pandas, load data with explicit dtypes, fix types (to_numeric, to_datetime, category), remove duplicates and impossible values, define the target, engineer features from dates (day of week, tenure), text and groups (customer-level aggregations with groupby and merge), and split the data, by time when the problem is temporal; then put imputation, scaling and encoding (SimpleImputer, StandardScaler, OneHotEncoder) inside a ColumnTransformer so their statistics come from the training rows only. This division avoids the most common error, leakage: computing means, scalers, encodings or aggregations on all data, including the test set or future information. Keep the pandas feature code in functions so the same code runs in training and in production, and check the final feature frame (no NaN where not expected, dtypes, row counts, class balance) before fitting.

Read full answer

Applies topandas 2.2+, scikit-learn 1.5+

Who Does What

Step pandas scikit-learn pipeline
Load, fix types, dedupe Yes
Target definition Yes
Date/group/text features Yes (no learned statistics)
Train/test split Yes (time-aware when needed) train_test_split
Imputation SimpleImputer
Scaling StandardScaler
Encoding OneHotEncoder, OrdinalEncoder
Model Estimator

Code Example

import numpy as np
import pandas as pd
from sklearn.compose import ColumnTransformer
from sklearn.impute import SimpleImputer
from sklearn.linear_model import LogisticRegression
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import OneHotEncoder, StandardScaler

rng = np.random.default_rng(42)
n = 600
raw = pd.DataFrame({
    "customer_id": np.arange(n),
    "signup": pd.Timestamp("2025-01-01") + pd.to_timedelta(rng.integers(0, 500, n), unit="D"),
    "plan": rng.choice(["free", "pro", "team", None], n, p=[0.5, 0.3, 0.15, 0.05]),
    "monthly_spend": np.where(rng.random(n) < 0.1, np.nan, rng.gamma(2, 20, n)),
    "support_tickets": rng.poisson(1.5, n),
})
raw["churned"] = (rng.random(n) < 0.2 + 0.05 * raw["support_tickets"].clip(upper=4)).astype(int)

def build_features(df, as_of):
    out = df.copy()
    out["tenure_days"] = (as_of - out["signup"]).dt.days
    out["signup_weekday"] = out["signup"].dt.dayofweek
    out["plan"] = out["plan"].astype("category")
    return out.drop(columns=["customer_id", "signup"])

data = build_features(raw, as_of=pd.Timestamp("2026-06-01"))
order = raw["signup"].argsort().to_numpy()                       # time-based split: older customers train
train, test = data.iloc[order[:450]], data.iloc[order[450:]]
X_train, y_train = train.drop(columns="churned"), train["churned"]
X_test, y_test = test.drop(columns="churned"), test["churned"]

numeric = ["monthly_spend", "support_tickets", "tenure_days", "signup_weekday"]
categorical = ["plan"]
prep = ColumnTransformer([
    ("num", Pipeline([("impute", SimpleImputer(strategy="median")), ("scale", StandardScaler())]), numeric),
    ("cat", Pipeline([("impute", SimpleImputer(strategy="most_frequent")),
                      ("onehot", OneHotEncoder(handle_unknown="ignore"))]), categorical),
])
model = Pipeline([("prep", prep), ("clf", LogisticRegression(max_iter=1000))]).fit(X_train, y_train)
print(f"test accuracy {model.score(X_test, y_test):.3f}, features after encoding: "
      f"{model.named_steps['prep'].transform(X_test.head(1)).shape[1]}")
# 4 numeric + 3 plan levels = 7 features

The imputer fills the missing plan values with the most frequent plan before encoding, so three plan columns remain (free, pro, team).

Common Pitfalls

  • Fitting scalers, imputers or encoders on the full dataset.

  • Group features (customer averages) computed using future rows or the target.

  • get_dummies separately on train and test, giving mismatched columns.

  • Random splits on time-ordered data.

Interview Tips

  • Separate pandas shaping from pipeline-learned preprocessing.

  • Stress leakage prevention and reusable feature functions.

38

Flag rows more than three standard deviations from the column mean

For each numeric column, compute the z-score (x − mean) / std in one vectorized step, (df[cols] - df[cols].mean()) / df[cols].std(), then flag a value when its absolute z-score is above 3; a row is flagged when any of its columns is, and keeping the per-column flags shows which value triggered it. Make it robust and useful: skip NaN (pandas does by default), ignore constant columns (std of 0 gives infinite or NaN z-scores), optionally compute statistics within groups (groupby().transform), and remember that the outliers themselves inflate the mean and standard deviation (masking), so on small or heavily contaminated data a robust version based on the median and MAD, the modified z-score 0.6745·(x − median) / MAD > 3.5, catches more. Flag rather than delete, so someone can review the rows.

Read full answer

Applies topandas 2.2+

Code Example

import numpy as np
import pandas as pd

def flag_outliers(df, columns=None, threshold=3.0, by=None, robust=False):
    """Add per-column flags and an `is_outlier` column; returns a new DataFrame."""
    out = df.copy()
    cols = columns or out.select_dtypes(include="number").columns.tolist()
    group = out.groupby(by)[cols] if by else None

    if robust:
        median = group.transform("median") if by else out[cols].median()
        mad = (out[cols] - median).abs()
        mad = mad.groupby(out[by]).transform("median") if by else mad.median()
        score = 0.6745 * (out[cols] - median) / mad.replace(0, np.nan)
        threshold = 3.5 if threshold == 3.0 else threshold
    else:
        mean = group.transform("mean") if by else out[cols].mean()
        std = group.transform("std") if by else out[cols].std()
        score = (out[cols] - mean) / std.replace(0, np.nan)

    flags = score.abs() > threshold
    out[[f"{c}_outlier" for c in cols]] = flags.to_numpy()
    out["is_outlier"] = flags.any(axis=1)
    return out

rng = np.random.default_rng(42)
df = pd.DataFrame({
    "amount": np.r_[rng.normal(100, 10, 200), [400.0]],
    "items": np.r_[rng.integers(1, 6, 200), [3]],
    "region": ["N"] * 101 + ["S"] * 100,
})

flagged = flag_outliers(df, columns=["amount", "items"])
print(flagged.loc[flagged["is_outlier"], ["amount", "amount_outlier", "items_outlier"]].tail(1).values.tolist())
# [[400.0, True, False]]   the 400 amount is about 12 standard deviations away, even though it inflates the std
print(int(flagged["is_outlier"].sum()) >= 1)
# True

small = pd.DataFrame({"x": [10, 11, 9, 10, 12, 95]})
print(flag_outliers(small)["is_outlier"].tolist())                 # 95 inflates the std (masking)
# [False, False, False, False, False, False]
print(flag_outliers(small, robust=True)["is_outlier"].tolist())    # median/MAD catches it
# [False, False, False, False, False, True]

In the six-value example the mean is 24.5 and the standard deviation is about 34.6, so 95 has a z-score of only about 2.0; its modified z-score is 0.6745 · (95 − 10.5) / 1.0 ≈ 57, far above 3.5. In the large sample, a few ordinary rows can also cross |z| = 3 by chance (about 0.3% for normal data).

Common Pitfalls

  • Deleting flagged rows automatically when they may be real (fraud, big customers).

  • Masking: extreme values inflate the standard deviation, especially in small samples.

  • Applying a normal-distribution rule to skewed data (log-transform or use robust scores).

  • Constant columns producing division by zero.

Interview Tips

  • Show the vectorized z-score and any(axis=1).

  • Mention masking, the MAD-based modified z-score and flagging instead of deleting.

39

How would you prepare and clean ecommerce sales data with pandas?

Start by making each table trustworthy: parse types (order dates as datetimes in one timezone, prices and quantities as numbers, IDs as strings), standardize text (trim and lowercase emails and product names, map country spellings), remove exact duplicate order lines, separate or flag cancellations, returns and test orders (negative quantities, zero prices, internal accounts), and handle missing customer IDs by keeping them for revenue totals but excluding them from customer-level analysis. Then build analysis-ready views: line revenue (quantity × unit price minus discounts), order-level totals with groupby("order_id"), customer-level metrics with named aggregations (first and last purchase, number of orders, total spend, average order value), RFM scores (recency, frequency, monetary) with qcut, monthly cohorts from each customer’s first purchase month, and product or category summaries. Validate at each step with row counts and revenue totals, so cleaning doesn’t silently change the numbers people will report.

Read full answer

Applies topandas 2.2+

Cleaning Checklist

Issue Treatment
Dates as text, mixed timezones to_datetime(utc=True), convert to business timezone
Duplicate lines drop_duplicates() on the line key
Cancellations / returns Flag by invoice prefix or negative quantity; analyse separately
Test or internal orders Exclude by account list or email domain
Missing customer ID Keep for revenue, exclude from customer metrics
Prices ≤ 0, extreme quantities Investigate, flag
Inconsistent text .str.strip().str.lower(), mapping tables

Code Example

import pandas as pd

lines = pd.DataFrame({
    "invoice": ["1001", "1001", "1002", "C1003", "1004", "1004", "1005"],
    "customer": ["c1", "c1", "c2", "c1", None, "c3", "c2"],
    "product": [" Mug ", "tea", "Mug", "mug", "Tea ", "lamp", "Tea"],
    "qty": [2, 1, 1, -2, 3, 1, 2],
    "unit_price": [250.0, 120.0, 250.0, 250.0, 120.0, 0.0, 120.0],
    "ts": ["2026-01-03 10:00", "2026-01-03 10:00", "2026-01-20 14:30", "2026-01-25 09:00",
           "2026-02-02 11:00", "2026-02-03 16:00", "2026-02-15 12:00"],
})

df = lines.assign(
    product=lines["product"].str.strip().str.lower(),
    ts=pd.to_datetime(lines["ts"]),
    is_return=lines["invoice"].str.startswith("C") | (lines["qty"] < 0),
    is_free=lines["unit_price"] <= 0,
)
df["revenue"] = df["qty"] * df["unit_price"]
sales = df[~df["is_return"] & ~df["is_free"]]
print(f"rows {len(lines)} -> sales lines {len(sales)}, revenue {sales['revenue'].sum():.0f}")
# rows 7 -> sales lines 5, revenue 1470

customers = (sales.dropna(subset=["customer"])
                  .groupby("customer")
                  .agg(orders=("invoice", "nunique"), spend=("revenue", "sum"),
                       first=("ts", "min"), last=("ts", "max")))
customers["avg_order_value"] = customers["spend"] / customers["orders"]
customers["recency_days"] = (pd.Timestamp("2026-03-01") - customers["last"]).dt.days
print(customers[["orders", "spend", "avg_order_value", "recency_days"]].to_dict("index"))
# {'c1': {'orders': 1, 'spend': 620.0, 'avg_order_value': 620.0, 'recency_days': 56},
#  'c2': {'orders': 2, 'spend': 490.0, 'avg_order_value': 245.0, 'recency_days': 13}}

monthly = sales.groupby([sales["ts"].dt.to_period("M"), "product"])["revenue"].sum().unstack(fill_value=0)
print(monthly.to_dict("index"))
# {Period('2026-01', 'M'): {'mug': 750.0, 'tea': 120.0}, Period('2026-02', 'M'): {'mug': 0.0, 'tea': 600.0}}

returns_value = -df.loc[df["is_return"], "revenue"].sum()
print(f"returns {returns_value:.0f}")
# returns 500

Sales revenue is 500 + 120 (invoice 1001) + 250 (1002) + 360 (1004, no customer) + 240 (1005) = 1,470. The free lamp and the return are excluded; the anonymous order counts toward revenue but not toward customers.

Common Pitfalls

  • Mixing returns into sales, which understates revenue and distorts averages.

  • Counting order lines as orders; use nunique on the invoice.

  • Dropping rows without customer IDs from revenue totals.

  • Not reconciling totals with the finance system after cleaning.

Interview Tips

  • Walk through types, duplicates, returns, test orders and missing IDs.

  • Show customer-level aggregation (RFM, cohorts) and validation of totals.

40

How do you merge multiple time series datasets in pandas?

First put every series on the same footing: parse timestamps as datetimes, convert them to one timezone, sort them, remove duplicate timestamps, and bring them to a common frequency with resample (aggregate higher-frequency data such as minute prices to hourly means, or upsample lower-frequency data such as daily weather with ffill only where carrying values forward is valid). Then combine them: for series on exactly matching timestamps use pd.concat([...], axis=1) or an outer merge/join on the index, which aligns by time and leaves NaN where a series has no value; for series sampled at different, irregular moments use pd.merge_asof, which matches each row to the most recent earlier observation (optionally within a tolerance and per key with by=), the safe choice for trades and quotes or sensor readings because it never looks into the future. Finally, handle the gaps deliberately per column (interpolate short gaps, forward-fill slowly changing values with a limit, leave long outages missing) and record how many values were filled.

Read full answer

Applies topandas 2.2+

Steps

Step Tools
Standardize time to_datetime(utc=True), tz_convert, sort_index, drop duplicates
Common frequency resample("h").mean(), asfreq, ffill(limit=...)
Exact alignment pd.concat(axis=1), join(how="outer")
Nearest earlier match pd.merge_asof(direction="backward", tolerance=...)
Gap handling interpolate(limit=...), ffill(limit=...), keep NaN
Check Count NaN per column, row counts, plots

Code Example

import pandas as pd

temp = pd.Series([20.0, 21.0, 23.0],
                 index=pd.to_datetime(["2026-07-01 00:00", "2026-07-01 01:00", "2026-07-01 03:00"]),
                 name="temp_c")
power = pd.Series([5.0, 5.5, 5.2, 6.0, 6.4, 7.0],
                  index=pd.date_range("2026-07-01 00:00", periods=6, freq="30min"),
                  name="power_kw")

hourly_power = power.resample("h").mean()                       # 30-minute data -> hourly means
hourly_temp = temp.resample("h").mean()                         # inserts NaN at 02:00
combined = pd.concat([hourly_temp, hourly_power], axis=1)
print(combined.to_dict("list"))
# {'temp_c': [20.0, 21.0, nan, 23.0], 'power_kw': [5.25, 5.6, 6.7, nan]}

combined["temp_c"] = combined["temp_c"].interpolate(limit=1)
print(combined["temp_c"].tolist())
# [20.0, 21.0, 22.0, 23.0]

trades = pd.DataFrame({"time": pd.to_datetime(["2026-07-01 09:00:02", "2026-07-01 09:00:07", "2026-07-01 09:00:30"]),
                       "price": [101.0, 101.5, 102.0]})
quotes = pd.DataFrame({"time": pd.to_datetime(["2026-07-01 09:00:00", "2026-07-01 09:00:05"]),
                       "bid": [100.8, 101.2]})
matched = pd.merge_asof(trades, quotes, on="time", direction="backward", tolerance=pd.Timedelta("10s"))
print(matched["bid"].tolist())
# [100.8, 101.2, nan]   the last trade is more than 10 s after the latest quote

The combined frame covers the union of timestamps, 00:00 to 03:00: temperature has no reading at 02:00 and power has none at 03:00. Interpolation fills the short gap inside the temperature series (22.0), while the missing power value at the end stays NaN instead of being invented; that is the gap policy made explicit.

Common Pitfalls

  • Mixing timezones or naive and aware timestamps.

  • Forward-filling across long gaps, which invents data.

  • merge_asof on unsorted data (it raises an error) or with direction="nearest" when future values must not be used.

  • Resampling with the wrong aggregation (summing temperatures, averaging counts).

Interview Tips

  • Standardize, resample, align, then handle gaps per column.

  • Explain merge_asof and why backward matching avoids leakage.

41

What are the advantages of vectorized operations in pandas over iteration?

Vectorized operations apply one operation to a whole column (or DataFrame) at once, df["total"] = df["qty"] * df["price"], and pandas executes them in compiled NumPy or Arrow code over contiguous arrays, while iteration (for loops, iterrows, itertuples, row-wise apply) runs Python code once per row, paying interpreter overhead, creating a Series or tuple object for every row, and looking up values by label each time. As a result vectorized code is typically 10 to more than 100 times faster, uses memory more predictably, handles missing values consistently (NaN propagates instead of raising errors halfway through), keeps dtypes intact (iterrows upcasts mixed rows to object or float), and is shorter and easier to read. Most row-by-row logic has a vectorized form: arithmetic and comparisons, np.where/np.select for conditions, .str and .dt accessors, map with a dict, groupby aggregations and transform, shift/rolling for sequential logic, and merge instead of per-row lookups. Iteration is still reasonable for truly sequential logic with state that can’t be expressed as cumulative operations, or for calling external services per row.

Read full answer

Applies topandas 2.2+, NumPy 2.x

Comparison

Vectorized Iteration
Execution Compiled loops in NumPy/Arrow Python bytecode per row
Speed Fast 10–1000× slower
dtypes Preserved iterrows upcasts rows
Missing values Propagate consistently Need manual handling
Code Short, declarative Longer, error-prone
Use for Almost everything Stateful sequential logic, external calls

Code Example

import time

import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
n = 100_000
df = pd.DataFrame({
    "qty": rng.integers(1, 10, n),
    "price": rng.uniform(5, 500, n).round(2),
    "country": rng.choice(["IN", "US", "DE"], n),
})
tax_rate = {"IN": 0.18, "US": 0.07, "DE": 0.19}

def timed(label, func):
    start = time.perf_counter()
    result = func()
    print(f"{label:14s} {time.perf_counter() - start:.4f}s")
    return result

def with_iterrows():
    out = []
    for _, row in df.iterrows():
        gross = row["qty"] * row["price"]
        out.append(gross * (1 + tax_rate[row["country"]]) if gross > 100 else gross)
    return pd.Series(out, index=df.index)

def vectorized():
    gross = df["qty"] * df["price"]
    return gross.where(gross <= 100, gross * (1 + df["country"].map(tax_rate)))

slow = timed("iterrows", with_iterrows)
fast = timed("vectorized", vectorized)
print("same result:", np.allclose(slow, fast))
# same result: True

row = next(df.iterrows())[1]
print(row.dtype)                                   # mixed row becomes object dtype
# object

On a typical laptop the iterrows version takes several seconds for 100,000 rows and the vectorized version a few milliseconds.

Common Pitfalls

  • Using apply(axis=1) and calling it vectorized; it is still a Python loop.

  • Modifying a DataFrame while iterating over it.

  • Building a DataFrame row by row with concat in a loop.

  • Forgetting that iterrows returns copies, so changing row doesn’t change df.

Interview Tips

  • Explain compiled array loops vs Python per-row overhead and dtype upcasting.

  • Show a loop rewritten with where, map or np.select.

42

How do you manage memory usage with large DataFrames?

Start by measuring: df.info(memory_usage="deep") and df.memory_usage(deep=True) show which columns are large, and object (string) columns are usually the culprits. Then reduce what you load and how it is stored: read only the needed columns (usecols) and rows (filters, nrows, chunking), set dtypes at read time instead of converting after, use category for low-cardinality strings, string[pyarrow] or dtype_backend="pyarrow" for high-cardinality text, downcast numbers (pd.to_numeric(downcast=...), int32/float32 when the precision is enough), use nullable dtypes (Int8, boolean) instead of float columns with NaN, and parse dates into datetime64 rather than strings. Store intermediate data in Parquet, which keeps dtypes and lets you read only some columns and row groups. During processing, drop unneeded columns early, avoid unnecessary copies (copy-on-write in pandas 2.x helps), delete large intermediates (del df_tmp), process big files in chunks with partial aggregations, and move to Polars, DuckDB or Dask when the data simply doesn’t fit.

Read full answer

Applies topandas 2.2+ (pyarrow for Arrow dtypes and Parquet)

Techniques

Technique Typical saving
usecols, row filters Only what you need
category for repeated strings 80–95% on those columns
string[pyarrow] for unique strings Large vs object
Downcast int64/float64 → int32/float32 (or smaller) 50–75%
Nullable small ints instead of float + NaN Up to 87%
Datetimes instead of strings Large
Parquet + column selection Less I/O and memory
Chunked processing Bounded memory

Code Example

import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
n = 500_000
df = pd.DataFrame({
    "user_id": rng.integers(0, 1_000_000, n),
    "country": rng.choice(["IN", "US", "DE", "BR", "JP"], n),
    "event": rng.choice(["view", "click", "buy"], n, p=[0.8, 0.15, 0.05]),
    "amount": rng.uniform(0, 1000, n),
    "rating": np.where(rng.random(n) < 0.3, np.nan, rng.integers(1, 6, n)),
    "ts": pd.Timestamp("2026-01-01").strftime("%Y-%m-%d"),
})

def mb(frame):
    return round(frame.memory_usage(deep=True).sum() / 1e6, 1)

print("before:", mb(df), "MB")

optimized = df.assign(
    user_id=pd.to_numeric(df["user_id"], downcast="integer"),        # fits in int32
    country=df["country"].astype("category"),
    event=df["event"].astype("category"),
    amount=df["amount"].astype("float32"),
    rating=df["rating"].astype("Int8"),                              # nullable small int
    ts=pd.to_datetime(df["ts"]),
)
print("after:", mb(optimized), "MB")
print(optimized.dtypes.astype(str).to_dict())
# {'user_id': 'int32', 'country': 'category', 'event': 'category', 'amount': 'float32',
#  'rating': 'Int8', 'ts': 'datetime64[ns]'}

optimized.to_parquet("events.parquet", index=False)
subset = pd.read_parquet("events.parquet", columns=["country", "amount"])   # read only two columns
print(subset.dtypes.astype(str).to_dict())
# {'country': 'category', 'amount': 'float32'}

The three string columns account for most of the original memory; after conversion, the frame typically shrinks by around 85–90%.

Common Pitfalls

  • Measuring without deep=True, which ignores string contents.

  • Downcasting to float32 for values that need full precision (money totals, IDs as floats).

  • Loading everything and filtering afterwards instead of filtering on read.

  • Keeping many intermediate copies alive in a notebook.

Interview Tips

  • Lead with measuring, then dtypes, column selection, Parquet and chunking.

  • Mention Arrow-backed dtypes and when to switch to Polars/DuckDB/Dask.

43

What strategies optimize pandas code performance?

Optimize in order of impact, after profiling to find the real bottleneck: first remove Python-level loops by vectorizing (arithmetic on columns, np.where/np.select, .str/.dt, map with dicts, groupby aggregations and transform, merge instead of lookups in loops); then make the data cheaper to process (categorical and Arrow dtypes, downcast numbers, only needed columns, filter rows early, Parquet instead of CSV, engine="pyarrow" for CSV parsing); then fix algorithmic patterns (collect DataFrames in a list and concat once, set and sort an index for repeated lookups, groupby(sort=False, observed=True), avoid repeated apply with lambdas, use eval/query for big expressions). If that isn’t enough, accelerate the remaining hot spots with NumPy on .to_numpy() arrays, Numba-compiled functions (engine="numba" in rolling and groupby operations, or @njit functions), or move the workload to Polars, DuckDB, Dask or Spark. Measure each change with %timeit or time.perf_counter and profile with cProfile or line_profiler, because guesses about bottlenecks are often wrong.

Read full answer

Applies topandas 2.2+, NumPy 2.x

Strategy Ladder

Level Strategy
1. Profile %timeit, cProfile, line_profiler, memory_usage(deep=True)
2. Vectorize Column operations instead of loops/apply
3. Data shape Fewer columns/rows, categorical/Arrow dtypes, Parquet
4. Patterns Concat once, indexed lookups, merges, observed=True
5. Compile NumPy arrays, Numba
6. Other engines Polars, DuckDB, Dask, Spark

Code Example: Three Common Rewrites

import time

import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
n = 200_000
orders = pd.DataFrame({
    "customer": rng.integers(0, 5_000, n),
    "amount": rng.uniform(1, 500, n),
    "segment": rng.choice(["a", "b", "c"], n),
})
customers = pd.DataFrame({"customer": np.arange(5_000), "tier": rng.choice(["gold", "silver"], 5_000)})

def timed(label, func):
    start = time.perf_counter()
    out = func()
    print(f"{label:34s} {time.perf_counter() - start:.4f}s")
    return out

# 1. lookups in a loop -> merge
tier_map = customers.set_index("customer")["tier"]
a = timed("lookup via apply", lambda: orders["customer"].apply(lambda c: tier_map[c]))
b = timed("merge", lambda: orders.merge(customers, on="customer", how="left")["tier"])
print("same:", a.equals(b))
# same: True

# 2. group statistic via apply -> transform
c = timed("groupby.apply share", lambda: orders.groupby("customer", group_keys=False)["amount"]
          .apply(lambda s: s / s.sum()))
d = timed("groupby.transform share", lambda: orders["amount"] / orders.groupby("customer")["amount"].transform("sum"))
print("same:", np.allclose(c.sort_index(), d))
# same: True

# 3. concat in a loop -> concat once
chunks = [orders.iloc[i:i + 2_000] for i in range(0, n, 2_000)]
def grow():
    out = chunks[0]
    for chunk in chunks[1:]:
        out = pd.concat([out, chunk])
    return out
e = timed("concat inside loop", grow)
f = timed("concat once", lambda: pd.concat(chunks))
print("same length:", len(e) == len(f))
# same length: True

Each rewrite is usually one to two orders of magnitude faster; concatenating inside a loop copies the growing frame each time, so its cost grows quadratically with the number of chunks.

Common Pitfalls

  • Micro-optimizing code that isn’t the bottleneck.

  • Replacing a loop with apply and assuming it is vectorized.

  • Converting to categorical and then joining or concatenating with mismatched categories (falls back to object).

  • Premature switch to a cluster tool for data that fits comfortably on one machine.

Interview Tips

  • Give the ladder: profile, vectorize, dtypes and I/O, patterns, compile, other engines.

  • Show one concrete rewrite such as merge instead of per-row lookups.

44

How do you process large CSV files in chunks with pandas?

Pass chunksize=N to pd.read_csv (or use with pd.read_csv(..., chunksize=N) as reader:), which returns an iterator of DataFrames of N rows each instead of one huge DataFrame, then process each chunk and keep only small partial results: filtered rows appended to an output file, running sums and counts for aggregates, or per-chunk group totals that you combine at the end. Memory stays bounded by the chunk size, so a file much larger than RAM can be summarized on a laptop. The rules are to make each chunk’s result combinable (sum sums and counts and divide at the end instead of averaging averages; median and distinct counts need special handling), to set usecols and dtype so every chunk is parsed the same way, to write outputs incrementally (append to CSV with header only once, or write a Parquet file per chunk), and to pick a chunk size that balances overhead and memory, typically 100,000 to 1,000,000 rows. For repeated analysis, convert the CSV to Parquet once; DuckDB, Polars’ streaming engine or Dask can also do this out of core automatically.

Read full answer

Applies topandas 2.2+

Combining Partial Results

Statistic Per chunk Combine
Count, sum count, sum Add
Mean Sum and count Total sum / total count
Min/max min, max Min of mins, max of maxes
Group totals groupby().sum() concat then groupby().sum()
Variance Count, sum, sum of squares Combine formulas (or Welford)
Median, distinct count Hard Approximate sketches, or a second pass

Code Example

import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
n = 250_000
pd.DataFrame({
    "store": rng.choice(["S1", "S2", "S3"], n),
    "amount": rng.uniform(1, 200, n).round(2),
    "status": rng.choice(["ok", "refund"], n, p=[0.95, 0.05]),
}).to_csv("big_sales.csv", index=False)                   # stand-in for a file too big for memory

totals = []
rows_seen = 0
first_write = True
with pd.read_csv("big_sales.csv", chunksize=50_000, usecols=["store", "amount", "status"],
                 dtype={"store": "category", "status": "category"}) as reader:
    for chunk in reader:
        rows_seen += len(chunk)
        ok = chunk[chunk["status"] == "ok"]
        totals.append(ok.groupby("store", observed=True)["amount"].agg(["sum", "count"]))
        refunds = chunk[chunk["status"] == "refund"]
        refunds.to_csv("refunds.csv", mode="w" if first_write else "a", header=first_write, index=False)
        first_write = False

summary = pd.concat(totals).groupby(level=0).sum()
summary["mean"] = summary["sum"] / summary["count"]       # mean from combined sums, not averaged means
print(rows_seen, summary.index.tolist())
# 250000 ['S1', 'S2', 'S3']

check = pd.read_csv("big_sales.csv")                      # only possible here because the demo file is small
expected = check[check["status"] == "ok"].groupby("store")["amount"].mean()
print(np.allclose(summary["mean"].sort_index(), expected.sort_index()))
# True
print(len(pd.read_csv("refunds.csv")) == int((check["status"] == "refund").sum()))
# True

The file is read in 5 chunks of 50,000 rows; at no point does the loop hold more than one chunk plus a few small summaries.

Common Pitfalls

  • Averaging per-chunk means, which is wrong when chunks have different group sizes.

  • Different dtypes inferred in different chunks (set dtype).

  • Writing the header for every chunk when appending to a CSV.

  • Accumulating every chunk in a list and concatenating at the end, which defeats the purpose.

Interview Tips

  • Show chunksize, combinable partial aggregates and incremental output.

  • Mention converting to Parquet and out-of-core tools for repeated work.

45

Why do categorical data types matter, especially with many unique values?

The category dtype stores each distinct value once and replaces every row with a small integer code, so for columns with few distinct values relative to their length (country, status, product type) it saves most of the memory of object strings and makes groupby, sorting, value_counts and comparisons faster because they work on integers; it also lets you define the allowed values and an order (CategoricalDtype(["low", "medium", "high"], ordered=True)) so sorting and comparisons follow business logic and invalid values are rejected. With many unique values the trade-off changes: when nearly every value is distinct (user IDs, emails, free text), the categories array is as large as the data itself plus the codes, conversion takes time, and memory can even grow, so category is the wrong choice there; string[pyarrow] is usually better for such columns. In between, with thousands of distinct values in millions of rows (product SKUs, cities), category still pays off, and the codes switch automatically from int8 to int16 or int32. Practical concerns are that groupby on categoricals should use observed=True to avoid creating empty groups for every category, and that combining categoricals with different categories falls back to object unless you align them.

Read full answer

Applies topandas 2.2+

When to Use Category

Distinct values vs rows Example Category?
Very few (≤ 100) Status, country Yes: large savings
Thousands in millions of rows SKU, city Usually yes
Close to the number of rows IDs, emails, text No: use string[pyarrow]
Ordered levels Size, rating band Yes, ordered categorical
Values change often Open-ended tags Careful: new values need new categories

Code Example

import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
n = 1_000_000

def mb(s):
    return round(s.memory_usage(deep=True) / 1e6, 1)

for label, n_unique in [("10 values", 10), ("5,000 values", 5_000), ("near-unique", 900_000)]:
    values = pd.Series(np.char.add("item_", rng.integers(0, n_unique, n).astype(str)))
    cat = values.astype("category")
    print(f"{label:13s}: object {mb(values):6.1f} MB, category {mb(cat):6.1f} MB, "
          f"codes {cat.cat.codes.dtype}, arrow string {mb(values.astype('string[pyarrow]')):6.1f} MB")
# 10 values    : category is about 1 MB vs tens of MB as object (int8 codes)
# 5,000 values : still a large saving (int16 codes)
# near-unique  : category saves little or nothing; Arrow strings are smallest

sizes = pd.Series(["M", "S", "L", "M"], dtype=pd.CategoricalDtype(["S", "M", "L"], ordered=True))
print(sizes.sort_values().tolist(), (sizes > "S").tolist())
# ['S', 'M', 'M', 'L'] [True, False, True, True]

status = pd.Series(["ok", "ok", "failed"], dtype=pd.CategoricalDtype(["ok", "failed", "pending"]))
print(status.value_counts().to_dict())                         # unused categories appear with 0
# {'ok': 2, 'failed': 1, 'pending': 0}
df = pd.DataFrame({"status": status, "ms": [120, 80, 400]})
print(df.groupby("status", observed=True)["ms"].mean().to_dict())
# {'ok': 100.0, 'failed': 400.0}

a = pd.Series(["x", "y"], dtype="category")
b = pd.Series(["y", "z"], dtype="category")
print(pd.concat([a, b]).dtype)                                  # different categories -> object
# object

groupby(observed=True) keeps only categories that occur, so “pending” doesn’t create an empty group.

Common Pitfalls

  • Converting near-unique columns to category.

  • Forgetting observed=True, which can create huge numbers of empty groups with several categorical keys.

  • Concatenating categoricals with different categories and silently getting object dtype; use union_categoricals or set categories first.

  • Assigning a value that isn’t a category (raises an error); add it with cat.add_categories.

Interview Tips

  • Explain codes plus categories, and how cardinality decides the benefit.

  • Mention ordered categoricals, observed=True and Arrow strings for unique text.

Sign in to keep reading

You've opened 2 full answers. Sign in to read all 45 Pandas answers, mark what you know and pick up where you left off.

Report a mistake

What's wrong?

All Machine Learning & Data Science subjects