Pandas Merge vs Join vs Concat: Combining Datasets Right

6 min read · Last updated: October 25, 2026

Combining datasets in pandas means choosing between concat, merge and join. Use concat to stack frames, merge for SQL-style joins on key columns, and join to combine by index. Before trusting any result, check that your keys are unique with validate and see unmatched rows with indicator=True.

Came here from Instagram, LinkedIn, X or YouTube? Here is the full source list you came for. Everything below is built from these, nothing else.

The post that started this

• From Separate Datasets to Real Questions: My Week 7 Journey with Pandas, Medium, October 2026

The docs

• pandas user guide: Merge, join, concatenate and compare, read October 25, 2026

Key takeaways

• The Week 7 post's point: writing code is only one part of solving a data problem, and understanding the data matters just as much.
• pandas has three ways to combine data: concat, merge and join.
• merge supports five how options: left, right, outer, inner and cross.
• validate="one_to_one" raises a MergeError before a bad merge runs.
• indicator=True adds a _merge column that labels every row left_only, right_only or both.

What did the Week 7 pandas post say?

A Medium author writing as @innocentekwueme79 published "From Separate Datasets to Real Questions: My Week 7 Journey with Pandas". The post's stated lesson is that writing code is only one part of solving a problem, and that understanding the data is just as important.

The title names the step where this bites: you stop working with one clean table and start combining several to answer a real question. I could not open the full post, so I am not quoting it. I am using its lesson as the frame and the pandas documentation for every technical claim.

Why does this matter to a developer?

Combining datasets is where code that runs without errors can still be wrong. A join on a key that repeats does not crash. It multiplies rows. The code is fine, and the data is not what you assumed.

The pandas documentation says it directly. When a key appears multiple times on both sides, a many-to-many join produces the Cartesian product of the matching rows. Ten matching rows on the left and ten on the right gives you a hundred.

That is the Week 7 lesson in one example. You can write pd.merge correctly and still answer the wrong question, because you never checked what a row in each table means.

What is the difference between concat, merge and join?

concat stacks pandas objects along an axis. merge performs database-style joins on columns or indexes. join combines columns by index and defaults to a left join on the calling frame's index. Pick by asking one question: am I adding rows, or matching rows?

Function	What it does	Matches on	Use it when
pd.concat()	Stacks frames along an axis	Index alignment	Same columns, new rows (for example one file per month)
pd.merge()	SQL-style joins	Columns (on, left_on, right_on) or indexes	Two tables share a key such as user_id
DataFrame.join()	Combines columns	The index	Both frames are already indexed by the key

One practical note from the docs: concat makes a full copy. If you are combining many frames, collect them in a list and call concat once.

What do the merge how options actually do?

The how argument controls which keys survive. Left keeps keys from the left frame only, right keeps the right only, inner keeps the intersection, outer keeps the union, and cross returns every row pairing. Keys missing on one side get NA in the other side's columns.

how	SQL equivalent	Keeps
"left"	LEFT OUTER JOIN	Keys from the left frame only
"right"	RIGHT OUTER JOIN	Keys from the right frame only
"outer"	FULL OUTER JOIN	Union of keys from both frames
"inner"	INNER JOIN	Intersection of keys from both frames
"cross"	CROSS JOIN	Cartesian product of rows of both frames

The choice is a question about your data, not about syntax. "Which customers have no orders?" needs a left join. "Which customers have orders?" needs an inner join. Same code shape, different answer.

How do you check a merge before you trust it?

Check a merge in four steps: confirm what one row means in each table, state the expected key relationship with validate, run the merge with indicator=True, then count the _merge values. If the counts surprise you, the data is not what you assumed.

1. Write down what one row is. One row per customer? Per order? Per day?
2. Declare the relationship. validate="one_to_one" raises a MergeError if the right keys are not unique. The docs describe this check as guarding against unexpected duplication and memory blow-ups.
3. Add indicator=True. pandas adds a categorical _merge column.
4. Count the result. Run value_counts() on _merge and compare it with what you expected.

Here is the example from the pandas docs:

import pandas as pd

left = pd.DataFrame({"col1": [0, 1], "col_left": ["a", "b"]})
right = pd.DataFrame({"col1": [1, 2, 2], "col_right": [2, 2, 2]})

result = pd.merge(left, right, on="col1", how="outer", indicator=True)
print(result)
#    col1 col_left  col_right      _merge
# 0     0        a        NaN   left_only
# 1     1        b        2.0        both
# 2     2      NaN        2.0  right_only
# 3     2      NaN        2.0  right_only

try:
    pd.merge(left, right, on="col1", how="outer", validate="one_to_one")
except pd.errors.MergeError as e:
    print(e)
# Merge keys are not unique in right dataset; not a one-to-one merge

The key 2 appears twice in right. Without validate, you would never see that. With it, the error names the problem.

What changes in practice?

Treat every merge as a claim about your data, and make the claim explicit. Three habits change:

• Row counts become a test. Print the shape before and after. An unexpected jump means duplicate keys.
• how is chosen from the question. Decide what you want to keep, then pick left, inner or outer.
• validate goes in by default. If you expect one row per key, say so in code. The merge fails loudly instead of quietly returning a bigger table.

This matches a point I made in code was never the hard part: syntax is the easy bit, and knowing what you are looking at is the work. The same applies to reading AI-written pandas code. Our guide on how to review AI-generated code like a pro applies here, because a generated merge with no validate runs fine and can still be wrong.

What should you do this week?

Pick one analysis you have already done and re-check it. Take any merge in it and add indicator=True and validate. Look at the _merge counts. If you find rows you did not expect, you have found your first real data question.

If you are earlier in the data journey, our posts on supervised machine learning and unsupervised machine learning show where clean, combined data ends up.

FAQ

What is the difference between merge and join in pandas?

merge joins on columns or indexes and supports left, right, outer, inner and cross. DataFrame.join() combines by index and defaults to a left join on the calling frame's index. Both produce joined tables. join is the shorter form when the key is already the index.

How do I stop a pandas merge from duplicating rows?

Pass validate="one_to_one" or another relationship such as "one_to_many". pandas checks key uniqueness before merging and raises a MergeError if your assumption is wrong. Also compare row counts before and after the merge.

How do I find rows that did not match in a merge?

Set indicator=True. pandas adds a _merge column with the values left_only, right_only or both. Filter on left_only or right_only to see the unmatched rows from each side.

When should I use concat instead of merge?

Use concat when the frames have the same columns and you are adding rows, such as monthly exports. Use merge when the frames hold different information about the same entities and share a key.

Try it on your own data this week, and use the free tools on Art of Code for the quick checks. Then read code was never the hard part.

Written by Adithya Guttha, Founder of Art of Code.