How do I join two DataFrames in pandas?

The short version

Use merge, tell it the key column with on, and choose the join type with how. The default is an inner join, which keeps only rows that match on both sides, so it can silently drop data you expected to keep. If you want every row from the left frame, ask for how="left".

A worked example

import pandas as pd

orders = pd.DataFrame({"order": [1, 2, 3], "cust_id": [10, 10, 20]})
custs = pd.DataFrame({"cust_id": [10, 20], "name": ["Ana", "Bo"]})

joined = orders.merge(custs, on="cust_id", how="left")
print(joined)

   order  cust_id name
0      1       10  Ana
1      2       10  Ana
2      3       20   Bo

Choosing the join type

The how argument decides which rows survive when a key exists on one side only. inner keeps matches on both sides. left keeps all rows from the left frame and fills missing right-hand columns with NaN. right mirrors that. outer keeps everything from both. Picking the wrong one is the most common merge bug, so decide deliberately which frame must keep all its rows.

When the key columns are named differently

If the two frames call the key by different names, use left_on and right_on.

orders.merge(custs, left_on="cust_id", right_on="cust_id")

The duplicate-key trap

Merging multiplies rows when a key repeats on both sides. Two matching rows on the left and three on the right produce six rows for that key. If your result has more rows than you started with, check for duplicated keys before assuming the data is wrong.

custs["cust_id"].duplicated().any()   # True means duplicates exist

merge versus join

DataFrame.join is a convenience wrapper that merges on the index by default. merge is more explicit and handles column keys directly, so prefer merge unless you are specifically joining on indexes. Whichever you use, always state how rather than relying on the default, so a reader can see your intent.