Match two lists by an identifier with a left merge in pandas. Keep every row from the main list, attach the lookup information where a key matches, and flag rows with no match. A validation rule can prevent duplicate lookup keys from unexpectedly multiplying rows.

The self-contained example below was executed with Python 3.11.3 and pandas 2.3.3. The data is hypothetical and included directly in the code; no dataset download is needed. Run it in a Python environment where pandas is installed.

Four order rows are matched to catalog descriptions; three rows match and ID 0019 stays visible as unmatched.
A left lookup retains four hypothetical order lines, including unmatched ID 0019.Credit: MathBlog

Define the main list and the lookup

Our main list has four order lines. The catalog has one product description per ID. ID 0017 appears twice in orders because two order lines can refer to the same product. It must appear only once in the catalog for this many-to-one lookup.

Order lines to retain
ID Units
0017 2
0018 1
0019 4
0017 3
Catalog lookup
ID Product
0017 Pen
0018 Notebook
0021 Eraser

Run the complete example

from io import StringIO
import pandas as pd

orders = pd.read_csv(StringIO("""id,units
0017,2
0018,1
0019,4
0017,3
"""), dtype={"id": "string", "units": "int64"})
catalog = pd.read_csv(StringIO("""id,product
0017,Pen
0018,Notebook
0021,Eraser
"""), dtype={"id": "string", "product": "string"})

def checked_ids(frame):
    result = frame.copy()
    result["id"] = result["id"].astype("string").str.strip()
    if result["id"].isna().any() or result["id"].eq("").any():
        raise ValueError("Every row needs a nonblank ID.")
    return result

orders = checked_ids(orders)
catalog = checked_ids(catalog)
matched = orders.merge(
    catalog, on="id", how="left", sort=False,
    validate="many_to_one", indicator=True,
)
unmatched = matched.loc[matched["_merge"].eq("left_only"), ["id", "units"]]
print(matched.to_string(index=False))
print("Unmatched orders:")
print(unmatched.to_string(index=False))

Check the result before using it

The result contains four rows. IDs remain 0017, 0018, 0019, and 0017, in the main list’s order. The first and last rows receive Pen, and 0018 receives Notebook. ID 0019 has no catalog entry.

The _merge column labels three rows both and one row left_only. The separate unmatched table contains ID 0019 with four units. Catalog-only ID 0021 does not appear because the main list defines the rows to retain.

A left merge is different from an inner merge, which would discard the unmatched order. It is also different from stacking files: MathBlog’s CSV combination tutorial adds rows rather than matching keys.

Protect the key column

The explicit string type preserves leading zeros. If an ID has already been read as the integer 17, converting it to text produces “17,” not the original “0017.” Read identifiers as text at import when their exact form matters.

The helper strips surrounding spaces and rejects missing or blank IDs. pandas can match null merge keys to other null keys, which may be surprising to readers familiar with SQL. Rejecting these keys makes that behavior irrelevant to this lookup.

Do not automatically remove internal spaces or change case unless the ID specification allows it. Cleaning can merge two genuinely different identifiers.

Make the relationship explicit

validate="many_to_one" allows repeated main-list IDs but requires unique catalog IDs. If the catalog has two entries for 0017, pandas raises a merge error instead of quietly expanding each matching order line.

Review such conflicts rather than taking an arbitrary first lookup row. For a one-row-per-ID main list as well, use validate="one_to_one". Choose the relationship from the data’s meaning, then reconcile row counts and unmatched keys.

Further reading