왼쪽 조인과 오른쪽 조인

비대칭 Join

Left Join: 왼쪽의 모든 행 보존하기

import pandas as pd

orders = pd.DataFrame({
    'order_id': [1, 2, 3, 4],
    'customer_id': [101, 102, 101, 999],
    'amount': [250, 80, 320, 150]
})
customers = pd.DataFrame({
    'customer_id': [101, 102, 103],
    'name': ['Alice', 'Bob', 'Carol']
})

result = pd.merge(orders, customers, on='customer_id', how='left')
print(result)
# All 4 orders kept; order with customer_id=999 gets NaN for name

Left Join 결과 구조

result = pd.merge(orders, customers, on='customer_id', how='left')
print(result)
#    order_id  customer_id  amount   name
# 0         1          101     250  Alice
# 1         2          102      80    Bob
# 2         3          101     320  Alice
# 3         4          999     150    NaN  <- no matching customer

# Count unmatched
unmatched = result['name'].isna().sum()
print(f'{unmatched} orders have no customer record')

Left Join을 사용하는 경우

# Realistic pattern: enrich all products with optional category data
products = pd.DataFrame({'sku': ['A1', 'B2', 'C3'], 'price': [10, 20, 15]})
categories = pd.DataFrame({'sku': ['A1', 'C3'], 'category': ['Tools', 'Home']})

# Keep all products; add category where available
enriched = pd.merge(products, categories, on='sku', how='left')
print(enriched)
#    sku  price category
# 0   A1     10    Tools
# 1   B2     20      NaN
# 2   C3     15     Home

Right Join: 오른쪽의 모든 행 보존하기

# Right join: keep all customers, attach orders where they exist
result = pd.merge(orders, customers, on='customer_id', how='right')
print(result)
# All 3 customers appear; Carol (103) has NaN for order_id and amount

# Equivalent left join (swap arguments):
result2 = pd.merge(customers, orders, on='customer_id', how='left')
# Same data, just column order differs

네 가지 Join 유형 비교

# Summary table
# how='inner'  : rows in BOTH tables
# how='left'   : ALL left rows  + matched right
# how='right'  : matched left   + ALL right rows
# how='outer'  : ALL left rows  + ALL right rows

# Test each
for how in ['inner', 'left', 'right', 'outer']:
    r = pd.merge(orders, customers, on='customer_id', how=how)
    print(f'{how}: {len(r)} rows')

Left Join 결과의 NaN 채우기

result = pd.merge(products, categories, on='sku', how='left')
result['category'] = result['category'].fillna('Uncategorised')
print(result)
#    sku  price       category
# 0   A1     10          Tools
# 1   B2     20  Uncategorised
# 2   C3     15           Home

Left Anti-Join: 일치하지 않는 행

# Anti-join: orders with no matching customer record
result = pd.merge(orders, customers, on='customer_id',
                  how='left', indicator=True)
unmatched_orders = result[result['_merge'] == 'left_only'].drop(columns='_merge')
print(unmatched_orders)
#    order_id  customer_id  amount  name
# 3         4          999     150   NaN

Left Join으로 행 수 보존하기

# Right table has duplicate keys -> row multiplication
orders_small = pd.DataFrame({'id': [1, 2], 'amount': [100, 200]})
multi_customer = pd.DataFrame({
    'id': [1, 1],  # duplicate!
    'contact': ['Alice', 'Alice2']
})

result = pd.merge(orders_small, multi_customer, on='id', how='left')
print(len(result))  # 3 rows! order 1 appears twice
print(result)

실제 Left Join 패턴

transactions = pd.DataFrame({
    'txn_id': [1, 2, 3],
    'cust_id': [10, 11, 99],
    'prod_id': [20, 21, 20],
    'total': [50, 30, 70]
})

custs = pd.DataFrame({'cust_id': [10, 11], 'cust_name': ['Alice', 'Bob']})
prods = pd.DataFrame({'prod_id': [20, 21], 'prod_name': ['Widget', 'Gadget']})

result = (
    transactions
    .merge(custs, on='cust_id', how='left')
    .merge(prods, on='prod_id', how='left')
)
print(result)

Left Join과 Inner Join 선택

# Left join: keep all transactions (some may have no product name)
result_left = pd.merge(transactions, prods, on='prod_id', how='left')
print('Left join rows:', len(result_left))   # same as transactions

# Inner join: only transactions with known product
result_inner = pd.merge(transactions, prods, on='prod_id', how='inner')
print('Inner join rows:', len(result_inner))  # may be fewer

빠른 확인

레슨 복습