Why do I get columns ending in _x and _y after a merge in pandas?

The short version

You get _x and _y when both frames contain a column with the same name that is not part of the join key. Rather than lose one, merge keeps both and tags them with default suffixes. Control the names with the suffixes argument, or decide which column you actually want and drop or rename accordingly.

Seeing it happen

import pandas as pd

a = pd.DataFrame({"id": [1], "score": [10]})
b = pd.DataFrame({"id": [1], "score": [99]})

print(a.merge(b, on="id").columns.tolist())
# ['id', 'score_x', 'score_y']

Both frames have a score column. id is the join key so it appears once, but score exists on both sides and is not a key, so merge keeps score_x (from the left) and score_y (from the right).

Giving them meaningful names

The default suffixes are rarely what you want in the output. Set your own with suffixes, which takes a pair for the left and right frames.

a.merge(b, on="id", suffixes=("_old", "_new")).columns.tolist()
# ['id', 'score_old', 'score_new']

Now the columns say what they are, which is far clearer than _x and _y downstream.

The usual real cause

Often the duplicated column is one you meant to join on but forgot to include in on. If two frames share several key columns, list all of them so they collapse into single columns rather than doubling up.

a.merge(b, on=["id", "date"])   # join on both, no duplicate date columns

If a _x/_y pair appears on a column you expected to match, check whether it should have been part of the join key.

Choosing which to keep

When one side's version is the one you want, keep it and drop the other after merging.

merged = a.merge(b, on="id", suffixes=("", "_drop"))
merged = merged.drop(columns=[c for c in merged.columns if c.endswith("_drop")])

Using an empty left suffix keeps the left column under its original name, and the right one is marked for dropping.

Spotting unexpected key mismatches

If you did not expect duplicate columns at all, the suffixes are a useful warning that the two frames share more column names than you realised. Inspect both sets of columns before merging, so you disambiguate deliberately rather than discovering _x and _y after the fact.