Pandas merge, join and concat: Combining Tables

Real data lives in several tables. Orders know the customer id and the store id, but not the customer's city or the store's manager. Order items know the product id, but not the product's category. To answer a question like "which product category sells best in which city to Prime customers?" you must join the tables together using the columns they share. If you know SQL, merge is JOIN, and concat is UNION ALL.

Joins are also the number one source of silent errors in analysis. A join on a key with duplicates can multiply your rows and inflate your totals without any error message. A join on the wrong key can drop half your data. So in this lesson we not only learn the mechanics (inner, left, right, outer) but also the habits that protect you: checking row counts before and after, using validate, and using indicator to see what failed to match.

Worked examples

Example 1: Know your tables

Question: How do the FreshBasket tables connect?

import pandas as pd
import numpy as np

orders = pd.read_csv("orders.csv")
items = pd.read_csv("order_items.csv")
products = pd.read_csv("products.csv")
customers = pd.read_csv("customers.csv")
stores = pd.read_csv("stores.csv")

for name, t in [("orders", orders), ("items", items), ("products", products), ("customers", customers), ("stores", stores)]:
    print(f"{name:<10}{t.shape[0]:>7,} rows  {list(t.columns)[:5]}")

print(orders["order_id"].is_unique, items["order_id"].is_unique)
print(products["product_id"].is_unique, customers["customer_id"].is_unique)

orders      6,000 rows  ['order_id', 'customer_id', 'store_id', 'order_date', 'delivery_slot']
items      19,962 rows  ['order_id', 'product_id', 'quantity', 'unit_price']
products       50 rows  ['product_id', 'product_name', 'category', 'brand', 'unit_price']
customers     600 rows  ['customer_id', 'name', 'email', 'city', 'signup_date']
stores         11 rows  ['store_id', 'city', 'store_name', 'manager', 'opened_on']
True False
True True

How it works: Each table has a key. orders.order_id, products.product_id, customers.customer_id and stores.store_id are unique (one row per entity). order_items repeats order_id (many lines per order) and refers to product_id. The shape of these relationships decides what a join does to the row count.

Reading the result: Orders have a unique id (True), but order items do not (False), because an order has several lines. Joining orders to items is therefore one-to-many: the result will have as many rows as items.

Example 2: Inner join

Question: Add the store's city and manager to each order.

joined = orders.merge(stores, on="store_id", how="inner")
print(orders.shape, joined.shape)
print(joined[["order_id", "store_id", "city", "manager"]].head(3).to_string(index=False))
print(joined["city"].value_counts().head(3).to_dict())

(6000, 9) (6000, 13)
order_id store_id      city     manager
  O02299      S03    Mumbai Meera Verma
  O04533      S07 Hyderabad  Karan Nair
  O00778      S09      Pune Rohan Kumar
{'Bengaluru': 1301, 'Delhi': 1291, 'Mumbai': 945}

How it works: merge(right, on=key, how="inner") keeps only the rows whose key exists in both tables. Here every order has a valid store, so no rows are lost, and since stores has unique store_id, the row count stays the same. This is a many-to-one join (many orders per store), the safest and most common type.

Reading the result: All 6,000 orders kept their place, and each now carries the store's city and manager. The busiest city by orders is Bengaluru.

Example 3: Left join and missing matches

Question: What happens when keys do not match?

small_products = products.head(30)

left = items.merge(small_products[["product_id", "product_name"]], on="product_id", how="left")
inner = items.merge(small_products[["product_id", "product_name"]], on="product_id", how="inner")

print(len(items), len(left), len(inner))
print(left["product_name"].isna().sum())
print(left[left["product_name"].isna()]["product_id"].nunique())

19962 19962 13056
6906
20

How it works: A left join keeps every row of the left table; where the right table has no match, its columns are filled with NaN. An inner join drops unmatched rows. With only the first 30 products on the right side, a left join keeps all 19962 lines but leaves names blank for the others, while an inner join silently shrinks the table.

