We're sorry but this app doesn't work properly without JavaScript enabled. Please enable it to continue.

This lesson's interactive features are locked, please to keep using them

Inner and Left Joins

Merging combines DataFrames by matching values in one or more key columns.

pd.merge(left_df, right_df, on="column_name", how="join_type")

This pd.merge() call has four key parameters:

  • left_df: the first DataFrame
  • right_df: the second DataFrame
  • on: the shared column, also called the key
  • how: what kind of join to perform ('inner', 'left', 'right', 'outer')

For example, these two DataFrames share a product_id key:

sales = pd.DataFrame(
    {"product_id": ["CHL-482", "TST-731", "OVN-205"], "quantity": [10, 20, 30]}
)

products = pd.DataFrame(
    {
        "product_id": ["CHL-482", "TST-731", "BRW-619"],
        "name": ["ChillVault Mini", "ToastForge Pro", "BrewPilot"],
    }
)

And only the rows with product_id CHL-482 and TST-731 exist in both DataFrames.

Inner Join

An "inner" join only keeps rows where the key exists in both DataFrames.

result = pd.merge(sales, products, on="product_id", how="inner")
print(result)
#   product_id  quantity             name
# 0    CHL-482        10  ChillVault Mini
# 1    TST-731        20   ToastForge Pro

Use inner joins when: you only want complete matches. If a sale doesn't have a matching product, exclude it.

Left Join

A "left" join keeps all rows from the left DataFrame, and adds matching rows from the right DataFrame. If there is no match, the right-side columns are filled with NaN.

result = pd.merge(sales, products, on="product_id", how="left")
print(result)
#   product_id  quantity             name
# 0    CHL-482        10  ChillVault Mini
# 1    TST-731        20   ToastForge Pro
# 2    OVN-205        30              NaN

This time, all three sales survive the merge. Product OVN-205 has no matching product data, so its name is NaN.

Use left joins when: your left table is the source of truth. This is the most common join in analytics. For example, you may have transactions on the left, and you want to enrich them with user data on the right.

Left joins can hide data quality issues. If your right table has duplicate keys, one left-side row can turn into many rows after the merge. Always check the row count before and after!

Assignment

SnackStack's support team needs a report of every device with its latest temperature reading and firmware version. The device registry is the source of truth, but the temperature and firmware details live in separate tables.

Complete the enrich_device_data function. It accepts three DataFrames: devices, telemetry, and firmware.