Introduction

Merging multiple pandas DataFrames usually means combining them either by key columns or by index. The right tool depends on whether you are doing a relational join, stacking rows, or aligning tables side by side.

Repeated merge for key-based joins

If several DataFrames share a common key, the most direct approach is to merge them one by one.

1import pandas as pd
2
3customers = pd.DataFrame({
4    "customer_id": [1, 2, 3],
5    "name": ["Ada", "Lin", "Noah"],
6})
7
8orders = pd.DataFrame({
9    "customer_id": [1, 1, 2],
10    "total": [100, 80, 120],
11})
12
13regions = pd.DataFrame({
14    "customer_id": [1, 2, 3],
15    "region": ["East", "West", "East"],
16})
17
18merged = customers.merge(orders, on="customer_id", how="left")
19merged = merged.merge(regions, on="customer_id", how="left")
20
21print(merged)

This pattern is explicit and easy to read, especially when each merge uses different join keys or join types.

Using functools.reduce for many DataFrames

If you have a list of DataFrames with the same join key, reduce can keep the code compact.

1from functools import reduce
2import pandas as pd
3
4dfs = [customers, orders, regions]
5
6merged = reduce(
7    lambda left, right: pd.merge(left, right, on="customer_id", how="left"),
8    dfs,
9)
10
11print(merged)

This is useful when the number of DataFrames is dynamic, but it is slightly harder to debug than explicit step-by-step merges.

When concat is the right tool

Do not use merge if you are simply stacking DataFrames with the same columns. Use pd.concat instead.

1q1 = pd.DataFrame({"month": ["Jan", "Feb"], "sales": [10, 12]})
2q2 = pd.DataFrame({"month": ["Mar", "Apr"], "sales": [9, 15]})
3
4combined = pd.concat([q1, q2], ignore_index=True)
5print(combined)

concat is for appending along rows or columns. merge is for SQL-like joins on keys.

Merging on index

Sometimes the shared key is already the index. In that case, join on the index directly.

1left = pd.DataFrame({"sales": [10, 20]}, index=["Jan", "Feb"])
2right = pd.DataFrame({"cost": [7, 14]}, index=["Jan", "Feb"])
3
4result = left.join(right)
5print(result)

join is especially convenient when several tables are already indexed the same way.

Choosing the join type

When merging multiple tables, how matters a lot:

• 'inner keeps only keys present in both sides'
• 'left keeps all keys from the left DataFrame'
• 'outer keeps the union of all keys'

If you merge several DataFrames in sequence, the join type at each step affects the final row set. That is why multi-merge pipelines can silently shrink or expand more than expected.

Watch for duplicate rows

If the key is not unique in one or more DataFrames, each merge can multiply rows. For example, a customer table merged with an orders table produces one row per order, not one row per customer.

This is correct relational behavior, but it surprises people who expected a one-to-one join.

You can validate key shape before merging:

assert customers["customer_id"].is_unique
assert regions["customer_id"].is_unique

That kind of guard is worth adding in data pipelines.

Common Pitfalls

• Using merge when concat is the right operation.
• Forgetting that sequential merges can duplicate rows if the join key is not unique.
• Mixing join types without checking how they affect the final row count.
• Assuming all DataFrames have the same key name when they do not.
• Writing a compact reduce expression before confirming the simpler step-by-step merge works.

Summary

• Use repeated merge calls when joining multiple DataFrames on key columns.
• Use functools.reduce when the number of DataFrames is dynamic and the join logic is uniform.
• Use pd.concat for stacking DataFrames, not for relational joins.
• Use join when the merge key is already the index.
• Always check whether your keys are unique before assuming the merge shape will stay one-to-one.