This lesson's interactive features are locked, please to keep using them
Merging on Composite Keys
A more nuanced problem is that sometimes a single column isn't enough to uniquely identify a row. In such cases, we need a composite key, i.e., a key made from two or more columns.
Say we have daily sales data organized by store:
sales = pd.DataFrame(
{
"store": ["Seattle", "Seattle", "Portland"],
"date": ["2024-01-01", "2024-01-02", "2024-01-01"],
"sales": [100, 150, 200],
}
)
targets = pd.DataFrame(
{
"store": ["Seattle", "Seattle"],
"date": ["2024-01-01", "2024-01-02"],
"target": [120, 130],
}
)
If we merge only on store, we'll get an invalid result:
bad_result = pd.merge(sales, targets, on="store", how="left")
print(bad_result)
# store date_x sales date_y target
# 0 Seattle 2024-01-01 100 2024-01-01 120.0
# 1 Seattle 2024-01-01 100 2024-01-02 130.0
# 2 Seattle 2024-01-02 150 2024-01-01 120.0
# 3 Seattle 2024-01-02 150 2024-01-02 130.0
# 4 Portland 2024-01-01 200 NaN NaN
Seattle matched every Seattle row in targets, even when the dates didn't match! So we have 4 rows for Seattle instead of 2, with messy duplicated date columns. The problem is that we need to look at both store and date to uniquely identify the correct target for each sales record.
Multiple Key Columns
pd.merge() supports composite keys by allowing you to specify multiple columns in the on parameter:
result = pd.merge(sales, targets, on=["store", "date"], how="left")
print(result)
# store date sales target
# 0 Seattle 2024-01-01 100 120.0
# 1 Seattle 2024-01-02 150 130.0
# 2 Portland 2024-01-01 200 NaN
Now rows are merged only when store and date both match. Seattle has one target for each date, while Portland never appears in targets, so its target for 2024-01-01 is NaN.
Assignment
SnackStack's logistics team needs a report matching orders to shipments. A single customer_id isn't enough because customers can place multiple orders, so the merge key is customer_id plus order_date. The shipment export can also contain repeated records, which would duplicate orders in the report.
Complete the merge_with_composite_key function. It accepts two DataFrames: orders and shipments.