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.

From the book

McKinney’s example dataset is a many-to-one join: the “many” side (orders, one row per order) has repeated customer_id values, while the “one” side (customers) has a unique row per id. If you don’t pass on=, pandas.merge will just use whatever column names overlap between the two tables — which works, but naming your join key explicitly is safer and self-documenting.

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)

merge: SQL-style joins

Inner join (only matching keys)

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)

Outer join (keep everything)

Outer join

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

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

join: align by index

join 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)

concat: 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)

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)

Merge tips

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

Patching data with combine_first

merge, join, and concat 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) 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_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)

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

With DataFrames, combine_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 NaN where there’s no match on the right.

diagram Join types mermaid

  flowchart LR
  A["customers"] --> M{"merge on customer_id"}
  B["orders"] --> M
  M -->|"how='inner'"| I["Only matching keys"]
  M -->|"how='left'"| L["All of customers + matches"]
  M -->|"how='outer'"| O["Everything, NaN where missing"]

Inner, left, and outer joins differ in which unmatched rows they keep.

sketch Inner vs left join p5.js Pause Restart

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

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Left Join Keeps Everything on the Left

pch.exerciseTag pch.exerciseTitle

Exercise 3 – Stack Two DataFrames With concat

pch.exerciseTag pch.exerciseTitle

Next

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