Outer Merge
Progress Tracking
Log in to save this lesson and continue from where you left off.
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.