Merging & Joining

Understanding Merging & Joining

Merging combines records by matching key values, such as customer identifiers. Concatenation combines tables along an axis: usually appending rows from similarly structured datasets. The important distinction is whether records should be connected by identity or simply collected together. Neither operation automatically checks that the resulting table answers your analytical question.

Start by describing each table’s grain: what one row represents. A customer table might contain one row per customer, while an order table contains one row per order. Joining customers to orders creates customer–order rows, not one row per customer. This matters when computing averages: customers with several orders appear several times and therefore receive more weight in calculations over joined rows.

With `merge`, specify the key explicitly using `on='customer_id'`. If key names differ, use `left_on` and `right_on`; multiple columns can form a composite key. Without explicit keys, a merge normally uses all shared column names. Its default join type is inner, retaining matching keys only. A left join retains every left-side record; a right join retains every right-side record. An outer join retains keys from both sides. Unmatched records receive missing values for the other table’s columns.

Join type controls which records survive, while cardinality describes how many records can match. In a one-to-many relationship, one customer can match several orders. If a key occurs twice on one side and three times on the other, that key produces six matching rows. Use `validate` to enforce expected uniqueness, and `indicator=True` to identify matched and unmatched records. Validation checks structure, not whether identifiers refer to the correct real-world entities.

Missing keys require special care: pandas can match null keys to other null keys, unlike ordinary SQL equality joins. Reject or separate missing identifiers when that matching would be misleading. For concatenation, `pd.concat([a, b], ignore_index=True)` stacks rows and replaces their index labels; it does not remove duplicates. Columns align by name, with missing values where fields are absent. Horizontal concatenation with `axis=1` aligns index labels rather than matching a customer column. Choose it only when those labels genuinely identify corresponding observations. The pandas user guide treats merging and concatenation as distinct tools for these different tasks.

Practical walkthrough

1. Define the result before joining. Suppose the question is: “What is recorded spending per registered customer?” The desired grain is one row per registered customer, including customers without orders. Assume the order extract covers the intended reporting period, each order appears once, and amounts use the same currency. These assumptions cannot be established by merge syntax.

2. Inspect the inputs. Check customer-key uniqueness with `is_unique`, missing keys with `isna()`, and duplicate order identifiers with `duplicated()`. Confirm that key types and formatting agree. An integer identifier and a text identifier need deliberate harmonization; converting codes to numbers may destroy meaningful leading zeros.

3. Aggregate orders to the target grain. Group by customer identifier and sum amounts before joining. This prevents repeated customer rows in the final report. The trade-off is that individual order details disappear from that summary, so retain the original order table for order-level questions.

4. Merge from customers using a left join and `validate='one_to_one'`. Both inputs should now have unique customer keys. Request an indicator column so unmatched customers remain visible. Separately inspect orders whose customer identifiers are absent from the customer table: a left join cannot display those excluded records.

5. Interpret missing results before replacing them. Here, an unmatched customer receives zero recorded spending because the extract is assumed complete. That does not prove the customer never purchased elsewhere or outside the period. If completeness is uncertain, leave spending unknown. Finally, verify the row count, customer uniqueness, excluded-order count, and spending total before calculating the customer-level average.

Worked example

import pandas as pd

customers = pd.DataFrame({
    'customer_id': [1, 2, 3],
    'name': ['Ada', 'Ben', 'Cy']
})
orders = pd.DataFrame({
    'order_id': [101, 102, 103, 104],
    'customer_id': [1, 1, 2, 4],
    'amount': [10, 20, 15, 7]
})

assert customers['customer_id'].is_unique
assert orders['order_id'].is_unique
assert customers['customer_id'].notna().all()
assert orders['customer_id'].notna().all()
assert orders['amount'].notna().all()

totals = orders.groupby('customer_id', as_index=False)['amount'].sum()
report = customers.merge(
    totals, on='customer_id', how='left',
    validate='one_to_one', indicator=True
).sort_values('customer_id')

unmatched = report['_merge'].eq('left_only')
report.loc[unmatched, 'amount'] = 0
excluded = ~orders['customer_id'].isin(customers['customer_id'])

assert len(report) == len(customers)
assert report['customer_id'].is_unique

for row in report.itertuples(index=False):
    print(f'{row.name}: {row.amount:.2f}')
print(f'Customers without matched orders: {int(unmatched.sum())}')
print(f'Orders outside customer list: {int(excluded.sum())}')
print(f'Mean recorded spending: {report["amount"].mean():.2f}')

Expected output

Ada: 30.00
Ben: 15.00
Cy: 0.00
Customers without matched orders: 1
Orders outside customer list: 1
Mean recorded spending: 15.00

Run this example in Python with pandas installed; it needs no files. Ada’s two orders become one total before the merge. Ben contributes 15, while Cy remains present despite having no matching order. The mean is therefore 45 divided by three registered customers, or 15.00. Customer identifier 4 contributes an order worth 7, but is outside the reporting population and is explicitly counted as excluded. Using an inner join would omit Cy and produce a different denominator. The indicator distinguishes an unmatched customer from a matched customer whose recorded total is genuinely zero, although both would display zero spending here.

Common mistakes and how to avoid them

• Assuming a left join preserves the left table’s row count.

  It preserves left records, not their multiplicity. Inspect right-key duplicates and enforce `many_to_one` when enriching many records from a unique lookup table.

• Removing duplicate keys without investigating their meaning.

  Repeated customer identifiers in orders are legitimate. Aggregate to the required grain, or add the missing key component; do not arbitrarily discard records.

• Treating every missing joined value as zero.

  Check the merge indicator and source completeness. An unmatched record differs from a matched record with an unknown amount; preserve that distinction.

• Ignoring overlapping non-key column names.

  Select needed columns before merging or supply meaningful suffixes, such as `('_customer', '_order')`. Compare conflicting fields before choosing which source to trust.

Review questions

Further reading

• pandas user guide
• scikit-learn user guide