Reading the result: The left join kept 19,962 rows with 6,906 missing names; the inner join kept only 13,056. When you want to preserve all the left-hand data, use how="left".

Example 4: Right and outer joins

Question: Which products have never been sold, and what does a full outer join show?

sold = items.groupby("product_id", as_index=False)["quantity"].sum()

right = sold.merge(products, on="product_id", how="right")
print(right["quantity"].isna().sum())

outer = sold.merge(small_products, on="product_id", how="outer", indicator=True)
print(outer["_merge"].value_counts().to_dict())
print(len(sold), len(products))

0
{'both': 30, 'left_only': 20, 'right_only': 0}
50 50

How it works: A right join keeps every row from the right table, and a full outer join keeps rows from both. The indicator=True option adds a _merge column showing whether each row came from left_only, right_only or both. That column is the quickest way to audit a join.

Reading the result: All 50 products have sold something (0 with no sales), and the outer join against only 30 products reveals 20 sold products that are missing from the smaller table.

Example 5: Different key names and several keys

Question: Join on columns with different names, and on two columns.

cust = customers.rename(columns={"customer_id": "cust_id"})

a = orders.merge(cust[["cust_id", "city", "is_prime"]], left_on="customer_id", right_on="cust_id", how="left")
print(a.shape, a["city"].isna().sum())
print(a.columns.tolist()[-4:])

a = a.drop(columns="cust_id")

daily = orders.groupby(["store_id", "order_date"]).size().rename("orders").reset_index()
targets = daily.head(5).assign(target=10)
two_keys = daily.merge(targets[["store_id", "order_date", "target"]], on=["store_id", "order_date"], how="left")
print(two_keys["target"].notna().sum())

(6000, 12) 0
['discount_pct', 'cust_id', 'city', 'is_prime']
5

How it works: left_on and right_on name the key in each table when the names differ; both key columns survive, so drop one. Passing a list to on matches rows only when all keys agree, as in SQL's composite join conditions.

Reading the result: Every order found its customer (0 unmatched), and the two-key join attached targets to exactly 5 store-days.

Example 6: Overlapping column names

Question: Both tables have a city column. What happens?

cust_small = customers[["customer_id", "city"]]
store_city = stores[["store_id", "city"]]

j = orders.merge(cust_small, on="customer_id").merge(store_city, on="store_id", suffixes=("_customer", "_store"))
print(j.columns.tolist())
print(j[["customer_id", "city_customer", "store_id", "city_store"]].head(3).to_string(index=False))

same = (j["city_customer"] == j["city_store"]).mean()
print(round(same, 3))

['order_id', 'customer_id', 'store_id', 'order_date', 'delivery_slot', 'payment_method', 'order_status', 'delivery_minutes', 'discount_pct', 'city_customer', 'city_store']
customer_id city_customer store_id city_store
      C0078        Mumbai      S03     Mumbai
      C0230     Hyderabad      S07  Hyderabad
      C0328          Pune      S09       Pune
1.0

How it works: When the two sides share non-key column names, pandas appends _x and _y, or the suffixes you provide. Be explicit: meaningful names such as city_customer and city_store prevent confusion. Note the first merge had only one city, so no suffix was needed there; the clash appeared on the second merge.

Reading the result: A customer and the store serving them are in the same city 100.0% of the time, so the two columns agree here. Keeping both under clear suffixes is what lets you run a check like this.

Example 7: validate and the row explosion

Question: How can a join accidentally multiply rows, and how do you prevent it?

dup_products = pd.concat([products, products.head(5)], ignore_index=True)
print(dup_products["product_id"].is_unique)

before = len(items)
bad = items.merge(dup_products[["product_id", "category"]], on="product_id", how="left")
print(before, len(bad), len(bad) - before)

try:
    items.merge(dup_products[["product_id", "category"]], on="product_id", how="left", validate="many_to_one")
