Combine DataFrames in Python

You've wrangled one DataFrame, cleaned it, sliced it, and summarized it. But real data analysis rarely lives in a single table. Customer data sits in one file, order history in another, and product details in a third. The moment you need to answer a question that spans those tables, you hit a wall: how do you combine dataframes in Python? Without a solid grip on join, merge, and concatenate, you'll either copy-paste data into spreadsheets or write slow, brittle loops. This lesson gives you the mental model and hands-on skills to combine DataFrames confidently — the exact tools used in every serious pandas workflow.

The problem this lesson solves

When your analysis requires data from multiple sources, you have two fundamental operations: stacking rows and joining columns. The pain points are familiar:

• You have sales data for January and February in separate files, and you need one table with all rows.
• You have a customer table and an orders table, and you need to attach customer names to each order.
• You're about to merge on a column that has slightly different names — and you're not sure whether pandas will crash or silently produce garbage.

Without knowing the right tool, you might try to loop over rows and manually look up values in dictionaries — slow, error-prone, and unmaintainable. Or you might attempt a merge and end up with a Cartesian explosion — thousands of unintended row combinations.

The pandas library solves this elegantly with three main functions: concat(), merge(), and join(). Each addresses a specific shape of problem. This lesson strips away the confusion and gives you a decision framework you'll use for every future analysis.

Core concept / mental model

Think of DataFrames as tables in a relational database. Combining them is like performing SQL JOIN or UNION operations. Here's the analogy that clarifies the three tools:

• concat() is stacking blocks. You place one DataFrame on top of another (or side by side) — like piling up sheets of paper. It matches rows or columns by position, not by a shared key.
• merge() is a database join. You combine columns from two DataFrames by matching values in a shared column (or multiple columns). It's the Swiss Army knife for relational-style joins: inner, left, right, outer.
• join() is a convenient alias for merge() — mostly used when joining on the index (row labels) rather than a column.

Here's a quick visual summary:

Definitions to keep handy:

• Key: the column (or index) you match on.
• Join type: how to handle rows that appear in one table but not the other (inner, left, right, outer).
• Cartesian product: every row from one table paired with every row from the other — dangerous if keys have duplicates.

Once you internalize this, every combination task becomes a matter of answering three questions: Do I need to stack rows or attach columns? Am I matching on a column or the index? Do I want to keep all rows or only matches?

How it works step by step

1. Concatenating rows with concat()

When your DataFrames have the same columns (or you want to stack them along axis 0), concat() is your tool. The function takes a list of DataFrames and stacks them vertically by default.

import pandas as pd

# Two monthly sales tables
jan = pd.DataFrame({
    "region": ["North", "South"],
    "revenue": [500, 300]
})

feb = pd.DataFrame({
    "region": ["North", "West"],
    "revenue": [700, 450]
})

combined = pd.concat([jan, feb], ignore_index=True)
print(combined)

Output:

  region  revenue
0  North      500
1  South      300
2  North      700
3  West       450

Notice that the original indices are retained unless you pass ignore_index=True. That parameter resets the index to a clean 0..n-1 sequence — often what you want.

2. Merging on a column with merge()

When you need to attach columns from a second table based on a shared key, use merge(). By default, it performs an inner join — only rows with matching keys are kept.

customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Alice", "Bob", "Charlie"]
})

orders = pd.DataFrame({
    "customer_id": [1, 1, 3],
    "order_total": [99.5, 57.0, 120.0]
})

full_orders = pd.merge(customers, orders, on="customer_id")
print(full_orders)

Output:

   customer_id     name  order_total
0            1    Alice         99.5
1            1    Alice         57.0
2            3  Charlie        120.0

Note that customer 2 had no orders, so they're dropped in an inner join. To keep all customers, you'd use how="left".

3. Joining on the index with join()

The join() method is a convenience for merging on the index. This is handy when row labels carry meaning (e.g., dates, IDs) and you don't want to reset them.

ratings = pd.DataFrame({
    "avg_rating": [4.5, 4.7, 4.2]
}, index=["TT101", "TT102", "TT103"])

prices = pd.DataFrame({
    "price": [29.99, 49.99, 39.99]
}, index=["TT101", "TT102", "TT104"])

combined = ratings.join(prices, how="left")
print(combined)

Output:

       avg_rating  price
TT101         4.5  29.99
TT102         4.7  49.99
TT103         4.2    NaN

The TT103 rating gets NaN for price because no matching row exists in prices.

Hands-on walkthrough

Let's combine everything with a realistic scenario: you're a data analyst at an e-commerce company. You have three dataframes:

• customers: customer_id, name, signup_date
• orders: order_id, customer_id, order_total
• order_items: order_id, product_id, quantity

Your goal: build a single DataFrame with each order line, the customer name, and the product ID.

Step 1: Load your data

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "name": ["Alice", "Bob", "Charlie", "Diana"],
    "signup_date": ["2024-01-15", "2024-02-10", "2024-03-01", "2024-03-20"]
})

orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104],
    "customer_id": [1, 2, 1, 3],
    "order_total": [99.5, 150.0, 57.0, 120.0]
})

order_items = pd.DataFrame({
    "order_item_id": [1, 2, 3, 4, 5],
    "order_id": [101, 101, 102, 103, 104],
    "product_id": ["P001", "P002", "P001", "P003", "P002"],
    "quantity": [1, 2, 1, 3, 2]
})

Step 2: Merge orders with customers

We want the customer name on every order. Since we need all orders (even if a customer is missing), use a left join.

orders_with_customers = pd.merge(
    orders, customers, on="customer_id", how="left"
)
print(orders_with_customers)

