# Merging and Concatenating — NumPy / Pandas / scikit-learn

Source: https://www.skillbyai.com/en/numpy-pandas-sklearn/g-merge

> SQL-style joins.

## 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.

```python
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.

**Quiz:** Which join keeps every order even if the customer is unknown?

- [ ] concat with axis=1
- [ ] An inner join
- [ ] A right join from orders
- [x] A left join from orders

*Answer:* A left join from orders. Left keeps all left rows.