pandas Interview Questions on Messy Data

pandas interview questions on messy data are mostly about defaults. A default is the choice pandas makes when you don't pass an argument, and when it reads, merges and groups data those choices can quietly change your result. For example, leading zeros vanish, the text "false" becomes True, and rows with a missing key disappear from a total. The ten questions below cover the defaults that cause the most trouble in practice, with code and output from pandas 3.0.2.

A good answer has two parts: the line of code that fixes the problem, and one sentence on why the default was wrong for this data. The second part is the one to rehearse, because it shows you understand the data and not just the API.

Key Takeaways

• Read files with dtype=str first, then convert columns on purpose; this keeps leading zeros and long ids intact.
• astype(bool) on text turns every non-empty string, including "false" and "no", into True. Map flags from an approved list instead.
• merge matches missing keys to each other by default; drop or flag NaN keys and use validate= and indicator=True.
• groupby drops missing keys unless you pass dropna=False, so group totals can stop adding up to the grand total.
• In pandas 3.0, chained assignment never updates the original DataFrame; use a single .loc[rows, col] = value.

The questions at a glance

Setup: the sample data

Questions 1 to 3 build their data inline. The other examples use these frames:

import io
import pandas as pd

c = pd.DataFrame({
    "customer_id": ["C1", "C1", "C2", "C2", "C3"],
    "email": ["ana@example.com", "ana.b@example.com", "ben@example.com",
              "ben@example.com", "cy@example.com"],
    "updated_at": pd.to_datetime(["2026-01-02 09:00", "2026-03-15 14:30",
                                  "2026-02-01 08:00", "2026-02-01 08:00", None]),
    "record_id": [1, 2, 3, 4, 5],
})
orders = pd.DataFrame({"order_id": [101, 102, 103, 104],
                       "customer_id": ["C1", "C2", "C9", None]})
customers = pd.DataFrame({"customer_id": ["C1", "C2", None],
                          "name": ["Ana", "Ben", "Unknown Ltd"]})
r = pd.DataFrame({"city": ["Leeds", " ", "N/A", "", None, "York"],
                  "score": [3, -999, 4, -999, 5, 2]})
d = pd.Series(["2026-03-04", "04/03/2026", "31/12/2026", "2026-02-30", "soon"])
df9 = pd.DataFrame({"status": ["new", "old"], "flag": [0, 0]})
g = pd.DataFrame({"region": ["North", "South", None, "North", None],
                  "sales": [10, 20, 30, 40, 50]})

1. Why did the ZIP code lose its leading zero?

Prompt: You load a CSV and zip shows 2134 instead of 02134. What happened?

csv = "account_id,zip,active,balance\n00123,02134,false,\"1,200.50\"\n00456,10001,TRUE,-\n00789,94105,no,300\n"
df = pd.read_csv(io.StringIO(csv))
df.dtypes  # account_id int64, zip int64, active str, balance str

Answer: read_csv inferred that account_id and zip were numbers, and integers have no leading zeros. Identifiers are labels, not quantities, so read them as text:

raw = pd.read_csv(io.StringIO(csv), dtype=str, keep_default_na=False)
raw["account_id"].tolist()  # ['00123', '00456', '00789']
raw["zip"].tolist()         # ['02134', '10001', '94105']

keep_default_na=False stops pandas from turning strings like "NA" or "null" into missing values before you have decided what they mean (pandas read_csv documentation). Long numeric ids have a related problem: if a missing value forces the column to float64, any id longer than about 15 to 16 significant digits loses digits. The free exercise keep identifiers as text and parse yes/no flags practices both.

2. Why does "false" become True?

Prompt: active holds "false", "TRUE", "no" and "". A colleague runs astype(bool). What do they get?

s = pd.Series(["false", "TRUE", "no", ""], dtype="str")
s.astype(bool).tolist()  # [True, True, True, False]

