DataFrame Merging: `merge` vs. `concat`

After this topic, you will be able to:

Clearly distinguish between merge and concat.
Understand inner, left, right, and outer joins.
Choose the correct merging method for practical scenarios.

When is merging necessary?

In practice, data is often distributed across multiple tables.

text

users table:           orders table:
┌────┬──────┐           ┌─────────┬────────┬────────┐
│ id │ name │           │ order_id│ user_id│ amount │
├────┼──────┤           ├─────────┼────────┼────────┤
│  1 │ Alice│           │    101  │    1   │   5000 │
│  2 │ Bob  │           │    102  │    2   │   3000 │
│  3 │ Carol│           │    103  │    1   │   7000 │
└────┴──────┘           └─────────┴────────┴────────┘

To calculate "total order amount per user," you need to combine these two tables. This is merging.

`merge` – Combining based on keys

merge() is equivalent to SQL's JOIN. It combines two DataFrames based on a common column (key).

python

import pandas as pd

users = pd.DataFrame({
    "user_id": [1, 2, 3],
    "name": ["Alice", "Bob", "Carol"]
})

orders = pd.DataFrame({
    "order_id": [101, 102, 103],
    "user_id": [1, 2, 1],
    "amount": [5000, 3000, 7000]
})

result = pd.merge(users, orders, on="user_id")
print(result)
#    user_id   name  order_id  amount
# 0        1  Alice       101    5000
# 1        1  Alice       103    7000
# 2        2    Bob       102    3000

Alice has two orders, so she appears in two rows. Carol does not have any orders, so she is not in the result – this is the default behavior of an inner join.

Types of Joins

python

users = pd.DataFrame({
    "user_id": [1, 2, 3],
    "name": ["Alice", "Bob", "Carol"]
})

orders = pd.DataFrame({
    "order_id": [101, 102, 104],
    "user_id": [1, 2, 4],
    "amount": [5000, 3000, 8000]
})
# user_id=3 (Carol) has no orders, user_id=4 is not in the users table.

Inner Join (default)

Only keys present in both sides.

python

pd.merge(users, orders, on="user_id", how="inner")
#    user_id   name  order_id  amount
# 0        1  Alice       101    5000
# 1        2    Bob       102    3000

Left Join

Keeps all rows from the left DataFrame. If a key is not in the right DataFrame, it fills the right DataFrame's columns with NaN.

python

pd.merge(users, orders, on="user_id", how="left")
#    user_id   name  order_id  amount
# 0        1  Alice     101.0  5000.0
# 1        2    Bob     102.0  3000.0
# 2        3  Carol       NaN     NaN

Carol has no orders, but she is still included in the result.

Right Join

Keeps all rows from the right DataFrame.

python

pd.merge(users, orders, on="user_id", how="right")
#    user_id   name  order_id  amount
# 0        1  Alice       101    5000
# 1        2    Bob       102    3000
# 2        4    NaN       104    8000

user_id=4 is not in the users table, but it is still included in the result.

Outer Join

Keeps all rows from both DataFrames.

python

pd.merge(users, orders, on="user_id", how="outer")
#    user_id   name  order_id  amount
# 0        1  Alice     101.0  5000.0
# 1        2    Bob     102.0  3000.0
# 2        3  Carol       NaN     NaN
# 3        4    NaN     104.0  8000.0

When key column names are different

python

users = pd.DataFrame({"id": [1, 2], "name": ["Alice", "Bob"]})
orders = pd.DataFrame({"customer_id": [1, 2], "amount": [5000, 3000]})

# Use left_on / right_on
pd.merge(users, orders, left_on="id", right_on="customer_id")
#    id   name  customer_id  amount
# 0   1  Alice            1    5000
# 1   2    Bob            2    3000

`concat` – Simply stacking

concat() combines DataFrames vertically or horizontally without key matching.

Vertical (axis=0, default)

python

jan = pd.DataFrame({"product": ["A", "B"], "sales": [100, 200]})
feb = pd.DataFrame({"product": ["A", "B"], "sales": [150, 250]})

quarterly = pd.concat([jan, feb], ignore_index=True)
#   product  sales
# 0       A    100
# 1       B    200
# 2       A    150
# 3       B    250