except pd.errors.MergeError as e:
    print("MergeError:", str(e)[:70])

good = items.merge(products[["product_id", "category"]], on="product_id", how="left", validate="many_to_one")
print(len(good) == before)

False
19962 20998 1036
MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

D
True

How it works: When the key is duplicated on the right side, each matching left row is repeated once per duplicate, so five duplicate products inflated the table. Revenue totals computed from it would be wrong without any warning. validate="many_to_one" (also one_to_one, one_to_many, many_to_many) makes pandas raise a MergeError when the data does not have the shape you expect. Use it routinely.

Reading the result: The duplicated key added 1036 extra rows (19962 became 20998), but the validated merge raised an error and the clean merge preserved all 19,962 rows. Always compare row counts before and after a join.

Example 8: Anti-join

Question: Which customers never placed an order?

m = customers.merge(orders[["customer_id"]].drop_duplicates(), on="customer_id", how="left", indicator=True)
never = m[m["_merge"] == "left_only"]
print(len(never), len(customers))

alt = customers[~customers["customer_id"].isin(orders["customer_id"])]
print(len(alt), set(never["customer_id"]) == set(alt["customer_id"]))

print(never[["customer_id", "signup_date", "acquisition_channel"]].head(3).to_string(index=False))

12 600
12 True
customer_id signup_date acquisition_channel
      C0010  2024-08-26            Referral
      C0037  2025-11-27    Offline Campaign
      C0088  2025-01-04            Referral

How it works: An anti-join returns rows from one table that have no match in another. With pandas, do a left join with indicator=True and keep left_only, or use ~isin. Both give the same answer. In SQL this is LEFT JOIN ... WHERE right.key IS NULL or NOT EXISTS.

Reading the result: 12 of the 600 customers have never ordered, and the two methods agree. These are candidates for an activation campaign.

Example 9: join on the index

Question: Use the index for joining and combining Series.

store_orders = orders.groupby("store_id").size().rename("orders")
store_avg = orders.groupby("store_id")["delivery_minutes"].mean().round(1).rename("avg_minutes")

combined = store_orders.to_frame().join(store_avg).join(stores.set_index("store_id")[["city"]])
print(combined.head(4))

table = pd.concat([store_orders, store_avg], axis=1)
print(table.shape, table.equals(combined[["orders", "avg_minutes"]]))

          orders  avg_minutes       city
store_id                                
S01          682         23.6  Bengaluru
S02          619         24.0  Bengaluru
S03          457         23.5     Mumbai
S04          488         23.6     Mumbai
(11, 2) True

How it works: DataFrame.join merges on the index by default, which is concise when the grouped results already have the key as their index. pd.concat(..., axis=1) lines up pieces side by side on their index. Choose whichever reads better; merge is more explicit.

Reading the result: The combined table has one row per store (11) with orders, average minutes and city, built without writing a single key name.

Example 10: concat to stack tables

Question: Combine monthly files into one table.

orders["order_date"] = pd.to_datetime(orders["order_date"])
parts = {m: g for m, g in orders.groupby(orders["order_date"].dt.to_period("M"))}
print(len(parts))

stacked = pd.concat(parts.values(), ignore_index=True)
print(len(stacked) == len(orders), stacked.shape)

jan = parts[pd.Period("2025-01")]
feb = parts[pd.Period("2025-02")]
labelled = pd.concat([jan, feb], keys=["jan", "feb"])
print(labelled.groupby(level=0).size().to_dict())

mismatch = pd.concat([jan[["order_id"]], feb[["order_id", "store_id"]]])
print(mismatch.shape, mismatch["store_id"].isna().sum() == len(jan))

18
True (6000, 9)
{'feb': 67, 'jan': 66}
(133, 2) True

How it works: concat stacks tables vertically (default axis=0). ignore_index=True renumbers rows, and keys= adds an outer index level to remember where each piece came from. Columns are aligned by name: columns missing from one piece are filled with NaN, as the last example shows. Concatenating many files into a list first and calling concat once is much faster than appending in a loop.