Answer: Python treats every non-empty string as true, so "false" and "no" both become True, and only the empty string becomes False. The fix is an explicit mapping that leaves unknown spellings missing:

flags = {"true": True, "yes": True, "y": True, "1": True,
         "false": False, "no": False, "n": False, "0": False}
parsed = s.str.strip().str.lower().map(flags).astype("boolean")
parsed.tolist()  # [False, True, False, <NA>]

The nullable boolean dtype keeps the blank as <NA> rather than guessing False. A strong follow-up line: "Then I'd count values that didn't map and report them."

3. How do you convert a messy numeric column?

Prompt: balance contains "1,200.50", "-" and "300". Make it numeric without losing track of failures.

bal = raw["balance"]
num = pd.to_numeric(bal.str.replace(",", "", regex=False), errors="coerce")
failed = num.isna() & bal.ne("")

Answer: errors="coerce" turns unparseable values into NaN instead of raising (pandas to_numeric documentation). On its own that hides problems, so the failed mask separates "the source was blank" from "the source could not be read." Ask what "-" means: in many finance exports it means zero, and that is a business rule, not a parsing decision. For money that must not drift, mention exact decimals rather than floats.

4. How do you keep the latest record per customer?

Prompt: Each customer can appear several times. Keep the most recently updated row.

latest = (c.sort_values(["updated_at", "record_id"], na_position="first")
           .drop_duplicates(subset="customer_id", keep="last")
           .sort_values("customer_id"))

Answer: drop_duplicates(keep="last") keeps whichever row comes last in the current order, so the sort is the survivor rule. C2 has two rows with the same timestamp; sorting by record_id as well makes the choice repeatable. na_position="first" puts unknown timestamps first, so they never win over a known one. Without the sort, the result depends on file order. The drop_duplicates documentation describes subset and keep; the free exercise pick one surviving record per account tests the same rule.

5. Why did an order with no customer get a name?

Prompt: Order 104 has no customer_id, yet after the merge it has a customer name. Explain.

orders.merge(customers, on="customer_id", how="left", indicator=True)

Answer: Unlike SQL, pandas matches missing keys to each other, as the merge documentation notes, so the order with no customer joined to a customer row with no id. Drop or flag missing keys before the merge, and use validate to catch duplicate keys on the right side:

safe = orders.merge(customers.dropna(subset=["customer_id"]),
                    on="customer_id", how="left",
                    validate="many_to_one", indicator=True)
safe["_merge"].value_counts()  # left_only 2, both 2, right_only 0

Now orders 103 and 104 are both reported as left_only, which is the honest result. See the lesson on merge with validate and indicator.

6. How do you handle blanks, "N/A" and -999?

Prompt: city has " ", "N/A" and ""; score uses -999 for missing. How many values are missing?

r["city"].isna().sum()  # 1, only the real None
r["city"] = r["city"].str.strip().replace({"": pd.NA, "N/A": pd.NA})
r["score"] = r["score"].astype("Int64").replace(-999, pd.NA)
r.isna().sum()  # city 4, score 2

Answer: pandas only counts real missing values, so the first count says one. After converting the placeholders, four cities and two scores are missing. The mean score shows why this matters: with the -999 codes left in, it is about -330.7; with them removed, it is 3.5. Strong answers keep the list of placeholder codes in one place and count how many rows each one affected. The exercise turn sentinel codes into missing values covers this.

7. How do you parse dates in mixed formats?

Prompt: A column holds 2026-03-04, 04/03/2026 (day first), 31/12/2026, 2026-02-30 and soon.

iso = pd.to_datetime(d, format="%Y-%m-%d", errors="coerce")
uk = pd.to_datetime(d, format="%d/%m/%Y", errors="coerce")
out = iso.fillna(uk)
out.notna().mean()  # 0.6

