Connect records to a lookup · Data Playground

The question

Attach depot managers without losing deliveries that have no matching assignment.

df + lookup Assigned & unassigned

Your inputs

`df`

Deliveries may repeat depot keys. lookup has exactly one row per depot; unmatched deliveries must remain visible.

Field meanings and units

delivery_id: Delivery identifier.
depot: Depot responsible for the delivery.
status: Recorded delivery state.
minutes: Elapsed duration in minutes; unknown is not zero.
weight_kg: Weight in kilograms.
date: Calendar date supplied as YYYY-MM-DD text unless the task states it is invalid.

`df` · first 5 of 16 rows
`delivery_id`	`depot`	`status`	`minutes`	`weight_kg`	`date`
D001	East	Delivered	32	1.2	2026-04-01
D002	West	Delivered	48	1.9	2026-04-01
D003	North	Pending	25	2.6	2026-04-02
D004	East	Returned	61	3.3	2026-04-02
D005	South	Delivered	45	4	2026-04-03

View all 16 rows

`df` · complete input
`delivery_id`	`depot`	`status`	`minutes`	`weight_kg`	`date`
D001	East	Delivered	32	1.2	2026-04-01
D002	West	Delivered	48	1.9	2026-04-01
D003	North	Pending	25	2.6	2026-04-02
D004	East	Returned	61	3.3	2026-04-02
D005	South	Delivered	45	4	2026-04-03
D006	North	Delivered	37	1.2	2026-04-03
D007	East	Pending	72	1.9	2026-04-04
D008	West	Returned	29	2.6	2026-04-04
D009	North	Delivered	33	3.3	2026-04-05
D010	East	Delivered	49	4	2026-04-05
D011	West	Pending	26	1.2	2026-04-06
D012	North	Returned	62	1.9	2026-04-06
D013	East	Delivered	46	2.6	2026-04-07
D014	West	Delivered	38	3.3	2026-04-07
D015	North	Pending	73	4	2026-04-08
D016	East	Returned	30	1.2	2026-04-08

`lookup`

One unique depot key per manager assignment.

Field meanings and units

depot: Depot responsible for the delivery.
manager: Assigned depot manager.

lookup · first 3 of 3 rows
`depot`	`manager`
East	Ari
West	Bo
North	Cai

The editable setup in Your Python creates these inputs when you run it.

Your task · Connect records to a lookup

Attach depot managers without losing deliveries that have no matching assignment.

matchedDataFrameunmatchedDataFrame

Deliverables

Make the outputs listed below. Use each name exactly as shown. Then choose Check answer.

Python name key

Teal outlines name table columns. Violet left rules name Python objects. Dotted rules mark syntax, functions and parameters. Use each name exactly as shown.

Assigned deliveriesOutputmatchedDataFrame
1. Create matched with all original delivery columns plus manager, for rows with a lookup match.
2. Keep source delivery order.
Unassigned deliveriesOutputunmatchedDataFrame
1. Create unmatched with the same columns for deliveries with no lookup match, keeping source order.

Data policy Keep all supplied source tables unchanged. Work on separate outputs.

Help Hints & explained solution

Choose the amount of help you need. Opening one hint does not reveal the others.

Hint 1 — Think

Which records must remain visible when no assignment exists?

Hint 2 — Tools

merge(), isna(), notna() and duplicated()

Revisit: Combine tables by keys · Find duplicate rows · Find missing values

Hint 3 — Approach

Keep every delivery while attaching its assignment, then separate matched and unmatched records.

Explained solution

Add these lines below # Your work in the editor. The supplied input setup is already there.

joined = df.merge(lookup, on='depot', how='left', validate='many_to_one', sort=False)
matched = joined[joined.manager.notna()]
unmatched = joined[joined.manager.isna()]
matched

View supplied setup

This code is already at the top of the editor.

import pandas as pd

# Supplied input: df (16 rows)
df = pd.DataFrame(
    [
        ('D001', 'East', 'Delivered', 32, 1.2, '2026-04-01'),
        ('D002', 'West', 'Delivered', 48, 1.9, '2026-04-01'),
        ('D003', 'North', 'Pending', 25, 2.6, '2026-04-02'),
        ('D004', 'East', 'Returned', 61, 3.3, '2026-04-02'),
        ('D005', 'South', 'Delivered', 45, 4, '2026-04-03'),
        ('D006', 'North', 'Delivered', 37, 1.2, '2026-04-03'),
        ('D007', 'East', 'Pending', 72, 1.9, '2026-04-04'),
        ('D008', 'West', 'Returned', 29, 2.6, '2026-04-04'),
        ('D009', 'North', 'Delivered', 33, 3.3, '2026-04-05'),
        ('D010', 'East', 'Delivered', 49, 4, '2026-04-05'),
        ('D011', 'West', 'Pending', 26, 1.2, '2026-04-06'),
        ('D012', 'North', 'Returned', 62, 1.9, '2026-04-06'),
        ('D013', 'East', 'Delivered', 46, 2.6, '2026-04-07'),
        ('D014', 'West', 'Delivered', 38, 3.3, '2026-04-07'),
        ('D015', 'North', 'Pending', 73, 4, '2026-04-08'),
        ('D016', 'East', 'Returned', 30, 1.2, '2026-04-08'),
    ],
    columns=['delivery_id', 'depot', 'status', 'minutes', 'weight_kg', 'date'],
)

# Supplied input: lookup (3 rows)
lookup = pd.DataFrame(
    [
        ('East', 'Ari'),
        ('West', 'Bo'),
        ('North', 'Cai'),
    ],
    columns=['depot', 'manager'],
)

Why these steps exist

Retaining the delivery side preserves unassigned deliveries as an explicit repair list.
Checking lookup uniqueness prevents a single delivery from multiplying into several output rows.

Another valid approach

Equivalent key-based attachment is accepted. Preserve unmatched deliveries and avoid multiplying rows when connecting the lookup.