Reading the result: The 18 monthly pieces stack back into the full 6,000 rows. The labelled January and February pieces contain 66 and 67 orders respectively.

Common mistakes

1. Not checking row counts before and after a join.

2. Joining on a non-unique key without validate, inflating totals.

3. Using inner when you meant left, silently dropping data.

4. Forgetting that key dtypes must match (a string id will not match an integer id).

5. Appending to a DataFrame in a loop instead of collecting pieces and calling concat once.

orders.merge(stores, on="no_such_key")

Traceback (most recent call last):
  File "example.py", line 1, in <module>
    orders.merge(stores, on="no_such_key")
    ~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 12902, in merge
    return merge(
        self,
    ...<10 lines>...
        validate=validate,
    )
  File "example.py", line 385, in merge
    op = _MergeOperation(
        left_df,
    ...<10 lines>...
        validate=validate,
    )
  File "example.py", line 1018, in __init__
    ) = self._get_merge_keys()
        ~~~~~~~~~~~~~~~~~~~~^^
  File "example.py", line 1620, in _get_merge_keys
    right_keys.append(right._get_label_or_level_values(rk))
                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^
  File "example.py", line 1764, in _get_label_or_level_values
    raise KeyError(key)
KeyError: 'no_such_key'

Practice questions

Practice 1 (Easy): Add the category to the items

Merge items with products to add category, and count the lines per category.

with_cat = items.merge(products[["product_id", "category"]], on="product_id", validate="many_to_one")
print(with_cat["category"].value_counts())

category
Snacks           4125
Dairy            4036
Fruits & Veg     3060
Staples          2663
Household        2382
Beverages        1944
Personal Care    1752
Name: count, dtype: int64

Practice 2 (Medium): Revenue by city

Compute the revenue (quantity × unit price) of delivered orders per city, ranked highest first.

d = orders[orders["order_status"] == "Delivered"][["order_id", "store_id"]]
rev = (
    items.merge(d, on="order_id", validate="many_to_one")
    .merge(stores[["store_id", "city"]], on="store_id", validate="many_to_one")
)
rev["value"] = rev["quantity"] * rev["unit_price"]
by_city = rev.groupby("city")["value"].sum().sort_values(ascending=False)
print(by_city.round(0))

city
Delhi        746553.0
Bengaluru    742990.0
Mumbai       585420.0
Hyderabad    429685.0
Pune         304967.0
Chennai      261780.0
Kolkata      239062.0
Ahmedabad    223377.0
Name: value, dtype: float64

Practice 3 (Hard): Customers acquired but inactive in 2026

List the customers who ordered in 2025 but placed no order in 2026, with their city and acquisition channel, and count them by channel.

y = orders["order_date"].dt.year
in25 = orders.loc[y == 2025, "customer_id"].drop_duplicates()
in26 = orders.loc[y == 2026, "customer_id"].drop_duplicates()

lapsed = in25.to_frame().merge(in26.to_frame(), on="customer_id", how="left", indicator=True)
lapsed = lapsed[lapsed["_merge"] == "left_only"].drop(columns="_merge")
lapsed = lapsed.merge(customers[["customer_id", "city", "acquisition_channel"]], on="customer_id")
print(len(lapsed))
print(lapsed["acquisition_channel"].value_counts())

33
acquisition_channel
Instagram           10
App Store            9
Referral             7
Google Ads           4
Offline Campaign     3
Name: count, dtype: int64

Key takeaways

• merge joins tables on key columns; choose inner, left, right or outer deliberately.
• Many-to-one joins keep the row count; duplicate keys on the right side multiply rows.
• Protect yourself with validate= and indicator=True, and compare row counts before and after.
• An anti-join (left_only) finds records with no match.
• Use suffixes for clashing names and left_on/right_on for different key names.
• concat stacks tables vertically (or side by side), aligning on column names.