What is the difference between merge, join and concat in pandas?

Think of two registers kept by a college office. One has roll numbers and names. The other has roll numbers and maths marks.

• merge is matching the two registers by roll number, so each student's name sits next to their marks.
• join is the same matching, but it uses the row labels (the index) instead of a column.
• concat is stapling the Section B register under the Section A register. Nothing is matched; the pages are just put together.

import pandas as pd

students = pd.DataFrame({"roll": [1, 2, 3], "name": ["Asha", "Ravi", "Meena"]})
marks = pd.DataFrame({"roll": [1, 2, 4], "maths": [88, 72, 95]})

# merge: match on a column (inner join by default)
print(pd.merge(students, marks, on="roll"))
#    roll  name  maths
# 0     1  Asha     88
# 1     2  Ravi     72

# merge with how="left": keep every student
print(pd.merge(students, marks, on="roll", how="left"))
#    roll   name  maths
# 0     1   Asha   88.0
# 1     2   Ravi   72.0
# 2     3  Meena    NaN

# join: match on the index (left join by default)
print(students.set_index("roll").join(marks.set_index("roll")))
#        name  maths
# roll
# 1      Asha   88.0
# 2      Ravi   72.0
# 3     Meena    NaN

# concat: stack rows, no matching
sec_b = pd.DataFrame({"roll": [4, 5], "name": ["Imran", "Divya"]})
print(pd.concat([students, sec_b], ignore_index=True))
#    roll   name
# 0     1   Asha
# 1     2   Ravi
# 2     3  Meena
# 3     4  Imran
# 4     5  Divya

Notice two details. Roll 4 has marks but no name, so the inner merge drops it. And maths turns into floats (88.0) in the left join, because pandas needs NaN for Meena and NaN is a float.

pd.merge(left, right, on=..., how=...)

• Matches rows where key columns are equal.
• how can be "inner" (default), "left", "right", "outer" or "cross", just like SQL.
• Use left_on and right_on when the key columns have different names.

left.join(right, how=...)

• A shortcut for merging on the index. Default is a left join.
• Handy when your data is already indexed by an ID or a date.

pd.concat([df1, df2], axis=0 or 1)

• axis=0 (default) stacks rows. Columns are aligned by name; a missing column becomes NaN.
• axis=1 places frames side by side, aligned by index.
• ignore_index=True gives a fresh 0, 1, 2... index instead of repeating the old ones.

• merge: joining an orders table with a customers table on customer_id.
• join: combining several daily price series that are all indexed by date.
• concat: reading 12 monthly CSV files and stacking them into one year of data.

• Using concat when you meant to match on a key. Rows will line up by position or index, not by ID, and the result will be silently wrong.
• Merging on a key that has duplicates on both sides. Every match pairs up, so rows multiply. Use validate="one_to_one" or "many_to_one" to catch this.
• Forgetting the default is an inner merge and losing rows without noticing. Compare row counts before and after.
• Overlapping column names get suffixes _x and _y. Set suffixes= to readable names.

• What happens when the merge key has duplicate values in both DataFrames?
• How would you find rows that exist in one DataFrame but not the other? (Hint: indicator=True.)
• What is the difference between concat with axis=0 and axis=1?
• Why did the integer column become float after a left merge?