How would you merge two pandas DataFrames and confirm the join did what you expected?

Short answer

State the expected relationship with the validate argument, add indicator=True to see where each row came from, and compare row counts before and after. A merge on duplicated keys multiplies rows, and a missing key leaves NaN without any warning.

Why

pd.merge does exactly what the keys allow, which is not always what the analyst meant. If a sample sheet lists each sample once but the results table has two rows for a repeated sample, a one-to-one join quietly becomes one-to-many and that sample is counted twice. The pandas guide warns that merging on duplicate keys produces the Cartesian product of matching rows.

Keys that differ only in formatting, such as S01 and S1 or a trailing space, do not match, so a left join leaves NaN in every result column for those samples. validate="one_to_one" or "many_to_one" checks uniqueness and raises MergeError if it fails. indicator=True adds a _merge column with left_only, right_only or both, so unmatched rows can be counted and listed.

How to work it through

Decide what relationship the tables should have before writing the merge, for example many results to one sample.
Clean and standardize the key columns in both tables, including type, case and whitespace.
Pass validate with that relationship so pandas raises MergeError if the keys break it.
Use how="outer" with indicator=True on a first run and count each _merge category.
Investigate every left_only and right_only row before switching to the join type you actually need.
Assert the final row count against the expected number in code, so the check runs every time.

An example

A sample sheet has 384 rows and a qPCR results table has 1,152 rows, three technical replicates per sample. The expected relationship is many_to_one from results to samples. An outer merge with indicator shows 1,146 both, 6 left_only and 0 right_only.

The 6 unmatched results carry the key "S-017 " with a trailing space. After stripping whitespace, all 1,152 rows match, validate passes, and an assertion that the merged frame has 1,152 rows is added to the script.

Where the answer stops holding

validate checks key uniqueness, not that the right records were paired. Two different samples given the same identifier will pass every check. Indicator counts show coverage but not whether a match is scientifically correct, so spot checks against source records are still needed.

For very large tables, an outer merge for inspection can be expensive, and counting unique keys first is cheaper.

A strong answer covers

Decide the expected cardinality before merging
Use validate to raise on duplicate keys
Use indicator to count unmatched rows
Standardize key type, case and whitespace
Assert the expected row count in code

Common mistakes

Checking the merged frame by eye with head() instead of counting matched and unmatched keys
Assuming a left join cannot add rows, when duplicated keys on the right multiply them
Merging an integer key column with a string key column that holds the same identifiers

The interviewer might ask next

How would you handle a results table that legitimately has repeated measurements per sample but should be summarized before the merge?