Merging and Joining Tables

What you'll build

An enriched orders table joined with a customers table, a check that the relationship is many-to-one as expected, and a list of orders whose customer is missing from the customer table: the kind of data-quality check that catches broken exports.

The idea

• merge(left, right, on=..., how=...) works like SQL joins: inner, left, right, outer.
• validate="many_to_one" raises an error if the right table has duplicate keys, which would silently multiply rows.
• indicator=True adds a _merge column showing where each row came from: great for finding unmatched keys.
• pd.concat([...]) stacks tables with the same columns (for example monthly files).

The code

from io import StringIO
import pandas as pd

csv = StringIO("""order_id,date,customer,country,product,quantity,unit_price
1001,2026-01-03,ana,DE,mug,2,8.50
1002,2026-01-03,ben,US,teapot,1,24.00
1003,2026-01-04,chen,DE,kettle,1,31.90
1004,2026-01-05,ana,DE,teapot,1,24.00
1005,2026-01-07,dara,FR,mug,4,8.50
1006,2026-01-07,ben,US,mug,1,8.50
""")
orders = pd.read_csv(csv, parse_dates=["date"])

customers = pd.DataFrame({
    "customer": ["ana", "ben", "chen"],
    "segment": ["vip", "regular", "regular"],
})
joined = orders.merge(customers, on="customer", how="left", validate="many_to_one", indicator=True)
print(joined[["order_id", "customer", "segment", "_merge"]])
print("orders without a customer record:", joined.loc[joined["_merge"] == "left_only", "customer"].unique().tolist())

dup_customers = pd.concat([customers, pd.DataFrame({"customer": ["ana"], "segment": ["new"]})])
try:
    orders.merge(dup_customers, on="customer", validate="many_to_one")
except pd.errors.MergeError as e:
    print("MergeError:", str(e).splitlines()[0])

   order_id customer  segment     _merge
0      1001      ana      vip       both
1      1002      ben  regular       both
2      1003     chen  regular       both
3      1004      ana      vip       both
4      1005     dara      NaN  left_only
5      1006      ben  regular       both
orders without a customer record: ['dara']
MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Common mistakes

• Joining on keys with duplicates on both sides, multiplying rows (revenue suddenly doubles); always validate.
• Inner joins dropping rows silently when you wanted to keep all orders; use how="left" and inspect unmatched rows.
• Keys with different types ("42" versus 42) or spacing never matching.

Key points

• merge works like SQL joins; choose how deliberately.
• validate catches unexpected duplicate keys.
• indicator=True finds unmatched rows.

Further reading: Merge, join, concatenate and compare