Skip to content
Browse

/commands/pandas/pandas-merge

pandas merge: join DataFrames on a key (inner, left, outer)

verified onenvironmentlocal · windows 10.0.26200 arm64 · GNU bash 5.3.15 (Git Bash)updated 2026-10-20
tool
pandas
versions
pandas 2.x, pandas 3.x
os
any

pd.merge(left, right, on="id", how="left") (or left.merge(right, ...)) combines two DataFrames by matching values in a key column, like a SQL JOIN. how chooses which keys survive: inner (default) keeps keys present in both, left keeps every row of the left frame, outer keeps all keys from both.

The four join types

bash
python -c '
import pandas as pd
users = pd.DataFrame({"id": [1, 2, 3], "name": ["ana", "ben", "cai"]})
orders = pd.DataFrame({"id": [1, 1, 4], "total": [9.5, 20.0, 7.0]})
for how in ["inner", "left", "right", "outer"]:
    print(f"--- how={how}")
    print(users.merge(orders, on="id", how=how).to_string(index=False))'

Output

--- how=inner
 id name  total
  1  ana    9.5
  1  ana   20.0
--- how=left
 id name  total
  1  ana    9.5
  1  ana   20.0
  2  ben    NaN
  3  cai    NaN
--- how=right
 id name  total
  1  ana    9.5
  1  ana   20.0
  4  NaN    7.0
--- how=outer
 id name  total
  1  ana    9.5
  1  ana   20.0
  2  ben    NaN
  3  cai    NaN
  4  NaN    7.0

Missing matches become NaN, which turns integer columns into floats. indicator=True adds a _merge column that tells you where each row came from.

Keys with different names, and duplicate checks

bash
python -c '
import pandas as pd
users = pd.DataFrame({"user_id": [1, 2], "name": ["ana", "ben"]})
orders = pd.DataFrame({"customer": [1, 1, 2], "total": [9.5, 20.0, 7.0]})
print(users.merge(orders, left_on="user_id", right_on="customer").to_string(index=False))
try:
    users.merge(orders, left_on="user_id", right_on="customer", validate="one_to_one")
except Exception as e:
    print(type(e).__name__, "-", e)'

Output

 user_id name  customer  total
       1  ana         1    9.5
       1  ana         1   20.0
       2  ben         2    7.0
MergeError - Merge keys are not unique in right dataset; not a one-to-one merge
Duplicates in right:
  customer
        1 ...

validate="one_to_one", "one_to_many" or "many_to_one" raises MergeError when the keys are not unique where you expect them to be, before duplicated rows reach your results.

Parameters you will use

ParameterMeaning
onkey column(s) present in both frames
left_on / right_onkey columns with different names
howinner, left, right, outer or cross
suffixesnames for overlapping columns, default ('_x', '_y')
indicatoradd a _merge column: left_only, right_only, both
validatecheck the key relationship (one_to_one, one_to_many, many_to_one)

Sources

Related pages

compare · pandaspandas loc vs iloc: label-based vs position-based selectionloc selects rows and columns by label and includes the end of a slice; iloc selects by integer position and excludes it. Cases where they return different rows.cheat sheets · pandaspandas cheat sheet: read, select, filter, group and reshapepandas cheat sheet: read CSV and Parquet, select with loc and iloc, filter rows, add columns, group and aggregate, pivot, handle missing values and write files.config · pandaspandas display options: show all columns and rows (display.max_columns)pandas truncates wide and long DataFrames when printing. Set display.max_columns and display.max_rows to None to see everything, or use option_context once.