Real data arrives in separate tables, and most questions need columns from more than one of them.
The merge function brings two tables together by matching a key.
The on option names the key when it has the same name in both tables, and left_on and right_on handle different names.
The how option decides which unmatched rows survive, exactly like the join types in SQL.
An inner merge is the default, so unmatched rows from both sides disappear unless you ask otherwise.
One to one, one to many and many to many describe how often each key appears on each side.
The validate option checks this, raising an error if a merge expected to be one to one finds duplicates.
Setting indicator=True adds a column saying whether each row came from both tables or only one.
Comparing row counts before and after a merge is the fastest check that nothing went wrong.
Keys must match exactly, so trailing spaces, different capitals or numbers stored as text all break matches.
Columns that share a name in both tables get endings such as _x and _y unless one is renamed first.
The concat function is different: it stacks tables on top of each other or side by side without matching keys.
The join method is a shortcut for merging on the index rather than on a column.
In practice most analysis starts by merging a table of events, such as orders, with tables that describe them.
The usual mistake is a silent growth in rows from duplicate keys, which inflates every total that follows.