Skip to content
LearnPY
pandas

Join related tables without losing rows

Merge DataFrames on a shared key, choose how unmatched rows survive, validate the relationship, and stack tables with concat.

11 min readbeginnerpandas

By the end

  • Merge two frames on a shared key.
  • Choose the join direction that keeps the rows you need.
  • Validate a many-to-one relationship.
  • Use the merge indicator to inspect unmatched rows.
  • Stack rows with concat when no key is involved.

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

Ready
Loading editor…

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.

howKeeps
innerRows with a matching key on both sides
leftEvery row from the left frame, plus matches from the right
rightEvery row from the right frame, plus matches from the left
outerAll rows from both frames
Merge directions

Python

An inner join drops the unmatched order

Ready
Loading editor…

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

Ready
Loading editor…

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

Ready
Loading editor…

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

Ready
Loading editor…

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

Ready
Loading editor…

Results

Run the tests to check your solution. The first run downloads Python; later runs reuse the session.

Quiz

Check your understanding

01Which join keeps every row from the left frame?
02Orders may share customer_id, but each customer_id is unique in customers. Which validation is correct?
03When should you use concat instead of merge?

Lesson progress