Answer: Each format is declared explicitly, so 04/03/2026 is read as 4 March because the source is known to be day-first, not because pandas guessed. 30 February and soon fail and become NaT, and the parse rate (60%) is reported rather than hidden. If different sources use different conventions, choose the format per source, as in the free exercise parse dates by declared locale. The to_datetime documentation lists the format and errors options.

8. Why did an integer column become float?

Prompt: A count column with one gap prints as 3.0, NaN, 7.0. Why, and how do you keep whole numbers?

pd.Series([3, None, 7]).dtype            # float64, values 3.0, nan, 7.0
pd.Series([3, None, 7], dtype="Int64")   # 3, <NA>, 7

Answer: NumPy integers cannot hold NaN, so pandas upcasts the column to float64. That turns counts into 3.0 and can corrupt ids when written back out. The nullable Int64 dtype keeps whole numbers whole and marks the gap as <NA> (pandas nullable integer documentation). The guide on pandas nullable dtypes covers the other nullable types.

9. Why didn't my assignment change the DataFrame?

Prompt: You set flag to 1 for new rows, but df9 still shows 0. What went wrong?

df9[df9["status"] == "new"]["flag"] = 1        # ChainedAssignmentError warning; df9 unchanged
df9.loc[df9["status"] == "new", "flag"] = 1    # works

Answer: The first line selects rows (creating a new object) and then sets a column on that new object. Under Copy-on-Write, which is the only mode in pandas 3.0, chained assignment like this never updates the original, and pandas warns with ChainedAssignmentError (pandas Copy-on-Write guide). A single .loc[rows, column] assignment does it in one step. Older interview answers mention SettingWithCopyWarning; saying that pandas 3.0 replaced that ambiguity with Copy-on-Write shows you are current. The lesson on boolean masks and .loc practices the pattern.

10. Why don't the group totals add up?

Prompt: Sales by region sum to 70, but total sales are 150. Where did 80 go?

g.groupby("region")["sales"].sum()                # North 50, South 20
g.groupby("region", dropna=False)["sales"].sum()  # North 50, South 20, NaN 80
g["sales"].sum()                                   # 150

Answer: groupby drops rows whose key is missing by default (the dropna parameter in the groupby documentation), so 80 of the 150 in sales vanished from the grouped output without any warning. Pass dropna=False to keep a missing-key group, then decide with the stakeholder how to label it. A quick reconciliation (group totals versus the grand total) catches this in any language. The exercise totals that reconcile with missing keys practices exactly this check.

How to practice pandas cleaning questions

Run each snippet, then break it: add a tie, a blank key or a new date format, and check whether your fix still holds. Follow-up questions usually add exactly those twists. The pandas interview questions page has more prompts, and the lesson on overriding the pandas defaults that change your data collects the defaults above in one place.

Frequently Asked Questions

Do pandas interviews still ask about SettingWithCopyWarning?

Some do, because older question banks are still in use. Explain the original problem (assigning to a possible copy), then say that pandas 3.0 uses Copy-on-Write, where chained assignment never updates the original and .loc in one step is the fix.

Should I use inplace=True in interview answers?

It is safer to assign the result (df = df.drop_duplicates(...)). It reads more clearly, chains well, and avoids questions about whether the operation really happened in place.

Is it fine to read everything as text in a real project?

Yes, as a first step. Reading as text preserves exactly what the file contained; you then convert each column with an explicit rule and count the failures. That is slower to write but much easier to defend.

Related posts

• 25 Data Cleaning Interview Questions (and What Interviewers Look For)
• SQL Data Cleaning Interview Questions With Worked Answers
• pandas merge Pitfalls: Duplicated Rows, Lost Rows and Null Keys
• pandas fillna Without Regret: When Filling Missing Values Is Wrong

Sources

• pandas documentation, merge, DataFrame.groupby, read_csv, to_numeric, to_datetime and DataFrame.drop_duplicates.
• pandas user guide, Nullable integer data type and Copy-on-Write.
• pandas release notes, What's new in 3.0.0.