Merging and Joining DataFrames

Concept

Real data is often split across tables. A customer table may hold customer attributes, while an orders table holds transactions. A merge combines them using a shared key, such as customer_id. The join type determines which unmatched rows remain.

Why It Matters

Joining tables makes richer analysis possible: revenue by customer region, orders by campaign, or model features built from several systems. A careless merge can also multiply rows silently and inflate totals, so joining is both an analysis task and a data-quality task.

Real-World / Data Science Example

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [101, 102, 103],
    "name": ["Asha", "Ben", "Chen"],
    "region": ["North", "South", "North"],
})
orders = pd.DataFrame({
    "order_id": [1, 2, 3],
    "customer_id": [101, 101, 102],
    "revenue": [120, 220, 80],
})

customer_orders = pd.merge(customers, orders, on="customer_id", how="left")
print(customer_orders)

The left join keeps every customer. Asha appears twice because she has two orders; Chen remains with missing order fields because no matching order exists. This is correct for a customer-to-orders relationship, but it changes the output to one row per customer-order combination.

Join Types

• Inner join keeps only keys found in both tables.
• Left join keeps every row from the left table and matching values from the right.
• Right join keeps every row from the right table.
• Outer join keeps every row from both tables and exposes unmatched keys.

Left joins are common when the left table defines the population you want to analyze, such as all active customers. The correct join is a business decision, not a default syntax choice.

Keys and Relationship Shape

A one-to-one merge has one matching row on each side for every key. A one-to-many merge has one customer and many orders. Many-to-many joins deserve special caution: duplicate keys on both sides can create every combination of matches.

customer_orders = pd.merge(
    customers,
    orders,
    on="customer_id",
    how="left",
    validate="one_to_many",
)

validate asks Pandas to check the relationship you expect. It is an early warning when a supposed unique customer table contains duplicate IDs.

Duplicate Column Names and Suffixes

When both tables have a non-key column with the same name, use suffixes so the result stays clear.

merged = pd.merge(
    customers,
    orders,
    on="customer_id",
    how="left",
    suffixes=("_customer", "_order"),
)

Code Explanation

pd.merge(left, right, on=..., how=...) aligns records with equal key values. It does not aggregate orders automatically. If the analysis needs one row per customer, group orders to customer level first, then merge that summary onto customers.

Checking a Merge Result

Treat a merge like a testable assumption. Record row counts before and after it, inspect key dtypes on both sides, and review unmatched keys. For a customer-level analysis, summarize orders first so the right table has one row per customer:

customer_revenue = orders.groupby("customer_id", as_index=False)["revenue"].sum()
customer_summary = pd.merge(
    customers,
    customer_revenue,
    on="customer_id",
    how="left",
    validate="one_to_one",
)

This preserves one row per customer. If the row count grows unexpectedly, check duplicate keys before calculating totals. An outer join can help expose keys present in only one source; a right join is useful when the right table defines the population, though that is less common in a customer-master analysis.

When to Use It

Merge when related tables share a trustworthy key. Check key types first: integer 101 and string "101" do not match cleanly. Use a merge indicator or unmatched-key review when data sources are new or uncertain.

Best Practices

Check row counts before and after a merge, inspect key uniqueness, and state the expected relationship with validate when possible. Keep IDs in the result for tracing. After a merge, inspect missing right-side fields to understand unmatched records rather than immediately dropping them.

Data Science Perspective

Merging is how customer attributes, transaction history, campaign exposure, and support activity become one analytical dataset. It supports feature engineering and business analytics, but correct join granularity is essential before a model or metric can be trusted.

Interview Perspective

Question: Why is a left join often useful in customer analysis? A strong answer: it preserves the defined customer population while attaching matching activity, making customers without activity visible rather than silently removing them.

Practice Questions

1. Merge products and sales on product_id using a left join.
2. Explain why a customer with three orders produces three rows after a customer-to-orders merge.
3. What checks would you make if total revenue doubles after a merge?
4. Why should customer_id have compatible dtypes in both tables?

Quick Quiz

1. Which join keeps only matching keys? Answer: inner join.
2. What does validate="one_to_many" check? Answer: unique keys on the left with repeated matches allowed on the right.
3. Why check row counts after a merge? Answer: to detect unexpected duplication or dropped records.
4. Which join can expose unmatched keys from both tables? Answer: outer join.

Next Lesson

You now have the initial Pandas tools needed to inspect, filter, clean, summarize, and combine real tabular data.