Output:

   order_id  customer_id  order_total name signup_date
0       101            1         99.5    Alice  2024-01-15
1       102            2        150.0    Bob    2024-02-10
2       103            1         57.0    Alice  2024-01-15
3       104            3        120.0    Charlie 2024-03-01

Step 3: Merge item details with orders

Now join the order IDs with the item lines to get product information on each line.

full_order_items = pd.merge(
    order_items, orders_with_customers, on="order_id", how="left"
)
print(full_order_items)

Output:

   order_item_id  order_id product_id  quantity  customer_id  order_total name signup_date
0              1       101      P001         1            1         99.5  Alice  2024-01-15
1              2       101      P002         2            1         99.5  Alice  2024-01-15
2              3       102      P001         1            2        150.0  Bob    2024-02-10
3              4       103      P003         3            1         57.0  Alice  2024-01-15
4              5       104      P002         2            3        120.0  Charlie 2024-03-01

Step 4: Add revenue per line (bonus)

You might also compute a total price per line. But that requires product prices — another merge. The pattern repeats.

prices = pd.DataFrame({
    "product_id": ["P001", "P002", "P003"],
    "price": [20.0, 15.5, 10.0]
})

final = pd.merge(full_order_items, prices, on="product_id", how="left")
final["line_revenue"] = final["quantity"] * final["price"]
print(final.head(3))

Output:

   order_item_id  order_id product_id  quantity  customer_id  order_total name signup_date  price  line_revenue
0              1       101      P001         1            1         99.5  Alice  2024-01-15   20.0          20.0
1              2       101      P002         2            1         99.5  Alice  2024-01-15   15.5          31.0
2              3       102      P001         1            2        150.0  Bob    2024-02-10   20.0          20.0

This pipeline — sequential merge() calls — is the foundation of almost every relational data analysis in pandas.

Pro tip: Always inspect the shape of your DataFrame after a merge. If the row count jumps unexpectedly, you probably have duplicate keys.

Compare options / when to choose what

Here's a quick decision table to help you pick the right tool:

Variations to consider

1. SQL-style joins: If you're coming from SQL, think of merge() as your JOIN clause. The how parameter maps to inner, left, right, outer.
2. Manual iteration: You could loop through rows and use dictionaries, but that's slow and error-prone. Avoid unless you have millions of rows and need to optimize with vectorized operations.
3. Third-party tools: For very large datasets, consider dask.dataframe or polars for out-of-core merging. But the pandas syntax translates directly.

Troubleshooting & edge cases

Case 1: Column name mismatch

Your two DataFrames have the same data but different column names (CustomerID vs customer_id). If you call merge() with on="customer_id" on DataFrames that both have that column, it works. But if one has customer_id and the other has CustomerID, pandas raises a ValueError:

ValueError: can not merge DataFrame with instance of type <class 'NoneType'>

Fix: Use the left_on and right_on parameters:

merged = pd.merge(
    customers,   # has 'customer_id'
    old_orders,  # has 'CustomerID'
    left_on="customer_id",
    right_on="CustomerID",
    how="left"
)

Case 2: Duplicate keys causing row explosion

If customer_id appears multiple times in both DataFrames, you'll get a Cartesian product — every pair of duplicate keys combined. Check your data for duplicates before merging.

# Detect duplicate keys
print(customers['customer_id'].duplicated().sum())
print(orders['customer_id'].duplicated().sum())

Fix: Clean duplicates or use validate parameter:

pd.merge(customers, orders, on="customer_id", validate="one_to_many")

validate='one_to_many' raises an error if the merge would produce a many-to-many result.

Case 3: Index alignment surprise with concat()

When concatenating with axis=1, pandas aligns rows by index labels — not by position. If your indices don't match, you'll get NaN values.

left = pd.DataFrame({"a": [1, 2]}, index=[0, 1])
right = pd.DataFrame({"b": [3, 4]}, index=[1, 2])
print(pd.concat([left, right], axis=1))

Output:

     a    b
0  1.0  NaN
1  2.0  3.0
2  NaN  4.0

Fix: Reset indices before concatenating, or use ignore_index=True (works only along axis 0). For axis=1, you need to reset the index manually:

pd.concat([left.reset_index(drop=True), right.reset_index(drop=True)], axis=1)

Case 4: Missing keys with NaN rows

When using an outer join, rows without matches get NaN in the missing columns. That's expected — don't panic.

full = pd.merge(customers, orders, on="customer_id", how="outer")
print(full)

Case 5: Type mismatches on key columns

If customer_id is int in one DataFrame and str ("1") in the other, the merge won't match. Cast them to the same type first:

orders['customer_id'] = orders['customer_id'].astype(str)

Or better, use pd.to_numeric or astype consistently.

What you learned & what's next

You've just mastered the three pillars of combining DataFrames in pandas:

• concat() for stacking rows or columns when position matters.
• merge() for relational joins on shared columns — the workhorse of data analysis.
• join() as a convenient shortcut on indexes.

You can now explain the core idea behind each operation and complete practical exercises that attach, stack, and blend dataframes with confidence. You also know how to troubleshoot common pitfalls like duplicate keys, column mismatches, and type mismatches.

This is a cornerstone skill. Next, you'll dive into data aggregation and grouping — using groupby() to summarize combined datasets, which is the natural next step after you've brought all your data together. You'll learn how to compute totals, averages, and counts by category, and you'll see how the merged frames you build here become the foundation for powerful insights.

Remember: every merge is a tiny decision. Ask yourself: What shape do I need? What key am I using? Do I want to keep unmatched rows? Then pick the tool that answers those questions.

Now go merge some real data — your analysis will thank you.