Skip to content
Python Central Hub

Merging and Joining Data (merge, join, concat)

Why merges matter

Most real analytics uses multiple tables:

  • Customers table
  • Orders table
  • Products table

You typically need to combine them to answer questions.

Example tables

Customers and orders
import pandas as pd
 
customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Asha", "Ravi", "Meera"],
})
 
orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104],
    "customer_id": [1, 2, 2, 4],
    "amount": [250, 180, 90, 300],
})
 
print(customers)
print(orders)
Customers and orders
import pandas as pd
 
customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Asha", "Ravi", "Meera"],
})
 
orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104],
    "customer_id": [1, 2, 2, 4],
    "amount": [250, 180, 90, 300],
})
 
print(customers)
print(orders)

mergemerge: SQL-style joins

Inner join (only matching keys)

Inner join
inner = customers.merge(orders, on="customer_id", how="inner")
print(inner)
Inner join
inner = customers.merge(orders, on="customer_id", how="inner")
print(inner)

Left join (keep all left rows)

Left join
left = customers.merge(orders, on="customer_id", how="left")
print(left)
Left join
left = customers.merge(orders, on="customer_id", how="left")
print(left)

Outer join (keep everything)

Outer join
outer = customers.merge(orders, on="customer_id", how="outer", indicator=True)
print(outer)
Outer join
outer = customers.merge(orders, on="customer_id", how="outer", indicator=True)
print(outer)

indicator=Trueindicator=True adds a _merge_merge column so you can see where each row came from.

joinjoin: align by index

joinjoin is convenient when keys are indices.

join by index
customers_idx = customers.set_index("customer_id")
orders_idx = orders.set_index("customer_id")
 
joined = customers_idx.join(orders_idx, how="left")
print(joined)
join by index
customers_idx = customers.set_index("customer_id")
orders_idx = orders.set_index("customer_id")
 
joined = customers_idx.join(orders_idx, how="left")
print(joined)

concatconcat: stack DataFrames

Append rows (same columns)

concat rows
jan = pd.DataFrame({"customer_id": [1, 2], "amount": [100, 200]})
feb = pd.DataFrame({"customer_id": [2, 3], "amount": [150, 50]})
 
all_orders = pd.concat([jan, feb], ignore_index=True)
print(all_orders)
concat rows
jan = pd.DataFrame({"customer_id": [1, 2], "amount": [100, 200]})
feb = pd.DataFrame({"customer_id": [2, 3], "amount": [150, 50]})
 
all_orders = pd.concat([jan, feb], ignore_index=True)
print(all_orders)

Add columns side-by-side

concat columns
df1 = pd.DataFrame({"A": [1, 2]})
df2 = pd.DataFrame({"B": [10, 20]})
 
combined = pd.concat([df1, df2], axis=1)
print(combined)
concat columns
df1 = pd.DataFrame({"A": [1, 2]})
df2 = pd.DataFrame({"B": [10, 20]})
 
combined = pd.concat([df1, df2], axis=1)
print(combined)

Merge tips

  • Use validate=validate= to enforce expected relationships (e.g., one-to-many).
  • Watch for duplicate column names; use suffixes=("_left", "_right")suffixes=("_left", "_right") when needed.
  • After a left join, missing matches become NaNNaN—handle them deliberately.

Patching data with combine_first

mergemerge, joinjoin, and concatconcat all combine different rows or columns from separate tables. Sometimes you instead have two versions of the same table, and you want to patch holes in one using values from the other — wherever the first is missing, fall back to the second.

numpy.where(pd.isna(a), b, a)numpy.where(pd.isna(a), b, a) can do this, but it ignores index labels entirely: it just lines values up by position, so it silently produces the wrong answer if the two objects aren’t already sorted the same way. combine_firstcombine_first does the same “fall back to the other value” logic, but it aligns by label first, the same way arithmetic between two Series does.

combine_first patches holes by label
import pandas as pd
import numpy as np
 
primary = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "email": ["asha@mail.com", np.nan, "meera@mail.com", np.nan],
    "phone": [np.nan, "9876500001", np.nan, "9876500004"],
}).set_index("customer_id")
 
backup = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "email": ["asha@old.com", "ravi@mail.com", "meera@old.com", "kabir@mail.com"],
    "phone": ["9876500011", "9876500002", "9876500003", "9876500004"],
}).set_index("customer_id")
 
patched = primary.combine_first(backup)
print(patched)
combine_first patches holes by label
import pandas as pd
import numpy as np
 
primary = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "email": ["asha@mail.com", np.nan, "meera@mail.com", np.nan],
    "phone": [np.nan, "9876500001", np.nan, "9876500004"],
}).set_index("customer_id")
 
backup = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "email": ["asha@old.com", "ravi@mail.com", "meera@old.com", "kabir@mail.com"],
    "phone": ["9876500011", "9876500002", "9876500003", "9876500004"],
}).set_index("customer_id")
 
patched = primary.combine_first(backup)
print(patched)

Every NaNNaN cell in primaryprimary gets filled in from backupbackup’s matching row and column — customer 1 keeps their own email (it wasn’t missing) but borrows a phone number from backupbackup, while customer 2 does the opposite. Where both sides have a value, primaryprimary’s value always wins.

With DataFrames, combine_firstcombine_first works column by column, and the result has the union of both objects’ columns — think of it as “patching,” not merging: it fills gaps in your main table using a secondary source, rather than joining rows on a key.

Visualize it

An inner join keeps only matching keys; a left join keeps every row from the left table and fills in NaNNaN where there’s no match on the right.

diagram Join types mermaid
Inner, left, and outer joins differ in which unmatched rows they keep.
sketch Inner vs left join p5.js
An inner join keeps only overlapping keys; a left join keeps every left-table row and fills gaps with NaN.

🧪 Try It Yourself

Exercise 1 – Inner Join Two Tables

Exercise 2 – Left Join Keeps Everything on the Left

Exercise 3 – Stack Two DataFrames With concat

Next

With tables combined, you’re ready to work with time-based data — continue to Working with Dates and Times (to_datetime, dt accessor).

If this helped you, consider buying me a coffee ☕

Buy me a coffee

Was this page helpful?

Let us know how we did