Join on Index
Python · Combining DataFrames (SQL Joins & Unions)
Theory
Sometimes the key is the row index rather than a column - for example after set_index('cust_id') or after a groupby. left.join(right) is a shortcut that matches left's index (or a column given with on=) against right's index, with how='left' by default. If both tables share column names, join requires lsuffix= / rsuffix=. You can join several frames at once: a.join([b, c]).
merge can do the same with left_index=True and right_index=True, or mix them: left.merge(right, left_on='cust_id', right_index=True) matches a column on one side to the index on the other. A typical use is attaching a groupby summary back to a lookup table: totals = orders.groupby('cust_id')['amount'].sum() has cust_id as its index, so customers.set_index('cust_id').join(totals) adds each customer's total, with NaN for customers who never ordered.
Why we use it
Indexes are pandas' built-in keys. Results of groupby, pivot_table and set_index are already indexed, so joining on the index avoids resetting and renaming columns and keeps code short.
When to use it
Use join when the right table's key is its index, especially for adding aggregated Series or lookup tables. Use merge when keys are ordinary columns or you need validate/indicator; reset_index() first if you prefer columns.
How it works
- customers.join(totals)
- Read the index labels of both tables
- Left label in the right index? Yes: Attach the right values. No: Right columns become NaN.
- Keep left order (how='left')
- Left table with extra columns from the right
Syntax
left.join(right, how='left', lsuffix='', rsuffix='')
left.join(right, on='key_col')
left.merge(right, left_index=True, right_index=True)
left.merge(right, left_on='key', right_index=True)Example
totals = orders.groupby('cust_id')['amount'].sum().rename('total')
print(customers.set_index('cust_id').join(totals))Key points
- join matches on the index of the right table; default how='left'
- Use on='col' to match a left column against the right index
- Overlapping column names need lsuffix= / rsuffix=
- merge(left_index=True, right_index=True) is the merge form
- SQL equivalent: LEFT JOIN (SELECT cust_id, SUM(amount) ... GROUP BY cust_id) t USING (cust_id)