Why tables need each other
Real data is usually split across tables. Orders know customer_id but not the customer's name; a customer table knows the name but not the orders. A merge brings both sets of columns together using the shared key.
Python
A left merge keeps every order
Output
Press Run to execute this cell. The first run downloads Python; later runs reuse the session.
Order 13 points at customer 4, which is not in customers. The left join keeps the order and fills the missing name with NaN. That missing value is a finding, not a formatting problem.
Choose how rows survive
The how argument decides which unmatched rows survive. An inner join keeps only matches, a left join keeps every row from the left frame, a right join keeps every row from the right frame, and an outer join keeps both sides.
| how | Keeps |
|---|---|
| inner | Rows with a matching key on both sides |
| left | Every row from the left frame, plus matches from the right |
| right | Every row from the right frame, plus matches from the left |
| outer | All rows from both frames |
Python
An inner join drops the unmatched order
Output
Press Run to execute this cell. The first run downloads Python; later runs reuse the session.
Validate the relationship
Merge accepts a validate argument that fails when the actual key relationship is not the one you expected. This turns a silent row explosion into a clear error.
Python
Assert the relationship and expose unmatched rows
Output
Press Run to execute this cell. The first run downloads Python; later runs reuse the session.
- many_to_one means many orders may share one customer, but each customer key is unique.
- one_to_one requires uniqueness on both sides.
- one_to_many is the reverse of many_to_one.
- indicator=True adds a _merge column that says both, left_only, or right_only.
Stack tables with concat
Use concat when the tables already have the same columns and you simply need more rows. It does not look for a key. Use merge when the tables need to be aligned on a shared key.
Python
Stack two frames and renumber the rows
Output
Press Run to execute this cell. The first run downloads Python; later runs reuse the session.
A join worth trusting
Before joining: identify the key and its expected relationship. After joining: compare row counts, inspect unmatched rows, and decide how missing values should be represented. The merge itself is the easy part.
Python
Count matches and misses
Output
Press Run to execute this cell. The first run downloads Python; later runs reuse the session.
value_counts on the merge indicator is a quick audit: every left_only row is an order whose customer is missing. The next lesson cleans missing values and restructures the table for analysis.
Practice
Make it stick
Run the tests as often as you like. Hints and the solution are available when you need them.
Exercise
Left-merge orders with customers on customer_id so every order survives. Assign the result to merged, then replace missing names with "Unknown".
Your solution
ReadyResults
Run the tests to check your solution. The first run downloads Python; later runs reuse the session.
Quiz
Check your understanding
Lesson progress