Data Analysis for Engineers/Module 2

Module 2 · Section 10 of 12

Lesson 2.9 - Merging & Joining

Target: ~9 min read - 20 min hands-on

Overview

merge() combines two DataFrames on a shared key column (like a SQL join); concat() stacks DataFrames on top of each other or side by side. We'll cover inner (only matching rows), left (all rows from the left table), and outer (all rows from either) by combining the site list with a service-contract table keyed by sector.

Why This Matters (Engineering Context)

Combining an asset or site register with a maintenance-contract table, a supplier list, or a cost-center map by a shared key is a routine step - no single source system holds everything a report needs.

Code-Along

# --- A second table to join against: service contracts, keyed by sector ---
service_contracts = pd.DataFrame({
    "sector":   ["Chemical", "Semiconductor", "Data Center", "Logistics"],
    "provider": ["ProChem Services", "FabCare", "UptimeOps", "MoveRight"],
    "sla_hours": [8, 4, 2, 24],
})

# merge() joins two DataFrames on a shared key column (on="sector"). how= sets
# which rows survive when a key exists in only one of the two tables.

# how="inner": keep only sectors present in BOTH tables (the intersection).
inner = site_summary.merge(service_contracts, on="sector", how="inner")
print("Inner join rows:", len(inner))
print(inner[["site", "sector", "provider"]])

# how="left": keep every row of the LEFT table (site_summary); provider is NaN
# for any site whose sector has no matching contract row.
left = site_summary.merge(service_contracts, on="sector", how="left")
print("\nLeft join rows:", len(left), "(NaN provider where a sector has no contract)")
print(left[["site", "sector", "provider"]])

# how="outer": keep every row from BOTH tables (the union); NaN wherever one
# side had no match.
outer = site_summary.merge(service_contracts, on="sector", how="outer")
print("\nOuter join rows:", len(outer))

# --- concat() vs merge(): concat stacks frames, it does NOT match on a key ---

# Two yearly snapshots of an asset-condition register with identical columns...
reg_2022 = pd.DataFrame({"asset_id": ["P-01", "P-02"], "year": 2022, "condition_index": [0.92, 0.78]})
reg_2023 = pd.DataFrame({"asset_id": ["P-01", "P-02"], "year": 2023, "condition_index": [0.89, 0.74]})
# ...stacked on top of each other (axis=0, the default). ignore_index=True
# renumbers the combined rows 0..3 instead of keeping the original 0,1,0,1.
reg_combined = pd.concat([reg_2022, reg_2023], ignore_index=True)
reg_combined

Run it: site_summary has sectors Chemical, Semiconductor, Data Center, Manufacturing, R&D; service_contracts covers Chemical, Semiconductor, Data Center, Logistics. So the inner join keeps 3 rows (Manufacturing and R&D drop - no contract); the left join keeps all 5 sites with NaN provider for Manufacturing and R&D; the outer join has 6 rows - it also picks up "Logistics" from the contract table, which has no matching site, with NaN in the site columns. reg_combined shows 4 rows: 2 assets x 2 years, stacked.

Practice Exercises

  1. The outer join has more rows than the inner/left joins. Which sector(s) appear only in the outer join, and why?
  2. Add a new site in sector "Logistics" to site_summary, then re-run the left join and confirm it now picks up the MoveRight contract.
  3. Use pd.concat() with axis=1 to join reg_2022 and a differently-named-column DataFrame side by side (not stacked) - describe what changes.
# Try the practice exercises here

Knowledge Check

  1. What's the difference between merge() and concat()?
  2. Which join type keeps only rows with matching keys in both tables?
  3. Which join type keeps every row from both tables, filling gaps with NaN?
Answer key
  1. merge() combines on shared key column(s) like a SQL join; concat() stacks along rows or columns without a shared key
  2. inner
  3. outer

20 / 63 sections · Course home · Join the coaching cohort