The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Keep matching keys | result = orders.merge(customers, on='customer_id', how='inner') | View examples |
| Preserve left rows | result = orders.merge(customers, on='customer_id', how='left') | View examples |
| Join different key names | result = orders.merge(customers, left_on='customer_id', right_on='id', how='left') | View examples |
| Validate key cardinality | result = orders.merge(customers, on='customer_id', validate='many_to_one') | View examples |
| Track row provenance | audit = left.merge(right, on='id', how='outer', indicator=True) | View examples |
| Label overlapping columns | result = current.merge(previous, on='id', suffixes=('_current', '_previous')) | View examples |
| Stack row batches | combined = pd.concat([january, february], ignore_index=True) | View examples |
| Require identical columns | combined = pd.concat(frames, join='inner', ignore_index=True) | View examples |
| Align columns by index | combined = pd.concat([features, targets], axis='columns') | View examples |
| Preserve batch identity | combined = pd.concat({'jan': january, 'feb': february}, names=['month']) | View examples |
| Join on indexes | result = customers.join(accounts, how='left', validate='one_to_one') | View examples |
| Fill from a fallback | combined = primary.combine_first(fallback) | View examples |
| Match the nearest prior key | result = pd.merge_asof(events, rates, on='time', direction='backward') | View examples |
| Show changed values | changes = before.compare(after, result_names=('before', 'after')) | View examples |
Combining tables is safest when key cardinality is declared and checked. Use merge for relational keys, join for index-oriented alignment, concat for stacking along an axis, and diagnostic indicators or comparisons to expose records that did not combine as expected.
Step by step
Detailed examples
Choose join keys and preservation rules
inner merge keeps matches; left merge preserves the left table and represents unmatched right values as missing. Name keys explicitly rather than relying on every shared column. Repeated keys on both sides create a Cartesian product within each key, and pandas matches null keys to one another unlike typical SQL joins, so validate or remove missing keys when that behavior is not intended.
import pandas as pd
orders = pd.DataFrame({'order_id': [1, 2, 3], 'customer_id': [10, 20, 30]})
customers = pd.DataFrame({'id': [10, 20], 'name': ['Ada', 'Lin']})
inner = orders.merge(customers, left_on='customer_id', right_on='id', how='inner')
left = orders.merge(customers, left_on='customer_id', right_on='id', how='left')
print(inner[['order_id', 'name']].to_dict('records'))
print(left[['order_id', 'name']].to_dict('records'))[{'order_id': 1, 'name': 'Ada'}, {'order_id': 2, 'name': 'Lin'}]
[{'order_id': 1, 'name': 'Ada'}, {'order_id': 2, 'name': 'Lin'}, {'order_id': 3, 'name': nan}]Assert cardinality and inspect unmatched keys
validate turns an assumed key relationship into an executable check before the merge result is accepted. Use many_to_one for fact-to-dimension enrichment, one_to_one for unique records, and the other declared relationships only when duplicates are intended. An outer merge with indicator exposes left-only and right-only keys, while explicit suffixes keep overlapping measurements understandable.
import pandas as pd
orders = pd.DataFrame({'customer_id': [10, 10, 20], 'status': ['new', 'paid', 'new']})
customers = pd.DataFrame({'customer_id': [10, 20, 30], 'status': ['active', 'active', 'paused']})
enriched = orders.merge(
customers, on='customer_id', how='left', validate='many_to_one', suffixes=('_order', '_customer'),
)
audit = orders[['customer_id']].drop_duplicates().merge(
customers[['customer_id']], on='customer_id', how='outer', indicator=True, validate='one_to_one',
)
print(enriched.columns.tolist())
print(audit.set_index('customer_id')['_merge'].astype(str).to_dict())['customer_id', 'status_order', 'status_customer']
{10: 'both', 20: 'both', 30: 'right_only'}Concatenate compatible batches or aligned features
concat stacks objects without relational key matching. Along rows, the default outer column union can introduce missing values when schemas differ; use join='inner' only when dropping non-shared columns is intentional. Along columns, pandas aligns index labels rather than row positions. Mapping keys create a source level that retains batch provenance.
import pandas as pd
january = pd.DataFrame({'id': [1, 2], 'amount': [10, 20]})
february = pd.DataFrame({'id': [3], 'amount': [30], 'note': ['late']})
rows = pd.concat([january, february], ignore_index=True)
strict_rows = pd.concat([january, february], join='inner', ignore_index=True)
keyed = pd.concat({'jan': january, 'feb': february}, names=['month'])
features = pd.DataFrame({'score': [0.8, 0.6]}, index=[101, 102])
targets = pd.DataFrame({'approved': [True, False]}, index=[102, 103])
columns = pd.concat([features, targets], axis='columns')
print(rows.columns.tolist())
print(strict_rows.to_dict('records'))
print(keyed.index.names)
print(columns.index.tolist())['id', 'amount', 'note']
[{'id': 1, 'amount': 10}, {'id': 2, 'amount': 20}, {'id': 3, 'amount': 30}]
['month', None]
[101, 102, 103]Use index alignment for labeled observations
DataFrame.join is convenient when row indexes are already meaningful keys and can validate their relationship. combine_first takes non-missing values from the caller and fills remaining locations from another aligned object, using the union of labels. It is a coalescing operation, not a conflict detector; compare present values separately when disagreements matter.
import pandas as pd
customers = pd.DataFrame({'name': ['Ada', 'Lin']}, index=pd.Index([10, 20], name='customer_id'))
accounts = pd.DataFrame({'tier': ['pro', 'basic']}, index=pd.Index([10, 20], name='customer_id'))
joined = customers.join(accounts, how='left', validate='one_to_one')
primary = pd.Series({10: 'ada@example.com', 20: None}, name='email')
fallback = pd.Series({20: 'lin@example.com', 30: 'sam@example.com'}, name='email')
combined = primary.combine_first(fallback)
print(joined.to_dict('index'))
print(combined.to_dict()){10: {'name': 'Ada', 'tier': 'pro'}, 20: {'name': 'Lin', 'tier': 'basic'}}
{10: 'ada@example.com', 20: 'lin@example.com', 30: 'sam@example.com'}Match ordered observations and compare revisions
merge_asof performs nearest-key matching for sorted numeric or datetime keys; backward matching is useful for attaching the latest state known at an event time. Add by for independent entities and tolerance to reject stale matches. DataFrame.compare requires identical labels and shows value differences, making it suitable after both versions have been deliberately aligned.
import pandas as pd
events = pd.DataFrame({
'time': pd.to_datetime(['2026-08-01 09:05', '2026-08-01 09:40']),
'amount': [100, 120],
}).sort_values('time')
rates = pd.DataFrame({
'time': pd.to_datetime(['2026-08-01 09:00', '2026-08-01 09:30']),
'rate': [1.1, 1.2],
}).sort_values('time')
matched = pd.merge_asof(events, rates, on='time', direction='backward')
before = pd.DataFrame({'status': ['open', 'paid'], 'amount': [100, 120]}, index=[1, 2])
after = pd.DataFrame({'status': ['paid', 'paid'], 'amount': [100, 125]}, index=[1, 2])
changes = before.compare(after, result_names=('before', 'after'))
print(matched['rate'].tolist())
print(changes.columns.tolist())[1.1, 1.2]
[('status', 'before'), ('status', 'after'), ('amount', 'before'), ('amount', 'after')]Local code tester
Validate and audit a table merge
Change the customer keys and inspect matched, left-only, and right-only records.
Press Run to load Python locally.
Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.