How to Join Two DataFrames with pandas in Python

Combining data from two different tables is one of the most common tasks in data analysis. With pandas' merge you can join two DataFrames on the column they share — for example, linking an orders table to a customers table. Here you'll do it step by step, with simple examples and the most frequent mistakes to avoid.

Prerequisites

• Python 3 installed on your computer.
• The pandas library installed (pip install pandas).
• Basic knowledge of a DataFrame: rows, columns and the read_csv function.

Step 1: Create the two DataFrames

For this example we'll use two small tables: one with customers and another with orders. Notice that both have the cliente_id column — that is the column that links the two tables.

import pandas as pd

clientes = pd.DataFrame({
    "cliente_id": [1, 2, 3],
    "nome": ["Ana", "Bruno", "Carla"],
    "cidade": ["Lisboa", "Porto", "Braga"]
})

pedidos = pd.DataFrame({
    "pedido_id": [100, 101, 102, 103],
    "cliente_id": [1, 2, 1, 3],
    "valor": [50, 30, 20, 90]
})

Step 2: Join the tables with merge

The pd.merge function takes the two DataFrames and the name of the shared column in the on parameter. The result is a new table where each order sits next to the data of its customer.

resultado = pd.merge(pedidos, clientes, on="cliente_id")
print(resultado)

For each row in pedidos, pandas looks up the matching row in clientes with the same cliente_id and joins the columns from both tables.

Tip: always use on when the linking column has the same name in both tables. It is the clearest way and avoids confusion.

Step 3: Choose the join type with how

By default, merge performs an inner join: it only keeps the rows that exist in both tables. The how parameter lets you change that behaviour:

• how="inner" — only customers with orders (the default).
• how="left" — all orders, even without a matching customer.
• how="right" — all customers, even without orders.
• how="outer" — everything from both tables.

todos = pd.merge(clientes, pedidos, on="cliente_id", how="left")
print(todos)

With how="left" starting from clientes, a customer with no orders still appears, with missing values (NaN) in the order columns.

Step 4: Join by columns with different names

Sometimes the same information has different names in each table — for example cliente_id in one and id in the other. In that case use left_on and right_on instead of on.

resultado = pd.merge(
    pedidos, clientes,
    left_on="cliente_id",
    right_on="id"
)

A common mistake is getting a KeyError: it happens when the name given in on does not exist exactly in one of the tables. Always check the names with df.columns.

Step 5: Avoid repeated columns with suffixes

When both tables have columns with the same name (besides the linking column), pandas adds the suffixes _x and _y to tell them apart. You can control those suffixes with the suffixes parameter to make the result more readable.

resultado = pd.merge(
    pedidos, clientes,
    on="cliente_id",
    suffixes=("_pedido", "_cliente")
)

Check the result

To confirm the join worked, look at the number of rows and peek at the first ones with head():

print(resultado.shape)
print(resultado.head())

If the number of rows is much larger than you expected, the linking column probably has repeated values in both tables — it is worth investigating with value_counts().

Conclusion

You now know how to join two DataFrames with merge, choose the join type with how and deal with columns that have different names. The natural next step is to explore groupby to summarise the joined data — for example, the total spent per city. What will be the first join you try on your own data?