Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 64 ยท Combining Data

Merge and Join

Match rows from two DataFrames by key with merge and join. Learn inner, left, right and outer joins, suffixes, validate, indicator, and how to avoid row explosions.

Data in the real world is split across tables. Customers live in one table, their orders in another, and the products in a third. To answer a question such as "how much did each city spend?" you have to bring the tables together by matching rows that share a key, in this case the customer id. In SQL this operation is called a join. In pandas it is DataFrame.merge() (and the closely related DataFrame.join()).

Unlike concat, which glues pieces by position or label, merge looks at values: a row from the left table is paired with every row of the right table that has the same key value. Most of the skill is in knowing which rows survive, and checking the row count afterwards. If you know SQL, merge maps directly onto JOIN (SQL joins explain the same idea).

Two small tables

The examples use four customers and five orders. Customer 4 (Dev) has no orders, and order 104 belongs to customer 5, who is not in the customers table.

flowchart LR
    subgraph L["customers"]
      c1["1 Ana"]
      c2["2 Ben"]
      c3["3 Cleo"]
      c4["4 Dev"]
    end
    subgraph R["orders"]
      o1["101, 102 (cust 1)"]
      o2["105 (cust 2)"]
      o3["103 (cust 3)"]
      o5["104 (cust 5)"]
    end
    c1 --- o1
    c2 --- o2
    c3 --- o3

Customer 4 has no match in orders; order 104 (customer 5) has no match in customers. The join type decides what happens to them.

The four join types

left.merge(right, on="key", how=...) takes a how argument:

how Rows in the result
"inner" (default) Only keys found in both tables
"left" All rows of the left table; right columns are NaN where nothing matches
"right" All rows of the right table
"outer" All rows from both tables

If a key matches several rows on the other side, the row is repeated once per match. Ana has two orders, so she appears twice.

Python Inner, left, right and outer joins

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Compare the outputs. inner keeps four rows (customer 4 and order 104 vanish). left keeps all customers, so Dev appears with NaN in the order columns. right keeps all orders, so order 104 appears with NaN for the name and city. outer keeps everything. In practice left is the most common: "start from the table I care about and attach extra information, losing nothing".

Notice also that order_id and amount turned into floats in the left and outer results. An integer column cannot hold NaN, so the gaps force a float. If that matters, convert back with astype("Int64") after dealing with the missing values.

Choosing the key

When both tables use the same column name, on="customer_id" is enough (a list means several keys: on=["region", "year"], and both must match). When the names differ, use left_on and right_on. The result then contains both key columns, and you can drop one.

If the tables share other column names that are not keys, pandas cannot keep two columns with the same name, so it adds suffixes. The default is _x for the left and _y for the right, which tells the reader nothing. Pass suffixes=("_old", "_new") for meaningful names, or rename the columns first.

To merge on an index, set left_index=True and/or right_index=True. DataFrame.join() is a shortcut: it joins the left frame's column (via on=) or index to the right frame's index, and its default is how="left".

Python Different key names, suffixes and join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Count your rows

The most dangerous merge bugs do not raise errors: they quietly produce too many or too few rows. A many-to-many match is the typical cause. If the key is repeated on both sides, every left row pairs with every right row having the same key: two rows times two rows gives four.

Suppose a customer table accidentally contains Ana twice (a duplicate that nobody noticed). Merging orders onto it duplicates all of Ana's orders, and your revenue total is wrong, yet nothing fails.

Three habits catch this:

  1. Compare row counts before and after. For a left merge onto a lookup table, the result must have the same number of rows as the left table.
  2. validate= makes pandas check the key relationship and raise a MergeError if it is violated: "one_to_one", "one_to_many", "many_to_one" or "many_to_many" (the last one checks nothing).
  3. indicator=True adds a _merge column with left_only, right_only or both for each row, so you can see what did not match. Pass a string to name the column.
Python validate and indicator

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With validate="one_to_many", pandas checks that the left keys are unique, which the duplicate violates. Use "many_to_one" when the unique side is on the right (the typical order-to-product lookup) and "one_to_one" when both must be unique.

The _merge column also makes an anti-join easy, which you will use in Advanced Merges: filter for left_only to list the rows without a match.

Missing keys

Rows whose key is NaN match each other in merge (unlike in SQL, where NULL never equals NULL). If your key column contains missing values, drop or fill them first, or you may get spurious matches.

Also make sure the key columns have the same dtype on both sides. Merging an integer column onto a string column (1 versus "1") raises an error or matches nothing, depending on the case. Fix it with astype first. See Data Types and Conversion.

A checklist before trusting a merge

  • Are the keys unique where you expect them to be? (df["key"].is_unique)
  • Do both key columns have the same dtype and the same cleaning (whitespace, case)?
  • Do you know the expected row count? Assert it.
  • Did you pick the how that matches your question?
  • Did unwanted suffixes appear? Rename before the merge.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. customers has 4 rows and orders has 5. What does customers.merge(orders, on="customer_id", how="left") always keep?

    A left join keeps every left row. A customer with several orders appears several times, and a customer without orders appears once with NaN in the order columns. So the result can have more than 4 rows.
  2. 2. Which argument shows you which rows found no match, as left_only, right_only or both?

    indicator=True adds a _merge column. how="outer" keeps unmatched rows but does not label them.
  3. 3. A key appears twice on both sides. How many rows does the merge produce for that key?

    Every left row pairs with every right row sharing the key: 2 x 2 = 4. This many-to-many explosion is a common source of inflated totals.
  4. 4. What does validate="many_to_one" check?

    many_to_one means many left rows point to one right row, so the right keys must be unique. pandas raises a MergeError if they are not.
  5. 5. Both tables have a non-key column named price. What does merge do without extra arguments?

    The default suffixes are _x and _y. Pass suffixes=("_old", "_new") for readable names.

Summary

  • left.merge(right, on=key, how=...) matches rows by key values; inner (default), left, right and outer decide which unmatched rows survive.
  • Use left_on/right_on for differently named keys, left_index/right_index or join for index keys, and suffixes to name clashing columns.
  • A key repeated on both sides multiplies rows. Compare row counts before and after.
  • validate= raises an error when the key relationship is not what you expected; indicator=True labels unmatched rows.
  • Matching keys need the same dtype and clean values; NaN keys match each other.

Next: Advanced Merges