Merging and Concatenating

how, indicator and validate

merge joins DataFrames on key columns like SQL: inner keeps matches, left keeps all left rows (missing matches become NaN), outer keeps everything. indicator=True adds a _merge column showing where each row came from, which is great for finding orphans. validate="one_to_one" or "many_to_one" checks key uniqueness and raises MergeError if the assumption is wrong. concat stacks frames vertically or horizontally.

Joining orders to customers, run

I ran this with Python 3.12.3 and pandas 3.0.6. The inner join drops order 4 (unknown customer 99); the left join keeps it and marks it left_only; validate one_to_one fails because customer 10 has two orders.

import pandas as pd

orders = pd.DataFrame({"order_id": [1, 2, 3, 4], "customer_id": [10, 11, 10, 99],
                       "amount": [120, 80, 45, 60]})
customers = pd.DataFrame({"customer_id": [10, 11, 12], "name": ["Asha", "Ben", "Chen"]})

print(orders.merge(customers, on="customer_id", how="inner"))
left = orders.merge(customers, on="customer_id", how="left", indicator=True)
print(left[["order_id", "name", "_merge"]])
print(pd.concat([orders.head(1), orders.tail(1)]).shape)
try:
    orders.merge(customers, on="customer_id", validate="one_to_one")
except Exception as e:
    print(type(e).__name__)

Output:

   order_id  customer_id  amount  name
0         1           10     120  Asha
1         2           11      80   Ben
2         3           10      45  Asha
   order_id  name     _merge
0         1  Asha       both
1         2   Ben       both
2         3  Asha       both
3         4   NaN  left_only
(2, 3)
MergeError

Check row counts after every join

An unexpected increase in rows usually means duplicate keys; use validate to catch it.

त्वरित जाँच: Which join keeps every order even if the customer is unknown?

• concat with axis=1
• An inner join
• A right join from orders
• A left join from orders