Combine tables by keys

Groups & reshaping · W24 · 8 MIN

Join a lookup table while checking the relationship.

Exercises within this concept

FollowFollow the techniqueCurrent exercise
ChangeKeep matches only
TransferChoose and combine

Understand the idea

merge attaches fields by matching a key. The join type decides what happens to records without a match.

Illustration · not the exercise output

A small example

If left keys are A, B and lookup has only A: a left join keeps A and B; an inner join keeps only A.

Follow the code

Apply the idea to the supplied table. Read from top to bottom; the final line displays the result.

df.merge(lookup, on="flavour", how="left", validate="many_to_one")

What each part does

on="flavour": Match rows on equal flavour values in both tables.
how="left": Keep every df row; unmatched lookup values become missing.
validate="many_to_one": Allow repeated flavours in df but require each flavour to appear only once in lookup.

Other choices for later exercises

how="inner": Keep only rows with matches on both sides instead.

Your inputs

The editable setup on the right creates df and the additional inputs shown below. Run executes the setup and your work from top to bottom.

Candy shop · 6 synthetic rows
candy	flavour	price	rating	shelf
Gummy Bear	fruity	1.2	4.1	A
Choco Pop	chocolate	2.1	4.6	B
Mint Bite	mint	1.5	3.8	A
Berry Loop	fruity	2.8	4.4	B
Cocoa Cube	chocolate	3.4	4.9	A
Lemon Drop	fruity	1.8	4	B

lookup · given lookup
flavour	priority
fruity	1
chocolate	2

Your task · Follow

Left-join lookup onto df on flavour; retain every df row and use validate="many_to_one".
Return the joined DataFrame.
Use: merge().

Hint

A left join retains unmatched observations; validate checks that the lookup key is unique.

Reveal solution

One way to do it. Keep any supplied setup in the editor and use this in the Your work section.

df.merge(lookup, on="flavour", how="left", validate="many_to_one")

Your task · Follow

Left-join lookup onto df on flavour; retain every df row and use validate="many_to_one".
Return the joined DataFrame.
Use: merge().

YOUR PYTHON

Tab: indent · Shift+Tab: outdent · Esc, then Tab: leave editor

# Supplied setup — editable
import pandas as pd

data = {
    "candy": [
        "Gummy Bear",
        "Choco Pop",
        "Mint Bite",
        "Berry Loop",
        "Cocoa Cube",
        "Lemon Drop",
    ],
    "flavour": ["fruity", "chocolate", "mint", "fruity", "chocolate", "fruity"],
    "price": [1.2, 2.1, 1.5, 2.8, 3.4, 1.8],
    "rating": [4.1, 4.6, 3.8, 4.4, 4.9, 4],
    "shelf": ["A", "B", "A", "B", "A", "B"],
}

df = pd.DataFrame(data)

lookup = pd.DataFrame(
    {
        "flavour": ["fruity", "chocolate"],
        "priority": [1, 2],
    }
)

# Your work
df.merge(lookup, on=____, how=____, validate=____)

Each run executes all editor code in a fresh Python session. Display a value by leaving it on the final line.

Python starts when you open a lesson.

Output

Run your code to see what Python returns.