When ignore_index=True, the index is reset to start from 0. If not used, the original index (0, 1, 0, 1) is duplicated.

Horizontal (axis=1)

python

info = pd.DataFrame({"name": ["Alice", "Bob"], "age": [30, 25]})
scores = pd.DataFrame({"math": [90, 85], "english": [88, 92]})

combined = pd.concat([info, scores], axis=1)
#     name  age  math  english
# 0  Alice   30    90       88
# 1    Bob   25    85       92

`merge` vs. `concat` – When to use which

	merge	concat
Purpose	Combine by matching keys	Simply stack/append
SQL Analogy	JOIN	UNION
Key Required	Yes (on, left_on/right_on)	No
Direction	Horizontal (add columns)	Vertical or horizontal
Use Case	Users + Orders, Products + Categories	Combine monthly data, combine split files

text

"Connect users and orders" -> merge (key matching)
"Append January data and February data" -> concat (simple stacking)

`merge` Caution – Duplication and Key Mismatch

Row explosion in many-to-many merges

python

# One user_id has multiple orders and shipments
orders = pd.DataFrame({
    "user_id": [1, 1, 1],
    "order_id": [101, 102, 103]
})
shipments = pd.DataFrame({
    "user_id": [1, 1],
    "shipment_id": ["S1", "S2"]
})

result = pd.merge(orders, shipments, on="user_id")
print(len(result))  # 6! (3 × 2 = Cartesian product)

When there are multiple keys on both sides, all combinations are generated. 3 rows × 2 rows = 6 rows. This type of merging can cause memory to explode in large datasets. You can prevent this with the validate parameter:

python

pd.merge(orders, shipments, on="user_id", validate="many_to_one")
# MergeError: Merge keys are not unique in right dataset

Practical Pattern – Combining Multiple Files

python

import os
import pandas as pd

data_dir = "monthly_reports"
all_dfs = []

for filename in sorted(os.listdir(data_dir)):
    if filename.endswith(".csv"):
        filepath = os.path.join(data_dir, filename)
        df = pd.read_csv(filepath)
        df["source_file"] = filename
        all_dfs.append(df)

combined = pd.concat(all_dfs, ignore_index=True)
print(f"Total rows: {len(combined)}")

This is a pattern for combining monthly CSV files into one. You can add a source_file column to track which file it came from.

Key Takeaways

Function	Purpose	Key Parameters
`merge()`	Key-based merging (JOIN)	`on`, `how`, `left_on`/`right_on`
`concat()`	Simple stacking (UNION)	`axis`, `ignore_index`

Join	Scope
inner	Keys present in both sides
left	All from left + matching from right
right	All from right + matching from left
outer	All from both (NaN if missing)

80% of data analysis is about correctly combining the right tables in the right way. Understanding the difference between merge and concat, and the behavior of the four types of joins, can help you prevent common data preprocessing mistakes (duplicate rows, NaN explosion, key mismatches).

BioPlayground

DataFrame Merging — merge and concat

DataFrame Merging: `merge` vs. `concat`

After this topic, you will be able to:

When is merging necessary?

`merge` – Combining based on keys

Types of Joins

Inner Join (default)

Left Join

Right Join

Outer Join

When key column names are different

`concat` – Simply stacking

Vertical (axis=0, default)

Horizontal (axis=1)

`merge` vs. `concat` – When to use which

`merge` Caution – Duplication and Key Mismatch

Row explosion in many-to-many merges

Practical Pattern – Combining Multiple Files

Key Takeaways

💬 Questions & Comments

DataFrame Merging — merge and concat

DataFrame Merging: merge vs. concat

After this topic, you will be able to:

When is merging necessary?

merge – Combining based on keys

Types of Joins

Inner Join (default)

Left Join

Right Join

Outer Join

When key column names are different

concat – Simply stacking

Vertical (axis=0, default)

Horizontal (axis=1)

merge vs. concat – When to use which

merge Caution – Duplication and Key Mismatch

Row explosion in many-to-many merges

Practical Pattern – Combining Multiple Files

Key Takeaways

💬 Questions & Comments

DataFrame Merging: `merge` vs. `concat`

`merge` – Combining based on keys

`concat` – Simply stacking

`merge` vs. `concat` – When to use which

`merge` Caution – Duplication and Key Mismatch