Merging and Joining DataFrames in Pandas
SkillVeris Team
Data Science Team
pd.merge() combines two DataFrames on shared key columns using database-style joins — inner, left, right, or outer — controlled by the how parameter.
In this guide, you'll learn:
- An inner join keeps only matching keys; left keeps all left rows; right keeps all right rows; outer keeps everything and fills gaps with NaN.
- df.join() aligns on the index and is a convenient shortcut, while pd.concat() stacks frames vertically or horizontally without matching keys.
- Duplicate keys cause a many-to-many join that multiplies rows — validate cardinality with the validate argument.
- The indicator=True option adds a _merge column that shows whether each row matched left_only, right_only, or both.
1How Do You Combine DataFrames in Pandas?
You combine DataFrames in Pandas with three tools: pd.merge() for database-style joins on key columns, df.join() for index-based joins, and pd.concat() for stacking frames along an axis. merge is the workhorse and covers inner, left, right, and outer joins through its how parameter.
The key question every combine forces you to answer is which rows should survive. Inner joins keep only rows present in both frames; outer joins keep everything. Get the join type right and the rest is detail.
2The merge() Function
pd.merge() matches rows from two frames wherever the key columns share a value. If both frames have a column with the same name, Pandas uses it automatically, but being explicit with the on argument makes intent clear and avoids surprises.
- pd.merge(orders, customers, on='customer_id') # join on a shared column
- pd.merge(orders, customers, on='customer_id', how='left') # keep all orders
- pd.merge(a, b, left_on='cust', right_on='id') # differently named keys
- pd.merge(a, b, on=['year', 'region']) # multi-column key
💡Always Name Your Keys
Pass on= explicitly even when Pandas could infer it. Explicit keys document your intent and prevent accidental joins on unexpected shared column names.
3The Four Join Types
The how parameter decides which keys are retained. Picturing two overlapping circles — a Venn diagram — makes the four options intuitive.
- inner (default): keep only keys present in both frames — the overlap.
- left: keep every row from the left frame; unmatched right columns become NaN.
- right: keep every row from the right frame; unmatched left columns become NaN.
- outer: keep all keys from both frames; fill every non-matching cell with NaN.
Which One Should I Use?
Use inner when you only want records that exist on both sides, such as orders that have a valid customer. Use left when the left frame is your source of truth and you are enriching it with optional details. Outer joins are best for reconciliation — finding what exists on one side but not the other.
4join() and concat()
Beyond merge, two more tools cover the remaining cases. df.join() is a shortcut that aligns on the index by default, handy when you have already set meaningful indexes. pd.concat() stacks frames without matching keys — vertically to append rows or horizontally to append columns.
- left.join(right, how='left') # index-aligned join
- pd.concat([df_jan, df_feb, df_mar]) # stack rows (axis=0)
- pd.concat([features, labels], axis=1) # stack columns (axis=1)
- pd.concat([a, b], ignore_index=True) # renumber the combined index
🔑merge vs concat
Use merge/join when rows must line up by matching key values. Use concat when you simply want to glue frames together end-to-end or side-by-side.
5Duplicate Keys and Row Explosion
The most dangerous merge bug is silent row multiplication. When a key appears multiple times on both sides, the join produces every matching combination — a many-to-many join. A frame of 1,000 rows can suddenly balloon into tens of thousands.
Pandas can guard against this. The validate argument asserts the relationship you expect and raises an error if the data violates it, turning a silent bug into a loud one.
- pd.merge(a, b, on='id', validate='one_to_one')
- pd.merge(a, b, on='id', validate='one_to_many')
- pd.merge(a, b, on='id', validate='many_to_one')
⚠️Check Row Counts After Every Merge
If a merge unexpectedly increases your row count, you almost certainly have duplicate keys causing a many-to-many join. Deduplicate the keys or add validate= to catch it early.
6Inspecting a Merge
After a join you often need to know what matched. The indicator=True option adds a _merge column tagging each row as left_only, right_only, or both — invaluable for debugging why rows appeared or vanished.
- m = pd.merge(a, b, on='id', how='outer', indicator=True)
- m[m['_merge'] == 'left_only'] # rows only in the left frame
- m['_merge'].value_counts() # quick match summary
7Best Practices
A handful of habits keep joins predictable and your row counts honest.
- Decide the join type deliberately before writing the merge, not after seeing the output.
- Use suffixes=('_left', '_right') to disambiguate overlapping non-key column names.
- Verify row counts before and after — an unexpected change signals duplicate keys.
- Add validate= to document and enforce the expected key relationship.
- Ensure key columns share the same dtype; an int on one side and a string on the other silently matches nothing.
8Key Takeaways
Merging comes down to keys, join type, and cardinality.
- merge() joins on columns; join() joins on the index; concat() stacks frames.
- how controls survivors: inner (overlap), left, right, outer (everything).
- Duplicate keys trigger many-to-many joins that multiply rows — validate them.
- indicator=True reveals which side each row came from.
- Matching key dtypes is essential; mismatched types match nothing.
9Frequently Asked Questions
Q: What is the difference between merge and join in Pandas? A: merge is a flexible function that joins on any columns or indexes and defaults to an inner join, while join is a DataFrame method that aligns on the index by default and defaults to a left join. Under the hood join calls merge, so use whichever reads more clearly for your keys.
Q: When should I use concat instead of merge? A: Use concat when you are stacking frames that share the same structure — for example, appending monthly files into one table (axis=0) or attaching new columns side by side (axis=1). Use merge only when rows must be matched by key values.
Q: Why did my DataFrame get more rows after merging? A: Duplicate keys on both sides create a many-to-many join, producing every matching combination. Check for duplicates with df['key'].duplicated().any(), deduplicate if needed, and pass validate= to catch the problem automatically.
Q: How do I keep all rows from both DataFrames? A: Use how='outer'. This retains every key from both frames and fills non-matching cells with NaN, which is ideal for reconciling two sources and spotting records that exist on only one side.
Related Reading
Get The Print Version
Download a PDF of this article for offline reading.
About the Publisher
SkillVeris Team
Data Science Team
Our data team shares real-world analytics, ML, and SQL insights grounded in industry practice.
View all postsRelated Posts
Never miss an update
Get the latest tutorials and guides delivered to your inbox.
No spam. Unsubscribe anytime.