This lesson's interactive features are locked, please to keep using them
Finding Unmatched Records
Merging may reveal that some rows don't match. It's important to identify those records before they degrade the analysis. One indication of match failures is the presence of NaN values in the merged DataFrame. In a left join, you can check a right-side column that is guaranteed to contain a value for every valid match:
sales = pd.DataFrame(
{
"product_id": ["CHL-482", "TST-731", "OVN-205", "BRW-619"],
"quantity": [10, 20, 15, 25],
}
)
# BRW-619 is missing!
products = pd.DataFrame(
{
"product_id": ["CHL-482", "TST-731", "OVN-205"],
"name": ["ChillVault Mini", "ToastForge Pro", "CrispWave Oven"],
}
)
merged = pd.merge(sales, products, on="product_id", how="left")
# Find sales with no matching product
unmatched = merged[merged["name"].isna()]
print(unmatched)
print(f"{len(unmatched)} sales have no matching product")
# 1 sales have no matching product
print(unmatched["product_id"].unique())
# ['BRW-619']
This quickly shows us there's a single product ID not found in the products table. It's a data quality issue that we can investigate further.
Merge Indicator
Checking a guaranteed non-null column works in a pinch, but pd.merge() also has a tool designed specifically for auditing matches: indicator=True.
merged = pd.merge(sales, products, on="product_id", how="left", indicator=True)
print(merged)
# product_id quantity name _merge
# 0 CHL-482 10 ChillVault Mini both
# 1 TST-731 20 ToastForge Pro both
# 2 OVN-205 15 CrispWave Oven both
# 3 BRW-619 25 NaN left_only
The new _merge column shows where each row came from: both, left_only, or right_only. For a left join, unmatched records are the left_only rows:
unmatched = merged[merged["_merge"] == "left_only"]
print(unmatched["product_id"].unique())
# ['BRW-619']
Assignment
SnackStack's data engineering team found sales and product records that don't match. Now they need to identify exactly which records failed so they can fix the source data.
Complete the find_unmatched function. It accepts two DataFrames: sales and products. It should find records that don't have a match in the other table.
-
"unmatched_sales""unmatched_products"