Enrich records
Attach descriptive attributes such as names or categories to fact rows using shared identifiers.
Pandas · Module
df.merge
Combines a DataFrame with another DataFrame or named Series using a database-style join. It matches column and/or index keys and supports inner, outer, left, right, cross, and anti-join strategies.
How it works
Call merge on the left DataFrame and provide the right DataFrame. Choose key columns or indexes, select the join mode, optionally control ordering, overlapping-column suffixes, provenance, and key-cardinality validation, then receive a new merged DataFrame.
Syntax
DataFrame.merge(right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, sort=False, suffixes=('_x', '_y'), copy=<no_default>, indicator=False, validate=None)Parameters
| Name | Type | Description |
|---|---|---|
right | DataFrame or named Series | DataFrame or named Series to merge with the calling DataFrame. |
how | str | Join strategy: left, right, outer, inner, cross, left_anti, or right_anti; defaults to inner. |
on | label or list | Column label or sequence of labels shared by both inputs; defaults to inferred common columns. |
left_on | label or list | Column or index-like keys from the left input. |
right_on | label or list | Column or index-like keys from the right input. |
left_index | bool | Use the left index as the join key. |
right_index | bool | Use the right index as the join key. |
sort | bool | Sort the resulting join keys lexicographically. |
suffixes | list-like | Two suffixes applied to overlapping non-key column names; at least one must not be None. |
copy | bool or None | Whether to copy data; the default is controlled by the current pandas behavior. |
indicator | bool or str | Add a source-indicator column to the result. |
validate | str | Check expected key uniqueness, such as one_to_one, one_to_many, many_to_one, or many_to_many. |
Returns
A DataFrame containing columns from both inputs according to the selected join strategy.
Key points
- The method combines a left DataFrame with a DataFrame or named Series using relational-style matching.
- Keys can be selected from columns, indexes, or both sides through the corresponding key arguments.
- A left join retains every key from the calling DataFrame and keeps left-key order.
- An outer join includes keys found in either input and sorts the key values lexicographically.
- A cross join pairs every left row with every right row and retains left-row ordering.
- A named Series can be supplied as the right operand and is handled as a one-column table.
Common mistakes
- When matching columns, neither DataFrame's index participates in the join; use left_index or right_index when index keys are intended.
- Null keys match other null keys in this operation, unlike the usual SQL behavior; clean or filter missing keys when that is undesirable.
- suffixes cannot both be None when overlapping non-key columns need disambiguation.
Example: Validated left join
import pandas as pd
orders = pd.DataFrame({"customer_id": [1, 2], "total": [10, 25]})
customers = pd.DataFrame({"customer_id": [1, 2], "name": ["Ada", "Linus"]})
result = orders.merge(
customers,
on="customer_id",
how="left",
validate="many_to_one",
)
print(result)Joins customer names onto orders by customer_id and checks that each customer key appears at most once on the right.
When to use it
Preserve a primary dataset
Keep unmatched rows from one input while adding available fields from another.
Index-based joins
Combine tables whose identifiers are stored in indexes rather than ordinary columns.
Audit data relationships
Assert relationship cardinality and inspect whether output rows came from one or both inputs.
Interview questions
QWhat does DataFrame.merge do, and what is its default join behavior?
It performs a database-style join between the left DataFrame and a right DataFrame or named Series. Keys may come from columns, indexes, or a combination of columns and indexes; the default is an inner join.
Follow-up: How would you preserve every left row while matching right-side data? Use how='left', and specify on, left_on/right_on, or the relevant index flags.
QHow do the main how values differ?
The how argument controls key inclusion: inner keeps the intersection, left keeps left keys, right keeps right keys, outer uses the union, and cross forms a Cartesian product. The documented anti-join modes keep keys absent from the opposite frame.
Follow-up: How can you preserve left-key order for an anti-join? Use how='left_anti'; it keeps left keys not found on the right and preserves their order.
QHow can merge detect unexpected duplicate keys and show row provenance?
validate checks whether merge keys have the expected uniqueness pattern, such as one-to-one, one-to-many, many-to-one, or many-to-many. indicator adds a column describing whether each result row came from the left, right, or both inputs.
Follow-up: Which validation is appropriate when many orders may belong to one customer, but each customer should occur once in the customer table? Use validate='many_to_one'.
Related
pandas.mergepandas.DataFrame.joinpandas.DataFrame.concat