Merge & Join

pd.merge()

Think of merge like a SQL JOIN. You combine two tables based on matching values.

pd.merge(left_df, right_df, on="common_column")

join types

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

different column names

When the join columns have different names:

pd.merge(orders, customers,
         left_on="cust_id",
         right_on="customer_id")

duplicate column names

When both DataFrames have columns with the same name (that aren't join keys):

pd.merge(df1, df2, on="id", suffixes=("_left", "_right"))
# Columns become: value_left, value_right

patterns

# Add customer details to orders
orders_with_details = pd.merge(orders, customers, on="customer_id")

# Find orders without customers (left join, then filter NaN)
all_orders = pd.merge(orders, customers, on="customer_id", how="left")
orphan_orders = all_orders[all_orders["customer_name"].isna()]

# Find all combinations (cross join)
pd.merge(df1, df2, how="cross")

See also

The SQL equivalent is INNER JOIN ... ON (and LEFT JOIN for how="left"). The matching mental model with table-flavored examples is on damato-sql at /learn/joining-tables/inner-join.