Outer Merge
An outer merge keeps everything from both sides. Unmatched rows get NaN where the other side has no data. This is useful for gap analysis: “which products have reviews but no sales, and which have sales but no reviews?” In practice, outer merges are rarer than inner or left — but when you need them, nothing else works.
Keeping Everything from Both Sides
how="outer" is like running left and right merge simultaneously. You get all rows from both DataFrames: matches where they exist, NaN where they don’t.
indicator=True makes outer merge especially powerful. The _merge column instantly tells you: both (matched), left_only (customer with no orders), right_only (order with no valid customer).
“Find all unmatched records from both tables.” Use outer merge with indicator=True, then filter for left_only and right_only.
Find Unmatched Records
| id | first_name | last_name | city | address | phone_number |
|---|---|---|---|---|---|
| 8 | John | Joseph | San Francisco | 928-386-8164 | |
| 7 | Jill | Michael | Austin | 813-297-0692 | |
| 4 | William | Daniel | Denver | 813-368-1200 | |
| 5 | Henry | Jackson | Miami | 808-601-7513 | |
| 13 | Emma | Isaac | Miami | 808-690-5201 |
| id | cust_id | order_date | order_details | total_order_cost |
|---|---|---|---|---|
| 1 | 3 | 2019-03-04 | Coat | 100 |
| 2 | 3 | 2019-03-01 | Shoes | 80 |
| 3 | 3 | 2019-03-07 | Skirt | 30 |
| 4 | 7 | 2019-02-01 | Coat | 25 |
| 5 | 7 | 2019-03-10 | Shoes | 80 |
Outer merge customers with orders using the indicator, then filter to rows that exist in only one table.
Key Takeaways
how="outer"returns all rows from both DataFrames.indicator=Truelabels each row asboth,left_only, orright_only.- Use it for data reconciliation and gap analysis.
What’s Next
All the merges so far require matching keys. But what if you want every possible combination? That’s cross merge.